Calculate head-to-head statistics using Pandas

user_7024802 0
Viewed 293

I have a DataFrame that looks like this:

  home_team  away_team  score  home_goals  away_goals  winner
1  Arsenal    Chelsea    3-0        3          0       Arsenal
2  ManCity    Arsenal    1-1        1          1       draw
3  Chelsea    Arsenal    2-1        2          1       Chelsea
4  Arsenal    Chelsea    5-5        5          5       draw
5  Arsenal    ManCity    1-2        1          2       ManCity

My question is: how do I calculate the win-draw-loss/head-to-head record for Arsenal vs. another team?

A potential desired outcome could look like the following:

   team      opponent  games_played  wins  draws  losses  goals_scored  goals_conceded
1  Arsenal   Chelsea        3          1     1      1          9              7
2  Arsenal   ManCity        2          0     1      1          2              3

Any help is greatly appreciated. Note, the dataframe is not real (in case any Premier League experts are lurking)!

pandas-groupby pandas dataframe python-3.x python
2 Answers

Check this code:

import pandas as pd

df_in = pd.read_csv('data.csv')
df_out = pd.DataFrame(columns = ['team', 'opponent', 'games_played', 'wins', 'draws', 'losses', 'goals_scored', 'goals_conceded'])

team = 'Arsenal'

for index, row in df_in.iterrows():
    if row['home_team'] == team:
        opponent = row['away_team']
        if row['home_goals'] > row['away_goals']:
            win = 1
            draw = 0
            loss = 0
        elif row['home_goals'] < row['away_goals']:
            win = 0
            draw = 0
            loss = 1
        else:
            win = 0
            draw = 1
            loss = 0
        goals_scored = row['home_goals']
        goals_conceded = row['away_goals']
    else:
        opponent = row['home_team']
        if row['home_goals'] > row['away_goals']:
            win = 0
            draw = 0
            loss = 1
        elif row['home_goals'] < row['away_goals']:
            win = 1
            draw = 0
            loss = 0
        else:
            win = 0
            draw = 1
            loss = 0
        goals_scored = row['away_goals']
        goals_conceded = row['home_goals']

    games_played = 1



    if opponent not in df_out['opponent'].unique():
        match = pd.DataFrame({'team': team,
                              'opponent': opponent,
                              'games_played': games_played,
                              'wins': win,
                              'draws': draw,
                              'losses': loss,
                              'goals_scored': goals_scored,
                              'goals_conceded': goals_conceded},
                             index = [0])
        df_out = pd.concat([df_out, match], ignore_index = True)
    else:
        df_out.loc[df_out['opponent'] == opponent, 'games_played'] += games_played
        df_out.loc[df_out['opponent'] == opponent, 'wins'] += win
        df_out.loc[df_out['opponent'] == opponent, 'draws'] += draw
        df_out.loc[df_out['opponent'] == opponent, 'losses'] += loss
        df_out.loc[df_out['opponent'] == opponent, 'goals_scored'] += goals_scored
        df_out.loc[df_out['opponent'] == opponent, 'goals_conceded'] += goals_conceded

This code will load you data as df_in and create a df_out with the data you want.
Output:

      team opponent games_played wins draws losses goals_scored goals_conceded
0  Arsenal  Chelsea            3    1     1      1            9              7
1  Arsenal  ManCity            2    0     1      1            2              3
user_92143570

First you need to duplicate the data and flip the home/away team to get the statistics in the team/opponent style you want.

This is because you need to count each match twice, once for the winner and once for the looser. Duplicate the df and flip the fields, then use the df.concat to put the dataframes together.

Now you can aggregate

You should aggregate on the home, away and winner. Count the lines and the goals in this step. Use the df.groupby(dimensions).agg(metrics)

Now you need to reset the index back into the df so you can use the winner column again. Use the df.reset_index(inplace=True) to accomplish this.

Once you have this, you create new columns win, loss, draw' where you compare the winner to the home_team column or to the static string 'draw'

You can now aggragate the df again and sum up the win/loss/draw columns.

user_37303970
Related