Average of variables in timetable belonging to the same day over several years

Hello, I have a timetable, TT(681-by-19), with unevenly spaced daily data from 2010 to 2020 and 19 variables (there might be days that do not occur every year in addition to gaps where entire months are missing). I want to calculate the mean of the variables that correspond to the same day throughout the different years. For example I want to calculate the mean of each of the 19 variables that correspond to 25-oct 2010, 25-oct 2011, ..., 25-oct 2020 and so on for the different dates. That way I would have each unique day-month occurring only once in the output.

Note: I also have the dates seperately as a datetime array and as a numeric array in the form of yyyymmdd (681-by-1) and the 19 variables in a numeric array (681-by-19), if there is a simpler method without the use of the timetable.

Thanks for any assistance.

Answer 1

[Peter Perkins]

Ziad, it seems that "dayofyear" is using "serial day number of year", and as you found, is lumping 29-Feb's with the non-leap 1-Mar's, etc. What you want is to group by month/day. If you use the month and day of month as grouping vars, you get what you want:

dt = [datetime(2012,2,27:31) ...
    datetime(2013,2,27:30) ...
    datetime(2014,2,27:30) ...
    datetime(2015,2,27:30) ...
    datetime(2016,2,27:31)]';
x = dt.Day;
tt = timetable(x,RowTimes=dt)

tt = 22×1 timetable
       Time        x 
    ___________    __

    27-Feb-2012    27
    28-Feb-2012    28
    29-Feb-2012    29
    01-Mar-2012     1
    02-Mar-2012     2
    27-Feb-2013    27
    28-Feb-2013    28
    01-Mar-2013     1
    02-Mar-2013     2
    27-Feb-2014    27
    28-Feb-2014    28
    01-Mar-2014     1
    02-Mar-2014     2
    27-Feb-2015    27
    28-Feb-2015    28
    01-Mar-2015     1

tt.Month = string(month(tt.Time,"name"));

means = 5×3 table
    dayofyear_Time    GroupCount    mean_x
    ______________    __________    ______

          58              5            27 
          59              5            28 
          60              5          12.2 
          61              5           1.6 
          62              2             2 

tt.Day = day(tt.Time,"dayofmonth");
means = groupsummary(tt,["Month" "Day"],"mean","x")

means = 5×4 table
      Month       Day    GroupCount    mean_x
    __________    ___    __________    ______

    "February"    27         5           27  
    "February"    28         5           28  
    "February"    29         2           29  
    "March"        1         5            1  
    "March"        2         5            2  

[Ziad Sari El Dine]

Great, thanks!

Answer 2

[Cris LaPierre]

Use groupsummary with the groupbins set to 'dayofyear'. Select one of the following syntaxes.

• G = groupsummary(T,groupvars,groupbins)
• G = groupsummary(T,groupvars,groupbins,method)
• G = groupsummary(T,groupvars,groupbins,method,datavars)

[Ziad Sari El Dine]

Thanks!

[Ziad Sari El Dine]

Hello, there is an issue where different days are grouped together due to the presence of two leap years (data from 2010 to 2020) so different days would have the same day of year number.

Is there any fix?