MATLAB: Calculate avergae values per hour, day, month and year

average multi column data hour day month 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.

Best 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)];
Related Question