Get the complete row with maximum column value grouped by other Column

Question

SaraJr 2016-8-29

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/301253-get-the-complete-row-with-maximum-column-value-grouped-by-other-column

回答： Stephen Jue 2016-9-1

Hello .. I have a table that I want to get the maximum value of D grouped by A & C . but also I want to get B that related to the Max value of D.

This is an example of the table

      |A|   |B|   |C|   |D|
       1     a     x     2  -->
       1     b     x     1
       1     c     y     6  -->
       1     a     y     5
       2     b     x     1
       2     c     x     6  -->
       2     a     y     5  -->
       2     b     y     1

I have tried grpstats

grpstats(Tbale, {'A','C'}, {'max'} ,'DataVars',{'D'});

but It I couldn't find a way to get the B value

The expect result is

      |A|   |B|   |C|   |D|
       1     a     x     2  
       1     c     y     6  
       2     c     x     6  
       2     a     y     5

2 个评论
显示无隐藏无

Azzi Abdelmalek 2016-8-29

What do you mean by the maximum value of D grouped by A & C? Post the expected result

SaraJr 2016-8-29

I have marked the expected results in the example(-->)and added it at the end of the question ..

请先登录，再进行评论。

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

Answer 1

Stephen Jue 2016-9-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/301253-get-the-complete-row-with-maximum-column-value-grouped-by-other-column#answer_233363

在 MATLAB Online 中打开

If I understand correctly, you want to filter your table such that it only shows the rows with max values of "D" grouped by "A" and "C".

I don't think that "grpstats" can retrieve the whole table, but you can take a more manual approach by creating your own logical filters and making a new table by taking the max of each set of rows. Here is how I did it:

% Reproduce your example table
A = [ones(4,1); 2 * ones(4,1)];
B = ('abcabcab')';
C = ('xxyyxxyy')';
D = [2,1,6,5,1,6,5,1]';
t = table(A,B,C,D);
colAValues = unique(t.A); % All unique values of column A
colCValues = unique(t.C); % All unique values of column C
tableFilter = @(a, c) (t.A == a) & (t.C == c); % Group by A and C
columnCombos = combvec(colCValues', colAValues'); % All combinations of A and C
logicalFilters = arrayfun(tableFilter, columnCombos(2,:), columnCombos(1,:), 'UniformOutput', false);
tNew = table;
for i = 1:length(logicalFilters)
    a = t(logicalFilters{i}, :);
    tNew(i, :) = a(a.D == max(a.D), :);
end