MATLAB: How to obtain hourly average from several years of data

average by hourtimetable

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

Best Answer

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

Related Solutions

MATLAB: Shifting a timeseries without lagmatrix

Something like this maybe,

%create some data
C = {'06-Jan-1911 00:00:00'  425
'07-Jan-1911 00:00:00'  345
'08-Jan-1911 00:00:00'  425
'09-Jan-1911 00:00:00'  317
'10-Jan-1911 00:00:00'  428
'11-Jan-1911 00:00:00'  566
'12-Jan-1911 00:00:00'  515
'13-Jan-1911 00:00:00'  473
'14-Jan-1911 00:00:00'  369
'15-Jan-1911 00:00:00'  330
'16-Jan-1911 00:00:00'  305
'17-Jan-1911 00:00:00'  243
'18-Jan-1911 00:00:00'  256
'19-Jan-1911 00:00:00'  266
'20-Jan-1911 00:00:00'  240};
%convert it timetable
TT = timetable(datetime(C(:,1)),[C{:,2}].')

now it looks like,

TT =
    15×1 timetable
         Time       Var1
      __________    ____
      1911-01-06    425 
      1911-01-07    345 
      1911-01-08    425 
      1911-01-09    317 
      1911-01-10    428 
      1911-01-11    566 
      1911-01-12    515 
      1911-01-13    473 
      1911-01-14    369 
      1911-01-15    330 
      1911-01-16    305 
      1911-01-17    243 
      1911-01-18    256 
      1911-01-19    266 
      1911-01-20    240

now add 7 days to the time column,

 TT.Time = TT.Time+days(7);

now the modified timetable looks like,

TT =
    15×1 timetable
         Time       Var1
      __________    ____
      1911-01-13    425 
      1911-01-14    345 
      1911-01-15    425 
      1911-01-16    317 
      1911-01-17    428 
      1911-01-18    566 
      1911-01-19    515 
      1911-01-20    473 
      1911-01-21    369 
      1911-01-22    330 
      1911-01-23    305 
      1911-01-24    243 
      1911-01-25    256 
      1911-01-26    266 
      1911-01-27    240

is this what you want?

MATLAB: Create a single average day out of hourly data (33days)

"First row is date second is hourly mean temp."

That would be column, not row, as just a nit.

Yes, either groupsummary or varfun will do the job, but you need to create the grouping variable to use them...beginning with a timetable, tt that looks like the above, then

>> tt.Day=day(tt.Date);
>> varfun(@mean,tt,'groupingvariables','Day')
ans =
  2×3 timetable
            Date            Day    GroupCount    mean_Temp
    ____________________    ___    __________    _________
    07-Mar-2019 11:00:00     7         13         15.692  
    08-Mar-2019 00:00:00     8          8           13.5  
>> groupsummary(tt,'Day','mean')
ans =
  2×3 table
    Day    GroupCount    mean_Temp
    ___    __________    _________
     7         13         15.692  
     8          8           13.5  
>> [g,ig]=findgroups(tt.Day);
>> splitapply(@mean,tt.Temp,g)
ans =
   15.6923
   13.5000
>> table(tt.Date(ig),splitapply(@mean,tt.Temp,g))
ans =
  2×2 table
            Var1             Var2 
    ____________________    ______
    07-Mar-2019 17:00:00    15.692
    07-Mar-2019 18:00:00      13.5
>>

are various routes to the end...

>> retime(timetable(tt.Date(ig),splitapply(@mean,tt.Temp,g),'VariableNames',{'MeanTemp'}),'daily','nearest')
ans =
  2×1 timetable
            Time            MeanTemp
    ____________________    ________
    07-Mar-2019 00:00:00     15.692 
    08-Mar-2019 00:00:00       13.5 
>>

also gets rid of the odd beginning hour for each day if your data aren't all collected beginning at 00:00 each day.

You could add some error checking to ensure that don't include days that are just nightime or daytime measurements so those would bias results where there is some extreme from day to night.

ERRATUM:

Average over hours, not days as misread original intent:

tt.Hour=hour(tt.Time);
>> varfun(@mean,tt,"InputVariables",'Temp',"GroupingVariables",'Hour')
ans =
  21×3 timetable
            Time            Hour    GroupCount    mean_Temp
    ____________________    ____    __________    _________
    08-Mar-2019 00:00:00      0         1            16    
    08-Mar-2019 01:00:00      1         1            15    
    08-Mar-2019 02:00:00      2         1            13    
    08-Mar-2019 03:00:00      3         1            12    
    08-Mar-2019 04:00:00      4         1            16    
    08-Mar-2019 05:00:00      5         1            12    
    08-Mar-2019 06:00:00      6         1            12    
    08-Mar-2019 07:00:00      7         1            12    
    07-Mar-2019 11:00:00     11         1            16    
    07-Mar-2019 12:00:00     12         1            14    
    07-Mar-2019 13:00:00     13         1            12    
    07-Mar-2019 14:00:00     14         1            19    
    07-Mar-2019 15:00:00     15         1            23    
    07-Mar-2019 16:00:00     16         1            14    
    07-Mar-2019 17:00:00     17         1            15    
    07-Mar-2019 18:00:00     18         1            18    
    07-Mar-2019 19:00:00     19         1            17    
    07-Mar-2019 20:00:00     20         1            13    
    07-Mar-2019 21:00:00     21         1            14    
    07-Mar-2019 22:00:00     22         1            14    
    07-Mar-2019 23:00:00     23         1            15    
>>

Best Answer

Related Solutions

MATLAB: Shifting a timeseries without lagmatrix

MATLAB: Create a single average day out of hourly data (33days)

Related Question