I'd like to acquire the values from certain columns. The columns are different for each row and depend on the values from another column. The example below should speak for itself.
Here's what I have:
id year data.2014 data.2015
1 2014 21 22
2 2015 23 24
Here's what I'd like to have:
id year data.2014 data.2015 data
1 2014 21 22 21
2 2015 23 24 24
Thank you in advance!
You could vectorize this by just adding data. to df$year and matching it in the column names
indx <- match(paste0("data.", df$year), names(df))
df$data <- df[cbind(1:nrow(df), indx)]
df
# id year data.2014 data.2015 data
# 1 1 2014 21 22 21
# 2 2 2015 23 24 24
Another option is to work on a long format data and then join back to the original data by id
library(data.table)
indx <- melt(setDT(df), id = 1:2)[variable == paste0("data.", year), .(id, value)]
df[indx, data := i.value, on = "id"]
df
# id year data.2014 data.2015 data
# 1: 1 2014 21 22 21
# 2: 2 2015 23 24 24
If your years columns are sorted this way, you can do something like:
data <- data.frame(id=1:2, year=2014:2015, data.2014=c(21,23), data.2015=c(22,24))
tmp <- c()
for (i in 1:nrow(data)){
tmp <- c(tmp, data[i, 3+data$year[i]-2014])
}
data$test <- tmp
data
