Pandas Pivot Table: Create a Pivot table and find manager wise, salesman wise total sale and also display the sum of all sale amount at the bottom

Last update on April 04 2025 13:40:51 (UTC/GMT +8 hours)

8. Manager and Salesman-Wise Total Sale with Grand Total

Write a Pandas program to create a Pivot table and find manager wise, salesman wise total sale and also display the sum of all sale amount at the bottom. Go to Excel data

Sample Solution:

Python Code :

import pandas as pd
import numpy as np
df = pd.read_excel('E:\SaleData.xlsx')
table = pd.pivot_table(df,index=["Manager","SalesMan"],values=["Units","Sale_amt"],
               aggfunc=[np.sum],fill_value=0,margins=True)
print(table)

Sample Output:

                         sum      
                    Sale_amt Units
Manager SalesMan                  
Douglas John        124016.0   156
        Karen        48204.0   170
        Michael      66836.0    89
Hermann Luis        206373.0   281
        Shelli       33698.0   193
        Sigal       125037.5   173
Martha  Alexander   236703.0   396
        Diana        36100.0   125
        Steven      199690.0   183
Timothy David       140955.0   213
        Stephen      88063.0   142
All                1305675.5  2121

Pivot Table:

Salesdata.xlsx:

For more Practice: Solve these Related Problems:

Write a Pandas program to create a pivot table that calculates the total sale amount by manager and salesman, including a grand total row.
Write a Pandas program to generate a pivot table from Salesdata.xlsx that groups by manager and salesman, and then append the overall sale sum at the bottom.
Write a Pandas program to build a pivot table with multi-level indexes (manager, salesman) and display the sum of sale amounts along with an overall total.
Write a Pandas program to create a pivot table that aggregates sale amounts by manager and salesman, and then use margins=True to show the grand total.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a Pandas program to create a Pivot table and count the manager wise sale and mean value of sale amount.
Next: Write a Pandas program to create a Pivot table and find the total sale amount region wise, manager wise, sales man wise where Manager = "Douglas".