Count the amount of NaNs in each group

Question

In this dataframe, I'm trying to count how many NaN's there are for each color within the color column.

This is what the sample data looks like. In reality, there's 100k rows.

   color     value  
0  blue      10 
1  blue      NaN  
2  red       NaN
3  red       NaN
4  red       8
5  red       NaN
6  yellow    2

I'd like the output to look like this:

   color     count  
0  blue      1 
1  red       3
2  yellow    0

Answer 1

You can use DataFrame.isna, GroupBy the column color and sum to add up all True rows in each group:

df.value.isna().groupby(df.color).sum().reset_index()

    color  value
0    blue    1.0
1     red    3.0
2  yellow    0.0

Answer 2

Also you may use agg() and isnull() or isna() as follows:

df.groupby('color').agg({'value': lambda x: x.isnull().sum()}).reset_index()