How to normalize nested json using pandas-CodePudding

I want to normalize the "values" array

{
  "data": [
    {
      "name": "page_call",
      "period": "lifetime",
      "values": [
        {
          "value": {
            "about": 11,
            "videos": 2,
            "posts": 15,
            "home": 21
          },
          "end_time": "2020-11-02T08:00:00 0000"
        }
      ],
      "title": null,
      "description": "Number of people who logged in.",
      "id": "1545/lifetime"
    }
  ]
}

I have tried df_results = pd.json_normalize(data, record_path=['data']) , the columns I'm getting are ['name', 'period', 'values', 'title', 'description', 'id'] but the "values" is not getting normalized, can anyone help me out on this

CodePudding user response：

Use:

df_results = pd.json_normalize(data['data'], 'values', ['name','period','title','description','id'])
print (df_results)
                   end_time  value.about  value.videos  value.posts  \
0  2020-11-02T08:00:00 0000           11             2           15   

   value.home       name    period title                      description  \
0          21  page_call  lifetime  null  Number of people who logged in.   

              id  
0  1545/lifetime

CodePudding user response：

Use:

df_results = pd.json_normalize(data, ["data"])
vals = pd.json_normalize(data, ["data", 'values'])
res = pd.concat([df_results, vals], axis = 1)
del res['values']
res

Output: