My question is as follows:
I would like to generate column d based on the information from column c. Column c provides the names of the columns from which to fetch data from, for that given row.
a b c d
1 5 3 a 5
2 8 6 b 6
3 12 8 a 12
My current method is very inefficient:
DT[, d:=mget(c)]
for(i in 1:nrow(DT)) { e[i] <- DT[,d][[i]][i]}
DT[,e:=e]
Appreciate it greatly if there is any one-liner solution.
You can group by the values in column c, and use get() to get the values.
dt[, d := get(c), by = c]
which gives
dt
# a b c d
# 1: 5 3 a 5
# 2: 8 6 b 6
# 3: 12 8 a 12
Data:
dt <- data.table(a = c(5, 8, 12), b = c(3, 6, 8), c = c("a", "b", "a"))
You actually don't even need data.table if you don't want:
DT$d <- sapply(1:nrow(DT),function(i){DT[i,get(as.character(DT[i,c]))]})
> DT
a b c d
1: 5 3 a 5
2: 8 6 b 6
3: 12 8 a 12
This solution is also more flexible in that it allows c to refer to any column in the data.
data
DT<-structure(list(a = c(5L, 8L, 12L), b = c(3L, 6L, 8L), c = structure(c(1L,
2L, 1L), .Label = c("a", "b"), class = "factor")), .Names = c("a",
"b", "c"), class = c("data.table", "data.frame"), row.names = c(NA,
-3L), .internal.selfref = <pointer: 0x00000000001f0788>)
Your data:
a <- c(5,8,12)
b <- c(3,6,8)
c <- c("a", "b", "a")
df <- as.data.frame(cbind(a,b,c))
This is how you could do it.
d <- NULL
for (i in 1:NROW(df)){d <- c(d, as.character(df[i,as.character(c[i])]))}
df$d <- d
# a b c d
#1 5 3 a 5
#2 8 6 b 6
#3 12 8 a 12
This allows you to do the same thing as above in the for loop using just 1 line of code (similar to MikeyMike's answer).
df$d <- sapply(1:NROW(df), function(i){as.character(df[i,as.character(c[i])])})
You can use an ifelse statement:
dt[, d := ifelse(c == "a", a, b)]
dt
# a b c d
# 1: 5 3 a 5
# 2: 8 6 b 6
# 3: 12 8 a 12
Another option is to consider to reshape your data which can deal with multiple columns problem:
dt[, id := seq_len(nrow(dt))] # create an id column for reshape purpose
[melt(dt, id.vars = c("id", "c"))[c == variable], d:=value , on = "id"]
# reshape data, select values that match the column names and then join back with the original data.
[, id := NULL] # drop the id column
dt
# a b c d
# 1: 5 3 a 5
# 2: 8 6 b 6
# 3: 12 8 a 12
