I have a float column in a dataframe. And I want to add another boolean column which will be True if condition satisfies on two consecutive values till another condition satisfies on next two consecutive values.
For Example I have a data-frame which look like this:
| index | Values % |
|---|---|
| 0 | 0 |
| 1 | 5 |
| 2 | 11 |
| 3 | 9 |
| 4 | 14 |
| 5 | 18 |
| 6 | 30 |
| 7 | 54 |
| 8 | 73 |
| 9 | 100 |
| 10 | 100 |
| 11 | 100 |
| 12 | 100 |
| 13 | 100 |
Now I want to mark True from where two consecutive values satisfies the condition df['Values %'] >= 10 till next two consecutive values satisfies the next condition i.e. df[Values %] == 100.
So the final result will look like something this:
| index | Values % | Flag |
|---|---|---|
| 0 | 0 | False |
| 1 | 5 | False |
| 2 | 11 | False |
| 3 | 9 | False |
| 4 | 14 | False |
| 5 | 18 | True |
| 6 | 30 | True |
| 7 | 54 | True |
| 8 | 73 | True |
| 9 | 100 | True |
| 10 | 100 | True |
| 11 | 100 | False |
| 12 | 100 | False |
| 13 | 100 | False |
You can use:
# give 2 consecutive values ≥ 10
m1 = df['Values %'].ge(10).rolling(2).min().eq(1)
# give 2 consecutive 100
m2 = df['Values %'].eq(100).rolling(2).min().eq(1)
# get values before (including) first m2 True
m3 = (~m2).shift(fill_value=True).cummin()
# get values between the first m1 True
df['Flag'] = m1.cummax() & m3
Output:
index Values % Flag
0 0 0 False
1 1 5 False
2 2 11 False
3 3 9 False
4 4 14 False
5 5 18 True
6 6 30 True
7 7 54 True
8 8 73 True
9 9 100 True
10 10 100 True
11 11 100 False
12 12 100 False
13 13 100 False
If you need the logic repeated on several groups:
# give 2 consecutive values ≥ 10
m1 = df['Values %'].ge(10).rolling(2).min().eq(1)
# give 2 consecutive 100
m2 = df['Values %'].eq(100).rolling(2).min().eq(1)
# set up grouper
group = (m2.shift()&~m2).cumsum()
# get values before (including) first m2 True
m3 = ((~m2).groupby(group, group_keys=False)
.apply(lambda x: x.shift(fill_value=True).cummin())
)
# get values between the first m1 True
df['Flag'] = m1.groupby(group).cummax() & m3
Output:
index Values % Flag
0 0 0 False
1 1 5 False
2 2 11 False
3 3 9 False
4 4 14 False
5 5 18 True
6 6 30 True
7 7 54 True
8 8 73 True
9 9 100 True
10 10 100 True
11 11 100 False
12 12 100 False
13 13 100 False
14 0 0 False
15 1 5 False
16 2 11 False
17 3 9 False
18 4 14 False
19 5 18 True
20 6 30 True
21 7 54 True
22 8 73 True
23 9 100 True
24 10 100 True
25 11 100 False
26 12 100 False
27 13 100 False
Intermediates:
index Values % Flag m1 m2 group m3
0 0 0 False False False 0 True
1 1 5 False False False 0 True
2 2 11 False False False 0 True
3 3 9 False False False 0 True
4 4 14 False False False 0 True
5 5 18 True True False 0 True
6 6 30 True True False 0 True
7 7 54 True True False 0 True
8 8 73 True True False 0 True
9 9 100 True True False 0 True
10 10 100 True True True 0 True
11 11 100 False True True 0 False
12 12 100 False True True 0 False
13 13 100 False True True 0 False
14 0 0 False False False 1 True
15 1 5 False False False 1 True
16 2 11 False False False 1 True
17 3 9 False False False 1 True
18 4 14 False False False 1 True
19 5 18 True True False 1 True
20 6 30 True True False 1 True
21 7 54 True True False 1 True
22 8 73 True True False 1 True
23 9 100 True True False 1 True
24 10 100 True True True 1 True
25 11 100 False True True 1 False
26 12 100 False True True 1 False
27 13 100 False True True 1 False