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'
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