Time since last event of grouped data in R

Question

I have a data frame that contains a grouping variable (ID), a date and an event column with numeric values, in which 0 represent no event and >0 represents an event. An example data frame can be generated with the following code:

df <- data.frame(ID = c (1, 1, 1, 1, 2, 2, 2),
                   date = as.Date(c("2014-08-03", "2014-08-04", "2014-08-07", "2014-08-10", "2015-07-01", "2015-07-03", "2015-08-01")),
                   event = c(1, 0, 3, 0, 0, 4, 0))
df
> df
  ID       date event
1  1 2014-08-03     1
2  1 2014-08-04     0
3  1 2014-08-07     3
4  1 2014-08-10     0
5  2 2015-07-01     0
6  2 2015-07-03     4
7  2 2015-08-01     0

Now, I want to calculate the time that has passed since any last event (>0) has occured. In the particular case that the first entry/entries for any ID contains no event, "NA" should be generated. My desired output would look like this:

> df
  ID       date event tae
1  1 2014-08-03     1   0
2  1 2014-08-04     0   1
3  1 2014-08-07     3   0
4  1 2014-08-10     0   3
5  2 2015-07-01     0  NA
6  2 2015-07-03     4   0
7  2 2015-08-01     0  29

I have tried several different approaches. The closest I got was this:

library(dplyr)

df %>%
  mutate(tmpG = cumsum(c(FALSE, as.logical(diff(event))))) %>%
  group_by(ID) %>%
  mutate(tmp = c(0, diff(date)) * !event) %>%
  group_by(tmpG) %>%
  mutate(tae = cumsum(tmp)) %>%
  ungroup() %>%
  select(-c(tmp, tmpG))

# A tibble: 7 x 4
     ID date       event   tae
  <dbl> <date>     <dbl> <dbl>
1     1 2014-08-03     1     0
2     1 2014-08-04     0     1
3     1 2014-08-07     3     0
4     1 2014-08-10     0     3
5     2 2015-07-01     0     3
6     2 2015-07-03     4     0
7     2 2015-08-01     0    29

Any suggestions on how to get that code running (or any other alternative) would be greatly appreciated.

Answer 1

Here is another tidyverse approach, that uses fill to carry forward the most recent event.

library(tidyverse)

df %>%
  group_by(ID) %>%
  mutate(last_event = if_else(event > 0, date, NA_real_)) %>%
  fill(last_event) %>%
  mutate(tae = as.numeric(date - last_event))

Output

     ID date       event last_event   tae
  <dbl> <date>     <dbl> <date>     <dbl>
1     1 2014-08-03     1 2014-08-03     0
2     1 2014-08-04     0 2014-08-03     1
3     1 2014-08-07     3 2014-08-07     0
4     1 2014-08-10     0 2014-08-07     3
5     2 2015-07-01     0 NA            NA
6     2 2015-07-03     4 2015-07-03     0
7     2 2015-08-01     0 2015-07-03    29