# Rating tennis players in a database, taking days to run

I have this project in data analysis for creating a ranking of tennis players. Currently, it takes more than 6 days to run on my computer.

Can you review the code and see where's the problem?

Project steps:

1. I have a database of 600,000 tennis matches called matchdatabase.The database fileds are a) winner name, b) loser name, c) tournament, d) other fields for the winner and loser.

2. From that database, I create a playerdatabase with every player in the matchdatabase.

3. For each match in the matchdatabase it goes into the playerdatabase, retrieves the ranking/elo and computes the expected result.

4. It updates the ranking after the match into the playerdatabase

This for loop ends up running 1 match/second, so the whole database takes several days to run!

import pandas as pd
import glob
import numpy as np
import math

all_data = all_data.sort(['date'], ascending=[0])
all_data = all_data.reindex(index = np.arange(1, len(all_data) + 1))

#it checks every player in the matchdatabase and creates a database of players

playerdatabase = pd.DataFrame()
list_winners = pd.pivot_table(all_data,index=["winner_name"],values=["tourney_id"],aggfunc=np.count_nonzero)
list_losers =  pd.pivot_table(all_data,index=["loser_name"],values=["tourney_id"],aggfunc=np.count_nonzero)
firstloss =  pd.pivot_table(all_data,index=["loser_name"],values=["date"],aggfunc=np.min)
firstwin =  pd.pivot_table(all_data,index=["winner_name"],values=["date"],aggfunc=np.min)
playerdatabase = pd.concat([list_winners, list_losers, firstloss, firstwin], axis=1)
playerdatabase['NumberOfGames'] = 0

#defines a elo calculator for expectations and modified ratings

def getExpectation(rating_1, rating_2):
"calculator for the expected result to player 1 based on the rating of both players"
calc = (1.0 / (1.0 + pow(10, ((rating_2 - rating_1) / 400.0))))
return calc

def modifyRating(rating, expected, actual, kfactor):
"gives the new rating given the current rating, expected results, actual results and k factor"
calc = (rating + kfactor * (actual - expected));
return calc

#Elo calculation for the database
#sets initial rating for everyone at 2100

playerdatabase['Rating'] = 2100

loser_k_factor = 30
winner_k_factor = 30

#loop  for the calculations

for i in xrange(0, 616242):

#gets the rating for both players from the playerdatabase
winner_rating = playerdatabase.loc[all_data.iloc[i]['winner_name'], 'Rating']
loser_rating = playerdatabase.loc[all_data.iloc[i]['loser_name'], 'Rating']
all_data['winner_elo'][i+1] = winner_rating
all_data['loser_elo'][i+1] = loser_rating

#gets the expected result for both players
winner_expectation = getExpectation(winner_rating, loser_rating)
loser_expectation = getExpectation(loser_rating, winner_rating)

#gets the updated result for both players
winner_new_rating = modifyRating(winner_rating, winner_expectation, 1, winner_k_factor)
loser_new_rating = modifyRating(loser_rating, loser_expectation, 0, loser_k_factor)
#updates the results for both players in the playerdatabase
playerdatabase.loc[all_data.iloc[i]['winner_name'], 'Rating'] = winner_new_rating
playerdatabase.loc[all_data.iloc[i]['loser_name'], 'Rating'] = loser_new_rating
#updates the number of games for both players in the playerdatabase
playerdatabase.loc[all_data.iloc[i]['winner_name'], 'NumberOfGames'] = playerdatabase.loc[all_data.iloc[i]['winner_name'], 'NumberOfGames'] + 1
playerdatabase.loc[all_data.iloc[i]['loser_name'], 'NumberOfGames'] = playerdatabase.loc[all_data.iloc[i]['loser_name'], 'NumberOfGames'] + 1

#records the rating list every 500 calculations

if i%500 == 0:
playerdatabase[i] = playerdatabase['Rating']
print i
print i

playerdatabase = playerdatabase.sort(['Rating'], ascending=[1])

• Some details about your database would be appreciated. – Loufylouf Jun 19 '15 at 13:50
• A sample of your csv files would be useful. – TheBlackCat Jun 19 '15 at 14:14
• Some users suggested computing the expected all at once for every player, but this is not possible as after each game (either victory or loss) the player rating is updated and naturally also the expected result. – user3661825 Jun 19 '15 at 14:18
• The structure of the database, called matchdatabase is basically one match per line containing a)winner name, b)loser name, c)tournament, d)other fields for the winner and loser – user3661825 Jun 19 '15 at 15:03

Without knowing what your csv file is structured, it is hard to give too much concrete. I do have some suggestions, however.

1. You can most likely drastically increase performance by converting strings like the player names to categorical data. Strings are slow in pandas, especially string lookup in a large column (as you have here many times). Using categeorical data converts it to integers seamlessly behind-the scenes, so you can benefit from using strings while still have fast lookups.
2. You should loop over the rows rather than re-indexing so much. In fact all you really need is the winner name and loser name from each match, which you can get at the beginning of each loop.
3. You may not be able to calculate the Rating all at once, but you can calculate Number of Games all at once by just counting how many times a player is a loser and adding that to how many times the same player is a winner.
4. Your other functions are one-liners. This is probably a small part, but it would be better to not have them as functions at all.
• " how many times a player is a loser and add that to how many times the same player is a loser." - do you mean "same player is a winner" ? – user1016274 Jun 19 '15 at 14:33

Maybe it's a smaller point, but I think logging things slows down performance (i.e. the print i).

• hmm.. Why not flesh that answer out a bit by running a quick benchmark? – Vogel612 Jul 15 '15 at 13:08

If you have slow code, it is usually best to first see which part of the code is slow, instead of investigating every single line. You can, for instance, use cProfile for this, see manual.

In your case, you can wrap your current code in a single function called createRanking, and then run:

import cProfile
cProfile.run('createRanking',
sort='cumtime', filename='createRankingProfile.txt')


The cProfile output is basically a binary file that contains the following information for each function called in the Python program:

• How long each call took (percall, inclusive and exclusive)
• How many times it was called (ncalls)
• How long it took (cumtime: includes the times of other functions it calls)
• How long it actually took (tottime: excludes the times of other functions)
• What functions it called (callees)
• What functions called it (callers)

The easiest way to visually see this information, is with a simple profile viewer.

1. Install cprofilev: sudo pip install cprofilev
2. Call it with your cProfile output: cprofilev /path/to/cprofile/output
3. Navigate in a browser to: http://localhost:4000

The rest speaks for itself. Just click on the links to familiarize yourself with the Tables and sorting.

Find more detailed info here.

• Welcome to Code Review! This feels in a way like a general "here is how you profile"-answer. While this can be helpful, Code Review answers are generally expected to be written specifically for the code written in the question. It would be more helpful if you would touch on specific things in the original code that can be improved. – Simon Forsberg Jul 15 '15 at 10:03
• @SimonAndréForsberg: thanks for the clarification. This is indeed general, although I did try to write how to run the profiler in this particular case. I think more specific advice is not very useful until after running a profiler. – physicalattraction Jul 15 '15 at 14:54
• +1 because way too many performance related questions do not profile at all before throwing it up here. – Emily L. Jul 15 '15 at 15:46