Crosstab with Sum instead of count

Question

JE101 2017-3-15

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/330039-crosstab-with-sum-instead-of-count

评论： JE101 2017-3-15

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Guillaume 2017-3-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/330039-crosstab-with-sum-instead-of-count#answer_258874

编辑：Guillaume 2017-3-15

在 MATLAB Online 中打开

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

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.

请先登录，再进行评论。