Pandas "count(distinct)" equivalent

Pandas 'count(distinct)' equivalent: Easy Solutions for Counting Unique Values

If you're using Pandas as a substitute for databases like Oracle or SQL Server, you may come across the need to count the number of distinct values in a column, just like the count(distinct) function in SQL. In this blog post, we'll explore common issues around this question and provide easy solutions using Pandas.

The Problem

Let's say you have a table loaded in a DataFrame with multiple columns: YEARMONTH, CLIENTCODE, SIZE, and so on. Your goal is to count the number of different clients per year, just like you would in SQL with the following query:

SELECT count(distinct CLIENTCODE) FROM table GROUP BY YEARMONTH;

The expected result would look something like:

201301    5000
201302    13245

Solution: Using Pandas for Counting Unique Values

To achieve the same result in Pandas, you can follow these steps:

Step 1: Group the DataFrame by YEARMONTH

Before counting the distinct values, you need to group the DataFrame by the YEARMONTH column. This can be done using the groupby() function in Pandas.

grouped_df = df.groupby('YEARMONTH')

Step 2: Apply the nunique() function

Now that you have the DataFrame grouped by YEARMONTH, you can apply the nunique() function to the CLIENTCODE column to count the number of distinct values.

result = grouped_df['CLIENTCODE'].nunique()

This will give you a Pandas Series with the distinct count of CLIENTCODE per YEARMONTH.

Step 3: Reset the index (optional)

By default, the resulting Series will have YEARMONTH as the index. If you prefer to have it as a column, you can reset the index using the reset_index() function.

result = result.reset_index()

Step 4: Rename the columns (optional)

To match the expected result shown earlier, you can rename the columns using the rename() function.

result = result.rename(columns={'CLIENTCODE': 'count(distinct CLIENTCODE)'})

Putting It All Together

Here's the complete code block that combines all the steps mentioned above:

grouped_df = df.groupby('YEARMONTH')
result = grouped_df['CLIENTCODE'].nunique().reset_index().rename(columns={'CLIENTCODE': 'count(distinct CLIENTCODE)'})

Now you can print or display the result DataFrame to see the desired output.

Get Counting with Pandas!

By following these simple steps, you can easily achieve the equivalent of count(distinct) functionality in Pandas. Don't let the absence of a dedicated function deter you from working with large datasets and making insightful analyses.

If you found this guide helpful or have any other Pandas-related questions, feel free to drop a comment below. Happy coding with Pandas! 😄🐼