Using accumarray or a similar method for tables

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

Answer 1

[Steven Lord]

This wasn't an option in the release the original poster was using (the two functions required were released in release R2016b, which came out a few months after this question was asked) but today I suggest turning your table into a timetable using table2timetable then call retime on the resulting timetable.

Answer 2

[Sean de Wolski]

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

Answer 3

[Patrick]

Since 2018 you can just use groupsummary on the datetime column to get the minute average

% Make a dataset
times = [datetime(2020,01,01,00,00,00):seconds(1):datetime(2020,01,01,01,00,00)].';
data1 = rand(length(times),1);
data2 = rand(length(times),1);
tableData = table(times,data1,data2);
groupedTable = groupsummary(tableData,"times","minute","mean")

groupedTable = 61x4 table
        minute_times        GroupCount    mean_data1    mean_data2
    ____________________    __________    __________    __________

    01-Jan-2020 00:00:00        60         0.47131       0.47329  
    01-Jan-2020 00:01:00        60         0.54204       0.55522  
    01-Jan-2020 00:02:00        60         0.53989       0.50714  
    01-Jan-2020 00:03:00        60         0.47104       0.47719  
    01-Jan-2020 00:04:00        60         0.56149       0.49005  
    01-Jan-2020 00:05:00        60         0.49992       0.52101  
    01-Jan-2020 00:06:00        60         0.46253       0.47699  
    01-Jan-2020 00:07:00        60         0.52482       0.44055  
    01-Jan-2020 00:08:00        60         0.53046        0.4562  
    01-Jan-2020 00:09:00        60         0.51016        0.5379  
    01-Jan-2020 00:10:00        60         0.55995       0.52767  
    01-Jan-2020 00:11:00        60         0.47306       0.49673  
    01-Jan-2020 00:12:00        60          0.4996       0.48779  
    01-Jan-2020 00:13:00        60         0.52775       0.47288  
    01-Jan-2020 00:14:00        60         0.52138        0.4711  
    01-Jan-2020 00:15:00        60         0.55344       0.48471  

To do this with arbitrary time intervals, you can use histcounts on your table.datetime column to correctly bin the times, add the bin values to the table, and use groupsummary on the bin column

% Bin times however you want
binWidth = seconds(120);
edges = datetime(2020,01,01,00,00,00):binWidth:datetime(2020,01,01,01,00,00);
[~,~,bins] = histcounts(tableData.times,edges);

% Add the times to the table
tableData = addvars(tableData,bins);

% Group by bins
groupedTable = groupsummary(tableData,"bins","mean")

groupedTable = 30x5 table
    bins    GroupCount         mean_times         mean_data1    mean_data2
    ____    __________    ____________________    __________    __________

      1        120        01-Jan-2020 00:00:59     0.50668       0.51425  
      2        120        01-Jan-2020 00:02:59     0.50546       0.49216  
      3        120        01-Jan-2020 00:04:59      0.5307       0.50553  
      4        120        01-Jan-2020 00:06:59     0.49367       0.45877  
      5        120        01-Jan-2020 00:08:59     0.52031       0.49705  
      6        120        01-Jan-2020 00:10:59     0.51651        0.5122  
      7        120        01-Jan-2020 00:12:59     0.51368       0.48034  
      8        120        01-Jan-2020 00:14:59     0.53741        0.4779  
      9        120        01-Jan-2020 00:16:59     0.49994       0.47842  
     10        120        01-Jan-2020 00:18:59     0.51361       0.45691  
     11        120        01-Jan-2020 00:20:59     0.49402       0.53125  
     12        120        01-Jan-2020 00:22:59     0.50632       0.43106  
     13        120        01-Jan-2020 00:24:59     0.48716       0.47295  
     14        120        01-Jan-2020 00:26:59     0.52234        0.4835  
     15        120        01-Jan-2020 00:28:59     0.50635       0.51349  
     16        120        01-Jan-2020 00:30:59     0.53371        0.4745