Convert Dataframe To Nested Json

I am using pd.read_sql_query() to get data from database and then use to_json(orient='records') this is the dataframe: (1) price_formula_id premium product_id exchange

Solution 1:

Use groupby with custom function with to_dict for all columns filtered by difference, reset_index and last convert it to_json:

cols = df.columns.difference(['price_formula_id','premium'])
j = (df.groupby(['price_formula_id','premium'])[cols]
       .apply(lambda x: x.to_dict('r'))
       .reset_index(name='basket')
       .to_json(orient='records'))
print (j)

[{
    "price_formula_id": 30064,
    "premium": 0.0,
    "basket": [{
            "exchange": "CME",
            "product_code": "CL",
            "product_id": "c001",
            "product_name": 2018,
            "weight": 0.3
        },
        {
            "exchange": "CME",
            "product_code": "CL",
            "product_id": "c002",
            "product_name": 2018,
            "weight": 0.7
        }
    ]
}]