Forward fill non na values with last observation carried forwards in Python

user_1770851 0
Viewed 115

Suppose I had a column in a dataframe like :

colname
Na
Na
Na
1
2
3
4
Na
Na
Na
Na
2
8
5
44
Na
Na

Does anyone know of a function to forward fill the Non NA values with the first value in the non na run? To produce :

colname
Na
Na
Na
1
1
1
1
Na
Na
Na
Na
2
2
2
2
Na
Na
pandas python
2 Answers

Use GroupBy.transform with GroupBy.first by compare values for missing values by Series.isna with cumulative sum by Series.cumsum, last correct NaNs by Series.where with Series.duplicated:

s = df['colNaNme'].isna().cumsum()
df['colNaNme'] = df.groupby(s)['colNaNme'].transform('first').where(s.duplicated())
print (df)
    colNaNme
0        NaN
1        NaN
2        NaN
3        1.0
4        1.0
5        1.0
6        1.0
7        NaN
8        NaN
9        NaN
10       NaN
11       2.0
12       2.0
13       2.0
14       2.0
15       NaN
16       NaN

Or filter only non missing values by invert mask m and processing only these groups:

m = df['colNaNme'].isna()
df.loc[~m, 'colNaNme'] = df[~m].groupby(m.cumsum())['colNaNme'].transform('first')
print (df)
    colNaNme
0        NaN
1        NaN
2        NaN
3        1.0
4        1.0
5        1.0
6        1.0
7        NaN
8        NaN
9        NaN
10       NaN
11       2.0
12       2.0
13       2.0
14       2.0
15       NaN
16       NaN

Solution with non groupby:

m = df['colNaNme'].isna()
m1 = m.cumsum().shift().bfill()
m2 = ~m1.duplicated() & m.duplicated(keep=False)    

df['colNaNme'] = df['colNaNme'].where(m2).ffill().mask(m)
print (df)
    colNaNme
0        NaN
1        NaN
2        NaN
3        1.0
4        1.0
5        1.0
6        1.0
7        NaN
8        NaN
9        NaN
10       NaN
11       2.0
12       2.0
13       2.0
14       2.0
15       NaN
16       NaN
user_29010020

You could try groupby and cumsum with shift and transform('first'):

>>> df.groupby(df['colname'].isna().ne(df['colname'].isna().shift()).cumsum()).transform('first')
   colname
0      NaN
1      NaN
2      NaN
3        1
4        1
5        1
6        1
7      NaN
8      NaN
9      NaN
10     NaN
11       2
12       2
13       2
14       2
15     NaN
16     NaN
>>>

Or try something like:

>>> x = df.groupby(df['colname'].isna().cumsum()).transform('first')
>>> x.loc[~x.duplicated()] = np.nan
>>> x
   colname
0      NaN
1      NaN
2      NaN
3        1
4        1
5        1
6        1
7      NaN
8      NaN
9      NaN
10     NaN
11       2
12       2
13       2
14       2
15     NaN
16     NaN
>>>
user_87083640
Related