如何用一个DataFrame作为限值参考，评估另一DataFrame的水质检测结果？

数据背景 #

需利用包含分析物-基质组合限值的表格，评估水质检测结果是否超限：

• 分析物：As（砷）、Cd（镉）、Cr（铬）
• 基质规则：Fish（鱼类组织，仅湿重限值）；Floc（絮凝物，含干重TEC/PEC两个限值）

限值表生成代码 #

limits= matrix(c(30,33,9.79,
                 0.5,4.98,0.99,
                 0.88,111,43.4), nrow=3, ncol=3, byrow=TRUE)
colnames(limits) = c("wet_fish","dry_floc_PEC","dry_floc_TEC")
rownames(limits) = c("As","Cd","Cr")
limits=data.frame(limits)

限值表输出：

wet_fish dry_floc_PEC dry_floc_TEC
As    30.00        33.00         9.79
Cd     0.50         4.98         0.99
Cr     0.88       111.00        43.40

检测模拟数据生成代码 #

data = matrix(c("Floc","As","31","1",
                "Floc","Cd","4.99","0.1",
                "Floc","Cr","112","0.1",
                "Fish","As","3","34",
                "Fish","Cd","1","4.99",
                "Fish","Cr","1","50",
                "Floc","As","1","1",
                "Floc","Cd","0.04","0.002",
                "Floc","Cr","0.08","0.008",
                "Fish","As","0.002","0.2",
                "Fish","Cd","0.0005","0.05",
                "Fish","Cr","0.001","5"), ncol=4, byrow=T)

colnames(data) = c("Matrix","Analyte","ResultDry","ResultWet")
data = data.frame(data)

检测数据输出：

Matrix Analyte ResultDry ResultWet
1    Floc      As        31         1
2    Floc      Cd      4.99       0.1
3    Floc      Cr       112       0.1
4    Fish      As         3        34
5    Fish      Cd         1      4.99
6    Fish      Cr         1        50
7    Floc      As         1         1
8    Floc      Cd      0.04     0.002
9    Floc      Cr      0.08     0.008
10   Fish      As     0.002       0.2
11   Fish      Cd    0.0005      0.05
12   Fish      Cr     0.001         5

预期结果 #

匹配后需新增LimitWet、TECDry、PECDry限值列，以及Exceed超限判断列：

Matrix Analyte ResultDry ResultWet LimitWet TECDry PECDry Exceed
1    Floc      As        31         1       NA   9.79     33    TEC
2    Floc      Cd      4.99       0.1       NA   0.99   4.98    PEC
3    Floc      Cr       112       0.1       NA   43.4    111    PEC
4    Fish      As         3        34       30     NA     NA   Fish
5    Fish      Cd         1      4.99      0.5     NA     NA   Fish
6    Fish      Cr         1        50     0.88     NA     NA   Fish
7    Floc      As         1         1       NA   9.79     33   None
8    Floc      Cd      0.04     0.002       NA   0.99   4.98   None
9    Floc      Cr      0.08     0.008       NA   43.4    111   None
10   Fish      As     0.002       0.2       30     NA     NA   None
11   Fish      Cd    0.0005      0.05      0.5     NA     NA   None
12   Fish      Cr     0.001         5     0.88     NA     NA   None

当前问题 #

现有代码生成了3个独立的超限判断列，但嵌套ifelse合并时，因NA值导致逻辑判断失效，出现大量NA结果：

Data_final = limits %>%
  full_join(data, by=c("Analyte"="Analyte")) %>%
  mutate(ResultDry = as.numeric(ResultDry),
         ResultWet = as.numeric(ResultWet),
         wet_fish = as.numeric(wet_fish),
         dry_floc_TEC = as.numeric(dry_floc_TEC),
         dry_floc_PEC = as.numeric(dry_floc_PEC)) %>%
  mutate(Exceed_Fish = ifelse(Matrix=="Fish",ResultWet>wet_fish,NA)) %>%
  mutate(Exceed_Floc_TEC = ifelse(Matrix=="Floc",ResultDry>dry_floc_TEC,NA)) %>%
  mutate(Exceed_Floc_PEC = ifelse(Matrix=="Floc",ResultDry>dry_floc_PEC,NA))

# 合并列的错误尝试
Data_combined = Data_final %>%
  mutate(Exceed = ifelse(Exceed_Fish==TRUE,"Yes - Fish",
                         ifelse(Exceed_Floc_TEC==TRUE&Exceed_Floc_PEC==FALSE, "Yes - Floc TEC",
                                ifelse(Exceed_Floc_PEC==TRUE, "Yes - Floc PEC", "No"))))

优化解决方案 #

使用case_when替代嵌套ifelse，同时直接整合限值列生成，避免冗余中间列，代码更简洁逻辑更清晰：

library(dplyr)

# 合并数据并转换类型
final_data <- data %>%
  mutate(ResultDry = as.numeric(ResultDry),
         ResultWet = as.numeric(ResultWet)) %>%
  left_join(limits, by = "Analyte") %>%
  # 生成限值列
  mutate(
    LimitWet = ifelse(Matrix == "Fish", wet_fish, NA),
    TECDry = ifelse(Matrix == "Floc", dry_floc_TEC, NA),
    PECDry = ifelse(Matrix == "Floc", dry_floc_PEC, NA),
    # 超限判断：注意优先级，PEC判断需放在TEC前
    Exceed = case_when(
      Matrix == "Fish" & ResultWet > wet_fish ~ "Fish",
      Matrix == "Floc" & ResultDry > dry_floc_PEC ~ "PEC",
      Matrix == "Floc" & ResultDry > dry_floc_TEC ~ "TEC",
      TRUE ~ "None"
    )
  ) %>%
  # 整理列顺序，移除冗余列
  select(Matrix, Analyte, ResultDry, ResultWet, LimitWet, TECDry, PECDry, Exceed)

运行结果 #

Matrix Analyte ResultDry ResultWet LimitWet TECDry PECDry Exceed
1    Floc      As      31.0       1.0       NA   9.79   33.0    TEC
2    Floc      Cd       4.99      0.10       NA   0.99    4.98    PEC
3    Floc      Cr     112.0       0.1       NA  43.40  111.0    PEC
4    Fish      As       3.0      34.0      30.0     NA     NA   Fish
5    Fish      Cd       1.0       4.99       0.5     NA     NA   Fish
6    Fish      Cr       1.0      50.0       0.88     NA     NA   Fish
7    Floc      As       1.0       1.0       NA   9.79   33.0   None
8    Floc      Cd       0.04      0.002       NA   0.99    4.98   None
9    Floc      Cr       0.08      0.008       NA  43.40  111.0   None
10   Fish      As       0.002     0.20      30.0     NA     NA   None
11   Fish      Cd       0.0005    0.05       0.5     NA     NA   None
12   Fish      Cr       0.001     5.00       0.88     NA     NA   None

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