如何优化用mutate处理多if/ifelse的水质数据QA/QC标记逻辑？

需求背景 #

我有一个包含多项水质测量数据的dataframe，需要根据不同分析物（Ammonia、NOx、Orthophosphate、Urea、Total Nitrogen、Total Phosphorous）的阈值完成QA/QC标记，标记需作为额外列存储（如"F1"、"F2"等），未来可能新增分析物或标记规则。目前的实现方式重复代码较多，希望得到更简洁且可扩展的优化方案。

原实现代码 #

ammonia_flaging <- function(value){
  if (value <= 0.01){
   "F1"
  } else if (value > 0.01 & value <= 0.02){
    "F2"
  } else {
    "x"
  }
}

nh3_flags <- subset(data, Analyte.Name == "Ammonia", select= c(Sample.ID, avgConc)) %>% 
  mutate(flags = map(.$avgConc, ammonia_flaging))

nitrate_flaging <- function(value){
  if (value <= 0.04){
    "F1"
  } else if (value > 0.04 & value <= 0.2){
    "F2"
  } else {
    "x"
  }
}

nox_flags <- subset(data, Analyte.Name == "NOx", select= c(Sample.ID, avgConc)) %>% 
  mutate(flags = map(.$avgConc, nitrate_flaging))

ortho_flaging <- function(value){
  if (value <= 1){
    "F1"
  } else if (value > 1 & value <= 3){
    "F2"
  } else {
    "x"
  }
}
ortho_flags <- subset(data, Analyte.Name == "Orthophosphate", select= c(Sample.ID, avgConc)) %>% 
  mutate(flags = map(.$avgConc, ortho_flaging))

tp_flaging <- function(value){
  if (value <= 1){
    "F1"
  } else if (value > 1 & value <= 5){
    "F2"
  } else {
    "x"
  }
}

tp_flags <- subset(data, Analyte.Name == "TP", select= c(Sample.ID, avgConc)) %>% 
  mutate(flags = map(.$avgConc, tp_flaging))

tn_flaging <- function(value){
  if (value < 0){
    "F3"
  }
  else {
    "x"
  }
}
tn_flags <- subset(data, Analyte.Name == "TN", select= c(Sample.ID, avgConc)) %>% 
  mutate(flags = map(.$avgConc, tn_flaging))

urea_flaging <- function(value){
  if (value < 0){
    "F3"
  }
  else{
    "x"
  }
}
urea_flags <- subset(data, Analyte.Name == "Urea", select= c(Sample.ID, avgConc)) %>% 
  mutate(flags = map(.$avgConc, urea_flaging))

all_flags <- rbind(ortho_flags, nox_flags, nh3_flags, tp_flags, tn_flags, urea_flags)

优化方案 #

方案一：直接用case_when向量化处理 #

这种方式最直观，代码量大幅减少，无需拆分合并数据，直接在原dataframe上生成标记列：

library(dplyr)

# 直接在原数据上生成标记列，无需拆分、合并
data_with_flags <- data %>%
  mutate(flags = case_when(
    # Ammonia 标记规则
    Analyte.Name == "Ammonia" & avgConc <= 0.01 ~ "F1",
    Analyte.Name == "Ammonia" & avgConc > 0.01 & avgConc <= 0.02 ~ "F2",
    # NOx 标记规则
    Analyte.Name == "NOx" & avgConc <= 0.04 ~ "F1",
    Analyte.Name == "NOx" & avgConc > 0.04 & avgConc <= 0.2 ~ "F2",
    # Orthophosphate 标记规则
    Analyte.Name == "Orthophosphate" & avgConc <= 1 ~ "F1",
    Analyte.Name == "Orthophosphate" & avgConc > 1 & avgConc <= 3 ~ "F2",
    # TP 标记规则
    Analyte.Name == "TP" & avgConc <= 1 ~ "F1",
    Analyte.Name == "TP" & avgConc > 1 & avgConc <= 5 ~ "F2",
    # TN、Urea 标记规则
    Analyte.Name %in% c("TN", "Urea") & avgConc < 0 ~ "F3",
    # 默认标记（未匹配任何规则）
    TRUE ~ "x"
  ))

方案二：用配置表管理规则（适合规则较多的场景） #

如果未来规则会频繁更新或新增，可把规则存入配置表，实现规则与代码分离，维护更方便：

library(dplyr)
library(rlang)

# 定义规则配置表，新增分析物/规则直接添加行
flag_rules <- tribble(
  ~Analyte.Name, ~condition, ~flag,
  "Ammonia",     "avgConc <= 0.01", "F1",
  "Ammonia",     "avgConc > 0.01 & avgConc <= 0.02", "F2",
  "NOx",         "avgConc <= 0.04", "F1",
  "NOx",         "avgConc > 0.04 & avgConc <= 0.2", "F2",
  "Orthophosphate", "avgConc <= 1", "F1",
  "Orthophosphate", "avgConc > 1 & avgConc <= 3", "F2",
  "TP",          "avgConc <= 1", "F1",
  "TP",          "avgConc > 1 & avgConc <= 5", "F2",
  "TN",          "avgConc < 0", "F3",
  "Urea",        "avgConc < 0", "F3"
)

# 按分析物分组，动态应用规则生成标记
data_with_flags <- data %>%
  group_by(Analyte.Name) %>%
  mutate(flags = case_when(
    !!!map2(
      flag_rules$condition[flag_rules$Analyte.Name == cur_group()$Analyte.Name],
      flag_rules$flag[flag_rules$Analyte.Name == cur_group()$Analyte.Name],
      ~parse_expr(paste0(.x, ' ~ "', .y, '"'))
    ),
    TRUE ~ "x"
  )) %>%
  ungroup()

优化亮点 #

• 消除重复代码：无需为每个分析物单独定义函数，所有规则集中管理。
• 避免数据拆分合并：直接在原数据上处理，省去subset、rbind、merge等步骤，代码更简洁，运行效率更高。
• 扩展性强：新增分析物或规则时，只需在case_when中添加行（方案一）或在配置表中新增记录（方案二），无需修改多处代码。
• 向量化操作：用case_when替代map循环，处理大数据量时速度更快。

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