非唯一键下，如何用另一DataFrame的值替换DataFrame中的NA？

以subject为匹配键，当df2的board和date列值为NA时，用df1中对应subject的board和date值进行替换。已知df1中subject（如vand）不唯一，但对应board和date值始终一致。

示例数据 #

df1 #

df1 <- structure(list(nature = c("sop", "dior", "coats", "sem", "wia", 
    "bodo"), subject = c("gank", "vand", "vand", 
    "jav", "vand", "haap"), board = c("REW", "EWW", "EWW", "SSD", 
    "EWW", "MMB"), date = c("2023-07-12", 
    "2023-06-09", "2023-06-09", 
    "2023-06-09", "2023-06-09", 
    "2023-03-05")), row.names = c(NA, -6L), class = c("tbl_df", 
    "tbl", "data.frame"))

df2 #

df2 <- structure(list(type = c("single", "couple", "couple", "couple", "couple", 
  "couple", "single", "couple", "couple", "couple"), name = c("ZIA", 
  "MIA", "lMIA", "LIA", 
  "LIA", "LIA", "DIA", 
  "LIA", "MIA", "SIA"
  ), subject = c("vand", "vank", "vank", 
  "jav", "tral", "twe", 
  "haap", "der", "leo", 
  "sdee"), board = c(NA, 
  "SSD", "REW", "EWW", "WWS, DDC", "SSD", 
  NA, "QQW", "XXD", "GGH"
  ), date = c(NA, "2023-07-03", "2023-07-03", 
  "2023-07-17", "2023-07-17", 
  "2023-01-16", NA, 
  "2023-07-17", "2023-06-08", 
  "2023-07-17")), class = "data.frame", row.names = c(NA, 
  -10L))

期望输出 #

df3 <- structure(list(type = c("single", "couple", "couple", "couple", "couple", 
  "couple", "single", "couple", "couple", "couple"), name = c("ZIA", 
  "MIA", "lMIA", "LIA", 
  "LIA", "LIA", "DIA", 
  "LIA", "MIA", "SIA"
  ), subject = c("vand", "vank", "vank", 
  "jav", "tral", "twe", 
  "haap", "der", "leo", 
  "sdee"), board = c("EWW", 
  "SSD", "REW", "EWW", "WWS, DDC", "SSD", 
  "MMB", "QQW", "XXD", "GGH"
  ), date = c("2023-06-09", "2023-07-03", "2023-07-03", 
  "2023-07-17", "2023-07-17", 
  "2023-01-16", "2023-03-05", 
  "2023-07-17", "2023-06-08", 
  "2023-07-17")), class = "data.frame", row.names = c(NA, 
  -10L))

解决方案 #

利用dplyr包实现，步骤如下：

1. 对df1去重，保留subject与board、date的唯一映射关系；
2. 将处理后的df1与df2按subject关联；
3. 使用coalesce函数，优先取df2的非NA值，NA值用df1对应的值替换。

代码实现：

library(dplyr)

# 整理df1为唯一映射表
df1_unique <- df1 %>%
  distinct(subject, board, date)

# 关联并替换NA
df3 <- df2 %>%
  left_join(df1_unique, by = "subject", suffix = c("_df2", "_df1")) %>%
  mutate(
    board = coalesce(board_df2, board_df1),
    date = coalesce(date_df2, date_df1)
  ) %>%
  select(-ends_with("_df2"), -ends_with("_df1")) # 移除临时列

运行代码后，输出的df3与期望结果完全一致。

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