Prepare your ratings data¶

The following case-study demonstrates how to prepare your ratings for further processing.

Preliminary tasks¶

As a first step, we are going to import a portfolio into a pd.DataFrame. We'll call it port_df. This dataframe comprises a number of securities with respective weights and ratings from different rating agencies.

In [1]:

import pandas as pd

import pyratings as rtg

port_df = pd.read_excel("portfolio.xlsx")
port_df.head()

import pandas as pd import pyratings as rtg port_df = pd.read_excel("portfolio.xlsx") port_df.head()

Out[1]:

	ISIN	weight	BB Comp	SP	Moody	Fitch
0	ISIN00000001	0.518515	AAA	NR	Aaa *-	AAA
1	ISIN00000002	0.950810	AAA	AA+	Aaa	AAA
2	ISIN00000003	0.497176	AA+ *-	AA+	Aa2	NaN
3	ISIN00000004	0.648453	NR	NaN	NR	AA-u
4	ISIN00000005	0.674328	NR	NaN	NR	AA-u

Clean your ratings¶

By looking at the very first rows of port_df, we can see that some ratings do have a watch attached. This is marked by the *- suffix, which follows the actual rating after a blank. Other ratings are unsolicited. In this case, the rating is followed by the letter u.

Before we can use these ratings for further computations, we need to clean the ratings and get rid of everything that is not part of the actual rating. That is, we need to strip off watches etc.

We are going to use the get_pure_ratings function. This function works on strings, so we need to make sure to pass the relevant columns.

In [2]:

ratings_clean_df = rtg.get_pure_ratings(
    port_df.loc[:, ["BB Comp", "SP", "Moody", "Fitch"]]
)
ratings_clean_df.head()

ratings_clean_df = rtg.get_pure_ratings( port_df.loc[:, ["BB Comp", "SP", "Moody", "Fitch"]] ) ratings_clean_df.head()

Out[2]:

	BB Comp_clean	SP_clean	Moody_clean	Fitch_clean
0	AAA	NR	Aaa	AAA
1	AAA	AA+	Aaa	AAA
2	AA+	AA+	Aa2	NaN
3	NR	NaN	NR	AA-
4	NR	NaN	NR	AA-

As you can see, the suffix "_clean" has automatically been added to the column headings. Let's add these clean ratings to port_df by simple concatenation.

In [3]:

port_df = pd.concat([port_df, ratings_clean_df], axis=1)
port_df.head()

port_df = pd.concat([port_df, ratings_clean_df], axis=1) port_df.head()

Out[3]:

	ISIN	weight	BB Comp	SP	Moody	Fitch	BB Comp_clean	SP_clean	Moody_clean	Fitch_clean
0	ISIN00000001	0.518515	AAA	NR	Aaa *-	AAA	AAA	NR	Aaa	AAA
1	ISIN00000002	0.950810	AAA	AA+	Aaa	AAA	AAA	AA+	Aaa	AAA
2	ISIN00000003	0.497176	AA+ *-	AA+	Aa2	NaN	AA+	AA+	Aa2	NaN
3	ISIN00000004	0.648453	NR	NaN	NR	AA-u	NR	NaN	NR	AA-
4	ISIN00000005	0.674328	NR	NaN	NR	AA-u	NR	NaN	NR	AA-

Consolidate your ratings¶

The natural next step is to consolidate the ratings; i.e. we need to assign one and only one concrete rating to any individual security.

Let's compute the worst ratings and directly concatenate the newly computed column to our existing port_df in one batch. pyratings automatically names the new column "worst_rtg".

We are going to use get_worst_ratings and use S&P's rating scale to display the data.

In [4]:

port_df = pd.concat(
    [
        port_df,
        rtg.get_worst_ratings(
            ratings=ratings_clean_df,
            rating_provider_input=["Bloomberg", "S&P", "Moody's", "Fitch"],
            rating_provider_output="S&P"
        )
    ],
    axis=1,
)

port_df.head()

port_df = pd.concat( [ port_df, rtg.get_worst_ratings( ratings=ratings_clean_df, rating_provider_input=["Bloomberg", "S&P", "Moody's", "Fitch"], rating_provider_output="S&P" ) ], axis=1, ) port_df.head()

Out[4]:

	ISIN	weight	BB Comp	SP	Moody	Fitch	BB Comp_clean	SP_clean	Moody_clean	Fitch_clean	worst_rtg
0	ISIN00000001	0.518515	AAA	NR	Aaa *-	AAA	AAA	NR	Aaa	AAA	AAA
1	ISIN00000002	0.950810	AAA	AA+	Aaa	AAA	AAA	AA+	Aaa	AAA	AA+
2	ISIN00000003	0.497176	AA+ *-	AA+	Aa2	NaN	AA+	AA+	Aa2	NaN	AA
3	ISIN00000004	0.648453	NR	NaN	NR	AA-u	NR	NaN	NR	AA-	AA-
4	ISIN00000005	0.674328	NR	NaN	NR	AA-u	NR	NaN	NR	AA-	AA-

There you are. You have all your securities and only one concrete rating attached to each one (in this case the worst rating).

In [5]:

port_df.loc[:, ["ISIN", "weight", "worst_rtg"]].head()

port_df.loc[:, ["ISIN", "weight", "worst_rtg"]].head()

Out[5]:

	ISIN	weight	worst_rtg
0	ISIN00000001	0.518515	AAA
1	ISIN00000002	0.950810	AA+
2	ISIN00000003	0.497176	AA
3	ISIN00000004	0.648453	AA-
4	ISIN00000005	0.674328	AA-