如何用data.table基于分组与多时间点区间的唯一计数创建变量

需求说明 #

统计每个以idnr标识的个体，在index_date + time_from_index这一日期之前1年内的唯一drug_code配药数量。

问题痛点 #

实际数据量庞大，无法使用笛卡尔积连接的方式实现统计，会导致内存溢出或计算效率极低。

解决方案（data.table实现） #

利用data.table的非等连接特性，这是处理大数据量时间窗口统计的高效方案，无需生成笛卡尔积：

步骤1：加载并转换数据为data.table格式 #

library(data.table)

# 转换为data.table
setDT(y.df)
setDT(x.df)

# 计算每个记录的时间窗口：end_date = index_date + time_from_index，start_date = end_date - 365天
y.df[, `:=`(
  end_date = index_date + time_from_index,
  start_date = index_date + time_from_index - 365
)]

步骤2：非等连接+统计唯一药物数量 #

通过按idnr匹配，同时筛选配药日期在时间窗口内的记录，再统计每个分组的唯一drug_code数量：

# 非等连接并统计
result <- x.df[y.df, 
               on = .(idnr, date_of_dispensing >= start_date, date_of_dispensing < end_date),
               .(no_of_unique_drugs = uniqueN(drug_code)),
               by = .EACHI]

# 合并原y.df的字段并整理输出
result <- y.df[result, on = .(idnr, start_date, end_date)][, 
               .(idnr, time_from_index, index_date, no_of_unique_drugs)]

数据示例 #

目标表（y.df） #

y.df <- data.frame(
  idnr = c(1, 1, 1, 2, 2, 3, 3, 3, 4, 4),
  time_from_index = c(0, 365, 730, 0, 365, 0, 365, 730, 0, 365),
  index_date = as.Date(c('2023-01-01', '2023-01-01', '2023-01-01', '2023-06-01', '2023-06-01', '2023-09-01', '2023-09-01', '2023-09-01', '2024-01-01', '2024-01-01'))
)

配药记录表（x.df） #

x.df <- data.frame(
  idnr = c(1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 4),
  date_of_dispensing = as.Date(c(
    '2022-12-15', '2022-12-20', '2023-01-05', '2023-01-20', 
    '2023-05-10', '2023-06-01', '2023-06-15', '2023-07-01', 
    '2023-08-10', '2023-09-05', '2023-10-01', '2023-11-01',
    '2023-12-15', '2023-12-20', '2024-01-05', '2024-01-15', '2024-02-01'
  )),
  drug_code = c('A', 'A', 'B', 'C', 'D', 'D', 'E', 'F', 'G', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N')
)

预期输出 #

# A tibble: 10 × 4
    idnr time_from_index index_date no_of_unique_drugs
   <dbl>           <dbl> <date>                  <int>
 1     1               0 2023-01-01                  1
 2     1             365 2023-01-01                  2
 3     1             730 2023-01-01                  0
 4     2               0 2023-06-01                  1
 5     2             365 2023-06-01                  3
 6     3               0 2023-09-01                  1
 7     3             365 2023-09-01                  3
 8     3             730 2023-09-01                  0
 9     4               0 2024-01-01                  2
10    4             365 2024-01-01                  3

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