Table of Contents
To create a pivot table with a count of values in Pandas, one would use the pivot_table() method to indicate the data values to be included in the table, specify the value column to be counted, and set the aggfunc to ‘count’. The pivot_table() method returns a DataFrame with the index and column labels set to the unique values in the specified columns, and the values in the DataFrame cells populated with the count of those 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 .