Pandas: How to Add Filter to Pivot Table

You can use the following basic syntax to add a filtering condition to a pandas pivot table:

df[df.col1 == 'A'].pivot_table(index='col1', values=['col2', 'col3'], aggfunc='sum')

This particular example creates a pivot table that displays the sum of values in col2 and col3, grouped by col1.

The filter before the pivot_table() function specifies that we only want to include rows where the value in col1 of the original DataFrame has a value of ‘A’.

The following example shows how to use this syntax in practice.

Example: How to Add Filter to Pandas Pivot Table

Suppose we have the following pandas DataFrame that contains information about various basketball players:

import pandas as pd

#create DataFrame
df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B',
                            'B', 'B', 'C', 'C', 'C', 'C'],
                   'points': [4, 4, 2, 8, 9, 5, 5, 7, 8, 8, 4, 3],
                   'assists': [2, 2, 5, 5, 4, 7, 5, 3, 9, 8, 4, 4]})

#view DataFrame
print(df)

   team  points  assists
0     A       4        2
1     A       4        2
2     A       2        5
3     A       8        5
4     B       9        4
5     B       5        7
6     B       5        5
7     B       7        3
8     C       8        9
9     C       8        8
10    C       4        4
11    C       3        4

We can use the following code to create a pivot table in pandas that shows the sum of the values in the points and assists columns grouped by team only for the rows where the original DataFrame has a value in the team column equal to ‘A’:

#create pivot table for rows where team is equal to 'A'
df[df.team == 'A'].pivot_table(index='team', values=['points', 'assists'],
                                 aggfunc='sum')

        assists	points
team		
A	14	18

Notice that the pivot table only summarizes the values in the points and assists columns for the rows where the team is equal to ‘A’.

You can also use the operators & and | to apply a filter that uses “AND” or “OR” logic.

For example, we can use the following syntax to create a pivot table that filters for rows where the value in the team column of the original DataFrame is equal to ‘A’ or ‘B’:

#create pivot table for rows where team is equal to 'A' or 'B'
df[(df.team == 'A') | (df.team == 'B')].pivot_table(index='team',
                                                    values=['points', 'assists'],
                                                    aggfunc='sum')

	assists	points
team		
A	14	18
B	19	26

Notice that the pivot table only summarizes the values in the points and assists columns for the rows where the team is equal to ‘A’ or ‘B’.

Note: You can find the complete documentation for the pandas pivot_table() function here.

Additional Resources

The following tutorials explain how to perform other common tasks in pandas:

Pandas: How to Sort Pivot Table by Values in Column
Pandas: How to Create Pivot Table with Sum of Values
Pandas: How to Add Subtotals to Pivot Table
Pandas: How to Modify Column Names in Pivot Table