Apply function with multiple arguments to rolling DataFrame Pandas

Question

I have a Pandas DataFrame similar to the below. Please not this does not need to be in this format, it can be put back into pivoted fashion and have each variable be a column if that's easier.

      date      variable  value
0   2014-01-31    item1  0.070898
1   2014-02-28    item1  0.064725
2   2014-03-31    item1  0.101292
3   2014-04-30    item1  0.041898
4   2014-05-31    item1  0.094894
5   2014-06-30    item1  0.110287
6   2014-07-31    item1  0.081844
7   2014-08-31    item1  0.043819
8   2014-09-30    item1  0.009196
9   2014-10-31    item1  0.076482
10  2014-11-30    item1  0.055906
11  2014-12-31    item1  0.042028
12  2015-01-31    item1  0.115469
13  2015-02-28    item1  0.130457
14  2015-03-31    item1  0.054314
15  2015-04-30    item1  0.141286
16  2015-05-31    item1  0.109387
17  2015-06-30    item1  0.066154
18  2015-07-31    item1  0.039390
19  2015-08-31    item1  0.071276
20  2015-09-30    item1  0.088784
21  2015-10-31    item1  0.054234
22  2015-11-30    item1  0.111854
23  2015-12-31    item1  0.005053
24  2016-01-31    item1  0.145953
25  2016-02-29    item1  0.138051
26  2016-03-31    item1  0.030395
27  2016-04-30    item1  0.055843
28  2016-05-31    item1  0.037960
29  2016-06-30    item1  0.147318
..         ...      ...       ...
120 2015-09-30      ref  0.043185
121 2015-10-31      ref  0.046849
122 2015-11-30      ref  0.008139
123 2015-12-31      ref  0.011222
124 2016-01-31      ref  0.026408
125 2016-02-29      ref  0.040404
126 2016-03-31      ref  0.039585
127 2016-04-30      ref  0.028782
128 2016-05-31      ref  0.002799
129 2016-06-30      ref  0.040413
130 2016-07-31      ref  0.004451
131 2016-08-31      ref  0.001946
132 2016-09-30      ref  0.029548
133 2016-10-31      ref  0.029416
134 2016-11-30      ref  0.024893
135 2016-12-31      ref  0.037656
136 2017-01-31      ref  0.045642
137 2017-02-28      ref  0.046690
138 2017-03-31      ref  0.019193
139 2017-04-30      ref  0.016362
140 2017-05-31      ref  0.025512
141 2017-06-30      ref  0.021625
142 2017-07-31      ref  0.026472
143 2017-08-31      ref  0.016531
144 2017-09-30      ref  0.047447
145 2017-10-31      ref  0.032180
146 2017-11-30      ref  0.048635
147 2017-12-31      ref  0.040450
148 2018-01-31      ref  0.047735
149 2018-02-28      ref  0.013727

For each item in variable I would like to apply a rolling 12 month calculation that accepts as arguments the rolling 12 months of value for that item, and the rolling 12 months of values where variable equals ref. The function is of the form:

def func(series1,series2):
   do something with series
   return single_value_from_doiong_something

My issue is how to pass two different series into this function to return my single value. Does anyone know how to do this?

It would be helpful if you included what your desired output is. Do you want to pass the yearly rolling mean value for example? I'm a bit confused as to whether you want to pass the **series** of values associated with that rolling 12 month window or the **mean** value of that series? — rahlf23, Jan 28 '20 at 19:02
I want to pass the series of values. The calculation takes the last 12 values for both the item and ref. — Kyle, Jan 28 '20 at 19:40
So you are guaranteed to not have any missing months? In which case you could avoid converting your date column to a DateTime object and just have a rolling window of size 12. Do you want the matching months from the `ref` then? — rahlf23, Jan 28 '20 at 19:57
I can guarantee no missing dates, and want matching months from ref. — Kyle, Jan 28 '20 at 20:07
If it makes it better, or easier, I can separate out the ref data to its own dataframe. — Kyle, Jan 28 '20 at 20:08
Thanks for the answer, and I do believe that will solve my problem. I am curious though, can you pass the function two arguments? I would like to not have the output be reliant on data internal to the function. — Kyle, Jan 28 '20 at 20:23

rahlf23 · Accepted Answer · 2020-01-28T20:48:46.730

Regarding the function you mention, after you restructure your original dataframe with a join(), this may be closer to what you are after:

combined = df[df['variable']!='ref'].set_index('date').join(df[df['variable']=='ref'].set_index('date'), lsuffix='', rsuffix='_ref').drop('variable_ref', axis=1)

def func(series, ref_series):

    #As an example
    return series.mean()/ref_series.mean()

combined.groupby('variable').rolling(12).apply(lambda x: func(x, combined.loc[x.index]['value_ref']), raw=False).drop('value_ref', axis=1)

This example yields the following (NaN are present due to the gaps in your sample data):

                        value
variable date                
item1    2014-01-31       NaN
         2014-02-28       NaN
         2014-03-31       NaN
         2014-04-30       NaN
         2014-05-31       NaN
         2014-06-30       NaN
         2014-07-31       NaN
         2014-08-31       NaN
         2014-09-30       NaN
         2014-10-31       NaN
         2014-11-30       NaN
         2014-12-31       NaN
         2015-01-31       NaN
         2015-02-28       NaN
         2015-03-31       NaN
         2015-04-30       NaN
         2015-05-31       NaN
         2015-06-30       NaN
         2015-07-31       NaN
         2015-08-31       NaN
         2015-09-30  1.912186
         2015-10-31  1.793184
         2015-11-30  2.609254
         2015-12-31  3.009455
         2016-01-31  3.123833
         2016-02-29  2.910599
         2016-03-31  2.708132
         2016-04-30  2.497878
         2016-05-31  2.561760
         2016-06-30  2.681712

This is quite confusing to figure out. I wish the `x` in the lambda function returned both columns of values so I didn't have to reference back the original dataframe for the ref values. It would just be a lot more straightforward. Appreciate the help though. No way I could have gotten here by myself. — Kyle, Feb 03 '20 at 21:19
Curious how this works now as the docs for rolling.apply() are terrible. Does this just pass in the first column of numeric data to the function when I reference `x`? Or when I reference `x` am I passing all the columns? — Kyle, Feb 04 '20 at 14:54

Apply function with multiple arguments to rolling DataFrame Pandas

1 Answers1