Pandas中的Vlookup功能

Question

我有两个数据帧。我会在持股中添加额外的列，比如说“标准普尔数字”，并通过从持股中提取标准普尔来执行Vlookup，查看评级图，然后返回数值。你知道吗

持有量=

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

评级图=

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

请注意，在这种情况下，我们没有一个关键点，如果我想涵盖所有3个收视率，并与合并我必须总是删除额外的列，因为

Holdings =  Holdings.merge(RatingsMap[['Fitch,'Number #]],left_on='Rating_ 
Fitch', right_on='Fitch', how ='inner']

输出：

 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

不使用“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

最终结果将同时包含“标准普尔”和“评级标准普尔”），我不得不放弃。我想知道有没有更好的方法-像弗拉库普？你知道吗

Answer 1

只需对right_on使用RatingsMap['Fitch']，并删除初始字段中的'Fitch'。就像下面一样。你知道吗

Holdings =  Holdings.merge(RatingsMap[['Number #]],left_on='Rating_Fitch', right_on= RatingsMap['Fitch'], how ='inner']

下面是我所做的&输出也显示出来了

df2 =  df.merge(df1[['Number_#']],left_on='Rating_Fitch', right_on=df1['Fitch'], how ='inner')

我的数据帧命名为df&df1，而不是Holdings&Ratingsmap

输出

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