How to invoke pandas.rolling.apply with parameters from multiple column?

Question

I've got a dataset:

    Open     High      Low    Close        
0  132.960  133.340  132.940  133.105
1  133.110  133.255  132.710  132.755
2  132.755  132.985  132.640  132.735 
3  132.730  132.790  132.575  132.685
4  132.685  132.785  132.625  132.755

I try to use rolling.apply function for all rows, like this:

df['new_col']= df[['Open']].rolling(2).apply(AccumulativeSwingIndex(df['High'],df['Low'],df['Close']))

shows error

or

df['new_col']=  df[['Open', 'High', 'Low', 'Close']].rolling(2).apply(AccumulativeSwingIndex)

pass only parameter from column 'Open'

Can anybody help me?

I don't know where `AccumulativeSwingIndex` comes from. See if my answer is helpful. — piRSquared, Aug 10 '16 at 16:37

piRSquared · Answer 1 · 2019-04-24T17:08:32.670

Define your own `roll`

We can create a function that takes a window size argument w and any other keyword arguments. We use this to build a new DataFrame in which we will call groupby on while passing on the keyword arguments via kwargs.

Note: I didn't have to use stride_tricks.as_strided but it is succinct and in my opinion appropriate.

from numpy.lib.stride_tricks import as_strided as stride
import pandas as pd

def roll(df, w, **kwargs):
    v = df.values
    d0, d1 = v.shape
    s0, s1 = v.strides

    a = stride(v, (d0 - (w - 1), w, d1), (s0, s0, s1))

    rolled_df = pd.concat({
        row: pd.DataFrame(values, columns=df.columns)
        for row, values in zip(df.index, a)
    })

    return rolled_df.groupby(level=0, **kwargs)

roll(df, 2).mean()

       Open      High       Low    Close
0  133.0350  133.2975  132.8250  132.930
1  132.9325  133.1200  132.6750  132.745
2  132.7425  132.8875  132.6075  132.710
3  132.7075  132.7875  132.6000  132.720

We can also use the pandas.DataFrame.pipe method to the same effect:

df.pipe(roll, w=2).mean()

OLD ANSWER

Panel has been deprecated. See above for updated answer.

see https://stackoverflow.com/a/37491779/2336654

define our own roll

def roll(df, w, **kwargs):
    roll_array = np.dstack([df.values[i:i+w, :] for i in range(len(df.index) - w + 1)]).T
    panel = pd.Panel(roll_array, 
                     items=df.index[w-1:],
                     major_axis=df.columns,
                     minor_axis=pd.Index(range(w), name='roll'))
    return panel.to_frame().unstack().T.groupby(level=0, **kwargs)

you should be able to:

roll(df, 2).apply(your_function)

Using mean

roll(df, 2).mean()

major      Open      High       Low    Close
1      133.0350  133.2975  132.8250  132.930
2      132.9325  133.1200  132.6750  132.745
3      132.7425  132.8875  132.6075  132.710
4      132.7075  132.7875  132.6000  132.720

f = lambda df: df.sum(1)

roll(df, 2, group_keys=False).apply(f)

   roll
1  0       532.345
   1       531.830
2  0       531.830
   1       531.115
3  0       531.115
   1       530.780
4  0       530.780
   1       530.850
dtype: float64

@piRSquared thanks! It's a little surprising there isn't a vanilla pandas way to do this. Feels quite a natural thing to want to do. — Andy Hayden, Apr 24 '19 at 18:14
It's a very useful function, which I was missing in the Pandas API. How would you approach the same task if you would want to use a window as a datetime frequency (for example `win="3D"`)? — MaxU - stand with Ukraine, May 19 '19 at 10:58
@MaxU that’s a good question. I’ll give it some thought when I have a chance. — piRSquared, May 19 '19 at 12:22
@piRSquared Any more thoughts on running this window method with a datetime offset? — blalterman, Oct 01 '19 at 21:37
@blalterman no unfortunately. I haven't had the time to do most of what I want to do (-: — piRSquared, Oct 01 '19 at 21:39
Nice approach, but it's very slow compared to basic DataFrame.rolling(period).apply. This really should be a part of the standard optimized pandas API... — user4157482, Apr 06 '20 at 06:24
@SebastianJylanki The slow part comes from the `pd.concat` operation, and it can be bypassed. — Gang Liang, Sep 17 '20 at 03:48

score 4 · Answer 2 · answered Apr 07 '18 at 20:17

