You can use the following syntax to count the number of unique combinations across two columns in a pandas DataFrame:
df[['col1', 'col2']].value_counts().reset_index(name='count')
The following example shows how to use this syntax in practice.
Example: Count Unique Combinations of Two Columns in Pandas
Suppose we have the following pandas DataFrame that shows the team and position of various basketball players:
import pandas as pd #create dataFrame df = pd.DataFrame({'team': ['Mavs', 'Mavs', 'Mavs', 'Mavs', 'Heat', 'Heat', 'Heat', 'Heat'], 'position': ['Guard', 'Guard', 'Guard', 'Forward', 'Guard', 'Forward', 'Forward', 'Guard']}) #view DataFrame df team position 0 Mavs Guard 1 Mavs Guard 2 Mavs Guard 3 Mavs Forward 4 Heat Guard 5 Heat Forward 6 Heat Forward 7 Heat Guard
We can use the following syntax to count the number of unique combinations of team and position:
df[['team', 'position']].value_counts().reset_index(name='count') team position count 0 Mavs Guard 3 1 Heat Forward 2 2 Heat Guard 2 3 Mavs Forward 1
From the output we can see:
- There are 3 occurrences of the Mavs-Guard combination.
- There are 2 occurrences of the Heat-Forward combination.
- There are 2 occurrences of the Heat-Guard combination.
- There is 1 occurrence of the Mavs-Forward combination.
Note that you can also sort the results in order of count ascending or descending.
For example, we can use the following code to sort the results in order of count ascending:
df[['team', 'position']].value_counts(ascending=True).reset_index(name='count') team position count 0 Mavs Forward 1 1 Heat Forward 2 2 Heat Guard 2 3 Mavs Guard 3
The results are now sorted by count from smallest to largest.
Note: You can find the complete documentation for the pandas value_counts() function here.
Additional Resources
The following tutorials explain how to perform other common tasks in pandas:
Pandas: How to Use GroupBy and Value Counts
Pandas: How to Use GroupBy with Bin Counts
Pandas: How to Create Pivot Table with Count of Values