How to convert a nested JSON object to a dataframe?

Question

I am getting a JSON object returned from an API call which looks like this:

{"meta":{"symbol":"AAPL","interval":"1min","currency":"USD","exchange_timezone":"America/New_York","exchange":"NASDAQ","mic_code":"XNGS","type":"Common Stock"},"values":[{"datetime":"2022-06-06 15:59:00","open":"146.14999","high":"146.47000","low":"146.09000","close":"146.14000","volume":"1364826"},{"datetime":"2022-06-06 15:58:00","open":"146.14000","high":"146.17999","low":"146.08000","close":"146.14680","volume":"358111"},{"datetime":"2022-06-06 15:57:00","open":"146.30499","high":"146.33000","low":"146.13000","close":"146.14000","volume":"0"},{"datetime":"2022-06-06 15:56:00","open":"146.25999","high":"146.34500","low":"146.20000","close":"146.31000","volume":"306725"},{"datetime":"2022-06-06 15:55:00","open":"146.14999","high":"146.38000","low":"146.07001","close":"146.25999","volume":"384471"},{"datetime":"2022-06-06 15:54:00","open":"145.95000","high":"146.25999","low":"145.91000","close":"146.15500","volume":"287583"},{"datetime":"2022-06-06 15:53:00","open":"145.97000","high":"146.10001","low":"145.89760","close":"145.94569","volume":"231640"},{"datetime":"2022-06-06 15:52:00","open":"145.96500","high":"146.00000","low":"145.78999","close":"145.96500","volume":"189185"},{"datetime":"2022-06-06 15:51:00","open":"145.89000","high":"146.00000","low":"145.74001","close":"145.96001","volume":"182617"},{"datetime":"2022-06-06 15:50:00","open":"145.74001","high":"146.11290","low":"145.74001","close":"145.89500","volume":"376980"},{"datetime":"2022-06-06 15:49:00","open":"145.63499","high":"145.85001","low":"145.63000","close":"145.73000","volume":"190471"},{"datetime":"2022-06-06 15:48:00","open":"145.61000","high":"145.71001","low":"145.58000","close":"145.65131","volume":"138908"},{"datetime":"2022-06-06 15:47:00","open":"145.64999","high":"145.65500","low":"145.53999","close":"145.61011","volume":"166144"},{"datetime":"2022-06-06 15:46:00","open":"145.81500","high":"145.82500","low":"145.62061","close":"145.66000","volume":"175801"},{"datetime":"2022-06-06 15:45:00","open":"145.88989","high":"145.98000","low":"145.80780","close":"145.81880","volume":"161626"},{"datetime":"2022-06-06 15:44:00","open":"145.80000","high":"145.89000","low":"145.77000","close":"145.89000","volume":"89067"},{"datetime":"2022-06-06 15:43:00","open":"145.95000","high":"145.97000","low":"145.78500","close":"145.80000","volume":"180386"},{"datetime":"2022-06-06 15:42:00","open":"145.84000","high":"146.09000","low":"145.82001","close":"145.96989","volume":"377760"},{"datetime":"2022-06-06 15:41:00","open":"145.59000","high":"145.86000","low":"145.59000","close":"145.83730","volume":"283091"},{"datetime":"2022-06-06 15:40:00","open":"145.46001","high":"145.60001","low":"145.36000","close":"145.58501","volume":"159567"},{"datetime":"2022-06-06 15:39:00","open":"145.50999","high":"145.56850","low":"145.45000","close":"145.47009","volume":"113975"},{"datetime":"2022-06-06 15:38:00","open":"145.30000","high":"145.50880","low":"145.24010","close":"145.50500","volume":"174004"},{"datetime":"2022-06-06 15:37:00","open":"145.44000","high":"145.44000","low":"145.27000","close":"145.30000","volume":"189831"},{"datetime":"2022-06-06 15:36:00","open":"145.54890","high":"145.54890","low":"145.38000","close":"145.44000","volume":"101993"},{"datetime":"2022-06-06 15:35:00","open":"145.53000","high":"145.56000","low":"145.41000","close":"145.54500","volume":"114006"},{"datetime":"2022-06-06 15:34:00","open":"145.58501","high":"145.60789","low":"145.50999","close":"145.52010","volume":"108473"},{"datetime":"2022-06-06 15:33:00","open":"145.53999","high":"145.60500","low":"145.47000","close":"145.58501","volume":"133996"},{"datetime":"2022-06-06 15:32:00","open":"145.56500","high":"145.64000","low":"145.46030","close":"145.53999","volume":"131019"},{"datetime":"2022-06-06 15:31:00","open":"145.34500","high":"145.60001","low":"145.34000","close":"145.58800","volume":"238105"},{"datetime":"2022-06-06 15:30:00","open":"145.34500","high":"145.35001","low":"145.27000","close":"145.34000","volume":"136026"}],"status":"ok"}

I am interested in the "values" section, the datetime,h,l,o,c,v values and I want to import them into a dataframe.

My code is:

resp = requests.get(url)

which generates the above response. Then:

df = pd.DataFrame(resp)

which provides this:

0  b'{"meta":{"symbol":"AAPL","interval":"1day","...
1  b'":"XNGS","type":"Common Stock"},"values":[{"...
2  b'e":"146.14000","volume":"65217850"},{"dateti...
3  b'5.39000","volume":"88471302"},{"datetime":"2...
4  b'1","volume":"72348100"},{"datetime":"2022-06...

How can I skip the meta section and populate the dataframe only with the values that I need?

I have tried:

df = pd.DataFrame(resp.meta.values)

and

df = pd.DataFrame(resp['meta']['values'])

which return errors: no attribute meta and not subscriptable respectively.

Does this answer your question? stackoverflow.com/questions/21104592/json-to-pandas-dataframe — medium-dimensional
– medium-dimensional, Commented Jun 7, 2022 at 8:17

Tranbi · Accepted Answer · 2022-06-07 10:47:11Z

1

Edit to fit actual solution:

You should be able to load your API response with:

data = resp.json()
pd.DataFrame(data['values'])

edited Jun 7, 2022 at 10:47

answered Jun 7, 2022 at 8:23

Tranbi

12.8k6 gold badges19 silver badges39 bronze badges

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

5 Comments

user1903663 Over a year ago

Thanks I have tried this (and various mutation). I get key value error of "values" and resp_str = bytes(resp).decode('utf-8') TypeError: 'bytes' object cannot be interpreted as an integer

Tranbi Over a year ago

have you tried data = resp.json()? This should load your json in a dictionary directly. Then df = pd.DataFrame(data['meta']['values'])

user1903663 Over a year ago

Yes, I tried exactly that. I can print out the json object, but it complains when I try to put it into a df

Tranbi Over a year ago

Can you print data['meta']['values']? please add it to the question if it causes problems.

user1903663 Over a year ago

solved: resp = requests.get(url) data = resp.json() #print(data['values']) df = pd.DataFrame(data['values'])

Collectives™ on Stack Overflow

How to convert a nested JSON object to a dataframe?

1 Answer 1

Edit to fit actual solution:

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit to fit actual solution:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related