rdplyrtidyrdata-manipulation

Get the number of days since last event


I have data where, for each individual, the dates of event are related. Here is an example:

  id       Date
 1001 2025-06-20
 1002 2025-06-24
 1002 2025-06-20
 1002 2025-06-19

What I would like to achieve, is to obtain all combinations of id in the range of date (from 2025-06-19 to 2025-06-24) with the number of days since the last event. Here would be the needed output:

   id     Date       LastDate
  1001 2025-06-24        4
  1001 2025-06-23        3
  1001 2025-06-22        2
  1001 2025-06-21        1
  1001 2025-06-20        0
  1001 2025-06-19       NA
  1002 2025-06-24        0
  1002 2025-06-23        3
  1002 2025-06-22        2
  1002 2025-06-21        1
  1002 2025-06-20        0
  1002 2025-06-19        0

Solution

  • Another tidyverse solution:

    library(tidyverse)
    
    df %>%
      mutate(Date = as.Date(Date), av = 1)%>%
      complete(id, Date = seq(as.Date("2025-06-19"),
                          as.Date("2025-06-24")),fill = list(av = 0))%>%
      mutate(av = Date-Date[na_if(cummax(row_number()*av), 0)], .by = id)%>%
      arrange(id, desc(Date))
    
    # A tibble: 12 × 3
          id Date       av     
       <int> <date>     <drtn> 
     1  1001 2025-06-24  4 days
     2  1001 2025-06-23  3 days
     3  1001 2025-06-22  2 days
     4  1001 2025-06-21  1 days
     5  1001 2025-06-20  0 days
     6  1001 2025-06-19 NA days
     7  1002 2025-06-24  0 days
     8  1002 2025-06-23  3 days
     9  1002 2025-06-22  2 days
    10  1002 2025-06-21  1 days
    11  1002 2025-06-20  0 days
    12  1002 2025-06-19  0 days