CODEWITHSUNDEEP
rlag
shafi Q
shafi Q

Reputation: 77

How to take lag on mutliple columns as given below in R or Python

Here is the reproducible example. I have more than 800 columns. I want to take the lags as given below.

ppt <- ts(rep(c(5,6,7,8,11,13,14,15,16,15,14,17,13,12,15,13,12,12,11,10,9,8,7,6), 10)*runif(120,0.8,1.2), freq = 24)
col <- c(1:20)
data <- data.frame(lapply(col, function(x) ppt[x:(110+x)])) # create a design matrix
colnames(data) <- c(paste0("x_",as.character(c(1:19))),"y")

enter image description here

Upvotes: 0

Views: 44

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388862

We can use lag in dplyr

library(dplyr)

data %>%
  mutate_all(list(lag1 = ~lag(.), lag2 = ~lag(., 2), lag3 = ~lag(., 3))) %>%
  select(gtools:: mixedorder(names(.)))

Or if you want to only lag columns that start with "x", we can use mutate_at

data %>%
  mutate_at(vars(starts_with('x')), list(lag1 = ~lag(.), lag2 = ~lag(., 2),
                                         lag3 = ~lag(., 3))) %>%
  select(gtools:: mixedorder(names(.)))

Upvotes: 1

akrun
akrun

Reputation: 887008

We can do this with shift from data.table

library(data.table)
library(gtoolss)
setDT(data)[, paste0(rep(names(data), each = 3), "Lag", 1:3) := shift(.SD, n = 1:3)]
setcolorder(data, mixedsort(names(data)))

-output

head(data, 3)
        x_1  x_1Lag1  x_1Lag2 x_1Lag3      x_2  x_2Lag1  x_2Lag2 x_2Lag3       x_3  x_3Lag1  x_3Lag2 x_3Lag3       x_4
1: 4.815211       NA       NA      NA 5.150696       NA       NA      NA  5.680486       NA       NA      NA  8.552656
2: 5.150696 4.815211       NA      NA 5.680486 5.150696       NA      NA  8.552656 5.680486       NA      NA 12.120158
3: 5.680486 5.150696 4.815211      NA 8.552656 5.680486 5.150696      NA 12.120158 8.552656 5.680486      NA 14.844832
     x_4Lag1  x_4Lag2 x_4Lag3      x_5  x_5Lag1  x_5Lag2 x_5Lag3      x_6  x_6Lag1  x_6Lag2 x_6Lag3      x_7  x_7Lag1
1:        NA       NA      NA 12.12016       NA       NA      NA 14.84483       NA       NA      NA 15.11120       NA
2:  8.552656       NA      NA 14.84483 12.12016       NA      NA 15.11120 14.84483       NA      NA 12.42051 15.11120
3: 12.120158 8.552656      NA 15.11120 14.84483 12.12016      NA 12.42051 15.11120 14.84483      NA 14.45322 12.42051
   x_7Lag2 x_7Lag3      x_8  x_8Lag1  x_8Lag2 x_8Lag3      x_9  x_9Lag1  x_9Lag2 x_9Lag3     x_10 x_10Lag1 x_10Lag2
1:      NA      NA 12.42051       NA       NA      NA 14.45322       NA       NA      NA 15.47728       NA       NA
2:      NA      NA 14.45322 12.42051       NA      NA 15.47728 14.45322       NA      NA 13.12934 15.47728       NA
3: 15.1112      NA 15.47728 14.45322 12.42051      NA 13.12934 15.47728 14.45322      NA 16.80903 13.12934 15.47728
   x_10Lag3     x_11 x_11Lag1 x_11Lag2 x_11Lag3     x_12 x_12Lag1 x_12Lag2 x_12Lag3     x_13 x_13Lag1 x_13Lag2 x_13Lag3
1:       NA 13.12934       NA       NA       NA 16.80903       NA       NA       NA 11.61594       NA       NA       NA
2:       NA 16.80903 13.12934       NA       NA 11.61594 16.80903       NA       NA 10.68735 11.61594       NA       NA
3:       NA 11.61594 16.80903 13.12934       NA 10.68735 11.61594 16.80903       NA 13.37579 10.68735 11.61594       NA
       x_14 x_14Lag1 x_14Lag2 x_14Lag3     x_15 x_15Lag1 x_15Lag2 x_15Lag3     x_16 x_16Lag1 x_16Lag2 x_16Lag3     x_17
1: 10.68735       NA       NA       NA 13.37579       NA       NA       NA 12.80526       NA       NA       NA 14.16574
2: 13.37579 10.68735       NA       NA 12.80526 13.37579       NA       NA 14.16574 12.80526       NA       NA 12.76850
3: 12.80526 13.37579 10.68735       NA 14.16574 12.80526 13.37579       NA 12.76850 14.16574 12.80526       NA  9.43223
   x_17Lag1 x_17Lag2 x_17Lag3      x_18 x_18Lag1 x_18Lag2 x_18Lag3      x_19 x_19Lag1 x_19Lag2 x_19Lag3         y
1:       NA       NA       NA 12.768504       NA       NA       NA  9.432230       NA       NA       NA  8.417161
2: 14.16574       NA       NA  9.432230 12.76850       NA       NA  8.417161 9.432230       NA       NA 10.170639
3: 12.76850 14.16574       NA  8.417161  9.43223  12.7685       NA 10.170639 8.417161  9.43223       NA  8.249955
       yLag1    yLag2 yLag3
1:        NA       NA    NA
2:  8.417161       NA    NA
3: 10.170639 8.417161    NA

Upvotes: 1

Related Questions