R Data Table: Update Join

R data table: update join

It is an update join:

library(data.table)
X <- data.table(id = 1:5, L = letters[1:5])
Y <- data.table(id = 3:5, L = c(NA, "g", "h"), N = c(10, NA, 12))
X[Y, on=.(id), c("L", "N"):=.(i.L, i.N)][]
#    id  L  N
# 1:  1  a NA
# 2:  2  b NA
# 3:  3 NA 10
# 4:  4  g NA
# 5:  5  h 12

gives you the desired result.

Here I found a solution for multiple columns:

library(data.table)
X <- data.table(id = 1:5, L = letters[1:5])
Y <- data.table(id = 3:5, L = c(NA, "g", "h"), N = c(10, NA, 12))

X[Y, on=.(id), names(Y)[-1]:=mget(paste0("i.", names(Y)[-1]))]

Another variant:

n <- names(Y)
X[Y, on=.(id), (n):=mget(paste0("i.", n))]

Update subset of data.table based on join

The easiest way I can think of is to key by id1 as well.
eg

setkey(DT1, id2,id1)
DT2[, id1 := 3]
setkey(DT2, id2, id1)

# use i.v1 to reference v1 from the i component
DT1[DT2, v1 := i.v1 ]

DT1
   id1 id2        v1
1:   2   e 0.7383247
2:   1   g 1.5952808
3:   2   j 0.3295078
4:   3   n 0.0000000
5:   3   s 0.5757814
6:   1   u 0.4874291

R Data Table - join but filter with update

You can create a dummy variable a in DT2, join on both columns a and b and then Update:

DT[DT2[, c(a = 3, .SD)], c := i.c, on = c("a", "b")]

DT
#   a b  c
#1: 1 1 NA
#2: 2 2 NA
#3: 3 3 10
#4: 1 4 NA
#5: 2 5 NA
#6: 3 6 10
#7: 1 7 NA
#8: 2 8 NA
#9: 3 9 10

Left join using data.table

You can try this:

# used data
# set the key in 'B' to the column which you use to join
A <- data.table(a = 1:4, b = 12:15)
B <- data.table(a = 2:3, b = 13:14, key = 'a') 

B[A]

---

Related Topics