'Pandas DataFrame directly to nested json while missing json-key

I have a DataFrame that I want to convert into a dictionary object to then write it as a .json file. The problem was the specific format, which I need the output .json file to have.

My DataFrame is the following:

departments,     users
'department_a',  'user_a'
'demaprtment_a', 'user_b'
'dempartment_b', 'user_a'
'department_b',  'user_c'
'department_c',  'user_d'

I want to insert the df to a custom .json file. I used to group the DataFrame by 'department' (df.groupby(department).apply(list).to_dict())

The .json-output was the following:

'departments'{ 'department_a':['user_a', 'user_b'], 'dempartment_b':['user_a', 'user_c'], ...}

So the column 'users' is ordered as Array-Type into the JSON, but I need the following:

'departments'{ 'department_a': {'users':['user_a', 'user_b']}, 'dempartment_b': {'users':['user_a', 'user_c']}, ...}

In the Pandas documentation, I can't find the right method to write the DataFrame as desired to a dictionary. I basically need the key 'users' in-between the key 'departments' and the listed users for each but don't know how to insert it. Is there a way to create that kind of dictionary right away and if not, what's a quick way to achieve that quickly.

Did I miss some functions from pandas that let me do this? Alternatively, I could also slightly modify the DataFrame structure for that.

-thanks in advance



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source