How to average data to montly data?

Hello,

I have datetime matrix T (size 1x1037) from 1992 to 2020 at this format :

'1995-11-11 10:06:20'

'1995-11-21 08:04:52'

'1995-12-01 06:03:23'

'1995-12-11 04:01:54'

'1995-12-21 02:00:25'

'1995-12-30 23:58:56'

'1996-01-09 21:57:26'

'1996-01-19 19:55:58'

'1996-01-29 17:54:31'

.

.

.

and another matrix B with the same size 1x1037, which correpondence indices' of time is measurement.

I would like to average this data to montly data and measurements should also be averaged to that specific month.

I tried with for loop but correspondences are mixed.

How can i do this averaging to montly? I would be very pleased for help.

Answer 1

[Pranav Verma]

Hi Ahmet,

From your question I understand that you want to club your matrices into one and then run the mean on it to get the monthly average. You can simply create a table from the two matrices and use the groupsummary function on that table in MATLAB to group the data on the basis of months and get the mean.

I have tried implementing the same on the given data. I have a datetime array "st" and have created an array "measure" containing same number of values as in st.

%% st = 
% {'1995-11-11 10:06:20';
% '1995-11-21 08:04:52';
% '1995-12-01 06:03:23';
% '1995-12-11 04:01:54';
% '1995-12-21 02:00:25';
% '1995-12-30 23:58:56';
% '1996-01-09 21:57:26';
% '1996-01-19 19:55:58';
% '1996-01-29 17:54:31';
% '1996-11-11 10:06:20'}

measure = [1; 2; 3; 4; 5; 6; 7; 8; 9; 1]
t = datetime(st,'InputFormat','yyyy-MM-dd HH:mm:ss')
data = table(t, measure)
groupsummary(data, 't', 'month', 'mean', 'measure')

This returns the result as:

      month_t     GroupCount    mean_vec1
    ________    __________    _________

    Nov-1995        2            1.5   
    Dec-1995        4            4.5   
    Jan-1996        3              8   
    Nov-1996        1              1   

Here I have put the bin as "month" and the function to be applied on measure as "mean".

You can try something on the similar lines with your dataset in groupsummary function.

The groupsummary documentation has all the details about the groupbins. Feel free to leverage the same.

Hope it helps!

Thanks

[Siddharth Bhutiya]

Another approach could be to put your data into a timetable and then use retime on it to get the monthly data.

