Pandas, select rows that has a columns bigger that next n rows

user_2358233 0
Viewed 30

I have a pandas structure like this:

index       bsl     mb              hbr
74729   47091.89    47160.00        74731.0
74730   47159.00    47201.00        74835.0
74731   47196.50    47201.50        74735.0
74732   47186.50    47198.02        74735.0
74733   47191.50    47191.50        74735.0
74734   47162.50    47254.00        74736.0
74735   47252.50    47411.50        74736.0
74736   47414.50    47421.00        74747.0
74737   47368.50    47403.00        74742.0
74738   47305.00    47310.00        74742.0
74739   47292.00    47320.00        74742.0
74740   47302.00    47374.00        74742.0
74741   47291.47    47442.50        74899.0
74742   47403.50    47416.50        74746.0
74743   47354.34    47362.50        74746.0
74737   47368.50    47403.00        74742.0

I'm looking for a solution without loop to find all the rows with 'hbr' bigger than next n rows. for instance if we have n=3 (in real problem n can be bigger than 500) output will be

index       bsl     mb              hbr
74730   47159.00    47201.00        74835.0
74736   47414.50    47421.00        74747.0
74741   47291.47    47442.50        74899.0
pandas dataframe python
1 Answers

You can shift and use a reverse rolling.max:

n = 3

# compute boolean mask on reverse shifted rolling
m = df['hbr'].gt(df['hbr'].shift(-1)[::-1].rolling(n).max())

# slice
out = df[m]

output:

    index       bsl       mb      hbr
1   74730  47159.00  47201.0  74835.0
7   74736  47414.50  47421.0  74747.0
12  74741  47291.47  47442.5  74899.0
user_163434640
Related