Grouping the rows is trivially achieved with varfun in just one line: newT = varfun(@mean, T, 'GroupingVariables', 1)
The tricky bit is indeed the grouping of the columns as it's not something that's really supported by tables. Probably the easiest way is to use a custom function with rowfun. This can do the row grouping at the same time. Something like: function varargout = groupingfunction(columngroups, columns)
columngroups = repmat(columngroups, size(columns, 1), 1);
varargout = num2cell(accumarray(columngroups(:), columns(:), [], @mean));
end
You can then use that with rowfun:
columgroups = [1 1 1 2 2 3 3 3 4 4 4 4 5 6 6 6];
columnames = {'a', 'b', 'c', 'd', 'e', 'f'};
newT = rowfun(@(cols) groupingfunction(columngroups, cols), T, 'GroupingVariables', 1,'SeparateInputs', false, 'NumOutputs', numel(unique(columngroups)), 'OutputVariableNames', columnnames);
A possible way of generating columngroups and columnnames, assuming they're all named prefix_number
prefixes = regexp(T.Properties.VariableNames(2:end), '.*?(?=(_\d+)?$)', 'match', 'once');
[columnnames, ~, columngroups] = unique(prefixes);
edit, now that you've posted some demo data: The above works without issue on your demo data, as long as you transpose the columngroups vector returned by unique:
T = readtable('test.csv');
prefixes = regexp(T.Properties.VariableNames(2:end), '.*?(?=(_\d+)?$)', 'match', 'once');
[columnnames, ~, columngroups] = unique(prefixes);
newT = rowfun(@(cols) groupingfunction(columngroups.', cols), T, 'GroupingVariables', 1,'SeparateInputs', false, 'NumOutputs', numel(unique(columngroups)), 'OutputVariableNames', columnnames);
Best Answer