如何使用pivot_wider转置时避免重复值生成列表列？

含重复值的初始数据 #

dat0 <- 
structure(list(id = c("P1", "P1", "P1", "P1", "P1", "P1", "P1", 
"P1", "P1", "P1", "P2", "P2", "P2", "P2", "P2", "P2"), analyte = c("A", 
"A", "B", "B", "B", "B", "C", "C", "D", "D", "B", "B", "B", "B", 
"D", "D"), analyzer = c("X", "Y", "X", "Y", "X", "Y", "X", "Y", 
"X", "Y", "X", "Y", "X", "Y", "X", "Y"), result = c(0.7, 0.9, 
1.26, 1.23, 1.24, 1.22, 5.7, 5.3, 4.1, 4.2, 1.22, 1.23, 1.21, 
1.22, 4.4, 4.5)), row.names = c(NA, -16L), class = c("tbl_df", 
"tbl", "data.frame")) 

初始数据中，部分id+analyte+analyzer组合存在重复的result值，直接使用pivot_wider会生成列表列并抛出警告。

当前执行结果及问题 #

执行以下代码：

dat1 <- dat0 %>% 
  pivot_wider(names_from = analyzer, values_from = result)  

会收到警告：

Values from result are not uniquely identified; output will contain list-cols.
• Use values_fn = list to suppress this warning.
• Use values_fn = {summary_fun} to summarise duplicates.
• Use the following dplyr code to identify duplicates.
{data} |>
dplyr::summarise(n = dplyr::n(), .by = c(id, analyte, analyzer)) |>
dplyr::filter(n > 1L)

得到的结果中，X和Y列是列表格式，不符合需求。

期望输出 #

希望转置后保留所有重复值，每个重复的测量结果对应单独一行，X和Y列分别显示对应analyzer的结果，示例格式如下：

解决方案 #

核心思路是给每组重复记录添加唯一标识符，让pivot_wider有明确的分组依据，避免生成列表列。具体步骤如下：

1. 给每个id+analyte+analyzer组合下的重复记录添加序号dup_id；
2. 以id+analyte+dup_id作为分组键执行转置；
3. 可选：删除辅助列dup_id。

代码实现：

library(tidyr)
library(dplyr)

# 给重复组添加唯一序号
dat_with_dup_id <- dat0 %>%
  group_by(id, analyte, analyzer) %>%
  mutate(dup_id = row_number()) %>%
  ungroup()

# 执行转置
final_dat <- dat_with_dup_id %>%
  pivot_wider(
    id_cols = c(id, analyte, dup_id),
    names_from = analyzer,
    values_from = result
  ) %>%
  select(-dup_id) # 若不需要保留dup_id可删除此句

# 查看结果
print(final_dat)

执行后即可得到符合期望的格式，所有重复值以单独行呈现，无列表列。

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