Convert python dataframe to nested json - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Convert python dataframe to nested json (/thread-36679.html) |
Convert python dataframe to nested json - kat417 - Mar-17-2022 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 - 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. RE: Convert python dataframe to nested json - kat417 - Mar-18-2022 Hello All, Please let know if you have any pointers |