MATLAB Answers

Robert
1

Calculate avergae values per hour, day, month and year

Asked by Robert
on 20 Dec 2016
Latest activity Commented on by Steven Lord
on 14 Jun 2019
I have ~23 years of hourly data in a large matrix (5 columns and over 5 millions rows), like this:
YEAR / MONTH / DAY / HOUR / DATA
1994 3 7 4 25.786
1994 3 7 4 25.686
1994 3 7 5 25.746
1994 3 7 6 25.686
1994 3 7 6 24.786
1994 3 7 6 25.686
1994 3 7 7 26.746
1994 3 7 8 22.686
....
2016 10 24 0 27.686
2016 10 24 0 28.746
2016 10 24 1 25.686
Where...
YEAR= 1994:1:2016 (with leap and regular years)
MONTH= 1:12 (during leap and regular years)
DAY= 1:31 (with 28-31 days depending on leap and regular years)
HOUR= 0-23 (0=time between midnight and 1am)
Unfortunately series doesn't start at MONTH 1, DAY 1, HOUR 0, thinking in loop here. Also HOUR values do not have the same time step (some days can have 3 values other days can have 48 values, etc).
Any suggestions on how to obtain the data average at: 1) each hour (per day per month per year), 2) each day (per month per year), and 3) each month (per year).
I am also interested on how to calculate the data average per: 1) year (23 years), 2) month (12 months), 3) day (366 days), and 4) hour (24 hours).
Thank you for your suggestions.

  0 Comments

Sign in to comment.

2 Answers

Answer by Andrei Bobrov
on 20 Dec 2016
Edited by Andrei Bobrov
on 21 Dec 2016
 Accepted Answer

Let data - your data.
%avergae values per hour
[ah,~,ch] = unique(data(:,1:4),'rows');
out_hour = [ah,accumarray(ch,data(:,5),[],@nanmean)];
%avergae values per day
[ad,~,cd] = unique(data(:,1:3),'rows');
out_day = [ad,accumarray(cd,data(:,5),[],@nanmean)];
%avergae values per month
[am,~,cm] = unique(data(:,1:2),'rows');
out_month = [am,accumarray(cm,data(:,5),[],@nanmean)];
%avergae values per year
[ay,~,cy] = unique(data(:,1:2),'rows');
out_year = [ay,accumarray(cy,data(:,5),[],@nanmean)];

  8 Comments

Hi Kelly, Thank you for your help on this question, wondering if I could bother you asking if it is possible to obtain the mean of multiple columns as a single result and not as individual cells per column?, something like this...
%I added another column to your original 'data'
data1 = [...
1994 3 7 4 25.786 12
1994 3 7 4 25.686 12
1994 3 7 5 25.746 11
1994 3 7 6 25.686 10
1994 3 7 6 24.786 11
1994 3 7 6 25.686 14
1994 3 7 7 26.746 15
1994 3 7 8 22.686 17
2016 10 24 0 27.686 1
2016 10 24 0 28.746 2
2016 10 24 1 25.686 3];
[unqyr, avgyr] = aggregate(data1(:,1), data1(:,5:end), @nanmean);
and have as result the mean (nanmean) as following...?
unqyr =
1994
2016
avgyr =
19.5872 %as the result of the nanmean of data1(1:8,5:6)
14.6863 %as the result of the nanmean of data1(9:11,5:6)
...and similar for month (avgmn), day (avgdy), and hour (avghr)?
Very appreciated!
%avergae values per hour
[ah,~,ch] = unique(data(:,1:4),'rows');
out_hour = [ah,accumarray(ch,data(:,5),[],@nanmean)];
%avergae values per day
[ad,~,cd] = unique(data(:,1:3),'rows');
out_day = [ad,accumarray(cd,data(:,5),[],@nanmean)];
%avergae values per month
[am,~,cm] = unique(data(:,1:2),'rows');
out_month = [am,accumarray(cm,data(:,5),[],@nanmean)];
%avergae values per year
[ay,~,cy] = unique(data(:,1:2),'rows');
out_year = [ay,accumarray(cy,data(:,5),[],@nanmean)];
I tried to used the same code to calculate the daily and hourly averages of my long term data which is yyyy,MM,DD,HH,MM,SS formate in different csv files. I ran initially code and stored all the data in different coloumn vector and then made the table and trying to avobe code but seems to be not working. Could you guide me how to i would get rid from my problem. It would be really appreciated.
Thanks!
Dev
If you have your data in a table or a timetable I recommend using Sean de Wolski's approach below. If you have a table you'll need to convert it into a timetable first using the table2timetable function as retime is only defined for timetable arrays.

