如何使用pivot_wider展开数据集并保留每条重复记录

你想用pivot_wider把race转为列名，对应每条Count记录，但运行后所有Count被放进列表，还收到警告。数据集和报错如下：

library(tidyverse)
a <- structure(list(Count = c(1, 1, 3, 1, 2, 1, 2, 1, 3, 1, 1, 2, 
2, 1, 3, 3, 3, 5, 3, 3), race = c("L", "F", "W", "F", "F", "LF", 
"F", "F", "F", "F", "F", "F", "F", "S", "F", "F", "F", "F", "F", 
"F"), year = c("2012", "2013", "2013", "2013", "2013", "2013", 
"2013", "2012", "2013", "2013", "2012", "2013", "2013", "2013", 
"2013", "2013", "2013", "2013", "2013", "2013")), row.names = c(NA, 
20L), class = "data.frame")

a %>% pivot_wider(names_from = race, values_from = Count)

运行后得到含列表列的结果，并收到警告：

# A tibble: 2 x 6
  year  L         F          W         LF        S        
  <chr> <list>    <list>     <list>    <list>    <list>   
1 2012  <dbl [1]> <dbl [2]>  <NULL>    <NULL>    <NULL>   
2 2013  <NULL>    <dbl [14]> <dbl [1]> <dbl [1]> <dbl [1]>

Warning message:
Values from `Count` 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::group_by(year, race) %>%
  dplyr::summarise(n = dplyr::n(), .groups = "drop") %>%
  dplyr::filter(n > 1L)  

问题原因 #

同一个year分组下存在多条相同race的记录，pivot_wider无法确定这些重复分组的记录该如何合并，因此默认用列表存储多值。

解决方案 #

要保留原数据的20行结构，让每个race列对应本行的Count值（无值填0），需先给每行添加唯一标识，再执行pivot_wider，最后替换NA为0：

a %>%
  mutate(row_id = row_number()) %>%  # 添加唯一行号，确保分组唯一
  pivot_wider(names_from = race, values_from = Count) %>%
  select(-row_id) %>%  # 移除临时行号列
  mutate(across(c(L, F, W, LF, S), ~replace_na(.x, 0)))  # 将NA替换为0

输出说明 #

运行后会得到20行数据，每个race列对应原数据该行的Count值，没有对应race的位置填充为0，与你期望的输出结构一致。

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