dtStr = {'1995-11-11 10:06:20',...
'1995-11-21 08:04:52',...
'1995-12-01 06:03:23',...
'1995-12-11 04:01:54',...
'1995-12-21 02:00:25',...
'1995-12-30 23:58:56',...
'1996-01-09 21:57:26',...
'1996-01-19 19:55:58',...
'1996-01-29 17:54:31'};
dt = datetime(dtStr','InputFormat',"uuuu-MM-dd HH:mm:ss");
measure = (1:numel(dt))';

tt = timetable(measure,'RowTimes',dt);
ttMonthly = retime(tt,'monthly','mean')

ttMonthly =

  3×1 timetable

       Time        measure
    ___________    _______

    01-Nov-1995      1.5  
    01-Dec-1995      4.5  
    01-Jan-1996        8

Since you are working with timestamped data and there is 1 to 1 correspondence between your time vector and data vector, it seems more logical to use a timetable, since timetable were designed for that specifc use case.

[Pranav Verma]

Thanks @Siddharth Bhutiya for a shorter workaround!

[Adam Danz]

Here's a comparison of outputs between retime and groupsummary. The main differences are

1. The groupsummary output contains a colulmn showing the number of samples for each month
2. groupsummary changes the names of the column variables
3. groupsummary formats the datetime values to show month-year but this needs to be manually set in the retime output timetable.
4. retime became available in R2016b, groupsummary became available in 2018a
5. The output is a timetable for retime and a table for groupsummary.

Create demo timetable TT

rng('default')
Time = datetime(1992,1,1) + hours([0;cumsum(randi([48,96],5000,1))]);
TT = array2timetable(rand(numel(Time),5).*randi(10,1,5), 'RowTimes', Time)

TT = 5001×5 timetable
            Time             Var1       Var2        Var3       Var4       Var5  
    ____________________    _______    _______    ________    _______    _______

    01-Jan-1992 00:00:00     0.8444     8.6562      3.9993     2.7166     7.6335
    04-Jan-1992 15:00:00     3.7258     7.8863      1.6554     5.0705     7.8684
    08-Jan-1992 11:00:00     2.3857     4.3977      3.6871     5.3697     8.2393
    10-Jan-1992 17:00:00     3.2672     3.6637      2.8372     1.6848     3.8285
    14-Jan-1992 13:00:00     4.8329     1.1392    0.080318    0.24835     3.5361
    17-Jan-1992 19:00:00     1.5651     8.3288      2.1276     6.6661     3.3052
    19-Jan-1992 23:00:00     0.3822    0.05023     0.14689     3.7134     7.0245
    22-Jan-1992 12:00:00     3.9571     1.6775      3.9209     1.1229     3.8389
    25-Jan-1992 14:00:00     1.8269     2.9165      4.9041     2.5393    0.31135
    29-Jan-1992 12:00:00     2.9255    0.45168      3.4497     1.9433     4.5664
    02-Feb-1992 11:00:00    0.91668     1.3008      3.8984     4.0649     2.7942
    04-Feb-1992 18:00:00    0.38459     6.5643      2.9899     4.4861     3.3628
    08-Feb-1992 17:00:00    0.76831     4.3405       1.423    0.83221    0.52184
    12-Feb-1992 15:00:00     4.1344     3.0426      3.7313    0.21905     5.3999
    15-Feb-1992 14:00:00     1.5048     2.1309      3.4694     3.3658     3.8094
    19-Feb-1992 05:00:00     1.9194     4.0578      2.2126     1.0787     9.9844

Use retime to compute monthly means

TT2 = retime(TT,'Monthly','mean');
TT2.Properties.RowTimes.Format = 'MMM-uuuu' % set month-year format

TT2 = 495×5 timetable
      Time       Var1      Var2      Var3      Var4      Var5 
    ________    ______    ______    ______    ______    ______

    Jan-1992    2.5713    3.9168    2.6808    3.1075    5.0152
    Feb-1992    2.3111    3.1556    2.5862    2.9162    5.3543
    Mar-1992    2.2455    4.4488    3.0459    3.0383    6.1231
    Apr-1992    1.9915    4.6263    2.4208    3.1691    4.3656
    May-1992    1.7988     3.433    2.9144    3.5001    4.1412
    Jun-1992    2.5952    4.8661    3.0239     3.479    5.7174
    Jul-1992    3.1465    3.8133    2.5012    2.9412    3.9418
    Aug-1992    2.8953    5.3106    2.5828    3.4545    4.0901
    Sep-1992    3.2063    5.7912    1.5066     3.444    3.8388
    Oct-1992    2.2486    4.7958    1.9014    3.2516    5.0418
    Nov-1992    2.1649    3.4559    2.9657    4.6068    3.4017
    Dec-1992    2.1432    5.1185    2.8395    3.8397     6.315
    Jan-1993    2.7347      4.01     3.411    3.4863    4.9686
    Feb-1993     2.274    4.0731    3.4523    3.5674    5.3943
    Mar-1993    3.1401    4.5919      2.31    3.4604    4.9355
    Apr-1993    2.1517    2.6981     1.117    3.2385    4.0843

Use groupsummary to show monthly means.

groupsummary(TT, 'Time', 'month', 'mean', TT.Properties.VariableNames)

ans = 495×7 table
    month_Time    GroupCount    mean_Var1    mean_Var2    mean_Var3    mean_Var4    mean_Var5
    __________    __________    _________    _________    _________    _________    _________

     Jan-1992         10         2.5713       3.9168       2.6808       3.1075       5.0152  
     Feb-1992          9         2.3111       3.1556       2.5862       2.9162       5.3543  
     Mar-1992          9         2.2455       4.4488       3.0459       3.0383       6.1231  
     Apr-1992         11         1.9915       4.6263       2.4208       3.1691       4.3656  
     May-1992         10         1.7988        3.433       2.9144       3.5001       4.1412  
     Jun-1992         10         2.5952       4.8661       3.0239        3.479       5.7174  
     Jul-1992         10         3.1465       3.8133       2.5012       2.9412       3.9418  
     Aug-1992         11         2.8953       5.3106       2.5828       3.4545       4.0901  
     Sep-1992          9         3.2063       5.7912       1.5066        3.444       3.8388  
     Oct-1992         11         2.2486       4.7958       1.9014       3.2516       5.0418  
     Nov-1992         10         2.1649       3.4559       2.9657       4.6068       3.4017  
     Dec-1992         11         2.1432       5.1185       2.8395       3.8397        6.315  
     Jan-1993         10         2.7347         4.01        3.411       3.4863       4.9686  
     Feb-1993         10          2.274       4.0731       3.4523       3.5674       5.3943  
     Mar-1993         12         3.1401       4.5919         2.31       3.4604       4.9355  
     Apr-1993          9         2.1517       2.6981        1.117       3.2385       4.0843