Aggregate pandas rows and store as dicts or other suitable data types

user_3380902 0
Viewed 39

I have pandas DataFrame that I am trying to aggregate and store the select columns as dicts.

import pandas as pd

df = pd.DataFrame({
                   'id': [1, 1, 2],
                   'lat': [37.7825, 37.7825, 37.7836],
                   'lon': [-122.4148, -122.4148, -122.4127],
                   'b': [1, 2, 1],
                   'r': [1000, 1300, 1100],
                   's': [650, 720, 600]
                 })

I'd like to aggregate the DataFrame such that every unique combination of b, r and s are stored as collections, a list of dicts in one column.

Account for any edge cases such as NaNs in b, r or s. If NaNs, then do not store them as dicts.

Expected output:

id lat      lon        new

1  37.7825  -122.4148  [{'b': 1, 'r': 1000, 's': 650}, {'b': 2, 'r': 1300, 's': 720}]
2  37.7836  -122.4127  [{'b': 1, 'r': 1100, 's': 600}]
pandas-groupby pandas python-3.x dictionary python
1 Answers

You can call to_dict as "records" in groupby.apply:

out = df.groupby(['id','lat','lon'])[['b','r','s']].apply(lambda x: x.to_dict('records')).reset_index(name='new')

Output:

   id      lat       lon                                                             new
0   1  37.7825 -122.4148  [{'b': 1, 'r': 1000, 's': 650}, {'b': 2, 'r': 1300, 's': 720}]
1   2  37.7836 -122.4127                                 [{'b': 1, 'r': 1100, 's': 600}]
Admin1
Related