Smallest amount within a column

Admin 1
Viewed 88

The lowest rating is decided using the following list (/ represent a new cloumn) and is dependent upon the rating company (MR, SPR or FR). The list shows which is the lowest rate in comparison to other companies

Ratings list MR:

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

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

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

As shown in the example below, if multiple companies rate a bond on the same day, the lowest must be chosen How would I select the row with the lowest letter in?

For example:

issue_id Rating_Date Rating Rating_type
5 2015-12-31 Aaa MR
5 2015-12-31 BBB+ SPR
5 2015-12-31 BBB- FR
5 2015-12-31 C MR

I would be wanting the following row as my output:

issue_id Rating_Date Rating Rating_type
5 2015-12-31 C MR
pandas dataframe min python
2 Answers

Code:

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"]]
user_148844540

If the rating column only has values from A to Z you can simply get the (according to python) maximum value and select the rows:

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