在R中统计每月处于开放状态的记录数量的高效方法

问题说明 #

现有数据框每行包含record_id、record_start_date和record_end_date字段，需要生成新数据框：第一列是每个日历月的起始日期（比如2020-01-01代表2020年1月），第二列统计该月内处于开放状态（无论记录覆盖整月还是部分月份）的唯一记录数。原方法是为每个月新建列生成哑变量后求和，这里提供更高效的实现方式。

示例数据：

ds <- data.frame(record_id = c("00a", "00b", "00c"),
                 record_start_date = as.Date(c("2020-01-16", "2020-03-25", "2020-02-22")),
                 record_end_date = as.Date(c("2020-12-05", "2020-06-21", "2020-11-12")))

高效实现方案 #

方法1：tidyverse生态实现（简洁易读） #

核心逻辑是把每条记录的日期区间扩展为它覆盖的所有月份，再按月份去重统计唯一记录数：

library(tidyverse)

# 生成数据中涉及的所有月份起始日期
all_months <- seq.Date(
  floor_date(min(ds$record_start_date), "month"),
  floor_date(max(ds$record_end_date), "month"),
  by = "month"
)

# 处理记录并统计
result <- ds %>%
  rowwise() %>%
  mutate(
    # 生成当前记录覆盖的所有月份起始日期列表
    month_start = list(seq.Date(
      floor_date(record_start_date, "month"),
      floor_date(record_end_date, "month"),
      by = "month"
    ))
  ) %>%
  unnest(month_start) %>%
  # 按月份分组，统计唯一记录数
  group_by(month_start) %>%
  summarise(open_records = n_distinct(record_id)) %>%
  # 补全所有月份，无开放记录的月份填0
  right_join(tibble(month_start = all_months), by = "month_start") %>%
  replace_na(list(open_records = 0)) %>%
  arrange(month_start)

print(result)

方法2：data.table实现（大数据量场景优化） #

如果数据量较大，data.table的内存和运行效率更有优势：

library(data.table)
library(lubridate)

setDT(ds)

# 生成所有涉及的月份
all_months <- seq.Date(
  floor_date(min(ds$record_start_date), "month"),
  floor_date(max(ds$record_end_date), "month"),
  by = "month"
)

# 为每条记录扩展覆盖的月份
ds[, month_start := lapply(1:.N, function(i) {
  seq.Date(
    floor_date(record_start_date[i], "month"),
    floor_date(record_end_date[i], "month"),
    by = "month"
  )
}), by = record_id]

# 展开数据并统计
result <- ds[, .(month_start = unlist(month_start)), by = record_id] %>%
  .[, .(open_records = uniqueN(record_id)), by = month_start] %>%
  .[data.table(month_start = all_months), on = "month_start"] %>%
  .[is.na(open_records), open_records := 0] %>%
  setorder(month_start)

print(result)

方案优势 #

• 无需手动创建大量哑变量列，代码简洁易维护
• 自动识别数据涉及的所有月份，无需手动指定时间范围
• 大数据量场景下，data.table版本能显著提升处理速度

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