Average values after every n coloumns

Hi,

Sorry I am quite new to Matlab and I hope to be clear in the explanation.

I have a Matrix C = 174688 X 29225

Each row represent an observation taken from a certain location.

Each coloumn contains 3-hourly temperature data. Thus I have around 10 years (1 year = 2920 tri-hourly values) of tri-hourly temperature observations from 174688 locations.

I would like to create a new matrix containing, for each location, 2920 tri-hourly temperature values representing the average for the whole 10-years time period.

Thus, for instance, I need to average the 1st, the 2921th, the 5841th...., the 26281th temperature data for averaging the first tri-hourly time-step.

Thanks for your help.

Best

Answer 1

[Voss]

I think something like this would be a pretty straightforward way to do what you want:

[N_loc,N_obs] = size(C);
N_obs_avg = 2920;
obs_idx = (0:9)*N_obs_avg;
C_avg = zeros(N_loc,N_obs_avg);
for i = 1:N_obs_avg
    C_avg(:,i) = mean(C(:,i+obs_idx),2);
end

Note that this method ignores the last 25 columns of C, since you stated the first tri-hourly time-step should only go up to column 26281 (rather than column 29201). If you want to include all the data (i.e., even those tri-hourly time-steps which actually have 11 years of data - the first 25), you could do this:

[N_loc,N_obs] = size(C);
N_obs_avg = 2920;
C_avg = zeros(N_loc,N_obs_avg);
for i = 1:N_obs_avg
    C_avg(:,i) = mean(C(:,i:N_obs_avg:end),2);
end

[Giorgio Sperandio]

Thank you very much.

The code works perfecly.

And also thanks for the variant code including all the temperature data.

Best

Answer 2

[Adam Danz]

You probably already have a vector of timestamps that define each column of your data. The first line in the demo below produces a vector of datetime values for each column of your data. If you do not have such a vector, you should make one following the example I provided. The second line produces some fake data (much smaller than yours).

The demo uses datevec to extract the year/month/day/hour/minute/second values from the vector of timestamps. It then uses unique to group the month/day/hour/minute values into 29225 groups.

grpstats is used to compute the mean of the data for each group. Each column of the output avg is defined by the variable groupDefs.

% Create fake data
timestamps = datetime(1999,01,01) + minutes(cumsum(3.*ones(1,29225))); 
data = rand(100, 29225); 

% Get year/month/day/hour/min/sec data 
dateMat = datevec(timestamps(:));

%group by same month/day/hour/minute
[groupDefs, groupID] = unique(dateMat(:,2:5),'stable', 'rows');

% group means (requires stats & machine learning toolbox)
avg = grpstats(data.',groupID,'mean')