I have a Pandas DataFrame which I need to transform into a JSON object. I thought by grouping it, I would achieve this but this does not seem to yield the correct results. Further, I wouldnt know how to name the sub group.

My data frame as follows:

And I do a groupby as I would like all children together under one parent in json:

df = df.groupby(['parent', 'name'])['age'].min()

And I would like it to yield the following:

   {
      "parent": "Nick",
      "children": [
        {
          "name": "Rob",
          "age": 10,
        },
        {
          "name": "Stef",
          "age": 15,
        },,..  ]
    }

When I do .to_json() it seems to regroup everything on age etc.

CodePudding user response：

df.groupby(['parent'])[['name', 'age']].apply(list).to_json()

CodePudding user response：

Given I wanted to add some styling, I ended up solving it as follows:

import json

df_grouped = df.groupby('parent')

new = []

for group_name, df_group in df_grouped:
    
    base = {}
    base['parent'] = group_name
    children = []

    for row_index, row in df_group.iterrows():
        temp = {}
        temp['name'] = row['name']
        temp['age'] = row['age']
        children.append(temp)
    
    base['children'] = children
    new.append(base)
    
json_format = json.dumps(new)

print(new)

Which yielded the following results:

[
   {
      "parent":"fee",
      "children":[
         {
            "name":"bob",
            "age":9
         },
         {
            "name":"stef",
            "age":10
         }
      ]
   },
   {
      "parent":"nick",
      "children":[
         {
            "name":"stef",
            "age":10
         },
         {
            "name":"tobi",
            "age":2
         },
         {
            "name":"ralf",
            "age":12
         }
      ]
   },
   {
      "parent":"patrick",
      "children":[
         {
            "name":"marion",
            "age":10
         }
      ]
   }
]