Parsing JSON data from an API to Pandas

Question

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.

Are you downloading correctly yours data? What is 'new.json'? — Emmanuel Arias
– Emmanuel Arias, Commented Aug 20, 2017 at 23:21

cs95 · Accepted Answer · 2017-08-20 23:29:55Z

9

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.

answered Aug 20, 2017 at 23:29

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Troy112 Over a year ago

Thanks alot, it works. Just wanted to know how can i delete Column 1 (0,1,2,3,4..) and sort it out with time? Also, is there any way to edit Json to get just the data in Data column, so that i can use 'pd.from_json'

Collectives™ on Stack Overflow

Parsing JSON data from an API to Pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related