As your rolling window is not too large, I think you can also put them in the same dataframe then use the apply function to reduce.

For example, with the dataset df as following

            Open    High        Low     Close
Date                
2017-11-07  258.97  259.3500    258.09  258.67
2017-11-08  258.47  259.2200    258.15  259.11
2017-11-09  257.73  258.3900    256.36  258.17
2017-11-10  257.73  258.2926    257.37  258.09
2017-11-13  257.31  258.5900    257.27  258.33

You can just add the rolling data to this dataframe with

window = 2
df1 = pd.DataFrame(index=df.index)
for i in range(window):
    df_shifted = df.shift(i).copy()
    df_shifted.columns = ["{}-{}".format(s, i) for s in df.columns]
    df1 = df1.join(df_shifted)
df1

           Open-0   High-0      Low-0   Close-0 Open-1  High-1      Low-1   Close-1
Date                                
2017-11-07  258.97  259.3500    258.09  258.67  NaN     NaN         NaN     NaN
2017-11-08  258.47  259.2200    258.15  259.11  258.97  259.3500    258.09  258.67
2017-11-09  257.73  258.3900    256.36  258.17  258.47  259.2200    258.15  259.11
2017-11-10  257.73  258.2926    257.37  258.09  257.73  258.3900    256.36  258.17
2017-11-13  257.31  258.5900    257.27  258.33  257.73  258.2926    257.37  258.09

Then you can make an apply on it easily with all the rolling data you want with

df1.apply(AccumulativeSwingIndex, axis=1)

score 4 · Answer 3 · answered Mar 03 '21 at 17:29

4

Here's a workaround I came up with:

df['new_col'] = list(map(fn, df.rolling(2)))

answered Mar 03 '21 at 17:29

Leonardo

124
1
8

May I ask what is fn? – High GPA Aug 09 '22 at 04:49
1

It's any function that will take the rolling window as a parameter and compute the transformation you want. – Leonardo Aug 10 '22 at 10:45

score 1 · Answer 4 · answered Jan 29 '22 at 04:49

I also encountered some problems alike. the following lines may help you out. this might be the simplest solution for retrieving the data(matrices) within dataframe.rolling(), after which we can do almost anything with it. As comparison, d.rolling().apply() only allows aggregation functions.

size = 20
matrices = [x.values for x in d.rolling(size)][size-1:]
len(matrices)
[do_anything(i) for i in matrices]

score 0 · Answer 5 · answered Jun 30 '22 at 09:24

How about this:

ggg = pd.DataFrame({"a":[1,2,3,4,5,6,7], "b":[7,6,5,4,3,2,1]})

def my_rolling_apply2(df, fun, window, if_prepend=True):
    prepend = [None] * (window - 1)
    end = len(df) - window
    mid = map(lambda start: fun(df[start:start + window]), np.arange(0,end))
    last =  fun(df[end:])
    if if_prepend:
        return [*prepend, *mid, last]
    else:
        return [*mid, last, *prepend]

display(my_rolling_apply2(ggg, lambda df: (df["a"].max(), df["b"].min()), 3))
display(my_rolling_apply2(ggg, lambda df: (df["a"].max(), df["b"].min()), 3, False))

And the result:

[None, None, (3, 5), (4, 4), (5, 3), (6, 2), (7, 1)]
[(3, 5), (4, 4), (5, 3), (6, 2), (7, 1), None, None]

score -4 · Answer 6 · answered Jan 12 '20 at 12:10

-4

If you're trying to apply the function to all rows of all columns:

df.rolling(size_of_your_window).apply(your_function_here)

answered Jan 12 '20 at 12:10

Mike

1

score -5 · Answer 7 · answered Aug 10 '16 at 16:47

-5

Try this for passing multiple columns to apply

df['new_column'] = df.apply(lambda x: your_function(x['High'],x['Low'],x['Close']), axis=1)

answered Aug 10 '16 at 16:47

SO44

1,249
9
9

1

how do we pass the window size? – RaduS Jan 28 '17 at 08:53
1

this does not address how to pass a window size, which is what the question asked – tsando Oct 10 '17 at 15:15

How to invoke pandas.rolling.apply with parameters from multiple column?

7 Answers7

Define your own `roll`

OLD ANSWER

Linked

Related

How to invoke pandas.rolling.apply with parameters from multiple column?

7 Answers7

Define your own roll

OLD ANSWER

Linked

Related

Define your own `roll`