> ## Content Index
> Fetch the complete content index at: https://datascientyst.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# GroupBy and Count Unique Rows in Pandas
- URL: https://datascientyst.com/pandas-groupby-count/
- Published: 2021-08-17T19:06:14.000Z
- Updated: 2022-03-24T07:00:22.000Z
- Author: John D K
- Tags: groupby()

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()`.

× **Pro Tip 1**  
It's recommended to use method **df.value\_counts** for counting the size of groups in Pandas. It's a bit faster and support parameter \`dropna\` since Pandas 1.3 

× **Pro Tip 2**  
Sorting the results of **groupby/count** or **value\_counts** will slow the process with roughly 20% 

× **Warning**  
Be careful of counting NaN values. They can change the expected results and counts. For **value\_counts** use parameter **dropna=True** to count with NaN values. 

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

```python
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](https://datascientyst.com/search-download-kaggle-dataset-pandas-dataframe/)

| url                                                                                                               | date       | publication          |
| ----------------------------------------------------------------------------------------------------------------- | ---------- | -------------------- |
| https://towardsdatascience.com/a-beginners-guide-to-word-embedding-with-gensim-word2vec-model-5970fa56cc92        | 2019-05-30 | Towards Data Science |
| https://towardsdatascience.com/hands-on-graph-neural-networks-with-pytorch-pytorch-geometric-359487e221a8         | 2019-05-30 | Towards Data Science |
| https://towardsdatascience.com/how-to-use-ggplot2-in-python-74ab8adec129                                          | 2019-05-30 | Towards Data Science |
| https://towardsdatascience.com/databricks-how-to-save-files-in-csv-on-your-local-computer-3d0c70e6a9ab            | 2019-05-30 | Towards Data Science |
| https://towardsdatascience.com/a-step-by-step-implementation-of-gradient-descent-and-backpropagation-d58bda486110 | 2019-05-30 | Towards Data Science |

## 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](https://datascientyst.com/extract-month-and-year-datetime-column-in-pandas/)

```python
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**:

```python
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()](https://datascientyst.com/ghost/#/editor/post/61821ebfc41e2d0410d3ddf4) \- 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:

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

```

we will get a MultiIndex as result:

|                  |           | reading\_time |       |     |
| ---------------- | --------- | ------------- | ----- | --- |
|                  |           | mean          | count | sum |
| publication      | date\_m   |               |       |     |
| Better Humans    | 2019-03   | 12.000000     | 5     | 60  |
| 2019-04          | 7.500000  | 4             | 30    |     |
| 2019-05          | 25.000000 | 4             | 100   |     |
| 2019-06          | 5.000000  | 1             | 5     |     |
| 2019-07          | 22.333333 | 3             | 67    |     |
| 2019-09          | 12.000000 | 3             | 36    |     |
| 2019-10          | 9.750000  | 4             | 39    |     |
| 2019-11          | 9.000000  | 2             | 18    |     |
| 2019-12          | 9.500000  | 2             | 19    |     |
| Better Marketing | 2019-03   | 9.600000      | 5     | 48  |
| 2019-04          | 5.500000  | 4             | 22    |     |
| 2019-05          | 6.529412  | 34            | 222   |     |
| 2019-06          | 7.000000  | 15            | 105   |     |
| 2019-07          | 6.106383  | 47            | 287   |     |
| 2019-08          | 5.880000  | 25            | 147   |     |

## 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`.**

```python
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:

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

```

**Note:** 

Which solution is better depends on the data and the context. In the end of the post there is a performance comparison of both methods.

## 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()`:

```python
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:

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

```

which will return additional stats like:

- `count`
- `mean`
- `std`
- percentile etc

|                  |         | count     | mean      | std      | min   | 25%   | 50%   | 75%   | max  |
| ---------------- | ------- | --------- | --------- | -------- | ----- | ----- | ----- | ----- | ---- |
| publication      | date\_m |           |           |          |       |       |       |       |      |
| Better Humans    | 2019-03 | 5.0       | 12.000000 | 5.196152 | 3.0   | 13.00 | 13.0  | 15.00 | 16.0 |
| 2019-04          | 4.0     | 7.500000  | 5.196152  | 2.0      | 4.25  | 7.0   | 10.25 | 14.0  |      |
| 2019-05          | 4.0     | 25.000000 | 7.958224  | 17.0     | 21.50 | 23.5  | 27.00 | 36.0  |      |
| 2019-06          | 1.0     | 5.000000  | NaN       | 5.0      | 5.00  | 5.0   | 5.00  | 5.0   |      |
| 2019-07          | 3.0     | 22.333333 | 15.307950 | 13.0     | 13.50 | 14.0  | 27.00 | 40.0  |      |
| 2019-09          | 3.0     | 12.000000 | 4.582576  | 8.0      | 9.50  | 11.0  | 14.00 | 17.0  |      |
| 2019-10          | 4.0     | 9.750000  | 5.123475  | 3.0      | 7.50  | 10.5  | 12.75 | 15.0  |      |
| 2019-11          | 2.0     | 9.000000  | 1.414214  | 8.0      | 8.50  | 9.0   | 9.50  | 10.0  |      |
| 2019-12          | 2.0     | 9.500000  | 2.121320  | 8.0      | 8.75  | 9.5   | 10.25 | 11.0  |      |
| Better Marketing | 2019-03 | 5.0       | 9.600000  | 8.080842 | 5.0   | 6.00  | 6.0   | 7.00  | 24.0 |
| 2019-04          | 4.0     | 5.500000  | 1.000000  | 5.0      | 5.00  | 5.0   | 5.50  | 7.0   |      |
| 2019-05          | 34.0    | 6.529412  | 4.039467  | 2.0      | 5.00  | 5.0   | 7.00  | 20.0  |      |
| 2019-06          | 15.0    | 7.000000  | 2.725541  | 3.0      | 5.50  | 6.0   | 8.50  | 13.0  |      |
| 2019-07          | 47.0    | 6.106383  | 2.664044  | 3.0      | 4.00  | 5.0   | 7.00  | 13.0  |      |
| 2019-08          | 25.0    | 5.880000  | 2.818392  | 3.0      | 4.00  | 5.0   | 6.00  | 15.0  |      |

## 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:

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

```

```python
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](https://github.com/softhints/datascientyst/blob/master/groupby/1.pandas-groupby-count.ipynb?ref=datascientyst.com)
- [pandas.DataFrame.groupby](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html?highlight=groupby&ref=datascientyst.com)
- [pandas.DataFrame.agg](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.agg.html?ref=datascientyst.com)
- [GroupBy.describe](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.DataFrameGroupBy.describe.html?ref=datascientyst.com)
- [GroupBy.count](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.DataFrameGroupBy.count.html?ref=datascientyst.com)
- [pandas.DataFrame.value\_counts](https://pandas.pydata.org/pandas-docs/dev/reference/api/pandas.DataFrame.value%5Fcounts.html?ref=datascientyst.com)