Pandas Pivot Table: Create a Pivot table with multiple indexes from a given excel sheet
Write a Pandas program to create a Pivot table with multiple indexes from a given excel sheet (Salesdata.xlsx). Go to Excel data
Sample Solution:
Python Code :
import pandas as pd
df = pd.read_excel('E:\SaleData.xlsx')
print(df)
pd.pivot_table(df,index=["Region","SalesMan"])
Sample Output:
OrderDate Region Manager ... Units Unit_price Sale_amt
0 2018-01-06 East Martha ... 95.00 1198.000 113810.00
1 2018-01-23 Central Hermann ... 50.00 500.000 25000.00
2 2018-02-09 Central Hermann ... 36.00 1198.000 43128.00
3 2018-02-26 Central Timothy ... 27.00 225.000 6075.00
4 2018-03-15 West Timothy ... 56.00 1198.000 67088.00
5 2018-04-01 East Martha ... 60.00 500.000 30000.00
6 2018-04-18 Central Martha ... 75.00 1198.000 89850.00
7 2018-05-05 Central Hermann ... 90.00 1198.000 107820.00
8 2018-05-22 West Douglas ... 32.00 1198.000 38336.00
9 2018-06-08 East Martha ... 60.00 500.000 30000.00
10 2018-06-25 Central Hermann ... 90.00 1198.000 107820.00
11 2018-07-12 East Martha ... 29.00 500.000 14500.00
12 2018-07-29 East Douglas ... 81.00 500.000 40500.00
13 2018-08-15 East Martha ... 35.00 1198.000 41930.00
14 2018-09-01 Central Douglas ... 2.00 125.000 250.00
15 2018-09-18 East Martha ... 16.00 58.500 936.00
16 2018-10-05 Central Hermann ... 28.00 500.000 14000.00
17 2018-10-22 East Martha ... 64.00 225.000 14400.00
18 2018-11-08 East Douglas ... 15.00 225.000 3375.00
19 2018-11-25 Central Hermann ... 96.00 58.500 5616.00
20 2018-12-12 Central Douglas ... 67.00 1198.000 80266.00
21 2018-12-29 East Douglas ... 74.00 58.500 4329.00
22 2019-01-15 Central Timothy ... 46.00 500.000 23000.00
23 2019-02-01 Central Douglas ... 87.00 500.000 43500.00
24 2019-02-18 East Martha ... 4.00 500.000 2000.00
25 2019-03-07 West Timothy ... 7.00 500.000 3500.00
26 2019-03-24 Central Hermann ... 50.00 58.500 2925.00
27 2019-04-10 Central Martha ... 66.00 1198.000 79068.00
28 2019-04-27 East Martha ... 96.00 225.000 21600.00
29 2019-05-14 Central Timothy ... 53.00 1198.000 63494.00
30 2019-05-31 Central Timothy ... 80.00 500.000 40000.00
31 2019-06-17 Central Hermann ... 5.00 125.000 625.00
32 2019-07-04 East Martha ... 62.00 58.500 3627.00
33 2019-07-21 Central Hermann ... 55.00 58.500 3217.50
34 2019-08-07 Central Hermann ... 42.00 58.500 2457.00
35 2019-08-24 West Timothy ... 3.00 125.000 375.00
36 2019-09-10 Central Timothy ... 7.00 1198.000 8386.00
37 2019-09-27 West Timothy ... 76.00 225.000 17100.00
38 2019-10-14 West Douglas ... 57.00 500.000 28500.00
39 2019-10-31 Central Martha ... 14.00 1198.000 16772.00
40 2019-11-17 Central Hermann ... 11.00 500.000 5500.00
41 2019-12-04 Central Hermann ... 94.00 500.000 47000.00
42 2019-12-21 Central Martha ... 28.00 500.000 14000.00
43 NaT NaN NaN ... 278.00 1125.000 62550.00
44 NaT NaN NaN ... 34.75 140.625 7818.75
[45 rows x 8 columns]
Pivot Table:
Salesdata.xlsx:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Python Pandas Pivot Table Exercises Home.
Next: Write a Pandas program to create a Pivot table and find the total sale amount region wise, manager wise.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics