如何用dplyr的case_when和mutate批量处理COL_开头多列

我用dplyr的case_when在数据框中创建NEW列，当前代码仅基于COL_1列实现判断逻辑，希望改成基于所有以COL_开头的列（示例中为4列），避免重复编写四次相同条件。

当前可运行代码：

library(dplyr)
set.seed(1)

# 生成示例数据
data <- data.frame(STRATUM_ID = c(rep("C19", 5), rep("C20", 15), rep("C21", 4)),
                   COL_1 = sample(c(rep("X", 3), rep("T", 2), rep("Y", 7), rep("Z", 5), rep("D", 5), rep("G", 2)), 24, replace = T),
                   COL_2 = sample(c(rep("T", 4), rep("G", 6), rep("Y", 3), rep("C", 2), rep("W", 6), rep("R", 3)), 24, replace = T),
                   COL_3 = sample(c(rep("G", 1), rep("F", 5), rep("D", 3), rep("Z", 7), rep("C", 3), rep("E", 5)), 24, replace = T),
                   COL_4 = sample(c(rep("E", 7), rep("G", 2), rep("Y", 7), rep("D", 5), rep("V", 1), rep("U", 2)), 24, replace = T))

# 基于COL_1创建NEW列
data <- data %>% mutate(NEW = case_when(
  STRATUM_ID == "C20" & COL_1 == "X" ~ "Class_A",
  STRATUM_ID == "C20" & COL_1 %in% c("C", "D", "E") ~ "Class_B",
  STRATUM_ID == "C20" & COL_1 %in% c("U", "V", "W", "Y") ~ "Class_C",
  STRATUM_ID == "C20" & COL_1 == "T" ~ "Class_D",
  STRATUM_ID == "C20" & COL_1 %in% c("G", "Z", "R") ~ "Class_E",
  STRATUM_ID == "C20" & COL_1 == "F" ~ "Class_F",
  STRATUM_ID == "C20" & is.na(COL_1) ~ "Unknown",
  TRUE ~ STRATUM_ID
))

之前尝试的两种写法均失败：

# 写法1
data <- data %>% mutate(test = case_when(
  STRATUM_ID == "C20" & grep("COL", colnames(data)) %in% c("C", "D", "E") ~ "CLASS_B"))

# 写法2
data <- data %>% mutate(test = case_when(
  STRATUM_ID == "C20" & vars(starts_with("COL")) %in% c("C", "D", "E") ~ "CLASS_B"))

注：实际数据集规模远大于示例，已简化问题。

方法1：dplyr原生方案（rowwise() + c_across()） #

按行处理所有COL_开头的列，将其合并为向量后应用判断逻辑。可先定义判断函数精简代码：

library(dplyr)

# 定义单值判断函数
get_class <- function(x) {
  case_when(
    x == "X" ~ "Class_A",
    x %in% c("C", "D", "E") ~ "Class_B",
    x %in% c("U", "V", "W", "Y") ~ "Class_C",
    x == "T" ~ "Class_D",
    x %in% c("G", "Z", "R") ~ "Class_E",
    x == "F" ~ "Class_F",
    is.na(x) ~ "Unknown",
    TRUE ~ NA_character_
  )
}

# 按行判断：只要任意COL列符合条件就返回对应类别
data <- data %>%
  rowwise() %>%
  mutate(NEW = case_when(
    STRATUM_ID != "C20" ~ STRATUM_ID,
    any(c_across(starts_with("COL")) == "X") ~ "Class_A",
    any(c_across(starts_with("COL")) %in% c("C", "D", "E")) ~ "Class_B",
    any(c_across(starts_with("COL")) %in% c("U", "V", "W", "Y")) ~ "Class_C",
    any(c_across(starts_with("COL")) == "T") ~ "Class_D",
    any(c_across(starts_with("COL")) %in% c("G", "Z", "R")) ~ "Class_E",
    any(c_across(starts_with("COL")) == "F") ~ "Class_F",
    any(is.na(c_across(starts_with("COL")))) ~ "Unknown",
    TRUE ~ STRATUM_ID
  )) %>%
  ungroup()

如果需要为每个COL列单独生成对应类别列，可使用across()：

# 为每个COL列生成独立的类别列
data <- data %>%
  mutate(across(starts_with("COL"), ~get_class(.x), .names = "{.col}_CLASS"))

方法2：使用purrr::pmap实现灵活行处理 #

适合需要更复杂行级逻辑的场景：

library(dplyr)
library(purrr)

data <- data %>%
  mutate(NEW = pmap_chr(select(., starts_with("COL")), ~{
    cols <- c(...)
    if (STRATUM_ID != "C20") return(STRATUM_ID)
    
    case_when(
      any(cols == "X") ~ "Class_A",
      any(cols %in% c("C", "D", "E")) ~ "Class_B",
      any(cols %in% c("U", "V", "W", "Y")) ~ "Class_C",
      any(cols == "T") ~ "Class_D",
      any(cols %in% c("G", "Z", "R")) ~ "Class_E",
      any(cols == "F") ~ "Class_F",
      any(is.na(cols)) ~ "Unknown",
      TRUE ~ STRATUM_ID
    )
  }))

失败写法的问题说明 #

• 第一种写法的grep("COL", colnames(data))返回的是列的索引（数字），不是列的实际值，无法和字符向量比较。
• 第二种写法的vars(starts_with("COL"))是dplyr的列选择语法，仅用于引用列，不能直接用来判断列的值。

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