How to obtain hourly average from several years of data

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

Answer 1

[Sindar]

G = groupsummary(struct2table(WSHelp),'DateTime','hour','mean');

[Sindar]

if you want average across all days (say, noon-1), use

>> G = groupsummary(struct2table(WSHelp),'DateTime','hourofday','mean')
G =
  24×3 table
    hourofday_DateTime    GroupCount    mean_WindSpeed
    __________________    __________    ______________

            0                2557           11.393    
            1                2557           11.571    
            2                2557           11.674    
            3                2557           11.704    
            4                2557           11.658    
            5                2556           11.607    
            6                2556           11.562    
            7                2556           11.487    
            8                2556           11.436    
            9                2556            11.41    
            10               2556           11.336    
            11               2556           11.202    
            12               2556           11.081    
            13               2556           11.036    
            14               2556           11.085    
            15               2556            11.16    
            16               2556           11.308    
            17               2556           11.426    
            18               2556           11.494    
            19               2556           11.494    
            20               2556           11.441    
            21               2556            11.32    
            22               2557           11.228    
            23               2557           11.243 

[Srilatha Raghavan]

Thank you for this. But I can get the 24 values which is the hourly average with groupsummary. I am looking for 8760 values, which are hourly avergae across the years. For example, I want the average of hour 1 of all the years i have to be the hour 1 of my answer. Hour 2 of all years averaged and hour 3 and so on... My group count should be 6(indicating 6 years).

TIA

[Sindar]

Oh, so you want average over hour-of-year? Gimme a minute to think

[Srilatha Raghavan]

Yes! I also tried hard coding it, but when I am trying to merge the tables with new columns using loops, the number of rows are not the same(leap years). This again is more lines of code which makes my code less readable.

[Sindar]

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]

Oh yes! This works. Thank you so much! I appreciate your help :)

[Sindar]

Sorry, I had some typos, try the updated