Mapping MLBAM Player IDs to the Lahman Database

After Downloading Gameday Data, I wanted to make a short post about translating the Lahman database into JSON. The purpose is so that I can compare season stats from Lahman with at-bat outcomes from MLB Gameday.

If you just want to download the JSON translations, check out JSONLahman on GitHub. The

The JSON

Here’s an example of what a player’s entry looks like:

{"item":{,"KEY_RETRO":"tulot001","KEY_MLBAM":"453064","KEY_BBREF":"tulowtr01","KEY_FANGRAPHS":"3531","THROWS":"R","BATS":"R","NAMEFIRST":"Troy","NAMELAST":"Tulowitzki","KEY_LAHMAN":"tulowtr01","BIRTHYEAR":"1984","BIRTHMONTH":"10","BIRTHDAY":"10"}}

The properties with format “KEY_XYZ” are the player IDs from a variety of websites. The ones that I’m using are KEY_MLBAM and KEY_LAHMAN but I’ve included Fangraphs, BBRef, and Retrosheet for the fantasy baseball enthusiasts.

A Bit of Java Code

The first step was to get a list of all players in the MLBAM database. The Chadwick Bureau has an exhaustive and reliable list of these players, but unfortunately doesn’t include the Lahman player IDs in their database. I then wrote a bit of R code that merges this csv file with the Master.csv file from Lahman based on the Retrosheet ID.

people <- read.csv('people.csv')
master <- read.csv('master.csv')
people <- people[, c("key_mlbam", "key_bbref", "key_retro", "key_fangraphs")]
master <- master[, c("retroID", "throws", "bats", "nameFirst", "nameLast", "key_lahman", "birthYear", "birthMonth", "birthDay")]
merged <- merge(x=people, y=master, by.x = "key_retro", by.y = "retroID")
write.csv(merged, file="merged.csv")

I then imported the CSV into Razor SQL to build a Derby database that I can embed into a Java application. I could have used SQLite but I love Apache’s Java libraries.

ResultSet res = stmt.executeQuery("SELECT * FROM PLAYERS");
 File file = new File(baseLocalURL+"\\players.json");
 writeTableToFile(res, file);

You can see all the code in the GitHub repo linked above. It’s not the best code but the goal was just to get the output.

Downloading MLB Gameday Data in Java

I have a project that I’m working on that involves downloading an entire season’s worth of MLB Gameday data. These files include per-pitch data for all at bats since 2007, with increasingly detailed information being recorded every season. I’ve written a tool in Java that downloads the pertinent files for my needs.

Here’s a sample from a Rangers @ Jays game from the 2015 ALDS:

The last 3 files are the Pitch F/X data and the first two are used for at-bat and game metadata.

How to do it

If you have Java installed, the quick way is to download this and run it from your terminal or command line. The command takes two parameters: 1) the year to download and 2) the local directory to save the files.

java -jar Downloader.jar 2014 "c:\Users\majorsaber\data"

I think it took about 25 minutes for the 2014 season. The tool will continue where it left off if you have to cancel it during the download but it might leave the 1 currently downloading file corrupted. This could probably be improved by multithreading the download which is why I made the code open source.

[UPDATE: The tool is now multithreaded and can download an entire season in 5 minutes!]

How it works

GitHub Repository

The URL template for a single game’s worth of data is

http://gd2.mlb.com/components/game/mlb/year_2015/month_10/

and this is the contents of that directory. By fetching the DOM for that HTML directory listing and parsing it using jsoup, I get a list of all folders that begin with “gid_”. I can then fetch the 5 files that I need from each game’s directory using apache commons-io’s very useful copyURLToFile method.

That’s it. My next task is to map the batter and pitcher IDs from the Gameday data to that in the Lahman Database and Retrosheet so I can cross reference player stats with at-bat level data.

*bat flip*