How do I convert Coin Market Cap API data from JSON to Pandas Dataframe?

Question

import requests
import pandas as pd
import APIKEY

url = 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest'

parameters = {
    'start': '1',
    'limit': '10',
    'convert': 'USD'
}
headers = {
    'Accepts': 'application/json',
    'X-CMC_PRO_API_KEY': APIKEY.KeyAPI,
}

jsondata = requests.get(url, params=parameters, headers=headers).json()

CoinDF = pd.read_json(jsondata)

CoinDF.to_csv(r'/home/USER/Documents/CoinData.csv')

I am trying to get the Pandas to read the JSON data that the API returns but when I run this program I get this error

Traceback (most recent call last):
  File "/home/crow/PycharmProjects/CoinTracker/CoinMarketCapAPI.py", line 24, in <module>
    CoinDF = pd.read_json(jsondata, orient='index')
  File "/home/crow/PycharmProjects/CoinTracker/venv/lib/python3.8/site-packages/pandas/util/_decorators.py", line 199, in wrapper
    return func(*args, **kwargs)
  File "/home/crow/PycharmProjects/CoinTracker/venv/lib/python3.8/site-packages/pandas/util/_decorators.py", line 299, in wrapper
    return func(*args, **kwargs)
  File "/home/crow/PycharmProjects/CoinTracker/venv/lib/python3.8/site-packages/pandas/io/json/_json.py", line 540, in read_json
    json_reader = JsonReader(
  File "/home/crow/PycharmProjects/CoinTracker/venv/lib/python3.8/site-packages/pandas/io/json/_json.py", line 622, in __init__
    data = self._get_data_from_filepath(filepath_or_buffer)
  File "/home/crow/PycharmProjects/CoinTracker/venv/lib/python3.8/site-packages/pandas/io/json/_json.py", line 659, in _get_data_from_filepath
    self.handles = get_handle(
  File "/home/crow/PycharmProjects/CoinTracker/venv/lib/python3.8/site-packages/pandas/io/common.py", line 558, in get_handle
    ioargs = _get_filepath_or_buffer(
  File "/home/crow/PycharmProjects/CoinTracker/venv/lib/python3.8/site-packages/pandas/io/common.py", line 371, in _get_filepath_or_buffer
    raise ValueError(msg)
ValueError: Invalid file path or buffer object type: <class 'dict'>

The connection to the API is working but for some reason I can't get Pandas to read what it is given.

Thank you for your help

Edit:

So I have done some tinkering with it and I think the hang up is the structure of the JSON file.

It seems to be a list of dictionaries but there is at least one set of nested dictionaries in there. like this...

[string1{blah:blah},string2{blah:blah,blah:blah,Nest{nest1:nest2}}]

. When I just tried reading this from JSON to a dataframe I think it choked on the second level of dictionaries. I suppose the question is now how to make nested dictionary keys their own columns in Pandas

Edit 2: For some reason the JSON file uses single quotes instead of double quotes and I think that is messing everything up. I will leave the question up if someone has a good answer but I am not sure if there is a good solution to this.

Final Edit:

import requests
import pandas as pd
import KeyStuff
import json

url = 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest'

parameters = {
    'start': '1',
    'limit': '10',
    'convert': 'USD'
}
headers = {
    'Accepts': 'application/json',
    'X-CMC_PRO_API_KEY': KeyStuff.APIKey,
}

resp = requests.get(url, params=parameters, headers=headers)
jsondata = json.loads(resp.text)
CoinDF = pd.json_normalize(jsondata['data'])

CoinDF.to_csv("C:\\Users\\USER\\Documents\\Junk\\CMCAPIDF.csv")

Here is the final functional code. Thank you very much to Jonathan Leon!

Answer 1

There's good documentation here: https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyListingsLatest

try something like this (because I'm not exactly seeing what you are, you may have play around a little bit).

resp = requests.get(url, params=parameters, headers=headers)
jsondata = json.loads(resp.text)
CoinDF = pd.json_normalize(jsondata['data'])

A couple thoughts: pesonally, i have never used read_json() from an api response, but that may be just personal preference, but sometimes it just doesn't work. an alternative is pd.DataFrame(your_data), but that doesn't always work to parse correctly. It's my go to though. But for this one I think they wrap this in a list so json_normalize() works great.

Also, when receiving api responses, you should look closely at the structure. You may or may need the status key, but you really want the data key, hence the jsondata['data']

Answer 2

test with telegram bot API, that returns type of json response. it works

import requests
import json
import pandas as pd
rs = requests.get('https://api.telegram.org/bot105574XXXXYYYYSSSXXXXXA/getupdates')

#Actually the data is <class 'dict'>, so enclose with pd.DataFrame.from_dict() works.
df = pd.DataFrame.from_dict(pd.json_normalize(rs.json()), orient='columns')
df.to_csv('api.csv')