Fill na values by adding x to previous row pandas

Question

I have a data frame with a column named SAM with following data

SAM
3
5
9
Nan
Nan
24
40
Nan
57

Now I want to Insert 12, 15 and 43 respectively in the Nan values (because 9+3=12, 12+3=15, and 40+3=43). In other words, fill any Nan row by adding 3 to previous row (which can also be Nan).

I know this can be done by iterating through a for loop. But can we do it in a vectorized manner? Like some modified version of ffill (which could have been used here if we didn't have consecutive NaNs) in pandas.fillna().

Answer 1

You can try this vectorized approach:

nul = df['SAM'].isnull()
nul.groupby((nul.diff() == 1).cumsum()).cumsum()*3 + df['SAM'].ffill()

#0     3.0
#1     5.0
#2     9.0
#3    12.0
#4    15.0
#5    24.0
#6    40.0
#7    43.0
#8    57.0
#Name: SAM, dtype: float64

1. Divide the missing values in the series into chunks and add 3,6,9 etc to the missing value positions depending on the length of each chunk;
2. Add the forward filled values from SAM column to the result.