Crosstab with Sum instead of count

I have a table with multiple columns. All but the last column are categories and the last column is a numerical column.

I want to create a table/data array where the rows correspond to the unique categories of column 1, the columns correspond to the unique categories of column 2, and the data is a summation of the numerical column where the unique categories of column 1 and column 2 occur. Basically, what I want is similar to a crosstab, but instead of the data being a count, I want it to be a sum of column 3.

I've got a few work-around solutions, but they are all a bit clunky, so I'm wondering if there is a quick/easy way of doing this.

Thanks in advance.

Answer 1

[Guillaume]

You probably can use the newish findgroups and splitapply for that, but I'm still old school and get on better with accumarray (which is probably faster anyway):

[rownames, ~, rowid] = unique(yourtable.column1);
[colnames, ~, colid] = unique(yourtable.column2);
crosstab = array2table(accumarray([rowid, colid], yourtable.column3), ...
                       'VariableNames', categories(colnames), ...  assumes categories are valid variable names
                       'RowNames', categories(rownames))

If the categories names are not valid variable names / row names then, of course, you can't use them as names for rows and columns (unless you pass them through matlab.lang.makeValidName), but I'm not sure what you would want to do in that case to label rows and columns.

The default of accumarray is to compute the sum, exactly as you want. If you wanted the count, the accumarray call would become:

                       accumarray([rowid, colid], yourtable.column3, [], @numel)

[JE101]

Hi Guillaume

Thanks for that. Does exactly what I want and is much neater than my workaround! I haven't seen accumarray before, but it looks like a useful function to know.