Crosstab with Sum instead of count
3 次查看(过去 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.
0 个评论
采纳的回答
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)
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!