Exactly Vlookup functionality in Pandas

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?

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