Getting some results

My previous post was all about setting up MySQL and importing the stats through Python into the SQL databases.  The next steps took me a bit more time.  In fact, I'm still having a bit of trouble getting the stats to add up to those shown on NFL websites.  I don't think it's a problem with the data, just my abilities to code and understand the data.  There are subtleties in extracting stats, a few examples:

• When a QB kneels it counts as a rush, and the yardage goes against the QB's rushing yards
• For punts and kickoffs the receiving team is on Offense, yet for an onside kick the kicking team is on Offense
• And few others I can't remember off the top of my head

But extracting stats for most players like Tom Brady provides correct results.  Sometimes the stats differ, and I think it has to do with some additional rules like those above.  But these are things I can work out as I go, and with all of the stats (mostly correct) I can start looking at stats.  I will forgo putting up the Python code that extracts the stats as it's not very interesting to really look at unless you want to see how I applied the logic to extract a stat.  

Alright, here's to my first plot.  First, here's a list of the tables in my mysql:

mysql> show tables;
+---------------------+
| Tables_in_pbp       |
+---------------------+
| game_index          |
| play_by_play        |
| player_roster       |
| yahoo_defense_stats |
| yahoo_kicker_stats  |
| yahoo_offense_stats |
+---------------------+
6 rows in set (0.01 sec)

The first three are related to the stats I loaded from CSV files (see previous post).  The last three are the stats for each player for each game played (currently just 2010 season) for Offense, Kicker, and Defense players.  The code below extracts the score (for the Yahoo league scoring) and plots a histogram.  It uses code I wrote in "queryStats.py" that has the functions to extract the stats from the yahoo databases and calculate the score for a single game or for all games in a year.  The plot created is only on the offensive player stats.  Here's the plot from the code below:

First, there are a lot of players with zero points, here's the plot when removing those players.

This is what I expect to see, most players don't provide a lot of scores and only a few (e.g. Tom Brady) provide scores in the 300s.  Here's the plot when the bins are bigger:

Now it's easier to see that approximately 300 players provide only around 60 points or less in 2010, while approximately 100 players provide 150 points or more.  Basically there aren't very many Tom Brady's, or this year's star Aaron Rodgers, in the NFL.  The sample mean of the distribution is 68 with a standard deviation of 67, but  clearly this distribution is not normal and looks exponential.  The next post will look at this distribution for game day points.

The code is below.  One side note, the yahoo databases I created only provide a GAME ID and NAME to identify the player.  Unfortunately this was not enough to distinguish between 'C.Johnson' of Detroit and 'C.Johnson' of Carolina.  I did a joint search between the GAME INDEX database that has the teams playing for a specific GAME ID to mitigate this problem.  Clearly this code below will struggle when Carolina and Detroit play each other.  Not sure if I need to redesign the databases (it takes a few hours to extract the stats for each game) or improve my sql search since most of the information is there to disambiguate the search.

import numpy as np
import MySQLdb as mdb
import queryStats as qstats
import matplotlib.mlab as mlab
import matplotlib.pyplot as plt

class Players:
    def __init__(self,name,pos,team):
        self.name = name
        self.pos = pos
        self.team = team
        self.stats = None
        self.score = 0
    def getScore(self,conn):
        if self.pos != 'DEF' or self.pos !='K':
            player = qstats.Player(name)
        elif self.pos == 'K':
            player = qstats.Kicker(name)
        else:
            player = qstats.Defense(name)

        self.stats = player

        db = player.db
        sql = "SELECT %s.* \
        FROM game_index,%s \
        WHERE game_index.GAME_ID=%s.GAME_ID \
        AND (game_index.H=\'%s\' OR game_index.V=\'%s\') \
        AND %s.NAME=\"%s\";"%(db,db,db,team,team,db,name)
        try:
            cursor = conn.cursor()
            cursor.execute(sql)
            games = cursor.fetchall()
            for game in games:
                player.addGameStats(game)
            self.score = player.getScore()
            cursor.close()
        except mdb.Error,e:
            print sql 
            print e


if __name__ == '__main__':
    conn = mdb.connect(host='localhost',user='foo',passwd='som_pass', db='pbp');
    
    qbsql = "SELECT DISTINCT NAME,POS,TEAM FROM player_roster WHERE POS='QB' OR POS='WR1' OR POS='WR2' OR POS='TE' OR POS='RB'"
    score = []
    try:
        cursor = conn.cursor()
        cursor.execute(qbsql)
        qbs = cursor.fetchall()
        for qb in qbs:
            name = qb[0]
            pos = qb[1]
            team = qb[2]
            player = Players(name,pos,team)
            player.getScore(conn)
            score.append(player.score)        
        cursor.close()
    except mdb.Error,e:
        print sqlstr
        print e
    conn.close()

    plt.figure(facecolor='w',dpi=80)
    n, bins, patches = plt.hist(score, 50)
    plt.grid(True)
    plt.title('Offense Fantasy Scoring 2010')
    plt.xlabel('Fantasy Points')
    plt.ylabel('Number of Players')
    plt.show()