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

How am I able to sum my hourly data to create one "typical/average" day (diurnal cycle) out of 33 days.

I'm using 2017b and stored my data in a timetable using retime() to story hourly means

First row is date second is hourly mean temp.

'07-Mar-2019 11:00:00' 16
'07-Mar-2019 12:00:00' 14
'07-Mar-2019 13:00:00' 12
'07-Mar-2019 14:00:00' 19
'07-Mar-2019 15:00:00' 23
'07-Mar-2019 16:00:00' 14
'07-Mar-2019 17:00:00' 15
'07-Mar-2019 18:00:00' 18
'07-Mar-2019 19:00:00' 17
'07-Mar-2019 20:00:00' 13
'07-Mar-2019 21:00:00' 14
'07-Mar-2019 22:00:00' 14
'07-Mar-2019 23:00:00' 15
'08-Mar-2019 00:00:00' 16
'08-Mar-2019 01:00:00' 15
'08-Mar-2019 02:00:00' 13
'08-Mar-2019 03:00:00' 12
'08-Mar-2019 04:00:00' 16
'08-Mar-2019 05:00:00' 12
'08-Mar-2019 06:00:00' 12
'08-Mar-2019 07:00:00' 12

Matlab 2019 has groupsummary() which might do my job....

Answer 1

[dpb]

"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    
>> 

[Martin Muehlegger]

Thanks, my bad ment columns instead of rows....

looked for varfun thanks.

Can you explein why i get a 2x1 timetable at the end?

Because what I kind of wanted to get is a 24x1 timetable (typical diurnal cycle of all the days)

thanks

[dpb]

I thought it was the day average you wanted over the year and there are only two days in the dataset.

Use hour() instead of day() for grouping variable but you don't have enough data in the sample to average (which is part of why I misread the request even though it does indeed say dirurnal; my equivalent of row vs column! :)

[Martin Muehlegger]

Perfekt that looks good. THANKS

Answer 2

[Paul MAUGER]

Ia ora na,

As I've got a similar question I suppose it's better if I post it here :

I've got almost 5 years of hourly solar GHI data and I want to create a " (hourly) typical year ", i.e., I want to average for each hour, of each day in any year, the GHI value (see attached timetable : MUS_TT.txt). For example, if I got 4 values of GHI for February, 8th, at 9:00 (within years 2017, 2018, 2019 and 2020) then I want to average these 4 values.

At the end I should get a 365*24 = 8760 rows timetable (if there's not too much holes).

Note that I don't care about Feb. 29th of leap years 2016 and 2020 as the purpose is a typical year average and most years have 365 days.

Thanks for your help.

[dpb]

Same as above except use two grouping variables, day and hour..you'll automagically get Feb 29 unless you remove it from the table first (or wait and do so later, it won't affect the results for other days).

[Paul MAUGER]

Yes, I'm sorry but despite I read your answer to Martin above, and also loads of other examples here and elsewhere, I struggle grouping the data. I don't understand how I should group them (the order : day first, hour then / or the contrary). I don't even know if I have to separate the groupings, i.e. . I don't master at all grouping tools. For now I tried :

MUS_TT.Hour = hour(MUS_TT.DATE_TIME);
MUS_TT.Day = day(MUS_TT.DATE_TIME);

varfun(@mean,MUS_TT,"InputVariables",'GHI',"GroupingVariables",'Day')
varfun(@mean,MUS_TT,"InputVariables",'GHI',"GroupingVariables",'Hour')
varfun(@mean,MUS_TT,"InputVariables",'GHI',"GroupingVariables",'Day',"GroupingVariables",'Hour')

I got a 24 x 3 array with 'Hour' filtering / I got a 31 x 3 array for 'Day' filtering. And for the latter : of course it didn't work. I have no idea of the syntax I should use.

Also, it seems 'Day' counts the day in a month, not in a year. Hence filtering this way will make 1st of Jannuary and 1st of June equivalent, which I don't want.

[dpb]

"'Day' counts the day in a month, not in a year. "

Yes, it returns the day from the calendar datetime value -- I was implying DOY and I thought there was a builtin function to do that, but I don't see it. I'll put in an enhancement request, it should be a no-brainer to have done.

But, you can work around it with a little cleverness in a "hack" since you also can't directly take the double() of a datetime --

MUS_TT.Hour=hour(MUS_TT.DATE_TIME);
MUS_TT.DOY=(MUS_TT.DATE_TIME); MUS_TT.DOY.Format='DDD'; % duplicate time show as DOY
MUS_TT_DOY=str2double(string(MUS_TT_DOY));              % convert display DOY to double

varfun(@mean,MUS_TT,"InputVariables",'GHI',"GroupingVariables",{'DOY','Hour'});

It is a litte more round-the-barn way to get there than I had thought as I thought there was a DOY function so wouldn't have to go thru gyrations to generate it.

You can convert to duration() by subtracting the beginning of the year from each datetime value, but if the series doesn't necessarily begin at the first of the year, you have to make sure to use Jan 1 of the specific year to get the right answer - the above does that internally with the actual datetime value so doesn't matter whether the series is complete nor do you have to find the year; it's embedded.

[dpb]

