满足条件时用另一DataFrame列值填充目标列NA值

需要在满足以下条件时，将df2的列值复制到df1的对应列中：

• df1与df2的ID和BirthDate完全匹配
• df1的week2为NA
  此时将df2的week2a值替换df1的week2。

示例输入数据：

ID <- c(1,2,3,4,5)
BirthDate <- c("2022-01-01", "2022-01-02", "2022-03-04", "2022-04-05", "2022-06-03")
week2 <- c("Y","Y","NA","NA","Y")

df1 <- data.frame(ID, BirthDate, week2)

ID <- c(1,2,3,4,5)
BirthDate <- c("2022-01-01", "2022-01-02", "2022-03-04", "2022-04-05", "2022-06-03")
week2a <- c("NA","NA","P","P","NA")
df2 <- data.frame(ID, BirthDate, week2a)

期望输出：

ID  BirthDate week2
1  1 2022-01-01     Y
2  2 2022-01-02     Y
3  3 2022-03-04     P
4  4 2022-04-05     P
5  5 2022-06-03     Y

注意：示例数据中的"NA"是字符串而非R的缺失值，需先转换为真正的NA才能正确识别。

方法一：基础R实现 #

# 转换字符串"NA"为R缺失值
df1$week2[df1$week2 == "NA"] <- NA
df2$week2a[df2$week2a == "NA"] <- NA

# 通过ID和BirthDate匹配行索引
match_indices <- match(paste(df1$ID, df1$BirthDate), paste(df2$ID, df2$BirthDate))

# 替换满足条件的week2值
df1$week2[is.na(df1$week2)] <- df2$week2a[match_indices[is.na(df1$week2)]]

# 查看结果
print(df1)

方法二：dplyr包实现（更简洁） #

library(dplyr)

# 转换字符串"NA"为R缺失值
df1 <- df1 %>% mutate(week2 = na_if(week2, "NA"))
df2 <- df2 %>% mutate(week2a = na_if(week2a, "NA"))

# 左连接后替换缺失值
df1 <- df1 %>%
  left_join(df2, by = c("ID", "BirthDate")) %>%
  mutate(week2 = ifelse(is.na(week2), week2a, week2)) %>%
  select(-week2a)

# 查看结果
print(df1)

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