Python: MultiIndex Dataframe to json-like list of dictionaries

Question

I want to store this data frame

df = pd.DataFrame({
            'id':[1,1,2,2], 
            'gender':["m","m","f","f"], 
            'val1':[1,2,5,6], 
            'val2':[3,4,7,8]
}).set_index(['id','gender'])

as a json file that contains lists of dictionaries like this:

d = [{'id':1, 'gender':"m", 'val1':[1,2], 'val2':[3,4]},
     {'id':2, 'gender':"f", 'val1':[5,6], 'val2':[7,8]}]

All my attempts to use variations of df.to_dict(orient = '...') or df.to_json(orient = '...') did not produce the desired output.

Answer 1

You first need to aggregate as list with groupby.agg and reset_index:

(df.groupby(['id', 'gender']).agg(list)
   .reset_index().to_json(orient='records')
)

Output:

[{"id":1,"gender":"m","val1":[1,2],"val2":[3,4]},{"id":2,"gender":"f","val1":[5,6],"val2":[7,8]}]