Query dataframe of sports data by winner/loser name, and get tables of aggregate stats per-player?

You need to query the dataframe for player name occurring in either 'Winner' or Loser' column, to get matches, a dataframe of all matches involving that player. This is harder than at first glance - we can't just use a simple df.groupby(). Sorry for underestimating your question.

• Solution below uses df[['Winner','Loser']].isin([player]).any(axis=1)

  • Note that parameterizes the list of columns where the name can occur, it doesn't hardcode them. More elegant idiom than df['Winner'] == player or df['Loser'] == player
  • I also looked into df.query("...") which takes arbitrary SQL-style query string, but its syntax is annoying

• There are 1387 unique players across both columns: 879 unique players in df['Winner'] and 1383 in df['Loser']. We combine both those into one set player_names. You could use lists but it's easier to use a set to remove duplicates: set(df['Winner'].unique()) | set(df['Loser'].unique()) where '|' is the setwise-'or'/.union.

• For efficiency in handling player names, we use a categorical instead of string. Note how the code has to take to have one unified categorical with merged categories values across both 'Winner','Loser' columns (see code).
• We use a defaultdict(dict) for players, this is elegant because now you can just directly define fields players[player]['WhateverField'] regardless whether the dict players[player] is defined, if not it will be automatically created.
• Code here shows examples of the aggregate fields you want to create.
  • note how you can directly used pandas vectorized mean(), sum(), etc. for simple stuff
  • to create other more arbitrary aggregations, you could use matches.agg(...) with a custom/lambda function inside.

Code:

import pandas as pd

# https://www.kaggle.com/jordangoblet/atp-tour-20002016
df = pd.read_csv('../input/Data.csv', encoding='latin1', usecols=range(12+1), na_values='NR',
    parse_dates=['Date'], dayfirst=True)
# make sure that the 'NR' in WRank/LRank column is recognized as NaN, so they read in as float columns
# Columns 0-12 (basic data), 13-25 (results by set), 26-53 (betting odds: float)

# Create table of number of wins, losses, days since last match, average serve percentage etc. per player.

pd.options.display.max_rows = 100
pd.options.display.precision = 3    

player_names = set(df['Winner'].unique()) | set(df['Loser'].unique())
player_names = sorted(player_names) # 1.0.x BUG: order seems totally unstable(!)
player_dtype = pd.CategoricalDtype(categories = player_names) # merge across multiple columns

# WRONG: categories only taken from each individual column, not merged
# df[['WinnerCat1','LoserCat1']] = df[['Winner','Loser']].astype('category')
# RIGHT:
df[['WinnerCat','LoserCat']] = df[['Winner','Loser']].astype(player_dtype)

from collections import defaultdict
players = defaultdict(dict)

for _,player in (player_names):
    # query df where player occurs in either column, can't use `df[['Winner','Loser']].groupby(player)`
    matches = df[ df[['Winner','Loser']].isin([player]).any(axis=1) ]

    print(f'\n[MATCHES FOR {_} {player}]')
    print(matches.iloc[:, 0:13])

    players[player]['Wins']   = matches['Winner'].eq(player).sum()
    players[player]['Losses'] = matches['Loser'].eq(player).sum()
    players[player]['Avg']    = matches['Winner'].eq(player).mean().round(4)
    # You can do any arbitrary calculation involving both players' columns, e.g....
    players[player]['RankDiff'] = (matches['WRank'] - matches['LRank']).mean()

    #if _ >= 2: break # for debugging

If you have a csv file, for example, load it in like that

import pandas as pd
dataframe = pd.read.csv('file.csv')

Now, select the columns that interest you(i'll use the kaggle database as reference).

Note that i won't wrote everything that you asked because i didn't understand your question completely(there's not date on the kaggle database, and average of what per player?)

dataframe = dataframe['Winner', 'Loser']

Now, we use the groupby function along with the count function to get the amount of wins and losses of each player(along with making its appeareance on the "Winner"/"Loser" columns unique.

dataframe['Wins'] = dataframe.groupby(labels='Winner', axis=1).count()
dataframe['Losses'] = dataframe.groupby(labels='Loser', axis=1).count()

I ran the following code without any errors so I dont think its an issue with the = game.

df = pd.read_csv(r'Ten.csv')


lol = df.values.tolist()


players = {}
for game in lol:
    Player,Tournament,Wins,Losses = game

    if Player not in players:
        players[Player] = {
            'Tournament': 'foo',
            'wins': 1,
            'losses': 2,
        }

print(players)

However if Player is different such as the following (lowercase "p")

player,Tournament,Wins,Losses = game

or: (an "s" at the end of [Player])

        players[Players] = {
            'Tournament': 'foo',
            'wins': 1,
            'losses': 2,
        }

the error NameError: name 'Player' is not defined will pop up. Are you 100% sure that game_id isn't being misspelled somewhere?