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.
json.loads(response.content.decode(response.encoding)) results in a TypeErrorresponse.text was used to extract the text, into data.list of lists, with index 0 as the header.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