How to get max of a slice of a dataframe based on column values?

user_541208 0
Viewed 695

I'm looking to make a new column, MaxPriceBetweenEntries based on the max() of a slice of the dataframe

idx Price EntryBar ExitBar
0   10.00 0        1
1   11.00 NaN      NaN
2   10.15 2        4
3   12.14 NaN      NaN
4   10.30 NaN      NaN

turned into

idx Price EntryBar ExitBar MaxPriceBetweenEntries
0   10.00 0        1       11.00
1   11.00 NaN      NaN     NaN
2   10.15 2        4       12.14
3   12.14 NaN      NaN     NaN
4   10.30 NaN      NaN     NaN

I can get all the rows with an EntryBar or ExitBar value with df.loc[df["EntryBar"].notnull()] and df.loc[df["ExitBar"].notnull()], but I can't use that to set a new column:

df.loc[df["EntryBar"].notnull(),"MaxPriceBetweenEntries"] = df.loc[df["EntryBar"]:df["ExitBar"]]["Price"].max()

but that's effectively a guess at this point, because nothing I'm trying works. Ideally the solution wouldn't involve a loop directly because there may be millions of rows.

pandas dataframe python
4 Answers

You can groupby the cumulative sum of non-null entries and take the max, unsing np.where() to only apply to non-null rows::

df['MaxPriceBetweenEntries'] = np.where(df['EntryBar'].notnull(),
                                        df.groupby(df['EntryBar'].notnull().cumsum())['Price'].transform('max'),
                                        np.nan)
df
Out[1]: 
   idx  Price  EntryBar  ExitBar  MaxPriceBetweenEntries
0    0  10.00       0.0      1.0                   11.00
1    1  11.00       NaN      NaN                     NaN
2    2  10.15       2.0      4.0                   12.14
3    3  12.14       NaN      NaN                     NaN
4    4  10.30       NaN      NaN                     NaN
user_63667700

Let's try groupby() and where:

s = df['EntryBar'].notna()
df['MaxPriceBetweenEntries'] = df.groupby(s.cumsum())['Price'].transform('max').where(s)

Output:

   idx  Price  EntryBar  ExitBar  MaxPriceBetweenEntries
0    0  10.00       0.0      1.0                   11.00
1    1  11.00       NaN      NaN                     NaN
2    2  10.15       2.0      4.0                   12.14
3    3  12.14       NaN      NaN                     NaN
4    4  10.30       NaN      NaN                     NaN
user_42384080

You can forward fill the null values, group by entry and get the max of that groups Price. Use that as the right side of a left join and you should be in business.

df.merge(df.ffill().groupby('EntryBar')['Price'].max().reset_index(name='MaxPriceBetweenEntries'), 
                                                                   on='EntryBar', 
                                                                   how='left')
user_47183500

Try

df.loc[df['ExitBar'].notna(),'Max']=df.groupby(df['ExitBar'].ffill()).Price.max().values
df
Out[74]: 
   idx  Price  EntryBar  ExitBar    Max
0    0  10.00       0.0      1.0  11.00
1    1  11.00       NaN      NaN    NaN
2    2  10.15       2.0      4.0  12.14
3    3  12.14       NaN      NaN    NaN
4    4  10.30       NaN      NaN    NaN
user_79645270
Related