Asked by Robert
on 20 Dec 2016

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

Robert
on 8 Mar 2017

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
on 13 Jun 2019

%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
on 14 Jun 2019

Sign in to comment.

Answer by Sean de Wolski
on 20 Dec 2016

Edited by Sean de Wolski
on 20 Dec 2016

% 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.

Sean de Wolski
on 21 Dec 2016

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
on 8 Mar 2017

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
on 21 Mar 2017

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.

Opportunities for recent engineering grads.

Apply Today
## 0 Comments

Sign in to comment.