Pandas: How to Create Pivot Table with Count of Values

You can use one of the following methods to create a pivot table in pandas that displays the counts of values in certain columns:

Method 1: Pivot Table With Counts

pd.pivot_table(df, values='col1', index='col2', columns='col3',
               aggfunc='count')

Method 2: Pivot Table With Unique Counts

pd.pivot_table(df, values='col1', index='col2', columns='col3',
               aggfunc=pd.Series.nunique)

The following examples show how to use each method with the following pandas DataFrame:

import pandas as pd

#create DataFrame
df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
                   'position': ['G', 'G', 'F', 'C', 'G', 'F', 'F', 'F'],
                   'points': [4, 4, 6, 8, 9, 5, 5, 12]})

#view DataFrame
df

	team	position points
0	A	G	 4
1	A	G	 4
2	A	F	 6
3	A	C	 8
4	B	G	 9
5	B	F	 5
6	B	F	 5
7	B	F	 12

Method 1: Create Pandas Pivot Table With Counts

The following code shows how to create a pivot table in pandas that shows the total count of ‘points’ values for each ‘team’ and ‘position’ in the DataFrame:

#create pivot table
df_pivot = pd.pivot_table(df, values='points', index='team', columns='position',
                          aggfunc='count')

#view pivot table
df_pivot

position	C	  F	  G
team			
   A	      1.0	1.0	2.0
   B	      NaN	3.0	1.0

From the output we can see:

• There is 1 value in the ‘points’ column for team A at position C.
• There is 1 value in the ‘points’ column for team A at position F.
• There are 2 values in the ‘points’ column for team A at position G.

And so on.

Method 2: Create Pandas Pivot Table With Unique Counts

The following code shows how to create a pivot table in pandas that shows the total unique number of ‘points’ values for each ‘team’ and ‘position’ in the DataFrame:

#create pivot table
df_pivot = pd.pivot_table(df, values='points', index='team', columns='position',
                          aggfunc=pd.Series.nunique)

#view pivot table
df_pivot

position	C	  F	  G
team			
   A	      1.0	1.0	1.0
   B	      NaN	2.0	1.0

From the output we can see:

• There is 1 unique value in the ‘points’ column for team A at position C.
• There is 1 unique value in the ‘points’ column for team A at position F.
• There is 1 unique value in the ‘points’ column for team A at position G.

And so on.

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 operations in pandas:

Pandas: How to Reshape DataFrame from Long to Wide
Pandas: How to Reshape DataFrame from Wide to Long
Pandas: How to Group and Aggregate by Multiple Columns