Calculate avergae values per hour, day, month and year

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.

Answer 1

[Andrei Bobrov]

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)];

[Robert]

Thank you for your help Andrei, very appreciated!

I am reading the description of accumarray, and it is not exactly what I need, as I am looking to obtain average values.

When changing accumarray for nanmean I get the following error:

>> out_hour = [ah,nanmean(ch,data(:,5))];

Error using sum

Dimension argument must be a positive integer scalar within indexing range.

Error in nanmean (line 28)

    n = sum(~nans,dim);

Your comments are welcome!

[Walter Roberson]

accumarray can take an optional 4th parameter which is a function handle such as @mean

[Kelly Kearney]

You can also cut out the intermediate step of getting indices of unique values via my aggregate function (which is really just a wrapper around accumarray, with some indexing tricks to reduce computation time for non-vector input).

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];

[unqyr, avgyr] = aggregate(data(:,1),   data(:,end), @nanmean);
[unqmn, avgmn] = aggregate(data(:,1:2), data(:,end), @nanmean);
[unqdy, avgdy] = aggregate(data(:,1:3), data(:,end), @nanmean);
[unqhr, avghr] = aggregate(data(:,1:4), data(:,end), @nanmean);

[Robert]

Thank you Walter, by adding that modification to Andrei's script my problem is solved. It is interesting that results are the same if I use @mean or @nanmean when using the modified data "D":

 D = ...
  [1994 3 7 4 NaN
 1994 3 7 4 NaN
 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]

 Results:
 1994  3  7  4  NaN
 1994  3  7  5  25.7460000000000
 1994  3  7  6  25.3860000000000
 1994  3  7  7  26.7460000000000
 1994  3  7  8  22.6860000000000
 2016  10  24  0  28.2160000000000
 2016  10  24  1  25.6860000000000

Thanks!

[Andrei Bobrov]

Thank you Walter!

I'm corrected the my answer.

[Robert]

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!

[Devendra Pal]

%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

[Steven Lord]

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.

[Lucas Guimaraes]

Hello,

thank you for that. Helped me too in my case.

But help me again, please haha. If I have to calculate the standard deviations of these data. How do I do?

thank you!

Lucas

Answer 2

[Sean de Wolski]

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.

[Robert]

Thank you Sean, very appreciated!

I modified D a little (added NaN's at the begining):

 D = ...
  [1994 3 7 4 NaN
 1994 3 7 4 NaN
 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];

...and run your script, with @mean and @nanmean, and I found same results, NaN's data is removed. Do you know how this could be solved?

 Here is what I get (average from two first rows is not there):
 '07-Mar-1994 05:00:00'  25.7460000000000
 '07-Mar-1994 06:00:00'  25.3860000000000
 '07-Mar-1994 07:00:00'  26.7460000000000
 '07-Mar-1994 08:00:00'  22.6860000000000
 '24-Oct-2016 00:00:00'  28.2160000000000
 '24-Oct-2016 01:00:00'  25.6860000000000

Your suggestions are welcome!

[Sean de Wolski]

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')))

[Robert]

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!

[Robert]

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!