ghcoders dot net

To sort of go along with the fantasy football related programming post from earlier, I also like to do a set of “power rankings” for the NFL using a fairly simple but data-heavy formula that I derived via a bit of trial and error and really just some thought exercises. For the past season or so I just maintained it using a simple spreadsheet (at first Excel, then Gnumeric once I converted my laptop to Linux after getting the new job), but even when I was doing it I noticed data inconsistency which almost definitely resulted from data entry errors on my part. For example, part of the formula involves a teams “points for” (points they score against other teams) and “points against” (points other teams score against them). Well, when you sum up the “points for” for all the teams, and when you sum up the “points against” for all of the teams, you should get the same number. But sometimes I wouldn’t. Those errors weren’t quite as nefarious to track down as other things like in my strength of schedule calculations the total W-L record of one component should be the mirror opposite of another, but it would be missing some wins somewhere.

So, rather than set up some crazy macros or extensive conditional formatting that will simply alert me when I had data inconsistencies I figured, “hey, hunting down these inconsistencies will be a lot easier if it’s bucketed into individual games and has a structure I can efficiently query.” So, I figured I’d cram the data into a database, and since I’ve had pleasant experiences with Django in the past, and I wanted an easier way to view/extract/modify the data than just via DB queries I decided to use it to build a quick and ugly web interface to it.

Most of the initial work was doable within an hour or two, just laying out the models and such. The built in admin interface is really nice, though there was one case where I thought I could use the edit_inline property on some of the ForeignKey fields I had, but it doesn’t look like I can flex it like that. Basically, I was hoping the admin interface would allow me to add in the relevant game results/statistics for this model:

class Game(models.Model):
    week = models.ForeignKey(Week)
    home = models.ForeignKey(TeamGame, related_name='home')
    away = models.ForeignKey(TeamGame, related_name='away')

with the TeamGame class being the individual representation of how a team performed in a given game:

class TeamGame(models.Model):
    team = models.ForeignKey(Team)
    points = models.IntegerField()
    yards = models.IntegerField()
    turnovers = models.IntegerField()

I used a similar data model for the poker league tracking site I wrote in Django about a year ago, only since it was a less-strict “many-to-one” (in that case, PlayerGame to Game) relationship instead of the strict one home, one away “two-to-one” relationship we have here, it makes using edit_inline unworkable, at least the way I understand it and the way I used it on the other site. But, writing a custom view to add the data was relatively simple, and I think I could have even just used the generic CRUD views provided, but I just wrote my own instead.

What makes me wonder if this data model/schema is the “right way” to do it is the fact that extracting some of the necessary data to do the rankings gets kind of cumbersome, probably because of all the normalization (though really normalization is supposed to be the big win here, remember). For example, the next page I wrote after the “add a game’s stats” page was one to view the year-to-date statistics of a given team for each week. And it’s not as clean and pretty as I’d like it to be. In fact, it’s kind of warty-looking, though it’s not too bad in the actual ORM syntax, even though I did resort to using the Q() method, because remember, I want the opponent info as well as the actual team’s info from each game that they’re in, which is what the Game objects are pulled for (and then I assign the current team’s TeamGame for that particular Game in the my_tg variable, and the opponent’s TeamGame in opp_tg):

def display_team(request, team_id):
    team = get_object_or_404(Team, pk=team_id)
    teamgames = TeamGame.objects.filter(team=team)
    games = Game.objects.filter(Q(home__in=teamgames) | Q(away__in=teamgames))
    tmp_games = []
    totals = {'pts_for': 0, 'pts_against': 0,
              'yds_for': 0, 'yds_against': 0,
              'to_margin': 0}
    for game in games:
        if team == game.home.team:
            opp_tg = game.away
            my_tg = game.home
        else:
            opp_tg = game.home
            my_tg = game.away
        if my_tg.points > opp_tg.points:
            result = 'W'
        else:
            result = 'L'
        tmp_games.append({'opponent': opp_tg.team,
                          'result': '%s, %s - %s' % (result, my_tg.points,
                                                      opp_tg.points),
                          'pts_for': my_tg.points,
                          'pts_against': opp_tg.points,
                          'yds_for': my_tg.yards,
                          'yds_against': opp_tg.yards,
                          'to_margin': opp_tg.turnovers - my_tg.turnovers,})
        totals['pts_for'] += my_tg.points
        totals['pts_against'] += opp_tg.points
        totals['yds_for'] += my_tg.yards
        totals['yds_against'] += opp_tg.yards
        totals['to_margin'] += (opp_tg.turnovers - my_tg.turnovers)
    num_games = float(games.count())
    averages = {'pts_for': totals['pts_for']/num_games,
                'pts_against': totals['pts_against']/num_games,
                'yds_for': totals['yds_for']/num_games,
                'yds_against': totals['yds_against']/num_games,}
    return render_to_response('rankings/team.html',
                              {'team': team, 'games': tmp_games,
                               'totals': totals, 'averages': averages})

Granted, a good portion of that is all that dict assignment junk so that I can have a much cleaner looking template file with less “business logic” in it and more pure iteration over results. But it just seems like an unhealthy amount of logic for what is pretty darn simple. But it does make for a pretty simple/clean template:

<h2>{{ team }}</h2>

Games
<table>
    <thead>
        <tr>
            <td>Opponent</td>
            <td>Result</td>
            <td>Yards For</td>
            <td>Yards Against</td>
            <td>TO Margin</td>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <td>TOTALS</td>
            <td>{{ totals.pts_for }} - {{ totals.pts_against }}</td>
            <td>{{ totals.yds_for }}</td>
            <td>{{ totals.yds_against }}</td>
            <td>{{ totals.to_margin }}</td>
        </tr>
        <tr>
            <td>AVERAGES</td>
            <td>{{ averages.pts_for }} - {{ averages.pts_against }}</td>
            <td>{{ averages.yds_for }}</td>
            <td>{{ averages.yds_against }}</td>
            <td></td>
        </tr>
    </tfoot>
    <tbody>
    {% for game in games %}
        <tr>
            <td>{{ game.opponent }}</td>
            <td>{{ game.result }}</td>
            <td>{{ game.yds_for }}</td>
            <td>{{ game.yds_against }}</td>
            <td>{{ game.to_margin }}</td>
        </tr>
        </tr>
    {% endfor %}
    </tbody>
</table>

So I like the result on the template side, but seeing as how I had to do something very similar with my poker site in order to get all the game data for the “display player info” page on that site (that is, aggregate stuff into a temp list or dict and churn a lot of data basically), I’m wondering if there’s a better way to do the view without making the template code crazy complex.