通过匹配其他列将表列中的某些记录替换为另一个表

Question

我想用另一个表t2替换t1$x3中的一些记录(注意，除x3外，所有其他列都是相同的)：

t1 <- data.frame(x1 = c(1,7,3,4,2,6),
                 x2 = c("I","R","R","I","I","R"),
                 x3 = c("a","a","a","a","a","a"))
t2 <- data.frame(x1 = c(4,2,6),
                 x2 = c("I","I","R"),
                 x3 = c("b","b","b"))
t1
#   x1 x2 x3
# 1  1  I  a
# 2  7  R  a
# 3  3  R  a
# 4  4  I  a
# 5  2  I  a
# 6  6  R  a

t2
#   x1 x2 x3
# 1  4  I  b
# 2  2  I  b
# 3  6  R  b

结果应该是这样的：

data.frame(x1 = c(1,7,3,4,2,6),
           x2 = c("I","R","R","I","I","R"),
           x3 = c("a","a","a","b","b","b"))

#   x1 x2 x3
# 1  1  I  a
# 2  7  R  a
# 3  3  R  a
# 4  4  I  b
# 5  2  I  b
# 6  6  R  b

我该怎么做呢？

Answer 1

然后左联接更新：

library(dplyr)

left_join(t1, t2, by = c("x1", "x2"), all.x = TRUE) %>% 
  mutate(x3 = ifelse(is.na(x3.y), as.character(x3.x), as.character(x3.y))) %>% 
  select(-c(x3.x, x3.y))

#   x1 x2 x3
# 1  1  I  a
# 2  7  R  a
# 3  3  R  a
# 4  4  I  b
# 5  2  I  b
# 6  6  R  b

使用R基：

# left join 
res <- merge(t1, t2, by = c("x1", "x2"), all.x = TRUE)
# update x3
res$x3 <- ifelse(is.na(res$x3.y), as.character(res$x3.x), as.character(res$x3.y))
# subset and reorder
res <- res[match(t1$x1, res$x1), c("x1", "x2", "x3")]

Answer 2

我们可以试试data.table。它应该非常快，因为我们正在分配(:=)到位。将'data.frame‘转换为'data.table’(setDT(t1))，加入't2‘on 'x1’和'x2'，然后将'i.x3‘值赋给'x3’。

library(data.table)
setDT(t1)[t2, x3 := i.x3, on = c("x1", "x2")]
t1
#   x1 x2 x3
#1:  1  I  a
#2:  7  R  a
#3:  3  R  a
#4:  4  I  b
#5:  2  I  b
#6:  6  R  b

或者我们可以使用match

t1$x3 <- factor(t1$x3, levels = c('a','b'))
t1[match(do.call(paste,t2[-3]), do.call(paste, t1[-3])), 'x3'] <- t2$x3
t1
# x1 x2 x3
#1  1  I  a
#2  7  R  a
#3  3  R  a
#4  4  I  b
#5  2  I  b
#6  6  R  b

Answer 3

我想这会给你你想要的。

将x3列转换为t1和t2中的字符，然后检查条件

t1$x3 <- as.character(t1$x3)
t2$x3 <- as.character(t2$x3)
t1[t1$x1 %in% t2$x1 & as.character(t1$x2) %in% as.character(t2$x2), "x3"] <- t2$x3

> t1
#  x1 x2 x3
#1  1  I  a
#2  7  R  a
#3  3  R  a
#4  4  I  b
#5  2  I  b
#6  6  R  b