Finding greatest fall and rise in a dynamic rolling window based on index

Question

Have a df of readings as follows:

import pandas as pd
import numpy as np
df = pd.DataFrame(np.random.randint(1000, size=100), index=range(100), columns = ['reading'])

Want to find the greatest rise and the greatest fall for each row based on its index, which theoretically may be achieved using the formula...

How can this be coded?

Tried:

df.assign(gr8Rise=df.rolling(df.index).apply(lambda x: x[-1]-x[0], raw=True).max())

...and failed with ValueError: window must be an integer

UPDATE: Based on @jezrael dataset the output for gr8Rise is expected as follows:

jezrael · Accepted Answer · 2019-12-04T08:55:31.093

1

Use:

np.random.seed(2019)

df = pd.DataFrame(np.random.randint(100, size=10), index=range(10), columns = ['reading'])

df['gr8Rise'] = [df['reading'].rolling(x).apply(lambda x: x[0]-x[-1], raw=True).max() 
                 for x in range(1, len(df)+1)]
df.loc[0, 'gr8Rise']= np.nan
print (df)
   reading  gr8Rise
0       72      NaN
1       31     41.0
2       37     64.0
3       88     59.0
4       62     73.0
5       24     76.0
6       29     72.0
7       15     57.0
8       12     60.0
9       16     56.0

edited Dec 04 '19 at 08:55

answered Dec 04 '19 at 07:02

jezrael

822,522
95
1,334
1,252

unfortunately it doesn't look to be correct. Row 1 should be 41, Row 2 should be 64. Please have a look at https://imgur.com/a/GrpAgE5? – reservoirinvest Dec 04 '19 at 08:20
df.gr8Rise.iloc[1] should be = 41 as 72 - 31 = 41 is the greatest rise. df.gr8Rise.iloc[2] should be = 64 as 88 - 24 = 64 is the greatest rise. Not able to understand how you got 51 and 51 for these... – reservoirinvest Dec 04 '19 at 08:27
@reservoirinvest - hmmm, so what is expected output for all data? – jezrael Dec 04 '19 at 08:27
gr8Rise[0..9] = (41, 64, 59, 73, 76, 72, 57, 60, 56). – reservoirinvest Dec 04 '19 at 08:50
@reservoirinvest - thanks, how are counts last 2 values? Because `last` is not `29-72=-43` ? – jezrael Dec 04 '19 at 08:52
1

please see UPDATE in the question. – reservoirinvest Dec 04 '19 at 08:54
@reservoirinvest - Is performance important? What is size of DataFrame? – jezrael Dec 04 '19 at 10:29
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/203585/discussion-between-reservoirinvest-and-jezrael). – reservoirinvest Dec 04 '19 at 11:30
@reservoirinvest - You are welcome! Btw, Maybe if check [this](https://stackoverflow.com/q/59172699/2901002) and compare with my solution above, it is question about improve performance of this solution ;) – jezrael Dec 06 '19 at 05:59

Finding greatest fall and rise in a dynamic rolling window based on index

1 Answers1