Sign in to comment.


Answer by Sean de Wolski
on 20 Dec 2016
Edited by Sean de Wolski
on 20 Dec 2016

This is the exact purpose of retime with the timetable class in R2016b.
% Your data
D = ...
[1994 3 7 4 25.786
1994 3 7 4 25.686
1994 3 7 5 25.746
1994 3 7 6 25.686
1994 3 7 6 24.786
1994 3 7 6 25.686
1994 3 7 7 26.746
1994 3 7 8 22.686
2016 10 24 0 27.686
2016 10 24 0 28.746
2016 10 24 1 25.686];
% Make Datetime
dt = datetime(D(:,1),D(:,2),D(:,3),D(:,4),0,0);
% Make timetable
tt = timetable(dt,D(:,end),'VariableNames',{'Data'})
%%Retiming
% Monthly
rmmissing(retime(tt,'monthly',@mean))
% Yearly
rmmissing(retime(tt,'yearly',@mean))
You can pass whatever function you want in instead of @mean.

  4 Comments

Show 1 older comment
Using omitnan with mean works fine for me... Could you provide what expect as the output for the above data?
% Monthly
rmmissing(retime(tt,'monthly',@(x)mean(x,'omitnan')))
% Yearly
rmmissing(retime(tt,'yearly',@(x)mean(x,'omitnan')))
Thank you Sean for your help!
Wondering if it is possible to calculate the mean of multiple columns using retime (assuming D has multiple columns with data)? Similar question that I asked Kelly above.
Very appreciated!
Hi Sean,
Following your last suggestion, I would like to ask you another question related to timetable if that is OK with you.
I am running this…
% Make Datetime and timetable
T1 = timetable(datetime(DATA(:,1), 'ConvertFrom', 'datenum'),DATA(:,2:4),'VariableNames',{'Data'});
% Then calculating daily mean
daily_mean=rmmissing(retime(T1,'daily',@(x)nanmean(x(:))));
The above works great!
However, this only helps me when I need to calculate the mean of few DATA columns. To better understand my problem, DATA is just a matrix with over 200,000 rows, 1st column (serial date number) and column 2 to end (numerical double values), for variables (and sub-variables), like this example…
Variable 1 = DATA Column 2-3 (Var1_01 Var1_02)
Variable 2 = DATA Column 4:8 (Var2_01 Var2_02 Var2_03 Var2_04)
Variable 3 = DATA Column 9-11 (Var3_01 Var3_02 Var3_03)
Variable 4 = DATA Column 12-18 (Var4_01 Var4_02 Var4_03 Var4_04 Var4_05 Var4_06 Var4_07)
…As I do have over 100 variables with different # of sub-variables (over 1000 columns), I created (from T1) a 1×52 cell array that contains ONLY the names of UNIQUE variables (like this … Columns 1 through 5 … 'Var1' 'Var2' 'Var3' 'Var4 'Var5' ….) associated to columns (2 to 6 in T1, as column 1 is Time), which could help to sort in the loop the search for specific Variable 1, and then run the mean, save it in a matrix/table, and then repeat the search for the following columns with Variable 2, and so on...
My question is how I can generate that loop to calculate the mean of each variable (using the above daily mean timetable solution).
Thank you for your help and comments!

Sign in to comment.