CODEWITHSUNDEEP
pythonpandas
smulard
smulard

Reputation: 139

Count the amount of NaNs in each group

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

Upvotes: 5

Views: 1254

Answers (4)

BENY
BENY

Reputation: 323356

A usage from size and count 

g=df.groupby('color')['value']
g.size()-g.count()
Out[115]: 
color
blue      1
red       3
yellow    0
Name: value, dtype: int64

Upvotes: 1

Loochie
Loochie

Reputation: 2472

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

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

Upvotes: 2

yatu
yatu

Reputation: 88285

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

Upvotes: 7

Vaishali
Vaishali

Reputation: 38415

Use isna().sum()

df.groupby('color').value.apply(lambda x: x.isna().sum())

color
blue      1
red       3
yellow    0

Upvotes: 2

Related Questions