Calculate difference between index date and date with first indicator

userFE4C 0
Viewed 30

Suppose I have the following data frame with an index date and follow up dates with a "1" as a stop indicator. I want to input the date difference in days into the index row and if no stop indicator is present input the number of days from the index date to the last observation:

id  date             group   indicator      
1   15-01-2022        1       0
1   15-01-2022        2       0
1   16-01-2022        2       1
1   20-01-2022        2       0
2   18-01-2022        1       0
2   20-01-2022        2       0
2   27-01-2022        2       0

Want:

id  date             group   indicator     stoptime
1   15-01-2022        1       0             NA
1   15-01-2022        2       0             NA
1   16-01-2022        2       1             1
1   20-01-2022        2       0             NA
2   18-01-2022        1       0             NA
2   20-01-2022        2       0             NA
2   27-01-2022        2       0             9
data-manipulation group-by date r
1 Answers

Convert the 'date' to Date class, grouped by 'id', find the position of 1 from 'indicator' (if not found, use the last position -n()), then get the difference of 'date' from the first to that position in days

library(dplyr)
library(lubridate)
df1 %>% 
  mutate(date = dmy(date)) %>% 
  group_by(id) %>%
  mutate(ind = match(1, indicator, nomatch = n()),
   stoptime = case_when(row_number() == ind ~ 
   as.integer(difftime(date[ind], first(date), units = "days"))), 
      ind = NULL) %>% 
  ungroup

-output

# A tibble: 7 × 5
     id date       group indicator stoptime
  <int> <date>     <int>     <int>    <int>
1     1 2022-01-15     1         0       NA
2     1 2022-01-15     2         0       NA
3     1 2022-01-16     2         1        1
4     1 2022-01-20     2         0       NA
5     2 2022-01-18     1         0       NA
6     2 2022-01-20     2         0       NA
7     2 2022-01-27     2         0        9

data

df1 <- structure(list(id = c(1L, 1L, 1L, 1L, 2L, 2L, 2L), date = c("15-01-2022", 
"15-01-2022", "16-01-2022", "20-01-2022", "18-01-2022", "20-01-2022", 
"27-01-2022"), group = c(1L, 2L, 2L, 2L, 1L, 2L, 2L), indicator = c(0L, 
0L, 1L, 0L, 0L, 0L, 0L)), class = "data.frame",
 row.names = c(NA, 
-7L))
akrun0
Related