Imagine I have a XTS object called SP500 and I would like to create new columns like this:
SP500$Cierre1 = lag(SP500$Cierre, k=1)
SP500$Cierre2 = lag(SP500$Cierre, k=2)
SP500$Cierre3 = lag(SP500$Cierre, k=3)
SP500$Cierre4 = lag(SP500$Cierre, k=4)
SP500$Cierre5 = lag(SP500$Cierre, k=5)
What I want is another way to create these columns but using a variable like this in R:
i = 11
SP500$(paste("Cierre",i)) = lag(SP500$Cierre, k=i)
Any idea?
Thank you very much in advance.
Because you have an xts object just adding columns with lapply is not going to work. The return of the lag functions is another xts object. The easiest way to combine this data is by using merge.
I will give a reproducible example which you can adjust to fit your needs. The lapply in combination with lag will create a list of lagged xts objects. These we merge back into the main xts object with Reduce and merge.
# use quantmod to get SPY data
library(quantmod)
SPY <- getSymbols("SPY", auto.assign = FALSE)
i <- 3
# use of Cl function to find the close.
# beware of multiple close columns
lagged_data <- lapply(1:i,function(x) lag(Cl(SPY), x))
# name the lagged data
names(lagged_data) <- paste0("close", 1:i)
# use of reduce and merge to combine everything
SPY <- Reduce(merge, lagged_data, SPY)
head(SPY)
SPY.Open SPY.High SPY.Low SPY.Close SPY.Volume SPY.Adjusted SPY.Close.1 SPY.Close.2 SPY.Close.3
2007-01-03 142.25 142.86 140.57 141.37 94807600 111.1660 NA NA NA
2007-01-04 141.23 142.05 140.61 141.67 69620600 111.4019 141.37 NA NA
2007-01-05 141.33 141.40 140.38 140.54 76645300 110.5134 141.67 141.37 NA
2007-01-08 140.82 141.41 140.25 141.19 71655000 111.0245 140.54 141.67 141.37
2007-01-09 141.31 141.60 140.40 141.07 75680100 110.9301 141.19 140.54 141.67
2007-01-10 140.58 141.57 140.30 141.54 72428000 111.2997 141.07 141.19 140.54