Filter records based on their occurrence in R

user_13734451 0
Viewed 46

I would to filter to some records to retain the records where there was an outcome==1 but drop subsequent records where there was no outcome. e.g. for id s001 I would like to keep records for 2020-04-23 and 2020-05-18 but drop the subsequent ones. Same to s002 drop records after 2020-09-30.

library(dplyr)

set.seed(2022)

dat1 <- tibble(
  surv_date = seq.Date(as.Date("2020-04-03"), as.Date("2021-01-01"), by = 5),
  outcome = rbinom(55, 1, 0.3),
  id = paste0("s00", rbinom(55, 10, 0.3))
)


first_dat <- dat1 %>%
  arrange(id, surv_date) %>%
  group_by(id, outcome) %>%
  mutate(occurance = row_number()) %>% 
  filter(occurance == 1 & outcome == 1 )


second_dat <- dat1 %>%
  arrange(id, surv_date) %>%
  group_by(id, outcome) %>%
  mutate(occurance = row_number()) %>% 
  filter( outcome == 0 )


first_dat %>% 
  bind_rows(second_dat) %>% 
  arrange(id, surv_date)
dplyr r
1 Answers

Maybe you can use something like cumall, which will include all rows until the first FALSE evaluated in filter. If the previous row is 1 for outcome then omit that and all subsequent rows. Or in other words, keep all rows until the prior lag row is 1.

dat1 %>%
  arrange(id, surv_date) %>%
  group_by(id) %>%
  filter(cumall(lag(outcome, default = 0) != 1))

Output

   surv_date  outcome id   
   <date>       <int> <chr>
 1 2020-04-23       0 s001 
 2 2020-05-18       1 s001 
 3 2020-04-08       0 s002 
 4 2020-06-07       0 s002 
 5 2020-09-10       0 s002 
 6 2020-09-25       0 s002 
 7 2020-09-30       1 s002 
 8 2020-04-03       1 s003 
 9 2020-05-08       0 s004 
10 2020-05-13       0 s004 
11 2020-05-23       0 s004 
12 2020-07-02       0 s004 
13 2020-07-22       0 s004 
14 2020-08-16       1 s004 
15 2020-06-17       0 s005 
16 2020-06-22       1 s005 
17 2020-11-14       0 s006 
18 2020-12-29       0 s006 
19 2020-10-30       0 s007
user_34606700
Related