Groupby values of dataframe of columns into JSON

Question

class_id	class	code	id
8	XYZ	A	1
8	XYZ	B	2
9	ABC	C	3

I have a dataframe like above. I want to transform it so the 'codes' column below collects all the unique (code, id) pairs into a JSON format that a class contains.

class_id	class	codes
8	XYZ	[{'code: 'A', 'id': 1}, {'code': 'B', 'id': 2}]
9	ABC	[{'code: 'C', 'id': 3}]

user7864386 · Accepted Answer · 2022-04-01 19:45:00Z

4

You could use groupby.apply where you pass in a lambda that uses the to_dict method:

out = df.groupby(['class_id','class'])[['code','id']].apply(lambda x: x.to_dict('records')).reset_index(name='codes')

Output:

   class_id class                                             codes
0         8   XYZ  [{'code': 'A', 'id': 1}, {'code': 'B', 'id': 2}]
1         9   ABC                          [{'code': 'C', 'id': 3}]

answered Apr 1, 2022 at 19:45

user7864386

Sign up to request clarification or add additional context in comments.

2 Comments

hedebyhedge Over a year ago

Thanks for the answers. What would be the best way to rename 'code' and 'id' inside the JSON to something else. So something like {'C': 'B', 'I': 2}

user7864386 Over a year ago

@hedebyhedge you could rename columns, then use groupby.apply

Collectives™ on Stack Overflow

Groupby values of dataframe of columns into JSON

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related