Pandas rolling weighted average

Question

I want to apply a weighted rolling average to a large timeseries, set up as a pandas dataframe, where the weights are different for each day. Here's a subset of the dataframe

DF:

Date        v_std  vertical                  
2010-10-01  1.909   545.231
2010-10-02  1.890   538.610
2010-10-03  1.887   542.759
2010-10-04  1.942   545.221
2010-10-05  1.847   536.832
2010-10-06  1.884   538.858
2010-10-07  1.864   538.017
2010-10-08  1.833   540.737
2010-10-09  1.847   537.906
2010-10-10  1.881   538.210
2010-10-11  1.868   544.238
2010-10-12  1.856   534.878

I want to take a rolling average of the vertical column using the v_std as the weights. I've been using the weighted average function:

def wavg(group, avg_name, weight_name):
    d = group[avg_name]
    w = group[weight_name]
    try:
        return (d * w).sum() / w.sum()
    except ZeroDivisionError:
        return d.mean()

But I can't figure out how to implement this for a rolling weighted average. I assume it is similar to

df.rolling(window = 7).apply(wavg, "vertical", "v_std")

or utilizing rolling_apply? Or will I have to write a new function all together? Thank you!

Answer 1

Here is my solution for rolling weighted average, using pandas _Rolling_and_Expanding:

First, I've added new column for the multiplication:

df['mul'] = df['value'] * df['weight']

Then write the function you would like to apply:

from pandas.core.window.rolling import _Rolling_and_Expanding
def weighted_average(x):
    d = []
    d.append(x['mul'].sum()/x['weight'].sum())
    return pd.Series(d, index=['wavg'])

_Rolling_and_Expanding.weighted_average = weighted_average

Apply the function by the following line:

result = mean_per_group.rolling(window=7).weighted_average()

Then you can get the series you wanted by:

result['wavg']