在R语言中通过遍历动态变量名创建新变量

我有一个包含混合问题与对应回答的数据框：Var1/Var2/Var3列存储问题编号（如Q1、Q2、Q3等，并非单列固定对应某个问题），a1/a2/a3是对应列的回答内容。示例数据如下：

have <- tribble(
  ~Var1,~a1,~Var2,~a2,~Var3,~a3,
  "Q1", "R1","Q3","R3","Q4","R4",
  "Q3", "R3","Q1","R1","Q3","R3",
  "Q3", "R3","Q1","R1","Q2","R3",
  "Q2", "R2","Q4","R4","Q1","R1"
) 

需要为每个目标问题（比如Q1、Q2、Q3）生成新列，提取该行中对应问题的回答。比如针对Q1的目标结果如下：

want <- tribble(
  ~Var1,~a1,~Var2,~a2,~Var3,~a3,~new_col,
  "Q1", "R1","Q3","R3","Q4","R4","R1",
  "Q3", "R3","Q1","R1","Q3","R3","R1",
  "Q3", "R3","Q1","R1","Q2","R3","R1",
  "Q2", "R2","Q4","R4","Q1","R1","R1"
)

目前我用case_when实现了单个问题的处理，但要针对多个问题重复操作时需要复制代码，想找更高效的批量实现方式。现有单个问题的代码：

want <- have %>%
  mutate(new_col =  case_when(
    Var1 == "Q1" ~ a1,
    Var2 == "Q1" ~ a2,
    Var3 == "Q1" ~ a3))

方法1：用across批量生成列 #

通过定义目标问题列表，利用across遍历每个问题，自动生成对应新列，无需重复编写case_when逻辑：

library(tidyverse)

# 定义需要处理的所有目标问题
target_questions <- c("Q1", "Q2", "Q3")

# 批量生成对应问题的结果列
result <- have %>%
  mutate(
    across(
      all_of(target_questions),
      ~ case_when(
        Var1 == .y ~ a1,
        Var2 == .y ~ a2,
        Var3 == .y ~ a3
      ),
      .names = "ans_{.y}"  # 新列命名格式，比如ans_Q1、ans_Q2
    )
  )

• 优势：代码简洁，逻辑清晰，适合问题数量较少的场景；.names参数可自定义新列名称。

方法2：数据重塑法（扩展性更强） #

当问题和回答列数量较多时，先将数据转为长格式处理，再转回宽格式，无需手动匹配Var与a的对应关系：

result <- have %>%
  # 新增行号用于后续合并
  mutate(row_id = row_number()) %>%
  # 将Var1/a1、Var2/a2等分组转成长格式
  pivot_longer(
    cols = -row_id,
    names_to = c("type", "num"),
    names_pattern = "(Var|a)(\\d)"
  ) %>%
  pivot_wider(names_from = type, values_from = value) %>%
  # 按原始行分组，将每个问题的回答转为宽列
  pivot_wider(
    id_cols = row_id,
    names_from = Var,
    values_from = a,
    names_prefix = "ans_"
  ) %>%
  # 合并回原始数据
  right_join(have %>% mutate(row_id = row_number()), by = "row_id") %>%
  select(-row_id)

• 优势：无需修改代码即可适配更多Var/a列，扩展性拉满，适合复杂场景。

内容的提问来源于stack exchange，提问作者Aaron R.