按条件查找num列NA值并按age分组填充对应num2值的R实现

原始数据框：

df <- data.frame(
    age  = c("age15","age15","age16","age16"),
    occo = c("ag","ag","uuse","ag"),
    occn = c("tw","uuse","use","uuse"),
    num  = c(12,NA,567,NA),
    occo2 = c("ag","use","ag","use"),
    occn2 = c("tw","use","tw","use"), 
    num2  = c(2,45,67,789)
)

需求说明 #

筛选出满足occo == "ag" & occn == "uuse"的num列NA单元格，按age分组后，将同组内满足occo2 == "ag" & occn2 == "tw"的num2值填充到对应NA位置（如age15组填充2，age16组填充67），预期结果如下：

df <- data.frame(
    age  = c("age15","age15","age16","age16"),
    occo = c("ag","ag","uuse","ag"),
    occn = c("tw","uuse","use","uuse"),
    num  = c(12,2,567,67),
    occo2 = c("ag","use","ag","use"),
    occn2 = c("tw","use","tw","use"), 
    num2  = c(2,45,67,789)
)

用户尝试的代码（待修正） #

df2 <- df %>%
 group_by(age) %>%
 mutate(num = case_when(
  occo == "ag" & occn == "uuse" ~ .[which(occo2 == "ag", occn2 == "tw"),][7]
))

正确实现代码 #

library(dplyr)

df2 <- df %>%
  group_by(age) %>%
  mutate(
    # 提取同组内符合occo2和occn2条件的num2值
    fill_value = first(num2[occo2 == "ag" & occn2 == "tw"]),
    # 定位目标NA单元格并填充
    num = case_when(
      occo == "ag" & occn == "uuse" & is.na(num) ~ fill_value,
      # 其他情况保留原num值
      TRUE ~ num
    )
  ) %>%
  # 移除临时填充值列
  select(-fill_value) %>%
  ungroup()

代码解释 #

1. 分组处理：按age分组，确保填充值仅来自同年龄组内的数据。
2. 提取填充值：用first()获取同组内符合occo2 == "ag" & occn2 == "tw"条件的第一个num2值（假设每组仅存在一个符合条件的记录）。
3. 精准替换NA：通过case_when定位需要填充的单元格（同时满足occo == "ag" & occn == "uuse"和num为NA），用提取到的填充值替换；其余情况保留原num值。
4. 清理临时列：删除用于存储填充值的临时列fill_value，并取消分组。

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