Search API: how to structure the credit rating data like in the GOVSRCH interface?
If I add a credit rating filter to my query in GOVSRCH, I get a nicely formatted table with a nested column containing e.g. Moody's Long-term Issuer Rating. See screenshot.
If I run the same query with the API, I get several columns that look like they are related to the credit rating filter, but contain only the raw data. See second screenshot.
I want to structure this data into a nicely formatted nested column structure in the same way as it is done in the GOVSRCH interface. How can I do this?
Best Answer
-
Hi @tr105 ,
I believe, we need to further transform the output we got from the API with Pandas toolings and that is what I guess is done under the hood by GOVSEARCH.
I wrote a quick code which does that but that should be carefully reviewed and tested before using since it is out of the scope of API support and might not be the perfect solution. In any case, I believe this should be the way to go (I mean df restructuring) as the API wouldn't output the way it is outputted in GOVSEARCH. Here is my solution:
import refinitiv.data as rd
import pandas as pd
rd.open_session ()
ratings = rd.discovery.search(
view =rd.discovery.Views.GOV_CORP_INSTRUMENTS,
top = 10,
select = 'DocumentTitle, RIC, RatingsScope'
)
ratings = ratings.explode('RatingsScope')
ratings = pd.concat([ratings.drop(['RatingsScope'], axis=1), ratings['RatingsScope'].apply(pd.Series)], axis=1)
agencies = ratings['Agency'].unique()
new_df = pd.DataFrame()
for agency in agencies:
columns = pd.MultiIndex.from_tuples([("DocumentTitle" , ""),
("RIC", ""), ("Agency", "")] + [(agency, col) for col in ratings.columns[3:]])
df_transformed = pd.DataFrame(ratings , columns = columns)
for item in range(len(columns)):
df_transformed.loc[: , columns[item]] = ratings.iloc[: , item]
new_df = pd.concat([new_df, df_transformed], axis =1)
new_df = new_df.loc[:, ~new_df.columns.duplicated()]
new_df = new_df.drop('Agency', axis=1, level=0)
new_df.head()Please note that in my output the name of multyindex column for eg 'Moody's Long-term Issuer Rating' is MDY. You may create a dict to map name with codes and read it from the dict in the loop. I didn't do that since I don't know the name making. In case you are not aware of that as well, you may raise content quiry via the MyRefinitiv.
Again, this may not be the best solution to transform the original dataframe, but I think that's the way of getting an output similar to the GOVSEARCH one.
Hope this helps.
Best regards,
Haykaz
0
Answers
-
Hi @tr105,
Thank you for your question. I am not aware of a direct way of solving the nested challenge from the API, however, may provide a workaround using Pandas Explode function.
Since I don't have your complete code I will demo in a simpler example which you can extend to your use case:
import refinitiv.data as rd
rd.open_session ()
df = rd.discovery.search(
view =rd.discovery.Views.GOV_CORP_INSTRUMENTS,
top = 10,
select = 'DocumentTitle, RIC, RatingsScope'
)
dfBelow I transform the df with Explode function and apply series to RatingsScope components:
df = df.explode('RatingsScope')
df = pd.concat([df.drop(['RatingsScope'], axis=1), df['RatingsScope'].apply(pd.Series)], axis=1)
dfI am not sure if this is exactly what you were looking for, but I am hopeful it will help to take this further.
Best regards,
Haykaz
0 -
Hi Haykaz, thanks for your clear answer.
It does not answer my question, though. Maybe I wasn't clear.
The point is that in your example the data is still not structured in the way that GOVSRCH does it (see screenshot below).
In the API response, the RatingsScope field contains the below array of objects for each bond. Note that there can be more entries in this array if you add more RatingsScope filters to the query. Under the hood, GOVSRCH restructures this array into the columns that you can see below in the screenshot.
Note that the RatingsScope data returned by the API does not contain any string like 'Moody's Long-term Issuer Rating'. GOVSRCH seems to conjure those from thin air. We want to replicate that.
Note that we are NOT asking how to create nested columns in Pandas dataframes, we don't care about that.
"RatingsScope": [
{
"Agency": "MDY",
"CurrentRating": "Baa1",
"CurrentRatingDate": "2018-08-09T00:00:00.000Z",
"CurrentRatingRank": 8,
"Preliminary": "n",
"RatingScope": "F",
"RatingType": "MIS",
"Solicited": "y"
},
{
"Agency": "FTC",
"CurrentRating": "WD",
"CurrentRatingDate": "2018-01-11T00:00:00.000Z",
"CurrentRatingRank": 98,
"Preliminary": "n",
"RatingScope": "F",
"RatingType": "FDL",
"Solicited": "y"
}
],0 -
Hi
@haykaz.aramyan , I couldn't add my response in a reply to your message due to some UI bug, so I added it below instead.0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