如何依据已有变量值按优先级为DataFrame新增edu_level变量？

问题背景 #

现有如下结构的多选学历DataFrame：

structure(list(A_levels = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
    College_Uni = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), CSEs = c(0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0), NVQ_HND_HNC = c(0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0), O_levels_GCSEs = c(1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1), Other_prof_qual = c(0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0), Prefer_not_to_answer = c(0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0), None_of_the_above = c(0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0), id = 5000:5010), row.names = c(NA, -11L), class = c("tbl_df", 
"tbl", "data.frame")) -> df

需要新增edu_level变量，映射规则及优先级如下：

当样本同时多选多个学历时，取最高优先级对应的edu_level（例如同时选College_Uni和A_levels时，edu_level取high）。

实现方法 #

方法1：使用dplyr::case_when（直观快捷） #

利用case_when的顺序判断特性，按优先级从高到低设置条件，满足前置条件则直接赋值，不再执行后续判断：

library(tidyverse)

df <- df %>%
  mutate(edu_level = case_when(
    # 最高优先级：只要College_Uni为1，赋值high
    College_Uni == 1 ~ "high",
    # 次优先级：A_levels或O_levels_GCSEs为1，赋值medium
    A_levels == 1 | O_levels_GCSEs == 1 ~ "medium",
    # 较低优先级：任意低学历变量为1，赋值low
    CSEs == 1 | NVQ_HND_HNC == 1 | Other_prof_qual == 1 ~ "low",
    # 特殊情况：选择保密或无对应学历，赋值NA
    Prefer_not_to_answer == 1 | None_of_the_above == 1 ~ NA_character_,
    # 其他情况（如全0）也赋值NA
    TRUE ~ NA_character_
  ))

方法2：使用长格式映射（扩展性强） #

适合后续需要调整映射规则的场景，只需修改映射表即可：

1. 定义优先级映射表

edu_map <- tibble(
  variable = c("College_Uni", "A_levels", "O_levels_GCSEs", 
               "CSEs", "NVQ_HND_HNC", "Other_prof_qual",
               "Prefer_not_to_answer", "None_of_the_above"),
  edu_level = c("high", "medium", "medium", "low", "low", "low", NA, NA),
  priority = c(3, 2, 2, 1, 1, 1, 0, 0) # 数字越大优先级越高
)

2. 转长格式匹配映射，提取最高优先级结果

df_edu <- df %>%
  # 将宽格式转长格式，保留id和变量值
  pivot_longer(cols = -id, names_to = "variable", values_to = "value") %>%
  # 筛选出值为1的有效选项
  filter(value == 1) %>%
  # 匹配映射表的edu_level和优先级
  left_join(edu_map, by = "variable") %>%
  # 按id分组，取每组优先级最高的记录
  group_by(id) %>%
  slice_max(priority, n = 1, with_ties = FALSE) %>%
  ungroup() %>%
  # 保留需要的列
  select(id, edu_level)

# 将结果合并回原数据框
df <- df %>%
  left_join(df_edu, by = "id")

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