Dataframe group columns to nested json

Question

I want to output a dataframe by grouping by the first 2 columns in a dictionary format. This is my dataframe:

    COMUNIDAD   PROVINCIA   EMPLAZAMIENTO
0   ANDALUCIA   ALMERIA ALMERIA
1   ANDALUCIA   ALMERIA EJIDO, EL
2   ANDALUCIA   ALMERIA HUERCAL OVERA
3   ANDALUCIA   ALMERIA PECHINA
4   ANDALUCIA   ALMERIA VELEZ BLANCO
... ... ... ...
246 VALENCIA    VALENCIA    COFRENTES
247 VALENCIA    VALENCIA    MONDUBER
248 VALENCIA    VALENCIA    ONTENIENTE
249 VALENCIA    VALENCIA    PICAYO
250 VALENCIA    VALENCIA    UTIEL

and I want this output:

{
    "ANDALUCIA":{
        "ALMERIA":[
            "ALMERIA",
            "EJIDO, EL",
            ...

        ],
        "CADIZ":[
            "JEREZ DE LA FRONTERA",
            ...
        ]
    },
    "ARAGON":{
        "HUESCA":[
            "ARGUIS",
            ...
        ],
        ...
    },
    ...
}

What is the best way to achieve this though pandas?

Answer 1

Use GroupBy.agg for lists and then create nested dictionary:

s = df.groupby(['COMUNIDAD','PROVINCIA'])['EMPLAZAMIENTO'].agg(list)

d = {level: s.xs(level).to_dict() for level in s.index.levels[0]}
print (d)
{'ANDALUCIA': {'ALMERIA': ['ALMERIA', 'EJIDO, EL', 
                           'HUERCAL OVERA', 'PECHINA', 'VELEZ BLANCO']}, 
 'VALENCIA': {'VALENCIA': ['COFRENTES', 'MONDUBER',
                           'ONTENIENTE', 'PICAYO', 'UTIEL']}}

Last for json use:

import json
j = json.dumps(d)
print (j)
{"ANDALUCIA": {"ALMERIA": ["ALMERIA", "EJIDO, EL",
                           "HUERCAL OVERA", "PECHINA", "VELEZ BLANCO"]}, 
 "VALENCIA": {"VALENCIA": ["COFRENTES", "MONDUBER", 
                           "ONTENIENTE", "PICAYO", "UTIEL"]}}