Pandas: How to Count Unique Combinations of Two Columns


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

Leave a Reply

Your email address will not be published. Required fields are marked *