Converting a nested JSON into a flatten one and then to pandas dataframe using pd.json

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?

CodePudding user response：

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