Groupby class and count missing values in features

  • A+

I have a problem and I cannot find any solution in the web or documentation, even if I think that it is very trivial.

What do I want to do?

I have a dataframe like this

CLASS FEATURE1 FEATURE2 FEATURE3   X      A       NaN      NaN   X     NaN       A       NaN   B      A        A        A 

I want to group by the label(CLASS) and display the number of NaN-Values that are counted in every feature so that it looks like this. The purpose of this is to get a general idea how missing values are distributed over the different classes.

CLASS FEATURE1 FEATURE2 FEATURE3   X      1        1        2   B      0        0        0 

I know how to recieve the amount of nonnull-Values - df.groupby['CLASS'].count()

Is there something similar for the NaN-Values?

I tried to subtract the count() from the size() but it returned an unformatted output filled with the value NaN


Compute a mask with isna, then group and find the sum:

df.drop('CLASS', 1).isna().groupby(df.CLASS, sort=False).sum().reset_index()    CLASS  FEATURE1  FEATURE2  FEATURE3 0     X       1.0       1.0       2.0 1     B       0.0       0.0       0.0 

Another option is to subtract the size from the count using rsub along the 0th axis for index aligned subtraction:

df.groupby('CLASS').count().rsub(df.groupby('CLASS').size(), axis=0) 


g = df.groupby('CLASS') g.count().rsub(g.size(), axis=0) 

       FEATURE1  FEATURE2  FEATURE3 CLASS                               B             0         0         0 X             1         1         2 

There are quite a few good answers, so here are some timeits for your perusal:

df_ = df df = pd.concat([df_] * 10000)  %timeit df.drop('CLASS', 1).isna().groupby(df.CLASS, sort=False).sum() %timeit df.set_index('CLASS').isna().sum(level=0)     %%timeit g = df.groupby('CLASS') g.count().rsub(g.size(), axis=0)  11.8 ms ± 108 µs per loop (mean ± std. dev. of 7 runs, 100 loops each) 9.47 ms ± 379 µs per loop (mean ± std. dev. of 7 runs, 100 loops each) 6.54 ms ± 81.6 µs per loop (mean ± std. dev. of 7 runs, 100 loops each) 

Actual performance depends on your data and setup, so your mileage may vary.


:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: