如何按PREGID将含多列重复行的长格式DataFrame转宽格式？

问题背景 #

将长格式DataFrame按PREGID分组转宽时，错误生成了仅对角线有值的大型矩阵，需要将每组内的多条记录转为同一行的多列（带_1/_2后缀）。

原始数据（前10行） #

structure(list(PREGID = structure(c(5133081, 5133081, 5133151, 5133151, 5133261, 5133261, 5133281, 5133281, 5133581, 5133581), label = "pregnancy ID", format.sas = "Z"), AliquotID = c(4776236, 4776237, 4791547, 4791548, 4770026, 5239746, 4778145, 4778146, 4748325, 4748326), Shelf = c("Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 44 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 20 (00010022)", "Shelf 20 (00010022)"), Rack = c("Rack 05224 ( ) - R1C4", "Rack 05224 ( ) - R1C4", "Rack 05224 ( ) - R1C5", "Rack 05224 ( ) - R1C5", "Rack 05224 ( ) - R1C3", "Rack 05511 ( ) - R4C1", "Rack 05224 ( ) - R1C3", "Rack 05224 ( ) - R1C3", "Rack 05230 ( ) - R3C4", "Rack 05230 ( ) - R3C4"), Row = c(3, 4, 7, 8, 9, 3, 8, 9, 5, 6), Column = c(7, 7, 4, 4, 5, 7, 3, 3, 7, 7), `Stock Number` = c(305349, 305350, 305403, 305404, 305255, 760854, 305234, 305235, 315751, 315752)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))

期望输出格式 #

PREGID    AliquotID_1  AliquotID_2    Shelf_1     Shelf_2         Rack_1           Rack_2              Row_1   Row_2 Column_1 Column_2 Stock Number_1 Stock Number_2
 5133081   4776236      4776327      "Shelf 19"    "Shelf 19"  "Rack 05224()-R1C4" "Rack 05224()-R1C4"      3       4      7       7        305349         305350
 5133151   4791547      4791548      "Shlef 19"    "Shelf 19"  "Rack 05224()-R1C5" "Rack 05224()-R1C5"      7       8      4       4        305403         305404

错误尝试代码 #

Inc.tmp.1 %>% select( PREGID, AliquotID, SA, Shelf, Rack, Row, Column, `Stock Number`) %>% 
pivot_wider(names_from = PREGID , values_from = c("AliquotID", "SA", "Shelf", "Rack", "Row", "Column","Draw_Date","Stock Number"))

解决方法 #

问题出在names_from = PREGID，这会把每个PREGID作为列名，导致宽表膨胀。正确做法是先给每个PREGID组内的记录分配序号，再以此序号作为列名后缀转宽：

基础转宽代码 #

library(tidyverse)

# 加载数据
df <- structure(list(PREGID = structure(c(5133081, 5133081, 5133151, 5133151, 5133261, 5133261, 5133281, 5133281, 5133581, 5133581), label = "pregnancy ID", format.sas = "Z"), AliquotID = c(4776236, 4776237, 4791547, 4791548, 4770026, 5239746, 4778145, 4778146, 4748325, 4748326), Shelf = c("Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 44 (00010022)", "Shelf 19 (00010022)", "Shelf 19 (00010022)", "Shelf 20 (00010022)", "Shelf 20 (00010022)"), Rack = c("Rack 05224 ( ) - R1C4", "Rack 05224 ( ) - R1C4", "Rack 05224 ( ) - R1C5", "Rack 05224 ( ) - R1C5", "Rack 05224 ( ) - R1C3", "Rack 05511 ( ) - R4C1", "Rack 05224 ( ) - R1C3", "Rack 05224 ( ) - R1C3", "Rack 05230 ( ) - R3C4", "Rack 05230 ( ) - R3C4"), Row = c(3, 4, 7, 8, 9, 3, 8, 9, 5, 6), Column = c(7, 7, 4, 4, 5, 7, 3, 3, 7, 7), `Stock Number` = c(305349, 305350, 305403, 305404, 305255, 760854, 305234, 305235, 315751, 315752)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))

# 生成组内行号并转宽
result <- df %>%
  group_by(PREGID) %>%
  mutate(row_num = row_number()) %>%
  ungroup() %>%
  pivot_wider(
    id_cols = PREGID,
    names_from = row_num,
    values_from = c(AliquotID, Shelf, Rack, Row, Column, `Stock Number`),
    names_glue = "{.value}_{row_num}"
  )

# 查看结果
print(result)

匹配期望格式的优化代码 #

如果需要清理Shelf和Rack中的冗余字符（比如去掉括号内的内容），可以添加文本处理步骤：

result <- df %>%
  group_by(PREGID) %>%
  mutate(row_num = row_number()) %>%
  ungroup() %>%
  # 清理文本格式
  mutate(
    Shelf = str_extract(Shelf, "Shelf \\d+"),
    Rack = str_replace_all(Rack, " \\( \\) - ", "-")
  ) %>%
  pivot_wider(
    id_cols = PREGID,
    names_from = row_num,
    values_from = c(AliquotID, Shelf, Rack, Row, Column, `Stock Number`),
    names_glue = "{.value}_{row_num}"
  )

代码说明 #

1. group_by(PREGID) %>% mutate(row_num = row_number())：给每个PREGID分组内的记录分配1、2...的序号，作为转宽时区分同组不同记录的标识。
2. pivot_wider参数：
   • id_cols = PREGID：指定PREGID作为行的唯一标识。
   • names_from = row_num：用组内行号作为列名后缀。
   • values_from：列出所有需要转宽的字段。
   • names_glue：控制最终列名格式为字段名_序号，和期望输出一致。

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