Convert python dataframe to nested json

[kat417]

I have flat csv data loaded into Data frame and trying to build nested json. I was able to build a nested json with orders as list but not able to add few columns like (geo, attributes) as dict within dict (json) and need some pointers on it.

Sample Data -
clientid requestid geo currency date orderid amount quantity attribute1 attribute2
X1 req1 AMER USD 16427166560 1111 600 1 Product A SKU-a
X1 req1 AMER USD 16427166500 1112 1200 1 Product B SKU-b
X1 req1 AMER CAD 16427167500 1113 1500 1 Product A SKU-a
X2 req2 EMEA EUR 16427163500 1114 1000 1 Product A SKU-a

Json O/P expected format -

Output:
{ "request": { "clientid": "X1", "requestid": "req1", "businessunit": { "geo": "AMER" } "currency": "USD", "orders": [{ "date": 16427166560, "order_id": "1111", "amount": "600", "attributes": { "attribute1": "Product A", "attribute2": "SKU-a" } }, { "date": 16427166500, "order_id": "1112", "amount": "1200", "attributes": { "attribute1": "Product B", "attribute2": "SKU-b" } } ] } }

Steps to reproduce

Python Data Frame

df = pd.DataFrame([['X1', 'req1', 'AMER', 'USD', 16427166560, '1111', 600, '1', 'Product A', 'SKU-a'], ['X1', 'req1', 'AMER', 'USD', 16427166500, '1112', 1200, '1', 'Product B', 'SKU-b'], ['X1', 'req1', 'AMER', 'CAD', 16427167500, '1113', 1500, '1', 'Product A', 'SKU-a'], ['X2', 'req2', 'EMEA', 'EUR', 16427163500, '1114', 1000, '1', 'Product A', 'SKU-a'] ], columns = ['clientid', 'requestid', 'geo', 'currency', 'date', 'order_id', 'amount', 'quantity', 'attribute1', 'attribute2'])

Initial draft which builds nested json -

j = (df.groupby(['clientid', 'requestid', 'geo', 'currency']) .apply(lambda x: x[['date', 'order_id', 'amount']].to_dict('records')) .reset_index() .rename(columns={0: 'orders'}) .to_json(orient='records'))

Good to have - If we can get separate json file as O/P for each geo/currency in single shot I would appreciate pointers on it.

[kat417]

Hello All, Please let know if you have any pointers