How to extract the alphavantage api response into a pandas dataframe

Question

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

The goal is to get something like this

  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

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.

Answer 1

• 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

Answer 2

You are on the right track, the rest part is to decode on a utf-8, and wrap it with StringIO, so that pandas can properly read it :

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)