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

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.

Answer 1

[Ameer Hamza]

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');

[Namrata Goswami]

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')