CODEWITHSUNDEEP
rdata.tablelubridate
Nneka
Nneka

Reputation: 1850

data.table - filtering rows grouped by ID in R

So i have data: 

# example data
ID <- c(rep("A", 5), rep("B", 6), rep("C", 2), rep("D", 3), rep("E", 4))
year <- as.numeric(c(rep(c(2012, 2013, 2014, 2015), 4), 2012, 2013, 2015, 2016))
mydata <- cbind(ID, year)
mydata <- as.data.table(mydata)
mydata$year <- as.numeric(mydata$year)

for this data, i have found out which IDs have at least three years of consecutive history: 

mydata2 <- mydata[, grp := cumsum(c(0, diff(year)==1)), by = ID][,max_grp := max(grp), by=ID][max_grp>=2]

Now, i want to keep only last three years of data for every ID: 

mydata2 <- mydata2[which(year >= max(year - 2)), by = ID]

the results are correct, but i get an warning here: 

Warning message:
In `[.data.table`(mydata2, which(year >= max(year - 3)), by = ID) :
  Ignoring by= because j= is not supplied

Basically, i want to filter IDs, that have three years consecutive years and in case they have more than three years of consecutive history, i want to keep only three years.

Is there a better way to do this? This doesnt seem very robust, even though i only have limited experience

Upvotes: 0

Views: 312

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388847

Perhaps, you are looking for this : 

library(data.table)
mydata2[, .SD[year >= max(year) - 2], by = ID]

#    ID year grp max_grp
# 1:  A 2013   1       3
# 2:  A 2014   2       3
# 3:  A 2015   3       3
# 4:  B 2013   0       4
# 5:  B 2014   1       4
# 6:  B 2015   2       4
# 7:  B 2013   3       4
# 8:  B 2014   4       4
# 9:  D 2013   0       2
#10:  D 2014   1       2
#11:  D 2015   2       2
#12:  E 2015   1       2
#13:  E 2016   2       2

Upvotes: 1

Related Questions