R语言：基于多列条件创建新列的高效实现方法

问题背景 #

现有如下DataFrame：

DATA <- data.frame(
    CARS = c("PA", "PI", "PA", "PI", "PA", "PA", "PI", "PI"),
    EYE_SIGHT= c("GOOD", "GOOD", "POOR", "POOR", "GOOD", "POOR", "GOOD", "GOOD"))

需要基于以下规则创建NEW_COL：

• EYE_SIGHT = "GOOD" 且 CARS = "PA" → 0
• EYE_SIGHT = "GOOD" 且 CARS = "PI" → 1
• EYE_SIGHT = "POOR" 且 CARS = "PA" → 3
• EYE_SIGHT = "POOR" 且 CARS = "PI" → 4

原实现代码（小数据集可用）：

library(dplyr)
Data1 <- DATA %>%
    mutate(NEW_COL = case_when(EYE_SIGHT == "GOOD"  & CARS == "PA" ~ 0, 
                               EYE_SIGHT == "GOOD"  & CARS == "PI" ~ 1, 
                               EYE_SIGHT == "POOR"  & CARS == "PA" ~ 3, 
                               EYE_SIGHT == "POOR"  & CARS == "PI" ~ 4))

运行输出：

CARS EYE_SIGHT NEW_COL
1   PA      GOOD       0
2   PI      GOOD       1
3   PA      POOR       3
4   PI      POOR       4
5   PA      GOOD       0
6   PA      POOR       3
7   PI      GOOD       1
8   PI      GOOD       1

但面对大数据集时，上述方法效率不足，以下是几种更高效的实现方案：

方案1：因子映射 + 数值替换 #

将两个变量组合成复合键，直接通过预定义的映射向量取值，避免多次条件判断，速度更快：

# 创建复合键
DATA$key <- paste(DATA$EYE_SIGHT, DATA$CARS, sep = "_")
# 定义映射规则
map_vec <- c("GOOD_PA" = 0, "GOOD_PI" = 1, "POOR_PA" = 3, "POOR_PI" = 4)
# 生成新列
DATA$NEW_COL <- map_vec[DATA$key]
# 可选：删除临时key列
DATA$key <- NULL

如果用dplyr简化实现：

library(dplyr)
DATA <- DATA %>%
    mutate(key = paste(EYE_SIGHT, CARS, sep = "_"),
           NEW_COL = case_match(key, 
                               "GOOD_PA" ~ 0, 
                               "GOOD_PI" ~ 1, 
                               "POOR_PA" ~ 3, 
                               "POOR_PI" ~ 4)) %>%
    select(-key)

方案2：使用data.table包（大数据集最优选择） #

data.table的底层优化在处理百万级以上数据时，效率远高于常规dplyr实现，有两种方式：

方式1：合并映射表 #

library(data.table)
setDT(DATA)
# 定义映射表
map_table <- data.table(EYE_SIGHT = c("GOOD", "GOOD", "POOR", "POOR"),
                        CARS = c("PA", "PI", "PA", "PI"),
                        NEW_COL = c(0,1,3,4))
# 关联生成新列
DATA <- map_table[DATA, on = .(EYE_SIGHT, CARS)]

方式2：直接条件赋值 #

library(data.table)
setDT(DATA)[, NEW_COL := 0L]
DATA[EYE_SIGHT == "GOOD" & CARS == "PI", NEW_COL := 1L]
DATA[EYE_SIGHT == "POOR" & CARS == "PA", NEW_COL := 3L]
DATA[EYE_SIGHT == "POOR" & CARS == "PI", NEW_COL := 4L]

（注：用L后缀指定整数类型，减少类型转换开销，进一步提升效率）

方案3：矩阵索引法 #

将分类变量转换为整数编码，通过预定义的映射矩阵直接取值，适合变量类别较少的场景：

# 将分类变量转换为整数编码
eye_code <- as.integer(factor(DATA$EYE_SIGHT, levels = c("GOOD", "POOR")))
car_code <- as.integer(factor(DATA$CARS, levels = c("PA", "PI")))
# 构建映射矩阵
map_matrix <- matrix(c(0,1,3,4), nrow = 2, ncol = 2)
# 通过索引取值生成新列
DATA$NEW_COL <- map_matrix[cbind(eye_code, car_code)]

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