Fill Na's in column with previous/next value +- 100

user_6852544 0
Viewed 65

I have a df column that looks like this:

**Price**
1000
NaN
NaN
NaN
1500
NaN

I want to fill the NaN's with previous value plus add to it 100. So it would look like this:

**Price**
    1000
    1100
    1200
    1300
    1500
    1600

I've tried this:

df['Price'] = df['Price'].fillna(df['Price'].ffill()+100)

And it works if there is only one missing value in between price values. I would like to have something that works with multiple missing values in between values that are present, also in case the first value in column is NaN I would like it to be filled by the result of deducting 100 from the next value. Thanks for your help, appreciate it.

pandas dataframe python-3.x python
1 Answers

Use GroupBy.cumcount by groups generated by testing not missing values with Series.cumsum, multiple 100 and add to forward filling misisng values:

s = df.groupby(df['Price'].notna().cumsum()).cumcount().mul(100)
df['Price'] = df['Price'].ffill() + s

print (df)
    Price
0  1000.0
1  1100.0
2  1200.0
3  1300.0
4  1500.0
5  1600.0
user_29010020
Related