THE BOOK--Playing The Percentages In Baseball

Why don’t you just do what was in Mark Adler’s book?  He provides code and the way he does it the database will stay up to date each year and there are less manual steps.  Or at least use it as a starting point.  He also used the simpler for beginners and free MySQL.

Joe Adler.  I’ve hyped Joe’s book several times in my blog, and recommend it to anyone.

***

This blog entry is a result of this hypotethetical exchange:

Fan: “Tango, can I see your work?”
Me: “Sure.”

It does *not* include this exchange:

Fan: “Uh, great.  Now that you opened it up for me, can you change your database, and make your processes more automated?  And make sure it updates annually.”
Me: “Sure.”

Looks great so far. I use MySQL at home, and so when I get off work tonight I should be able to provide anyone that wants it with the “conversions.”

I made updates to the main blog entry.  I might just post links to the files instead of embedding the text within the blog entry.

I noticed a couple of fields I made a string, but they should be integers.  Such is the curse of my releasing beta-instructions.

I suggest that if you load your database, only do it with the 2007 data.  Things will change.

Tom,

Since I clearly don’t know everything, I’m assuming you’re using a Mac or other UNIX-based system, correct? Just want to clear before I comment with the Windows-based solutions I’m working with, though most of them appear to be in Joe’s book in some form.

He’s using batch files, so that means he’s using DOS/Windows. He’s just using Oracle, a heavy-duty database far beyond the reach (or needs, really) of most of us mortals. That said, everything he uses to generate the CSV files with Chadwick will apply to any database system (even Access, if you’d like.) It’s getting those CSV files into the database that won’t work as-is on other systems.

I’m using Windows, though all this should be portable to *NIX.

There are basic free versions of Oracle available, like Oracle Express.

Apparently so.

“Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine.”

If all you’re using it for is one Retrosheet database, I can’t see you running into the 4gb limit. If you have multiple CPUs or more than a gig of RAM, you may notice a performance decline, I suppose.

Thanks for the clarification. I too, like Colin, am using MySQL to try and load the event files. I also am using the Perl scripts from Baseball Hacks, available from the book’s website. (I’m still illiterate as to adding a link here, so feel free to do so for me).

Colin, what challenges did you run into with MySQL?

Matt: I used BEVENT and a different set of definitions. I’ve done my best to duplicate Tango’s database definition in MySQL. Here goes:

