Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

Pandas Pivot Table: Create a Pivot table with multiple indexes from a given excel sheet

Pandas: Pivot Table Exercise-1 with Solution

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.



Python: Tips of the Day

Find current directory and file's directory:

To get the full path to the directory a Python file is contained in, write this in that file:

import os 
dir_path = os.path.dirname(os.path.realpath(__file__))

(Note that the incantation above won't work if you've already used os.chdir() to change your current working directory, since the value of the __file__ constant is relative to the current working directory and is not changed by an os.chdir() call.)

To get the current working directory use

import os
cwd = os.getcwd()

Documentation references for the modules, constants and functions used above:

  • The os and os.path modules.
  • The __file__ constant
  • os.path.realpath(path) (returns "the canonical path of the specified filename, eliminating any symbolic links encountered in the path")
  • os.path.dirname(path) (returns "the directory name of pathname path")
  • os.getcwd() (returns "a string representing the current working directory")
  • os.chdir(path) ("change the current working directory to path")

Ref: https://bit.ly/3fy0R6m