THE BOOK--Playing The Percentages In Baseball

Yes, this is fantastic!

Fantastic! This deserves so much praise.

I created a very quick-and-dirty R script to update my MySQL database with the newest ID tables every night. It’s probably not relevant to all that many, but I figured some might find it useful, so I posted it--click my name.

What I’d like to see along with this is standardized team and franchise three-letter identifiers.

I’m sure Ted can (or already does) incorporate this.  Consider these as entity or object IDs.

Cliff,

The teams table in the Baseball Databank is a good step in this direction - that’ll net you Baseball Reference and Retrosheet team IDs (Retrosheet is what we use at BPro as well).

And just a note - I sent Ted our master player list from BPro, to incorporate into this as he sees fit.

Retrosheet unfortunately decided in 2005 that Anaheim was going to be ALA. Everyone else uses LAA.  Retrosheet should have changed it, but I don’t think they did.  Otherwise retrosheet’s rules for team names are or should be the standard.  First three letter of the city.  If the city has two words then it is the first letter of each word followed by A or N.  If there is the same city in the AL and NL, then it is first two two letters followed by N or A.  I think that covers everything unless there is eventually a city with 3 words.  Then it could be the same as if it only had the first two words, or it could be the first letter of each word.  Plus, it could come up eventually that more than one team in each league could have the same name (like two NL teams in NY), in which case another rule would have to be made.

Retrosheet unfortunately decided in 2005 that Anaheim was going to be ALA. Everyone else uses LAA.  Retrosheet should have changed it, but I don’t think they did.  Otherwise retrosheet’s rules for team names are or should be the standard.

MGL - Retrosheet uses ANA, (not ALA), for Anaheim because that’s what MLB uses in the files from which Retrosheet now gets its data.  I think everybody else uses LAA because that’s what they were using previously and it made no sense to change everything over.  Retrosheet and MLB for the current minor leagues should be the standard, although I think there is one duplication of three letter code between minor and major leagues.  For defunct major league teams Retrosheet also gives a 3 letter-number code for all the previous teams on its web site.  For defunct minor league teams it would make sense to probably follow a similar standard.

mlbam has many duplicate three letter team codes between levels - they are only unique on each level, and that’s how they implement them. For example, colmlb is Colorado, colaaa is Columbus, Ohio.

I’ve blended mlbam’s team codes with SABR’s and have so far managed to find a three letter code for every pro team, foreign and domestic, starting in 1998. This week I had to deal with the Tucson Padres, which is technically an expansion team, but got lazy and went with the same ‘TUC’ of the old team there.

I just successfully downloaded the file using this Python script, takes about 5 minutes for the 10 mb file.

Now I’ll write a dos batch file that calls this script to get the file, unzips it, and loads the csv files into MySQL

import urllib
urllib.urlretrieve( “http://balco.sabr.org/data/baseballid/baseballid-latest.zip”,’c:/mybbos/data/sabr/baseballid-latest.zip’ )

The smiley face is representing a single quote followed by a closed parentheses

(Ed note: I put a space and that takes care of the smiley issue.)

"What I’d like to see along with this is standardized team and franchise three-letter identifiers.”

I maintain a ‘professional teams’ table, MLB, minors, foreign teams, Negro League teams, etc. which is used for all of the SABR minor league data, etc. but obviosly I can’t control what Retrosheet or others have decided to use for MLB team identifiers.

There are currently almost 32,000 season/team entries.....

Re: #5

Colin, that simplifies life. I never paid attention to the far end of the team table.