Hello,
Although I do not fully understand what you mean by "lag-value of 8 quarters", based off what I could understand, I've come up with this for now:
library(tidyverse)
df <- tibble::tibble(
id = c(rep(1,14)),
year = c(rep(1990,4),rep(1991,4),rep(1992,3),rep(1993,3)),
quarter = c(rep(seq(1:4),2),rep(seq(1:3),2)),
var1 = c(rep(1,14)),
var2 = c(rep(0,2),rep(1,12)),
var3 = c(rep(0,14)),
var4 = c(rep(0,14)),
var5 = c(rep(0,10),1,0,0,1),
quarter_num = c(1:11,13:15)
)
#Create a complete dataset for year and quarter
completeData = data.frame(
year = min(df$year):max(df$year),
quarter = rep(1:4, each = length(min(df$year):max(df$year)))
) %>% arrange(year, quarter)
completeData
#> year quarter
#> 1 1990 1
#> 2 1990 2
#> 3 1990 3
#> 4 1990 4
#> 5 1991 1
#> 6 1991 2
#> 7 1991 3
#> 8 1991 4
#> 9 1992 1
#> 10 1992 2
#> 11 1992 3
#> 12 1992 4
#> 13 1993 1
#> 14 1993 2
#> 15 1993 3
#> 16 1993 4
#Copy-paste for each ID
completeData = map_df(unique(df$id), function(myId){
completeData %>% mutate(id = myId)
})
completeData
#> year quarter id
#> 1 1990 1 1
#> 2 1990 2 1
#> 3 1990 3 1
#> 4 1990 4 1
#> 5 1991 1 1
#> 6 1991 2 1
#> 7 1991 3 1
#> 8 1991 4 1
#> 9 1992 1 1
#> 10 1992 2 1
#> 11 1992 3 1
#> 12 1992 4 1
#> 13 1993 1 1
#> 14 1993 2 1
#> 15 1993 3 1
#> 16 1993 4 1
#Join the rest of data (but crop missing quarters at start and end)
completeData = completeData %>%
left_join(df, by = c("year", "quarter", "id")) %>%
filter(between(row_number(), min(quarter_num, na.rm = T),
max(quarter_num, na.rm = T)))
completeData
#> year quarter id var1 var2 var3 var4 var5 quarter_num
#> 1 1990 1 1 1 0 0 0 0 1
#> 2 1990 2 1 1 0 0 0 0 2
#> 3 1990 3 1 1 1 0 0 0 3
#> 4 1990 4 1 1 1 0 0 0 4
#> 5 1991 1 1 1 1 0 0 0 5
#> 6 1991 2 1 1 1 0 0 0 6
#> 7 1991 3 1 1 1 0 0 0 7
#> 8 1991 4 1 1 1 0 0 0 8
#> 9 1992 1 1 1 1 0 0 0 9
#> 10 1992 2 1 1 1 0 0 0 10
#> 11 1992 3 1 1 1 0 0 1 11
#> 12 1992 4 1 NA NA NA NA NA NA
#> 13 1993 1 1 1 1 0 0 0 13
#> 14 1993 2 1 1 1 0 0 0 14
#> 15 1993 3 1 1 1 0 0 1 15
#Look at presence of vars in last 8 quarters
completeData %>% group_by(id) %>% slice((n() - 7):n()) %>%
summarise(across(c(var1:var5), function(x){
any(x == 1, na.rm = T)
}))
#> # A tibble: 1 x 6
#> id var1 var2 var3 var4 var5
#> <dbl> <lgl> <lgl> <lgl> <lgl> <lgl>
#> 1 1 TRUE TRUE FALSE FALSE TRUE
Created on 2021-08-25 by the reprex package (v2.0.1)
Hope this helps,
PJ