How to create separate columns for each RatingType found in RatingScope Column using python?

dianne.palmario
dianne.palmario LSEG
in Eikon Data APIs

import refinitiv.data as rd

rd.open_session()


rd.discovery.search(

view=rd.discovery.Views.MUNICIPAL_INSTRUMENTS,

top=10,

filter="(IsActive eq true and SearchAllCategoryv2 eq 'US Municipal Bonds' and (((((((((((((((((((((((RatingsScope((RatingType eq 'FTC'))) or (RatingsScope((RatingType eq 'FUN')))) or (RatingsScope((RatingType eq 'FST')))) or (RatingsScope((RatingType eq 'FUS')))) or (RatingsScope((RatingType eq 'FDR')))) or (RatingsScope((RatingType eq 'FRR')))) or (RatingsScope((RatingType eq 'MDY')))) or (RatingsScope((RatingType eq 'MUN')))) or (RatingsScope((RatingType eq 'MLI')))) or (RatingsScope((RatingType eq 'MLH')))) or (RatingsScope((RatingType eq 'MST')))) or (RatingsScope((RatingType eq 'MSH')))) or (RatingsScope((RatingType eq 'MUS')))) or (RatingsScope((RatingType eq 'MSI')))) or (RatingsScope((RatingType eq 'MLN')))) or (RatingsScope((RatingType eq 'FDL')))) or (RatingsScope((RatingType eq 'FDS')))) or (RatingsScope((RatingType eq 'MDT')))) or (RatingsScope((RatingType eq 'MDR')))) or (RatingsScope((RatingType eq 'MIS')))) or (RatingsScope((RatingType eq 'MSS')))))))",

select="LongName,Ticker,CouponRate,MaturityDate,CUSIP,RIC,MuniStateDescription,CouponClassDescription,EJVAssetID,BusinessEntity,PI,SearchAllCategoryv3,SearchAllCategoryv2,SearchAllCategory,IssueDate,BankQualified,BondInsuranceDescription,IsCallable,IsPutable,IsSinkable,RatingsScope(filter:((RatingType xeq 'FTC') or (RatingType xeq 'FUN') or (RatingType xeq 'FST') or (RatingType xeq 'FUS') or (RatingType xeq 'FDR') or (RatingType xeq 'FRR') or (RatingType xeq 'MDY') or (RatingType xeq 'MUN') or (RatingType xeq 'MLI') or (RatingType xeq 'MLH') or (RatingType xeq 'MST') or (RatingType xeq 'MUS') or (RatingType xeq 'MSH') or (RatingType xeq 'MSI') or (RatingType xeq 'MLN') or (RatingType xeq 'FDL') or (RatingType xeq 'FDS') or (RatingType xeq 'MDT') or (RatingType xeq 'MDR') or (RatingType xeq 'MIS') or (RatingType xeq 'MSS'))),RatingX1XRatingRank"

)

When I run this code in Codebook, the result is a single field, RatingScope Column, containing multiple dictionaries grouped by RatingType.


For example, a cell in RatingScope contains the following data:

[{'Agency': 'MDY', 'CurrentRating': 'A1', 'CurrentRatingDate': '2023-10-03T00:00:00.000Z', 'CurrentRatingRank': 5, 'Preliminary': 'n', 'RatingScope': 'D', 'RatingType': 'MDT', 'Solicited': 'y'},

{'Agency': 'MDY', 'CurrentRating': 'Aa3', 'CurrentRatingDate': '2020-04-23T00:00:00.000Z', 'CurrentRatingRank': 4, 'Preliminary': 'n', 'RatingScope': 'D', 'RatingType': 'MDR', 'Solicited': 'y'},

{'Agency': 'MDY', 'CurrentRating': 'MIG 1', 'CurrentRatingDate': '2024-06-17T00:00:00.000Z', 'CurrentRatingRank': 1, 'Preliminary': 'n', 'PreviousRatingDate': '2023-10-03T00:00:00.000Z', 'PreviousRatingRank': 1, 'RatingType': 'MUS', 'Solicited': 'y'},

{'Agency': 'MDY', 'CurrentRating': 'MIG 1', 'CurrentRatingDate': '2024-06-17T00:00:00.000Z', 'CurrentRatingRank': 1, 'Preliminary': 'n', 'PreviousRatingDate': '2023-10-03T00:00:00.000Z', 'PreviousRatingRank': 1, 'RatingType': 'MST', 'Solicited': 'y'}]


My question is: instead of pulling the entire RatingScope with multiple dictionaries per RatingType, how can I modify the code to retrieve separate columns for each RatingType? Specifically, I would like to extract the Agency, CurrentRating, and CurrentRatingDate for each of the 21 rating types, resulting in 63 columns (3 per RatingType).

For example:

  • Column 1: 'RatingType': 'MDT', 'Agency'
  • Column 2: 'RatingType': 'MDT', 'CurrentRating'
  • Column 3: 'RatingType': 'MDT', 'CurrentRatingDate'
  • Column 4: 'RatingType': 'MDR', 'Agency'
  • Column 5: 'RatingType': 'MDR', 'CurrentRating'
  • Column 6: 'RatingType': 'MDR', 'CurrentRatingDate'

And so on for all 21 rating types.

I appreciate your assistance and look forward to your suggestions.

Tagged:

Best Answer

  • aramyan.h
    aramyan.h admin
    Answer ✓

    Hi @dianne.palmario ,


    Although as much as I know it is not natively possible with the library, you can leverage python capabilities to achieve it. Below I have implemented a small function that does that based on my understanding:

    import pandas as pd

    def extract_rating_type_columns(df):
        for index, row in df.iterrows():
            for rating_dict in row['RatingsScope']:
                rating_type = rating_dict['RatingType']
                df.loc[index, f'{rating_type}_Agency'] = rating_dict.get('Agency')
                df.loc[index, f'{rating_type}_CurrentRating'] = rating_dict.get('CurrentRating')
                df.loc[index, f'{rating_type}_CurrentRatingDate'] = rating_dict.get('CurrentRatingDate')

    extract_rating_type_columns(df)

    df = df.drop(columns=['RatingsScope'])
    df

    screenshot-2024-10-18-at-095032.png

    Is this what you were looking for?


    Best regards,

    Haykaz

Answers

Categories