How to group data according to date in years?

I have a table with data of specific days of the year over a range of 10 years, e.g.

1.Jan.2013 12

8.Jan.2013 10

...

23.May.2013 15

30.May.2013 12

...

8.Nov.2013 5

...

1.Jan.2014 16

8.Jan.2014 11

...

23.May.2014 11

30.May.2014 10

...

23.May.2015 8

30.May.2015 9

...

(in other words: I have data over a period of 10 years of the 1.Jan,8.Jan,....,23.May,30.May,.....8.Nov,...)

Now I would like to have a groupsummary according to these dates over all the years, e.g.

Date mean func1 median etc.

1.Jan 11 6 4

8.Jan 10 5 3

...

23.May 9 5 10

...

In the end I would like to plot all the function values (mean, median, func1) over Date (1.Jan, 8.Jan, ...,23.May,... on x-axis).

How can I achieve this?

I tried with 'dayofyear', but this is a bit annoying because of leap years...

Answer 1

[Star Strider]

Since 'dayofyear' won’t work, break the dates down into two new variables, specifically the day and the month, then group on them. To get them back to the 'dd.MMM' format requires a bit of creativity, however it can be done —

T1 = table({'1.Jan.2013'; '8.Jan.2013'; '1.Jan.2014'; '8.Jan.2014'}, [12; 10;, 16; 11], 'VariableNames',{'Date','Something'})

T1 = 4×2 table
         Date         Something
    ______________    _________

    {'1.Jan.2013'}       12    
    {'8.Jan.2013'}       10    
    {'1.Jan.2014'}       16    
    {'8.Jan.2014'}       11    

T1.Var1 = datetime(T1.Date, 'InputFormat','dd.MMM.yyyy','Format','dd.MMM.yyyy')

T1 = 4×3 table
         Date         Something       Var1    
    ______________    _________    ___________

    {'1.Jan.2013'}       12        01.Jan.2013
    {'8.Jan.2013'}       10        08.Jan.2013
    {'1.Jan.2014'}       16        01.Jan.2014
    {'8.Jan.2014'}       11        08.Jan.2014

T1.Day = day(T1.Date);
T1.Month = month(T1.Date)

T1 = 4×5 table
         Date         Something       Var1        Day    Month
    ______________    _________    ___________    ___    _____

    {'1.Jan.2013'}       12        01.Jan.2013     1       1  
    {'8.Jan.2013'}       10        08.Jan.2013     8       1  
    {'1.Jan.2014'}       16        01.Jan.2014     1       1  
    {'8.Jan.2014'}       11        08.Jan.2014     8       1  

G1 = groupsummary(T1,{'Day','Month'},{'mean','median','std',@(x)std(x)/sqrt(numel(x))},'Something')

G1 = 2×7 table
    Day    Month    GroupCount    mean_Something    median_Something    std_Something    fun1_Something
    ___    _____    __________    ______________    ________________    _____________    ______________

     1       1          2                14                 14              2.8284              2      
     8       1          2              10.5               10.5             0.70711            0.5      

G1.DayMonth = datetime(zeros(size(G1.Month)),G1.Month,G1.Day, 'Format','dd.MMM')

G1 = 2×8 table
    Day    Month    GroupCount    mean_Something    median_Something    std_Something    fun1_Something    DayMonth
    ___    _____    __________    ______________    ________________    _____________    ______________    ________

     1       1          2                14                 14              2.8284              2           01.Jan 
     8       1          2              10.5               10.5             0.70711            0.5           08.Jan 

G1.Properties.VariableNames{end-1} = 'StdErrMean_Something';
G2 = G1(:,[end 3 4:end-1])

G2 = 2×6 table
    DayMonth    GroupCount    mean_Something    median_Something    std_Something    StdErrMean_Something
    ________    __________    ______________    ________________    _____________    ____________________

     01.Jan         2                14                 14              2.8284                 2         
     08.Jan         2              10.5               10.5             0.70711               0.5         

Add other summary statistics as desired.

With respect to plotting them, see Plot Dates and Durations for a complete dsescription of the process.

.

[Marcel345614]

Thanks a lot! This was very helpful!

[Star Strider]

As always, my pleasure!

This is an interesting problem. Thank you for posting it!