Join Dataframe Based On Date Which Is Between Dates From Another Dataframe

I have two Pandas Data Frames. First one looks like (db1): id date_from date_until v1 v2 0 2015-06-17 2015-06-19 2 3 0 2015-06-17 2015-07-01 3 4 1 2

Solution 1:

Let's use pd.merge_asof:

pd.merge_asof(df1,df2, left_on=['date_from'], right_on=['date'], by='id')

Output:

iddate_fromdate_untilv1v2datev3v4002015-06-17  2015-06-19   232015-06-17   43102015-06-17  2015-07-01   342015-06-17   43212015-06-18  2015-06-20   562015-06-18   64312015-06-19  2015-07-12   542015-06-18   64

Solution 2:

Another way to do it:

import pandas as pd

import sys
if sys.version_info[0] < 3: 
    from StringIO import StringIO
else:
    from io import StringIO

a = StringIO('''id,date_from,date_until,v1,v2
0,2015-06-17,2015-06-19,2,3
0,2015-06-17,2015-07-01,3,4
1,2015-06-18,2015-06-20,5,6
1,2015-06-19,2015-07-12,5,4''')

b = StringIO('''id,date,v3,v4
0,2015-06-17,4,3
1,2015-06-17,5,6 
1,2015-06-18,6,4''')

df1 = pd.read_csv(a, sep=',')
df2 = pd.read_csv(b, sep=',')
df1.merge(df2, how='left', left_on=['id', 'date_from'], right_on=['id', 'date']).ffill()