df.groupby('var1')['var2'].apply(lambda x: (x=='val').sum()).reset_index(name='count')
This particular syntax groups the rows of the DataFrame based on var1 and then counts the number of rows where var2 is equal to ‘val.’
The following example shows how to use this syntax in practice.
Example: Groupby and Count with Condition in Pandas
Suppose we have the following pandas DataFrame that contains information about various basketball players:
import pandas as pd
#create DataFrame
df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
'pos': ['Gu', 'Fo', 'Fo', 'Fo', 'Gu', 'Gu', 'Fo', 'Fo'],
'points': [18, 22, 19, 14, 14, 11, 20, 28]})
#view DataFrame
print(df)
team pos points
0 A Gu 18
1 A Fo 22
2 A Fo 19
3 A Fo 14
4 B Gu 14
5 B Gu 11
6 B Fo 20
7 B Fo 28
The following code shows how to group the DataFrame by the team variable and count the number of rows where the pos variable is equal to ‘Gu’:
#groupby team and count number of 'pos' equal to 'Gu'
df_count = df.groupby('team')['pos'].apply(lambda x: (x=='Gu').sum()).reset_index(name='count')
#view results
print(df_count)
team count
0 A 1
1 B 2
From the output we can see:
Team A has 1 row where the pos column is equal to ‘Gu’
Team B has 2 rows where the pos column is equal to ‘Gu’
We can use similar syntax to perform a groupby and count with some numerical condition.
For example, the following code shows how to group by the team variable and count the number of rows where the points variable is greater than 15:
#groupby team and count number of 'points' greater than 15
df_count = df.groupby('team')['points'].apply(lambda x: (x>15).sum()).reset_index(name='count')
#view results
print(df_count)
team count
0 A 3
1 B 2
From the output we can see:
Team A has 3 rows where the points column is greater than 15
Team B has 2 rows where the points column is greater than 15
You can use similar syntax to perform a groupby and count with any specific condition you’d like.
Additional Resources
The following tutorials explain how to perform other common tasks in pandas:
How to Count Unique Values Using Pandas GroupBy
How to Apply Function to Pandas Groupby
How to Create Bar Plot from Pandas GroupBy
Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike. My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.
Hi, thanks for this post.
Is there a way that I can add another column to this dataframe, like you created ‘count’ where the points greater than 15 are shown. What if I want to add another column in the same dataframe, named ‘average’ where it shows the average per team.
Thanks
