Getting weighted average and standard deviation on several columns in Pandas

Question

I am trying to do weighted standard deviation on top of weighted average on my pandas dataframe. I have a pandas dataframe like:

import numpy as np
import pandas as pd
df = pd.DataFrame({"Date": pd.date_range(start='2018-01-01', end='2018-01-03 18:00:00', freq='6H'),
               "Weight": np.random.uniform(3, 5, 12),
               "V1": np.random.uniform(10, 15, 12),
               "V2": np.random.uniform(10, 15, 12),
               "V3": np.random.uniform(10, 15, 12)})

Currently, to get the weighted mean, inspired by this post, I am doing the following:

def weighted_average_std(grp):
    return grp._get_numeric_data().multiply(grp['Weight'], axis=0).sum()/grp['Weight'].sum()
df.index = df["Date"]
df_agg = df.groupby(pd.Grouper(freq='1D')).apply(weighted_average_std).reset_index()
df_agg

Where I get the following:

    Date    V1  V2  V3  Weight
0   2018-01-01  11.421749   13.090178   11.639424   3.630196
1   2018-01-02  12.142917   11.605284   12.187473   4.056303
2   2018-01-03  12.034015   13.159132   11.658969   4.318753

I want to modify weighted_average_std so that it returns standard deviation for each column in addition to weighted average. The idea is to use the weighted average for each group in a vectorized fashion. The new column names for Weighted Standard Deviation can be something like V1_WSD, V2_WSD and V3_WSD.

PS1: This post goes through the theory of weighted standard deviation.

PS2: Column Weight in df_agg is meaningless.

unutbu unutbu · Accepted Answer · 2018-01-17T18:54:34

You could use EOL's NumPy-based code to calculate weighted averages and standard deviation. To use this in a Pandas groupby/apply operation, make weighted_average_std return a DataFrame:

import numpy as np
import pandas as pd


def weighted_average_std(grp):
    """
    Based on http://stackoverflow.com/a/2415343/190597 (EOL)
    """
    tmp = grp.select_dtypes(include=[np.number])
    weights = tmp['Weight']
    values = tmp.drop('Weight', axis=1)
    average = np.ma.average(values, weights=weights, axis=0)
    variance = np.dot(weights, (values - average) ** 2) / weights.sum()
    std = np.sqrt(variance)
    return pd.DataFrame({'mean':average, 'std':std}, index=values.columns)

np.random.seed(0)
df = pd.DataFrame({
    "Date": pd.date_range(start='2018-01-01', end='2018-01-03 18:00:00', freq='6H'),
    "Weight": np.random.uniform(3, 5, 12),
    "V1": np.random.uniform(10, 15, 12),
    "V2": np.random.uniform(10, 15, 12),
    "V3": np.random.uniform(10, 15, 12)})

df.index = df["Date"]
df_agg = df.groupby(pd.Grouper(freq='1D')).apply(weighted_average_std).unstack(-1)
print(df_agg)

yields

                 mean                             std                    
                   V1         V2         V3        V1        V2        V3
Date                                                                     
2018-01-01  12.105253  12.314079  13.566136  1.803014  1.725761  0.679279
2018-01-02  13.223172  12.534893  11.860456  1.709583  0.950338  1.153895
2018-01-03  13.782625  12.013557  12.105231  0.969099  1.189149  1.249064

Getting weighted average and standard deviation on several columns in Pandas

1 Answers