Select column dynamically based on value from another column in R

Question

How do I use column in data table as variable name to fetch values from other columns based on the said column.

library(data.table)
a = c(2,3,5)
b = c(5,7,7)
c = c(1,2,3)
x = c ('a','b','c')
dt <- data.table(a,b,c,x)

> dt
   a b c x
1: 2 5 1 a
2: 3 7 2 b
3: 5 7 3 c

output I desire column y which is based on values of column x which contains the column names of values to be fetched.

dt
   a b c x y
1: 2 5 1 a 2
2: 3 7 2 b 7
3: 5 7 3 c 3

I tried

dt[,get(x)]

dt[,match(x,colnames(dt))]

use the `dt[, y := dt[, get(x), 1:nrow(dt)]$V1]` – akrun Jul 30 '18 at 17:54 — akrun, Jul 30 '18 at 17:54

score 3 · Accepted Answer · answered Jul 30 '18 at 17:54

3

By looping through the sequence of rows, extract the value with get and assign it to create 'y'

dt[, y := .SD[, get(x), seq_len(.N)]$V1]
dt
#   a b c x y
#1: 2 5 1 a 2
#2: 3 7 2 b 7
#3: 5 7 3 c 3

answered Jul 30 '18 at 17:54

akrun

874,273
37
540
662

1

Should be more efficient to do this by=x than by row, I guess. (Fwiw, searching for a dupe.) – Frank Jul 30 '18 at 18:06
Just one more help. How to insert this y column at beggining of frame? – aprilian Jul 30 '18 at 18:16
@OmerFarooq After the assignment `setcolorder(dt, c("y", setdiff(names(dt), "y")))` – akrun Jul 30 '18 at 22:09
1

@akrun Just `setcolorder(dt, 'y')` should suffice in the latest version (I think since v1.11.0). – Ameya Jul 30 '18 at 22:48

Select column dynamically based on value from another column in R

1 Answers1