Dataframe Group Columns To Nested Json

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 ALME

Solution 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"]}}