CREATE TABLE events (
SEQ_EVENTS INTEGER NOT NULL UNIQUE KEY AUTO_INCREMENT
,GAME_ID VARCHAR(12)
,AWAY_TEAM_ID VARCHAR(3)
,INN_CT INTEGER
,BAT_HOME_ID INTEGER
,OUTS_CT INTEGER
,BALLS_CT INTEGER
,STRIKES_CT INTEGER
,PITCH_SEQ_TX VARCHAR(40)
,AWAY_SCORE_CT INTEGER
,HOME_SCORE_CT INTEGER
,BAT_ID VARCHAR(8)
,BAT_HAND_CD VARCHAR(1)
,RESP_BAT_ID VARCHAR(8)
,RESP_BAT_HAND_CD VARCHAR(1)
,PIT_ID VARCHAR(8)
,PIT_HAND_CD VARCHAR(1)
,RESP_PIT_ID VARCHAR(8)
,RESP_PIT_HAND_CD VARCHAR(1)
,POS2_FLD_ID VARCHAR(8)
,POS3_FLD_ID VARCHAR(8)
,POS4_FLD_ID VARCHAR(8)
,POS5_FLD_ID VARCHAR(8)
,POS6_FLD_ID VARCHAR(8)
,POS7_FLD_ID VARCHAR(8)
,POS8_FLD_ID VARCHAR(8)
,POS9_FLD_ID VARCHAR(8)
,BASE1_RUN_ID VARCHAR(8)
,BASE2_RUN_ID VARCHAR(8)
,BASE3_RUN_ID VARCHAR(8)
,EVENT_TX VARCHAR(100)
,LEADOFF_FL VARCHAR(1)
,PH_FL VARCHAR(1)
,BAT_FLD_CD VARCHAR(2)
,BAT_LINEUP_ID INTEGER
,EVENT_CD INTEGER
,BAT_EVENT_FL VARCHAR(1)
,AB_FL VARCHAR(1)
,H_CD INTEGER
,SH_FL VARCHAR(1)
,SF_FL VARCHAR(1)
,EVENT_OUTS_CT INTEGER
,DP_FL VARCHAR(1)
,TP_FL VARCHAR(1)
,RBI_CT INTEGER
,WP_FL VARCHAR(1)
,PB_FL VARCHAR(1)
,FLD_CD INTEGER
,BATTEDBALL_CD VARCHAR(1)
,BUNT_FL VARCHAR(1)
,FOUL_FL VARCHAR(1)
,BATTEDBALL_LOC_TX VARCHAR(5)
,ERR_CT INTEGER
,ERR1_FLD_CD VARCHAR(8)
,ERR1_CD VARCHAR(1)
,ERR2_FLD_CD VARCHAR(8)
,ERR2_CD VARCHAR(1)
,ERR3_FLD_CD VARCHAR(8)
,ERR3_CD VARCHAR(1)
,BAT_DEST_ID INTEGER
,RUN1_DEST_ID INTEGER
,RUN2_DEST_ID INTEGER
,RUN3_DEST_ID INTEGER
,BAT_PLAY_TX VARCHAR(8)
,RUN1_PLAY_TX VARCHAR(15)
,RUN2_PLAY_TX VARCHAR(15)
,RUN3_PLAY_TX VARCHAR(15)
,RUN1_SB_FL VARCHAR(1)
,RUN2_SB_FL VARCHAR(1)
,RUN3_SB_FL VARCHAR(1)
,RUN1_CS_FL VARCHAR(1)
,RUN2_CS_FL VARCHAR(1)
,RUN3_CS_FL VARCHAR(1)
,RUN1_PK_FL VARCHAR(1)
,RUN2_PK_FL VARCHAR(1)
,RUN3_PK_FL VARCHAR(1)
,RUN1_RESP_PIT_ID VARCHAR(8)
,RUN2_RESP_PIT_ID VARCHAR(8)
,RUN3_RESP_PIT_ID VARCHAR(8)
,GAME_NEW_FL VARCHAR(1)
,GAME_END_FL VARCHAR(1)
,PR_RUN1_FL VARCHAR(1)
,PR_RUN2_FL VARCHAR(1)
,PR_RUN3_FL VARCHAR(1)
,REMOVED_FOR_PR_RUN1_ID VARCHAR(8)
,REMOVED_FOR_PR_RUN2_ID VARCHAR(8)
,REMOVED_FOR_PR_RUN3_ID VARCHAR(8)
,REMOVED_FOR_PH_BAT_ID VARCHAR(8)
,REMOVED_FOR_PH_BAT_FLD_CD VARCHAR(8)
,PO1_FLD_CD INTEGER
,PO2_FLD_CD INTEGER
,PO3_FLD_CD INTEGER
,ASS1_FLD_CD INTEGER
,ASS2_FLD_CD INTEGER
,ASS3_FLD_CD INTEGER
,ASS4_FLD_CD INTEGER
,ASS5_FLD_CD INTEGER
,EVENT_ID INTEGER
,HOME_TEAM_ID VARCHAR(3)
,BAT_TEAM_ID VARCHAR(3)
,FLD_TEAM_ID VARCHAR(3)
,BAT_LAST_ID INTEGER
,INN_NEW_FL VARCHAR(1)
,INN_END_FL VARCHAR(1)
,START_BAT_SCORE_CT INTEGER
,START_FLD_SCORE_CT INTEGER
,INN_RUNS_CT INTEGER
,GAME_PA_CT INTEGER
,INN_PA_CT INTEGER
,PA_NEW_FL VARCHAR(1)
,PA_TRUNC_FL VARCHAR(1)
,START_BASES_CD INTEGER
,END_BASES_CD INTEGER
,BAT_START_FL VARCHAR(1)
,PIT_START_FL VARCHAR(1)
,RUN1_FLD_CD VARCHAR(2)
,RUN1_LINEUP_CD INTEGER
,RUN1_ORIGIN_EVENT_ID INTEGER
,RUN2_FLD_CD VARCHAR(2)
,RUN2_LINEUP_CD INTEGER
,RUN2_ORIGIN_EVENT_ID INTEGER
,RUN3_FLD_CD VARCHAR(2)
,RUN3_LINEUP_CD INTEGER
,RUN3_ORIGIN_EVENT_ID INTEGER
,PA_BALL_CT INTEGER
,PA_INTENT_BALL_CT INTEGER
,PA_PITCHOUT_BALL_CT INTEGER
,PA_OTHER_BALL_CT INTEGER
,PA_STRIKE_CT INTEGER
,PA_CALLED_STRIKE_CT INTEGER
,PA_SWINGMISS_STRIKE_CT INTEGER
,PA_FOUL_STRIKE_CT INTEGER
,PA_OTHER_STRIKE_CT INTEGER
,EVENT_RUNS_CT INTEGER
,FLD_ID VARCHAR(8)
,BASE2_FORCE_FL VARCHAR(1)
,BASE3_FORCE_FL VARCHAR(1)
,BASE4_FORCE_FL VARCHAR(1)