R语言匹配两数据框id时text不匹配赋值NA的简化方法

现有数据 #

两个测试数据框的构造代码如下：

df1 <- data.frame(
  id=c("632592651","633322173","634703802","634927873","635812953","636004739","636101211","636157799","636263106","636752420"),
  text=c("asdf","cat","dog","mouse","elephant","goose","rat","mice","kitty","kitten"),
  response=c("y","y","y","n","n","y","y","n","n","y")
)

df2 <- data.frame(
  id=c("632592651","633322173","634703802","634927873","635812953","636004739","636101211","636157799","636263106","636752420","636809222","2004722036","2004894388","2005045755","2005535472","2005630542","2005788781","2005809679","2005838317","2005866692"),
  text=c("asdf_xyz","cat","dog","mouse","elephant","goose","rat","mice","kitty","kitten","tiger_xyz","lion","leopard","ostrich","kangaroo","platypus","fish","reptile","mammals","amphibians_xyz"),
  volume=c("1234","432","324","333","2223","412346","7456","3456","2345","2345","6","345","23","2","4778","234","8675","3459","8","9")
)

两表基础结构：

• df1共10行，包含id、text、response三列
• df2共20行，包含id、text、volume三列

匹配需求 #

需要生成结果表df3，规则如下：

• 仅保留两表中id能匹配的行（以df1的id范围为准）
• 对匹配到的行：若id和text在两表中完全一致，保留text原值；若text不匹配，对应位置的text赋值为NA
• 同时关联匹配id对应的df2其他字段（示例中为volume列）

期望输出结果：

id         text response volume
1  632592651     <NA>        y   1234
2  633322173      cat        y    432
3  634703802      dog        y    324
4  634927873    mouse        n    333
5  635812953 elephant        n   2223
6  636004739    goose        y 412346
7  636101211      rat        y   7456
8  636157799     mice        n   3456
9  636263106    kitty        n   2345
10 636752420   kitten        y   2345

原有实现的痛点 #

原有方法拆分了反连接、值替换、双列内连接、列重命名、行绑定多个步骤，生成了多个中间数据框，当表的列数较多时，操作繁琐、容易出错，维护成本高。

简化实现代码 #

直接用dplyr单次左连接+条件判断即可一步得到结果，不需要生成中间表：

library(dplyr)

df3 <- df1 %>%
  # 按id关联df2所有列，同名列自动加后缀区分
  left_join(df2, by = "id", suffix = c("", "_df2")) %>%
  # 两表text相等则保留原值，否则赋值为NA
  mutate(text = ifelse(text == text_df2, text, NA_character_)) %>%
  # 选择需要的最终列，丢弃临时生成的对比用text_df2列
  select(id, text, response, volume)

运行上述代码得到的结果和期望输出完全一致。如果df2还有其他需要关联的列，只需要在最后的select中补充列名即可，不需要修改其他逻辑。

内容的提问来源于stack exchange，提问作者Catalyst