I have a dataframe like

Index  Value
0        5
1        Nan
2        6
3        16
4        23
5        32
6        33
7        34

if the difference with the previous row is less than 2, the current row value has to be equal to previous row only.

So I want it like

Index  Value
0        5
1        Nan
2        6
3        16
4        23
5        32
6        32
7        32
I have tried : 

test = np.where(df.Value.notnull() & df.Value.shift(-1).notnull() & ((df.Value - df.Value.shift(-1)) < 2),df.Value.shift(-1),df.Value)

its not giving the desired output, where am i going wrong?

Edit:

Hi, i have a small modifocation in my requiremement. If the previous value is null, i want it to be compared with the before non-null value and calculate the difference. So in this case i want it to be

Index  Value
0        5
1        Nan
2        5
4        23
5        32
6        32
7        32

Index-2 value should be 5 only not 6

1 Answers

3
Wen-Ben On Best Solutions

One way using diff and cumsum create the groupkey , then we using transform

s=df.Value.diff()
df.groupby((s.isnull()|s.gt(2)).cumsum()).Value.transform('first')
0     5.0
1     NaN
2     6.0
3    16.0
4    23.0
5    32.0
6    32.0
7    32.0
Name: Value, dtype: float64

Update hehe

s=df.Value.diff()
print(df.groupby((s.gt(2)).cumsum()).Value.transform('first').mask(df.Value.isnull()))
0     5.0
1     NaN
2     5.0
3    16.0
4    23.0
5    32.0
6    32.0
7    32.0
Name: Value, dtype: float64