How to convert pandas dataframe to nested dictionary

Question

I am running Python 3.6 and Pandas 0.19.2 and have a DataFrame which looks as follows:

Name      Chain        Food       Healthy  

George    McDonalds    burger     False
George    KFC          chicken    False
John      Wendys       burger     False
John      McDonalds    salad      True

I want to transform this dataframe into a dict which looks as follows:

health_data = {'George': {'McDonalds': {'Food': 'burger', 'Healthy':False},
                          'KFC':       {'Food': 'chicken', 'Healthy':False}},
               'John':   {'Wendys':    {'Food': 'burger', 'Healthy':False},
                          'McDonalds': {'Food': 'salad', 'Healthy': True}}}

My thoughts so far are:

1. Use df.groupby to group the names column
2. Use df.to_dict() to transform the dataframe into a dictionary along the lines of: health_data = input_data.set_index('Chain').T.to_dict()

Thoughts? Thanks up front for the help.

Answer 1

I think you was very close.

Use groupby and to_dict:

df = df.groupby('Name')[['Chain','Food','Healthy']]
       .apply(lambda x: x.set_index('Chain').to_dict(orient='index'))
       .to_dict()

print (df)
{'George': {'KFC': {'Healthy': False, 'Food': 'chicken'}, 
           'McDonalds': {'Healthy': False, 'Food': 'burger'}}, 
'John': {'McDonalds': {'Healthy': True, 'Food': 'salad'},
         'Wendys': {'Healthy': False, 'Food': 'burger'}}}