在R中基于多条件将列值迁移至目标列的数据清洗需求

1. 模拟符合场景的数据集 #

先复现你描述的典型数据结构，方便后续演示操作：

library(tidyverse)

set.seed(123)
# 3个参与者，每人参与2-3个项目，对应P1-P5的voice评分
df <- tibble(
  Name = rep(c("Alice", "Bob", "Charlie"), each = 3),
  `Project Name` = rep(c("ProjA", "ProjB", "ProjC"), 3),
  `Project Number` = sample(paste0("P", 1:5), 9, replace = TRUE),
  P1_voice = sample(1:5, 9, replace = TRUE),
  P2_voice = sample(1:5, 9, replace = TRUE),
  P3_voice = sample(1:5, 9, replace = TRUE),
  P4_voice = sample(1:5, 9, replace = TRUE),
  P5_voice = sample(1:5, 9, replace = TRUE)
)

2. 核心匹配迁移方法 #

方法一：行级直接匹配提取（快速简洁） #

利用rowwise()+get()组合，直接根据每行的项目编号，提取对应*_voice列的数值到目标列：

df_processed <- df %>%
  rowwise() %>%
  # 拼接项目编号与_voice，提取对应列值到T2_voice1
  mutate(T2_voice1 = get(paste0(`Project Number`, "_voice")), .after = `Project Number`) %>%
  ungroup() %>%
  # 处理T2_voice2：假设为同一参与者的第二个项目评分，可根据实际规则调整
  group_by(Name) %>%
  mutate(
    project_order = row_number(),
    T2_voice2 = ifelse(project_order == 2, T2_voice1, NA)
  ) %>%
  ungroup() %>%
  # 保留指定列，按需调整
  select(Name, `Project Name`, `Project Number`, T2_voice1, T2_voice2)

方法二：宽转长再匹配（灵活适配复杂规则） #

如果原数据是宽格式（单参与者一行对应多项目评分），先转长格式完成匹配，再转回目标结构：

# 把所有*_voice列转成长格式，拆分项目编号与评分
voice_long <- df %>%
  select(Name, starts_with("P") & ends_with("_voice")) %>%
  pivot_longer(
    cols = starts_with("P"),
    names_to = "Project Number",
    values_to = "voice_score",
    names_transform = list(`Project Number` = ~str_remove(., "_voice"))
  )

# 合并回原数据，匹配对应项目的评分
df_processed <- df %>%
  select(-starts_with("P")) %>%
  left_join(voice_long, by = c("Name", "Project Number")) %>%
  rename(T2_voice1 = voice_score) %>%
  # 按需求生成T2_voice2，示例为同一参与者的第二个项目评分
  group_by(Name) %>%
  mutate(T2_voice2 = nth(T2_voice1, 2)) %>%
  ungroup() %>%
  select(Name, `Project Name`, `Project Number`, T2_voice1, T2_voice2)

3. 规则调整说明 #

• 若项目编号是纯数字（如1而非P1），只需修改paste0的拼接逻辑（比如paste0("P", Project Number, "_voice")）
• 若T2_voice2是指定项目编号的评分，可替换为ifelse(Project Number == "P3", T2_voice1, NA)这类逻辑

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