1

Minimal working example:

library(dplyr)
df = data.frame(group_id = c("G1","G1", rep("G2",8)),
                prod_id = c(1,2,5,6,7,8,9,10,11,12),
                prod_type = rep(c("a","a", "b", "c","d"),2),
                start = lubridate::dmy(c("01/01/2001", "02/02/2002",
                                         "05/05/2005","06/06/2006", "07/07/2007", "08/08/2008", "09/09/2009","01/01/2010", "02/02/2011", "03/03/2012" )))%>%
  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")

df:

group_id prod_id prod_type start      next_acd
   <chr>      <dbl> <chr>     <date>        <dbl>
 1 G1             1 a         2001-01-01        2
 2 G1             2 a         2002-02-02       NA
 3 G2             5 b         2005-05-05        6
 4 G2             6 c         2006-06-06        7
 5 G2             7 d         2007-07-07        8
 6 G2             8 a         2008-08-08        9
 7 G2             9 a         2009-09-09       11
 8 G2            10 b         2010-01-01       11
 9 G2            11 c         2011-02-02       12
10 G2            12 d         2012-03-03       NA

Where next_acd is the next prod_id in that group which is prod_type "a", "c" or "d".

I now want to create a new column fin which is the start date of the next prod_type "a", "c" or "d". I.e. I want to match the next_acd to prod_id and get the corresponding start.

I have tried the answer given on another question Get data from variable z where variable x = variable y

df$fin <- df$start[df$prod_id[df$next_acd]]

df:

 group_id prod_id prod_type start      next_acd fin       
   <chr>      <dbl> <chr>     <date>        <dbl> <date>    
 1 G1             1 a         2001-01-01        2 2002-02-02
 2 G1             2 a         2002-02-02       NA NA        
 3 G2             5 b         2005-05-05        6 2010-01-01
 4 G2             6 c         2006-06-06        7 2011-02-02
 5 G2             7 d         2007-07-07        8 2012-03-03
 6 G2             8 a         2008-08-08        9 NA        
 7 G2             9 a         2009-09-09       11 NA        
 8 G2            10 b         2010-01-01       11 NA        
 9 G2            11 c         2011-02-02       12 NA        
10 G2            12 d         2012-03-03       NA NA

I'm not sure what is going on here. fin is only correctly entered for row 1.

This is what the output should be:

group_id prod_id prod_type start      fin        next_acd
   <chr>      <dbl> <chr>     <date>     <date>        <dbl>
 1 G1             1 a         2001-01-01 2002-02-02        2
 2 G1             2 a         2002-02-02 NA               NA
 3 G2             5 b         2005-05-05 2006-06-06        6
 4 G2             6 c         2006-06-06 2007-07-07        7
 5 G2             7 d         2007-07-07 2008-08-08        8
 6 G2             8 a         2008-08-08 2009-09-09        9
 7 G2             9 a         2009-09-09 2011-02-02       11
 8 G2            10 b         2010-01-01 2011-02-02       11
 9 G2            11 c         2011-02-02 2012-03-03       12
10 G2            12 d         2012-03-03 NA               NA
user438383
  • 5,716
  • 8
  • 28
  • 43
Mark Davies
  • 787
  • 5
  • 18
  • Perhaps https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right ? – Martin Gal May 06 '22 at 14:35
  • I'm not sure if it's what you meant, but see my answer below. But do you know why the attempt in the question doesn't work? – Mark Davies May 06 '22 at 15:07
  • It's using `next_acd` as a row index within the group, e.g. 6 is looking up the 6th row of group `G2`. You could use the `match` function per below. – Carl May 06 '22 at 15:11

2 Answers2

1

A tidyverse option:

library(tidyverse)

df <- data.frame(
  group_id = c("G1", "G1", rep("G2", 8)),
  prod_id = c(1, 2, 5, 6, 7, 8, 9, 10, 11, 12),
  prod_type = rep(c("a", "a", "b", "c", "d"), 2),
  start = lubridate::dmy(c(
    "01/01/2001", "02/02/2002",
    "05/05/2005", "06/06/2006", "07/07/2007", "08/08/2008", "09/09/2009", "01/01/2010", "02/02/2011", "03/03/2012"
  ))
) %>%
  group_by(group_id) %>%
  mutate(next_acd = if_else(lead(prod_type) %in% c("a", "c", "d"), lead(prod_id), prod_id[NA])) %>%
  fill(next_acd, .direction = "up")

df %>%
  group_by(group_id) %>%
  mutate(fin = start[match(next_acd, prod_id, nomatch = NA_real_)])
#> # A tibble: 10 × 6
#> # Groups:   group_id [2]
#>    group_id prod_id prod_type start      next_acd fin       
#>    <chr>      <dbl> <chr>     <date>        <dbl> <date>    
#>  1 G1             1 a         2001-01-01        2 2002-02-02
#>  2 G1             2 a         2002-02-02       NA NA        
#>  3 G2             5 b         2005-05-05        6 2006-06-06
#>  4 G2             6 c         2006-06-06        7 2007-07-07
#>  5 G2             7 d         2007-07-07        8 2008-08-08
#>  6 G2             8 a         2008-08-08        9 2009-09-09
#>  7 G2             9 a         2009-09-09       11 2011-02-02
#>  8 G2            10 b         2010-01-01       11 2011-02-02
#>  9 G2            11 c         2011-02-02       12 2012-03-03
#> 10 G2            12 d         2012-03-03       NA NA

Created on 2022-05-06 by the reprex package (v2.0.1)

Carl
  • 4,232
  • 2
  • 12
  • 24
0

Based on the suggestion to use a merge:

df1= df[,c("prod_id","start")]%>%rename(fin = start)
df2= df[,c("group_id", "prod_type","prod_id", "start","next_acd")]

left_join(df2, df1, by= c("next_acd"="prod_id"))

group_id prod_type prod_id start      next_acd fin       
   <chr>    <chr>       <dbl> <date>        <dbl> <date>    
 1 G1       a               1 2001-01-01        2 2002-02-02
 2 G1       a               2 2002-02-02       NA NA        
 3 G2       b               5 2005-05-05        6 2006-06-06
 4 G2       c               6 2006-06-06        7 2007-07-07
 5 G2       d               7 2007-07-07        8 2008-08-08
 6 G2       a               8 2008-08-08        9 2009-09-09
 7 G2       a               9 2009-09-09       11 2011-02-02
 8 G2       b              10 2010-01-01       11 2011-02-02
 9 G2       c              11 2011-02-02       12 2012-03-03
10 G2       d              12 2012-03-03       NA NA
Mark Davies
  • 787
  • 5
  • 18