使用dplyr/data.table按日期统计暴露事件数与暴露人·时总量

核心逻辑 #

利用事件开始标记生成全局唯一事件ID，按ID分组即可完成两类统计需求。如果你已经生成了起止指示变量，可直接替换下文的event_start生成逻辑，无需重复计算。

依赖安装（按需选择） #

两种实现均依赖lubridate处理时间计算：

install.packages(c("dplyr", "lubridate")) # 选dplyr的话装这个
install.packages(c("data.table", "lubridate")) # 选data.table的话装这个

dplyr 实现 #

步骤1：数据预处理 #

library(dplyr)
library(lubridate)

# 构造示例数据，实际使用替换为你的数据集
df <- tibble(
  Date = c("11/1","11/1","11/2","11/2","11/2","11/2","11/2","11/2","11/3","11/3"),
  Time = c("1PM","3PM","1PM","4PM","6PM","7PM","8PM","9PM","3PM","6PM"),
  PeopleExposed = c(1,0,5,10,3,0,2,0,5,0)
)

df_processed <- df %>%
  mutate(
    # 合并为标准时间格式用于计算时长
    datetime = mdy_hm(paste(Date, Time)),
    # 事件开始标记：上一时刻暴露人数为0，当前时刻>0即为新事件开始
    event_start = ifelse(lag(PeopleExposed, default = 0) == 0 & PeopleExposed > 0, 1, 0),
    # 生成全局唯一事件ID
    event_id = cumsum(event_start)
  ) %>%
  # 过滤无暴露的0值行，减少计算量
  filter(event_id != 0)

步骤2：按日期统计结果 #

daily_stats <- df_processed %>%
  group_by(Date) %>%
  summarise(
    NumEvents = n_distinct(event_id),
    # 单条记录人时 = 暴露人数 * 该记录持续时长（小时），求和得总人时
    PeopleHoursTotal = sum(PeopleExposed * as.numeric(difftime(lead(datetime), datetime, units = "hours")))
  ) %>%
  ungroup()

步骤3：事件明细统计 #

event_details <- df_processed %>%
  # 按全局事件ID分组汇总
  group_by(event_id) %>%
  summarise(
    Date = first(Date),
    PeopleHoursTotal = sum(PeopleExposed * as.numeric(difftime(lead(datetime), datetime, units = "hours"))),
    TimeStart = first(Time),
    TimeEnd = last(Time)
  ) %>%
  # 生成同一日期内的事件序号
  group_by(Date) %>%
  mutate(EventNum = row_number()) %>%
  ungroup() %>%
  # 按需求调整输出列顺序
  select(Date, EventNum, PeopleHoursTotal, TimeStart, TimeEnd)

data.table 实现 #

步骤1：数据预处理 #

library(data.table)
library(lubridate)

# 转换为data.table格式，实际使用替换为你的数据集
dt <- data.table(
  Date = c("11/1","11/1","11/2","11/2","11/2","11/2","11/2","11/2","11/3","11/3"),
  Time = c("1PM","3PM","1PM","4PM","6PM","7PM","8PM","9PM","3PM","6PM"),
  PeopleExposed = c(1,0,5,10,3,0,2,0,5,0)
)

dt[, `:=`(
  datetime = mdy_hm(paste(Date, Time)),
  event_start = fifelse(shift(PeopleExposed, fill = 0) == 0 & PeopleExposed > 0, 1, 0)
)][, event_id := cumsum(event_start)][event_id != 0]

步骤2：按日期统计结果 #

daily_stats <- dt[, .(
  NumEvents = uniqueN(event_id),
  PeopleHoursTotal = sum(PeopleExposed * as.numeric(difftime(shift(datetime, type = "lead"), datetime, units = "hours")))
), by = Date]

步骤3：事件明细统计 #

event_details <- dt[, .(
  Date = first(Date),
  PeopleHoursTotal = sum(PeopleExposed * as.numeric(difftime(shift(datetime, type = "lead"), datetime, units = "hours"))),
  TimeStart = first(Time),
  TimeEnd = last(Time)
), by = event_id][, EventNum := rowid(Date)][, .(Date, EventNum, PeopleHoursTotal, TimeStart, TimeEnd)]

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