R: Days since last event per ID

Question

I am interested in finding the number of days since the last event per ID. The data looks like this:

df <- data.frame(date=as.Date(
c("06/07/2000","15/09/2000","15/10/2000","03/01/2001","17/03/2001",
"06/08/2010","15/09/2010","15/10/2010","03/01/2011","17/03/2011"), "%d/%m/%Y"), 
event=c(0,0,1,0,1, 1,0,0,0,1),id = c(rep(1,5),rep(2,5)))

         date event id
1  2000-07-06     0  1
2  2000-09-15     0  1
3  2000-10-15     1  1
4  2001-01-03     0  1
5  2001-03-17     1  1
6  2010-08-06     1  2
7  2010-09-15     0  2
8  2010-10-15     0  2
9  2011-01-03     0  2
10 2011-03-17     1  2

I am borrowing heavily from a data table solution here but this does not consider ID's.

library(data.table)
setDT(df)
setkey(df, date,id)

df = df[event == 1, .(lastevent = date), key = date][df, roll = TRUE]
df[, tae := difftime(lastevent, shift(lastevent, 1L, "lag"), unit = "days")]
df[event == 0, tae:= difftime(date, lastevent, unit = "days")]

It generates the following output

          date  lastevent event id       tae
 1: 2000-07-06       <NA>     0  1   NA days
 2: 2000-09-15       <NA>     0  1   NA days
 3: 2000-10-15 2000-10-15     1  1   NA days
 4: 2001-01-03 2000-10-15     0  1   80 days
 5: 2001-03-17 2001-03-17     1  1  153 days
 6: 2010-08-06 2010-08-06     1  2 3429 days
 7: 2010-09-15 2010-08-06     0  2   40 days
 8: 2010-10-15 2010-08-06     0  2   70 days
 9: 2011-01-03 2010-08-06     0  2  150 days
10: 2011-03-17 2011-03-17     1  2  223 days

My desired output however is as follows:

          date  lastevent event id       tae
 1: 2000-07-06       <NA>     0  1   NA days
 2: 2000-09-15       <NA>     0  1   NA days
 3: 2000-10-15 2000-10-15     1  1   NA days
 4: 2001-01-03 2000-10-15     0  1   80 days
 5: 2001-03-17 2001-03-17     1  1  153 days
 6: 2010-08-06 2010-08-06     1  2   NA days
 7: 2010-09-15 2010-08-06     0  2   40 days
 8: 2010-10-15 2010-08-06     0  2   70 days
 9: 2011-01-03 2010-08-06     0  2  150 days
10: 2011-03-17 2011-03-17     1  2  223 days

The only difference is the NA in row 6 and column tae. This is a related post that is unanswered. I have looked here, but the solution does not work in my case. There are many other questions like this but not for calculations per ID. Thank you!

simone · Accepted Answer · 2017-05-26T14:29:28.820

df <- data.table(date=as.Date(c("06/07/2000","15/09/2000","15/10/2000","03/01/2001","17/03/2001","06/08/2010","15/09/2010","15/10/2010","03/01/2011","17/03/2011"), 
"%d/%m/%Y"), event=c(0,0,1,0,1, 1,0,1,0,1),id = c(rep(1,5),rep(2,5)))

tempdt <- df[event==1,]

tempdt[,tae := date - shift(date), by = id]

df <- merge(df, tempdt, by = c("date", "event", "id"), all.x = TRUE)

df[, tae := ifelse(shift(event)==1, date - shift(date), tae), by = id]

EDIT

More general solution

df <- data.table(date=as.Date(c("06/07/2000","15/09/2000","15/10/2000","03/01/2001","17/03/2001", "18/03/2001",
                            "06/08/2010","15/09/2010","15/10/2010","03/01/2011","17/03/2011","19/03/2011"), 
                          "%d/%m/%Y"), 
             event=c(1,0,0,0,0,0,1,1,1,0,1,0),id = c(rep(1,6),rep(5,6)))

##for event = 1 observations
tempdt <- df[event==1,]

tempdt[,tae := date - shift(date), by = id]

df <- merge(df, tempdt, by = c("date", "event", "id"), all.x = TRUE)

##for event = 0 observations
for(d in df[event==0, date]){
  # print(as.Date(d, origin = "1970-01-01"))
  df[date == d & event == 0, tae := as.Date(d, origin = "1970-01-01") - 
   max(df[date<d & event==1,date]), by = id]  
}

EDIT 2 Now, there must be a faster way to do this, but if first observation is event = 0, this won't result in any warning

df <- data.table(date=as.Date(c("06/07/2000","15/09/2000","15/10/2000","03/01/2001","17/03/2001","06/08/2010","15/09/2010","15/10/2010","03/01/2011","17/03/2011"),
                           "%d/%m/%Y"), event=c(0,0,1,0,1, 1,0,0,0,1),id = c(rep(1,5),rep(2,5))) 

tempdt <- df[event==1,] 

tempdt[,tae := date - shift(date), by = id] 

df <- merge(df, tempdt, by = c("date", "event", "id"), all.x = TRUE) 

for(i in unique(df[,id])){
  # print(i)
  for(d in df[date>df[id == i & event==1,min(date)] & event==0, date]){
  # print(as.Date(d, origin = "1970-01-01"))
    df[id == i & date == d & event == 0,
     tae := as.Date(d, origin = "1970-01-01") - max(df[date<d & 
     event==1,date])]
  }  
}

Just wanted to mention that your code does not work for this data: df <- data.frame(date=as.Date(c("06/07/2000","15/09/2000","15/10/2000","03/01/2001","17/03/2001", "18/03/2001","06/08/2010","15/09/2010","15/10/2010","03/01/2011","17/03/2011","19/03/2011"), "%d/%m/%Y"), event=c(1,0,0,0,0,0, 1,1,1,0,1,0),id = c(rep(1,6),rep(5,6))) — HOSS_JFL, May 26 '17 at 06:04
Thank you a thousand times. However, for the dataset in my edited post. I get some warnings. "In max.default(numeric(0), na.rm = FALSE) : no non-missing arguments to min; return -Inf ". It does not work at the begining....Is this due to data table? — HOSS_JFL, May 26 '17 at 13:19
Hello! Yes I use data table 1.10-4.rm(list = ls()) df <- data.table(date=as.Date( c("06/07/2000","15/09/2000","15/10/2000","03/01/2001","17/03/2001", "06/08/2010","15/09/2010","15/10/2010","03/01/2011","17/03/2011"), "%d/%m/%Y"), event=c(0,0,1,0,1, 1,0,0,0,1),id = c(rep(1,5),rep(2,5))) tempdt <- df[event==1,] tempdt[,tae := date - shift(date), by = id] df <- merge(df, tempdt, by = c("date", "event", "id"), all.x = TRUE) for(d in df[event==0, date]){ df[date == d & event == 0, tae := as.Date(d, origin = "1970-01-01") - max(df[date — HOSS_JFL, May 26 '17 at 13:50

R: Days since last event per ID

1 Answers1