Converting a nested JSON into a flatten one and then to pandas dataframe using pd.json_normalize()

Question

I am trying to convert the below JSON as a dataframe. The below JSON is in a string.

 json_str='{"TileName": "Master",
  "Report Details": 
     [
       {
        "r1name": "Primary",
        "r1link": "link1",
        "report Accessible": ["operations", "Sales"]
       },
       {
       "r2name": "Secondry",
       "r2link": "link2",
       "report Accessible": ["operations", "Sales"]
       }
     ]
  }'

So I am trying to get below df

TileName      ReportAccssible     ReportName     ReportLink
Master        operations           Primary        link1
Master        Sales                Primary        link1
Master        operations           Secondary      link2
Master        Sales                Secondary      link2

In order to achieve the above, I am trying the below code snippet:

js_str = json.loads(json_str)
df = pd.json_normalize(js_df,'Report Details',['TileName',['report Accessible']],\
record_prefix='Col_',errors='ignore')

But the above code is not giving me the output as per desired format.

What I am missing here?

Is there wrong in the column ReportLink? I guess you want 'link1' and 'link2' instead of 'r1link' and 'r2link'. — ILS
– ILS, Commented Nov 29, 2022 at 9:55

ILS · Accepted Answer · 2022-11-29 10:00:03Z

1

You have wrong record_path, which should be ['Report Details', 'report Accessible'].

js_obj = json.loads(json_str.replace('r2', 'r1'))  # keep columns consistent
df = pd.json_normalize(js_obj, ['Report Details', 'report Accessible'], 
                       ['TileName', ['Report Details', 'r1name'], ['Report Details', 'r1link']])
df.columns = ['ReportAccssible', 'TileName', 'ReportName', 'ReportLink']

You will get what you want.

  ReportAccssible TileName ReportName ReportLink
0      operations   Master    Primary      link1
1           Sales   Master    Primary      link1
2      operations   Master   Secondry      link2
3           Sales   Master   Secondry      link2

answered Nov 29, 2022 at 10:00

ILS

1,3901 gold badge9 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Converting a nested JSON into a flatten one and then to pandas dataframe using pd.json_normalize()

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