Parsing JSON into Dataframe-CodePudding

Need to parse quarterly earnings for a ticker (in JSON) from alphavantage API and convert to dataframe. What am I doing wrong?

Details below:

Output:

[{'symbol': 'AMZN', 'annualReports': [{'fiscalDateEnding': '2021-12-31', 'reportedCurrency': 'USD', 'grossProfit': '1269', 'totalRevenue': '4679'}, 
{'fiscalDateEnding': '2020-12-31', 'reportedCurrency': 'USD', 'grossProfit': '1025', 'totalRevenue': '3844'}, 
], 'quarterlyReports': [{'fiscalDateEnding': '2022-06-30', 'reportedCurrency': 'USD', 'grossProfit': '3959', 'totalRevenue': '1205'}, 
{'fiscalDateEnding': '2022-03-31', 'reportedCurrency': 'USD', 'grossProfit': '3701', 'totalRevenue': '1159'}]}]

Want:

fiscalDateEnding    reportedCurrency    grossProfit    totalRevenue 
6/30/2022           USD                 3959           1205
3/31/2022           USD                 3701           1159

Code:

 i = 'AMZN'
    #endpoint for hitting alphavantage api
    income_statement_url = ["https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=%s&apikey=APIKEY" % (i)]
    
    #json response
    income_statement_json = [(json.loads(requests.get(i).text)) for i in income_statement_url]
    
    income_statement_df = [(pd.DataFrame.from_dict(i['totalRevenue'], orient = 'index').sort_index(axis = 1).assign(ticker = i['symbol']['quarterlyReports'])) for i in price_json]

Error Message:

Heading KeyError: 'totalRevenue'

CodePudding user response：

You want .json_normalize

df = pd.json_normalize(data=data, record_path="quarterlyReports")
print(df)

  fiscalDateEnding reportedCurrency grossProfit totalRevenue
0       2022-06-30              USD        3959         1205
1       2022-03-31              USD        3701         1159