parse RKD fundamental api data from json to pandas dataframe

vijay.singh
vijay.singh Explorer
in TRKD

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).

image


Tagged:

Best Answer

  • Gurpreet
    Gurpreet admin
    Answer ✓

    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)


    image


Answers

Welcome!

It looks like you're new here. Sign in or register to get started.
Sign In
Register

Welcome!

It looks like you're new here. Sign in or register to get started.
Sign In
Register

Quick Links

Categories