列中的最小数量

Question

最低评级由以下列表决定（/代表新的CLUMN），并取决于评级公司（MR、SPR或FR）。该列表显示了与其他公司相比，哪家公司的税率最低

评级表 先生:

Aaa/Aa1、Aa2、Aa3/A1、A2、A3/Baa1、Baa2、Ba3/Ba1、Ba2、Ba3/B1、B2、B3/Caa1、Caa2、Caa3、Ca、C
SPR：

AAA/AA+，AA，AA-/A+，A，A-/BBB+，BBB-/BB+，BB，BB-/B+，B，B-/CCC+，CCC，CCC-，CC，C，D
FR:

AAA/AA+，AA，AA-/A+，A，A-/BBB+，BBB-/BB+，BB，BB-/B+，B，B-/CCC，CC，C，DDD，DD，D

如下面的示例所示，如果多家公司在同一天对债券进行评级，则必须选择最低的 如何选择中字母最低的行

例如：

^{tb1}$

我希望以下行作为我的输出：

^{tb2}$

Answer 1

如果rating列只有从A到Z的值，则只需获取（根据python）最大值并选择行：

df[df['Rating'] == df['Rating'].max()]

Answer 2

代码：

import pandas as pd

#Sample dataframe
df = pd.DataFrame({"Issue_id":[5,5,5,5], "Rating_Date": ["2015-12-31"]*4, "Rating": ["Aaa", "BBB+", "BBB-", "C"], "Rating_Type":["MR", "SPR", "FR", "MR"]})

#Intializing company ratings
mr = pd.DataFrame({"Rating": ["Aaa/Aa1","Aa2","Aa3/A1","A2","A3/Baa1","Baa2","Baa3/Ba1","Ba2","Ba3/B1","B2","B3/Caa1","Caa2","Caa3","Ca","C"]})
spr = pd.DataFrame({"Rating": ["AAA/AA+","AA","AA-/A+", "A", "A-/BBB+","BBB","BBB-/BB+","BB","BB-/B+","B","B-/CCC+","CCC","CCC-","CC","C","D"]})
fr = pd.DataFrame({"Rating": ["AAA/AA+","AA","AA-/A+", "A", "A-/BBB+","BBB","BBB-/BB+","BB","BB-/B+","B","B-/CCC","CC","C","DDD","DD","D"]})

#Spliting ALias and original
mr.Rating = mr.Rating.map(lambda x: x.lower())
spr.Rating = spr.Rating.map(lambda x: x.lower())
fr.Rating = fr.Rating.map(lambda x: x.lower())

mr[['Rating','Alias']] = mr['Rating'].str.split('/',expand=True)
spr[['Rating','Alias']] = spr['Rating'].str.split('/',expand=True)
fr[['Rating','Alias']] = fr['Rating'].str.split('/',expand=True)

#Character score of the ratings
mr["char_score"] = mr.apply(lambda x: x["Rating"][0], axis = 1)
spr["char_score"] = spr.apply(lambda x: x["Rating"][0], axis = 1)
fr["char_score"] = fr.apply(lambda x: x["Rating"][0], axis = 1)

#Conditional numeric score of each character score
mr["num_score"] = mr.groupby("char_score").cumcount()
spr["num_score"] = spr.groupby("char_score").cumcount()
fr["num_score"] = fr.groupby("char_score").cumcount()

#Lower case of all ratings
df["Rating"] = df.Rating.map(lambda x: x.lower())


#Code to find char and num score for each row of the dataframe
def find_score(row):
    if row.Rating_Type == "MR":
        mr_rating = mr[(mr.Rating == row.Rating) | (mr.Alias == row.Rating)].iloc[0]
        return [mr_rating.char_score, mr_rating.num_score]
    elif row.Rating_Type == "SPR":
        spr_rating = spr[(spr.Rating == row.Rating) | (spr.Alias == row.Rating)].iloc[0]
        return [spr_rating.char_score, spr_rating.num_score]     
    elif row.Rating_Type == "FR":
        fr_rating = fr[(fr.Rating == row.Rating) | (fr.Alias == row.Rating)].iloc[0]
        return [fr_rating.char_score, fr_rating.num_score]


df["score"]= df.apply(lambda x: find_score(x), axis = 1)
df[['char_score','num_score']] = pd.DataFrame(df.score.tolist(), index= df.index)

df_sorted = df.sort_values(["char_score", "num_score"], ascending = False)
df_sorted.reset_index(inplace = True)
lowest_num, lowest_char = df_sorted['num_score'][0], df_sorted['char_score'][0]

output = df_sorted[(df_sorted["char_score"] == lowest_char) & (df_sorted["num_score"] == lowest_num)][["Issue_id", "Rating_Date", "Rating", "Rating_Type"]]