I have 2 Dataframes. I excel i would add additional columns to Holdings lets say “S&P Number” and perform Vlookup by taking S&P from Holdings and look in Rating Map then return Number value.
Holdings =
Security Buy/Sell Rating_S&P Rating_Moody Rating_Fitch XS1843430536 B_T (P)AAA N.A. AAAe US912810EH78 BUY N.A. Aaa AAAu US912828F213 BUY N.A. Aaa AAAu US912828G872 BUY N.A. Aaa AAAu US912828G872 BUY N.A. Aaa AAAu
Ratings Map =
Fitch Moody´s S&P DBRS Number_# Final_Rating
NaN NaN NaN NaN 23 NaN
AAA Aaa AAA AAA 22 AAA
AAAu NaN NaN AAAu 22 NaN
AAAe Aaae NaN NaN 22 NaN
AAA/NR (P)Aaa (P)AAA (P)AAA 22 NaN
AAA/*- Aaa/*- NaN NaN 22 NaN
NaN Aaau NaN NaN 22 NaN
AA+ Aa1 AA+ AAhigh 21 AA+
NaN Aa1u NaN NaN 21 NaN
NaN Aa1/*+ AA+/*+ AAH 21 NaN
Please note that we dont have one key in this situation, if i want to cover all 3 Ratings and go with merge i have to always drop additional column because
Holdings = Holdings.merge(RatingsMap[['Fitch,'Number #]],left_on='Rating_ Fitch', right_on='Fitch', how ='inner']
Output:
Security Buy/Sell Rating_S&P Rating_Moody Rating_Fitch Number_# Fitch XS1843430536 B_T (P)AAA N.A. AAAe 22.0 AAAe US912810EH78 BUY N.A. Aaa AAAu 22.0 AAAu US912828F213 BUY N.A. Aaa AAAu 22.0 AAAu US912828G872 BUY N.A. Aaa AAAu 22.0 AAAu US912828G872 BUY N.A. Aaa AAAu 22.0 AAAu
Desired Output without using “drop”:
Security Buy/Sell Rating_S&P Rating_Moody Rating_Fitch Number_# XS1843430536 B_T (P)AAA N.A. AAAe 22.0 US912810EH78 BUY N.A. Aaa AAAu 22.0 US912828F213 BUY N.A. Aaa AAAu 22.0 US912828G872 BUY N.A. Aaa AAAu 22.0 US912828G872 BUY N.A. Aaa AAAu 22.0
Final result will contain both “S&P” and “Rating_S&P”) which i have to drop. I was wondering if there is any better way to do that – like vlookup?
Advertisement
Answer
Just use RatingsMap['Fitch'] for right_on and remove 'Fitch' in the initial field. Like below.
Holdings = Holdings.merge(RatingsMap[['Number #]],left_on='Rating_Fitch', right_on= RatingsMap['Fitch'], how ='inner']
Below is what i have done & the output is also shown
df2 = df.merge(df1[['Number_#']],left_on='Rating_Fitch', right_on=df1['Fitch'], how ='inner')
My dataframes are named as df & df1 instead of Holdings & Ratingsmap
output
Security Buy/Sell Rating_S&P Rating_Moody Rating_Fitch Number_# XS1843430536 B_T (P)AAA N.A. AAAe 22 US912810EH78 BUY N.A. Aaa AAAu 22 US912828F213 BUY N.A. Aaa AAAu 22 US912828G872 BUY N.A. Aaa AAAu 22 US912828G872 BUY N.A. Aaa AAAu 22