Merge multiple values for same key to one dict/json (Pandas, Python, Dataframe)?-CodePudding

I have the following dataframe:

pd.DataFrame({'id':[1,1,1,2,2], 'key': ['a', 'a', 'b', 'a', 'b'], 'value': ['kkk', 'aaa', '5', 'kkk','8']})

I want to convert it to the following data frame:

id  value
1   {'a':['kkk', 'aaa'], 'b': 5}
2   {'a':['kkk'], 'b': 8}

I am trying to do this using .to_dict method but the output is

df.groupby(['id','key']).aggregate(list).groupby('id').aggregate(list)

{'value': {1: [['kkk', 'aaa'], ['5']], 2: [['kkk'], ['8']]}}

Should I perform dict comprehension or there is an efficient logic to build such generic json/dict?

CodePudding user response：

After you groupby(['id', 'key']) and agg(list), you can group by the first level of the index and for each group thereof, use droplevel to_dict:

new_df = df.groupby(['id', 'key']).agg(list).groupby(level=0).apply(lambda x: x['value'].droplevel(0).to_dict()).reset_index(name='value')

Output:

>>> new_df
   id                              value
0   1  {'a': ['kkk', 'aaa'], 'b': ['5']}
1   2         {'a': ['kkk'], 'b': ['8']}

Or, simpler:

new_df = df.groupby('id').apply(lambda x: x.groupby('key')['value'].agg(list).to_dict())