How To Extract The Alphavantage Api Response Into A Pandas Dataframe

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/

Solution 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

Solution 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)