Familiar with Retrosheet? Extracting save opportunities - Fantasy Baseball Cafe 2015 Fantasy Baseball Cafe

Return to Baseball Leftovers

Familiar with Retrosheet? Extracting save opportunities

Moderator: Baseball Moderators

Familiar with Retrosheet? Extracting save opportunities

Postby MikeBillsFan » Wed Jan 06, 2010 11:25 am

I've extracted all of the 2009 event and game files from Retrosheet.org and created MySQL tables as follows:

create table retrosheet_bevent (
EventID int(8) not null auto_increment,
game_id char(12) not null,
visiting_team char(3) not null,
inning int(2) not null,
batting_team int(1) not null,
outs int(1) not null,
balls int(1) not null,
strikes int(1) not null,
vis_score int(2) not null,
home_score int(2) not null,
res_batter varchar(8) not null,
res_batter_hand enum('R','L') not null,
res_pitcher varchar(8) not null,
res_pitcher_hand enum('R','L') not null,
first_runner varchar(8),
second_runner varchar(8),
third_runner varchar(8),
event_text varchar(64) not null,
leadoff_flag enum('T','F') not null,
pinch_hit_flag enum('T','F') not null,
defensive_position int(2) not null,
lineup_position int(1) not null,
event_type int(2) not null,
batter_event_flag char(1) not null,
ab_flag enum('T','F') not null,
hit_value int(1) not null,
sh_flag enum('T','F') not null,
sf_flag enum('T','F') not null,
outs_on_play int(1) not null,
rbi_on_play int(1) not null,
wild_pitch_flag enum('T','F') not null,
passed_ball_flag enum('T','F') not null,
num_errors int(1) not null,
batter_dest int(1) not null,
first_runner_dest int(1) not null,
second_runner_dest int(1) not null,
third_runner_dest int(1) not null,
first_runner_sb_flag enum('T','F') not null,
second_runner_sb_flag enum('T','F') not null,
third_runner_sb_flag enum('T','F') not null,
first_runner_cs_flag enum('T','F') not null,
second_runner_cs_flag enum('T','F') not null,
third_runner_cs_flag enum('T','F') not null,
first_runner_picked_off_flag enum('T','F') not null,
second_runner_picked_off_flag enum('T','F') not null,
third_runner_picked_off_flag enum('T','F') not null,
first_runner_pitcher varchar(8),
second_runner_pitcher varchar(8),
third_runner_pitcher varchar(8),
primary key (EventID)

create table retrosheet_bgame (
game_id varchar(12) not null,
game_date date not null,
game_number int(1) not null,
day_of_week enum('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') not null,
start_time int(4) not null,
dh_used_flag enum('T','F') not null,
day_night_flag enum('D','N') not null,
visiting_team char(3) not null,
home_team char(3) not null,
game_site char(5) not null,
vis_starting_pitcher varchar(8) not null,
home_starting_pitcher varchar(8) not null,
attendance int(6),
time_of_game int(3),
number_of_innings int(2) not null,
visitor_final_score int(2) not null,
home_final_score int(2) not null,
visitor_hits int(2) not null,
home_hits int(2) not null,
visitor_errors int(2) not null,
home_errors int(2) not null,
visitor_left_on_base int(2) not null,
home_left_on_base int(2) not null,
winning_pitcher varchar(8) not null,
losing_pitcher varchar(8) not null,
save_for varchar(8),
vis_last_pitcher varchar(8),
home_last_pitcher varchar(8),
primary key (game_id)

It doesn't include absolutely everything from the event and game files which you can see from this link down near the bottom, but I think I have all of the essential data I need.

Where I'm stuck is trying to query this data to determine save opportunities, so then I can figure out blown saves. The number of saves achieved is right in the game file as "save_for".

Somehow I need to look at the event file and find the first time a pitcher enters a ballgame if he has a save opportunity at that point. Anyone have any ideas?
Softball Supervisor
Softball Supervisor

User avatar

Posts: 27
Joined: 23 Feb 2007
Home Cafe: Baseball
Location: Toronto, Canada

Re: Familiar with Retrosheet? Extracting save opportunities

Postby ayebatter » Thu Jan 07, 2010 2:14 am

That seems like a lot of work, I just log into one of my leagues on the CBS site, and go to 'all pitchers 2009 totals', and select blown saves, it's not an impressive nor meaningful list, I don't see how much use it would be. :-°
So-Cal 25 Club _762_

User avatar
ModeratorCafeholicResponse TeamMock(ing) Drafter
Posts: 12226
(Past Year: 693)
Joined: 15 Dec 2004
Home Cafe: Baseball

Re: Familiar with Retrosheet? Extracting save opportunities

Postby Neato Torpedo » Thu Jan 07, 2010 3:35 am

Lbl A
Output(A,B,"[mod edit]")
Goto A

That's all the programming I know, sorry. :-°

Rocinante2: you know
Rocinante2: its easy to dismiss the orioles as a bad team
ofanrex: go on
Rocinante2: i'm done
Rocinante2: lmao

Play Brushback Baseball! (we need more people)
Neato Torpedo
Hall of Fame Hero
Hall of Fame Hero

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerWeb Supporter
Posts: 8618
Joined: 4 Mar 2007
Home Cafe: Baseball
Location: we don't burn gasoline, we burn our dreams

Re: Familiar with Retrosheet? Extracting save opportunities

Postby Inukchuk » Thu Jan 07, 2010 11:30 am

abrunn11... the place to go for all your sig needs...
General Manager
General Manager

User avatar
CafeholicCafe WriterMock(ing) Drafter
Posts: 4014
Joined: 24 Jan 2006
Home Cafe: Baseball
Location: Coming down on this hospital like the hammer of Thor

Re: Familiar with Retrosheet? Extracting save opportunities

Postby MikeBillsFan » Thu Jan 07, 2010 12:32 pm

It is a lot of work but I am extracting all the other stats too to put in my database. I think I've come to the conclusion to add blown saves and holds to the database as a separate entry.

Agree on the nerds comment, Inukchuk.
Softball Supervisor
Softball Supervisor

User avatar

Posts: 27
Joined: 23 Feb 2007
Home Cafe: Baseball
Location: Toronto, Canada

Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 10 guests

Forums Articles & Tips Sleepers Rankings Leagues

Get Ready...
The 2015 MLB season starts in 22:01 hours
(and 28 days)

  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact