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

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.)

X
Log In Sign Up

forgot?
Log In Sign Up

Please choose a new SB Nation username and password

As part of the new SB Nation launch, prior users will need to choose a permanent username, along with a new password.

Your username will be used to login to SB Nation going forward.

I already have a Vox Media account!

Verify Vox Media account

Please login to your Vox Media account. This account will be linked to your previously existing Eater account.

Please choose a new SB Nation username and password

As part of the new SB Nation launch, prior MT authors will need to choose a new username and password.

Your username will be used to login to SB Nation going forward.

Forgot password?

We'll email you a reset link.

If you signed up using a 3rd party account like Facebook or Twitter, please login with it instead.

Forgot password?

Try another email?

Almost done,

By becoming a registered user, you are also agreeing to our Terms and confirming that you have read our Privacy Policy.

Join Beyond the Box Score

You must be a member of Beyond the Box Score to participate.

We have our own Community Guidelines at Beyond the Box Score. You should read them.

Join Beyond the Box Score

You must be a member of Beyond the Box Score to participate.

We have our own Community Guidelines at Beyond the Box Score. You should read them.

Spinner.vc97ec6e

Authenticating

Great!

Choose an available username to complete sign up.

In order to provide our users with a better overall experience, we ask for more information from Facebook when using it to login so that we can learn more about our audience and provide you with the best possible experience. We do not store specific user data and the sharing of it is not required to login with Facebook.

tracking_pixel_9351_tracker