How to parse nested Json using python and pandas?

Question

I am trying to parse response I get from API. here is the format I have:

 jsn= " {'headers': ['deviceCategory', 'eventName', 'eventCount', 'eventValue'],
 'rows': [['desktop', 'page_v', '25213805', '0'],
  ['desktop', 'session_s', '32556', '0'],
  ['desktop', 'first_vis', '4324', '0'],
  ['desktop', 'user_eng', '2340', '0'],
  ['desktop', 'scroll', '3143', '0'],
  ['desktop', 'click', '3131', '0'],
  ['desktop', 'purchase', '2781', '581141.86'],
  ['desktop', 'file_download', '7475', '0'],
  ['desktop', 'view_search_results', '478', '0'],
  ['desktop', 'view_prom', '21', '0'],
  ['desktop', 'view_item', '1', '2.35']]}"

I have tried using:

import pandas as pd
df = pd.json_normalize(jsn)

I also tried using json_normalize from json library and I am not able to store this correctly into dataframe. As result i get column Header and Column row with just one line that contains all headers and all rows in each cell

How do i extract this data so i have 4 Columns as shown in headers and rows data populated below?

can you check if the string you shared is a valid json ?

srinath
– srinath

2022-07-28 18:25:54 +00:00
Commented Jul 28, 2022 at 18:25 — srinath
– srinath, Commented Jul 28, 2022 at 18:25

SomeDude · Accepted Answer · 2022-07-28 18:36:31Z

1

Assuming you have right json, the below should do:

import json
d = json.loads(jsn)
df = pd.DataFrame(columns = d['headers'], data = d['rows'])

answered Jul 28, 2022 at 18:36

SomeDude

14.3k5 gold badges26 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

How to parse nested Json using python and pandas?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related