2007-2009 Pitch F/X Download Available (.sql)

I finally got a shockey monkey to get my brother going and he has the 2007 to 2009 Pitch F/X data ready for download. He has included the scripts which he used to speed up the download process.

Once the retrosheet data is out for 2009, we will make the entire dataset available. Due to its large size, we will hopefully be making a smaller version of just 2000 to 2009 data available this year as some people have requested. It all depends on how well the shockie monkey works out.

over 2 years ago Jeff Zimmerman 9 comments 2 recs |

Story-email Email Printer Print

Comments

Fantastic

This is great. I don’t need it because I already have it…. but the hassle I had to deal with to get it makes this great for those who didn’t want to deal with all of the hassle but want the data.

www.redsminorleagues.com
www.FirstInning.com

by dougdirt on Oct 26, 2009 2:33 PM EDT reply actions

Beginner question...

Formerly known as "Andersklasen."
Check out the best Twins' blog on the web: TwinsTarget.com.

by TwinsTarget on Oct 27, 2009 12:27 AM EDT reply actions

Didn't mean to post that yet...

Beginner question: Is there anything like a “table definition” so I can know how to write queries and draw certain information?

Formerly known as "Andersklasen."
Check out the best Twins' blog on the web: TwinsTarget.com.

by TwinsTarget on Oct 27, 2009 12:28 AM EDT up reply actions

Here is one for the Pitches table:

http://fastballs.wordpress.com/2007/08/02/glossary-of-the-gameday-pitch-fields/

If you have any question on what exactly you are looking to do in particular, ask away.

Jeff Zimmerman - Protecting the world from RBI's and Wins from my mom's guest house.

by Jeff Zimmerman on Oct 27, 2009 12:40 AM EDT up reply actions

Thanks, will do!

I’m looking forward to diving into this stuff. I’ve touched the surface with season-by-season data, but pitch fx looks to be an entirely different beast.

Formerly known as "Andersklasen."
Check out the best Twins' blog on the web: TwinsTarget.com.

by TwinsTarget on Oct 27, 2009 12:46 AM EDT up reply actions

Here's a question:

I have the data, and am having a blast sorting by description, speed, break, and all that fun stuff, but what would I add in the “WHERE” clause that would give me results from a particular pitcher/team/year? Is this even possible?

Formerly known as "Andersklasen."
Check out the best Twins' blog on the web: TwinsTarget.com.

by TwinsTarget on Oct 27, 2009 1:10 AM EDT reply actions

Basically, what do I match up in the JOIN clause?

FROM pitches p JOIN players a ON p.id = a.lahmanid

What is lahmanid, and where are the names/id given in the pitches table?

Formerly known as "Andersklasen."
Check out the best Twins' blog on the web: TwinsTarget.com.

by TwinsTarget on Oct 27, 2009 1:15 AM EDT up reply actions

To get the Pitcher/Team/Batter/ Year

The following query is the one I used, with the date add per your request, is the one I used in the Vlad Strike zone Judgment article. You can see that you need to join almost all the tables using the game date information for year and the players table to get name.

select p.des,p.px,p.pz from
atbats a
join pitches p on a.ab_id = p.ab_id
join games g on a.game_id = g.game_id
join players pl on a.batter = pl.eliasid
where
g.date like ‘2009%’and
((px > 1.2 and px < 2 and pz <4.5 and pz >.5)
or (px < -1.2 and px > -2 and pz <4.5 and pz >.5)
or (px >= -1.2 and px <= 1.2 and pz > 3.5 and pz <4.5)
or (px >= -1.2 and px <= 1.2 and pz > .5 and pz <1.5))
and pl.last = ‘Mauer’
order by p.des

Jeff Zimmerman - Protecting the world from RBI's and Wins from my mom's guest house.

by Jeff Zimmerman on Oct 27, 2009 1:59 AM EDT up reply actions

Jeff, or Harry, or anyone

I’ve been thinking it’s a good idea to extract player stats using PItch f/x data. That way it updates throughout the season, and you can get exactly what you want. I figured out a pretty easy way to do so by using a bunch of SUM(IF( commands in the SELECT clause, so I’m good with that; the only problem is that I can’t figure out a way to count R and ER totals by pitcher.

In the “des” field in the “atbats” table, it provides a description of the play, and what happened. For example: