pandas merge asof with more than one match
IIUC, pd.merge_asof
, followed by a merge
should suffice:
(pd.merge_asof(ll, rr.date_right, left_on='date_left', right_on = 'date_right')
.merge(rr, on='date_right', how = 'left')
)
date_left date_right variable
0 2010-01-01 2010-01-01 12
1 2010-01-01 2010-01-01 6
2 2010-02-01 2010-01-01 12
3 2010-02-01 2010-01-01 6
this works also for the updated sample question:
(pd.merge_asof(ll, rr.date_right, left_on='date_left', right_on = 'date_right')
.merge(rr, on='date_right', how = 'left')
)
date_left date_right variable
0 2010-01-01 2010-01-01 12
1 2010-01-01 2010-01-01 6
2 2010-02-01 2010-01-01 12
3 2010-02-01 2010-01-01 6
4 2010-03-01 2010-03-01 3
5 2010-04-01 2010-03-01 3