CODEWITHSUNDEEP
pythonpandasdatetime
L1meta
L1meta

Reputation: 373

pandas - use aggregation function on column for last n values of a datetime column in same dataframe

I have a Dataframe with sportsbetting data containing: match_id, team_id, goals_scored and a datetime column for the time the match started. I want to add a column to this dataframe that for each row shows the sum of the goals scored by each team for the previous n matches.

Upvotes: 0

Views: 131

Answers (2)

Dickster
Dickster

Reputation: 3009

I made up some mock data, because i like football, but like Jacob H suggests it's best to always supply a sample data frame with the question.

import pandas as pd
import numpy as np
np.random.seed(2)

d = {'match_id': np.arange(10)
        ,'team_id': ['City','City','City','Utd','Utd','Utd','Albion','Albion','Albion','Albion']
        ,'goals_scored': np.random.randint(0,5,10)
        ,'time_played': [0,1,2,0,1,2,0,1,2,3]}
df = pd.DataFrame(data=d)

#previous n matches
n=2

#some Saturday 3pm kickoffs.
rng = pd.date_range('2017-12-02 15:00:00','2017-12-25 15:00:00',freq='W')

# change the time_played integers to the datetimes
df['time_played'] = df['time_played'].map(lambda x: rng[x])

#be sure the sort order is correct
df = df.sort_values(['team_id','time_played'])

# a rolling sum() and then shift(1) to align value with row as per question
df['total_goals'] = df.groupby(['team_id'])['goals_scored'].apply(lambda x: x.rolling(n).sum())
df['total_goals'] = df.groupby(['team_id'])['total_goals'].shift(1)

which produces:

   goals_scored  match_id team_id         time_played  total_goals->(in previous n)
6             2         6  Albion 2017-12-03 15:00:00          NaN
7             1         7  Albion 2017-12-10 15:00:00          NaN
8             3         8  Albion 2017-12-17 15:00:00          3.0
9             2         9  Albion 2017-12-24 15:00:00          4.0
0             0         0    City 2017-12-03 15:00:00          NaN
1             0         1    City 2017-12-10 15:00:00          NaN
2             3         2    City 2017-12-17 15:00:00          0.0
3             2         3     Utd 2017-12-03 15:00:00          NaN
4             3         4     Utd 2017-12-10 15:00:00          NaN
5             0         5     Utd 2017-12-17 15:00:00          5.0

Upvotes: 1

Jacob H
Jacob H

Reputation: 607

There's probably a more efficient way to do this with aggregation functions, but here's a solution where, for each entry, you're filtering your whole dataframe to isolate that team and date range, and then summing the goals.

df['goals_to_date'] = df.apply(lambda row: np.sum(df[(df['team_id'] == row['team_id'])\
    &(df['datetime'] < row['datetime'])]['goals_scored']), axis = 1)

Upvotes: 1

Related Questions