CODEWITHSUNDEEP
pythonpandasdataframe
Rich Weis
Rich Weis

Reputation: 5

Calculating Head to Head Statistics for Teams in Python

I'm struggling to calculate Head to Head statistics for teams based on a dataframe of historical games. I can't think of how to use groupby given that the teams could be in either the home team or the away team column in the historical games dataframe. The real data is much much bigger but below is an example of what i'm trying to do.

Historical = Team     Opponent     Points     Points Against     Win?    
             Eagles   Cowboys      34         20                 1
             Cowboys  Falcons      15         35                 0
             Cowboys  Eagles       21         20                 1

I'm looking to take the above, and create the Head to Head Column in the below based on the two teams that are playing in the Home Team and Away Team columns. I don't really care how it's formatted but have shown it below as the winning percentage of the team in the Home Team column.

Upcoming =     Home Team      Away Team     Head to Head (Home Team)
                 Falcons        Cowboys       100%
                 Cowboys        Eagles        50%
                 Falcons        Eagles        NaN

Let me know if anything above isn't clear, appreciate the help.

Upvotes: 0

Views: 418

Answers (1)

Ian Wright
Ian Wright

Reputation: 346

If I'm reading this correctly, you can use named aggregation

pd.groupby(["Team","Opponent"]) \
  .agg(num_won=("Win", "sum"),
       num_played("Win", "count")) \
  .assign(win_rate=lambda x: x['num_won'] / num['played'])

Upvotes: 1

Related Questions