question

Upvotes
dianne.palmario avatar image
0 ●0 ●3 ●8

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

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.

workspace#technologypython apilseg-data-library
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Upvote
aramyan.h avatar image
7.3k ●18 ●2 ●8

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


screenshot-2024-10-18-at-095032.png (148.0 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dianne.palmario avatar image dianne.palmario
Hello @aramyan.h - this is it! How do I incorporate it with the formula so that the other columns would also be there while these separated columns are there?
aramyan.h avatar image aramyan.h

just assign your RD call to df variable and the call th function I have provided:

df = rd.discovery.searh(...)


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.