How to sum over grouped data in a table

Suppose I have a table like this toy example:

>> groups = { 'a'; 'a'; 'b'; 'b'; 'c'; 'c'};
>> values = [1;2;3;4;5;6];
>> exampletable = table(groups, values);

I want another column in the table that finds the sum of the values for each group. How can I do this?

Answer 1

[MathWorks Support Team]

For MATLAB R2018b and later:

If you only require a summary table containing the sum for each group, use "groupsummary".

>> G = groupsummary(exampletable,'groups','sum')

G =

  3×3 table

    groups    GroupCount    sum_values
    ______    __________    __________

     'a'          2              3    
     'b'          2              7    
     'c'          2             11    

Use "grouptransform" to both perform the sum calculation by group and expand the result back to your original "exampletable".

>> exampletable = grouptransform(exampletable,'groups',@sum,'ReplaceValues',false)

exampletable =

  6×3 table

    groups    values    fun_values
    ______    ______    __________

     'a'        1            3    
     'a'        2            3    
     'b'        3            7    
     'b'        4            7    
     'c'        5           11    
     'c'        6           11    

The documentation pages for "groupsummary" and "grouptransform" are below.

https://www.mathworks.com/help/matlab/ref/double.groupsummary.html

https://www.mathworks.com/help/matlab/ref/double.grouptransform.html

For MATLAB R2018a and earlier:

You can use a for loop to calculate the sums in a new, preallocated vector. Then, append this vector to the end of your original table.

y = zeros(size(groups));

for i = 1:length(groups)
    y(i) = sum(values(ismember(groups,groups(i))));
end

exampletable.sum_values = y;

Answer 2

[Peter Perkins]

It seems more likely that you would want the sum for each group in a table with one row for each group. There are several simple ways to do that, including findgroups/splitapply, groupsummary, and this version using varfun:

>> groups = { 'a'; 'a'; 'b'; 'b'; 'c'; 'c'};
>> values = [1;2;3;4;5;6];
>> exampletable = table(groups, values);
>> exampletable.groups = categorical(exampletable.groups) % convert to categorical IN the table
>> sums = varfun(@sum,exampletable,'GroupingVariable','groups')
sums =
  3×3 table
    groups    GroupCount    sum_values
    ______    __________    __________
      a           2              3    
      b           2              7    
      c           2             11     

If broadcasting the summed values out to each row of the original table is the goal, findgroups does the trick.

>> igroup = findgroups(exampletable.groups)
>> exampletable.sums = sums.sum_values(igroup)

All of this is in base MATLAB.