我正试图从我的API请求创建一个pandas数据帧
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,Activer...
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,ActivernAACQW,Arti...
... ...
5322 b'L,NYSE ARCA,Stock,2017-10-11,null,Activern...
5323 b'2017-09-22,null,ActivernZWZZT,NASDAQ TEST ...
5324 b'016-01-19,null,ActivernZXZZT,NASDAQ TEST S...
5325 b'l,ActivernZYNE,Zynerba Pharmaceuticals Inc...
5326 b'vernZZK,,NYSE ARCA,Stock,2020-07-22,null,A...
[5327 rows x 1 columns]
当我遍历行时,我得到了这个
b'vernZZK,,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
- 列表理解用于将文本拆分并清理为
lists
的list
,其中索引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
你走在了正确的轨道上,剩下的部分是在utf-8
上解码,并用StringIO包裹它,这样熊猫就可以正确地读取它:
base_url = "https://www.alphavantage.co/query?"
params = {"function": "LISTING_STATUS", "apikey": "S1CBJQPC92YX01S8"}
response = requests.get(base_url, params=params)
# decode, then wrap in StringIO, so Pandas can properly read it
wrapped_data = StringIO(response.content.decode("utf-8"))
df = pd.read_csv(wrapped_data)