converting pandas dataframe to a custom JSON

September 17, 2022

This is my dataframe:

df = pd.DataFrame(
    {
        'a': ['x', 'x', 'y', 'y'],
        'b': ['xs', 'sx', 'rrx', 'ywer'],
        'c': ['aaa', 'bbb', 'rrsdrx', 'yz'],
    }
)

And this is the JSON output that I want:

{
    'x':{
        'links':[
            {
                'b': 'xs',
                'c': 'aaa'
            },
            {
                'b': 'sx',
                'c': 'bbb'
            }
        ]
    },

    'y':{
        'links':[
            {
                'b': 'rrx',
                'c': 'rrsdrx'
            },
            {
                'b': 'ywer',
                'c': 'yz'
            }
        ]
    },
}

I have tried the accepted answer of this post. And the following code was my other try:

x = df.groupby('a')['b'].apply(list).reset_index()
y = x.to_json(orient='records')
parsed = json.loads(y)
z = json.dumps(parsed, indent=4)

but the output was not what I needed.

>Solution :

Group the dataframe by a, then create dictionary for each dataframe for the keys, and create the required dictionary.

{k:{'links': d.drop(columns=['a']).to_dict('records')} for k,d in df.groupby('a')}

OUTPUT

{
  "x": {
    "links": [
      {
        "b": "xs",
        "c": "aaa"
      },
      {
        "b": "sx",
        "c": "bbb"
      }
    ]
  },
  "y": {
    "links": [
      {
        "b": "rrx",
        "c": "rrsdrx"
      },
      {
        "b": "ywer",
        "c": "yz"
      }
    ]
  }
}