MATLAB Answers

How to obtain hourly average from several years of data

12 views (last 30 days)
Srilatha Raghavan
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.
https://www.mathworks.com/matlabcentral/answers/455537-trouble-using-retime-to-obtain-1-year-of-hourly-average-values-from-15-years-of-hourly-average-value
I would appreciate if someone could help me with this.
Thank you

  0 Comments

Sign in to comment.

Accepted Answer

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

  7 Comments

Show 4 older comments
Sindar
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
Srilatha Raghavan on 17 Jul 2020
Oh yes! This works. Thank you so much! I appreciate your help :)

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!