Fast Way To Get The Number Of NaNs In A Column Counted From The Last Valid Value In A DataFrame

Say I have a DataFrame like A B 0 0.1880 0.345 1 0.2510 0.585 2 NaN NaN 3 NaN NaN 4 NaN 1.150 5 0.2300 1.210 6 0.1670 1.290

Solution 1:

You can use:

a = df.isnull()
b = a.cumsum()
df1 = b.sub(b.mask(a).ffill().fillna(0).astype(int))
print (df1)
    A  B
0   0  0
1   0  0
2   1  1
3   2  2
4   3  0
5   0  0
6   0  0
7   0  0
8   0  1
9   0  2
10  1  3
11  2  4
12  3  5

For better understanding:

#add NaN where True in a
a2 = b.mask(a)
#forward filling NaN
a3 = b.mask(a).ffill()
#replace NaN to 0, cast to int
a4 = b.mask(a).ffill().fillna(0).astype(int)
#substract b to a4
a5 = b.sub(b.mask(a).ffill().fillna(0).astype(int))
df1 = pd.concat([a,b,a2, a3, a4, a5], axis=1, 
                keys=['a','b','where','ffill nan','substract','output'])
print (df1)
        a         b    where      ffill nan      substract    output   
        A      B  A  B     A    B         A    B         A  B      A  B
0   False  False  0  0   0.0  0.0       0.0  0.0         0  0      0  0
1   False  False  0  0   0.0  0.0       0.0  0.0         0  0      0  0
2    True   True  1  1   NaN  NaN       0.0  0.0         0  0      1  1
3    True   True  2  2   NaN  NaN       0.0  0.0         0  0      2  2
4    True  False  3  2   NaN  2.0       0.0  2.0         0  2      3  0
5   False  False  3  2   3.0  2.0       3.0  2.0         3  2      0  0
6   False  False  3  2   3.0  2.0       3.0  2.0         3  2      0  0
7   False  False  3  2   3.0  2.0       3.0  2.0         3  2      0  0
8   False   True  3  3   3.0  NaN       3.0  2.0         3  2      0  1
9   False   True  3  4   3.0  NaN       3.0  2.0         3  2      0  2
10   True   True  4  5   NaN  NaN       3.0  2.0         3  2      1  3
11   True   True  5  6   NaN  NaN       3.0  2.0         3  2      2  4
12   True   True  6  7   NaN  NaN       3.0  2.0         3  2      3  5