使用dplyr::mutate()重塑数据框：为对应subject添加percent列

先明确核心逻辑：从长数据中拆分出subject与percent的映射关系，再将其精准关联到对应score行，最后转换为宽格式。以下是具体实现步骤：

1. 示例数据（匹配你的数据结构即可） #

library(dplyr)
library(tidyr)

df_long <- tibble(
  subject = rep(c("math", "english"), each = 2),
  id = c("scoreQ1", "percentQ1", "scoreQ2", "percentQ2"),
  grade = c(85, 90, 78, 85)
)

2. 提取subject-percent映射表 #

筛选出id含percentQ的行，将grade重命名为percent，保留subject作为匹配键：

percent_map <- df_long %>%
  filter(grepl("percentQ", id)) %>%
  select(subject, percent = grade)

3. 为score行匹配对应percent值 #

筛选出score行，通过subject字段与映射表做左连接，自动填充对应科目的percent值：

df_merged <- df_long %>%
  filter(grepl("scoreQ", id)) %>%
  left_join(percent_map, by = "subject")

4. 转换为目标宽格式 #

用pivot_wider将长数据转成指定的宽格式结构：

df_wide <- df_merged %>%
  pivot_wider(
    names_from = id,
    values_from = grade,
    names_prefix = "score_"  # 可选：给score列加前缀优化命名
  ) %>%
  relocate(subject, percent, starts_with("score_"))  # 调整列顺序，让结构更清晰

关键注意点 #

之前匹配失败大概率是没建立正确的subject关联逻辑——直接用mutate不做分组/连接的话，极易把percent值错误广播到所有行。通过left_join按subject精准匹配，能确保每个科目对应的percent值完全对应。

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