python - pd.merge_asof 每个时间段有多个匹配项？

Question

我正在尝试按时间合并多个匹配的两个数据帧。我正在查找 timestamp 在 df1 中的 endofweek 之前 7 天或更短时间的 df2 的所有实例。可能有多个记录符合这种情况，我想要所有匹配项，而不仅仅是第一个或最后一个(pd.merge_asof 所做的)。

import pandas as pd
df1 = pd.DataFrame({'endofweek': ['2019-08-31', '2019-08-31', '2019-09-07', '2019-09-07', '2019-09-14', '2019-09-14'], 'GroupCol': [1234,8679,1234,8679,1234,8679]})
df2 = pd.DataFrame({'timestamp': ['2019-08-30 10:00', '2019-08-30 10:30', '2019-09-07 12:00', '2019-09-08 14:00'], 'GroupVal': [1234, 1234, 8679, 1234], 'TextVal': ['1234_1', '1234_2', '8679_1', '1234_3']})
df1['endofweek'] = pd.to_datetime(df1['endofweek'])
df2['timestamp'] = pd.to_datetime(df2['timestamp'])

我已经尝试过

pd.merge_asof(df1, df2, tolerance=pd.Timedelta('7d'), direction='backward', left_on='endofweek', right_on='timestamp', left_by='GroupCol', right_by='GroupVal')

但这让我很感动

   endofweek  GroupCol           timestamp  GroupVal TextVal
0 2019-08-31      1234 2019-08-30 10:30:00    1234.0  1234_2
1 2019-08-31      8679                 NaT       NaN     NaN
2 2019-09-07      1234                 NaT       NaN     NaN
3 2019-09-07      8679                 NaT       NaN     NaN
4 2019-09-14      1234 2019-09-08 14:00:00    1234.0  1234_3
5 2019-09-14      8679 2019-09-07 12:00:00    8679.0  8679_1

我丢失了文本 1234_1。有没有办法为 pd.merge_asof 进行某种外部联接，这样我可以保留 df2 的所有实例，而不仅仅是第一个或最后一个实例？

我的理想结果如下所示(假设 endofweek 时间被视为该日期的 00:00:00):

   endofweek  GroupCol           timestamp  GroupVal TextVal
0 2019-08-31      1234 2019-08-30 10:00:00    1234.0  1234_1
1 2019-08-31      1234 2019-08-30 10:30:00    1234.0  1234_2
2 2019-08-31      8679                 NaT       NaN     NaN
3 2019-09-07      1234                 NaT       NaN     NaN                 
4 2019-09-07      8679                 NaT       NaN     NaN 
5 2019-09-14      1234 2019-09-08 14:00:00    1234.0  1234_3
6 2019-09-14      8679 2019-09-07 12:00:00    8679.0  8679_1

Answer 1

pd.merge_asof 仅执行左连接。在尝试加速 groupby/merge_ordered 示例时经历了很多挫折后，在两个数据源上执行 pd.merge_asof 更加直观、更快不同方向，然后进行外连接将它们组合起来。

left_merge = pd.merge_asof(df1, df2,
    tolerance=pd.Timedelta('7d'), direction='backward', 
    left_on='endofweek', right_on='timestamp', 
    left_by='GroupCol', right_by='GroupVal')

right_merge = pd.merge_asof(df2, df1, 
    tolerance=pd.Timedelta('7d'), direction='forward', 
    left_on='timestamp', right_on='endofweek',
    left_by='GroupVal', right_by='GroupCol')

merged = (left_merge.merge(right_merge, how="outer")
    .sort_values(['endofweek', 'GroupCol', 'timestamp'])
    .reset_index(drop=True))

merged

   endofweek  GroupCol           timestamp  GroupVal TextVal
0 2019-08-31      1234 2019-08-30 10:00:00    1234.0  1234_1
1 2019-08-31      1234 2019-08-30 10:30:00    1234.0  1234_2
2 2019-08-31      8679                 NaT       NaN     NaN
3 2019-09-07      1234                 NaT       NaN     NaN
4 2019-09-07      8679                 NaT       NaN     NaN
5 2019-09-14      1234 2019-09-08 14:00:00    1234.0  1234_3
6 2019-09-14      8679 2019-09-07 12:00:00    8679.0  8679_1

此外，它比我的其他答案要快得多:

import time
n=1000
start=time.time()
for i in range(n):
    left_merge = pd.merge_asof(df1, df2,
        tolerance=pd.Timedelta('7d'), direction='backward', 
        left_on='endofweek', right_on='timestamp', 
        left_by='GroupCol', right_by='GroupVal')
    right_merge = pd.merge_asof(df2, df1, 
        tolerance=pd.Timedelta('7d'), direction='forward', 
        left_on='timestamp', right_on='endofweek',
        left_by='GroupVal', right_by='GroupCol')
    merged = (left_merge.merge(right_merge, how="outer")
        .sort_values(['endofweek', 'GroupCol', 'timestamp'])
        .reset_index(drop=True))

end = time.time()

end-start
15.040804386138916