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.
댓글 수: 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에서 Tables에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!