使用dplyr在R中聚合每小时ID计数并补全零值

我来帮你搞定这个需求！要计算ID的每小时出现次数，同时补全每个日期的24小时区间（没记录的小时填0），用R里的常用工具链就能轻松实现，下面给你两种实用方案：

这个方法代码可读性强，适合大多数日常场景：

首先我们先构造一个示例数据框（你可以替换成自己的真实数据）：

library(tidyverse)
library(lubridate)

# 构造示例数据：包含ID和时间戳列
set.seed(123)
df <- tibble(
  ID = sample(c("A", "B", "C"), 50, replace = TRUE),
  timestamp = ymd_hms("2024-05-20 08:15:00") + hours(sample(0:10, 50, replace = TRUE)) + minutes(sample(0:59, 50))
)

接下来分三步处理：

1. 拆分时间字段，按日期、小时、ID统计出现次数

df_count <- df %>%
  mutate(
    date = date(timestamp),  # 提取日期部分
    hour = hour(timestamp)   # 提取小时部分（0-23）
  ) %>%
  count(date, hour, ID, name = "occurrence_count")  # 分组计数

2. 生成每个日期的完整24小时+所有ID的组合网格

# 获取数据里的所有唯一日期和ID
all_dates <- unique(df_count$date)
all_ids <- unique(df$ID)

# 生成包含所有日期、0-23小时、所有ID的完整组合
full_time_grid <- expand_grid(
  date = all_dates,
  hour = 0:23,
  ID = all_ids
)

3. 左连接计数结果，把无记录的小时填充为0

final_result <- full_time_grid %>%
  left_join(df_count, by = c("date", "hour", "ID")) %>%
  mutate(occurrence_count = replace_na(occurrence_count, 0))  # NA替换为0

如果你的数据集非常大，data.table的处理速度会比tidyverse快很多，步骤类似：

library(data.table)
library(lubridate)

# 构造示例data.table
set.seed(123)
dt <- data.table(
  ID = sample(c("A", "B", "C"), 50, replace = TRUE),
  timestamp = ymd_hms("2024-05-20 08:15:00") + hours(sample(0:10, 50, replace = TRUE)) + minutes(sample(0:59, 50))
)

处理步骤：

1. 提取日期和小时，分组计数

dt[, `:=`(date = date(timestamp), hour = hour(timestamp))]
dt_count <- dt[, .(occurrence_count = .N), by = .(date, hour, ID)]

2. 生成完整组合网格并填充0

# 生成所有日期、小时、ID的组合
full_grid <- dt_count[, CJ(date = unique(date), hour = 0:23, ID = unique(ID), sorted = FALSE)]

# 左连接并替换NA为0
final_result_dt <- full_grid[dt_count, on = .(date, hour, ID)]
final_result_dt[is.na(occurrence_count), occurrence_count := 0]

小提示 #

• 如果你的时间列还不是datetime格式，记得先用ymd_hms()或as.POSIXct()转换；
• 如果不需要按ID拆分统计（只统计每个小时的总次数），去掉代码里涉及ID的分组和网格组合即可；
• 两种方法最终都会生成每个日期下0-23点、每个ID的完整计数，无记录的时段自动填充0。

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