How to find month wise mean and std from data of many years

4 May 2023
2 Answers
Answer Accepted
Updated 8 May 2023
14 Views (30 days)

0 votes

my data is from 2005 to 2022 containg 7 variables as well as NaN , how I can find month wise mean and std
january
February
March
.
.
december

Sign in to answer this question.

 Accepted Answer

Stephen23
Stephen23 on 4 May 2023
Edited: Stephen23 on 4 May 2023
Ran in:

0 votes

Ran in:
"I think groupsummary is good function but I am unable to do that with my table data."
Lets try it:
T = readtable('data.csv')
T = 190×8 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 __________ ________ ________ ________ ________ ________ ________ ________ 01.01.2005 NaN NaN NaN NaN NaN NaN NaN 01.02.2005 NaN NaN NaN NaN NaN NaN NaN 01.03.2005 NaN NaN NaN NaN NaN NaN NaN 01.04.2005 0.060516 0.069278 0.086602 0.12014 0.13746 0.15712 0.17569 01.05.2005 0.042994 0.046768 0.057593 0.080507 0.091848 0.1056 0.11985 01.06.2005 0.022818 0.024686 0.032003 0.051454 0.064051 0.071256 0.086593 01.07.2005 0.02403 0.023208 0.027521 0.043028 0.052892 0.055302 0.065115 01.08.2005 0.0416 0.044165 0.052146 0.07018 0.078721 0.088574 0.099769 01.09.2005 0.01913 0.022287 0.027821 0.039807 0.046763 0.057148 0.065082 01.10.2005 NaN NaN NaN NaN NaN NaN NaN 01.11.2005 NaN NaN NaN NaN NaN NaN NaN 01.12.2005 NaN NaN NaN NaN NaN NaN NaN 01.01.2006 NaN NaN NaN NaN NaN NaN NaN 01.02.2006 NaN NaN NaN NaN NaN NaN NaN 01.03.2006 0.037744 0.056849 0.069755 0.10222 0.11358 0.13603 0.14901 01.04.2006 0.11103 0.12077 0.13721 0.17954 0.19551 0.22405 0.24487
M = groupsummary(T,"Var1","monthofyear","mean")
M = 12×9 table
monthofyear_Var1 GroupCount mean_Var2 mean_Var3 mean_Var4 mean_Var5 mean_Var6 mean_Var7 mean_Var8 ________________ __________ _________ _________ _________ _________ _________ _________ _________ 1 16 NaN NaN NaN NaN NaN NaN NaN 2 16 NaN NaN NaN NaN NaN NaN NaN 3 16 0.045766 0.054519 0.069323 0.098659 0.11312 0.13184 0.14281 4 16 0.060333 0.069163 0.087339 0.12182 0.13985 0.1613 0.17534 5 16 0.049193 0.056765 0.0727 0.10341 0.1197 0.13815 0.15172 6 16 0.034483 0.040508 0.051351 0.073566 0.085357 0.098864 0.10694 7 16 0.043381 0.051312 0.066551 0.095141 0.10924 0.12253 0.13347 8 16 0.039411 0.047438 0.062881 0.090989 0.10379 0.11745 0.1262 9 16 0.038461 0.044521 0.056156 0.077728 0.087706 0.099486 0.10232 10 16 0.034368 0.039684 0.047874 0.067032 0.077068 0.088097 0.087638 11 15 NaN NaN NaN NaN NaN NaN NaN 12 15 NaN NaN NaN NaN NaN NaN NaN
S = groupsummary(T,"Var1","monthofyear","std")
S = 12×9 table
monthofyear_Var1 GroupCount std_Var2 std_Var3 std_Var4 std_Var5 std_Var6 std_Var7 std_Var8 ________________ __________ ________ ________ ________ ________ ________ ________ ________ 1 16 NaN NaN NaN NaN NaN NaN NaN 2 16 NaN NaN NaN NaN NaN NaN NaN 3 16 0.018045 0.02027 0.025623 0.034739 0.040814 0.047919 0.053149 4 16 0.018353 0.018561 0.019253 0.027429 0.032353 0.038971 0.042178 5 16 0.03032 0.032032 0.035915 0.04531 0.050774 0.056176 0.062771 6 16 0.027055 0.028075 0.031033 0.036179 0.038964 0.042538 0.04613 7 16 0.042787 0.045058 0.05038 0.060642 0.065259 0.070543 0.074824 8 16 0.021072 0.023252 0.02941 0.039042 0.042936 0.045753 0.048582 9 16 0.022576 0.023834 0.028152 0.034917 0.038047 0.04175 0.044054 10 16 0.026021 0.028996 0.035781 0.046004 0.050124 0.053847 0.050478 11 15 NaN NaN NaN NaN NaN NaN NaN 12 15 NaN NaN NaN NaN NaN NaN NaN

