I have dataframe:
one N th
0 A 5 1
1 Z 17 0
2 A 16 0
3 B 9 1
4 B 17 0
5 B 117 1
6 XC 35 1
7 C 85 0
8 Ce 965 1
I'm looking the way to keep alternating 0101 in column three without doubling 0 or 1. So, i want to delete row with min of values in case if i have two repeating 0 in column th and max values if i have repeating 1.
My base consis of 1000000 rows.
I expect to have dataframe like this:
one N th
0 A 5 1
1 Z 17 0
3 B 9 1
4 B 17 0
6 XC 35 1
7 C 85 0
8 Ce 965 1
What is the fastest way to do it. I mean vectorized way. My attempts without result.
groupby.idxmaxYou can swap the sign if "th" is 1 (to get the max instead of min), then set up a custom grouper (with diff or shift + cumsum) and perform a groupby.idxmax to select the rows to keep:
out = df.loc[df['N'].mul(df['th'].map({0: 1, 1: -1}))
.groupby(df['th'].ne(df['th'].shift()).cumsum())
.idxmax()]
Variant with a different method to swap the sign and to compute the group:
out = df.loc[df['N'].mask(df['th'].eq(1), -df['N'])
.groupby(df['th'].diff().ne(0).cumsum())
.idxmax()]
Output:
one N th
0 A 5 1
1 Z 17 0
3 B 9 1
4 B 17 0
6 XC 35 1
7 C 85 0
8 Ce 965 1
Intermediates:
one N th swap group max
0 A 5 1 -5 1 X
1 Z 17 0 17 2 X
2 A 16 0 16 2
3 B 9 1 -9 3 X
4 B 17 0 17 4 X
5 B 117 1 -117 5
6 XC 35 1 -35 5 X
7 C 85 0 85 6 X
8 Ce 965 1 -965 7 X
The above code works for an arbitrary number of consecutive 0s or 1s. If you know that you only have up to 2 successive ones, you could also use boolean indexing, which should be significantly faster:
# has the value higher precedence than the next?
D = df['N'].mask(df['th'].eq(1), -df['N']).diff()
# is the th different from the previous?
G = df['th'].ne(df['th'].shift(fill_value=-1))
# rule for the bottom row
m1 = D.gt(0) | G
# rule for the top row
# same rule as above but shifted up
# D is inverted
# comparison is not strict in case of equality
m2 = ( D.le(0).shift(-1, fill_value=True)
| G.shift(-1, fill_value=True)
)
# keep rows of interest
out = df.loc[m1&m2]
Output:
one N th
0 A 5 1
1 Z 17 0
3 B 9 1
4 B 17 0
6 XC 35 1
7 C 85 0
8 Ce 965 1
Intermediates:
one N th D G m1 m2 m1&m2
0 A 5 1 NaN True True True True
1 Z 17 0 22.0 True True True True
2 A 16 0 -1.0 False False True False
3 B 9 1 -25.0 True True True True
4 B 17 0 26.0 True True True True
5 B 117 1 -134.0 True True False False
6 XC 35 1 82.0 False True True True
7 C 85 0 120.0 True True True True
8 Ce 965 1 -1050.0 True True True True
More complex example with equal values:
one N th D G m1 m2 m1&m2
0 A 5 1 NaN True True True True
1 Z 17 0 22.0 True True True True
2 A 16 0 -1.0 False False True False
3 B 9 1 -25.0 True True True True
4 B 17 0 26.0 True True True True
5 B 117 1 -134.0 True True False False
6 XC 35 1 82.0 False True True True
7 C 85 0 120.0 True True True True
8 Ce 965 1 -1050.0 True True True True
9 u 123 0 1088.0 True True True True # because of D.le(0)
10 v 123 0 0.0 False False True False # because or D.gt(0)
NB. in case of equality, it is possible to select the first/second row or both or none, depending on the operator used (D.le(0), D.lt(0), D.gt(0), D.ge(0)).
Although limited to maximum 2 consecutive "th", the boolean mask approach is ~4-5x faster. Timed on 1M rows:
# groupby + idxmax
96.4 ms ± 6.64 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
# boolean masks
22.2 ms ± 1.48 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)