How to calculate conditional aggregate measure for each row in dataframe?

user_7911933 0
Viewed 44

I have a table like this...

Date PlayerId Goals
June 1 A 1
June 14 A 1
June 15 B 2
June 28 A 1
July 6th B 0
July 17th A 1

I would like to calculate the amount of goals a player had scored in the 30 days prior (NOT 30 games). The final results should look like...

Date PlayerId Goals Goals_Prev_30
June 1 A 1 0
June 14 A 1 1
June 15 B 2 0
June 28 A 1 2
July 6th B 0 2
July 17th A 1 1

I created a for loop that filters that identifies a single row in the dataframe, then filters the dataframe by characteristics of the row, then calculates the sum of goals in the filtered dataframe, appends it to a list, which is finally assigned to the Goals_Prev_30 column. The code looks like...

30_day_goals = []
for i in range(len(df)):
    row = df.iloc[i]
    filtered_df = df[(df['Date'] < row['Date']) & (df['Date'] >= row['Date'])- pd.to_timedelta(30,unit='d')) & (df['PlayerId'] == row['PlayerId'])]
    total = filtered_df['Goals'].sum()
    30_day_goals.append(total)

df['Goals_Prev_30'] = 30_day_goals

This solution works, but it's slow. It can do around 30 rows a second, however it's not a viable solution as I have multiple measures that are similar and there are over 1.2M rows. This means it will take around 11hrs per measure to complete.

How can this problem be solved in a more efficient manner?

pandas conditional-statements aggregate python
1 Answers

I change your solution to custom function per groups with mask created by broadcasting and sum values of Goals column per groups if match:

#if necessary
#df['Date'] = pd.to_datetime(df['Date'], format='%B %d')

def f(x):
    d1 = x['Date']
    d2 = d1 - pd.to_timedelta(30,unit='d')
    a1 = d1.to_numpy()
    a2 =  d2.to_numpy()
    
    m = (a1 < a1[:, None]) & (a1 >=a2[:, None])
    x['Goals_Prev_30'] = np.where(m, x['Goals'], 0).sum(axis=1)
    return x
 

df = df.groupby('PlayerId').apply(f)

print (df)
        Date PlayerId  Goals  Goals_Prev_30
0 1900-06-01        A      1              0
1 1900-06-14        A      1              1
2 1900-06-15        B      2              0
3 1900-06-28        A      1              2
4 1900-07-06        B      0              2
5 1900-07-17        A      1              1
user_29010020
Related