You could try something like
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
library(tidyr)
#> Warning: package 'tidyr' was built under R version 4.0.5
library(magrittr)
#>
#> Attaching package: 'magrittr'
#> The following object is masked from 'package:tidyr':
#>
#> extract
df = data.frame(
Date_ = 1:10,
ISIN = rep(c('CH00','GB00'),length.out=10,each=5),
Return = c(0.017531563, -0.073376071 , -0.010745412 , 0.045742055, -0.072018814 ,
0.041200982 , 0.040654871 , -0.029012563 , -0.073155490 , 0.042203267 ),
STOXX_Return = c(-0.0003749766, -0.0220566972 , 0.0182991778 , 0.0641541456 ,
-0.0193375447 , 0.0144049186 , 0.0119439111 , -0.0463974419 ,
-0.0066808630 , 0.0047172371 ) ,
Rating_Change = c(0,0,0,1.9,0,0,0,0,-8.9,0),
Rating = c('A','A','A','A','A','B+','B+','B+','B','B')
)
df
#> Date_ ISIN Return STOXX_Return Rating_Change Rating
#> 1 1 CH00 0.01753156 -0.0003749766 0.0 A
#> 2 2 CH00 -0.07337607 -0.0220566972 0.0 A
#> 3 3 CH00 -0.01074541 0.0182991778 0.0 A
#> 4 4 CH00 0.04574205 0.0641541456 1.9 A
#> 5 5 CH00 -0.07201881 -0.0193375447 0.0 A
#> 6 6 GB00 0.04120098 0.0144049186 0.0 B+
#> 7 7 GB00 0.04065487 0.0119439111 0.0 B+
#> 8 8 GB00 -0.02901256 -0.0463974419 0.0 B+
#> 9 9 GB00 -0.07315549 -0.0066808630 -8.9 B
#> 10 10 GB00 0.04220327 0.0047172371 0.0 B
df2 <- df %>%
group_by(ISIN) %>%
arrange(Date_) %>%
mutate (y1 = lag(Return,1),
y2 = lag(Return,2),
y3 = lag(Return,3),
y4 = lag(Return,4),) %>%
mutate (x1 = lag(STOXX_Return,1),
x2 = lag(STOXX_Return,2),
x3 = lag(STOXX_Return,3),
x4 = lag(STOXX_Return,4)
)
df2
#> # A tibble: 10 x 14
#> # Groups: ISIN [2]
#> Date_ ISIN Return STOXX_Return Rating_Change Rating y1 y2 y3
#> <int> <chr> <dbl> <dbl> <dbl> <chr> <dbl> <dbl> <dbl>
#> 1 1 CH00 0.0175 -0.000375 0 A NA NA NA
#> 2 2 CH00 -0.0734 -0.0221 0 A 0.0175 NA NA
#> 3 3 CH00 -0.0107 0.0183 0 A -0.0734 0.0175 NA
#> 4 4 CH00 0.0457 0.0642 1.9 A -0.0107 -0.0734 0.0175
#> 5 5 CH00 -0.0720 -0.0193 0 A 0.0457 -0.0107 -0.0734
#> 6 6 GB00 0.0412 0.0144 0 B+ NA NA NA
#> 7 7 GB00 0.0407 0.0119 0 B+ 0.0412 NA NA
#> 8 8 GB00 -0.0290 -0.0464 0 B+ 0.0407 0.0412 NA
#> 9 9 GB00 -0.0732 -0.00668 -8.9 B -0.0290 0.0407 0.0412
#> 10 10 GB00 0.0422 0.00472 0 B -0.0732 -0.0290 0.0407
#> # ... with 5 more variables: y4 <dbl>, x1 <dbl>, x2 <dbl>, x3 <dbl>, x4 <dbl>
df2 <- df2 %>%
filter(Rating_Change != 0)
df2
#> # A tibble: 2 x 14
#> # Groups: ISIN [2]
#> Date_ ISIN Return STOXX_Return Rating_Change Rating y1 y2 y3
#> <int> <chr> <dbl> <dbl> <dbl> <chr> <dbl> <dbl> <dbl>
#> 1 4 CH00 0.0457 0.0642 1.9 A -0.0107 -0.0734 0.0175
#> 2 9 GB00 -0.0732 -0.00668 -8.9 B -0.0290 0.0407 0.0412
#> # ... with 5 more variables: y4 <dbl>, x1 <dbl>, x2 <dbl>, x3 <dbl>, x4 <dbl>
df3 <- tidyr::pivot_longer(df2 %>% select(-c('y1','y2','y3','y4'))
,cols = c('x1','x2','x3','x4'),names_to='var1',values_to='x')
df4 <- tidyr::pivot_longer(df2 %>% select( c('ISIN','y1','y2','y3','y4'))
,cols = c('y1','y2','y3','y4'),names_to='var2',values_to='y')
cbind(df3,df4 %>% ungroup() %>% select(-'ISIN'))
#> # A tibble: 8 x 10
#> # Groups: ISIN [2]
#> Date_ ISIN Return STOXX_Return Rating_Change Rating var1 x var2
#> <int> <chr> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <chr>
#> 1 4 CH00 0.0457 0.0642 1.9 A x1 0.0183 y1
#> 2 4 CH00 0.0457 0.0642 1.9 A x2 -0.0221 y2
#> 3 4 CH00 0.0457 0.0642 1.9 A x3 -0.000375 y3
#> 4 4 CH00 0.0457 0.0642 1.9 A x4 NA y4
#> 5 9 GB00 -0.0732 -0.00668 -8.9 B x1 -0.0464 y1
#> 6 9 GB00 -0.0732 -0.00668 -8.9 B x2 0.0119 y2
#> 7 9 GB00 -0.0732 -0.00668 -8.9 B x3 0.0144 y3
#> 8 9 GB00 -0.0732 -0.00668 -8.9 B x4 NA y4
#> # ... with 1 more variable: y <dbl>
Created on 2021-05-15 by the reprex package (v2.0.0)