Pandas rolling max loss between 2 columns with condition

mihadu 0
Viewed 37

Having a pandas dataframe with multiple columns, I would like to get the max difference between column 'high' and subsequent values column 'low' over n observations, for each row.


    close   high    low     open
0   12.65   13.16   12.63   12.80
1   12.46   12.84   12.28   12.70
2   13.14   13.25   12.63   13.16
3   12.92   13.14   12.79   12.98
4   12.95   13.05   12.69   13.00
5   13.40   13.71   13.03   13.10

Let's take an example: Say we have a n=3. In this case df.max_loss[0] would be:

max([df.high[0] - df.low[0],
    df.high[0] - df.low[1], <---
    df.high[0] - df.low[2],
    df.high[1] - df.low[1],
    df.high[1] - df.low[2],
    df.high[2] - df.low[2]]

0.88

The value for a given subset can be computed, but I would like to apply it to the whole df, computing the max_loss over next three rows for every observation.

def max_loss(high, low, window=3):
  lst = []
  for ih in np.arange(0,window):
    for il in np.arange(ih,window):
      dd = round(high[ih] - low[il], 2)
      lst.append(dd)

  return max(lst)

max_loss(df.high, df.low, 3)

I could make another for loop, but I feel there must be a more elegant way... . I tried using rolling().apply(), but no success yet. Is it possible to apply to a rolling window a function that involves multiple rows and columns? Any idea?

rolling-computation pandas python
3 Answers

Probably not the fastest solution but you can define a custom function that cross merges the indexes - with the indexes of high being leq than that of low:

def greater_cartesian(df, n):
          idx_frame = df.index[:n].to_frame()
          product = idx_frame.merge(idx_frame, how='cross')
          product.columns = ['high', 'low']

          # filter out the indexes where high has greater index
          product = product[product['high'] <= product['low']]
          return product

Then apply the new indexes on high and low on the actual dataframe and get the max value:

greater_cartesian(df, 3).\
        apply(lambda row: df.loc[row.high, 'high'] - df.loc[row.low, 'low'], axis=1)

which gives

0    0.53
1    0.88
2    0.53
4    0.56
5    0.21
8    0.62

of course, you can get the max value with max after apply.

Nuri Taş0

A non-refined solution would be to 'improvise' a rolling window subsetting the initial df.

def max_loss(df, n):
  #initialize a max_loss column
  df['max_loss'] = np.NaN

  #roll through df with an improvised n-sized window
  for i in np.arange(0, len(df)-n+1):
    df_roll=df[i:i+n].copy()
    lst=[]

    #compute each loss in the roll (considering ih<=il) & append to the list
    for ih in np.arange(0,n):
      for il in np.arange(ih,n):
        dd = round(df_roll.loc[df_roll.index[ih], 'high'] - df_roll.loc[df_roll.index[il], 'low'], 2)
        lst.append(dd)
        
    #get the max   
    df.loc[df.index[i], 'max_loss'] = max(lst)

  return df

df = max_loss(df, n=3)

Desired output. I removed the unnecessary columns for better visibility.

    high    low     max_loss
0   13.16   12.63   0.88
1   12.84   12.28   0.62
2   13.25   12.63   0.62
3   13.14   12.79   0.68
4   13.05   12.69   NaN
5   13.71   13.03   NaN

However, passing through 3 for-loops is not very efficient, especially if we want to scale it to multiple large dataframes.

mihadu0

Ok, so I found another approach with is ~30x faster (the bar was quite low though)

First, we generate a list of tuples (ih, il) where ih is the high column index and il is the low column index, considering that ih<il and both <n

We then iterate through the tuples and shift the high and low columns according to the tuple. We will obtain a series of columns computing the differences between the 2 shifted columns. Then we simply take the max across the columns on the dummy 'loss' dataframe.

def max_loss2(df, n):
  #this is optional, just to avoid warnings when n is to big
  from warnings import simplefilter
  simplefilter(action="ignore", category=pd.errors.PerformanceWarning)

  tuples = [(ih, il) for ih in np.arange(0,n) for il in np.arange(ih,n)]

  loss = pd.DataFrame(index = df.index)
  for ih, il in tuples:
    loss[f'{ih}_{il}'] = round(df.high.shift(-ih) - df.low.shift(-il), 2)
  df[f'max_loss{n}'] = loss.max(axis=1)

  return df

Considering a df with ~400 rows:

%time max_loss2(df, n=5)
CPU times: user 21 ms, sys: 54 µs, total: 21.1 ms
Wall time: 19.7 ms

compared with the previous "loop through":

%time df = max_loss(df, n=5)
CPU times: user 672 ms, sys: 14 ms, total: 686 ms
Wall time: 672 ms

Any idea to go even faster? :)

mihadu0
Related