Changing the shape of a time series in R

Question

I have data.frame as shown below.

Values 1 16.50 2 16.53 3 16.48 4 16.38 5 16.16 6 16.10 7 16.21 8 16.50 

I need to create a new data frame that contains for each value the previous 2 records in one row, for example:

  line #1: 16.50, 16.53, 16.48 line #2: 16.53, 16.48, 16.38 

Do you have any suggestions on how to achieve the result?

Thanks in advance.

Answer 1

You can use the embed function in the R base:

 embed(DF$Values, 3L)[, 3:1] # [,1] [,2] [,3] # [1,] 16.50 16.53 16.48 # [2,] 16.53 16.48 16.38 # [3,] 16.48 16.38 16.16 # [4,] 16.38 16.16 16.10 # [5,] 16.16 16.10 16.21 # [6,] 16.10 16.21 16.50 

Answer 2

Try

 library(dplyr) df %>% mutate(Col1=lead(Values,1), Col2=lead(Values,2)) # Values Col1 Col2 #1 16.50 16.53 16.48 #2 16.53 16.48 16.38 #3 16.48 16.38 16.16 #4 16.38 16.16 16.10 #5 16.16 16.10 16.21 #6 16.10 16.21 16.50 #7 16.21 16.50 NA #8 16.50 NA NA 

Update

Or you can try

 n <- 3 m1 <- matrix(df$Values, nrow=nrow(df)+1, ncol=n)[-nrow(df)+1,] m1 # [,1] [,2] [,3] #[1,] 16.50 16.53 16.48 #[2,] 16.53 16.48 16.38 #[3,] 16.48 16.38 16.16 #[4,] 16.38 16.16 16.10 #[5,] 16.16 16.10 16.21 #[6,] 16.10 16.21 16.50 #[7,] 16.50 16.50 16.53 #[8,] 16.50 16.53 16.48 

data

 df <- structure(list(Values = c(16.5, 16.53, 16.48, 16.38, 16.16, 16.1, 16.21, 16.5)), .Names = "Values", class = "data.frame", row.names = c("1", "2", "3", "4", "5", "6", "7", "8")) 

Answer 3

You can define a helper function

 lagfunc <- function(x, y) unname(cbind(y, sapply(seq_len(x), function(x) c(tail(y, -x), rep(NA, x))))) 

Then run it

 n <- 2 lagfunc(n, df$Values) # [,1] [,2] [,3] # [1,] 16.50 16.53 16.48 # [2,] 16.53 16.48 16.38 # [3,] 16.48 16.38 16.16 # [4,] 16.38 16.16 16.10 # [5,] 16.16 16.10 16.21 # [6,] 16.10 16.21 16.50 # [7,] 16.21 16.50 NA # [8,] 16.50 NA NA 

---

Or using data.table

 library(data.table) setDT(df)[, shift(Values, 0:n, type = "lead")]