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

2 次查看（过去 30 天）
SaraJr
SaraJr 2016-8-29
回答： 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
Azzi Abdelmalek 2016-8-29
What do you mean by the maximum value of D grouped by A & C? Post the expected result
SaraJr
SaraJr 2016-8-29
I have marked the expected results in the example(-->)and added it at the end of the question ..

请先登录，再进行评论。

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

回答（1 个）

Stephen Jue
Stephen Jue 2016-9-1
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
Once this code runs, "tNew" should contain the rows in your expected result.

类别

在 Help Center 和 File Exchange 中查找有关 Tables 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by