Advanced Graphing Techniques Part 2 - Installing R, Linking it to Pitch F/X Database and Plotting an Graph

Update 2/17/10 - Driver needed for PC and order of commands changed

There seems to be no more questions on installing the Pitch F/X database, so I will continue on with installing and plotting in R.  I am pretty much a beginner with R, so I will be struggling with you from now on.  If anyone has any tricks or advice, please chime in.

1.  Follow Part 1 -  Advanced Graphing Techniques Part 1 - Installing a Pitch FX Database on a Computer

2.  Goto http://cran.r-project.org/ and download the version for your computer.  For Windows I selected the base version.  I will be following the Windows instructions from now on.  Others can chime in with the Apple instructions.  Once the other instructions are verified, I will add them to the article.

3. Install R following the instructions.  The only variation I took was that I installed all instruction manuals on my machine.

4. Start R and go to Packages along top tool bar.  Select a location near you under the CRAN Mirror

5. Go back to Packages and select Install Packages.  You want to add:

RODBC

Once it is added close R

5.5. Install ODBC Driver for MySQL at:

http://dev.mysql.com/downloads/connector/odbc/5.1.html

6.  Open Microsoft Windows Control Panel and then double-click the Administrative Tools icon. Double-click on the Data Sources (ODBC) icon.  Add a new User DSN called pfx with a MySQL ODBC 5.1 Diver.

7.  Once a new User DSN is created, select it and press Configure

Set the following values (I am not 100% sure these are the same for all Window machines)

Server: localhost

port: 3306

user: root

Database: php2

These are the same as the ones on the SQLyog login and the Pitch F/X dataset installed

These setting let R know what database to use.  Here is an image of the previous 2 panels discussed:

8.  Open R again

In the R console run:

library(RODBC)

channel <- odbcConnect('pfx')

odbcGetInfo(channel)

sqlTables(channel)

All 4 lines can be copy and paste at once.  These will connect R to database and let you know that they are connect.  No errors should be seen and the Pitch FX tables should be listed.

9.  Running a query.  First you need to tell R that you are plotting a query. 

Here is the query for Joe Mauer's home runs.  I was having some problems, so I added "as x" and "as y" to describe the px and pz coordinates. Note, the entire query needs to be on a single line.

query <- sqlQuery(channel, "select p.px as x, p.pz as y from atbats a join pitches p on p.ab_id = a.ab_id join games g on g.game_id = a.game_id join players pl on pl.eliasid = a.batter where pl.last = 'Mauer' and pl.first = 'Joe' and g.date like '2009%' and a.event = 'Home Run' and p.type = 'X';")

Also the extents of data can be set at this time with the xlim and ylim commands

plot(query,xlim=c(-1.5,1.5), ylim=c(0,5))

Here is the command that will draw a red strike zone at the defined points:

lines(c(1,1,-1,-1,1),c(1.5,3.5,3.5,1.5,1.5), col=c("red"))

Here is what the output should look like:

8.  Saving graphic.  Select the R - Graphic Window, go to the top toolbar and select File, Save as and then the image type you want.  The above image is from a saved file.

That is it for now.  I am sure there will be more questions this time so fire away and let us know if you find out any ideas to make process smoother or better.