Event dif_time fixing last date occurrence

Question

I have some events identified by id, var1, var2 and date.

The desired output for dif_time is as follow:

  id var1 var2      date1      date2 dif_time
  1  120    1 2014-06-03 2014-06-30       26
  1  120    1 2014-06-04 2014-06-30       26
  1  120    4 2014-06-05 2014-06-30       25
  2  220    1 2014-06-05 2014-06-30       23
  2  220    1 2014-06-07 2014-06-30       23
  3  120    2 2014-06-10 2014-06-30       15
  3  120    2 2014-06-12 2014-06-30       15
  3  120    1 2014-06-15 2014-06-30       15
  5  220    3 2014-06-20 2014-06-30       10

I need to calculate the dif_time in days between date1 (the event date) and a control date date2. The constrain is:

For an event (id,var1,var2) I need to find the last.date1 and calculate:

dif_time(days) = date2 - last.date1, for each event and report the result for the event.

I did not find a way to fixed last.date1, so your help is appreciated.

Answer 1

You could do this

dd<-data.frame(
    id = c(1L, 1L, 1L, 2L, 2L, 3L, 3L, 3L, 5L),
    var1 = c(120L, 120L, 120L, 220L, 220L, 120L, 120L, 120L, 220L), 
    var2 = c(1L, 1L, 4L, 1L, 1L, 2L, 2L, 1L, 3L), 
    date1 = structure(c(16224, 16225, 16226, 16226, 16228, 16231, 16233, 16236, 16241), class = "Date"), 
    date2 = structure(c(16251, 16251, 16251, 16251, 16251, 16251, 16251, 16251, 16251), class = "Date")
)

last.date1<-with(dd, ave(date1, id, var1, var2, FUN=max, drop=T))
dd$date2-last.date1

dd$diff_time <- dd$date2-last.date1