Plot time series in date format from Excel sheet

The Excel sheet attached contains a date column, which is in the format dd/mm/yyyy.

I would like to create a time-series plot for the prices contained in table A, with the date displayed on the x-axis.

I've tried to convert the dates in the 'date' column to datetime type, then use the plot() function to create the plot, but apparentlty it is of the wrong data type.

My code:

opts = detectImportOptions('fruitvegprices.csv');%detects import options for the file
opts = setvartype(opts,["variety"],"categorical");%changes variety to categorical
data = readtable('fruitvegprices.csv',opts);%import spreadsheet 

%apples
apples = data(data.item=="apples",:); %table of just apples
uniq_apples=unique(apples.variety,'stable');%distinct varieties of apple

item=uniq_apples(8);
A=apples(ismember(apples.variety,item),:)

ts=timeseries(A.price);

date = datetime(A.date,'InputFormat','dd-mmmm-yyyy')
plot(date,ts)

How could I resolve this?

Thank you!

Answer 1

[Star Strider]

There is nothing wrong with the dates.

Try something like this —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/834340/fruitvegprices.csv', 'VariableNamingRule','preserve')

T1 = 9148×6 table
      category               item                   variety               date       price      unit  
    _____________    ____________________    ______________________    __________    _____    ________

    {'fruit'    }    {'apples'          }    {'bramleys_seedling' }    2021-11-12    1.49     {'kg'  }
    {'fruit'    }    {'apples'          }    {'coxs_orange_group' }    2021-11-12    1.09     {'kg'  }
    {'fruit'    }    {'apples'          }    {'egremont_russet'   }    2021-11-12    1.14     {'kg'  }
    {'fruit'    }    {'apples'          }    {'braeburn'          }    2021-11-12    1.01     {'kg'  }
    {'fruit'    }    {'apples'          }    {'gala'              }    2021-11-12    1.01     {'kg'  }
    {'fruit'    }    {'apples'          }    {'other_early_season'}    2021-11-12    0.95     {'kg'  }
    {'fruit'    }    {'pears'           }    {'conference'        }    2021-11-12    1.04     {'kg'  }
    {'fruit'    }    {'pears'           }    {'doyenne_du_comice' }    2021-11-12    1.08     {'kg'  }
    {'fruit'    }    {'raspberries'     }    {'raspberries'       }    2021-11-12     4.4     {'kg'  }
    {'fruit'    }    {'strawberries'    }    {'strawberries'      }    2021-11-12    6.24     {'kg'  }
    {'vegetable'}    {'beetroot'        }    {'beetroot'          }    2021-11-12    0.51     {'kg'  }
    {'vegetable'}    {'brussels_sprouts'}    {'brussels_sprouts'  }    2021-11-12    0.98     {'kg'  }
    {'vegetable'}    {'pak_choi'        }    {'pak_choi'          }    2021-11-12    2.72     {'kg'  }
    {'vegetable'}    {'curly_kale'      }    {'curly_kale'        }    2021-11-12    3.26     {'kg'  }
    {'vegetable'}    {'cabbage'         }    {'red'               }    2021-11-12    0.55     {'kg'  }
    {'vegetable'}    {'cabbage'         }    {'savoy'             }    2021-11-12     0.5     {'head'}

[G,ID] = findgroups(T1.variety)                                 % Group By 'variety'

G = 9148×1

13 27 33 12 36 45 23 31 55 65

ID = 77×1 cell array
    {'13_cm'             }
    {'9_cm'              }
    {'all'               }
    {'all_other'         }
    {'all_varieties'     }
    {'all_washed'        }
    {'asparagus'         }
    {'beetroot'          }
    {'black'             }
    {'blackberries'      }
    {'blueberries'       }
    {'braeburn'          }
    {'bramleys_seedling' }
    {'broad'             }
    {'brussels_sprouts'  }
    {'bulb_brown'        }
    {'bulb_red'          }
    {'butterhead_indoor' }
    {'calabrese'         }
    {'celeriac'          }
    {'cherry'            }
    {'chinese_leaf'      }
    {'conference'        }
    {'coriander'         }
    {'cos'               }
    {'courgettes'        }
    {'coxs_orange_group' }
    {'crisp_iceberg_type'}
    {'cucumbers'         }
    {'curly_kale'        }

Gu = unique(G);                                                 % Number Of Unique Groups (Information Only, Not Needed For Code)
T1.price(G==G(1),:);                                            % Checking Indexing

figure
plot(T1.date(G==G(7),:), T1.price(G==G(7),:))
hold on
plot(T1.date(G==G(4),:), T1.price(G==G(4),:))
plot(T1.date(G==G(5),:), T1.price(G==G(5),:))
plot(T1.date(G==G(6),:), T1.price(G==G(6),:))
plot(T1.date(G==G(8),:), T1.price(G==G(8),:))
hold off
grid
str = strrep(ID([7 4 5 6 8]), '_','\_');
legend(str, 'Location','best')
xlabel('Date')
ylabel('Price (€)')

Experiment!

.

[Achuan Chen]

Brilliant. Thank you! I was overcomplicating things.

[Star Strider]

As always, my pleasure!

No worries — this is how we all learn!

.