How to Count Observations by Group in Pandas

---

Often you may be interested in counting the number of observations by group in a pandas DataFrame.

Fortunately this is easy to do using the groupby() and size() functions with the following syntax:

df.groupby('column_name').size()

This tutorial explains several examples of how to use this function in practice using the following data frame:

import numpy as np
import pandas as pd

#create pandas DataFrame
df = pd.DataFrame({'team': ['A', 'A', 'B', 'B', 'B', 'C', 'C'],
                   'division':['E', 'W', 'E', 'E', 'W', 'W', 'E'],
                   'rebounds': [11, 8, 7, 6, 6, 5, 12]})

#display DataFrame
print(df)

  team division  rebounds
0    A        E        11
1    A        W         8
2    B        E         7
3    B        E         6
4    B        W         6
5    C        W         5
6    C        E        12

Example 1: Count by One Variable

The following code shows how to count the total number of observations by team:

#count total observations by variable 'team'
df.groupby('team').size()

team
A    2
B    3
C    2
dtype: int64

From the output we can see that:

• Team A has 2 observations
• Team B has 3 observations
• Team C has 2 observations

Note that the previous code produces a Series. In most cases we want to work with a DataFrame, so we can use the reset_index() function to produce a DataFrame instead:

df.groupby('team').size().reset_index(name='obs')

        team	obs
0	A	2
1	B	3
2	C	2

Example 2: Count and Sort by One Variable

We can also use the sort_values() function to sort the group counts.

We can specify ascending=False to sort group counts from largest to smallest or ascending=True to sort from smallest to largest:

df.groupby('team').size().reset_index(name='obs').sort_values(['obs'], ascending=True)

        team	obs
0	A	2
2	C	2
1	B	3

Example 3: Count by Multiple Variables

We can also count the number of observations grouped by multiple variables in a pandas DataFrame:

#count observations grouped by team and division
df.groupby(['team', 'division']).size().reset_index(name='obs')

        team	division  obs
0	A	E	  1
1	A	W	  1
2	B	E	  2
3	B	W	  1
4	C	E	  1
5	C	W	  1

From the output we can see that:

• 1 observation belongs to Team A and division E
• 1 observation belongs to Team A and division W
• 2 observations belongs to Team B and division E
• 1 observation belongs to Team B and division W
• 1 observation belongs to Team C and division E
• 1 observation belongs to Team C and division W

Additional Resources

How to Calculate the Sum of Columns in Pandas
How to Calculate the Mean of Columns in Pandas
How to Find the Max Value of Columns in Pandas