使用Pandas计算头对头的统计数据

Question

我有一个看起来像这样的DataFrame：

  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

我的问题是：如何计算阿森纳对另一支球队的平局输球/面对面的记录？

潜在的预期结果如下所示：

   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

任何帮助都是非常感谢的。请注意，数据不是真实的(以防任何英超专家潜伏)！

Answer 1

检查此代码：

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

这段代码将以df_in的形式加载数据，并使用所需的数据创建df_out。

输出：

      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

Answer 2

首先，您需要复制数据并翻转主队/客场队，以获得您想要的球队/对手风格的统计数据。

这是因为每一场比赛你需要数两次，一次是赢家，一次是松散的。复制df并翻转字段，然后使用df.concat将数据放到一起。

现在你可以聚合

你应该集中在主场、客场和胜利者身上。数一数这一步中的线条和目标。使用df.groupby(dimensions).agg(metrics)

现在，您需要将索引重新设置为df，以便可以再次使用winner列。使用df.reset_index(inplace=True)来完成这一任务。

一旦实现了这一点，就可以创建新的win, loss, draw'列，在这里您可以将获胜者与home_team列或静态字符串“绘图”进行比较。

现在，您可以再次增加df，并对胜诉/输赢/抽签列进行汇总。