CODEWITHSUNDEEP
rdata.tablelubridate
iboboboru
iboboboru

Reputation: 1102

select most recent rows for duplicate groups with data.table in R

I have a dataset with duplicated records, that can be determined by a group. I want to flag anything after the earliest record (by date) as a duplicate (or first row.id if the dates are the same).

library(data.table)
library(lubridate)

groupA <- c("A","B","C","A","B","C","D","E","A")
groupB <-  c("y","n","n","y","y","n","y","n","y")
#ymd format
date <- c("2017-04-01","2017-02-01","2017-03-01","2017-01-01","2017-05-01","2017-03-01","2017-07-01","2017-08-01","2017-09-01")

mydata <- data.table(groupA, groupB, date=ymd(date))
check.dups <- mydata[,.("count"=.N),by=.(groupA,groupB)]
#These are the duplicate keys
check.dups <- check.dups[count>1,]

#Create dupliate.flag on most recent example for duplicates
keycols <- c("groupA","groupB")
setkeyv(mydata, keycols)
setkeyv(check.dups, keycols)

I am stuck on the logic for the selecting rows after the earliest date/first row.id for creation of duplicate flag. 

#Select rows for duplicate flag
mydata[check.dups,][date > min(date),dup.flag := ]

Any help much appreciated.

Expected Output:

A flag due to dates, C flagged because of row.id (dates are the same)

 groupA groupB date       dup.flag
 A      y      2017-04-01  y
 B      n      2017-02-01  NA
 C      n      2017-03-01  NA
 A      y      2017-01-01  NA
 B      y      2017-05-01  NA
 C      n      2017-03-01  y
 D      y      2017-07-01  NA
 E      n      2017-08-01  NA
 A      y      2017-09-01  y

Upvotes: 0

Views: 151

Answers (1)

Uwe
Uwe

Reputation: 42544

Please, try the duplicated() function from the data.table package:

setkey(mydata, groupA, groupB, date)
mydata[, dup := duplicated(mydata, by = c("groupA", "groupB"))]

mydata
#   groupA groupB       date   dup
#1:      A      y 2017-01-01 FALSE
#2:      A      y 2017-04-01  TRUE
#3:      A      y 2017-09-01  TRUE
#4:      B      n 2017-02-01 FALSE
#5:      B      y 2017-05-01 FALSE
#6:      C      n 2017-03-01 FALSE
#7:      C      n 2017-03-01  TRUE
#8:      D      y 2017-07-01 FALSE
#9:      E      n 2017-08-01 FALSE

Upvotes: 2

Related Questions