Tag Archives: hockey

Creating a searchable and sortable list of draft-eligible CHL players using Python and AngularJS

Looking at past NHL Entry Drafts it can be found that a large number of players selected in this event come from the Canadian Hockey League (CHL), namely its three constituents: the Quebec Major Junior Hockey League (QMJHL), the Ontario Hockey League (OHL), and the Western Hockey League (WHL). A quick analysis of the last six drafts shows that a portion of more than 46% of the selected individuals had at that time been playing in on of the three major junior leagues. Even though this percentage used to be higher as other areas and leagues have become and are becoming more important providers of NHL talent, it is safe to assume that this situation won’t change in the foreseeable future.

For the devoted fan of an NHL team this is one additional reason to follow the CHL more closely. Something that – if you’re not able to attend games personally – has become more feasible with the online dissemination of game scores and player statistics. Yet while it is possible to regularly visit each league’s website to retrieve this information, I have found it unexpectedly hard to keep track of which players have already been drafted by an NHL team (as it is very common that these return to junior for one or more seasons) or are still too young to be selected in the upcoming draft. As I am not aware of any list that compiles all candidates that are eligible for the upcoming NHL Entry Draft I have decided to create such a list by myself. The result is already available, however I will try to outline my work to achieve it in the lines below.

Technologically the process introduced consists of two individual operations:

  1. On the back end we have to retrieve the data from the aforementioned websites according to a number of criteria and finally create a suitable compilation containing all desired players and accompanying information. In the example presented here we have a Python script implementation providing the collected data in a JSON file.
  2. On the front end we want to provide means for searching and sorting the compiled data presented on a corresponding website of our own. This is done using the AngularJS framework which enhances regular HTML for dynamic content display.

Back end data retrieval

Let’s start by having a look at the back end. The general workflow for the data retrieval is made up of three working steps. First we are going to retrieve a list of all teams playing in each of the concerned leagues. For each team we will then fetch roster information, i.e. all players associated with the given team. By doing so we are going to register basic information about each player, i.e. height, weight or position but also age and NHL draft status allowing for the sole selection of draft-eligible individuals. In a last step we will then retrieve up-to-date player statistics to be finally represented in the compiled list which itself will a made available as a JSON file.

The complete process is implemented in a Python script that has been made available in the Portolan GitHub repository. Here we are going to shed light on a few selected aspects of it.

To temporarily hold information I have learned to appreciate named tuples as they have been introduced in Python’s collection module with version 2.6. If you don’t need the flexibility and mutability of real objects but still want to have your data well structured and easily accessible, named tuples should be your first choice. Following are the definitions that have been made to keep information about teams, players and player statistics:

The main criterion to differentiate between players that are draft-eligible and those that are not is age. The exact rule is laid out in the NHL’s Hockey Operation Guidelines, for the upcoming draft it boils down to the following concrete dates (note that the cutoff date does not correspond with the draft date itself hence the separate definition):

(Obviously, I am a friend of the dateutil module and you should be, too.)

As with a lot of current websites, the ones of the three leagues in question don’t have their data presently available in regular HTML directives anymore but in associated data streams usually formatted in JSON notation. In our case this holds true for team overviews, roster summaries and even team player statistics. Whilst it is somewhat awkward to find these links in the first place, it is actually quite awesome for scraping as the data is already well-structured and therefore easily accessible. Hence we’re defining look up dictionaries for each league and dataset type (see source code for actual values):

The retrieval itself is actually quite straightforward and follows the workflow outlined above:

For implementation of the used functions again see the actual source code over at GitHub.

Finally we have a JSON file with all draft-eligible skaters from the major junior leagues looking like this:

Please note the selected lines that show the actual age of the player on draft day and a boolean variable indicating whether the current player is considered an overager, i.e. could have already been drafted in the previous draft.

Front end data display

The collected data can now be displayed in tabular form. Whilst using regular HTML is perfectly viable to achieve this task, the user can easily be enabled to search, filter and sort the data comfortably by utilizing AngularJS, a JavaScript framework that extends traditional HTML to allow for dynamic information display. Angular builds on the model-view-controller architecture – and it’s not my business to introduce here what has been explained much better somewhere else (for example at the Chrome Developer Page). An important feature of Angular are directives, basically additional HTML attributes that extend the behavior of the corresponding tag. Theses directives usually can be easily recognized as they are starting with the prefix ‘ng-‘. Always striving to create valid HTML I will further add ‘data-‘ to the directive as described as best practice in the AngularJS docs. Otherwise being fairly new to Angular, I have based my work on an example presented at scotch.io.

The solution I have come up with consists of three parts:

  1. An HTML page outlining the basic layout of our page (junior.html).
  2. A JavaScript file containing the logic of our solution (junior.js).
  3. The actual data – this is the JSON file that has been produced by the back end (junior.json).

A very basic version of junior.js could look like the following snippet. We just create an application called showSortJuniorApp and define the main controller. Within this controller there is just one function. It reads the JSON data file and saves its contents within the scope.

Now let’s have a look at a basic version of the accompanying junior.html. After importing CSS definitions from Bootstrap and the AngularJS source from Google, we finally include our very own JavaScript file. In the body part (among a few other things) a container element is linked with the app we created above (using the ng-app and ng-controller directives) and a table is defined and populated via the ng-repeat directive. The latter basically provides a loop over the array we loaded in our junior.js and creates a new table row for each element.

