CODEWITHSUNDEEP
pythonjsonpandas
Denis
Denis

Reputation: 12077

Can't seem to figure out why I can't read JSON into DataFrame

How would I convert the "Data" section of this JSON to a simple pandas table. Here's what I have:

import json
import urllib.request
import pandas

url = urllib.request.urlopen('https://www.cryptocompare.com/api/data/coinlist/')
json_obj = json.load(url)
print(json.dumps(json_obj, sort_keys=True, indent=4))

json_data = json_obj['Data']
print(json.dumps(json_data, sort_keys=True, indent=4))

df = pandas.DataFrame(json_data)
with pandas.option_context('display.max_rows', None, 'display.max_columns', 3):
    print(df)

Tried a bunch of ways but the result doesn't look correct. The table should be:

CoinName,Algorithm,...
ABC,          AAA, ...
DEF,          DDS, ...

Upvotes: 0

Views: 372

Answers (3)

piRSquared
piRSquared

Reputation: 294338

Looks good to me

pandas.DataFrame(json_obj['Data']).T

          Algorithm          CoinName                 FullName FullyPremined  
007          Scrypt          007 coin           007 coin (007)             0   
1337            X13              1337              1337 (1337)             0   
1CR          Scrypt           1Credit            1Credit (1CR)             0   
1ST             N/A        FirstBlood         FirstBlood (1ST)             1   
2015            X11         2015 coin         2015 coin (2015)             0   
2BACCO       Scrypt       2BACCO Coin     2BACCO Coin (2BACCO)             0   
2GIVE        Scrypt         2GiveCoin        2GiveCoin (2GIVE)             0   
32BIT           X11         32Bitcoin        32Bitcoin (32BIT)             0   
365             X11           365Coin            365Coin (365)             0   
404          Scrypt           404Coin            404Coin (404)             0   
42           Scrypt           42 Coin             42 Coin (42)             0   
611          SHA256         SixEleven          SixEleven (611)             0   
808          SHA256               808                808 (808)             0   
888             N/A          Octocoin           Octocoin (888)             0

Upvotes: 2

chthonicdaemon
chthonicdaemon

Reputation: 19770

Once you have read the JSON, you now actually have a Python dict in json_data. Therefore, you need pandas.DataFrame.from_dict:

df = pandas.DataFrame.from_dict(json_obj['Data'], orient='index')

Upvotes: 0

Aaron Paul
Aaron Paul

Reputation: 117

Not a full answer, but I don’t have the reputation to leave a comment.

urllib.request.urlopen is returning an object but json.load is expecting a string.

If you are looking for a string you must be using the wrong method. https://docs.python.org/3.4/library/urllib.request.html?highlight=urllib.request.urlopen#urllib.request.urlopen

>>> json_obj = json.load(url)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File  "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/json/__init__.py", line 268, in load
parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)
File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/json/__init__.py", line 312, in loads
s.__class__.__name__))
TypeError: the JSON object must be str, not 'bytes'    
>>> url.info()
<http.client.HTTPMessage object at 0x105ab3518>    
>>> url.getcode()
200

Upvotes: 0

Related Questions