Create a Pivot Table in Python using Pandas

October 29, 2022

In this guide, you’ll see how to create a pivot table in Python using Pandas.

More specifically, you’ll observe how to pivot your data across 5 different scenarios.

Steps to Create a Pivot Table in Python using Pandas

Firstly, you’ll need to capture the data in Python.

You can accomplish this task using Pandas DataFrame:

import pandas as pd

data = {'person': ['A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E'],
        'sales': [1000, 300, 400, 500, 800, 1000, 500, 700, 50, 60, 1000, 900, 750, 200, 300, 1000, 900, 250, 750, 50],
        'quarter': [1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4],
        'country': ['US', 'Japan', 'Brazil', 'UK', 'US', 'Brazil', 'Japan', 'Brazil', 'US', 'US', 'US', 'Japan', 
                    'Brazil', 'UK', 'Brazil', 'Japan', 'Japan', 'Brazil', 'UK', 'US']
        }

df = pd.DataFrame(data)

print(df)

Run the above code in Python, and you’ll get the following DataFrame:

   person  sales  quarter country
0       A   1000        1      US
1       B    300        1   Japan
2       C    400        1  Brazil
3       D    500        1      UK
4       E    800        1      US
5       A   1000        2  Brazil
6       B    500        2   Japan
7       C    700        2  Brazil
8       D     50        2      US
9       E     60        2      US
10      A   1000        3      US
11      B    900        3   Japan
12      C    750        3  Brazil
13      D    200        3      UK
14      E    300        3  Brazil
15      A   1000        4   Japan
16      B    900        4   Japan
17      C    250        4  Brazil
18      D    750        4      UK
19      E     50        4      US

Once you have your DataFrame ready, you’ll be able to pivot your data.

5 Scenarios of Pivot Tables in Python using Pandas

Scenario 1: Total sales per person

To get the total sales per person, you’ll need to add the following syntax to the Python code:

pivot = df.pivot_table(index=['person'], values=['sales'], aggfunc='sum')

This will allow you to sum the sales (across the 4 quarters) per person by using the aggfunc=’sum’ operation.

Your complete Python code would look like this:

import pandas as pd

data = {'person': ['A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E'],
        'sales': [1000, 300, 400, 500, 800, 1000, 500, 700, 50, 60, 1000, 900, 750, 200, 300, 1000, 900, 250, 750, 50],
        'quarter': [1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4],
        'country': ['US', 'Japan', 'Brazil', 'UK', 'US', 'Brazil', 'Japan', 'Brazil', 'US', 'US', 'US', 'Japan',
                    'Brazil', 'UK', 'Brazil', 'Japan', 'Japan', 'Brazil', 'UK', 'US']
        }

df = pd.DataFrame(data)

pivot = df.pivot_table(index=['person'], values=['sales'], aggfunc='sum')

print(pivot)

Once you run the code, you’ll get the total sales by person:

        sales
person       
A        4000
B        2600
C        2100
D        1500
E        1210

Scenario 2: Total sales by country

Now, you’ll see how to group the total sales by the county.

Here, you’ll need to aggregate the results by the ‘country‘ field, rather than the ‘person’ field, as you saw in the first scenario.

You may then run the following code in Python:

import pandas as pd

data = {'person': ['A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E'],
        'sales': [1000, 300, 400, 500, 800, 1000, 500, 700, 50, 60, 1000, 900, 750, 200, 300, 1000, 900, 250, 750, 50],
        'quarter': [1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4],
        'country': ['US', 'Japan', 'Brazil', 'UK', 'US', 'Brazil', 'Japan', 'Brazil', 'US', 'US', 'US', 'Japan',
                    'Brazil', 'UK', 'Brazil', 'Japan', 'Japan', 'Brazil', 'UK', 'US']
        }

df = pd.DataFrame(data)

pivot = df.pivot_table(index=['country'], values=['sales'], aggfunc='sum')

print(pivot)

You’ll then get the total sales by county:

         sales
country       
Brazil    3400
Japan     3600
UK        1450
US        2960

Scenario 3: Sales by both the person and the country

You may aggregate the results by more than one field (unlike the previous two scenarios where you aggregated the results based on a single field).

For example, you may use the following two fields to get the sales by both the:

  • person; and
  • country
import pandas as pd

data = {'person': ['A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E'],
        'sales': [1000, 300, 400, 500, 800, 1000, 500, 700, 50, 60, 1000, 900, 750, 200, 300, 1000, 900, 250, 750, 50],
        'quarter': [1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4],
        'country': ['US', 'Japan', 'Brazil', 'UK', 'US', 'Brazil', 'Japan', 'Brazil', 'US', 'US', 'US', 'Japan',
                    'Brazil', 'UK', 'Brazil', 'Japan', 'Japan', 'Brazil', 'UK', 'US']
        }

df = pd.DataFrame(data)

pivot = df.pivot_table(index=['person', 'country'], values=['sales'], aggfunc='sum')

print(pivot)

Run the code, and you’ll see the sales by both the person and the country:

                sales
person country       
A      Brazil    1000
       Japan     1000
       US        2000
B      Japan     2600
C      Brazil    2100
D      UK        1450
       US          50
E      Brazil     300
       US         910

Scenario 4: Maximum individual sale by country

So far, you used the sum operation (i.e., aggfunc=’sum’) to group the results, but you are not limited to that operation.

In this scenario, you’ll find the maximum individual sale by the county using the aggfunc=’max’

import pandas as pd

data = {'person': ['A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E'],
        'sales': [1000, 300, 400, 500, 800, 1000, 500, 700, 50, 60, 1000, 900, 750, 200, 300, 1000, 900, 250, 750, 50],
        'quarter': [1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4],
        'country': ['US', 'Japan', 'Brazil', 'UK', 'US', 'Brazil', 'Japan', 'Brazil', 'US', 'US', 'US', 'Japan',
                    'Brazil', 'UK', 'Brazil', 'Japan', 'Japan', 'Brazil', 'UK', 'US']
        }

df = pd.DataFrame(data)

pivot = df.pivot_table(index=['country'], values=['sales'], aggfunc='max')

print(pivot)

And the result:

         sales
country       
Brazil    1000
Japan     1000
UK         750
US        1000

Scenario 5: Mean, median and minimum sales by country

You can use multiple operations within the aggfunc argument. For example, to find the mean, median and minimum sales by country, you may use:

aggfunc={'median', 'mean', 'min'}

And here is the complete Python code:

import pandas as pd

data = {'person': ['A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E', 'A', 'B', 'C', 'D', 'E'],
        'sales': [1000, 300, 400, 500, 800, 1000, 500, 700, 50, 60, 1000, 900, 750, 200, 300, 1000, 900, 250, 750, 50],
        'quarter': [1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4],
        'country': ['US', 'Japan', 'Brazil', 'UK', 'US', 'Brazil', 'Japan', 'Brazil', 'US', 'US', 'US', 'Japan',
                    'Brazil', 'UK', 'Brazil', 'Japan', 'Japan', 'Brazil', 'UK', 'US']
        }

df = pd.DataFrame(data)

pivot = df.pivot_table(index=['country'], values=['sales'], aggfunc={'median', 'mean', 'min'})

print(pivot)

You’ll then get the following results:

              sales              
               mean median    min
country                          
Brazil   566.666667  550.0  250.0
Japan    720.000000  900.0  300.0
UK       483.333333  500.0  200.0
US       493.333333  430.0   50.0

You just saw how to create pivot tables across 5 simple scenarios. But the concepts reviewed here can be applied across large number of different scenarios.

You can find additional information about pivot tables by visiting the Pandas documentation.