如何在R语言中按ID分组匹配列值填充DataFrame的NA

问题描述 #

现有一个包含NA值的DataFrame，B列存在缺失值。需求是：在每个ID分组内，当某行的D列值与同组内另一行的C列值匹配时，用匹配行的E列值填充当前行B列的NA。

原始数据 #

df <- structure(list(ID = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2), A = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 
10L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L), B = c(1L, NA, 
3L, 4L, NA, 6L, NA, 8L, 9L, NA, 1L, NA, 3L, 4L, NA, 6L, NA, 8L, 
9L, NA), C = c("A1", "B1", "C1", "D1", "E1", "A2", "B2", "C2", 
"D2", "E2", "A1", "B1", "C1", "D1", "E1", "A2", "B2", "C2", "D2", 
"E2"), D = c("A1", "E1", "C1", "D1", "B1", "A2", "E2", "C2", 
"D2", "B2", "A1", "E1", "C1", "D1", "B1", "A2", "E2", "C2", "D2", 
"B2"), E = c(12L, 11L, 10L, 9L, 8L, 7L, 6L, 5L, 4L, 3L, 12L, 
11L, 10L, 9L, 8L, 7L, 6L, 5L, 4L, 3L)), row.names = c(NA, -20L
), class = "data.frame")

解决方案 #

使用dplyr包进行分组处理，核心思路是为每个ID分组创建C到E的映射关系，再通过D列匹配映射值填充B列的NA：

library(dplyr)

df_filled <- df %>%
  group_by(ID) %>%
  mutate(
    # 构建C与E的映射：用C作为名称，E作为对应值
    c_e_map = setNames(E, C),
    # 填充B列NA：若B为NA，用D匹配映射表取对应E值，否则保留原B
    B = ifelse(is.na(B), c_e_map[D], B)
  ) %>%
  ungroup() %>%
  select(-c_e_map) # 移除临时映射列

验证结果 #

运行上述代码后，得到的df_filled即为期望输出，结构如下：

structure(list(ID = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2), A = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 
10L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L), B = c(1, 8, 3, 
4, 11, 6, 3, 8, 9, 6, 1, 8, 3, 4, 11, 6, 3, 8, 9, 6), C = c("A1", 
"B1", "C1", "D1", "E1", "A2", "B2", "C2", "D2", "E2", "A1", "B1", 
"C1", "D1", "E1", "A2", "B2", "C2", "D2", "E2"), D = c("A1", 
"E1", "C1", "D1", "B1", "A2", "E2", "C2", "D2", "B2", "A1", "E1", 
"C1", "D1", "B1", "A2", "E2", "C2", "D2", "B2"), E = c(12L, 11L, 
10L, 9L, 8L, 7L, 6L, 5L, 4L, 3L, 12L, 11L, 10L, 9L, 8L, 7L, 6L, 
5L, 4L, 3L)), row.names = c(NA, -20L), class = "data.frame")

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