CODEWITHSUNDEEP
pythonjsonpandas
Troy112
Troy112

Reputation: 23

Parsing JSON data from an API to Pandas

I am trying to get data from an api ( https://min-api.cryptocompare.com/data/histoday?fsym=BTC&tsym=ETH&limit=30&aggregate=1&e=CCCAGG ) to pandas. API gives data in Json.

df = pd.read_json('new.json' , orient = 'columns')

Error: Mixing dicts with non-Series may lead to ambiguous ordering.

Data i need:- Image Link : https://i.sstatic.net/zsCA5.jpg

I am really new to this, any help would be fantastic.

Upvotes: 2

Views: 9017

Answers (1)

cs95
cs95

Reputation: 402613

I believe the issue is that you're passing the entirety of your JSON to the read_json function, when you should only be passing the data stored in the Data attribute.

If you're downloading your data programmatically, I would recommend requests:

In [422]: import requests

In [416]: data = requests.get('https://min-api.cryptocompare.com/data/histoday?fsym=BTC&tsym=ETH&limit=30&aggregate=1&e=CCCAGG')\
                         .json()['Data']

data is now a dictionary, not a JSON string. You can call the pd.DataFrame.from_dict function to parse the data, like this:

In [420]: df = pd.DataFrame.from_dict(data)

In [421]: df.head()
Out[421]: 
   close   high    low   open        time  volumefrom   volumeto
0  12.29  11.55  12.73  12.54  1500595200    72064.93  875383.96
1  12.22  11.93  12.61  12.29  1500681600    39624.40  489345.17
2  12.03  11.94  12.37  12.22  1500768000    37270.80  452462.43
3  12.22  11.99  12.36  12.03  1500854400    28582.22  347606.39
4  12.59  12.22  13.03  12.22  1500940800    54004.34  676716.41

If you insist on using pd.from_json, then you must only pass the string data that is contained inside the Data attribute of the JSON response, otherwise it will not work.

Upvotes: 9

Related Questions