R语言tidyr::pivot_wider多值长数据转多列宽表问题求解

问题背景 #

• 现有30万行数据框，核心字段为NAME（姓名）、SUBJECT（科目），需将长格式数据转为宽格式：同一科目下存在的不同年份多条记录，需横向并列排布。
• 直接调用tidyr::pivot_wider无法得到预期结果。
• 示例数据如下：

DF <- data.frame(
  NAME = c("ABC", "ABC", "DEF", "ABC", "ABC", "ABC", "DEF", "ABC", "DEF", "ABC", "DEF", "DEF", "DEF", "DEF", "DEF", "DEF", "ABC"), 
  SUBJECT = c("MATHS", "LANGUAGE 1", "LANGUAGE 1", "LANGUAGE 2","LANGUAGE 2","LANGUAGE 2","LANGUAGE 2", "SCIENCE", "SCIENCE", "HISTORY", "PE", "ENVIRONMENT", "COMPUTERS", "COMPUTERS", "COMPUTERS", "BIOLOGY", "SANSKRIT"), 
  YEAR = c("2010", "2011", "2012", "2013", "2014", "2015", "2013", "2015", "2016", "2016", "2017", "2015", "2016", "2017", "2018", "2015", "2013"), 
  MARKS = c("45", "48", "47", "44", "48", "46", "42", "42", "43", "37", "42", "43", "42", "41", "44", "41", "44"), 
  MAXIMUM = c("46", rep("50", 5), "45", "50", rep("45", 9))
)

示例数据预览：

失败原因 #

直接调用pivot_wider时，函数无法自动区分同一NAME+SUBJECT分组下的多条记录，会默认将同组值合并为列表列，无法实现横向拆分并排的效果。

实现代码 #

核心思路是先按NAME、SUBJECT分组，给同组内的记录按年份排序生成序号，作为区分同科目多条记录的标识，再执行宽表转换：

library(dplyr)
library(tidyr)

wide_result <- DF %>%
  # 按姓名、科目分组，按年份升序为同组记录生成序号
  group_by(NAME, SUBJECT) %>%
  mutate(record_id = row_number(YEAR)) %>%
  ungroup() %>%
  # 执行宽表转换
  pivot_wider(
    id_cols = NAME,
    names_from = c(SUBJECT, record_id),
    values_from = c(YEAR, MARKS, MAXIMUM),
    names_vary = "slowest" # 保证同一科目同一条记录的年份、分数、满分字段相邻排列
  )

效果说明 #

• 转换后每一行对应唯一的NAME值
• 同一科目的多条记录按年份从早到晚依次排布，每一组包含YEAR、MARKS、MAXIMUM三个相邻字段
• 无对应记录的单元格自动填充为NA
• 该方法对30万行规模的数据运行效率足够，无额外性能瓶颈

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