按3个月间隔汇总季度采集数据均值的技术实现咨询

1. 计算日期间隔并划分区间 #

先计算每条记录的date与init_date的月份差，再根据差值划分到对应区间。用lubridate处理日期、dplyr做数据转换，流程简单清晰：

library(dplyr)
library(lubridate)

# 加载示例数据
df <- structure(list(id = c(1, 1, 1, 1, 1, 1, 1), 
                     init_date = structure(c(19212, 19212, 19212, 19212, 19212, 19212, 19212), class = "Date"), 
                     group = c("A", "A", "A", "A", "A", "A", "A"), 
                     date = structure(c(19214, 19235, 19236, 19237, 19239, 19241, 19431), class = "Date"), 
                     value = c(20, 17, 13, 15.3, 16.3, 23.1, 17.9)), 
                row.names = c(NA, -7L), class = "data.frame")

# 计算月份差并划分区间
df_processed <- df %>%
  # 计算date与init_date的精确月份差（向上取整保证区间划分准确）
  mutate(month_diff = ceiling(time_length(difftime(date, init_date), unit = "month"))) %>%
  # 自定义区间规则
  mutate(interval = case_when(
    month_diff <= 3 ~ "baseline",
    month_diff <= 6 ~ "3 months",
    month_diff <= 9 ~ "6 months",
    month_diff <= 12 ~ "9 months",
    TRUE ~ "12+ months" # 处理超过1年的记录
  ))

2. 计算各区间均值 #

根据需求选择分组维度（比如按group，或id+group），聚合计算均值：

# 按group和区间计算均值
mean_summary <- df_processed %>%
  group_by(group, interval) %>%
  summarise(mean_value = round(mean(value, na.rm = TRUE), 2),
            .groups = "drop")

# 查看结果
print(mean_summary)

对应示例数据的输出结果：

# A tibble: 2 × 3
  group interval  mean_value
  <chr> <chr>          <dbl>
1 A     baseline        17.8
2 A     6 months        17.9

如果需要按id和group细分，将group_by改为group_by(id, group, interval)即可。

3. 生成表格与可视化 #

生成美观表格 #

用knitr::kable输出结构化表格：

library(knitr)
kable(mean_summary, caption = "各时间区间均值统计")

可视化对比 #

用ggplot2绘制柱状图，直观展示不同组的均值变化：

library(ggplot2)

ggplot(mean_summary, aes(x = interval, y = mean_value, fill = group)) +
  geom_col(position = "dodge") +
  labs(title = "各区间均值对比", x = "时间区间", y = "均值") +
  theme_minimal()

关于pivot操作的说明 #

不需要提前做pivot，先划分区间再聚合的流程更直观。如果后续需要宽格式输出（将区间转为列），可以在聚合后用tidyr::pivot_wider转换：

library(tidyr)

wide_summary <- mean_summary %>%
  pivot_wider(names_from = interval, values_from = mean_value)

print(wide_summary)

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