How to convert csv to nested json

Question

I have this csv file and wondering how to display result as expected ?

CSV File

order_date,customer_id,customername,order_id,total
22/06/2023,1,Allan,5,50
22/06/2023,2,Boy,2,150

Code

import JSON
import pandas as pd
df = pd.read_csv(file, dtype=str)

def get_nested_rec(key, grp):
    rec = {}
    rec['customer_id'] = key[0]
    rec['customername'] = key[1]
    rec['order_id'] = key[2]
    rec['total'] = key[3]

    return rec

records = []

for key, grp in df.groupby(['customer_id','customername','order_id','total']):
    rec = get_nested_rec(key, grp)
    records.append(rec)

records = dict(data = records)    

print(json.dumps(records, indent=4))    

Result

{
    "data": [
        {
            "customer_id": "A001",
            "customername": "Allan",
            "order_id": "5",
            "total": "50"
        },
        {
            "customer_id": "A002",
            "customername": "Boy",
            "order_id": "2",
            "total": "150"
        }
    ]
}

Expected result

{
    "order_date":"22/06/2023",
    "data": [
        {
            "customer_id": "A001",
            "customername": "Allan",
            "order_id": "5",
            "total": "50"
        },
        {
            "customer_id": "A002",
            "customername": "Boy",
            "order_id": "2",
            "total": "150"
        }
    ]
}

Another easy way to do ? I am new in this Nested JSON. Thank you.

Answer 1

Set order_date as index to transform remaining records as data and use to_dict to convert the dataframe as dictionary. Finally, export your result to JSON with to_json:

records = (df.set_index('order_date').groupby(level='order_date')
             .apply(lambda x: x.to_dict('records'))
             .rename('data').reset_index()
             .to_json(orient='records', indent=4))

Output:

>>> records
[
    {
        "order_date":"22\/06\/2023",
        "data":[
            {
                "customer_id":1,
                "customername":"Allan",
                "order_id":5,
                "total":50
            },
            {
                "customer_id":2,
                "customername":"Boy",
                "order_id":2,
                "total":150
            }
        ]
    }
]

If you have one and only one date, you can use:

records = (df.set_index('order_date').groupby(level='order_date')
             .apply(lambda x: x.to_dict('records'))
             .rename('data').reset_index()
             .squeeze().to_json(indent=4))

Output:

>>> records
{
    "order_date": "22/06/2023",
    "data": [
        {
            "customer_id": 1,
            "customername": "Allan",
            "order_id": 5,
            "total": 50
        },
        {
            "customer_id": 2,
            "customername": "Boy",
            "order_id": 2,
            "total": 150
        }
    ]
}