Crosstab with Sum instead of count

4 次查看(过去 30 天)
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.

采纳的回答

Guillaume
Guillaume 2017-3-15
编辑:Guillaume 2017-3-15
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)
  1 个评论
JE101
JE101 2017-3-15
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.

请先登录,再进行评论。

更多回答(0 个)

类别

Help CenterFile Exchange 中查找有关 Data Type Conversion 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by