Find certain row out of many rows with the same id
1 view (last 30 days)
Show older comments
Triple G
on 22 Jun 2020
Answered: Walter Roberson
on 23 Jun 2020
I have a matrix whose each row consists of an id column and some other columns with other features of each object. One of the characteristics is a date. Now, some rows of the matrix have the same id and I want to keep only the one with the most recent date. For example, in the table above, the id is in the third column, so, from the second and third row I want to keep only the third, as it has the most recent date. How could I do that in a matrix with many subsets of rows with the same id?
490 673 9987 {'2015-09-10 00:00:00'} 58
491 110556 10233 {'2010-02-22 00:00:00'} 70
492 110556 10233 {'2011-02-22 00:00:00'} 65
0 Comments
Accepted Answer
Walter Roberson
on 23 Jun 2020
One approach:
sortrows() on the ID column, and the date column in descending order. unique() the ID column with 'stable' and take the second output. Use that to index the sortrows output to get just the first (newest because descending order) entry for each ID.
If necessary re-sort on the first column.
Or...
findgroups() on the ID, and splitapply() specifying a function that will receive the variable entries that are associated with a single ID. The function would datetime() and max() to get the index of the maximum and return that entry from the variables.
0 Comments
More Answers (0)
See Also
Categories
Find more on Matrices and Arrays in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!