Count number of np.nan in a Pandas dataframe

user_8430396 0
Viewed 350

I have a Pandas dataframe containing np.nan (Numpy not-a-number) values:

            field1
2020-12-24  NaN
2020-12-25  NaN
2020-12-26  1.0
2020-12-27  2.0
2020-12-28  NaN
2020-12-29  1.0
2020-12-30  2.0

(The index is a datetime.) I would like to obtain a new dataframe with the start date and the number of times np.nan occurs, i.e.

            field1
2020-12-24  2
2020-12-28  1

I have tried this code:

prev = 1
for col_name, el in df.iterrows():
    print(el)
    if prev != np.nan and el[0] == np.nan:
        cnt = 1
    if prev == np.nan and el[0] == np.nan:
        cnt = cnt + 1
    if prev == np.nan and el[0] != np.nan:
        print(cnt)
    prev = el[0]

But it doesn't work as expected, and moreover I want to avoid "for" loops because I expect them to be very slow on bigger dataframes. Any help would be appreciated!

pandas dataframe numpy python
2 Answers

You can create groups by test non missing values by Series.notna with Series.cumsum and then filter only NaNs rows, then get counts by Series.map and Series.value_counts and filter first duplicated rows by Series.duplicated:

m = df['field1'].notna()
s = m.cumsum()[~m]

df1 = s.map(s.value_counts())[~s.duplicated()].to_frame()
print (df1)
            field1
2020-12-24       2
2020-12-28       1
user_29010020

You can use the sum of isna() on the column:

field1.isna().sum()

This is equivalent to taking the length of the column minus the count (which will exclude NaN's)

len(df'field1') - df.count()

So you are thus able to count all the Nan's in the database with the following command:

len(df) - df.count()

For rows you can use axis=1:

df.isna().sum(axis=1)
user_75839530
Related