Define the variable to drop among ID duplicates in data.table

user_14029910 0
Viewed 66

I have the following dataset

DT <- data.table(
      id = c(1,2,3,4,4,5,6,6,7),
      date  = c("2013-11-22","2017-01-24","2020-02-10","2011-01-03"
               ,"2011-01-03","2012-04-03","2010-09-03","2010-09-03"
               ,"2010-05-03"),                         
      status = c("Never","Current","Former",NA,"Former"
                , NA,"Never","Former","Current")
     )

I want to create a unique id and remove the duplicates.

  • The id row that should be kept depends on the status.
  • If staus has NA and a nonNA, I want to keep the nonNA observation.
  • If status has Former and Never, I want to keep Former observation.

Sample output below:

    id  date      status 
1:  1 2013-11-22   Never 
2:  2 2017-01-24 Current 
3:  3 2020-02-10  Former 
4:  4 2011-01-03  Former 
5:  5 2012-04-03    <NA> 
6:  6 2010-09-03  Former 
7:  7 2010-05-03 Current

The original dataset has more rows and columns and a data.table function would be time efficient. Also some id occur more than one time. I tried it before to keep the id with the latest date. But than, I have too many 'NA' that had another status entry for an earlier date.

How can I define for identical id which status should be kept?

data.table duplicates r
1 Answers

We could create a factor with status specifying the levels, use that to order along with 'id' and get the unique by 'id'

library(data.table)
unique(DT[order(id, ordered(status, c("Former", "Current", "Never")))], by = 'id')
user_37322710
Related