Fetching NHL Play by Play game data (part 2)

December 9, 2016 2 minutes read

csv • github • hockey • javascript • jq • json • nhl • node.js

Following-up from a previous post Fetching NHL Play by Play game data where I created a Node.js app that enabled the fetching of NHL Play by Play JSON game files. The next step was to enable the parsing of JSON files into CSV files in order to do some further exploring… say like analyzing with R, load into a RDBMS, or visualizing it!

So I added a new javascript file convert.js to the existing nhlplaybyplay-node app on the GitHub repo: https://github.com/sfrechette/nhlplaybyplay-node

One important thing! I’m using jq a lightweight and flexible command-line JSON processor. You can download it here or install using [Homebrew] (http://brew.sh/)by issuing the following command: brew install jq

Essentially the following code snippet from convert.js parses a JSON Play by Play file into a CSV file format. You can give it a try, assuming you have previously ran fetch.js and have JSON files(s) in your data folder. Simply replace ${season} and ${game} accordingly and copy to a Terminal session to execute.

*If you don’t have files, or have not installed the app, you can still try it out by downloading the following file and saving it as 2016020001.json the output after executing the below snippet will look like this 2016020001.csv

jq -r --arg season ${season} --arg gameid ${game} '.data.game as $p | .data.game.plays.play[] | . as $c | 
   [$season | tonumber, $gameid | tonumber] + [([$c.aoi[] | tostring] | join(","))] + [$p.awayteamid, 
   $p.awayteamname, $p.awayteamnick, $p.hometeamid, $p.hometeamname, $p.hometeamnick] + [$c.as, 
   $c.asog, $c.desc, $c.eventid, $c.formalEventId] + [([$c.hoi[] | tostring] | join(","))] + [$c.hs, 
   $c.hsog, $c.localtime, $c.p1name, $c.p2name, $c.p3name, $c.period, $c.pid, $c.pid1, $c.pid2, 
   $c.pid3, $c.playername, $c.strength, $c.sweater, $c.teamid, $c.time, $c.type, $c.xcoord, $c.ycoord] | 
   @csv' ./data/${season}/${game}.json &gt; ./data/${season}/${game}.csv`

Snapshot image – Play by Play JSON for game 2016020001

Snapshot image – Converted 2016020001.json to 2016020001.csv

For further details on how to install and to use the app see the following README.md
Excerpt: (This assumes you already ran fetch.js and have JSON files in your data/{season} folder)

Usage

Convert Play by Play JSON to CSV
node convert.js season [game]

To convert all games for a specific season
node fetch.js 20152016

To convert a specific game from a specific season
node convert.js 20162017 2016020001

Once all the JSON files as been converted to CSV, you can issue the following command to merge all of the CSV files into one (make sure to change your directory to where the files are located)

cat *.csv > 20152016.csv

Enjoy!