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!

Build pandas DataFrame from JSON data

1 Answers