Using pandas' groupby and agg to merge column containing dicts

Question

python 3.6.5

seasoned python veteran new to pandas and dataframes

given

d = {'time':['2021-08-12 14:59:15.230','2021-08-12 14:59:15.230','2021-08-12 14:59:15.230']
   , 'dayy':['14:59:15.231818','14:59:15.231330','14:59:15.232206']
   , 'catg':['40B0','4035','40E8']
   , 'data':[{'OQWOWF': '0', 'OQD8S1F': '10010100'},{'OQTADT': 30.0, 'OQTAVL': '0', 'OQAOAD': 2},{'OQGS01': '0', 'OQGS02': '0', 'OQGS03': '0'}]
   }
df = pd.DataFrame( data=d)
print(df)

yields

                      time             dayy  catg                                           data
0  2021-08-12 14:59:15.230  14:59:15.231818  40B0         {'OQWOWF': '0', 'OQD8S1F': '10010100'}
1  2021-08-12 14:59:15.230  14:59:15.231330  4035   {'OQTADT': 30.0, 'OQTAVL': '0', 'OQAOAD': 2}
2  2021-08-12 14:59:15.230  14:59:15.232206  40E8  {'OQGS01': '0', 'OQGS02': '0', 'OQGS03': '0'

how can I merge the dicts into one dict grouped by time? ie

2021-08-12 14:59:15.230  {'OQWOWF': '0', 'OQD8S1F': '10010100', 'OQTADT': 30.0, 'OQTAVL': '0', 'OQAOAD': 2, 'OQGS01': '0', 'OQGS02': '0', 'OQGS03': '0'}

I've gotten the groupby and columns-listed parts down but sense I need either an .apply() or an .agg() function

df.groupby('time')['data'].???

Any ideas?

TIA

Still-learning Steve

related: [`df.groupby("time")['data'].agg(lambda x: {k: v for d in x for k, v in d.items()})`](https://stackoverflow.com/questions/3494906/how-do-i-merge-a-list-of-dicts-into-a-single-dict) — anky, Aug 13 '21 at 18:07

score 2 · Accepted Answer · answered Aug 13 '21 at 18:00

You can create a custom function to merge the dictionaries then use the function as aggregate for groupby object:

def mergeDicts(series):
    result = {}
    for d in series.values:
        result.update(d)
    return result

df.groupby('time')['data'].agg(mergeDicts)

OUTPUT:

time
2021-08-12 14:59:15.230    {'OQWOWF': '0', 'OQD8S1F': '10010100', 'OQTADT...
Name: data, dtype: object

PS: If multiple values exist for the same dictionary key under a group, the resulting dictionary for the group will always have the last value.

both solutions work - I went with the one-liner. Thanks so much! — code_warrior, Aug 13 '21 at 18:53

Using pandas' groupby and agg to merge column containing dicts

1 Answers1