How to obtain hourly average from several years of data

12 views (last 30 days)
Srilatha Raghavan on 17 Jul 2020
Commented: Sindar on 17 Jul 2020
Hello,
I have 7 years of hourly data(attached file). I have them in a timetable that has (7*8760 + extra hours from leap year) number of rows.
I am trying to find the average of each hour of the years. I have been exporting the table above to excel and then splittin it column wise and averaging and coming back to matlab and working on it. I tried the groupsummary function, but it gives me 24 hours of value.
I did find a previous answer to a similar question, but that was confusing to me as well. I have attached the link to that answer.
I would appreciate if someone could help me with this.
Thank you

Sindar on 17 Jul 2020
G = groupsummary(struct2table(WSHelp),'DateTime','hour','mean');

Sindar on 17 Jul 2020
Here's what I've got:
mytable = struct2table(WSHelp);
% add a column with the datetime excluding year and minute info
% you can mess with the format, but '01/01 00' sorts well and looks nice to me
mytable.yearhour = string(mytable.DateTime,'MM/dd HH');
% perform summary based on that column (exclude original time column from summary)
G = groupsummary(mytable(:,2:end),'yearhour','mean');
this gets you 8784 rows (includes leap year hours), looking like this:
G(1:10,:)
10×3 table
yearhour GroupCount mean_WindSpeed
__________ __________ ______________
"01/01 00" 7 13.905
"01/01 01" 7 14.095
"01/01 02" 7 14.178
"01/01 03" 7 14.098
"01/01 04" 7 14.302
"01/01 05" 6 12.989
"01/01 06" 6 12.796
"01/01 07" 6 12.485
"01/01 08" 6 12.888
"01/01 09" 6 12.601
If you want to exclude leap days:
idx = ~(month(mytable.DateTime)==2 & day(mytable.DateTime)==29);
G = groupsummary(mytable(idx,2:end),'yearhour','mean');
Srilatha Raghavan on 17 Jul 2020
Oh yes! This works. Thank you so much! I appreciate your help :)
Sindar on 17 Jul 2020
Sorry, I had some typos, try the updated