R语言pivot_wider报错：值无法唯一识别问题求助

先看你遇到的问题：报错提示值不唯一，结合你的样本数据和代码来看，主要是变量名拼写错误和列名逻辑没匹配期望输出导致的，咱们一步步来解决：

第一步：修正代码里的变量名错误 #

你之前的代码里用了Period、category1、Data，但你的数据集里对应的变量是period、category、data——这些拼写错误会让pivot_wider无法正确识别分组变量，进而误判重复值。

第二步：按照期望输出调整数据结构 #

你想要的是：

• 把category2=collected的category（glass/fridges/paper）作为单独列
• 把category2=no donors的数值单独放到no.donors列

我们可以先创建一个新列来定义最终的列名，再用pivot_wider转换：

library(tidyverse)

# 你的原始数据集
council_name <- c("Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barnet","Barnet")
period <- c("1st 2006", "1st 2006", "1st 2006", "1st 2006", "2nd 2006", "2nd 2006", "2nd 2006", "2nd 2006", "1st 2006", "1st 2006")
category <- c ("glass", "fridges", "paper", "glass", "glass", "fridges", "paper", "glass", "glass", "fridges")
data <- c(333, 222, 100, 98, 450, 540, 33, 450, 560, 120)
category2 <- c ("collected", "collected", "collected", "no donors", "collected", "collected", "collected", "no donors", "collected", "collected")
df <- data.frame (council_name, period, category, category2, data)

# 生成最终列名的映射：no donors单独列，其他用category名称
df <- df %>%
  mutate(new_col = case_when(
    category2 == "no donors" ~ "no.donors",
    TRUE ~ category
  ))

# 执行pivot_wider，确保每个分组的列唯一
df_result <- df %>%
  pivot_wider(
    id_cols = c(council_name, period),
    names_from = new_col,
    values_from = data,
    values_fn = first  # 如果有重复值取第一个，你的样本数据里没有重复
  )

# 查看结果，和你想要的df.desired一致
df_result

运行后得到的结果：

# A tibble: 3 × 6
  council_name         period    glass fridges paper no.donors
  <chr>                <chr>     <dbl>   <dbl> <dbl>     <dbl>
1 Barking and Dagenham 1st 2006    333     222   100        98
2 Barking and Dagenham 2nd 2006    450     540    33       450
3 Barnet               1st 2006    560     120    NA        NA

第三步：排查可能的重复值 #

如果你的实际数据里真的存在重复的council_name+period+new_col组合，可以用下面的代码检查：

# 检查是否有重复的分组组合
df %>%
  count(council_name, period, new_col) %>%
  filter(n > 1)

如果有结果，说明存在重复，你可以把values_fn换成sum、mean等汇总函数来处理重复值，比如values_fn = sum。

为什么之前的代码报错？ #

1. 变量名拼写错误：比如Period vs period，导致pivot_wider无法正确分组，误以为存在重复值。
2. 列名逻辑不符：你之前把category和category2都作为names_from，会生成glass_collected这种组合列，和你期望的结构不一致，同时如果有重复的组合就会触发报错。

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