I have looked for other related topics on SO only finding similar issues, but nothing that can help me.
I am querying AlphaVantage for stock data. I recieved the data and decode, but am currently unable to convert to a pandas dataframe due to format issues. The response is in the below form:
{
"Meta Data": {
"1. Information": "Daily Prices (open, high, low, close) and Volumes",
"2. Symbol": "AAPL",
"3. Last Refreshed": "2021-04-26",
"4. Output Size": "Full size",
"5. Time Zone": "US/Eastern"
},
"Time Series (Daily)": {
"2021-04-26": {
"1. open": "134.8300",
"2. high": "135.0600",
"3. low": "133.5600",
"4. close": "134.7200",
"5. volume": "66905069"
},
"2021-04-23": {
"1. open": "132.1600",
"2. high": "135.1200",
"3. low": "132.1600",
"4. close": "134.3200",
"5. volume": "78756779"
},
"2021-04-22": {
"1. open": "133.0400",
"2. high": "134.1500",
"3. low": "131.4100",
"4. close": "131.9400",
"5. volume": "84566456"
},
After running the below code:
import requests as rq
import json
base_url = "https://www.alphavantage.co/query?"
params = {"function":function, "symbol":symbol, "outputsize":output_size, "datatype":data_type, "apikey":api_key}
response = rq.get(base_url, params=params)
data_str = data_bytes.decode("utf-8")
My issue arises when trying to load the data to a dataframe:
data_dict = json.loads(data_str)
df = pd.DataFrame(data_dict.items())
df.head()
Returns:
0 1
0 Meta Data {'1. Information': 'Daily Prices (open, high, ...
1 Time Series (Daily) {'2021-04-26': {'1. open': '134.8300', '2. hig...
And...
data_dict = json.loads(data_str)
df = pd.DataFrame(data_dict)
df.head()
Returns:
Meta Data Time Series (Daily)
1. Information Daily Prices (open, high, low, close) and Volumes NaN
2. Symbol AAPL NaN
3. Last Refreshed 2021-04-26 NaN
4. Output Size Full size NaN
5. Time Zone US/Eastern NaN
Which neither are usable. I am after a dataframe of the form:
date open high low close volume
Is there a way to get the response into this format?
As you need Time Series (Daily) values only. So you can use directly when you create dataframe
data_dict = json.loads(data_str)
df = pd.DataFrame(data_dict["Time Series (Daily)"])
df = df.T # Transpose Dataframe for desired results