CODEWITHSUNDEEP
r
add-semi-colons
add-semi-colons

Reputation: 18810

R: Create multiple lag versions of each column

I have following data table in R:

> head(oil.data)
   Quarter  US_GDP       UK   Canada MiddleEast   Africa Total_Oil_Production
1:  2008Q1 14685.3 77.22900 96.73333 0.06666667 7784.333               1290.3
2:  2008Q2 14668.4 78.19967 98.36667 0.36666667 7988.200               1212.8
3:  2008Q3 14813.0 78.29500 98.46667 0.13333333 8090.567               1302.0
4:  2008Q4 14843.0 78.63800 97.56667 0.60000000 8120.800               1136.6
5:  2009Q1 14549.9 78.47733 98.23333 0.30000000 8197.200                846.4
6:  2009Q2 14383.9 79.22400 99.70000 0.40000000 8278.100                748.3

> tail(oil.data)
   Quarter  US_GDP       UK   Canada MiddleEast   Africa Total_Oil_Production
1:  2014Q3 17270.0 84.91467 111.8667 -0.9333333 11175.43               1500.0
2:  2014Q4 17522.1 85.44067 111.8333 -3.2000000 11029.73               1451.2
3:  2015Q1 17615.9 85.19467 112.2000 -0.2000000 10941.33               1392.3
4:  2015Q2 17649.3 86.17133 114.5000  0.9333333 10858.97               1346.3
5:  2015Q3 17913.7 86.65300 115.7000 -0.1000000 10985.20               1554.4
6:  2015Q4 18060.2 86.85767 116.9000  0.8000000 10933.03               1542.6

Objective: Generate multiple lag versions of each column and append to the data.table

I can successfully do that with the following one line: Lets say for example I want to lag it twice, 

oil.data[, c(paste("US_GDP_lag"  , 1: 2, sep="")) := lapply(1: 2, function(i) c(rep(NA, i), head(US_GDP,   -i)))]

I get following: 

> head(oil.data)
   Quarter  US_GDP       UK   Canada MiddleEast   Africa Total_Oil_Production US_GDP_lag1 US_GDP_lag2
1:  2008Q1 14685.3 77.22900 96.73333 0.06666667 7784.333               1290.3          NA          NA
2:  2008Q2 14668.4 78.19967 98.36667 0.36666667 7988.200               1212.8     14685.3          NA
3:  2008Q3 14813.0 78.29500 98.46667 0.13333333 8090.567               1302.0     14668.4     14685.3
4:  2008Q4 14843.0 78.63800 97.56667 0.60000000 8120.800               1136.6     14813.0     14668.4
5:  2009Q1 14549.9 78.47733 98.23333 0.30000000 8197.200                846.4     14843.0     14813.0
6:  2009Q2 14383.9 79.22400 99.70000 0.40000000 8278.100                748.3     14549.9     14843.0

For each column I can keep doing this but if I have large number of columns this can be tedious process. My goal was to create a function. I did the following: 

lag.data.table <- function(data.set, lag.period) {
  col.names <- names(data.set)
  for(col in col.names) {
    lagged.df <- data.set[, c(paste(paste(col, "_l", sep=""), 1: lag.period, sep="")) := lapply(1: lag.period, function(i) c(rep(NA, i), head(col,   -i)))]
  }
  lagged.df
}

all the lagged columns are NA: What am I doing wrong here?

> head(lag.df)
   Quarter  US_GDP       UK   Canada MiddleEast   Africa Total_Oil_Production Quarter_l1 Quarter_l2 US_GDP_l1 US_GDP_l2 UK_l1 UK_l2 Canada_l1 Canada_l2 MiddleEast_l1 MiddleEast_l2
1:  2008Q1 14685.3 77.22900 96.73333 0.06666667 7784.333               1290.3         NA         NA        NA        NA    NA    NA        NA        NA            NA            NA
2:  2008Q2 14668.4 78.19967 98.36667 0.36666667 7988.200               1212.8         NA         NA        NA        NA    NA    NA        NA        NA            NA            NA
3:  2008Q3 14813.0 78.29500 98.46667 0.13333333 8090.567               1302.0         NA         NA        NA        NA    NA    NA        NA        NA            NA            NA
4:  2008Q4 14843.0 78.63800 97.56667 0.60000000 8120.800               1136.6         NA         NA        NA        NA    NA    NA        NA        NA            NA            NA
5:  2009Q1 14549.9 78.47733 98.23333 0.30000000 8197.200                846.4         NA         NA        NA        NA    NA    NA        NA        NA            NA            NA
6:  2009Q2 14383.9 79.22400 99.70000 0.40000000 8278.100                748.3         NA         NA        NA        NA    NA    NA        NA        NA            NA            NA
   Africa_l1 Africa_l2 Total_Oil_Production_l1 Total_Oil_Production_l2
1:        NA        NA                      NA                      NA
2:        NA        NA                      NA                      NA
3:        NA        NA                      NA                      NA
4:        NA        NA                      NA                      NA
5:        NA        NA                      NA                      NA
6:        NA        NA                      NA                      NA

I did research any there are built in function that does the lag but those only do one each column a one lag version.

I also found a previous answer but didn't do exactly what I am trying to do.

Upvotes: 1

Views: 655

Answers (1)

Darren Frye
Darren Frye

Reputation: 116

It looks like you are keeping the quotes around the column name in your lapply function within the for loop. When you create a vector with the list of names they are pulled with quotes around them. At the end where you have head(col, -i) you are essentially calling a string instead of the object you are looking for. If you replace it with head(data.set[[col]], -i) it should work for you. The full function should be:

lag.data.table <- function(data.set, lag.period) {
  col.names <- names(data.set)
  for(col in col.names) {
    lagged.df <- data.set[, c(paste(paste(col, "_l", sep=""), 1: lag.period, sep="")) := lapply(1: lag.period, function(i) c(rep(NA, i), head(data.set[[col]],   -i)))]
  }
  lagged.df
}

Upvotes: 1

Related Questions