Jan-06-2023, 11:16 PM
I need help on Panda pivot_table. The following are the code and the output. I will need help on:
1. Why Product and the Term are not in the output?
2. The columns in Excel are automatically in alphabetic order. How can I have the order in the order I specified ['Current_Bal','Amount1','Amount2']?
3. How to make it like Excel pivot table that has subtotal by Product and a grand total?
Thank you.
1. Why Product and the Term are not in the output?
2. The columns in Excel are automatically in alphabetic order. How can I have the order in the order I specified ['Current_Bal','Amount1','Amount2']?
3. How to make it like Excel pivot table that has subtotal by Product and a grand total?
Thank you.
sql = """ select Product , Term , Current_Bal , Amount1 , Amount2 from working.Temp_Ares_Data_Since_Aquisition where As_of_date = '2022-12-31'""" df = pd.read_sql(sql,cnxn) pivot = pd.pivot_table(df, values=['Current_Bal','Amount1','Amount2'], index=['Product','Term'],aggfunc='sum') writer = pd.ExcelWriter(fullpath) pivot.to_excel(writer, sheet_name='Pivot', index=False) writer.save()
Output:Amount1 Amount2 Current_Bal
100000 150000 50000