CODEWITHSUNDEEP
pythonpandasdataframe
JejeBelfort
JejeBelfort

Reputation: 1663

Flag only first row where condition is met in a DataFrame

I have the following DataFrame df, which can be created as follows:

date_today = datetime.now().date()
days = pd.date_range(date_today, date_today + timedelta(19), freq='D')
x = np.arange(0,2*np.pi,0.1*np.pi)   # start,stop,step
y = np.sin(x)
df = pd.DataFrame({'dates': days, 'vals': y, 'is_hit': abs(y)>0.9})
df = df.set_index('dates')

And which looks like this:

            is_hit          vals
dates                           
2019-03-27   False  0.000000e+00
2019-03-28   False  3.090170e-01
2019-03-29   False  5.877853e-01
2019-03-30   False  8.090170e-01
2019-03-31    True  9.510565e-01
2019-04-01    True  1.000000e+00
2019-04-02    True  9.510565e-01
2019-04-03   False  8.090170e-01
2019-04-04   False  5.877853e-01
2019-04-05   False  3.090170e-01
2019-04-06   False  1.224647e-16
2019-04-07   False -3.090170e-01
2019-04-08   False -5.877853e-01
2019-04-09   False -8.090170e-01
2019-04-10    True -9.510565e-01
2019-04-11    True -1.000000e+00
2019-04-12    True -9.510565e-01
2019-04-13   False -8.090170e-01
2019-04-14   False -5.877853e-01
2019-04-15   False -3.090170e-01

I want to flag the rows where the is_hit condition is True for the first time, such that the expected new column hit_first would be:

           is_hit          vals  hit_first
dates                                      
2019-03-27   False  0.000000e+00      False
2019-03-28   False  3.090170e-01      False
2019-03-29   False  5.877853e-01      False
2019-03-30   False  8.090170e-01      False
2019-03-31    True  9.510565e-01       True
2019-04-01    True  1.000000e+00      False
2019-04-02    True  9.510565e-01      False
2019-04-03   False  8.090170e-01      False
2019-04-04   False  5.877853e-01      False
2019-04-05   False  3.090170e-01      False
2019-04-06   False  1.224647e-16      False
2019-04-07   False -3.090170e-01      False
2019-04-08   False -5.877853e-01      False
2019-04-09   False -8.090170e-01      False
2019-04-10    True -9.510565e-01       True
2019-04-11    True -1.000000e+00      False
2019-04-12    True -9.510565e-01      False
2019-04-13   False -8.090170e-01      False
2019-04-14   False -5.877853e-01      False
2019-04-15   False -3.090170e-01      False

How to create this hit_first column?

Upvotes: 9

Views: 1622

Answers (4)

Loochie
Loochie

Reputation: 2472

Also this can be done by using simple difference between the series and it's shifted series by 1 period :

df['hit_first'] = df['is_hit']-df['is_hit'].shift()==1

Upvotes: -1

Scott Boston
Scott Boston

Reputation: 153460

I also, think you can do it this way:

df['is_hit'].astype(int).diff() == 1

Output:

dates
2019-03-27    False
2019-03-28    False
2019-03-29    False
2019-03-30    False
2019-03-31     True
2019-04-01    False
2019-04-02    False
2019-04-03    False
2019-04-04    False
2019-04-05    False
2019-04-06    False
2019-04-07    False
2019-04-08    False
2019-04-09    False
2019-04-10     True
2019-04-11    False
2019-04-12    False
2019-04-13    False
2019-04-14    False
2019-04-15    False
Name: is_hit, dtype: bool

Timings:

%timeit df['is_hit'] & (~df['is_hit']).shift(1)
1.13 ms ± 5.63 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

%timeit df['is_hit'].ne(df['is_hit'].shift()) & df['is_hit']
908 µs ± 9.53 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

%timeit df['is_hit'].astype(int).diff() == 1
689 µs ± 8.24 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Upvotes: 3

jezrael
jezrael

Reputation: 862661

Use Series.shift chained with & for bitwise AND:

df['hit_first'] = df['is_hit'].ne(df['is_hit'].shift()) & df['is_hit']
print (df)
                    vals  is_hit  hit_first
dates                                      
2019-03-27  0.000000e+00   False      False
2019-03-28  3.090170e-01   False      False
2019-03-29  5.877853e-01   False      False
2019-03-30  8.090170e-01   False      False
2019-03-31  9.510565e-01    True       True
2019-04-01  1.000000e+00    True      False
2019-04-02  9.510565e-01    True      False
2019-04-03  8.090170e-01   False      False
2019-04-04  5.877853e-01   False      False
2019-04-05  3.090170e-01   False      False
2019-04-06  1.224647e-16   False      False
2019-04-07 -3.090170e-01   False      False
2019-04-08 -5.877853e-01   False      False
2019-04-09 -8.090170e-01   False      False
2019-04-10 -9.510565e-01    True       True
2019-04-11 -1.000000e+00    True      False
2019-04-12 -9.510565e-01    True      False
2019-04-13 -8.090170e-01   False      False
2019-04-14 -5.877853e-01   False      False
2019-04-15 -3.090170e-01   False      False

Upvotes: 3

ecortazar
ecortazar

Reputation: 1422

My suggestion:

df['hit_first'] = df['is_hit'] & (~df['is_hit']).shift(1)

Upvotes: 11

Related Questions