python - 将 Alphavantage JSON 文件导入 pandas

Question

网站 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)

Answer 1

你可以试试这个。我将 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