Tried to explain the expected output considering 6*6 table and lagging by 2. but the real dataset is much bigger consisting 10,000 rows and 50 columns and needs to be lagged by 18 times cyclically as described below:
# Input dataset
eg <- data.frame(Month = c("a","b","c","d","e","f"), Code = c(11111,22222,33333,44444,66666,23445),
col1 = c(1,2,3,4,5,6), col2 = c(3,5,6,7,8,9), col3 = c(2,3,5,7,8,9), col4 = c(1,6,3,5,2,8), col5 = c(2,9,53,67,12,33))
# Multiply each row by 3 times (Number of times to lag minus 1)
rep_eg <- eg %>% slice(rep(1:n(), each = 3))
# Rename the column based on lagging set point : m
colnames(rep_eg) <- c("Month","Code","m-2","m-1","m","m+1","m+2")
# Output as of now
current_out <- rep_eg %>% mutate("m-1" = lag(m),
"m-2" = lag(m,n = 2))
(current_out)
# Expected output : Shift to be applied **cyclically** on the shifted column i.e.
expected_output = current_out
expected_output$`m-2` = c(NA,NA,2,NA,NA,3,NA,NA,5,NA,NA,7,NA,NA,8,NA,NA,9)
expected_output$`m-1` = c(NA,2,2,NA,3,3,NA,5,5,NA,7,7,NA,8,8,NA,9,9)
# Expected output
(expected output)
Here is the demo of lagging 2 columns (first column by 1 time and second by 2 times) Is there way to lag in this manner for 36 columns (first columns by 1 time , second by 2 times......36th by 36 times)**
How to obtain such expected output described
Thanks in advance