CODEWITHSUNDEEP
pythonpandasdataframeconditional-statements
Chris Bearden
Chris Bearden

Reputation: 13

how can I calculate new column based on pandas conditions of multiple previous values

I have the following dataframe. How can I create a new column based on the following: if, for example, the row of 19:24 has a diff < .1 AND the row of 19:21 has a diff of < .1 then create new column and set row equal to say 1

I know how I can do these with if statements, but i'm stuck on trying to use a pandas (more efficient) solution for this.

                    open     high      low    close      diff                   
TimeStamp                                                                           
2018-10-05 19:00:00  6637.90  6645.06  6637.17  6643.52 -0.057705                   
2018-10-05 19:03:00  6643.52  6650.00  6641.00  6649.99  0.002710                   
2018-10-05 19:06:00  6648.91  6650.00  6640.82  6642.85 -0.017769                   
2018-10-05 19:09:00  6642.85  6646.47  6642.00  6646.20  0.000000                   
2018-10-05 19:12:00  6646.20  6646.21  6642.00  6642.40  0.085065                   
2018-10-05 19:15:00  6641.00  6641.84  6636.35  6638.54 -0.024863                   
2018-10-05 19:18:00  6639.26  6643.38  6638.00  6640.00 -0.067340                   
2018-10-05 19:21:00  6642.49  6643.82  6642.47  6643.63  0.104931                   
2018-10-05 19:24:00  6643.78  6645.31  6635.50  6640.02 -0.031497

Upvotes: 1

Views: 89

Answers (1)

jpp
jpp

Reputation: 164663

You can use pd.Series.shift, and the & operator to combine two Boolean series.

This will set flag = 1 for every instance where diff < 0.1 on a row where that row and the preceding row both satisfy the condition.

df['flag'] = (df['diff'].lt(0.1) & df['diff'].shift().lt(0.1)).astype(int)

Possibly more efficient would be to use shift on a single comparison:

s = df['diff'].lt(0.1)
df['flag'] = (s & s.shift()).astype(int)

Result:

print(df)

                        open     high      low    close      diff  flag
TimeStamp                                                              
2018-10-05 19:00:00  6637.90  6645.06  6637.17  6643.52 -0.057705     0
2018-10-05 19:03:00  6643.52  6650.00  6641.00  6649.99  0.002710     1
2018-10-05 19:06:00  6648.91  6650.00  6640.82  6642.85 -0.017769     1
2018-10-05 19:09:00  6642.85  6646.47  6642.00  6646.20  0.000000     1
2018-10-05 19:12:00  6646.20  6646.21  6642.00  6642.40  0.085065     1
2018-10-05 19:15:00  6641.00  6641.84  6636.35  6638.54 -0.024863     1
2018-10-05 19:18:00  6639.26  6643.38  6638.00  6640.00 -0.067340     1
2018-10-05 19:21:00  6642.49  6643.82  6642.47  6643.63  0.104931     0
2018-10-05 19:24:00  6643.78  6645.31  6635.50  6640.02 -0.031497     0

Upvotes: 2

Related Questions