# Using accumarray or a similar method for tables

14 views (last 30 days)
D on 25 Jul 2016
Answered: Sean de Wolski on 25 Jul 2016
Hello,
I have a table with the 1st column being numeric date/time information, and the rest of the data columns (dozens of them) being numeric. The time interval for each row of data is approx. 1 second, but I would like to calculate the minute-averaged value. I’ve converted my table to an array and have mostly followed the help here: https://www.mathworks.com/matlabcentral/answers/81203-convert-10-minute-average-to-hourly, however the “accumarray” function doesn’t work for a matrix. Instead, I have a “for” loop for each column, where accumarray operates on each column. I would like to eliminate this “for” loop because it can be slow when I have days of data to process. Can someone please tell me if there is another method to do this, or if there is a command for tables that would do this automatically? (I thought about using a table grouping variable, however I think this would just group my data into 60 rows – 1 for each minute.)
Here is my code that I have, and thank you in advance for your help.
data = table2array(MyData); % table to array
date1 = datevec(data(:,1)); % date/time matrix
[a,~,c] = unique(date1(:,1:5),'rows'); % returns each unique row of date1 looking only up to minutes
out(:,1:6)=[a,zeros(size(a,1),1)]; % first 5 columns are yr, mnth, day, hr, min from unique function above, 6th column is sec (0s)
for i=1:width(MyData)-1 % loop over each data column
out(:,i+6) = accumarray(c,data(:,i+1),[],@mean); % ith col is average value over minute
end

Sean de Wolski on 25 Jul 2016
% fake c and data
c = [1; 2; 2; 1; 3]
data = repmat(1:6,5,1)
% engine
sz = size(data)
accumarray([repmat(c,sz(2),1), repelem((1:sz(2))',sz(1),1)],data(:),[],@mean)
Take advantage of the fact that subs can be two dimensional.