在R中按detect_flag规则分组聚合DataFrame计算均值

先将你提供的数据导入为DataFrame：

df <- structure(list(Loc_name = c("U3R", "U3R", "U3R", "U3R", "U3R", 
"U3R", "HWY301", "HWY301", "HWY301", "HWY301", "HWY301", "HWY301", 
"U3R", "U3R", "U3R", "U3R", "U3R", "U3R", "HWY301", "HWY301", 
"HWY301", "HWY301", "HWY301", "HWY301"), `fish type` = c("bass", 
"bass", "bass", "catfish", "catfish", "catfish", "flathead", 
"flathead", "flathead", "bass", "bass", "bass", "bass", "bass", 
"bass", "catfish", "catfish", "catfish", "flathead", "flathead", 
"flathead", "bass", "bass", "bass"), Report_result_value = c(1.1, 
1.2, 1.3, 2.1, 2.2, 2.3, 3.1, 3.2, 3.3, 4.1, 4.2, 4.3, 1.1, 1.2, 
1.3, 2.1, 2.2, 2.3, 3.1, 3.2, 3.3, 4.1, 4.2, 4.3), Chemical_Name = c("Cs-137", 
"Cs-137", "Cs-137", "Cs-137", "Cs-137", "Cs-137", "Cs-137", "Cs-137", 
"Cs-137", "Cs-137", "Cs-137", "Cs-137", "SR-90", "SR-90", "SR-90", 
"SR-90", "SR-90", "SR-90", "SR-90", "SR-90", "SR-90", "SR-90", 
"SR-90", "SR-90"), detect_flag = c("Y", "Y", "Y", "N", "Y", "N", 
"N", "N", "N", "Y", "N", "N", "Y", "Y", "Y", "N", "Y", "N", "N", 
"N", "N", "Y", "N", "N")), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -24L))

方法1：使用dplyr（tidyverse生态） #

这是最直观的写法，适合新手理解，且无需循环，效率足够处理绝大多数常规数据：

library(dplyr)

result_df <- df %>%
  # 按指定的三个维度分组
  group_by(Loc_name, `fish type`, Chemical_Name) %>%
  # 按规则计算均值
  summarize(
    avg_value = if (any(detect_flag == "Y")) {
      mean(Report_result_value)
    } else {
      0
    },
    # 禁用分组自动取消，可选，若后续还要操作分组可保留
    .groups = "drop"
  )

# 查看结果
print(result_df)

关键逻辑说明： #

• any(detect_flag == "Y")：判断分组内是否存在至少一个检测标记为"Y"的记录
• 若存在，则计算该组所有Report_result_value的算术平均值；否则赋值为0
• 示例验证：HWY301+flathead+SR-90分组内所有detect_flag都是"N"，所以avg_value为0；HWY301+bass+Cs-137分组存在"Y"，均值为(4.1+4.2+4.3)/3 = 4.2，符合要求

方法2：使用data.table（超大数据场景更高效） #

如果你的数据量非常大（百万级以上），data.table的内存效率和运算速度会优于dplyr，写法如下：

library(data.table)

# 转换为data.table格式
dt <- as.data.table(df)

# 分组计算
result_dt <- dt[, .(
  avg_value = if (any(detect_flag == "Y")) mean(Report_result_value) else 0
), by = .(Loc_name, `fish type`, Chemical_Name)]

# 查看结果
print(result_dt)

两种方法都完全避免了循环，符合你对效率的要求，且逻辑清晰易维护。

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