Vectorized method find value in dataframe and then use comparison of previous row's value of same column

Jonathan Stearns 0
Viewed 13

Sample Data

Siutation 1:

DateTime         Value1   Value2
8/15/2022 10:47  0.00151    0
8/15/2022 10:48  0.0016     1
8/15/2022 10:49  0.00168    1

Situation 2:

DateTime         Value1   Value2
8/16/2022 13:38  0.00163    1
8/16/2022 13:39  0.0016     0
8/16/2022 13:40  0.00157    0

What I am trying to do:

  • Find where Value1 == 0.0016
  • If Value1.shift(1) <= Value1, then change Value2 to 0
  • If Value1.shift(1) > Value1, then change Value2 to 1

I can do this with lambda or loops. But I want a way to vectorize the code as there are many rows.

I tried:

df['Value3'] = df['Value1'].diff()
df['Value2'] = np.where((df['Value1'] == 0.0016) & (df['Value3'] <= 0 ),0,
               np.where((df['Value1'] == 0.0016) & (df['Value3'] >  0 ),1,np.nan))

I also Tried:

df['Value2'] = np.where((df['Value1'] == 0.0016) & (df['Value1'].shift(1) <= 0.0016 ),0,
               np.where((df['Value1'] == 0.0016) & (df['Value1'].shift(1) >  0.0016 ),1,np.nan))

But as you know, neither approach worked. Both resulted in all rows being 'nan'.

I appreciate your help.

vectorization dataframe pandas python
1 Answers

Easy with loc and masks:

mask1 = df.Value1==0.0016
mask2 = df.Value1.shift(1) > df.Value1

df.loc[mask1 & mask2, 'Value2'] = 1
df.loc[mask1 & ~mask2, 'Value2'] = 0
Josh Friedlander0
Related