ADDENDUM: On reflection, you will want to remove the leap year February dates first this way--they do offset the DOY by one for those years for following days.

[Paul MAUGER]

Hi,

Thanks for the help. I wandered indeed about the leap day and his effect. I think you're right and that I should delete concerned rows before anything else.

(btw, the last line of code is missing a } at the end)

[dpb]

Good catch on the typo...

BTW, I didn't try, but also I think you can/could leave DOY as the string array, but not as the datetime date even though it is displayed as DOY--internally the full time is still stored; only the output format is changed (just like you can print a double with less precision without changing its stored value).

[Paul MAUGER]

I tried this (my comments in Capital letters in the code) :

isLeapDay = month(MUS_TT.DATE_TIME)==2 & day(MUS_TT.DATE_TIME)==29; 
MUS_TT(isLeapDay,:) = []; % CHECKED : REMOVES ALL FEB. 29TH HOURS IN THE WHOLE TIMETABLE

MUS_TT.Hour = hour(MUS_TT.DATE_TIME); % SO THERE SHOULDN'T BE ANY FEB. 29TH IN THERE
MUS_TT.DOY =(MUS_TT.DATE_TIME); MUS_TT.DOY.Format='DDD'; % duplicate time show as DOY --> NEVERTHELESS, 'DDD' STILL COUNTS 366 DAYS A YEAR FOR 2016 AND 2020
MUS_TT.DOY =  str2double(string(MUS_TT.DOY));              % convert display DOY to double
TYP_YEAR = varfun(@mean,MUS_TT,"InputVariables",'GHI',"GroupingVariables",{'DOY','Hour'});

So, in the end, I still get a 366*24 = 8784 rows timetable ... even though I can't find any Feb. 29th in DateTime column of TYP_YEAR timetable, which troubles me, because I don't know where these 24 extra hours are hiding.

[Paul MAUGER]

Finally, if I apply the LeapDay filter on TYP_YEAR timetable, I got a 8760 hourly GHI typical year.

Thanks for the help. How can I get your answer validated on the other topic you flagged ?

[dpb]

Oh!

Yeah, it's easy for one to overlook the obvious...the calculation of the DOY internally to the datetime value will still know if the year is a leap year or not...it's based on the actual calendar date whether there are any data left in the table for the day or not.

The thing to do is after you've removed the 29th day February from the table, recast the year for the leap years to a non-leapyear year. Then the internal clock will think there are only 365 days in that year, too. Otherwise, as you observed, it just won't care--a leapyear is a leapyear, regardless.

Since you're only counting the days of year, and not using the year itself here, "lying" to it about which year it is won't matter but will count all years as 365-day years.

That's an easier fix than trying to locate and subtract 1 from March 1 thru end of year from the DOY for the leapyears which is the other way to correct for it.

Alternatively, if you had no missing data, you could just arbitrarily assign the 1:365 DOY vector--that gets to be a mess to handle the general case if there were any missing data which is why I went this way. Just didn't think it through all the way, sorry!

[dpb]

Example...

>> tt.Date=linspace(tt.Date(1),datetime(2016,12,31),24).';  % build a leap year table
>> tt.DOY=tt.Date; tt.DOY.Format='DDD';                     % and DOY grouping var
>> tail(tt)                                                 % get 366
ans
  8×2 table
          Date           DOY
    _________________    ___
    11-Sep-2016 03:26    255
    26-Sep-2016 23:31    270
    12-Oct-2016 19:36    286
    28-Oct-2016 15:41    302
    13-Nov-2016 11:45    318
    29-Nov-2016 07:50    334
    15-Dec-2016 03:55    350
    31-Dec-2016 00:00    366
>> tt.Date=tt.Date+years(1);                    % add a year...now it's 2017
>> tt.DOY=tt.Date; tt.DOY.Format='DDD';         % redo DOY 
tail(tt)
  8×2 table
          Date           DOY
    _________________    ___
    11-Sep-2017 09:15    254
    27-Sep-2017 05:20    270
    13-Oct-2017 01:25    286
    28-Oct-2017 21:30    301
    13-Nov-2017 17:34    317
    29-Nov-2017 13:39    333
    15-Dec-2017 09:44    349
    31-Dec-2017 05:49    365
>> 

and now there are only 365 days since it isn't a leapyear any longer.

Alternatively, if you didn't want to just throw the data away, I wasn't sure what datetime would do internally, but experimentation shows--

t = 
  datetime
   29-Feb-2016 14:36:31
>> t=t+years(1)
t = 
  datetime
   28-Feb-2017 20:25:43
>> 

Adding a year to a leapyear Feb 29 day just converts it to the last day of the month for the next year. This would end up giving you two Feb 28th for the two leap years to be averaged in to the means for the 59th DOY. Your choice as to which way you would want to go, looks like.

[dpb]

OH! Again...wasn't thinking -- you want to use calyears() here, not years() --

>> t+days(1)
ans = 
  datetime
   29-Feb-2016 20:25:43
>> ans+calyears(1)
ans = 
  datetime
   28-Feb-2017 20:25:43
>>

It just rolls the time field over and maxes-out the number of days/month so 29-Feb --> 28-Feb in the non-leapyear year.

This is "free" and doesn't require you to throw the 29th day of data away entirely unless you choose to do so.