按子类别汇总时间数据时pivot_wider报错，求可行解决方案

数据集 #

df <- structure(list(date = structure(c(19653, 19572, 19572, 19572, 
19572, 19572, 19752), class = "Date"), var_1 = c("def", "jkl", "abc", 
"abc", "ghi", "abc", "ghi"), subcategory = c(7, 8, 2, 4, 9, 9, 3), category = c("A", 
"B", "B", "B", "C", "F", "G"), location = c("a", "j", "j", "c", "h", 
"h", "d"), time = c("07:30:00", "00:30:00", "00:30:00", "01:30:00", 
"03:40:00", "03:30:00", "07:00:00"), month = c(10, 8, 8, 8, 8, 8, 8), year = c(2023, 
2023, 2023, 2023, 2023, 2023, 2023)), row.names = c(NA, -7L), class = c("tbl_df", 
"tbl", "data.frame"))

当前代码及错误 #

library(tidyverse)

df <- df %>% 
  mutate(time = seconds(lubridate::hms(time)))

df %>% 
  mutate(total = seconds_to_period(sum(seconds(lubridate::hms(time)))), 
         .by = category) %>%
  pivot_wider(names_from = subcategory, 
              values_from = time,
              names_prefix = "time_",
              values_fn = lubridate::hms) %>%
  select(order(colnames(.)))

错误信息：

Error in pivot_wider():
! Applying values_fn to time must result in a single summary value per key.
ℹ Applying values_fn resulted in a vector of length 2.
Run rlang::last_trace() to see where the error occurred.
There were 50 or more warnings (use warnings() to see the first 50)

预期输出 #

# A tibble: 3 × 4
  category time_2     time_3     time_4      total
  <chr>    <Period>   <Period>   <Period>   <Period>
1 A        1H 0M 0S   NA         2H 30M 0S  3H 30M 0S
2 B        NA         1H 30M 0S  0H 30M 0S  2H 0M 0S
3 C        NA         1H 0M 0S   NA         1H 0M 0S

解决方案 #

错误根源是同一category+subcategory组合下存在多个时间值（比如原数据中category=F、subcategory=9有2条记录），而lubridate::hms无法直接处理多值，必须先对子类别内的时间做汇总（求和/均值等），再做宽表转换。

以下是修正后的代码，支持时间求和，且可扩展其他指标：

library(tidyverse)
library(lubridate)

df_processed <- df %>%
  # 1. 将时间字符串转为秒数值，方便计算
  mutate(time_sec = as.numeric(hms(time))) %>%
  # 2. 按类别+子类别分组，汇总时间（这里用求和，可替换为mean/max等）
  group_by(category, subcategory) %>%
  summarise(time_period = seconds_to_period(sum(time_sec)), .groups = "drop") %>%
  # 3. 按类别计算总时间
  group_by(category) %>%
  mutate(total = seconds_to_period(sum(time_period))) %>%
  ungroup() %>%
  # 4. 转换为宽表
  pivot_wider(
    names_from = subcategory,
    values_from = time_period,
    names_prefix = "time_"
  ) %>%
  # 5. 调整列顺序，匹配预期格式
  select(category, total, starts_with("time_")) %>%
  select(category, sort(colnames(.)[-1]))

# 查看结果
df_processed

代码说明 #

• 先将时间转为秒数值，避免Period类型直接计算的问题；
• 先完成子类别内的时间汇总，确保每个category+subcategory只有一个值，解决pivot_wider的多值错误；
• 计算类别总时间时，基于已汇总的子类别时间求和，结果更准确；
• 若需要其他指标（比如均值），只需将sum(time_sec)替换为mean(time_sec)，再用seconds_to_period转换即可。

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