R数据表:更新连接 [英] R data table: update join

问题描述

假设我有两个数据表:

X <- data.table(id = 1:5, L = letters[1:5])

   id L
1:  1 a
2:  2 b
3:  3 c
4:  4 d
5:  5 e

Y <- data.table(id = 3:5, L = c(NA, "g", "h"), N = c(10, NA, 12))

   id  L  N
1:  3 NA 10
2:  4  g NA
3:  5  h 12

是否可以使用数据表内置函数通过 id 对 X 和 Y 进行左外连接?如果没有，我想构建一个具有以下预期输出的函数(例如 leftOuterJoin):

Would it be possible to do a left outer join of X and Y by id using data table built-in functions? If not, I would like build a function (e.g. leftOuterJoin) with the following expected output:

leftOuterJoin(X, Y, on = "id")

   id  L  N
1:  1  a NA
2:  2  b NA
3:  3 NA 10
4:  4  g NA
5:  5  h 12

我试过没有成功:

X[Y, on = "id"]

   id L i.L  N
1:  3 c  NA 10
2:  4 d   g NA
3:  5 e   h 12

我也试过这个，几乎就是我要找的:

I have also tried this, which is almost what I am looking for:

setkey(X, id)
setkey(Y, id)
merge(X, Y, all.x = TRUE)

   id L.x L.y  N
1:  1   a  NA NA
2:  2   b  NA NA
3:  3   c  NA 10
4:  4   d   g NA
5:  5   e   h 12

推荐答案

是更新连接:

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]))]

另一种变体:

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

这篇关于R数据表:更新连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！