从JSON到pd dataframe python的Marketstack数据

Question

我想将marketstack api的“日内”输出从JSON转换为pd数据帧，但出现以下错误：

data = r.json()['intraday']
KeyError: 'intraday'

我使用的Python代码：

r = requests.get('http://api.marketstack.com/v1/tickers/AAPL/intraday?access_key=KEY')
x = r.json()
data = r.json()['intraday']
df = pd.DataFrame.from_dict(data)
print(df)

API（r）的输出：

{'pagination': {'limit': 100, 'offset': 0, 'count': 100, 'total': 4279}, 'data': {'name': 'APPLE INC', 'symbol': 'AAPL', 'has_intraday': True, 'has_eod': False, 'country': None, 'stock_exchange': {'name': 'Investors Exchange', 'acronym': 'IEX', 'mic': 'IEXG', 'country': 'USA', 'country_code': 'US', 'city': 'New York', 'website': 'www.iextrading.com'}, 'intraday': [{'open': 113.73, 'high': 116.55, 'low': 112.88, 'last': 113.84, 'close': 115.04, 'volume': 1293135.0, 'date': '2020-10-26T18:00:00+0000', 'symbol': 'AAPL', 'exchange': 'IEXG'}, {'open': 113.73, 'high': 116.55, 'low': 113.59, 'last': 113.9, 'close': 115.04, 'volume': 1027116.0, 'date': '2020-10-26T17:00:00+0000', 'symbol': 'AAPL', 'exchange': 'IEXG'}, {'open': 113.73, 'high': 116.55, 'low': 113.59, 'last': 114.885, 'close': 115.04, 'volume': 506229.0, 'date': '2020-10-26T15:00:00+0000', 'symbol': 'AAPL', 'exchange': 'IEXG'}, {'open': 113.73, 'high': 115.855, 'low': 113.59, 'last': 115.69, 'close': 115.04, 'volume': 223879.0, 'date': '2020-10-26T14:00:00+0000', 'symbol': 'AAPL', 'exchange': 'IEXG'}, {'open': 113.73, 'high': 113.83, 'low': 113.59, 'last': 113.65, 'close': 115.04, 'volume': 930.0, 'date': '2020-10-26T13:00:00+0000', 'symbol': 'AAPL', 'exchange': 'IEXG'}

如果有人有解决方案，那就太好了，提前谢谢

Answer 1

下次看到KeyError被引发时，请首先检查可用键：

data = r.json()
print(data.keys())
# dict_keys(['pagination', 'data'])
print(data["data"].keys())
# dict_keys(['name', 'symbol', 'has_intraday', 'has_eod', 'country', 'stock_exchange', 'intraday'])

---

pd.json_normalize(data["data"]["intraday"])
    open    high    low last    close   volume  date    symbol  exchange
0   113.73  116.55  112.88  113.84  115.04  1293135.0   2020-10-26T18:00:00+0000    AAPL    IEXG
1   113.73  116.55  113.59  113.9   115.04  1027116.0   2020-10-26T17:00:00+0000    AAPL    IEXG
2   113.73  116.55  113.59  114.885 115.04  506229.0    2020-10-26T15:00:00+0000    AAPL    IEXG
3   113.73  115.855 113.59  115.69  115.04  223879.0    2020-10-26T14:00:00+0000    AAPL    IEXG
4   113.73  113.83  113.59  113.65  115.04  930.0   2020-10-26T13:00:00+0000    AAPL    IEXG

Answer 2

您也可以在此处使用json_normalize：

df = pd.json_normalize(r, record_path=['data', 'intraday'], meta=[['data', 'name'], ['data', 'symbol']])
print(df)

     open     high     low     last   close     volume                      date symbol exchange  data.name data.symbol
0  113.73  116.550  112.88  113.840  115.04  1293135.0  2020-10-26T18:00:00+0000   AAPL     IEXG  APPLE INC        AAPL
1  113.73  116.550  113.59  113.900  115.04  1027116.0  2020-10-26T17:00:00+0000   AAPL     IEXG  APPLE INC        AAPL
2  113.73  116.550  113.59  114.885  115.04   506229.0  2020-10-26T15:00:00+0000   AAPL     IEXG  APPLE INC        AAPL
3  113.73  115.855  113.59  115.690  115.04   223879.0  2020-10-26T14:00:00+0000   AAPL     IEXG  APPLE INC        AAPL
4  113.73  113.830  113.59  113.650  115.04      930.0  2020-10-26T13:00:00+0000   AAPL     IEXG  APPLE INC        AAPL