I have a question about calculating a weighted average in pandas. I have a solution, but I am not happy with it and I am sure there must be a faster and easier way that I am not aware of.
I have a dataframe that includes columns ID, date and some other columns containing numerical values. For every ID I have at least 1 entry/row. The dataframe is sorted by date. Now I want to do the following weighted average calculation: For every ID in the dataframe I want to calculate for every date for that ID the time-weighted average of the numerical column values that where before the current reference date. The average is time-weighted based on the current reference date, say by (reference date-X) for every date X that was before the reference date for the current ID. The dates are more or less random and each ID may have a different combination of dates. So the tricky part is that the reference date changes per row, which means the time-weights change per row, so I don't see a simple way to do this with an apply/lambda combination inside the dataframe. I am doing it at the moment with 2 loops, which is very slow.
Simplified example with only one id and 4 dates:
from datetime import datetime
import pandas as pd
df = pd.DataFrame({'date' : [datetime(2001,1,13),datetime(2002,4,15),
datetime(2005,7,5), datetime(2008,9,16)],
'id' : [1,1,1,1],
'col1' : [9,6,4,3]})
for id in df['id'].unique():
df_sub1 = df[df['id'] == id]
for date in df_sub1['date'].to_list():
df_sub2 = df_sub1[df_sub1['date'] < date]
if df_sub2.empty:
continue
df_sub2['time_weight'] = date - df_sub2['date']
feature1 = ((df_sub2['col1'] * df_sub2['time_weight']).sum()
/df_sub2['time_weight'].sum())
df_sub1.loc[df_sub1['date'] == date, 'features1'] = feature1
df.loc[df['id'] == id, 'feature1'] = df_sub1['features1']
The output for feature1 is: nan, 9, 7.74, 6.96
Does anyone have a suggestion how to implement a more efficient solution?
Edit: Added example