GroupBy and Count Unique Rows in Pandas   

In this short guide, we'll see how to use groupby() on several columns and count unique rows in Pandas.

Several examples will explain how to group by and apply statistical functions like: sum, count, mean etc. Often there is a need to group by a column and then get sum() and count().

To start, here is the syntax that we may apply in order to combine groupby and count in Pandas:

df.groupby(['publication', 'date_m'])['url'].count()

The DataFrame used in this article is available from Kaggle.

If you like to learn more about how to read Kaggle as a Pandas DataFrame check this article: How to Search and Download Kaggle Dataset to Pandas DataFrame

Step 1: Use groupby() and count() in Pandas

Let say that we would like to combine groupby and then get unique count per group.

In this first step we will count the number of unique publications per month from the DataFrame above.

First we need to convert date to month format - YYYY-MM with(learn more about it - Extract Month and Year from DateTime column in Pandas

df['date'] = pd.to_datetime(df['date'])
df['date_m'] = df['date'].dt.to_period('M')

and then we can group by two columns - 'publication', 'date_m' and count the URLs per each group:

df.groupby(['publication', 'date_m'])['url'].count()

this will result into:

publication    date_m
Better Humans  2019-03     5
               2019-04     4
               2019-05     4
               2019-06     1
               2019-07     3
                          ..
UX Collective  2019-08    24
               2019-09    33
               2019-10    86
               2019-11    28
               2019-12    46

An important note is that will compute the count of each group, excluding missing values.

If we like to count distinct values in Pandas - nunique() - check the linked article.

Step 2: groupby(), count() and sum() in Pandas

In Pandas method groupby will return object which is: <pandas.core.groupby.generic.DataFrameGroupBy object at 0x7f26bd45da20> - this can be checked by df.groupby(['publication', 'date_m']).

This kind of object has an agg function which can take a list of aggregation methods.

This is very useful if we need to check multiple statistics methods - sum(), count(), mean() per group.

So if we like to group by two columns publication and date_m - then to check next aggregation functions - mean, sum, and count we can use:

df.groupby(['publication', 'date_m']).agg(['mean', 'count', 'sum'])

we will get a MultiIndex as result:

Step 3: groupby() + count() + sort() = value_counts()

In the latest versions of pandas (>= 1.1) you can use value_counts in order to achieve behavior similar to groupby and count.

df.value_counts(['publication', 'date_m'])

will give us:

publication    date_m
The Startup    2019-05    656
               2019-10    477
               2019-07    401
               2019-12    310
               2019-06    286
                         ...
Better Humans  2019-07      3
UX Collective  2019-01      2
Better Humans  2019-12      2
               2019-11      2
               2019-06      1
Length: 79, dtype: int64

Note the code above is equivalent to:

df.groupby(['publication', 'date_m'])['url'].count().sort_values(ascending=False)

Step 4: Combine groupby() and size()

Alternative solution is to use groupby and size in order to count the elements per group in Pandas. The example below demonstrate the usage of size() + groupby():

df.groupby(['publication', 'date_m']).size()

result is a Pandas series like:

publication    date_m
Better Humans  2019-03     5
               2019-04     4
               2019-05     4
               2019-06     1
               2019-07     3
                          ..
UX Collective  2019-08    24
               2019-09    33
               2019-10    86
               2019-11    28
               2019-12    46
Length: 79, dtype: int64

Step 5: Combine groupby() and describe()

The final option is to use the method describe(). It will return statistical information which can be extremely useful like:

df.groupby(['publication', 'date_m'])['reading_time'].describe()

which will return additional stats like:

• count
• mean
• std
• percentile etc

Performance - groupby() & count() vs value_counts()

Finally lets do a quick comparison of performance between:

• groupby() and count()
• value_counts()

The next example will return equivalent results:

df.groupby(['publication', 'date_m'])['publication'].count()

df.value_counts(subset=['publication', 'date_m'], sort=False)

Below you can find the timings:

df.groupby

3.59 ms ± 24.1 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

df.value_counts

1.05 ms ± 4.73 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

df.value_counts with sort parameter

1.2 ms ± 11.2 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Conclusion and Resources

In this post we covered how to use groupby() and count unique rows in Pandas. How to sort results of groupby() and count().

Also we covered applying groupby() on multiple columns with multiple agg methods like sum(), min(), min().

Finally we saw how to use value_counts() in order to count unique values and sort the results.

The resources mentioned below will be extremely useful for further analysis:

• Notebook - 1.pandas-groupby-count
• pandas.DataFrame.groupby
• pandas.DataFrame.agg
• GroupBy.describe
• GroupBy.count
• pandas.DataFrame.value_counts