clock menu more-arrow no yes

Filed under:

Saberizing a Mac #3: Adding Win Shares & Other Data to the BDB

New, 27 comments

Based on the lack of comments in the previous post, I'm going to guess people had no trouble installing the Baseball Databank database. If you did, feel free to ask questions in the comments of that post. To follow the process I personally used to get it done, read this comment. All Saberizing a Mac posts can be found here.

This next challenge was suggested by Jeff (TucsonRoyal). The Baseball Databank is awesome, but what if there's additional information from other files that we want to add to the database? How do we do that? Well, let's figure it out, starting with Win Shares data provided by Dave Studeman. There are two .csv files in there, both of which include BDB ids, allowing us a way to hook into the data that's already in the BDB.

Let's tackle the total WS file first, then the WS above average. We have some choices to make. Do we want this as a separate table in the database, or do we want to add fields to an existing table? I'm not sure both are even an option with this WS data, but it's an interesting question to discuss.

As a challenge, let's see if everyone can get the data imported and then write a SQL query to find the ten oldest teams since 1980, where team age is defined as the weighted average of each player's age and their total Win Shares. As an example, to find the 2008 World Champion Phillies' weighted age, you'd multiply Chase Utley's 2008 age by his WS, add in Ryan Howard's 2008 age times his WS, add in the same thing for everybody else, then divide by the total team Win Shares. (I've chosent his definition because it requires us to use WS data, and weights team age by the players who are actually providing value to the team. You could also weight by PAs or IPs to find the team age based on playing time instead of performance.)

Calculating age can be a bit tricky, and I'll refer people once again to Colin's two primers on SQL. Colin also has some additional sample code available here. He's offered to help folks with their own queries, which can be something else that happens in the comments of this post. If you challenge yourself to come up with a query to answer an interesting question, but are struggling, ask for help. If you succeed, share the code.

Once you've got the Win Shares data incorporated, try adding some of the data Tom Tango's made available here, like primary defensive position and wOBA. And if you find any other data we can add, please share it.

Get your celebratory age-appropriate beverages chilled... set... go!

(And yes, this is something PC-users can play along with, too, although your results won't be as aestheticlly pleasing and you can't share in our yuppy attitudes.)