Plot time series in date format from Excel sheet

2021 12 15
1 个回答
回答已采纳
更新时间：2021 12 15
3 次查看（30 天）

0 个投票

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!

请先登录，再回答此问题。

 采纳的回答

Star Strider
Star Strider 2021-12-15
Ran in:

1 个投票

Ran in:
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!
.

2 个评论
显示 无 隐藏 无

Achuan Chen
Achuan Chen 2021-12-15
Brilliant. Thank you! I was overcomplicating things.
Star Strider
Star Strider 2021-12-15
As always, my pleasure!
No worries — this is how we all learn!
.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by