Select column using the value in other row of a data.table in R

Question

I need to create a new variable containing the value in one column in a data.frame depending on the value of another column, the example goes as follows:

library(data.table)

set.seed(pi)
DT <- data.table(
  X1 = LETTERS[1:10],
  X2 = letters[1:10],
  Z = sample(c("X1", "X2"), 10, replace = TRUE)
)

DT[]

This code generates the following

    X1 X2  Z
 1:  A  a X1
 2:  B  b X2
 3:  C  c X1
 4:  D  d X1
 5:  E  e X2
 6:  F  f X2
 7:  G  g X1
 8:  H  h X1
 9:  I  i X2
10:  J  j X2

Now I want to have a column W where if column Z is "X1" (or "X2") the content on the column X1 (or X2) is selected.

One solution can be:

DT[Z == "X1", W := X1]
DT[Z == "X2", W := X2]

But I would like to find a more elegant way to do this because I have many columns where I need to select one entry.

Thanks

score 2 · Accepted Answer · answered Sep 14 '16 at 18:36

2

We can use get after looping through sequence of rows

DT[, W :=  get(Z) , 1:nrow(DT)]

Or with eval(as.name

DT[,  W := eval(as.name(Z)) , 1:nrow(DT)]

answered Sep 14 '16 at 18:36

akrun

789,025
32
460
575

1

Thank you, now the answer look easy but I was stuck in expressions like: `DT[, W := diag(as.matrix(DT[,DT[, Z], with = FALSE]))]` – Enrique Pérez Herrero Sep 14 '16 at 18:39
1

@EnriquePérezHerrero Creating a matrix and taking `diag` should be not efficient – akrun Sep 14 '16 at 18:42
Yes you lose the advantages of using a `data.table` – Enrique Pérez Herrero Sep 14 '16 at 18:43
1

@EnriquePérezHerrero Yes, that is one problem, second is that creating matrix with huge datasets can take more memory along with `diag` should be slow – akrun Sep 14 '16 at 18:44
1

@EnriquePérezHerrero If you are using `data.frame`, this can be made more efficient with row/column indexing i.e. `setDF(DT); DT$W – akrun Sep 14 '16 at 18:48

Select column using the value in other row of a data.table in R

1 Answers1