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!
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