CODEWITHSUNDEEP
pythonpandas
ben121
ben121

Reputation: 897

pandas merge on date range

I have two dataframes, 

df = pd.DataFrame({'Date': ['2011-01-02', '2011-04-10', '2015-02-02', '2016-03-03'], 'Price': [100, 200, 300, 400]})

df2 = pd.DataFrame({'Date': ['2011-01-01', '2014-01-01'], 'Revenue': [14, 128]})

I want add df2.revenue to df to produce the below table using the both the date columns for reference. 

Date         Price      Revenue
2011-01-02   100        14
2011-04-10   200        14
2015-02-02   300        128
2016-03-03   400        128

As above the revenue is added according df2.Date and df.Date

Upvotes: 3

Views: 2933

Answers (1)

jezrael
jezrael

Reputation: 862431

Use merge_asof:

df['Date'] = pd.to_datetime(df['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])

df3 = pd.merge_asof(df, df2, on='Date')
print (df3)
        Date  Price  Revenue
0 2011-01-02    100       14
1 2011-04-10    200       14
2 2015-02-02    300      128
3 2016-03-03    400      128

Upvotes: 6

Related Questions