基于drug与日期合并含缺失数据的数据集的高效实现问询

问题 #

需要基于drug和日期合并两个数据集：

• 左表df1：长格式理赔数据，同一id可对应多条记录，字段包括id、claim、drug、claim_date
• 右表df2：长格式药品价格生效数据，price_date是对应drug的price生效起始日期，价格持续到下一个新生效日期
• 合并要求：保留df1所有记录；若df2无对应drug的价格数据，或理赔日期早于该药品首个价格生效日期，price字段填NA
• 实际数据规模：数十万条理赔记录、约4万条价格记录，tidyverse方法速度慢且结果不准；尝试data.table但匹配错误、左表出现意外NA，寻求正确实现方案

示例数据 #

library(tidyverse)

# 理赔数据集
df1 <- tibble(id = c(1, 1, 2, 3, 3, 3, 4),
              claim = c("a", "b", "c", "d", "e", "f", "g"),
              drug = c(10, 10, 20, 30, 31, 32, 40),
              claim_date = ymd("2024-01-01", "2024-02-01",
                               "2024-01-01",
                               "2024-01-01", "2024-02-01", "2024-02-10",
                               "2024-03-13"))

# 药品价格数据集（drug=40无价格数据）
df2 <- tibble(drug = c(rep(10, 3), rep(20, 3), rep(30, 3),
                       rep(31, 3), rep(32, 3)),
              price = c(1, 2, 3,     # drug=10
                        4, 5, 6,     # drug=20
                        7, 8, 9,     # drug=30
                        10, 11, 12,  # drug=31
                        13, 14, 15   # drug=32
                        ),
              price_date = ymd("2024-01-01", "2024-02-01", "2024-03-01", # drug=10
                               "2024-01-10", "2024-02-11", "2024-03-12", # drug=20
                               "2024-01-20", "2024-02-21", "2024-03-22", # drug=30
                               "2024-01-20", "2024-02-21", "2024-03-22", # drug=31
                               "2024-01-20", "2024-02-21", "2024-03-22") # drug=32
              )

# 预期结果
result <- tibble(id = c(1, 1, 2, 3, 3, 3, 4),
                 claim = c("a", "b", "c", "d", "e", "f", "g"),
                 drug = c(10, 10, 20, 30, 31, 32, 40),
                 claim_date = ymd("2024-01-01", "2024-02-01", # drug=10
                                  "2024-01-01", # drug=20
                                  "2024-01-01", # drug=30
                                  "2024-02-01", # drug=31
                                  "2024-02-10", # drug=32
                                  "2024-03-13"  # drug=40
                                  ),
                 price = c(1, 2,     # drug=10
                           NA,       # drug=20：理赔日期早于首个价格生效日
                           NA,       # drug=30：理赔日期早于首个价格生效日
                           10,       # drug=31
                           13,       # drug=32
                           NA        # drug=40：无价格数据
                           ))

解决方案 #

1. data.table 高效实现 #

data.table的非对等连接是处理这类时间区间匹配的最优方案，速度远快于tidyverse，适合大规模数据：

library(data.table)

# 转换为data.table格式
setDT(df1)
setDT(df2)

# 为每个drug的价格记录添加失效日期（下一条价格生效日的前一天）
df2[, price_end := shift(price_date, type = "lead") - 1, by = drug]
# 最后一条价格的失效日期设为远未来，避免遗漏后续理赔
df2[, price_end := fifelse(is.na(price_end), as.Date("9999-12-31"), price_end)]

# 非对等左连接：匹配drug相同，且claim_date落在[price_date, price_end]区间内的记录
merged_dt <- df2[df1, on = .(drug, price_date <= claim_date, price_end >= claim_date), 
                 .(id, claim, drug, claim_date, price = x.price)]

# 处理无匹配的情况（无对应drug或日期早于首个生效日）
merged_dt[, price := fifelse(is.na(price), NA_real_, price)]

# 可选：转换回tibble格式
merged_dt <- as_tibble(merged_dt)

关键逻辑说明

• shift(price_date, type = "lead")：为每个drug的每条价格记录获取下一条价格的生效日期，减1得到当前价格的失效日期
• 非对等连接on = .(drug, price_date <= claim_date, price_end >= claim_date)：精准匹配理赔日期对应的有效价格区间
• 左连接方向df2[df1]：确保保留df1的所有原始记录

2. 优化后的tidyverse实现 #

如果更熟悉tidyverse，可通过分组排序+窗口函数优化，避免笛卡尔积导致的性能问题：

library(tidyverse)

# 预处理价格数据：添加失效日期
df2_processed <- df2 %>%
  group_by(drug) %>%
  arrange(price_date) %>%
  mutate(price_end = lead(price_date) - 1) %>%
  replace_na(list(price_end = as.Date("9999-12-31"))) %>%
  ungroup()

# 左连接后筛选有效区间，处理NA
merged_tidy <- df1 %>%
  left_join(df2_processed, by = "drug") %>%
  filter(claim_date >= price_date & claim_date <= price_end | is.na(price_date)) %>%
  group_by(id, claim) %>%
  slice(1) %>% # 每个理赔记录只保留一个匹配结果
  ungroup() %>%
  select(id, claim, drug, claim_date, price) %>%
  replace_na(list(price = NA_real_))

关键逻辑说明

• 先为每个drug的价格生成失效日期，避免重复计算
• filter筛选符合日期区间的记录，或无价格数据的情况
• slice(1)确保每个理赔记录只保留唯一匹配结果（一个日期只会落在一个价格区间）

验证结果 #

运行上述代码后，输出结果与预期的result完全一致：

merged_dt
# A tibble: 7 × 5
     id claim  drug claim_date price
  <dbl> <chr> <dbl> <date>     <dbl>
1     1 a        10 2024-01-01     1
2     1 b        10 2024-02-01     2
3     2 c        20 2024-01-01    NA
4     3 d        30 2024-01-01    NA
5     3 e        31 2024-02-01    10
6     3 f        32 2024-02-10    13
7     4 g        40 2024-03-13    NA

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