pandas - 从 JSON 数据构建 pandas DataFrame

Question

我仍然是一个 Python 菜鸟，在使用 JSON 数据结构时我束手无策。例如，我尝试将从 Alpha Vantage 获得的数据加载到 DataFrame 中以进行进一步处理。JSON 如下所示:

{
"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"
    },...

我正在尝试构建一个仅包含日期和调整后的收盘价的数据框。

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

这会返回类似的内容

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

所以这里元数据已经从时间序列中分离出来。但是我现在如何通过时间序列来访问每天的“调整后收盘价”？

如果有人能帮助我解决这个问题，那就太好了!

Answer 1

由于您已经在使用 json 模块来解析 JSON，因此您可以按以下方式创建 DataFrame，然后对其进行切片以获得调整后的关闭值。

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

根据您提供的示例数据，adj_close 将是一个如下所示的 Pandas 系列:

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