Convert nested JSON to pandas DataFrame

Question

I have a nested JSON, where I want to extact part of it and make it into a pandas DataFrame. It just ain't working whatever stackoverflow post I look through!

I tried different ways using already existing posts but can't get it working

JSON from "result"

 b'{"coin":{"id":363,"name":"Bitcoin","code":"BTC"},"dataType":"marketCap","baseCurrency":"USD","data":[{"date":"2018-01-12","marketCap":"232547809668.32000000"},{"date":"2018-01-13","marketCap":"241311607656.32000000"}

CODE:

http = urllib3.PoolManager()
url = 'https://www.cryptocurrencychart.com/api/coin/history/363/2018-01-12/2019-01-12/marketCap/USD'
headers = urllib3.util.make_headers(basic_auth='xxx:xxx')
r = http.request('GET', url , headers = headers) 
result = r.data

df = json_normalize(result['data'])
df.set_index('date', inplace = True)

Result is type: bytes

ERROR:

TypeError: byte indices must be integers or slices, not str

Expected DataFrame

            BTCmarketCap
2019-01-01  xxxxxxx 
2019-01-02  xxxxxx

cs95 · Accepted Answer · 2019-01-15 21:21:53Z

5

To unpack the dictionary, use json_normalize with a record_path=... argument.

import pandas.io.json as pd_json

data = pd_json.loads(result)
pd_json.json_normalize(data, record_path='data')

         date              marketCap
0  2018-01-12  232547809668.32000000
1  2018-01-13  241311607656.32000000

If you want the other values as well, pass a meta=.... argument:

df = pd_json.json_normalize(data, 
                            record_path='data', 
                            meta=['coin', 'dataType', 'baseCurrency'])
df

         date              marketCap     ...        dataType baseCurrency
0  2018-01-12  232547809668.32000000     ...       marketCap          USD
1  2018-01-13  241311607656.32000000     ...       marketCap          USD

 df.columns
# Index(['date', 'marketCap', 'coin', 'dataType', 'baseCurrency'], dtype='object')

edited Jan 15, 2019 at 21:21

answered Jan 15, 2019 at 21:04

cs95

406k106 gold badges744 silver badges797 bronze badges

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

4 Comments

Alexander Thomsen Over a year ago

Thanks for it! hmm I'm getting this error with above: AttributeError: 'HTTPResponse' object has no attribute 'json'

cs95 Over a year ago

@AlexanderThomsen Sorry, assumed it was requests, did not see that clearly. Made edits, check now?

Alexander Thomsen Over a year ago

Thank god! so to get it straight, you load the JSON and decode it (whatever that do) while making it into a dataframe and then normalize it and chooses only 'data' path.

cs95 Over a year ago

@AlexanderThomsen It is a byte string. Actually, decoding is not necessary, because loads will do it anyway. Just pass the string, have it converted into a dictionary, and then use json_normalize to decode it by picking the appropriate record path to extract.

Collectives™ on Stack Overflow

Convert nested JSON to pandas DataFrame

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related