(Cross-posting to Retrolist and BDB)

This is to point out a discrepancy, without knowing which is correct.

The Lahman DB and B-R.com has Jim Busby with one game at catcher.  Retrosheet
has two games, with the additional being in 1960:
http://www.retrosheet.org/boxesetc/B/Pbusbj101.htm

Tom

---------------------------------------------
Tim Raines, Hall of Fame 2008
http://www.raines30.com/










---------------------------------------------

I have compiled additional batting and pitching statistics from
Retrosheet Play by Play data and wanted to share them here. I've
linked the Retro ID's to the BDB player ID's so you can easily join
them to your database. The only things stopping me from posting them
is that I don't know how to identify players with multiple stints on
the same team during one season. Also I am uncertain as to what
statistical categories I should include. For pitchers, I'd really
would of liked to have counted pickoffs. However, I didn't know an
easy way to seperate catchers pickoffs from pitchers pickoffs. That'll
be on the way shortly. Stolen Base and Caught Stealing data for
pitchers and catchers is already in the fielding tables.  Here's the
list of what I have compiled from Retrosheet from 1954 to 2007:

Batting:

Catcher's Interference
Reached on Error
Reached on Error Sacrifice Hit
Reached on Error Sacrifice Fly
Reached on Fielder's Choice (No Outs Recorded)
Reached on Fielder's Choice Sacrifice Hit
Pickoff Caught Stealing
Picked Off
Pickoff Error
Balk (Lead Baserunner)
Passed Ball (Lead baserunner that Advances)
Wild Pitch (Lead baserunner that Advances)
Defensive Indifference
Other Advance (Out Advancing)
Ground Balls
Fly Balls
Line Drives
Popups
GIDP Opportunities (Man on 1st, 0 or 1 out)
Retrosheet Plate Appearances
Missing Plate Appearances

Pitching

Doubles Allowed
Triples Allowed
GIDP Allowed
Sacrifice Hits Allowed (Complete from 1921 to the present)
Sacrifice Flys Allowed
Catcher's Interference Allowed
Reached on Error Allowed
Reached on Error Sacrifice Hit Allowed
Reached on Error Sacrifice Fly Allowed
Reached on Fielder's Choice Allowed
Reached on Fielder's Choice Sacrifice Hits Allowed
Ground Balls Against
Fly Balls Against
Line Drives Against
Popups Against
GIDP Against Opportunities
Retrosheet IP Outs
Missing IP Outs

Again, let me know what you'd for me to include. I can obviously
combine some of the categories.

Matt,

I don't know anything about scraping data. The basic data (AB,H,...)is
already in the BDB database. Unless you wanted to derive the basic
data for 2008. But it would probably be easiest to copy and paste the
basic data from Baseball Reference. Again, you'll have to provide a
little more detail about what you are trying to do here.

