drop rows if before match code within an order

user_14622403 0
Viewed 48

I have a series of individuals represented by ID's that are associated with addresses in an order. Some, but not all, of those individuals have an address with a positive match code (matchcode = 1). I want to drop all the addresses before that match code, keeping the one with the positive match code, and the ones after. I also want to keep all the addresses if the person does not have a positive match code. So, from this dummy data:

ID = c("id1", "id1", "id1", "id2", "id2", "id2", "id3", "id3", "id3", "id4", "id4", "id4")
matchcode = c(0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0)
address = c("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l")

df = data.frame(ID, matchcode, address)

df <- df %>% group_by(ID) %>% mutate(order = row_number()) %>% ungroup

I would like to get this:

  ID    matchcode address order
  <chr>     <dbl> <chr>   <int>
1 id1           0 a           1
2 id1           1 b           2
3 id2           0 d           1
4 id2           0 e           2
5 id2           1 f           3
6 id3           0 g           1
7 id3           0 h           2
8 id3           0 i           3
9 id4           1 j           1

Any help would be greatly appreciated.

r
1 Answers

After grouping by 'ID', match the position of 1 in the 'matchcode', get the sequence and use slice to subset the rows. If there are not cases, nomatch will return the number of rows (n()), thus we get all the observations

library(dplyr)
df %>% 
  group_by(ID) %>%
  slice(seq_len(match(1, matchcode, nomatch = n()))) %>%
  ungroup

-output

# A tibble: 9 × 4
  ID    matchcode address order
  <chr>     <dbl> <chr>   <int>
1 id1           0 a           1
2 id1           1 b           2
3 id2           0 d           1
4 id2           0 e           2
5 id2           1 f           3
6 id3           0 g           1
7 id3           0 h           2
8 id3           0 i           3
9 id4           1 j           1

Or with filter

df %>%
   group_by(ID) %>% 
   filter(cumsum(c(0, diff(matchcode))) >=0) %>%
   ungroup

-output

# A tibble: 10 × 4
   ID    matchcode address order
   <chr>     <dbl> <chr>   <int>
 1 id1           0 a           1
 2 id1           1 b           2
 3 id1           0 c           3
 4 id2           0 d           1
 5 id2           0 e           2
 6 id2           1 f           3
 7 id3           0 g           1
 8 id3           0 h           2
 9 id3           0 i           3
10 id4           1 j           1
user_37322710
Related