基于日期时间条件高效合并数据帧

Question

我有以下数据帧：

print(df)

client_id   route_id         start                       
    1          1_2        2019-01-01 03:00:00     
    8          1_4        2019-01-23 10:00:00     
    3          1_2        2019-01-15 04:10:00     
    4          1_10       2019-01-10 05:00:00
...

print(accident_df)

   route_id        start         end
      1_2        2018-12-24   2018-01-03
      1_4        2019-01-25   2019-01-29
      1_2        2019-02-24   2019-02-28
      1_10       2019-01-05   2019-01-15

...

我希望按照以下条件合并它们（&；）：

1.“路线id”必须匹配

2.df.start必须介于事故df.start和事故df.end之间（因此，大于第一个，小于后一个）

print(final_df)

client_id   route_id         start                       
    1          1_2        2019-01-01 03:00:00            
    4          1_10       2019-01-10 05:00:00

由于两个数据帧都相当大（大于10M行），并且存在数千个“路由id”，因此基于“路由id”合并然后按条件选择行的解决方案效率极低，会导致与内存相关的问题（如Python Pandas - Compare 2 dataframes, multiple parameters）

我怎样才能有效地做到这一点

Answer 1

我们可以说这种方法比join更有效，因为它首先将df1切割成更小的数据帧，然后匹配路由id。（我不是专家）

假设数据集存储在excel工作表中（可以是任何其他格式/文件）

df1 = pd.read_excel('your_file.xlsx',sheet_name='Sheet1')
df2 = pd.read_excel('your_file.xlsx',sheet_name='Sheet2')

使用pandasql，您可以使用SQL脚本获取所需内容：

import pandas as pd
import pandasql as ps


q1 = """
     SELECT client_id, route_id, start FROM df1 
     WHERE EXISTS 
     (SELECT 1 FROM df2 WHERE df1.start BETWEEN df2.start AND df2.end)
     and route_id IN (SELECT route_id FROM df2)
     """

print(ps.sqldf(q1, locals()))

结果：

       client_id route_id                       start
0          1      1_2  2019-01-01 03:00:00.000000
1          4     1_10  2019-01-10 05:00:00.000000