How to use an existing dummy variable to create a new one that takes the value 1 for certain lead ob-CodePudding

I have a dataset like the one below:

 dat <- data.frame (id  = c(1,1,1,1,1,2,2,2,2,2),
                  year = c(2015, 2016, 2017,2018, 2019, 2015, 2016, 2017, 2018, 2019),
                  sp=c(1,0,0,0,0,0,1,0,0,0))
dat
   id year sp
1   1 2015  1
2   1 2016  0
3   1 2017  0
4   1 2018  0
5   1 2019  0
6   2 2015  0
7   2 2016  1
8   2 2017  0
9   2 2018  0
10  2 2019  0

I'd like to use the "sp" dummy variable to create a new dummy (call it "d") that takes the value of 1 for observations t 2 or more years (within each id group) after the sp variable takes the value of 1. The resulting dataset should look like the one below:

   id year sp d
1   1 2015  1 0
2   1 2016  0 0
3   1 2017  0 1
4   1 2018  0 1
5   1 2019  0 1
6   2 2015  0 0
7   2 2016  1 0
8   2 2017  0 0
9   2 2018  0 1
10  2 2019  0 1

Using the dplyr package, I am able to create the desired d variable for t 2 years after the sp variable takes the value of 1, but have no idea how to assign to d the value 1 for all years (within each id group) greater than t 2.

dat<- 
  dat%>%
  group_by(id) %>%
  mutate(d = dplyr::lag(sp, n = 2, order_by=year,default = 0))

dat

     id  year    sp     d
   <dbl> <dbl> <dbl> <dbl>
 1     1  2015     1     0
 2     1  2016     0     0
 3     1  2017     0     1
 4     1  2018     0     0
 5     1  2019     0     0
 6     2  2015     0     0
 7     2  2016     1     0
 8     2  2017     0     0
 9     2  2018     0     1
10     2  2019     0     0

Any help is much appreciated. Thank you!

CodePudding user response：

We can use cummax on the lag

library(dplyr)
dat %>%
  group_by(id) %>%
  mutate(d = cummax(lag(sp, 2, default = 0))) %>%
  ungroup

-output

 A tibble: 10 × 4
      id  year    sp     d
   <dbl> <dbl> <dbl> <dbl>
 1     1  2015     1     0
 2     1  2016     0     0
 3     1  2017     0     1
 4     1  2018     0     1
 5     1  2019     0     1
 6     2  2015     0     0
 7     2  2016     1     0
 8     2  2017     0     0
 9     2  2018     0     1
10     2  2019     0     1

CodePudding user response：

Here is an alternative using cumsum and an ifelse statement:

dat %>% 
  group_by(id, col1 = cumsum(sp == 1)) %>% 
  mutate(d = ifelse(abs(first(year) - year) >= 2, 1, 0)) %>% 
  ungroup() %>% 
  select(-col1)

  id  year    sp     d
   <dbl> <dbl> <dbl> <dbl>
 1     1  2015     1     0
 2     1  2016     0     0
 3     1  2017     0     1
 4     1  2018     0     1
 5     1  2019     0     1
 6     2  2015     0     0
 7     2  2016     1     0
 8     2  2017     0     0
 9     2  2018     0     1
10     2  2019     0     1