使用dplyr或data.table通过另一数据框日期过滤数据的问题

问题背景 #

我有两个数据框：

• df：记录不同用户的时序采集数值
• df1：记录不同用户的事件起止日期

需要用df1中的日期区间过滤df，仅保留落在事件区间内的数据。现有两种方案存在问题：

• dplyr的left_join方案因数据量过大导致内存不足
• data.table方案接近可行，但输出仅保留起始日期，而非所有符合条件的原始日期

示例数据 #

user=c(rep('A',7),rep('B',8))
data = seq(1:15)
date = as.Date(c('2016-01-01','2016-01-02','2016-01-03','2016-01-04','2016-01-05','2016-01-06','2016-01-07','2016-01-08','2016-01-09','2016-01-10','2016-01-11','2016-01-12','2016-01-13','2016-01-14','2016-01-15'))
df = data.frame(user,date,data)

df1 =data.frame(user = c('A','B'), start_date = as.Date(c('2016-01-02','2016-01-10')),  end_date = as.Date(c('2016-01-06','2016-01-14')))

原方案问题 #

原dplyr方案（内存不足） #

library(dplyr)
df<-left_join(df,df1,by="user")
df <- df %>% filter(date>=start_date & date<=end_date)

问题：left_join会将df中每个用户的所有行与df1中该用户的行做笛卡尔连接，导致数据量急剧膨胀，内存占用过高。

原data.table方案（丢失原始日期） #

require(data.table)
setDT(df)[df1, .(user,date,data), on=.(user, date>=start_date, date<=end_date)]

问题：使用date>=start_date, date<=end_date作为连接条件时，输出中的date会被替换为start_date，无法保留df中的原始日期。

修复方案 #

方案1：优化后的dplyr方案 #

通过分组过滤避免冗余连接，大幅减少内存占用：

library(dplyr)
library(purrr)

# 转换为用户到日期区间的映射表
user_ranges <- df1 %>%
  select(user, start_date, end_date) %>%
  tibble::deframe() %>%
  map(~as.Date(.))

df %>%
  group_by(user) %>%
  filter(date >= user_ranges[[cur_group()$user]][1] & date <= user_ranges[[cur_group()$user]][2]) %>%
  ungroup()

或者使用dplyr 1.1.0+支持的非等值连接，直接完成条件匹配：

library(dplyr)

df %>%
  inner_join(df1, by = join_by(user == user, date >= start_date, date <= end_date)) %>%
  select(user, date, data)

方案2：正确的data.table方案 #

通过x.date引用原始日期，并设置nomatch=0过滤掉不匹配的行：

require(data.table)
setDT(df)
setDT(df1)

# 保留原始日期的过滤结果
filtered_df <- df[df1, 
                  .(user, date = x.date, data), 
                  on = .(user, date >= start_date, date <= end_date), 
                  nomatch = 0]
print(filtered_df)

运行后会输出所有落在事件区间内的原始数据，日期列完整保留df中的原始值。

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