parse RKD fundamental api data from json to pandas dataframe

vijay.singh

Hello Team, I am facing problem in parsing RKD fundamental api data from json to pandas dataframe.

---

Code:

appid = ""

token = ""

# input_1 : appid and token

# RKD api request message headers

message_headers = {

'content-type': 'application/json;charset=utf-8' ,

'X-Trkd-Auth-ApplicationID': appid,

'X-Trkd-Auth-Token' : token

}

# input_2 : api url

# RKD FinancialStatements Service URL

api_url = 'http://api.trkd.thomsonreuters.com/api/Fundamentals/Fundamentals.svc/REST/Fundamentals_1/GetFinancialStatementsReports_1'

# input 3 : message_request(api dependent)

company_id = 'IBM.N'

message_request = {

'GetFinancialStatementsReports_Request_1':{

'companyId': company_id,

'companyIdType': 'RIC'

}

}

# get api response

api_response = doSendRequest(api_url, message_request, message_headers)

print(api_response)

---

I have attached the api json response in the text file.

GetFinancialStatementsReports_Request_1_response.txt

PFB the screenshot of expected data format(similar format is required in pandas dataframe).

Gurpreet

Hi @vijay.singh,

Its best to seek help related to pandas on their docs site or on stackexchange. I tried this and was able to get some matching content. Other items like renaming index series and appending data will require iterative python loops.

jData = json.loads(data_string)
df = pd.DataFrame()
for fYear in jData['GetFinancialStatementsReports_Response_1']['FundamentalReports']['ReportFinancialStatements']['FinancialStatements']['AnnualPeriods']['FiscalPeriod']:
    df1 = pd.DataFrame(fYear['Statement'][0]['lineItem']).set_index('coaCode').rename(columns={'Value': fYear['FiscalYear']})
    if not df.size:
        df = df.append(df1)
    else:
        df = df.join(df1)

vijay.singh

@Gurpreet, thanks a lot for above example. I will surely help me to proceed further.