使用dplyr实现月度活动总量及按类型筛选的月度计数统计

基于tidyverse生态实现，代码可直接适配你的数据集，最终输出完全符合要求的宽表结构。

实现思路 #

• 先对原始数据做标准化预处理，统一月份、类型字段的命名格式
• 预先定义需要覆盖的所有月份，确保无数据的月份也能生成对应列
• 分模块统计不同维度的计数，每类统计单独处理后合并
• 所有缺失计数统一填充为0，适配模型输入要求

完整可运行代码 #

# 加载依赖包
library(tidyverse)
library(lubridate)

# 示例数据集（你自己的数据集可直接替换这部分）
activities <- data.frame(
  Person = c(rep("Person A", 3), rep("Person B", 3)), 
  Month = seq(as.Date("2020/1/1"), by = "month", length.out = 6),
  Activity.Type = as.factor(seq(1:2)),
  Activity.Sub.Type = as.factor(seq(1:3))
)

# 1. 数据预处理
activities_processed <- activities %>%
  mutate(
    # 格式化年月为列名可用的格式
    year_month = format(Month, "%Y_%m"),
    Activity.Type = paste0("Type_", Activity.Type),
    Activity.Sub.Type = paste0("SubType_", Activity.Sub.Type)
  )

# 2. 定义需要覆盖的所有月份（这里示例为2020年全年12个月，可按需修改）
all_months <- str_c("2020_", str_pad(1:12, 2, side = "left", pad = "0"))
# 如果你需要按数据实际时间范围补全月份，可替换为以下代码：
# min_month <- min(activities_processed$Month)
# max_month <- max(activities_processed$Month)
# all_months <- seq.Date(floor_date(min_month, "month"), floor_date(max_month, "month"), by = "month") %>% format("%Y_%m")

# 3. 统计全局维度：总活动量、各活动类型/子类型总计数
global_stats <- activities_processed %>%
  group_by(Person) %>%
  summarise(
    # 总活动量
    total_activities = n(),
    # 各活动类型总计数
    !!!count(cur_data(), Activity.Type, name = "cnt") %>%
      pivot_wider(names_from = Activity.Type, values_from = cnt, values_fill = 0),
    # 各活动子类型总计数
    !!!count(cur_data(), Activity.Sub.Type, name = "cnt") %>%
      pivot_wider(names_from = Activity.Sub.Type, values_from = cnt, values_fill = 0),
    .groups = "drop"
  )

# 4. 统计各月总活动计数
monthly_total <- activities_processed %>%
  group_by(Person, year_month) %>%
  summarise(cnt = n(), .groups = "drop") %>%
  # 补全所有人员+所有月份组合，缺失值填0
  complete(Person, year_month = all_months, fill = list(cnt = 0)) %>%
  pivot_wider(
    names_from = year_month,
    values_from = cnt,
    names_prefix = "MonthlyTotal_"
  )

# 5. 统计各月各活动类型计数
monthly_type <- activities_processed %>%
  group_by(Person, year_month, Activity.Type) %>%
  summarise(cnt = n(), .groups = "drop") %>%
  complete(Person, year_month = all_months, Activity.Type, fill = list(cnt = 0)) %>%
  pivot_wider(
    names_from = c(year_month, Activity.Type),
    values_from = cnt,
    names_glue = "{Activity.Type}_{year_month}"
  )

# 6. 统计各月各活动子类型计数
monthly_subtype <- activities_processed %>%
  group_by(Person, year_month, Activity.Sub.Type) %>%
  summarise(cnt = n(), .groups = "drop") %>%
  complete(Person, year_month = all_months, Activity.Sub.Type, fill = list(cnt = 0)) %>%
  pivot_wider(
    names_from = c(year_month, Activity.Sub.Type),
    values_from = cnt,
    names_glue = "{Activity.Sub.Type}_{year_month}"
  )

# 7. 合并所有统计结果，得到最终宽表
final_wide <- global_stats %>%
  left_join(monthly_total, by = "Person") %>%
  left_join(monthly_type, by = "Person") %>%
  left_join(monthly_subtype, by = "Person") %>%
  # 统一填充所有数值列的缺失值为0
  mutate(across(where(is.numeric), ~replace_na(.x, 0)))

代码说明 #

• 月份范围可灵活配置：如果需要统计其他年份的12个月，直接修改all_months的生成逻辑即可，后续统计代码无需调整
• 自动适配分类数量：如果你的数据集里活动类型、子类型的数量和示例不同，代码会自动生成对应统计列，无需手动指定
• 最终输出完全适配模型输入要求：每行对应1位人员，所有要求的统计列无缺失，无活动的月份对应值统一为0

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