SQL Equivalent count(distinct) in Pandas - nunique

In this post, we will see how to count distinct values in Pandas.

The SQL's Equivalent of count(distinct) is method nunique(). It will count distinct values per group in Pandas DataFrame:

(1) Pandas method nunique() and `groupby()

df.groupby('Magnitude Type')['Date'].nunique()

(2) Pandas count distinct - nunique() + agg()

df.groupby(['Magnitude Type'])['Date'].agg(['count', 'nunique'])

The above solution will group by column 'Magnitude Type' and will count all unique values for column 'Date'.

If you need to learn more about Pandas and SQL you can visit this cheat sheet: Pandas vs SQL Cheat Sheet.

Let's cover the above in more detail. Suppose we have DataFrame like:

Step 1: nunique() and groupby() - SQL's equivalent to count(distinct) in Pandas

If we like to group by a column and then get unique values for another column in Pandas you can use the method: .nunique().

The syntax is simple:

df.groupby('Magnitude Type')['Date'].nunique()

the result is similar to the SQL query:

SELECT count(distinct Date) FROM earthquakes GROUP BY `Magnitude Type`;

Magnitude Type
MB     2474
MD        5
MH        4
ML       60
MS     1316
MW     4665
MWB    2036
MWC    3458
MWR      18
MWW    1256
Name: Date, dtype: int64

df['Magnitude Type'].value_counts(dropna=False)

result:

MW     7722
MWC    5669
MB     3761
MWB    2458
MWW    1983
MS     1702
ML       77
MWR      26
MD        6
MH        5
NaN       3
Name: Magnitude Type, dtype: int64

Step 2: nunique() and agg() in Pandas

If we like to get all unique values in each group plus information like:

• min
• max
• count
• mean

You can modify the upper query a bit:

df.groupby(['Magnitude Type'])['Date'].agg(['count', 'nunique'])

This will result into:

As you can see for Magnitude Type = MH there are 4 unique Dates but 5 records:

Because there are 2 records for date: 02/09/1971.

Step 3: Pandas count distinct multiple columns

To count number of unique values for multiple columns in Pandas there are two options:

• Combine agg() + nunique()
• Pandas method crosstab()

Count distinct with agg() + nunique()

First one is using the same approach as above:

df.groupby('Magnitude Type').agg({'Date': ['nunique', 'count'],
                                      'Depth': ['nunique', 'count']})

result:

Count distinct in Pandas with crosstab()

Usually Pandas provide multiple ways of achieving something. In this example we are going to use the method crosstab.

It might be slower than the other but you may have additional useful information for more insights.

So first let's do a simple demo of crosstab:

pd.crosstab(df['Magnitude Type'], df['Date'])

This will give a table which is showing information for each Date and Magnitude Type:

And then we can summarize the information into unique values by using .ne(0).sum(1) :

pd.crosstab(df['Magnitude Type'], df['Date']).ne(0).sum(1)

result:

Magnitude Type
MB     2474
MD        5
MH        4
ML       60
MS     1316
MW     4665
MWB    2036
MWC    3458
MWR      18
MWW    1256
dtype: int64

Step 4: Count distinct with condition in Pandas

Finally we have an option of using a lambda in order to count the unique values with condition.

This will be useful if you like to apply conditions on the count - for example excluding some records.

df.groupby('Magnitude Type')['Date'].apply(lambda x: x.unique().shape[0])

For example example excluding groups smaller than 1000:

df.groupby('Magnitude Type')['Date'].apply(lambda x: x.unique().shape[0] if x.unique().shape[0] > 1000 else None).dropna()

Resources

• Notebook
• pandas.DataFrame.nunique
• pandas.DataFrame.ne
• pandas.DataFrame.agg