pd.merge_asof with multiple matches per time period?

Question

I'm trying to merge two dataframes by time with multiple matches. I'm looking for all the instances of df2 whose timestamp falls 7 days or less before endofweek in df1. There may be more than one record that fits the case, and I want all of the matches, not just the first or last (which pd.merge_asof does).

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'])

I've tried

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

but that gets me

   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

I'm losing the text 1234_1. Is there way to do a sort of outer join for pd.merge_asof, where I can keep all of the instances of df2 and not just the first or last?

My ideal result would look like this (assuming that the endofweek times are treated like 00:00:00 on that date):

   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

score 1 · Accepted Answer · answered Dec 06 '19 at 19:04

pd.merge_asof only does a left join. After a lot of frustration trying to speed up the groupby/merge_ordered example, it's more intuitive and faster to do pd.merge_asof on both data sources in different directions, and then do an outer join to combine them.

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

In addition, it is much faster than my other answer:

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

This will still only work when the max. amount of matches between df1 and df2 is 2, right? — Bebeerna, Nov 16 '22 at 17:02

score 0 · Answer 2 · answered Nov 12 '19 at 18:47

One way I tried is using groupby on one data frame, and then subsetting the other one in a pd.merge_ordered:

merged = (df1.groupby(['GroupCol', 'endofweek']).
apply(lambda x: pd.merge_ordered(x, df2[(
(df2['GroupVal']==x.name[0])
&(abs(df2['timestamp']-x.name[1])<=pd.Timedelta('7d')))], 
left_on='endofweek', right_on='timestamp')))

merged

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

merged[['endofweek', 'GroupCol']] = (merged[['endofweek', 'GroupCol']]
.fillna(method="bfill"))

merged.reset_index(drop=True, inplace=True)

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

However it seems to me the result is very slow:

import time
n=1000
start=time.time()
for i in range(n):
    merged = (df1.groupby(['GroupCol', 'endofweek']).
    apply(lambda x: pd.merge_ordered(x, df2[(
    (df2['GroupVal']==x.name[0])
    &(abs(df2['timestamp']-x.name[1])<=pd.Timedelta('7d')))], 
    left_on='endofweek', right_on='timestamp')))

end = time.time()

end-start
40.72932052612305

I would greatly appreciate any improvements!

pd.merge_asof with multiple matches per time period?

2 Answers2

Linked