Extract nested JSON data with Python pandas

Question

As stated trying to extract data from a nested JSON API response (see below).

res = b'{"Response":"Success","Message":"","HasWarning":false,"Type":100,"RateLimit":{},"Data":{"Aggregated":false,"TimeFrom":1629936000,"TimeTo":1630800000,"Data":[{"time":1629936000,"high":49358.2,"low":46456.68,"open":48996.44,"volumefrom":34276.36,"volumeto":1618461768.41,"close":46852.22,"conversionType":"direct","conversionSymbol":""},{"time":1630022400,"high":49166.31,"low":46376.81,"open":46852.22,"volumefrom":27872.76,"volumeto":1331637984.84,"close":49088.1,"conversionType":"direct","conversionSymbol":""}]}}'

My code

df = pd.json_normalize(res, record_path =['Data'], meta = ['Data']['time', 'high', 'low', 'close'] )

Error:

TypeError: list indices must be integers or slices, not tuple

Also tried different ways like

df = pd.json_normalize(res, record_path =['Data'][3], meta = ['time', 'high', 'low', 'close'] )

I'm trying to extract the data (time, high, low, close) and make it into a data frame + convert time to a readable time e.g "2021-09-05"

Expected output (example)

            close. high. low
2021-09-04.  48996 49000 48000
2021-09-05.  49600 49800 49500

Corralien · Accepted Answer · 2021-09-05 12:54:47Z

1

Convert your json string to a python data structure using json.loads and then use json_normalize:

import json

cols = ['time', 'close', 'high', 'low']
data = json.loads(res)
df = pd.json_normalize(data['Data'], record_path='Data')[cols]
df = df.astype({'time': 'datetime64[s]'}).set_index('time')

Output:

>>> df
               close      high       low
time
2021-08-26  46852.22  49358.20  46456.68
2021-08-27  49088.10  49166.31  46376.81

edited Sep 5, 2021 at 12:54

answered Sep 5, 2021 at 12:49

Corralien

121k8 gold badges43 silver badges68 bronze badges

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

Comments

NYC Coder · Accepted Answer · 2021-09-05 12:45:32Z

0

You should use json_normalize like this:

df = pd.json_normalize(data, record_path=['Data', 'Data'])
df = df[['time', 'close', 'low', 'high']]
print(df)

         time     close       low      high
0  1629936000  46852.22  46456.68  49358.20
1  1630022400  49088.10  46376.81  49166.31

answered Sep 5, 2021 at 12:45

NYC Coder

7,6443 gold badges14 silver badges25 bronze badges

1 Comment

Alexander Thomsen Over a year ago

Thanks for the reply, this throws similar error as I had

Collectives™ on Stack Overflow

Extract nested JSON data with Python pandas

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related