0

I have two tables currently:

  A  B
3.3 10
2.5 11
6.7 11
6.0 12
5.4 12
3.5 12
6.5 13
8.0 13

and 

  B Val
 10   0
 11   1
 12   2
 13   3

What would like to do is to create a new column C in the first table such that it contains the value Val corresponding to each element of B in the first table that matches the B in the second. I would like to obtain:

  A  B C
3.3 10 0
2.5 11 1
6.7 11 1
6.0 12 2
5.4 12 2
3.5 12 2
6.5 13 3
8.0 13 3

The example code is:

DT.1 <- data.table(A=c(3.3,2.5,6.7,6.0,5.4,3.5,6.5,8.0), B=c(10,11,11,12,12,12,13,13))
DT.2 <- data.table(B=c(10,11,12,13),Val=c(0,1,2,3))

Thanks for any hints or inputs.

markus
  • 25,843
  • 5
  • 39
  • 58
user321627
  • 2,350
  • 4
  • 20
  • 43
  • 1
    This is certainly a duplicate. Search for "update join" and you'll find plenty of posts. One syntax that will work is `DT.1[DT.2, val := i.Val, on="B"]`. – lmo Jan 15 '19 at 12:04

1 Answers1

1

The joining-part is most certainly a duplicate.. I included this answer, because some renaming/reordering is also being done..

dt1 <- fread("A  B
3.3 10
2.5 11
6.7 11
6.0 12
5.4 12
3.5 12
6.5 13
8.0 13", header = TRUE)

dt2 <- fread("B Val
10   0
11   1
12   2
13   3", header = TRUE)

result <- dt2[dt1, on = .(B)]
setcolorder(result, c("A", "B", "Val") )
setnames(result, old = "Val", new = "C")

#      A  B C
# 1: 3.3 10 0
# 2: 2.5 11 1
# 3: 6.7 11 1
# 4: 6.0 12 2
# 5: 5.4 12 2
# 6: 3.5 12 2
# 7: 6.5 13 3
# 8: 8.0 13 3
Wimpel
  • 26,031
  • 1
  • 20
  • 37
  • 2
    This seems more straightforward: `dt1[dt2, on = "B", C := Val]; dt1` – markus Jan 15 '19 at 12:11
  • @markus hey, that's cool! Why does that join behave like a left join?.. Sidenote: does `[]` on the end result in the same as `; dt1` – Wimpel Jan 15 '19 at 12:15
  • The `[]` prints the DT, as you know - so yes, it is the same as `; dt1`. The semicolon just imitates a line break here in the comments. Check Jaap's answer in the linked post for details. Learned a lot when I first read it. – markus Jan 15 '19 at 12:28
  • 1
    @markus thanks!! never knew this... this really helps improving my memory-optimisation.. I use `Y[X]` a lot :) – Wimpel Jan 15 '19 at 13:45