Reputation: 1467
I have a data table with a number of columns containing values. I have another column which defines which one of those columns whose value I need to select. I am having trouble finding a way to do this.
Here is a simple example.
> d <- data.table(
value.1 = c("one", "uno", "1"),
value.2 = c("two", "dos", "2"),
name.of.col = c("value.1","value.2","value.1"))
> d
value.1 value.2 name.of.col
1: one two value.1
2: uno dos value.2
3: 1 2 value.1
I would like to add a column 'value.of.col' which contains the value of the column specified by 'name.of.col'.
> d
value.1 value.2 name.of.col value.of.col
1: one two value.1 one
2: uno dos value.2 dos
3: 1 2 value.1 1
Upvotes: 5
Views: 1929
Reputation: 121608
Another option:
d[ , value.of.col := diag(as.matrix(.SD)), .SDcols = d[ , name.of.col]]
> d
value.1 value.2 name.of.col value.of.col
1: one two value.1 one
2: uno dos value.2 dos
3: 1 2 value.1 1
EDIT add a faster solution:
d[ , value.of.col :=
melt(d,id.vars='name.of.col')[name.of.col==variable, value]]
Upvotes: 6
Reputation: 52687
You can use matrix indexing to pull values from the first and second columns:
mx.idx <- d[, cbind(1:nrow(d), match(name.of.col, names(d)))]
d[,
value.of.col:=
as.matrix(d[, 1:2])[mx.idx]
]
d
# value.1 value.2 name.of.col value.of.col
# 1: one two value.1 one
# 2: uno dos value.2 dos
# 3: 1 2 value.1 1
Upvotes: 2
Reputation: 59612
The following should be memory efficient and a little easier to read/follow.
for (i in unique(d[["name.of.col"]]))
d[ name.of.col==i, value.of.col:=get(i) ]
d
value.1 value.2 name.of.col value.of.col
1: one two value.1 one
2: uno dos value.2 dos
3: 1 2 value.1 1
Upvotes: 2