7 Comments
Show 5 older comments Hide 5 older comments

Mathieu NOE
Mathieu NOE on 4 May 2023
@Stephen23
I believe the request (as far as I have understood) is to make one mean and std value for all data that belong to the same month for all years
Stephen23
Stephen23 on 4 May 2023
Edited: Stephen23 on 4 May 2023
Ran in:
Ran in:
@Mathieu NOE: Like this?:
T = readtable('data.csv')
T = 190×8 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 __________ ________ ________ ________ ________ ________ ________ ________ 01.01.2005 NaN NaN NaN NaN NaN NaN NaN 01.02.2005 NaN NaN NaN NaN NaN NaN NaN 01.03.2005 NaN NaN NaN NaN NaN NaN NaN 01.04.2005 0.060516 0.069278 0.086602 0.12014 0.13746 0.15712 0.17569 01.05.2005 0.042994 0.046768 0.057593 0.080507 0.091848 0.1056 0.11985 01.06.2005 0.022818 0.024686 0.032003 0.051454 0.064051 0.071256 0.086593 01.07.2005 0.02403 0.023208 0.027521 0.043028 0.052892 0.055302 0.065115 01.08.2005 0.0416 0.044165 0.052146 0.07018 0.078721 0.088574 0.099769 01.09.2005 0.01913 0.022287 0.027821 0.039807 0.046763 0.057148 0.065082 01.10.2005 NaN NaN NaN NaN NaN NaN NaN 01.11.2005 NaN NaN NaN NaN NaN NaN NaN 01.12.2005 NaN NaN NaN NaN NaN NaN NaN 01.01.2006 NaN NaN NaN NaN NaN NaN NaN 01.02.2006 NaN NaN NaN NaN NaN NaN NaN 01.03.2006 0.037744 0.056849 0.069755 0.10222 0.11358 0.13603 0.14901 01.04.2006 0.11103 0.12077 0.13721 0.17954 0.19551 0.22405 0.24487
U = stack(T,2:8,'NewDataVariableName','Data');
M = groupsummary(U,"Var1","monthofyear","mean","Data")
M = 12×3 table
monthofyear_Var1 GroupCount mean_Data ________________ __________ _________ 1 112 NaN 2 112 NaN 3 112 0.093719 4 112 0.11645 5 112 0.098805 6 112 0.070153 7 112 0.088374 8 112 0.083617 9 112 0.072031 10 112 0.063109 11 105 NaN 12 105 NaN
S = groupsummary(U,"Var1","monthofyear","std","Data")
S = 12×3 table
monthofyear_Var1 GroupCount std_Data ________________ __________ ________ 1 112 NaN 2 112 NaN 3 112 0.049733 4 112 0.050869 5 112 0.058374 6 112 0.044218 7 112 0.066424 8 112 0.04806 9 112 0.040978 10 112 0.04562 11 105 NaN 12 105 NaN
T = join(M,S) % optional
T = 12×4 table
monthofyear_Var1 GroupCount mean_Data std_Data ________________ __________ _________ ________ 1 112 NaN NaN 2 112 NaN NaN 3 112 0.093719 0.049733 4 112 0.11645 0.050869 5 112 0.098805 0.058374 6 112 0.070153 0.044218 7 112 0.088374 0.066424 8 112 0.083617 0.04806 9 112 0.072031 0.040978 10 112 0.063109 0.04562 11 105 NaN NaN 12 105 NaN NaN
Ritesh
Ritesh on 4 May 2023
Hello @Stephen23 earlier you have done was good, please do that.
have to calculate mean and std for all variables sapretly.
Mathieu NOE
Mathieu NOE on 4 May 2023
@Stephen23
thanks for showing me how to use groupsummary !
Ritesh
Ritesh on 8 May 2023
Thanks @Stephen23 for this.
I have a more question, If I have to calculate seasonal mean like
Spring (march - may)
Summer (Jun - Aug)
Autumn (Sep - Nov)
then what will be the code for same table.
Stephen23
Stephen23 on 8 May 2023
Edited: Stephen23 on 8 May 2023
Ran in:
Ran in:
" If I have to calculate seasonal mean like ... then what will be the code for same table."
You would have to define groups that encode those "seasons". Because the "seasons" that you list are simply the year quarters offset by two months, a lazy approach could be something like this:
T = readtable('data.csv')
T = 190×8 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 __________ ________ ________ ________ ________ ________ ________ ________ 01.01.2005 NaN NaN NaN NaN NaN NaN NaN 01.02.2005 NaN NaN NaN NaN NaN NaN NaN 01.03.2005 NaN NaN NaN NaN NaN NaN NaN 01.04.2005 0.060516 0.069278 0.086602 0.12014 0.13746 0.15712 0.17569 01.05.2005 0.042994 0.046768 0.057593 0.080507 0.091848 0.1056 0.11985 01.06.2005 0.022818 0.024686 0.032003 0.051454 0.064051 0.071256 0.086593 01.07.2005 0.02403 0.023208 0.027521 0.043028 0.052892 0.055302 0.065115 01.08.2005 0.0416 0.044165 0.052146 0.07018 0.078721 0.088574 0.099769 01.09.2005 0.01913 0.022287 0.027821 0.039807 0.046763 0.057148 0.065082 01.10.2005 NaN NaN NaN NaN NaN NaN NaN 01.11.2005 NaN NaN NaN NaN NaN NaN NaN 01.12.2005 NaN NaN NaN NaN NaN NaN NaN 01.01.2006 NaN NaN NaN NaN NaN NaN NaN 01.02.2006 NaN NaN NaN NaN NaN NaN NaN 01.03.2006 0.037744 0.056849 0.069755 0.10222 0.11358 0.13603 0.14901 01.04.2006 0.11103 0.12077 0.13721 0.17954 0.19551 0.22405 0.24487
T.season = categorical(quarter(T.Var1-calmonths(2)),1:4,{'spring','summer','autumn','winter'})
T = 190×9 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8 season __________ ________ ________ ________ ________ ________ ________ ________ ______ 01.01.2005 NaN NaN NaN NaN NaN NaN NaN winter 01.02.2005 NaN NaN NaN NaN NaN NaN NaN winter 01.03.2005 NaN NaN NaN NaN NaN NaN NaN spring 01.04.2005 0.060516 0.069278 0.086602 0.12014 0.13746 0.15712 0.17569 spring 01.05.2005 0.042994 0.046768 0.057593 0.080507 0.091848 0.1056 0.11985 spring 01.06.2005 0.022818 0.024686 0.032003 0.051454 0.064051 0.071256 0.086593 summer 01.07.2005 0.02403 0.023208 0.027521 0.043028 0.052892 0.055302 0.065115 summer 01.08.2005 0.0416 0.044165 0.052146 0.07018 0.078721 0.088574 0.099769 summer 01.09.2005 0.01913 0.022287 0.027821 0.039807 0.046763 0.057148 0.065082 autumn 01.10.2005 NaN NaN NaN NaN NaN NaN NaN autumn 01.11.2005 NaN NaN NaN NaN NaN NaN NaN autumn 01.12.2005 NaN NaN NaN NaN NaN NaN NaN winter 01.01.2006 NaN NaN NaN NaN NaN NaN NaN winter 01.02.2006 NaN NaN NaN NaN NaN NaN NaN winter 01.03.2006 0.037744 0.056849 0.069755 0.10222 0.11358 0.13603 0.14901 spring 01.04.2006 0.11103 0.12077 0.13721 0.17954 0.19551 0.22405 0.24487 spring
M = groupsummary(T,"season","mean","Var"+(2:8))
M = 4×9 table
season GroupCount mean_Var2 mean_Var3 mean_Var4 mean_Var5 mean_Var6 mean_Var7 mean_Var8 ______ __________ _________ _________ _________ _________ _________ _________ _________ spring 48 0.05189 0.060181 0.076542 0.10811 0.12448 0.14397 0.15701 summer 48 0.039092 0.04642 0.060261 0.086565 0.099461 0.11295 0.12185 autumn 47 0.036973 0.042762 0.053144 0.073839 0.083838 0.095345 0.096726 winter 47 NaN NaN NaN NaN NaN NaN NaN
S = groupsummary(T,"season","std","Var"+(2:8))
S = 4×9 table
season GroupCount std_Var2 std_Var3 std_Var4 std_Var5 std_Var6 std_Var7 std_Var8 ______ __________ ________ ________ ________ ________ ________ ________ ________ spring 48 0.024088 0.025471 0.028965 0.037758 0.04313 0.049139 0.05436 summer 48 0.031149 0.033003 0.037843 0.046486 0.050314 0.054139 0.057476 autumn 47 0.023351 0.02525 0.030561 0.038574 0.041964 0.045574 0.045931 winter 47 NaN NaN NaN NaN NaN NaN NaN

