在R中按子组规则识别含值列并将指定范围空白填充为0

原始数据 #

df <- structure(list(Family = c("POP", "POP", "POP", "POP", "POP", 
"POP", "POP", "POP", "POP", "POP", "QOP", "QOP", "QOP", "QOP", 
"QOP", "QOP", "QOP", "QOP", "QOP", "QOP"), `Sub Family` = c("ABC", 
"ABC", "ABC", "ABC", "ABC", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ", 
"PRQ", "PRQ", "PRQ", "PRQ", "PRQ", "STV", "STV", "STV", "STV", 
"STV"), Size = c("1H", "2H", "3H", "4H", "5H", "1H", "2H", "3H", 
"4H", "5H", "1H", "2H", "3H", "4H", "5H", "1H", "2H", "3H", "4H", 
"5H"), Num1 = c(NA, NA, NA, NA, NA, 0.25, 0.25, 0.25, 0.25, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Num2 = c(0.5, 0.2, 0.3, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 1, NA, NA, NA, 
NA)), row.names = c(NA, -20L), class = c("tbl_df", "tbl", "data.frame"
))

处理规则 #

• 按Sub Family分组，针对Num1、Num2两列：
  • 若组内该列所有值都是NA，保持原列不变；
  • 若组内该列存在有效值，保留现有非NA值，将组内该列的NA替换为0。

解决方案代码 #

使用dplyr包实现分组逻辑：

library(dplyr)

processed_df <- df %>%
  group_by(`Sub Family`) %>%
  mutate(
    Num1 = if (all(is.na(Num1))) Num1 else replace(Num1, is.na(Num1), 0),
    Num2 = if (all(is.na(Num2))) Num2 else replace(Num2, is.na(Num2), 0)
  ) %>%
  ungroup()

# 查看完整处理结果
print(processed_df, n = 20)

代码说明 #

1. group_by(Sub Family)：以子家族为分组依据；
2. mutate函数内对每一列做判断：
   • all(is.na(列名))检查当前分组内该列是否全为NA；
   • 若全为NA则直接返回原列，否则用replace函数将列中的NA替换为0；
3. ungroup()：取消分组，恢复普通数据框结构。

处理后关键变化 #

• XYZ组的Num1列：5H位置的NA被替换为0，其余有效值保留；
• ABC组的Num2列：4H、5H的NA被替换为0，1H-3H的有效值保留；
• STV组的Num2列：2H-5H的NA被替换为0，1H的有效值保留；
• ABC组的Num1、PRQ组的Num1和Num2因全为NA，保持原样。

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