python - 将Alphavantage JSON文件导入 Pandas

网站AlphaVantage具有JSON格式可供下载：

https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=MSFT&apikey=demo

输出格式如下：

{
    "Meta Data": {
        "1. Information": "Daily Time Series with Splits and Dividend Events",
        "2. Symbol": "MSFT",
        "3. Last Refreshed": "2019-12-24",
        "4. Output Size": "Compact",
        "5. Time Zone": "US/Eastern"
    },

    "Time Series (Daily)": {
        "2019-12-24": {
            "1. open": "157.4800",
            "2. high": "157.7100",
            "3. low": "157.1150",
            "4. close": "157.3800",
            "5. adjusted close": "157.3800",
            "6. volume": "8989150",
            "7. dividend amount": "0.0000",
            "8. split coefficient": "1.0000"
        },

        "2019-12-23": {
            "1. open": "158.1200",
            "2. high": "158.1200",
            "3. low": "157.2700",
            "4. close": "157.4100",
            "5. adjusted close": "157.4100",
            "6. volume": "17726283",
            "7. dividend amount": "0.0000",
            "8. split coefficient": "1.0000"
        },

我希望退回以下物品：

[Symbol, Date, Closing]

MSFT, 2019-12-24, 157.3800

MSFT, 2019-12-23, 157.4100

坚持下面的代码：

import requests

import json

from pandas.io.json import json_normalize

url = 'https://www.alphavantage.co/queryfunction=TIME_SERIES_DAILY_ADJUSTED&symbol=MSFT&apikey=demo
'

jsonfetch = json.loads(requests.get(url))

df = json_normalize(jsonfetch, max_level=1, errors='ignore')

print(df)

最佳答案

你可以试试看我将jsonfetch设置为您在顶部的字典

x=jsonfetch

def createDF(x):
    advdict = {}
    advdict['Symbol'] = {}
    advdict['Date'] = {}
    advdict['Closing'] = {}
    symbol = x['Meta Data']['2. Symbol']
    date = list(d['Time Series (Daily)'].keys())
    closing = []
    for y in d['Time Series (Daily)'].keys():
       closing.append(d['Time Series (Daily)'][y]['4. close'])
    for y in range(len(closing)):
        advdict['Symbol'].update({y:symbol})
        advdict['Date'].update({y:date[y]})
        advdict['Closing'].update({y:closing[y]})
    return pd.DataFrame(advdict)

createDF(d)

  Symbol        Date   Closing
0   MSFT  2019-12-24  157.3800
1   MSFT  2019-12-23  157.4100