Extract one row per group based on column value from table.
7 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
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 Kommentare
Antworten (1)
Ameer Hamza
am 2 Dez. 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 Kommentar
Siehe auch
Kategorien
Mehr zu String Parsing finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!