I have the following 2 data frames:
df1 = pd.DataFrame({
'dates': ['02-Jan','03-Jan','30-Jan'],
'currency': ['aud','gbp','eur'],
'amount': [100,330,500]
})
df2 = pd.DataFrame({
'dates': ['01-Jan','02-Jan','03-Jan','30-Jan'],
'aud': [0.72,0.73,0.74,0.71],
'gbp': [1.29,1.30,1.4,1.26],
'eur': [1.15,1.16,1.17,1.18]
})
I want to obtain the intersection of df1.dates
& df1.currency
. For eg: Looking up the prevalent 'aud'
exchange rate on '02-Jan'
It can be solved using the Index + Match
functionality of excel. What shall be the best way to replicate it in Pandas.
Desired Output: add a new column 'price'
dates currency amount price
02-Jan aud 100 0.73
03-Jan gbp 330 1.4
30-Jan eur 500 1.18
The best equivalent of INDEX MATCH is DataFrame.lookup
:
df2 = df2.set_index('dates')
df1['price'] = df2.lookup(df1['dates'], df1['currency'])