如何将alphavantage api响应提取到熊猫数据框中？

debugcn 发表于 Dev

雅库布

我正在尝试根据我的API请求创建一个熊猫数据框，

import pandas as pd
from pandas import DataFrame
import json
import requests

base_url = 'https://www.alphavantage.co/query?'
params = {'function': 'LISTING_STATUS',
        'apikey': '**********'}
response = requests.get(base_url, params=params)

# I Saw this on stack overflow but getting this error
   # TypeError: decode() argument 1 must be str, not None
data = json.loads(response.content.decode(response.encoding))
df = pd.DataFrame([data])

# This attempt prints out the solution below
df = pd.DataFrame.from_dict(response)

但请尽量将其作为我的最佳尝试

0     b'symbol,name,exchange,assetType,ipoDate,delis...
1     b'coa Inc,NYSE,Stock,2016-11-01,null,Active\r\...
2     b'Mint Physical Gold,NYSE ARCA,ETF,2018-08-15,...
3     b'on Inc - Class A,NASDAQ,Stock,2020-09-04,nul...
4     b'Q,Stock,2020-07-14,null,Active\r\nAACQW,Arti...
...                                                 ...
5322  b'L,NYSE ARCA,Stock,2017-10-11,null,Active\r\n...
5323  b'2017-09-22,null,Active\r\nZWZZT,NASDAQ TEST ...
5324  b'016-01-19,null,Active\r\nZXZZT,NASDAQ TEST S...
5325  b'l,Active\r\nZYNE,Zynerba Pharmaceuticals Inc...
5326  b've\r\nZZK,,NYSE ARCA,Stock,2020-07-22,null,A...
 
[5327 rows x 1 columns]

当我遍历行时我得到这个

b've\r\nZZK,,NYSE ARCA,Stock,2020-07-22,null,A...Name: 5326, dtype: object

目标是得到这样的东西

  symbol                  name exchange     ipoDate  delistingDate       status
0   AAPL             Apple Inc     test  12/12/1980            NaN   test222222
1   MSFT        Microsoft Corp     test   3/13/1986            NaN  test_status
2     FB          Facebook Inc     test   5/18/2012            NaN  test_status
3   TSLA             Tesla Inc   NASDAQ   6/29/2010            NaN  test_status
4   GOOG  Alphabet Inc Class C   NASDAQ   3/27/2014            NaN  test_status

很想获得一些有关如何执行此操作的良好文档的链接。我一直在环顾四周，但我不明白，因为每一行仍然是一个json对象？我想我应该以某种方式使其成为python字典？

任何帮助或指导将不胜感激。

特伦顿·麦金尼

json.loads(response.content.decode(response.encoding)) 导致 TypeError
response.text用于将文本提取到中data。
列表理解用于拆分文本，并将其清理为listof lists，其中索引0为标题。
该pandas.DataFrame构造函数用于创建数据框，从data。

import request
import pandas as pd

# get data from api
base_url = 'https://www.alphavantage.co/query?'
params = {'function': 'LISTING_STATUS', 'apikey': '**********'}
response = requests.get(base_url, params=params)

# convert text data in to a list of of list
data = [row.strip().split(',') for row in response.text.split('\n')]

# load data into a dataframe
df = pd.DataFrame(data[1:-1], columns=data[0])

# display(df)
  symbol                                                            name   exchange assetType     ipoDate delistingDate  status
0      A                                        Agilent Technologies Inc       NYSE     Stock  1999-11-18          null  Active
1     AA                                                       Alcoa Inc       NYSE     Stock  2016-11-01          null  Active
2    AAA                                 AAF First Priority CLO Bond ETF  NYSE ARCA       ETF  2020-09-09          null  Active
3   AAAU                                        Perth Mint Physical Gold  NYSE ARCA       ETF  2018-08-15          null  Active
4   AACG                                                         ATA Inc     NASDAQ     Stock  2008-01-29          null  Active
5   AACQ                                Artius Acquisition Inc - Class A     NASDAQ     Stock  2020-09-04          null  Active
6  AACQU  Artius Acquisition Inc - Units (1 Ord Share Class A & 1/3 War)     NASDAQ     Stock  2020-07-14          null  Active
7  AACQW                  Artius Acquisition Inc - Warrants (13/07/2025)     NASDAQ     Stock  2020-09-04          null  Active
8   AADR                             ADVISORSHARES DORSEY WRIGHT ADR ETF  NYSE ARCA       ETF  2010-07-21          null  Active
9    AAL                                     American Airlines Group Inc     NASDAQ     Stock  2005-09-27          null  Active

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。