How to convert dataframe to nested dictionary?

Question

I am running Python 3.7 and Pandas 1.1.3 and have a DataFrame which looks like this:

location = {'city_id': [22000,25000,27000,35000],
        'population': [3971883,2720546,667137,1323],
        'region_name': ['California','Illinois','Massachusetts','Georgia'],
        'city_name': ['Los Angeles','Chicago','Boston','Boston'],
        }

df = pd.DataFrame(location, columns = ['city_id', 'population','region_name', 'city_name'])

I want to transform this dataframe into a dict that looks like:

{
'Boston': {'Massachusetts': 27000, 'Georgia': 35000},
 'Chicago': {'Illinois': 25000},
 'Los Angeles': {'California': 22000}
}

And if the same cities in different regions, nested JSON should be sorted by population (for example Boston is in Massachusetts and Georgia. The city in Massachusetts is bigger, we output it first.

My code is:

result = df = df.groupby(['city_name'])[['region_name','city_id']].apply(lambda x: x.set_index('region_name').to_dict()).to_dict()

Output:

{'Boston': {'city_id': {'Massachusetts': 27000, 'Georgia': 35000}},
 'Chicago': {'city_id': {'Illinois': 25000}},
 'Los Angeles': {'city_id': {'California': 22000}}}

how can you see to dictionary add key - "city_id"

Tell me, please, how I should change my code that gets the expected result?

Anurag Dabas · Accepted Answer · 2021-03-30 15:38:08Z

1

just method chain apply() method to your current solution:

result=df.groupby(['city_name'])[['region_name','city_id']].apply(lambda x: x.set_index('region_name').to_dict()).apply(lambda x:list(x.values())[0]).to_dict()

Now if you print result you will get your expected output:

{'Boston': {'Massachusetts': 27000, 'Georgia': 35000},
 'Chicago': {'Illinois': 25000},
 'Los Angeles': {'California': 22000}}

answered Mar 30, 2021 at 15:38

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert dataframe to nested dictionary?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related