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
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