CODEWITHSUNDEEP
pythonjsonpandasdataframe
che kiria
che kiria

Reputation: 1

How to convert dataframe to nested dictionary?

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?

Upvotes: 0

Views: 51

Answers (1)

Anurag Dabas
Anurag Dabas

Reputation: 24314

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}}

Upvotes: 1

Related Questions