Manipulating Data Of Pandas Dataframe

I'm reading a dataframe and trying to 'insert' a list inside another list and then converting it to json file. I'm using python 3 and 0.25.3 version of pandas for it. My dataframe:

Solution 1:

First cast both columns to strings and then use lambda functions with DataFrame.to_dict and rename columns names, last convert output to json by DataFrame.to_json:

data[["part_number","number_client"]] = data[["part_number","number_client"]].astype(str)

f = lambda x: x.split('_')[0]

j =(data.groupby(["id","label","id_customer","label_customer"])['part_number','number_client']
        .apply(lambda x: x.rename(columns=f).to_dict('r')).reset_index(name='number')
        .groupby(["id", "label"])[ "id_customer", "label_customer", "number"]
        .apply(lambda x: x.rename(columns=f).to_dict('r')).reset_index(name='customer')
        .to_json(orient='records'))

---

print (j)

    [{
        "id": 6,
        "label": "Sao Paulo",
        "customer": [{
            "id": "CUST-99992",
            "label": "Brazil",
            "number": [{
                "part": "7897",
                "number": "982"
            }, {
                "part": "888",
                "number": "12"
            }]
        }]
    }, {
        "id": 92,
        "label": "Hong Kong",
        "customer": [{
            "id": "CUST-88888",
            "label": "China",
            "number": [{
                "part": "147",
                "number": "288"
            }]
        }]
    }]