cthulhukk cthulhukk - 8 months ago 59
R Question

Creating new columns in dataframe with incremently decreasing amount of rows in R

I am relatively new to R, and programming in general, and have a dataframe with two columns, such as:

X <- c(2010,2011,2012,2013,2014)
Y <- c(1,2,3,4,5)
DATA <- data.frame(X,Y)

Now I would like to create a number of new columns equalling the number of rows minus one. The first newly created column should begin in row number two and each additional column should start one below the prior one (until the last column with one value is created).
The values should be taken from column Y one row above.

The desired output is as follows:

X <- c(2010,2011,2012,2013,2014)
Y <- c(1,2,3,4,5)
NEW_COLUMN_1 <- c(NA,1,2,3,4)
NEW_COLUMN_2 <- c(NA,NA,2,3,4)
NEW_COLUMN_3 <- c(NA,NA,NA,3,4)

As I said, I am new to R and have no idea how to start, so I would appreciate any help you may offer.

Answer Source

You can create the new columns using lapply and dplyr::lag. The resulting list is bound together using Reduce("cbind", ...) and converted to a data.frame/tibble, and given column names using setNames

newcols <- Reduce("cbind", lapply(1:(nrow(DATA)-1), function(i) dplyr::lag(DATA$Y, i))) %>%
  as_tibble() %>%
  setNames(paste0("NEW_COLUMN_", 1:ncol(.)))
cbind(DATA, newcols)

# 1 2010 1           NA           NA           NA           NA
# 2 2011 2            1           NA           NA           NA
# 3 2012 3            2            1           NA           NA
# 4 2013 4            3            2            1           NA
# 5 2014 5            4            3            2            1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download