Find all values that one column’s value has and collect as JSON

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}]

Answer

This approach should works for you:

import pandas as pd

records = {"class": ["XYZ", "XYZ", "ABC"], "code": ["A", "B", "C"], "id": [1, 2, 3]}
df = pd.DataFrame(records)
pd.DataFrame(
    [
        (x[0], x[1].drop(columns=["class"]).to_dict(orient="records"))
        for x in df.groupby("class")
    ],
    columns=["class", "codes"],
)

Output: