R data.table add new column with values from other columns by referencing

user_13148253 0
Viewed 400

I have a sample data.table as below:

> dt = data.table("Label" = rep(LETTERS[1:3], 3),
+                 "Col_A" = c(2,3,5,0,2,7,6,8,9),
+                 "Col_B" = c(1,4,3,5,2,0,7,5,8),
+                 "Col_C" = c(2,0,4,1,5,6,7,3,0))
> dt[order(Label)]

 Label Col_A Col_B Col_C
1:     A     2     1     2
2:     A     0     5     1
3:     A     6     7     7
4:     B     3     4     0
5:     B     2     2     5
6:     B     8     5     3
7:     C     5     3     4
8:     C     7     0     6
9:     C     9     8     0

I want to create a new column which takes values from the existing columns based on the Label column. My desired sample output is as below:

 Label Col_A Col_B Col_C Newcol
1:     A     2     1     2      2
2:     A     0     5     1      0
3:     A     6     7     7      6
4:     B     3     4     0      4
5:     B     2     2     5      2
6:     B     8     5     3      5
7:     C     5     3     4      4
8:     C     7     0     6      6
9:     C     9     8     0      0

The logic is that the Newcol value refers to the respective columns based on the Label column. For example, the first 3 rows of the Label column is A, so the first 3 rows of the Newcol column refers to the first 3 rows of the Col_A column.

I have tried using the code dt[, `:=` ("Newcol" = eval(as.symbol(paste0("Col_", dt$Label))))] but it doesn't give the desired output.

data.table r eval
4 Answers

We can use a vectorized switch function of the kit package, which like data.table is part of the fastverse.

dt[, "Newcol" := kit::vswitch(Label, c("A", "B", "C"), list(Col_A, Col_B, Col_C))]

# or if you want to pass column indices
dt[, "Newcol" := kit::vswitch(Label, c("A", "B", "C"), dt[,2:4])]

dt
   Label Col_A Col_B Col_C Newcol
1:     A     2     1     2      2
2:     A     0     5     1      0
3:     A     6     7     7      6
4:     B     3     4     0      4
5:     B     2     2     5      2
6:     B     8     5     3      5
7:     C     5     3     4      4
8:     C     7     0     6      6
9:     C     9     8     0      0
user_148539070

With fcase:

cols <- unique(dt$Label)
dt[,newCol:=eval(parse(text=paste('fcase(',paste0("Label=='",cols,"',Col_",cols,collapse=','),')')))][]

    Label Col_A Col_B Col_C newCol
   <char> <num> <num> <num>  <num>
1:      A     2     1     2      2
2:      B     3     4     0      4
3:      C     5     3     4      4
4:      A     0     5     1      0
5:      B     2     2     5      2
6:      C     7     0     6      6
7:      A     6     7     7      6
8:      B     8     5     3      5
9:      C     9     8     0      0
user_135133280 
library(data.table)
dt = data.table("Label" = rep(LETTERS[1:3], 3),
                "Col_A" = c(2,3,5,0,2,7,6,8,9),
                "Col_B" = c(1,4,3,5,2,0,7,5,8),
                "Col_C" = c(2,0,4,1,5,6,7,3,0))

dt[, new := ifelse(Label == "A", Col_A, NA)]
dt[, new := ifelse(Label == "B", Col_B, new)]
dt[, new := ifelse(Label == "C", Col_C, new)]
user_39248230

If you are able to use the dplyr library, I would use the case_when function from there.

dt$newCol <- case_when(dt$Col_A == 'A' ~ Col_A, dt$Col_A == 'B' ~ Col_B, dt$Col_A == 'C' ~ Col_C)

I've not tested that code but it would be something like that.

user_141828710
Related