CODEWITHSUNDEEP
rdplyr
Mark Davies
Mark Davies

Reputation: 940

Find the next row matching condition

df = data.frame(group_id = c(rep("a",5), rep("b",5)),
                prod_id = 1:10,
                prod_type = rep(c("a","a", "b", "c","d"),2),
                start = lubridate::dmy(c("01/01/2001", "02/02/2002", "03/03/2003", "04/04/2004", "05/05/2005")),
                fin = lubridate::dmy(c(NA,NA,NA,NA,NA)))

df
  group_id prod_id prod_type      start  fin
1         a       1         a 2001-01-01 <NA>
2         a       2         a 2002-02-02 <NA>
3         a       3         b 2003-03-03 <NA>
4         a       4         c 2004-04-04 <NA>
5         a       5         d 2005-05-05 <NA>
6         b       6         a 2001-01-01 <NA>
7         b       7         a 2002-02-02 <NA>
8         b       8         b 2003-03-03 <NA>
9         b       9         c 2004-04-04 <NA>
10        b      10         d 2005-05-05 <NA>

The data will be grouped by group_id. I need a way to identify, per group, for each prod_id the next prod_id that is, for example prod_type "a", "c", or "d".

Upvotes: 1

Views: 52

Answers (1)

r2evans
r2evans

Reputation: 160407

Try this:

library(dplyr)
# library(tidyr) # fill
df %>%
  group_by(group_id) %>%
  mutate(next_acd = if_else(lead(prod_type) %in% c("a","c","d"), lead(prod_id), prod_id[NA])) %>%
  tidyr::fill(next_acd, .direction = "up") %>%
  ungroup()
# # A tibble: 10 x 6
#    group_id prod_id prod_type start      fin    next_acd
#    <chr>      <int> <chr>     <date>     <date>    <int>
#  1 a              1 a         2001-01-01 NA            2
#  2 a              2 a         2002-02-02 NA            4
#  3 a              3 b         2003-03-03 NA            4
#  4 a              4 c         2004-04-04 NA            5
#  5 a              5 d         2005-05-05 NA           NA
#  6 b              6 a         2001-01-01 NA            7
#  7 b              7 a         2002-02-02 NA            9
#  8 b              8 b         2003-03-03 NA            9
#  9 b              9 c         2004-04-04 NA           10
# 10 b             10 d         2005-05-05 NA           NA

Upvotes: 1

Related Questions