Pandas grouping by multiple columns to get a multi nested Json

Question

I have a dataframe that looks as follow:

Lvl1  lvl2  lvl3  lvl4  lvl5
x     1x    3xx   1     "text1"
x     1x    3xx   2     "text2"
x     1x    3xx   3     "text3"
x     1x    4xx   4     "text4"
x     2x    4xx   5     "text5"
x     2x    4xx   6     "text6"
y     2x    5xx   7     "text7"
y     3x    5xx   8     "text8"
y     3x    5xx   9     "text9"
y     3x    6xx   10    "text10"
y     4x    7xx   11    "text11"
y     4x    7xx   62    "text12"
y     4x    8xx   62    "text13"
z
z
z
w
w
w

I would like to convert to nested json so it looks like this:

[{
  "x":{
         "1x":[{
                "3xx": [
                {
                lvl4: 1
                lvl5: "text1"
                },
                {
                lvl4: 2
                lvl5: "text2"
                },
                {
                lvl4: 3
                lvl5: "text3"
                }],
                "4xx": [
                {
                lvl4: 4
                lvl5: "text4"
                }],
         "2x":[{
                "4xx": [
                {
                lvl4: 5
                lvl5: "text5"
                },
                {
                lvl4: 6
                lvl5: "text6"
                }],
                "5xx": [
                {
                lvl4: 7
                lvl5: "text7"   
                }],
                }]

. . .

I am using the example here as a start, but I need the lvl1, lvl2, lvl3 indented as in the shown data. The reference example returns lvl1,lvl2,lvl3 at same level.

Also, I need the lvl's key to be the lvl value. For example "x" and not "lvl1".

[{
  "x":{

Thank you

Answer 1

According the expected output, you can do it with three nested groupby and the use of to_dict. It is possible there is a better way but at least a start:

[df.groupby('Lvl1')\
  .apply(lambda x: x.groupby('lvl2')\
                    .apply(lambda x: [x.groupby('lvl3')
                                       .apply(lambda x: x[['lvl4','lvl5']].to_dict('r')
                                              ).to_dict()]
                          ).to_dict()
  ).to_dict()]

[{'x': {'1x': [{'3xx': [{'lvl4': 1, 'lvl5': '"text1"'},
                        {'lvl4': 2, 'lvl5': '"text2"'},
                        {'lvl4': 3, 'lvl5': '"text3"'}],
                '4xx': [{'lvl4': 4, 'lvl5': '"text4"'}]
                }],
        '2x': [{'4xx': [{'lvl4': 5, 'lvl5': '"text5"'},
                        {'lvl4': 6, 'lvl5': '"text6"'}]}]},...

I just have doubt on the exact external format

EDIT thanks to @Trenton McKinney, it seems that if you do:

df['lvl5'] = df['lvl5'].str.strip('"')
test = [df.groupby('Lvl1')\
          .apply(lambda x: x.groupby('lvl2')\
                            .apply(lambda x: [x.groupby('lvl3')
                                               .apply(lambda x: x[['lvl4','lvl5']].to_dict('r')
                                                      ).to_dict()]
                                  ).to_dict()
          ).to_dict()]

import json
json_res = list(map(json.dumps, test))

then json_res could fit json needs

Note:

• The following code, will properly save test to a double quoted json format

with open('data.json', 'w') as f:
    json.dump(test, f)