Converting a pandas dataframe to a nested dictionary with specific key

  dataframe, pandas, python

I am trying to create a nested dictionary from a pandas dataframe with the following format:

Name Info  Location  Alias

AA   InfoA locationA AliasA
BB   InfoB locationB AliasB
CC   InfoC locationC AliasC

The result dict that I am looking for has the following format:

my_nested_dict = {"AA": {"Info" : "InfoA", "Location": {"Where": "locationA", "Alias": "AliasA"},
                  "BB": {"Info" : "InfoB", "Location": {"Where": "locationB", "Alias": "AliasB"},
                  "CC": {"Info" : "InfoC", "Location": {"Where": "locationC", "Alias": "AliasC"}
}

I’ve tried to use groupby and apply a lambda with set_index but it seems that I am doing it wrong:

df.groupby('Name')[['Location', 'Alias']].apply(lambda x: x.set_index('Location').to_dict(orient='index')).to_dict()

But with this I am not able to add the "Where" key. The output looks like:

my_nested_dict = {"AA": {"Info" : "InfoA", "Location": {"locationA": {"Alias": "AliasA"}},
                  "BB": {"Info" : "InfoB", "Location": {"locationB": {"Alias": "AliasB"}},
                  "CC": {"Info" : "InfoC", "Location": {"locationC": {"Alias": "AliasC"}}
}

Additionally, I’ve tried to first convert the columns that I am interested in, which works but I am having hard time to replace the "Location" column with the result:

df[['Location', 'Alias']].rename(columns={'Location': 'Where'}).to_dict('index')

What is the way to achieve the expected result?

Thanks in advance!

Source: Python Questions

LEAVE A COMMENT