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.
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.