How to average data to montly data?

7 views (last 30 days)
Ahmet Hakan UYANIK on 16 May 2021
Edited: Adam Danz on 19 May 2021
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.

Pranav Verma on 19 May 2021
Edited: Pranav Verma on 19 May 2021
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
3 CommentsShow 1 older commentHide 1 older comment
Pranav Verma on 19 May 2021
Edited: Pranav Verma on 19 May 2021
Thanks @Siddharth Bhutiya for a shorter workaround!
Adam Danz on 19 May 2021
Edited: Adam Danz on 19 May 2021
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