Sign in to comment.

More Answers (1)

Mathieu NOE
Mathieu NOE on 4 May 2023

0 votes

hello
try this
T = readtable('data.csv');
data = table2array(T(:,2:end));
dates = T(:,1);
month_mean = mean(data,2);
month_std = std(data,0,2);
% create output table
t = array2table([month_mean month_std],'VariableNames',{'Mmean' 'Mstd'});
Tout = [dates t];
will generate an output table :
Tout =
Var1 Mmean Mstd
__________ ________ _________
01.01.2005 NaN NaN
01.02.2005 NaN NaN
01.03.2005 NaN NaN
01.04.2005 0.11526 0.044447
01.05.2005 0.077879 0.0298
01.06.2005 0.050409 0.024809

2 Comments
Show None Hide None

Ritesh
Ritesh on 4 May 2023
@Mathieu NOE I have to find only 12 months mean and std, I am looking for a function like retime, I think groupsummary is good function but I am unable to do that with my table data. Thanks for your effort.
Mathieu NOE
Mathieu NOE on 4 May 2023
this is a new attempt
so all data from same month (al years ) are processed
at the end you get 12 mean and std values . I admit I don't use enough more advanced functions but it works too (retime and timetable are not my everyday topic)
Results :
Tout = 12×3 table
Month number Mmean Mstd
____________ ________ ________
1 NaN NaN
2 NaN NaN
3 0.093719 0.049733
4 0.11645 0.050869
5 0.098805 0.058374
6 0.070153 0.044218
7 0.088374 0.066424
8 0.083617 0.04806
9 0.072031 0.040978
10 0.063109 0.04562
11 NaN NaN
12 NaN NaN
T = readtable('data.csv');
data = table2array(T(:,2:end));
TT = table2timetable(T);
dates = (TT.Var1);
mm = month(dates);
tol = 1e-6;
for ci = 1:12
id_cols = (abs(mm-ci)<tol);
% group same month (all years) data in one vector
data_all = data(id_cols,:);
month_mean(ci,1) = mean(data_all(:),'omitnan');
month_std(ci,1) = std(data_all(:),'omitnan');
end
% create output table
m_list = (1:12)';
Tout = array2table([m_list month_mean month_std],'VariableNames',{'Month number' 'Mmean' 'Mstd'});

Sign in to comment.

Categories

Find more on Timetables in Help Center and File Exchange

Tags

Asked:

Ritesh
Ritesh
on 4 May 2023

Edited:

Stephen23
Stephen23
on 8 May 2023

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!