在R中统计数据框指标超标次数（含PH区间标准）

先还原你的数据框：

wqdata <- structure(list(SYS_LOC_CODE = c("RM-1", "RM-1", "RM-1", "RM-1", 
"RM-2", "RM-2", "RM-2", "RM-2", "RM-3", "RM-3", "RM-3", "RM-3", 
"RM-4", "RM-4", "RM-4", "RM-4"), CHEMICAL_NAME = c("NITRATE", 
"POTASSIUM", "PH", "TURB", "NITRATE", "POTASSIUM", "PH", "TURB", 
"NITRATE", "POTASSIUM", "PH", "TURB", "NITRATE", "POTASSIUM", 
"PH", "TURB"), REPORT_RESULT_VALUE = c(7, 9, 10, 4, 8, 3, 7, 
2, 7, 14, 7, 5, 9, 55, 4, 4), STANDARD = c("14", "10", "6.5-8", 
"2", "14", "10", "6.5-8", "2", "14", "10", "6.5-8", "2", "14", 
"10", "6.5-8", "2")), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -16L))

步骤1：拆分PH的标准区间 #

先把PH的STANDARD字段拆分为下限和上限，其他分析物的上下限统一为原标准值：

library(dplyr)
library(tidyr)

wqdata_processed <- wqdata %>%
  separate(STANDARD, into = c("low", "high"), sep = "-", fill = "right", convert = TRUE) %>%
  mutate(
    high = ifelse(is.na(high), low, high),
    low = as.numeric(low),
    high = as.numeric(high)
  )

步骤2：定义超标判断规则 #

针对PH和其他分析物分别设置判断逻辑：

wqdata_processed <- wqdata_processed %>%
  mutate(
    is_exceed = case_when(
      CHEMICAL_NAME == "PH" ~ REPORT_RESULT_VALUE < low | REPORT_RESULT_VALUE > high,
      TRUE ~ REPORT_RESULT_VALUE > high
    )
  )

步骤3：汇总超标次数 #

按分析物分组统计超标次数：

exceed_summary <- wqdata_processed %>%
  group_by(CHEMICAL_NAME) %>%
  summarise(exceed_count = sum(is_exceed, na.rm = TRUE))

print(exceed_summary)

运行后输出结果：

# A tibble: 4 × 2
  CHEMICAL_NAME exceed_count
  <chr>                 <int>
1 NITRATE                   0
2 PH                        2
3 POTASSIUM                 2
4 TURB                      3

核心说明 #

• 用separate处理PH的区间标准，通过fill = "right"让非PH数据的上下限统一，简化判断逻辑
• PH的超标条件为结果小于区间下限或大于区间上限，其他分析物为结果超过标准值
• sum(is_exceed)直接统计TRUE的数量，即超标次数

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