MATLAB: Using acummarray to average several columns at a time

accummarrayaccummarray multiple collumnsgrpstatshourly averageStatistics and Machine Learning Toolbox

Hello

I have a data array (mat) with the following dimensions: 149016×93

The columns are

year | month | day | hour | data 1 | data 2 | data 3 | and so on until data 89

2001 | 1 | 1 | 0 | random numbers …

… | … | … | … | random numbers …

2017 | 12 | 31 | 23 | random numbers …

The data is random and it is what I want to average.

I found this example (MathWorks example) and it is fine, however I've been strugling in how to run it over column 5 to 93…

[ah,~,ch] = unique(mat(:,2:4),'rows');
hraverage = [ah,accumarray(ch,mat(:,5),[],@nanmean)];

My problem is that I'm not being able to have as an output the 8784×93 array, only an 8784* x 4, I've tried loops but i'm missing something that I am not aware of…

*The dataset has several years of data. I want the hour average for each each day of the year. So it's 366 days * 24hours = 8784

for the sake of example, please feel free to consider a smaller array.

thank you for the attention! will keep digging on this…

sample data in attachment. randomly generated:

4 first collumns are: year, month, day, hour, and columns 5 to 7 are data columns.

the final result should be a 8784×7 file.

Best Answer

There's a simpler way of doing this with groupsummary

I imported the sampledata and made the table with 7 columns: Year,Month,Day,Hour,VarName5,VarName6,VarName7

Then used the following commands to take advantage of binning in groupsummary and of being able to include empty groups:

sampledata.Time = datetime(sampledata.Year,sampledata.Month,sampledata.Day)
result = groupsummary(sampledata,{'Time','Hour'},{'dayofyear','none'},'mean',{'VarName5','VarName6','VarName7'},'IncludeEmptyGroups',1)

Related Solutions

MATLAB: Convert “number of days since” to datetime, given 365-day and 360-day calendars (not Gregorian)

Adding support for a broader range of calendars is something I've been thinking about. For a fixed-length calendar (360-day or 365-day), there are a couple issues to deal with:

1) Where do you put the extra days in the Gregorian calendar? Is day 59 from the model on a leap year 29 Feb or 1 Mar? If 29 Feb, then the extra day falls at the end of the year.

2) How do you want to fill in data for the days that aren't represented in the model data? Fill with previous? average of the previous and next? Leave it as missing? This matters when you want to do more aggregation/statistics on your data (e.g. monthly mean and standard deviation).

3) If you're using fraction-of-year values (second column of var_time_mx), you run into floating point and roundoff issues and needing to correct things to the nearest day.

One approach that I've come up with:

t = readtable('var_time_mx.txt');
d = t.Var1;
dt = datetime(1949,12,1)+calyears(fix(d/365))+caldays(floor(mod(d,365)))+hours((mod(d,365)-floor(mod(d,365)))*24)

This approach includes 29 Feb 1952, but skips 30 Nov 1952 (because 1 Dec is the start of the 365-day year in your definition). This is a bit weird, but a pain to work around and may not be necessary for your use. If it is, you can us the leapyear() function to check whether the dates need to be shifted by one day.

MATLAB: How to convert three dimension daily data into monthly mean

Let me try it step-by-step:

% Load the data
load('prec_aphrodite.mat');
% Reshape the data to 730(day)-by-387(site) array
data_year1 = squeeze(prec_aphrodite(:,1,:))';
data_year2 = squeeze(prec_aphrodite(:,2,:))';
Data = [data_year1; data_year2];
% Assuming the first day is 2001/1/1
Time = datetime(2001,1,1):days(1):datetime(2002,12,31);
Time = Time';
% Create timetable by combining 'Time' and 'Data'
TT = table2timetable([table(Time),array2table(Data)]);
% Calculate monthly mean value for each site
TT2 = retime(TT,'monthly','mean');

The result is:

>> TT2
ans =
24×387 timetable
     Time        Data1      Data2 ...
  __________    _______    _______
  2001/01/01    0.93232     1.0593 ...
  2001/02/01     2.3482     2.4687 ...
  2001/03/01     2.3196     2.1202 ...
  2001/04/01    0.75275    0.63212 ...
  ...           ...        ...

Related Question