merging 2 data-frame based on nearest value in python

user_10698069 0
Viewed 67

after all cleaning data , i got to this point. can anyone help me please to finish it off?

I have 2 dataframe that after all cleaning and merging need to be merge to each other at the end ! 

df1:
PriceDate   tenor   ccy
3/07/2019   6   USD_AED

df2:
PriceDate   Term_By_Day     Ccy   rate
1/07/2019   7            USD_AED    2.34
2/07/2019   7            USD_AED    3.45
4/07/2019   7            USD_AED    4.89

so my goal is to merge this 2 data-frame based on exact "Ccy" and nearest "pricedate" and nearest "Tenor with Term_by_day". 

so the ideal output will be :   
PriceDate   tenor    ccy       rate
3/07/2019   6     USD_AED      whatever it should be

I believe i have to use interpolation with method=nearest ????? but i'm so lost now !

Can you please give me any tip if i'm on the right track and if interpolation is the best way to do it ?

cheers,

Z

dataframe interpolation python
1 Answers

You can find the nearest PriceDate in df2 for all rows in df1 like this: 

nearest_rates = []
for row in df1.itertuples(index=False):
    nearest_index = df2.set_index('PriceDate').index.get_loc(row.PriceDate, method='nearest')
    nearest_rate  = df2.iloc[nearest_index]['rate']
    nearest_rates.append(nearest_rate)
df1['rate'] = nearest_rates

For your example, this will find nearest_index = 2 (due to the nearest method) with nearest_rate = 4.89 and append this to df1.

It is not quite clear to me if you need a combined dataframe or if you want to include the nearest rates from df2 in df1. For actually joining/merging the two sources there are multiple methods and you will have to define your specific conditions (column names e.g. Ccy vs. ccy; handling of possible duplicates etc.).

user_120565640
Related