Extract one row per group based on column value from table.
2 次查看(过去 30 天)
显示 更早的评论
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.
0 个评论
回答(1 个)
Ameer Hamza
2020-12-2
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');
另请参阅
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!