+- 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: Panda pivot_table questions (/thread-39136.html)
Panda pivot_table questions - dee - Jan-06-2023
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.
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 50000RE: Panda pivot_table questions - dee - Jan-09-2023
I figured out my question #1. Remove index=False in pivot.to_excel(writer, sheet_name='Pivot', index=False).
For #2, the order is in the order I specified when I created another Python scripts. Not sure why.
I will still need help on #3. The Grand Total is incorrect for calculated columns. I also need help on how to have subtotal.
Thanks.