R语言中如何基于第二个DataFrame匹配值填充空白列？

我有两个dataframe：data和glossary。data包含4个已有值的列（Code1、Code2、Code3、Code4）以及4个空白列（CodeA、CodeB、CodeC、CodeD）；glossary中的值均唯一，但在data中可能重复出现。

示例数据：

data <- data.frame(ID = c(1, 2, 3, 4, 5),
                   Code1 = c("team", "team", "crew", "group", "crew"),
                   Code2 = c("trust", "trust", "comms", "liking", "comms"),
                   Code3 = c("virtual", "virtual", "intact", "hybrid", "intact"),
                   Code4 = c("pooled", "pooled", "seqent", "recip", "sequent"),
                   CodeA = NA,
                   CodeB = NA,
                   CodeC = NA,
                   CodeD = NA)

glossary <- data.frame(CodeA = c("team", "crew", "group"),
                       CodeB = c("trust", "comms", "liking"),
                       CodeC = c("virtual", "intact", "adhoc"),
                       CodeD = c("pooled", "intensive", "recip"))

我需要通过data$Code1与glossary$CodeA的匹配关系，将glossary中的对应值填充到data的CodeA-CodeD列中。其中Code1与CodeA完全一致，但Code2-4与CodeB-D可能存在差异（例如data中Code3为"hybrid"时，对应的CodeC需要填充glossary里的"adhoc"）。

预期最终结果：

data <- data.frame(ID = c(1, 2, 3, 4, 5),
                   Code1 = c("team", "team", "crew", "group", "crew"),
                   Code2 = c("trust", "trust", "comms", "liking", "comms"),
                   Code3 = c("virtual", "virtual", "intact", "hybrid", "intact"),
                   Code4 = c("pooled", "pooled", "seqent", "recip", "sequent"),
                   CodeA = c("team", "team", "crew", "group", "crew"),
                   CodeB = c("trust", "trust", "comms", "liking", "comms"),
                   CodeC = c("virtual", "virtual", "intact", "adhoc", "intact"),
                   CodeD = c("pooled", "pooled", "intensive", "recip", "intensive"))

我尝试过用dplyr的match和mutate方法，但没找到能一次性优雅填充这四列的方案。

用dplyr的left_join是最直接优雅的方案，因为glossary里的CodeA和data的Code1是一对一的匹配关系，连接后直接替换空白列即可：

library(dplyr)

# 连接数据集并填充空白列
data_filled <- data %>%
  # 按Code1和CodeA匹配连接
  left_join(glossary, by = c("Code1" = "CodeA")) %>%
  # 替换原空白列，连接后glossary的列会带y.前缀
  mutate(
    CodeA = Code1,  # Code1和CodeA完全一致，直接赋值
    CodeB = y.CodeB,
    CodeC = y.CodeC,
    CodeD = y.CodeD
  ) %>%
  # 移除连接生成的临时列
  select(-starts_with("y."))

# 查看结果
print(data_filled)

运行这段代码后，就能得到你想要的填充结果。如果想更简洁，也可以用rename_with去掉临时列的前缀，再直接覆盖原空白列：

data_filled <- data %>%
  left_join(glossary, by = c("Code1" = "CodeA")) %>%
  rename_with(~sub("y\\.", "", .), starts_with("y.")) %>%
  mutate(across(CodeA:CodeD, ~coalesce(., get(cur_column()))))

不过第一种写法更直观，便于理解和维护。

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