Now how to allow for sortable columns? This can be achieved quite easy. First we define a default sort criterion and order in junior.js:

We may then modify the ng-repeat directive in junior.html to make the whole table sort by points in descending order as the default configuration:

To create clickable column headings allowing for varying sort criteria an according HTML tag and the ng-click directive have to be added to each header cell of the table in junior.html:

Here we set a descending sort order on the games played column. However to allow for sorting in both directions we can set the variable to take on its negated value. See the example for draft day age above. This configuration will change the sort order every time we click on the column heading.

Finally we would like a search function allowing for the filtering of last names and a checkbox to hide overagers. To do so we first have to add a suitable form to the HTML:

After adding some variables and a short filter function to junior.js…

… we can complete the ng-repeat directive for our tabular data in the following manner:

After a few more modifications to the HTML and the JavaScript code, the final version of our front end data display also includes the ability to switch table contents between basic statistics (as used above), player information (such as height, weight, etc.) and additional information (i.e. special team stats). You may refer to the GitHub repository to review the most recent version of this solution.

An NHL hockey database using Python, PostgreSQL and SQLAlchemy (Pt. 1): Teams and Divisions

Being an avid hockey follower and fan of the National Hockey League’s Toronto Maple Leafs for more than twenty years, I have observed the recent surge of approaches to tackle the sport and its peculiarities based on application of analytical methods with great interest. Of course as a foundation of any data analysis there has to be some data first. Preferably in a standardized and suitable environment such as a relational database management system. My very personal endeavors to retrieve, structure, store and analyze hockey-related data will be subject of a larger series of posts that I am starting today. The series title already lays out the scope within this undertaking will be conducted. I will be looking at the National Hockey League as it provides (with all of its shortcomings) the most detailed data available in this regard. Python as programming language, PostgreSQL as database system and SQLAlchemy as SQL and mapping framework connecting both of the formerly mentioned will serve as my technology stack. But let’s get it on already.

As first part of this series I will be looking at NHL teams and divisions. This is fitting for the beginning as the accompanying data is not very complex and particularly static as well, i.e. it changes with very low frequency. We may provide a simple database entity, i.e. table, containing basic team data using the following SQL data definition statement:

As you can see this table design accounts for the fact that from time to time franchises move around or change their names. In case a certain team is the current reincarnation of a given franchise, both became_team and end_year contain NULL values thus marking it as active.

To whom it may concern: Differentiating between the various abbreviation types is necessary due to the NHL using different ways of shorting a team name on game reports (N.J, L.A,…) and in URLs (NJD, LAK,…). Additionally I wasn’t happy with some of their choices for abbreviations so that I created my very own column.

Update (February 2016): With the current update of the NHL’s website the previous system of using abbreviations for team identification was abolished and actual team (and franchise) ids have been introduced. The according changes in the data model are marked above. As far as I have grasped the url_abbr column should no longer be necessary, I will keep it for safety and nostalgic reasons though.

After setting up the table data insertion may be conducted easily by preparing and applying the according SQL insert statements. Both nhl_id and franchise_id values have been retrieved from the NHL’s website.

A table containing NHL divisions may be set up using the following definition:

Here a particular property of PostgreSQL is visible: the ability to store multidimensional arrays of varying length in a database column. In this case the teams column will contain the IDs of the teams that set up a certain division at a certain time. Unfortunately there is currently no support for referential integrity between the elements of an array (here: team_ids in column teams of table nhl_divisions) and the elements of another table (column team_id in table nhl_teams). Although amends have been made to introduce this feature in more than just one of the more recent releases, it hasn’t become part of the code base yet.

We will deal with inserting data into this table later on.

Subsequently an (updated) ERD of the current database configuration looks like the following:


Using SQLAlchemy it is now very easy to map these tables to Python classes. While it is very viable to use SQLAlchemy magic to set up the table structures from Python, I personally like to go the other way around: Having tables already defined in the database system and mapping it to Python by metadata reflection. Everything that is necessary to do so is the mapped class being inherited from the SQLAlchemy Base class and it having two special attributes __tablename__ and __autoload__.

We may now add a way to search for team objects in the database, i.e. by implementing according class methods to search the database using an abbreviation or team id. Additionally we will add means to make teams comparable and sortable by using Python’s comparison methods.

Please note that this use of Session is actually not recommended. Instead a scoped session should be your choice in production circumstances. However here the more simple approach has been used here for the sake of clarity.

It is now possible to find an NHLTeam object by specifying its abbreviation:

This gives you:

Similarly we may map the database table containing NHL Division information with an according Python class. As we’re creating division data from Python as well, we’re including a real constructor this time:

Given definitions and mappings for both team and division objects and having some source data ready, it is now convenient to insert division data using a special helper method:

Now if we finally want to determine the league configuration for a given season we can do so by linking division data and the year in question by providing the NHLDivision object definition with the following class method:

Accordingly this results in:

Class definitions for both NHLTeam and NHLDivision are available via GitHub. In the next installment of this series we will have a look at the table and class definition for NHL player data.