如何将alphavantage api响应提取到 pandas 数据框中? [英] How to extract the alphavantage api response into a pandas dataframe?

问题描述

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

I am trying to create a pandas dataframe from my API request,

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)

但请继续努力，这是我的最佳尝试

but keep getting this as my best attempt

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]

当我遍历行时​​，我得到了

When I iterate trough the rows I get this

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字典?

Would love to get a link to some good documentation on how to do this. I have been looking around, and I don't understand since every row is still a json object? I think I am supposed to make it into a python dictionary somehow?

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

Any help or guidance would be much appreciated.

推荐答案

• json.loads(response.content.decode(response.encoding))导致TypeError
• response.text用于将文本提取到data.
• 列表理解用于拆分，并将文本清理为lists的list，以索引0作为标题.
• pandas.DataFrame构造函数用于从data创建数据框.
• json.loads(response.content.decode(response.encoding)) results in a TypeError
• response.text was used to extract the text, into data.
• A list-comprehension is used to split, and clean the text into a list of lists, with index 0 as the header.
• The pandas.DataFrame constructor is used to create the dataframe, from 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

这篇关于如何将alphavantage api响应提取到 pandas 数据框中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！