Extract one row per group based on column value from table.

4 views (last 30 days)
Namrata Goswami
Namrata Goswami on 2 Dec 2020
Commented: Namrata Goswami on 2 Dec 2020
I have a table with multiple rows per ID. I want to extract one row per ID where the date is max/latest.
input table:
ID Date Value
1 12-Nov-2020 200
1 15-Nov-2020 240
2 12-Nov-2020 270
2 13-Nov-2020 290
2 14-Nov-2020 270
2 19-Nov-2020 220
3 23-Nov-2020 210
expected output:
ID Date Value
1 15-Nov-2020 240
2 19-Nov-2020 220
3 23-Nov-2020 210
I'm trying to use findgroups and splitapply but it's not working out.

Answers (1)

Ameer Hamza
Ameer Hamza on 2 Dec 2020
Try something like this
[grps, vals] = findgroups(T.ID);
T_new = splitapply(@(ids, dts, vals) {ids(max(dts)==dts) max(dts) vals(max(dts)==dts)}, T, grps);
T_new = cell2table(T_new, 'VariableNames', T.Properties.VariableNames)
For testing above code, I created a sample table like this
C = {
1 '12-Nov-2020' 200
1 '15-Nov-2020' 240
2 '12-Nov-2020' 270
2 '13-Nov-2020' 290
2 '14-Nov-2020' 270
2 '19-Nov-2020' 220
3 '23-Nov-2020' 210
};
T = cell2table(C, 'VariableNames', {'ID', 'Date', 'Value'});
T.Date = datetime(T.Date, 'InputFormat', 'dd-MMM-yyyy');
  1 Comment
Namrata Goswami
Namrata Goswami on 2 Dec 2020
Thank you for the solution.
Though I could actually get the expected outcome using 'groupfilter' as:
outputTable = groupfilter(inputTable,'ID', @(x) x==max(x),'Date')

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by