Convert a MultiIndex pandas DataFrame to a nested JSON

Question

I have the following Dataframe with MultiIndex rows in pandas.

               time    available_slots       status
month day                                          
1     1    10:00:00                  1    AVAILABLE
      1    12:00:00                  1    AVAILABLE
      1    14:00:00                  1    AVAILABLE
      1    16:00:00                  1    AVAILABLE
      1    18:00:00                  1    AVAILABLE
      2    10:00:00                  1    AVAILABLE
...             ...                ...          ...
2     28   12:00:00                  1    AVAILABLE
      28   14:00:00                  1    AVAILABLE
      28   16:00:00                  1    AVAILABLE
      28   18:00:00                  1    AVAILABLE
      28   20:00:00                  1    AVAILABLE

And I need to transform it to a hierarchical nested JSON as this:

[
    {
        "month": 1,
        "days": [
            {
                "day": 1,
                "slots": [
                    {
                        "time": "10:00:00",
                        "available_slots": 1,
                        "status": "AVAILABLE"
                    },
                    {
                        "time": "12:00:00",
                        "available_slots": 1,
                        "status": "AVAILABLE"
                    },
                    ...
                ]
            },
            {
                "day": 2,
                "slots": [
                    ...
                ]
            }
        ]
    },
    {
        "month": 2,
        "days":[
            {
                "day": 1,
                "slots": [
                    ...
                ]
            }
        ]
    },
    ...
]

Unfortunately, it is not as easy as doing df.to_json(orient="index").

Does anyone know if there is a method in pandas to perform this kind of transformations? or in what way I could iterate over the DataFrame to build the final object?

score 2 · Accepted Answer · 2022-02-27 04:26:11Z

Here's one way. Basically repeated groupby + apply(to_dict) + reset_index until we get the desired shape:

out = (df.groupby(level=[0,1])
       .apply(lambda x: x.to_dict('records'))
       .reset_index()
       .rename(columns={0:'slots'})
       .groupby('month')
       .apply(lambda x: x[['day','slots']].to_dict('records'))
       .reset_index()
       .rename(columns={0:'days'})
       .to_json(orient='records', indent=True)
      )

Output:

[
 {
  "month":1,
  "days":[
   {
    "day":1,
    "slots":[
     {
      "time":"10:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     },
     {
      "time":"12:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     },
     {
      "time":"14:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     },
     {
      "time":"16:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     },
     {
      "time":"18:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     }
    ]
   },
   {
    "day":2,
    "slots":[
     {
      "time":"10:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     }
    ]
   }
  ]
 },
 {
  "month":2,
  "days":[
   {
    "day":28,
    "slots":[
     {
      "time":"12:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     },
     {
      "time":"14:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     },
     {
      "time":"16:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     },
     {
      "time":"18:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     },
     {
      "time":"20:00:00",
      "available_slots":1,
      "status":"AVAILABLE"
     }
    ]
   }
  ]
 }
]

Corralien · Accepted Answer · 2022-02-27 07:52:45Z

1

You can use a double loop for each level of your index:

data = []
for month, df1 in df.groupby(level=0):
    data.append({'month': month, 'days': []})
    for day, df2 in df1.groupby(level=1):
        data[-1]['days'].append({'day': day, 'slots': df2.to_dict('records')})

Output:

import json
print(json.dumps(data, indent=2))

[
  {
    "month": 1,
    "days": [
      {
        "day": 1,
        "slots": [
          {
            "time": "10:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          },
          {
            "time": "12:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          },
          {
            "time": "14:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          },
          {
            "time": "16:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          },
          {
            "time": "18:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          }
        ]
      },
      {
        "day": 2,
        "slots": [
          {
            "time": "10:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          }
        ]
      }
    ]
  },
  {
    "month": 2,
    "days": [
      {
        "day": 28,
        "slots": [
          {
            "time": "12:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          },
          {
            "time": "14:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          },
          {
            "time": "18:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          },
          {
            "time": "20:00:00",
            "available_slots": 1,
            "status": "AVAILABLE"
          }
        ]
      }
    ]
  }
]

edited Feb 27, 2022 at 7:52

answered Feb 27, 2022 at 0:20

Corralien

121k8 gold badges44 silver badges69 bronze badges

3 Comments

user7864386 Over a year ago

I knew there must be a better way :-)

Dani Over a year ago

But with this approach I get a list of days with a length of 1. The remaining days are missing from the list. I.e, in month 1 I should have a list of days, starting from 1 to 31 (each of them with their slots), and the same for month 2 (up to 28 in this case because it is Febraury) and so on

Corralien Over a year ago

Sorry I made a mistake. It was level=1 for days and not level=0. Fixed.

Collectives™ on Stack Overflow

Convert a MultiIndex pandas DataFrame to a nested JSON

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related