take averages of obvervations for each month

suppose I have a table S

date             income
----------------------------
1/1/2020     100
1/2/2020     20

I want to create a table T of mean of income at each month like

month            income average
----------------------------
1/2020     50
2/2020     20

So is it that I first create

T.yyyyMM=datetime(S.date, 'InputFormat','MM/dd/yyyy', 'Format','yyyyMM');

then use

T.accumarray(T.yyyyMM,S.income,[],@mean)

Does it make sense?

Answer 1

[jonas]

Try this instead

retime(T,'monthly','mean')

where T is a timetable

example

t = (datetime(2020,1,1):days(1):datetime(2021,1,1))';
income = 10+rand(numel(t),1).*4;
T = timetable(t,income)
T_Monthly = retime(T,'monthly','mean')

[alpedhuez]

Thank you. But what if T is more complicated such as

date             income time_paid
----------------------------
1/1/2020     100        morning
1/2/2020     20         evening

and want to create

month            income average
----------------------------
1/2020     50
2/2020     20

?

[jonas]

I don't understand why this case is more complicated. Obviously you cannot take an average of a column with strings, but you can easily exclude it. Just pass specific columns to retime.

In your case it would be

T_Monthly = retime(T(:,1),'monthly','mean')

[madhan ravi]

https://www.mathworks.com/help/matlab/matlab_prog/access-data-in-a-table.html