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.
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