超大数据框下按ID筛选180天内购不同车型记录的高效方案

示例数据 #

df <- data.frame(id = c(1,1,1,2,2,2,2,3,3,4), 
                 car = c("subaru", "audi", "subaru", "toyota", "toyota", "audi", "subaru", "nissan", "nissan", "chevrolet"),
                buy_date = c("01/01/2000", "01/01/2001", "01/02/2001", "01/01/2000", "01/12/2004", "01/01/2005", "01/03/2005", "01/01/2000", "02/01/2000", "01/01/2010"))
df$buy_date <- as.Date(df$buy_date, format="%d/%m/%Y") 

需求 #

对每个ID，若该ID存在180天内购买两种不同类型汽车的记录，则保留该ID中相关的行（具体看预期输出：仅保留属于符合时间范围且涉及不同车型的行，排除孤立的、无符合条件配对的行）。预期输出如下：

困境与疑问 #

原始数据有1200万行，使用inner_join会生成大量中间行，导致内存溢出。考虑通过逐行比较实现：新增一列记录每行与同ID上一行的天数差（同ID首行为NA），然后筛选出车型不同且天数差≤180的行，请问该方案是否可行？

你的方案不完全可行，存在明显漏洞：

• 仅比较当前行与上一行的间隔，会漏掉当前行与更早非相邻行的符合条件配对。比如某ID的购买记录是car=A（day1）、car=A（day50）、car=B（day200），day50与day200间隔150天且车型不同，但你的方案只会检查day50和day1、day200和day50的配对，虽然这能抓到有效配对，但如果是更复杂的时间窗口，比如car=A（day1）、car=C（day100）、car=B（day250），day1与day250间隔240天不符合，day100与day250间隔150天符合，你的方案也能抓到，但如果存在非相邻且中间行无有效配对的情况，就可能漏保留相关行。
• 你的方案只能标记直接相邻的符合条件行，无法自动把同一时间窗口内的所有关联行都保留。比如ID2中2004-12-01的toyota和2005-03-01的subaru间隔90天且车型不同，但它们不是相邻行，你的方案能保留这三行是因为相邻的toyota-audi、audi-subaru都符合条件，这属于巧合；如果存在非相邻但符合条件的配对，且中间行无相邻有效配对，你的方案就会漏行。

更高效准确的处理方案（适配千万级数据集） #

优先用data.table处理，它的分组和滚动操作内存效率远高于dplyr，不会像inner_join那样生成爆炸式中间数据：

1. 先按ID和购买日期排序

   library(data.table)
   setDT(df)[order(id, buy_date)]
   

2. 滚动窗口检查有效配对
   对每个ID，找出当前行前后180天内的所有记录，检查是否存在不同车型，标记需保留的行：

   df[, {
       # 为每行生成180天的时间窗口范围
       window_start <- buy_date - 180
       # 筛选窗口内的所有行
       window_data <- .SD[buy_date >= window_start]
       # 判断窗口内是否有不同车型
       has_match <- any(window_data$car != car)
       .(car, buy_date, has_match)
   }, by = id]
   

3. 筛选最终结果

   result <- df[has_match == TRUE, .(id, car, buy_date)]
   

对你原始方案的补充优化（临时可用） #

如果你的数据中同一ID的购买记录不会出现“非相邻但符合条件且中间行无有效配对”的情况，可以用以下补充方案覆盖大部分场景：

library(dplyr)
df <- df %>%
  arrange(id, buy_date) %>%
  group_by(id) %>%
  mutate(
    days_diff = buy_date - lag(buy_date),
    valid_pair = (days_diff <= 180) & (car != lag(car))
  ) %>%
  # 标记该ID是否存在有效配对
  mutate(has_valid_pair = any(valid_pair, na.rm = TRUE)) %>%
  # 找出该ID所有在有效配对时间范围内的行
  mutate(
    min_valid_date = min(buy_date[valid_pair | lag(valid_pair)], na.rm = TRUE),
    max_valid_date = max(buy_date[valid_pair | lead(valid_pair)], na.rm = TRUE),
    in_window = buy_date >= min_valid_date & buy_date <= max_valid_date
  ) %>%
  filter(in_window) %>%
  ungroup() %>%
  select(id, car, buy_date)

但这个方案依然不如data.table的滚动窗口方法准确。

内容的提问来源于stack exchange，提问作者Badboybørge