Let’s first create the example pandas dataframe:
In [1]: import numpy as np
In [2]: import pandas as pd
In [3]: index = pd.Index(['01/01/2012','01/01/2012','01/01/2012','01/02/2012','01/02/2012'], name="Date")
In [4]: df = pd.DataFrame({'ID':[100,101,102,201,202],'wt':[.5,.75,1,.5,1],'value':[60,80,100,100,80]},index=index)
Then, the average of ‘wt’ weighted by ‘value’ and grouped by the index is obtained as:
In [5]: df.groupby(df.index).apply(lambda x: np.average(x.wt, weights=x.value))
Out[5]:
Date
01/01/2012 0.791667
01/02/2012 0.722222
dtype: float64
Alternatively, one can also define a function:
In [5]: def grouped_weighted_avg(values, weights, by):
...: return (values * weights).groupby(by).sum() / weights.groupby(by).sum()
In [6]: grouped_weighted_avg(values=df.wt, weights=df.value, by=df.index)
Out[6]:
Date
01/01/2012 0.791667
01/02/2012 0.722222
dtype: float64