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