How to partition hourly Data by month

Hi,i have 20-years hourly data converted into timetable,i need to retime my data to month running mean timetable and then substract my data from it. is it possible with retime function ? Otherwise, how to partition the data by month using reshape and taking into account leap and regular years ?

Thanks

Answer 1

[Akira Agata]

Yes, you can calculate monthly mean of your timetable by retime function, like this. More details can be found in the help page of retime function .

outputTT = retime(yourTT,'monthly','mean')

[locas]

Thank you Akira Agata for the answer. But what i'm really looking for is the hourly mean per day per year of each month and not the monthly mean values because monthly mean wont be able to substrat my data from it since we get only one value each month. Hope be clear .

[Akira Agata]

Thank you for your quick response. But, sorry, it's not clear for me what "the hourly mean per day per year of each month" means... Could you give us some example what it looks like?

[locas]

Let X(h,d,m,y) be my 20-years hourly data such that :

h: the hour of the day , (0 to 23)

d: the day of the month, (with 28-31 days depending on leap and regular years)

m: number of the month (1 to 12)

y: the year ( 2 jan 1989 to 31 dec 2004)

I would like to segment each year into monthly periods so that i could define for every hour (0:23) the hourly data mean X(h,m) in the hour h of month m.

After, i should operate : X(h,d,m,y)-X(h,m)

[Akira Agata]

Let me clarify one point. Is the "hourly data mean X(h,m)" the mean value of X with specific h and m and different y and d, like:

 X(h,m) = ( X(h,1,m,1989) + X(h,2,m,1989) + ... + X(h,31,m,2004) )/N

Is my understanding correct??

[locas]

Exactly Sir. Its summation over number of days (1:D) of a given m and number of years (1:Y) divided on DY . Is it possible with timetable functions ?

[Akira Agata]

Thank you for the clarification! OK, the following script would be what you described (I hope!).

% Sample hourly data from 1989/1/2 to 2004/12/31
Time = (datetime(1989,1,2,0,0,0):hours(1):datetime(2004,12,31,23,0,0))';
Data = rand(size(Time));
TT = timetable(Time,Data);

%%Calculate hourly data mean X(h,m)
[group,Hour,Month] = findgroups(hour(TT.Time),month(TT.Time));
DataAvg = splitapply(@mean,TT.Data,group);
Xresult = table(Hour,Month,DataAvg);

[Akira Agata]

Regarding the second part of your question, please add the following:

% Calculate X(h,d,m,y)-X(h,m) and store as TT.Data2
TT.Data2 = TT.Data - Xresult.DataAvg(group);

[locas]

This is genius Akira ! Thanks so much .

Answer 2

[Akira Agata]

When your data has multiple columns, the following will work.

% Sample hourly data from 1989/1/2 to 2004/12/31
Time = (datetime(1989,1,2,0,0,0):hours(1):datetime(2004,12,31,23,0,0))';
Data1 = rand(size(Time));
Data2 = rand(size(Time));
TT = timetable(Time,Data1,Data2);

% Calculate hourly data mean X(h,m)
[group,Hour,Month] = findgroups(hour(TT.Time),month(TT.Time));
DataAvg = splitapply(@mean,TT{:,:},group);
Xresult = [table(Hour,Month),array2table(DataAvg)];
Xresult.Properties.VariableNames(3:end) = TT.Properties.VariableNames;

% Calculate X(h,d,m,y)-X(h,m) for each column and store as TT2
TT2 = TT{:,:} - Xresult{group,3:end};
TT2 = array2table(TT2,'VariableNames',TT.Properties.VariableNames);
TT2 = timetable(Time,TT2);