如何合并同名行并按年份创建列：R语言销售数据聚合问题

需求说明 #

需将原始销售数据聚合，展示2013-2023年各客户的历史销售总况。原始数据每行包含公司名称、零件编号、销售年份、销量，存在大量重复行（单发票对应一行），最终需将同一公司的多行数据合并为一行，各年份列为对应总销量，无销售记录的年份填0。

原始数据示例 #

Company_1      2013     part_number_1      quantity_1
    Company_1      2013     part_number_2      quantity_2
    Company_1      2015     part_number_1      quantity_3
    Company_2      2013     part_number_3      quantity_4
    Company_2      2016     part_number_4      quantity_5

期望输出格式 #

现有代码问题 #

最初代码仅完成了数据重命名和列选择，未实现分组聚合与行列转换：

sales_data_clean <- sales_data_raw %>% 
  rename(part_number = Base,
         year = Year,
         company_name = `Company Name`,
         quantity = Quantity)

sales_data_grouped <- sales_data_clean %>% 
  select(company_name, part_number, year, quantity)

后续更新代码尝试聚合，但存在两个问题：一是空值显示为1而非0；二是未强制生成2013-2023的全部年份列：

library(readxl)
library(tidyverse)

sales_data_raw <- data.frame(
  year = c(2010, 2010, 2011, 2012, 2016, 2016, 2017, 2019),
  company_name = c("Company A", "Company B", "Company B", "Company C", "Company D", "Company E", "Company E", "Company E"),
  part_number = c("3200", "619", "619", "LR20", "O8M", "BA-10", "BA-10", "BA-10"),
  quantity = c(650, 69000, 31000, 500, 1000, 402, 1768, 6098)
)

sales_data_grouped <- sales_data_raw %>% 
  filter(year%in%2010:2023) %>% 
  select(company_name, year, quantity) %>% 
  group_by(company_name, year) %>% 
  summarize(total=sum(quantity, 
  na.rm = T)) %>% 
  spread(year, total, fill = T)

修正方案 #

使用pivot_wider替代旧版的spread，同时指定完整的目标年份序列，确保所有2013-2023的年份列都被生成，缺失值填充为0：

library(tidyverse)

# 测试数据保持不变
sales_data_raw <- data.frame(
  year = c(2010, 2010, 2011, 2012, 2016, 2016, 2017, 2019),
  company_name = c("Company A", "Company B", "Company B", "Company C", "Company D", "Company E", "Company E", "Company E"),
  part_number = c("3200", "619", "619", "LR20", "O8M", "BA-10", "BA-10", "BA-10"),
  quantity = c(650, 69000, 31000, 500, 1000, 402, 1768, 6098)
)

# 修正后的聚合代码
sales_data_grouped <- sales_data_raw %>% 
  # 过滤目标年份2013-2023
  filter(year %in% 2013:2023) %>% 
  # 按公司和年份分组，计算年度总销量
  group_by(company_name, year) %>% 
  summarize(total_quantity = sum(quantity, na.rm = TRUE)) %>% 
  ungroup() %>% 
  # 行列转换，强制生成所有目标年份列，缺失值填0
  pivot_wider(
    names_from = year,
    values_from = total_quantity,
    values_fill = 0,
    names_seq = 2013:2023
  )

关键修正点 #

1. 替换spread为pivot_wider：spread已被tidyverse弃用，pivot_wider功能更灵活，支持强制生成指定列。
2. 修正填充值：将fill = T改为values_fill = 0，R中TRUE等价于1，这是之前空值显示为1的核心原因。
3. 指定完整年份序列：通过names_seq = 2013:2023确保所有目标年份列都被生成，无论该年份是否有销售数据。
4. 过滤正确年份：根据需求将过滤范围改为2013:2023，匹配"过去10年"的业务场景。

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