python - 如何将 alphavantage api 响应提取到 pandas 数据框中
问题描述
我正在尝试根据我的 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
.- 列表理解用于将文本拆分并清理为 a
list
oflists
,索引 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
推荐阅读
- sql-server - 与 Oracle 链接的 SQL Server
- python - windows - PermissionError: [Errno 13] 访问 Apache 日志文件时权限被拒绝
- sql-server - 删除存储过程然后重新创建而不是仅仅改变它们有什么好处吗
- c# - 如何一次重构开关、返回值和调用异步方法
- vue.js - Vuejs中的条件a href
- linux - 为 bash 中的参数设置特定选项集
- rinsim - RinSim 本身是否支持 PDP Roadmodel 上的不可见、非碰撞代理?
- python - 如何使用标记化列表作为查询的基础在列表中搜索字符串?
- git - 存储在 Dropbox 上的定期损坏的 Git 存储库
- php - 匹配 MySQL JSON 列的全部内容