Fast way to get the number of NaNs in a column counted from the last valid value in a DataFrame

Question

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  
7   0.0835  1.400  
8   0.0418  NaN    
9   0.0209  NaN    
10  NaN     NaN    
11  NaN     NaN    
12  NaN     NaN

I want a new DataFrame of the same shape where each entry represents the number of NaNs counted up to its position started from the last valid value as follows

    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

I wonder if this can be done efficiently by utilizing some of the Pandas/Numpy functions?

@Dadep I can only do it with double loops and I do not really think I am fast enough. — Warren, Apr 20 '17 at 11:21
How many columns do you have typically? How many rows are there typically? — Divakar, Apr 20 '17 at 12:03

jezrael · Accepted Answer · 2017-04-20T12:01:51.573

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

Fast way to get the number of NaNs in a column counted from the last valid value in a DataFrame

1 Answers1

Linked