I am trying to take a pandas dataframe and convert it to nested JSON. The default to_json options all lead to repeated data that I would rather be summarized in the format shown below. I've tried following the solution here [Convert Pandas Dataframe to nested JSON but I keep getting [] as a result. I am not sure what I am doing wrong and would appreciate some explanation as to why my code is not working correctly and how to achieve the desired results. I have attached the validated json output that I would like to achieve, a sample of data that I will be using to generate the JSON, and the code I have tried so far. Thanks in advance!
Desired Result
{
"Entries": [{
"Plant": "1234",
"Classification": "Fresh Plant",
"Material": "135474968746485",
"Material Description": "Stuff",
"Week": "MAY-2020 2020-04-27 To 2020-05-03",
"Segment": "FRESH",
"Amount": "356.68",
"Date": "May-20",
"Type": "UPC < $+/-1500",
"Comment": "Something",
"Main Group (PH1)": "Pharma - Value Added",
"Product Group (PH2)": "Heparin",
"Product Line (PH3)": "Heparin - API",
"Product Category (PH4)": "PH - Heparin Sodium",
"Product Form (PH5)": "Pharma - Dry",
"Material Group": "FIN/WIP- Pharma",
"Show Detail": "0",
"Detail": [{
"Movement Type": "1234",
"Movement Type Text": "Movement",
"Reason": "PH99619",
"Batch": "abcd",
"Storage Location": "7001",
"User name": "Cdizzle",
"Reference": "asdfasdf",
"Quantity": "1234",
"Unit": "LB",
"Qty in PUoM": "131",
"Entry Date": "4/5/2020",
"Posting Date": "4/3/2020",
"ID": "12345"
},
{
"Movement Type": "1234",
"Movement Type Text": "Movement",
"Reason": "PH99619",
"Batch": "abcd",
"Storage Location": "7001",
"User name": "Cdizzle",
"Reference": "asdfasdf",
"Quantity": "1234",
"Unit": "LB",
"Qty in PUoM": "131",
"Entry Date": "4/5/2020",
"Posting Date": "4/3/2020",
"ID": "12345"
}
]
}]
}
Code I've Tried
j = (df.groupby(['Plant','Classification','Combined','Material','Week','Segment','Amount','Date','Type','Comment','Material Description', 'Main Group (PH1)', 'Product Group (PH2)', 'Product Line (PH3)',
'Product Category (PH4)', 'Product Form (PH5)', 'Material Group','Show Detail'], as_index=False)
.apply(lambda x: x[['Movement Type','Movement Type Text','Reason','Batch','Storage Location','User name','Reference','Quantity','Unit','Qty in PUoM','Posting Date','Entry Date','ID']]
.to_dict('r'))
.reset_index()
.rename(columns={0:'detail'})
.to_json(orient='records'))
print(json.dumps(json.loads(j), indent=2, sort_keys=True))
Sample Data
,Plant,Classification,Combined,Material,Week,Segment,Amount,Date,Type,Comment,Movement Type,Movement Type Text,Reason,Batch,Storage Location,User name,Reference,Quantity,Unit,Qty in PUoM,Posting Date,Entry Date,Material Description,Main Group (PH1),Product Group (PH2),Product Line (PH3),Product Category (PH4),Product Form (PH5),Material Group,Show Detail,ID
0,1209,Fresh Plant,701-722 Weight Adjustments,90070800073002,MAY-2020 2020-04-27 To 2020-05-03,FRESH,356.68,MAY-2020,UPC < $+/-1500,,701,GR phys.inv.: whse,,PH99619,7001,CDWILLIAMS3,,0.0,G,11.0,2020-04-29,2020-04-29,"HEPARIN,SODIUM,PH3005,1GAL-NALGENE",Pharma - Value Added,Heparin,Heparin - API,PH - Heparin Sodium,Pharma - Dry,FIN/WIP- Pharma ,0,12985
1,1210,Fresh Plant,551-552 STO Variance Correction (9011),90070247101405,MAY-2020 2020-04-27 To 2020-05-03,FRESH,0.27,MAY-2020,UPC < $+/-1500,,551,GI scrapping,9011,0119011931,7001,MFLYNN,0081871949,0.001,CBO,2.0,2020-04-30,2020-04-30,"SPLEEN,PK,INED,BULK,TUB/CBO",Kill/Cut,Hot Offals,Spleens,Dry - Fresh,Combo,Commodity Pork,0,14612
2,1210,Fresh Plant,551-552 STO Variance Correction (9011),90070247101405,MAY-2020 2020-04-27 To 2020-05-03,FRESH,0.27,MAY-2020,UPC < $+/-1500,,552,RE scrapping,9011,0119011931,7001,MFLYNN,0081871949,0.001,CBO,1.0,2020-04-30,2020-04-30,"SPLEEN,PK,INED,BULK,TUB/CBO",Kill/Cut,Hot Offals,Spleens,Dry - Fresh,Combo,Commodity Pork,0,14614
3,1210,Fresh Plant,551-552 STO Variance Correction (9011),90070247101931,MAY-2020 2020-04-27 To 2020-05-03,FRESH,3.66,MAY-2020,UPC < $+/-1500,,552,RE scrapping,9011,0120012031,7001,MFLYNN,0081871954,0.033,CBO,54.0,2020-04-30,2020-04-30,"LUNGS,PK,INED,BULK,TUB/CBO",Kill/Cut,Hot Offals,Lungs,Dry - Fresh,Combo,Commodity Pork,0,14615
4,1210,Fresh Plant,551-552 STO Variance Correction (9011),90070247101955,MAY-2020 2020-04-27 To 2020-05-03,FRESH,73.68,MAY-2020,UPC < $+/-1500,,552,RE scrapping,9011,0120012011,7001,MFLYNN,0081874842,0.273,CBO,400.0,2020-05-01,2020-05-01,"LIVER,PK,INED,BULK,TUB/CBO",Kill/Cut,Hot Offals,Livers,Dry - Fresh,Combo,Commodity Pork,0,16674
5,1210,Fresh Plant,551-552 STO Variance Correction (9011),90070247707195,MAY-2020 2020-04-27 To 2020-05-03,FRESH,748.22,MAY-2020,UPC < $+/-1500,,552,RE scrapping,9011,0120012011,7001,MFLYNN,0081874842,1.052,CBO,1782.0,2020-05-01,2020-05-01,"GULLET,PK,FROM TONGUE BASE,INED,CBO",Kill/Cut,Hot Offals,Trachea,Dry - Fresh,Combo,Commodity Pork,0,16675
6,1210,Fresh Plant,551-552 STO Variance Correction (9011),90070247707195,MAY-2020 2020-04-27 To 2020-05-03,FRESH,748.22,MAY-2020,UPC < $+/-1500,,551,GI scrapping,9011,0118011811,7001,MFLYNN,0081874842,1.095,CBO,1782.0,2020-05-01,2020-05-01,"GULLET,PK,FROM TONGUE BASE,INED,CBO",Kill/Cut,Hot Offals,Trachea,Dry - Fresh,Combo,Commodity Pork,0,16666
7,1210,Fresh Plant,551-552 STO Variance Correction (9011),90191883506406,MAY-2020 2020-04-27 To 2020-05-03,FRESH,133.86,MAY-2020,UPC < $+/-1500,,552,RE scrapping,9011,8820913400,7001,MFLYNN,0081874819,0.151,CBO,224.0,2020-05-01,2020-05-01,FROZEN INEDIBLE NZ OVINE TRACHEA,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,16668
8,1210,Fresh Plant,551-552 STO Variance Correction (9011),90191883506444,MAY-2020 2020-04-27 To 2020-05-03,FRESH,23730.420000000002,MAY-2020,UPC > $+/-1500,,552,RE scrapping,9011,9348934810,7001,MFLYNN,0081874819,2.043,CBO,3642.0,2020-05-01,2020-05-01,FROZEN INEDIBLE NZ LAMB MDM,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,16669
9,1210,Fresh Plant,551-552 STO Variance Correction (9011),90191883506444,MAY-2020 2020-04-27 To 2020-05-03,FRESH,23730.420000000002,MAY-2020,UPC > $+/-1500,,552,RE scrapping,9011,9350935010,7001,MFLYNN,0081874819,1.997,CBO,3561.0,2020-05-01,2020-05-01,FROZEN INEDIBLE NZ LAMB MDM,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,16670
10,1210,Fresh Plant,551-552 STO Variance Correction (9011),90191883506444,MAY-2020 2020-04-27 To 2020-05-03,FRESH,23730.420000000002,MAY-2020,UPC > $+/-1500,,552,RE scrapping,9011,9351935110,7001,MFLYNN,0081874819,1.935,CBO,3450.0,2020-05-01,2020-05-01,FROZEN INEDIBLE NZ LAMB MDM,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,16671
11,1210,Fresh Plant,551-552 STO Variance Correction (9011),90191883506444,MAY-2020 2020-04-27 To 2020-05-03,FRESH,23730.420000000002,MAY-2020,UPC > $+/-1500,,552,RE scrapping,9011,9352935210,7001,MFLYNN,0081874819,0.971,CBO,1731.0,2020-05-01,2020-05-01,FROZEN INEDIBLE NZ LAMB MDM,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,16672
12,1210,Fresh Plant,551-552 STO Variance Correction (9011),90191883506444,MAY-2020 2020-04-27 To 2020-05-03,FRESH,23730.420000000002,MAY-2020,UPC > $+/-1500,,552,RE scrapping,9011,9353935310,7001,MFLYNN,0081874819,2.01,CBO,3583.0,2020-05-01,2020-05-01,FROZEN INEDIBLE NZ LAMB MDM,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,16673
13,1210,Fresh Plant,551-552 STO Variance Correction (9011),90191883506444,MAY-2020 2020-04-27 To 2020-05-03,FRESH,23730.420000000002,MAY-2020,UPC > $+/-1500,,551,GI scrapping,9011,8104931100,7001,MFLYNN,0081874819,9.0,CBO,16047.0,2020-05-01,2020-05-01,FROZEN INEDIBLE NZ LAMB MDM,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,16667
14,1210,Fresh Plant,551-552 STO Variance Correction (9011),90191883550096,MAY-2020 2020-04-27 To 2020-05-03,FRESH,1.98,MAY-2020,UPC < $+/-1500,,552,RE scrapping,9011,9301930115,7001,MFLYNN,0081874447,0.001,CBO,2.0,2020-04-30,2020-04-30,FROZEN INEDIBLE WIP MD DUCK LOW ASH,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,14613
15,1210,Fresh Plant,701-722 Weight Adjustments,90191883506352,MAY-2020 2020-04-27 To 2020-05-03,FRESH,0.0,MAY-2020,UPC < $+/-1500,,701,GR phys.inv.: whse,,9344934410,7001,MFLYNN,,0.531,CBO,0.0,2020-04-30,2020-04-30,FROZEN INEDIBLE NZ LAMB LIVERS,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,14616
16,1210,Fresh Plant,701-722 Weight Adjustments,90191883506352,MAY-2020 2020-04-27 To 2020-05-03,FRESH,0.0,MAY-2020,UPC < $+/-1500,,701,GR phys.inv.: whse,,9346934600,7001,MFLYNN,,0.047,CBO,0.0,2020-04-30,2020-04-30,FROZEN INEDIBLE NZ LAMB LIVERS,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,14617
17,1210,Fresh Plant,701-722 Weight Adjustments,90191883506352,MAY-2020 2020-04-27 To 2020-05-03,FRESH,0.0,MAY-2020,UPC < $+/-1500,,702,GI phys.inv.: whse,,9323932310,7001,MFLYNN,,0.02,CBO,0.0,2020-04-30,2020-04-30,FROZEN INEDIBLE NZ LAMB LIVERS,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,14618
18,1210,Fresh Plant,701-722 Weight Adjustments,90191883506352,MAY-2020 2020-04-27 To 2020-05-03,FRESH,0.0,MAY-2020,UPC < $+/-1500,,702,GI phys.inv.: whse,,9305930510,7001,MFLYNN,,0.038,CBO,0.0,2020-04-30,2020-04-30,FROZEN INEDIBLE NZ LAMB LIVERS,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,14619
19,1210,Fresh Plant,701-722 Weight Adjustments,90191883506352,MAY-2020 2020-04-27 To 2020-05-03,FRESH,0.0,MAY-2020,UPC < $+/-1500,,702,GI phys.inv.: whse,,9344934410,7001,MFLYNN,,0.5,CBO,0.0,2020-04-30,2020-04-30,FROZEN INEDIBLE NZ LAMB LIVERS,Fr Pork Other,Other,Other,Dry - Frozen,Other,Pet Food Frozen,0,14620
