Build pandas DataFrame from JSON data

Question

I'm still a python rookie and I'm at my wits' end when it comes to working with a JSON data structure. For example, I tried to load data obtained from Alpha Vantage to a DataFrame for further processing. The JSON looks like this:

{
"Meta Data": {
    "1. Information": "Daily Time Series with Splits and Dividend Events",
    "2. Symbol": "SHAK",
    "3. Last Refreshed": "2017-11-03",
    "4. Output Size": "Compact",
    "5. Time Zone": "US/Eastern"
},
"Time Series (Daily)": {
    "2017-11-03": {
        "1. open": "35.9000",
        "2. high": "37.0700",
        "3. low": "35.5600",
        "4. close": "36.9800",
        "5. adjusted close": "36.9800",
        "6. volume": "874351",
        "7. dividend amount": "0.0000",
        "8. split coefficient": "1.0000"
    },
    "2017-11-02": {
        "1. open": "38.5000",
        "2. high": "38.7000",
        "3. low": "35.4300",
        "4. close": "35.9000",
        "5. adjusted close": "35.9000",
        "6. volume": "1860695",
        "7. dividend amount": "0.0000",
        "8. split coefficient": "1.0000"
    },
    "2017-11-01": {
        "1. open": "37.8800",
        "2. high": "38.2600",
        "3. low": "36.9600",
        "4. close": "37.1500",
        "5. adjusted close": "37.1500",
        "6. volume": "1350008",
        "7. dividend amount": "0.0000",
        "8. split coefficient": "1.0000"
    },...

I am trying to build a dataframe that contains the Date and the adjusted close only.

from urllib.request import Request, urlopen
import json
import pandas as pd
from pandas.io.json import json_normalize

request=Request('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=SHAK&apikey=topsecret')
response=urlopen(request)

x=response.read()
data=json.loads(x)
df=pd.read_json(x,typ='series')

This returns something like

Meta Data              {'1. Information': 'Daily Time Series with Spl...
Time Series (Daily)    {'2017-11-03': {'1. open': '96.1700', '2. high...
dtype: object

So here the metadata is already separated from the the time series. But how would I now work through the time series to access each day's "adjusted close"?

It'd really be great if someone could help me with this!

ah, I found this discussion https://stackoverflow.com/questions/44742003/alphavantage-api-stock-market-indices and it might be helpful... — RazzleDazzle, Nov 05 '17 at 17:42

score 0 · Answer 1 · answered Nov 29 '17 at 19:44

Since you are already using the json module to parse the JSON, you can create your DataFrame the following way, and then slice it to get just the adjusted close.

request=Request('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=SHAK&apikey=topsecret')
response=urlopen(request)

data=json.loads(response.read())
df=pd.DataFrame.from_dict(data['Time Series (Daily)'], orient="index")

# Probably want that index to be a DatetimeIndex
df.index = pd.to_datetime(df.index)

# To get a pandas series that just has adjusted close, select that column
adj_close = df['5. adjusted close']

With the sample data you provided, adj_close will be a Pandas series that looks like this:

2017-11-01    37.1500
2017-11-02    35.9000
2017-11-03    36.9800
Name: 5. adjusted close, dtype: object

Build pandas DataFrame from JSON data

1 Answers1