> Dave - Thanks so much for your efforts from the baseball databank group!
>
> I was wondering if you could show me a thing or two.  Also I was wondering
> if you knew of an easy way to get pitch by pitch, or just similar data to
> what you have provided plus the basics (offense: AB, H, 2B, 3B, RBI, R,
> etc.

Join RetroSQL yahoo group.

Tom

--- In baseball-databank@yahoogroups.com, "Dave Carter" <terpsfan101@...> wrote:

> Ground Balls
> Fly Balls
> Line Drives
> Popups

For pre-1980 years, the differences between these will blur in the data, and you
won't
have hits. I'd just go for:

Air Outs - Pulled
Ground Outs - Pulled

Pitchers have just Air Outs and Ground Outs.

And a record of bunts:

Sacrifice Attempts - Sacrifices - Hits - Errors
Bunt for a Hit Attempts - Hits
Squeeze Attempts - Squeezes

Pitchers would have a mirrored record. I'd also consider something like Runners
Moved Up
and some sort of RBI Opportunity.

For fielders, have Independent Putouts and Assists, those being ones the fielder
started,
and Double Plays Started and Turned, and opportunities for the Double Plays.
Separate
errors by those that put a runner on base, those that allow an existing runner
to advance,
and other, mostly muffed foul flies that don't do much.

Charles,

Most of your suggestions shouldn't be difficult to implement. I
had a feeling that I should have used outs for batted balls.
Although seperating "Outs on Base" from the batted-ball categories
would be a pain. For instance, a single where the batter gets thrown
out at second-base would get counted as a ground-ball out. Would you
be OK with this. If you prefer, I can look at the baserunner fields
and remove these plays from GB and FB outs.

For Sacrifice Bunt Attempts, I'll use any bunt that occurrs during a
sacrifice situation. Bunt Hits and Bunt Hit attempts we'll be easy.
MGL uses infield hits, I think this would be more useful than Bunt
Hits. I don't think I want to attempt to measure "Runners moved up."
Should RBI Opportunities include situations where there is only 1
baserunner and he's on first base, in other words, anytime there is
a player on-base?

The defensive stats you suggested would be great to have. I can
categorize pickoffs for pitchers and catchers as well. This will
take some time. Give me at least a week to compile this stuff. If
there are any other categories you'd like to see, post them here or
email me.


--- In baseball-databank@yahoogroups.com, "Charles Saeger"
<rasputin@...> wrote:
>
> --- In baseball-databank@yahoogroups.com, "Dave Carter"
<terpsfan101@> wrote:
>
> > Ground Balls
> > Fly Balls
> > Line Drives
> > Popups
>
> For pre-1980 years, the differences between these will blur in the
data, and you won't
> have hits. I'd just go for:
>
> Air Outs - Pulled
> Ground Outs - Pulled
>
> Pitchers have just Air Outs and Ground Outs.
>
> And a record of bunts:
>
> Sacrifice Attempts - Sacrifices - Hits - Errors
> Bunt for a Hit Attempts - Hits
> Squeeze Attempts - Squeezes
>
> Pitchers would have a mirrored record. I'd also consider something
like Runners Moved Up
> and some sort of RBI Opportunity.
>
> For fielders, have Independent Putouts and Assists, those being
ones the fielder started,
> and Double Plays Started and Turned, and opportunities for the
Double Plays. Separate
> errors by those that put a runner on base, those that allow an
existing runner to advance,
> and other, mostly muffed foul flies that don't do much.
>

I guess that I could throw out some of the baserunning categories.
Here's my new list:

Batting:

XI
ROE
ROE SH
ROE SF
RFC
RFC SH
PkO CS
PkO
PkO Error
GB
FB
LD
POP
GIDP Opp
Retro PA
Miss PA

Pitching:

2BA
3BA
GIDPA
SHA
SFA
XIA
ROEA
ROEA SH
ROEA SF
RFCA
RFCA SH
GBA
FBA
LDA
POPA
GIDPA Opp
Retro IPOuts
Miss IPOuts

I realize the season isn't over yet, but I always like the info.

Hey guys...I don't know if anyone else noticed this since it's not a
huge deal 99% of the time, but for some reason in the 2007 season
(most recent in the database version I have) the convention to include
all pitching seasons and fielding seasons in the batting table whether
or not a batting record for that player/season existed seems to have
stopped and there are missing names.  The guy building our search
feature relies on the batting table as the general player record
creator (each player has one record per teamID/stint in the batting
table, which is why he chose to do that), so we were kind of wondering
if any fixes were planned to fill in those missing records or not.

Thanks in advance,

SABR Matt

We've longed discussed the idea of an APPEARANCES table, so that it
captures exactly what it's supposed to capture, rather than taking over
the BATTING table just because the official rules say to use it.  (DBAs
follow the data, not the legal definitions of names.)

The APPEARANCES table would give us the total number of games played,
games started, games finished, and potentially further broken down by
fielding position and lineup position.  So, you could have:

playerID, appearCode, appearSubcode, GS, GP, GF
raineti01, 0, 0, 141, 147, 142
raineti01, fld, 7, 141, 141, 140
raineti01, fld, 8, 0, 8, 2
raineti01, bat, 1, 140, 140, 139
raineti01, bat, 3, 1, 1, 1
raineti01, bat, 7, 0, 6, 2

Something like that.  So, for your purposes, you would select on
appearCode = 0.

Or, if that's too complicated, we'd have an APPEARANCES table like this:
playerID, GS, GP, GF
raineti01, 141, 147, 142

And an APPEARANCES_SPLIT table like this:
playerID, appearCode, appearSubcode, GS, GP, GF
raineti01, fld, 7, 141, 141, 140
raineti01, fld, 8, 0, 8, 2
raineti01, bat, 1, 140, 140, 139
raineti01, bat, 3, 1, 1, 1
raineti01, bat, 7, 0, 6, 2

Regardless, I do not have an answer for your original question.

Tom

That's the way I would approach it as well if I were the guy doing the
building of the DB...I highly recommend Lahman/Forman work toward that
goal.  It sounds like you're saying, however, that there are no plans
to make the batting table rules consistent...which means I should be
telling our web search/sort builder to prepare other methods for
fixing the irregularities.  Do I have that correct?

> That's the way I would approach it as well if I were the guy doing the
> building of the DB...I highly recommend Lahman/Forman work toward that
> goal.  It sounds like you're saying, however, that there are no plans
> to make the batting table rules consistent...which means I should be
> telling our web search/sort builder to prepare other methods for
> fixing the irregularities.  Do I have that correct?
>
>

There are no current plans, that's correct.  How/when this will be
rectified is undetermined.

Tom


---------------------------------------------
The Book--Playing The Percentages In Baseball
http://www.InsideTheBook.com

OK...thanks Tom.

I'm going to ask our web coder to McGuyver an appearances
table...that's the correct way to normalize the database anyway.

--- In baseball-databank@yahoogroups.com, "Randy Fiato"
<sysadmin@...> wrote:
>
> I realize that this is a relatively minor detail in relation to
some of the
> other things being planned here, but I've noticed that the current
MySQL
> schema has several fields that are oversized. That is to say, the
fields are
> represented as an integer that is far too large for the possible
range of
> values.
>
> For example, in the Master table, birthYear and deathYear are both
int(4),
> which in MySQL is a 4-byte integer. smallint(4) unsigned (the same
as yearID
> in the other tables) would be better, as this uses only 2 bytes.
Similarly,
> the bith/death days and months could be tinyint(2) unsigned instead
of
> int(2).
>
> Another example that occurs in several tables is that games in a
season (or
> wins, losses, etc.) can be described as tinyint(3) unsigned (range
of 0 -
> 255) instead of smallint(3). I don't think MLB is going to start
playing
> 250-game seasons any time soon. :-)
>
> I've made these changes, among several others, to my copy and have
been able
> to shrink the size of the database by at least a couple of
megabytes (not
> sure of the exact amount).
>
> --
> Randy Fiato
> System Administrator, Big League Forums
<http://www.bigleagueforums.net/>

