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)
,BAT_SAFE_ERR_FL VARCHAR(1)
,BAT_FATE_ID INTEGER
,RUN1_FATE_ID INTEGER
,RUN2_FATE_ID INTEGER
,RUN3_FATE_ID INTEGER
,FATE_RUNS_CT INTEGER
,ASS6_FLD_CD INTEGER
,ASS7_FLD_CD INTEGER
,ASS8_FLD_CD INTEGER
,ASS9_FLD_CD INTEGER
,ASS10_FLD_CD INTEGER
,PRIMARY KEY (GAME_ID, EVENT_ID)
);

I seriously need to get to bed - I’ll try and post something on how to load the data in MySQL tomorrow.

For an Oracle database, defining the numeric fields as date type PLS_INTEGER(1) or PLS_INTEGER(2) is preferable to “INTEGER”, which supposedly is treated by Oracle as an alias for NUMBER(38) - much bigger than needed and not actually an integer. In mysql, the equivalent is smallint(1) or smallint(2).

My problem with the smallint is when you start to do sum(myCol).  With 8 million records in the database, the datatype of the smallint may carry through to the sum column.

I should look into making the database leaner, though.

***

Colin: the memory requirement looks ambiguous, but you will not suffer any kind of degradation if you have too much memory.  As for the multiple CPU, that is an Oracle “honor system” requirement to limit the use of the database to single non-power users.  As for the 4GB, you will get pretty close with the Retro database.  With 8 million records, you just need 500 bytes per record to reach that limit.  I think I’m pretty close to that, though I’ll have to check at home.  Of course, you don’t have to load the entire set, or you can go to MySQL or the other free ones out there that don’t have such a restriction.

Regarding the database size, can Access handle something this large, or will I need to get MySQL?

I have found that when you get passed 1 million records, Access starts to choke.  In the current years, and season is almost 200,000 records.

On the other hand, Access DOES allow you to connect through an ODBC connection to other databases (easy to setup).  So, you can use the wonderful front-end of Access, to get to your other databases.

I was a little sloppy with FLD_xx

Any field that ends with… means…
FLD_ID: his 8 character player id, as a text string
FLD_CD: numeric fielding position

So, it looks like I have it right in some places, and wrong in others.  If CWEVENT is spitting out a player id, then the field should end with FLD_ID as a VARCHAR2(8).  If CWEVENT is spitting out a position, then the field should end with FLD_CD as an INTEGER.

I’ll correct that tonight.

***

You will note a certain standard in the field names.  The last two characters all end with one of:
_CD: a code
_CT: a counter, number
_FL: a switch, flag
_ID: an identifier (names, teams, etc)
_TX: a string, text

Based on this, we know basically what the field is about.

The set of characters directly preceding the suffix is the “object”.  So,
BAT_ID
RESP_BAT_ID
REMOVED_FOR_PH_BAT_ID

All refer to the actual batter’s player ID.  The text preceding “BAT_ID” gives it additional context.  So, the first field is strictly the batter.  The second field is the “responsible” batter (for cases in mid-PA switches).  And the third field is the batter who was removed (not the pinch hitter).

Similarly:
BASE1_RUN_ID
BASE2_RUN_ID
BASE3_RUN_ID

Here we are talking about the player id of a runner.  The BASE1 refers to the runner on first base.

If you think that some of my names are not consistent, please speak up.  Once we’re done here, this will be pretty much set in stone.  Changing field names is not something that we like to do.

The full description of each field can be obtained by running CWEVENT with the -h flag.

These are the corrections to the datatype:
ALTER TABLE EVENTS
MODIFY (ERR1_FLD_CD INTEGER )
MODIFY (ERR2_FLD_CD INTEGER )
MODIFY (ERR3_FLD_CD INTEGER )
MODIFY (BAT_FLD_CD INTEGER )
MODIFY (RUN1_FLD_CD INTEGER )
MODIFY (RUN2_FLD_CD INTEGER )
MODIFY (RUN3_FLD_CD INTEGER )

The field names are correct.  If you already loaded the table, you may be prevented from changing the datatype.  The easiest way out of the mess is to start from scratch.

***

The Retrosheet schema takes up 3.9 GB in Oracle.  Add in the sysadmin schemas and the temp schema, and you’ll be above the 4GB limit.  I intend to create additional tables, so look for a database that can handle more than 5 or 6 GB.

ALTER TABLE EVENTS
RENAME COLUMN RUN1_LINEUP_CD to RUN1_LINEUP_ID
RENAME COLUMN RUN2_LINEUP_CD to RUN2_LINEUP_ID
RENAME COLUMN RUN3_LINEUP_CD to RUN3_LINEUP_ID

I’ll be taking my posts here:

Until I’ve got something more production-ready, at which point I’ll make an announcement here.

I don’t think my thread here was the right format.

I updated the wiki:

http://www.tangotiger.net/wiki/index.php?title=Retrosheet_Database

I wasn’t sure what thread I should post this. I noticed an error with the Chadwick program when processing plays where 99 (unknown fielding credit) is listed in the event text. Chadwick mistakengly assigns a putout and an assist to the rightfielder (pos #9) on these plays. It also mistakengly assigns a 9 in the FLD_CD column when the BAT_EVENT_FL is set to T.

Great job.  I will alert Ted, and check this out myself tonight.

I posted your comment to Retrolist, and I also said:

“We discussed this last year, and when I process this data, I ignore anything in the putout/assist fields on the “99” plays.  So, I don’t know if this is an “error” so much as a design decision (of CWEVENT and BEVENT).  If I remember right, a value was needed so the counts are correct.  And for whatever reason, the choice was limited to 1-9.”

The fielding portion of a few event text’s would start with 99, like a catch and baserunner kill by the rightfielder. I wonder if the unknown fielding credit flag is set to T for these plays. If not, then you would want to make sure you use the unknonw fielding credit flag to find these plays, not just the event text, as the event text would show a few false positives for those plays where the fielding credit starts with 99.

Ok.  I’ve done this last year and I was trying to update it this year and running into issues.

1.  I’m using Mysql
2.  I’ve downloaded the cwevent,cwgame files etc etc.
3.  I downloaded the event files
4.  I unzipped them
5.  I have in the parse folder all2005 to all2009.csv, games2005 to games2009.csv, and subs2005 to subs2009.csv.

6. I used Colins events table to create the table.

thats about where I’m at.  I’m having issues loading the csv files into Mysql.  Do I need to import the files one by one?  Whats the difference between all2005, games2005 and subs2005?  Do I need them all?  Do I need any other table but “events”?  Any help would be greatly appreciated.