And to think this is the source of most of my problems. (Well, no,
but I'm trying to add dramatic flair.)

The problem I'm running into is that, to use these values for a lot
of purposes I have to recast them as signed variables. I really
couldn't find any other discussion on these issues - would it be
really inconvenient to change the schema to not use unsigned
variables?

--CW

On Oct 18, 2008, at 10:56 PM, wyerscj wrote:

> --- In baseball-databank@yahoogroups.com, "Randy Fiato"
> <sysadmin@...> wrote:
>>
>> I realize that this is a relatively minor detail in relation to
> some of the
>> other things being planned here, but I've noticed that the current
> MySQL
>> schema has several fields that are oversized. That is to say, the
> fields are
>> represented as an integer that is far too large for the possible
> range of
>> values.
>>
>> For example, in the Master table, birthYear and deathYear are both
> int(4),
>> which in MySQL is a 4-byte integer. smallint(4) unsigned (the same
> as yearID
>> in the other tables) would be better, as this uses only 2 bytes.
> Similarly,
>> the bith/death days and months could be tinyint(2) unsigned instead
> of
>> int(2).
>>
>> Another example that occurs in several tables is that games in a
> season (or
>> wins, losses, etc.) can be described as tinyint(3) unsigned (range
> of 0 -
>> 255) instead of smallint(3). I don't think MLB is going to start
> playing
>> 250-game seasons any time soon. :-)
>>
>> I've made these changes, among several others, to my copy and have
> been able
>> to shrink the size of the database by at least a couple of
> megabytes (not
>> sure of the exact amount).
>>
>> --
>> Randy Fiato
>> System Administrator, Big League Forums
> <http://www.bigleagueforums.net/>
>
> And to think this is the source of most of my problems. (Well, no,
> but I'm trying to add dramatic flair.)
>
> The problem I'm running into is that, to use these values for a lot
> of purposes I have to recast them as signed variables. I really
> couldn't find any other discussion on these issues - would it be
> really inconvenient to change the schema to not use unsigned
> variables?

If the nature of the data is that it is unsigned, it should be stored
in an unsigned column.  If the nature of your application is that you
use the values in a signed way, *your application* should make the
necessary adjustments.

Otherwise, why should the next person not come along and say, my
purposes require the values to be floats, can the schema be changed to
make them floats?

--- In baseball-databank@yahoogroups.com, Paul DuBois <paul@...> wrote:
>
>
> On Oct 18, 2008, at 10:56 PM, wyerscj wrote:
>
> > --- In baseball-databank@yahoogroups.com, "Randy Fiato"
> > <sysadmin@> wrote:
> >>
> >> I realize that this is a relatively minor detail in relation to
> > some of the
> >> other things being planned here, but I've noticed that the current
> > MySQL
> >> schema has several fields that are oversized. That is to say, the
> > fields are
> >> represented as an integer that is far too large for the possible
> > range of
> >> values.
> >>
> >> For example, in the Master table, birthYear and deathYear are both
> > int(4),
> >> which in MySQL is a 4-byte integer. smallint(4) unsigned (the same
> > as yearID
> >> in the other tables) would be better, as this uses only 2 bytes.
> > Similarly,
> >> the bith/death days and months could be tinyint(2) unsigned instead
> > of
> >> int(2).
> >>
> >> Another example that occurs in several tables is that games in a
> > season (or
> >> wins, losses, etc.) can be described as tinyint(3) unsigned (range
> > of 0 -
> >> 255) instead of smallint(3). I don't think MLB is going to start
> > playing
> >> 250-game seasons any time soon. :-)
> >>
> >> I've made these changes, among several others, to my copy and have
> > been able
> >> to shrink the size of the database by at least a couple of
> > megabytes (not
> >> sure of the exact amount).
> >>
> >> --
> >> Randy Fiato
> >> System Administrator, Big League Forums
> > <http://www.bigleagueforums.net/>
> >
> > And to think this is the source of most of my problems. (Well, no,
> > but I'm trying to add dramatic flair.)
> >
> > The problem I'm running into is that, to use these values for a lot
> > of purposes I have to recast them as signed variables. I really
> > couldn't find any other discussion on these issues - would it be
> > really inconvenient to change the schema to not use unsigned
> > variables?
>
> If the nature of the data is that it is unsigned, it should be stored
> in an unsigned column.  If the nature of your application is that you
> use the values in a signed way, *your application* should make the
> necessary adjustments.
>
> Otherwise, why should the next person not come along and say, my
> purposes require the values to be floats, can the schema be changed to
> make them floats?
>


Anything in baseball that could possibly be a negative number?

Regarding the tinyint and int and the like issue: I disagree.  I'll give
you two good reasons:

1. An 8 GB flash drive costs $29.  A 500 GB backup drive costs 75$.
Saving space should be the least of our concerns.  Indeed, I'd prefer
creating a data warehouse, which would mean heaving denormalized data.

2. If you do sum(wins) on a tinyint, some DBMS will keep the data type as
tinyint.  You will get into overflow situations.

Whatever performance issues you can possibly gain pales in comparison to
potentially corrupt output.

Tom

Who is "SABRE", and can you cite their reasoning?

Tom

> You can do it that way, but SABRE prefers it to be the other way.
>  
>  
>
>
> --- On Tue, 11/11/08, Tangotiger <tangotiger@...> wrote:
>
> From: Tangotiger <tangotiger@...>
> Subject: Re: [baseball-databank] 2008 Update this week or early next
> To: baseball-databank@yahoogroups.com
> Date: Tuesday, November 11, 2008, 6:18 AM
>
>
>
>
>
>
>
>
>
>
> It is usually desirable to go vertically, than horizontally, like so:
> http://sports. groups.yahoo. com/group/ baseball- databank/ message/3606
>
> You can create more codes, without altering the number of columns.  So,
> you can include stuff like batting order and the like.  From there, you
> can always convert the subset you need to map horizontally.
>
> Tom
>
>
> ------------ --------- --------- --------- ------
>
> --- On Mon, 11/10/08, Sean Forman <sean-forman@ baseball- reference. com>
> wrote:
>
> From: Sean Forman <sean-forman@ baseball- reference. com>
> Subject: [baseball-databank] 2008 Update this week or early next
> To: "Baseball Databank" <baseball-databank@ yahoogroups. com>
> Date: Monday, November 10, 2008, 12:51 PM
>
>
>
>
> I've made good progress on the BDB update for this year and should have
> something soon.
>
> I'm adding an Appearances table with the following schema (this will have
> data from 1973 on for the AL and 1974 on for the NL).  Corresponding to
> complete retrosheet data for the NL and the start of the DH for the AL. 
>
> I'm open to suggestions, but in Batting, I think I'm going to put an entry
> in for every player who played and then  add a column G_bat that will show
> how many games they appeared in the lineup, so pre-Interleague AL pitchers
> will have their games pitches in G and then 0 for G_bat and nulls for the
> values of the batting stats.
>
> Feedback?
>
> sean
>
> CREATE TABLE `Appearances` (
>   `yearID` smallint(4) NOT NULL default '0',
>   `teamID` char(3) NOT NULL default '',
>   `lgID` char(2) default NULL,
>   `playerID` char(9) NOT NULL default '',
>   `G_all` tinyint(3) unsigned default NULL,
>   `G_batting` tinyint(3) unsigned default NULL,
>   `G_defense` tinyint(3) unsigned default NULL,
>   `G_p` tinyint(3) unsigned default NULL,
>   `G_c` tinyint(3) unsigned default NULL,
>   `G_1b` tinyint(3) unsigned default NULL,
>   `G_2b` tinyint(3) unsigned default NULL,
>   `G_3b` tinyint(3) unsigned default NULL,
>   `G_ss` tinyint(3) unsigned default NULL,
>   `G_lf` tinyint(3) unsigned default NULL,
>   `G_cf` tinyint(3) unsigned default NULL,
>   `G_rf` tinyint(3) unsigned default NULL,
>   `G_of` tinyint(3) unsigned default NULL,
>   `G_dh` tinyint(3) unsigned default NULL,
>   `G_ph` tinyint(3) unsigned default NULL,
>   `G_pr` tinyint(3) unsigned default NULL,
>   PRIMARY KEY  (`yearID`,`teamID` ,`playerID` )
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
>
>
> --
> Sean Forman
> President, Sports Reference LLC
> http://www.sports- reference. com/
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


---------------------------------------------
The Book--Playing The Percentages In Baseball
http://www.InsideTheBook.com

Added an appearances table that goes from 1973-present for the AL and 1974 on for the NL. These years were chosen because they contain the entirety of the DH-era for the AL and the seasons in the NL for which Retrosheet data is complete. This table contains a summary of games played by position including a summary by position. It also lists, in G_batting, the number of games in which the player appeared in a batting order. In the DH-era AL pitchers may well have all zeros here as they never appeared in the lineup.

Postseason batting, pitching and fielding tables were greatly expanded and improved from play-by-play data.

The games played data in Batting should now list all games played for all players, and all players will appear in this table regardless of whether they were in the lineup. I've added a G_batting column to show you when the player did not appear in the lineup that season and also nulled out their stats when they did not have batting stats for that year.

In FieldingOF, I deleted entries for seasons after 1956, since I have entered full LF-CF-RF entries for those seasons and the games played totals are now redundant.

Added an AllstarFull table that adds info like starter and GP info.

Two hours without comment?  I hope that's because we are in shock and not
passe over this.  Anyway, a huge thanks to Sean for delivering the data.

I am going to work on updating my MS Access shell to automatically import
the data.  With new columns, I have to spend a bit of time to get that
straightened out.  I'll make that available once I've got it working,
hopefully no later than early next week.

Also, please treat this as an open invitation to everyone out there to
deliver whatever data they get their hands on, especially "ID mapping"
data across the various data sources. It's important to think of this
ground as a dumping ground for any and all data.

Tom


> I've updated a BDB update today.  Here are the release notes.  One should
> consider this a proposed release as I would like lots of review to point
> out
> any issues with the newest release.
>
> sean
>
> Notes:
>
> Added an appearances table that goes from 1973-present for the AL and 1974
> on for the NL. These years were chosen because they contain the entirety
> of
> the DH-era for the AL and the seasons in the NL for which Retrosheet data
> is
> complete. This table contains a summary of games played by position
> including a summary by position. It also lists, in G_batting, the number
> of
> games in which the player appeared in a batting order. In the DH-era AL
> pitchers may well have all zeros here as they never appeared in the
> lineup.
>
> Postseason batting, pitching and fielding tables were greatly expanded and
> improved from play-by-play data.
>
> The games played data in Batting should now list all games played for all
> players, and all players will appear in this table regardless of whether
> they were in the lineup. I've added a G_batting column to show you when
> the
> player did not appear in the lineup that season and also nulled out their
> stats when they did not have batting stats for that year.
>
> In FieldingOF, I deleted entries for seasons after 1956, since I have
> entered full LF-CF-RF entries for those seasons and the games played
> totals
> are now redundant.
>
> Added an AllstarFull table that adds info like starter and GP info.
>
>
> --
> Sean Forman
> President, Sports Reference LLC
> http://www.sports-reference.com/
>


---------------------------------------------
The Book--Playing The Percentages In Baseball
http://www.InsideTheBook.com