Recent Forum Posts

From categories:

All categories General: General Discussion Databases: Access Databases: MySQL Databases: Other Databases Datasets: Baseball Databank Datasets: Retrosheet Datasets: Gameday XML Datasets: Other datasets. Other Software: Scripting Languages Other Software: Statistics Packages

page 1 of 212next »

Requery the GameDay Fielder code?

kelakos 1253404387|%e %b %Y, %H:%M %Z|agohover
in discussion Datasets / Gameday XML » Requery the GameDay Fielder code?

I ran Colin's GameDay fielder code after loading RecentGames.bat into my gameday database from BBOS just to see what it did. Then, I loaded AllAvailableGames.bat into my gameday database to get all the BBOS games. When I look in gameday.event_player table ( that Colin's code created), there are only games from the RecentGames load. What do I need to do to have it include all the new games in my database?

Also, how would I retrieve batted ball x,y locations prior to the Pitch F/X GameDay XML data ~2006?

Re: using retrosheet to figure total pitches thrown by pitcher

chiefjamesp 1237212330|%e %b %Y, %H:%M %Z|agohover
in discussion Datasets / Retrosheet » using retrosheet to figure total pitches thrown by pitcher

could anyone help me parse the pitch sequence strings? i'd like to remove all pickoff throws from the strings, and my best effort was gonna be to use the replace function to delete them essentially, but i was wondering if someone knows a way to pull the characters that represent pitches out of the string and into a new column (is that possible?) any ideas, as always, are greatly appreciated, have an awesome day!

Re: using retrosheet to figure total pitches thrown by pitcher

chiefjamesp 1236570630|%e %b %Y, %H:%M %Z|agohover
in discussion Datasets / Retrosheet » using retrosheet to figure total pitches thrown by pitcher

oh man, I was really hoping you might write a tutorial exactly like this at! thank you so much!

Re: Weather Data

Colin Wyers 1236529126|%e %b %Y, %H:%M %Z|agohover
in discussion General / General Discussion » Weather Data

There's something from SABRMatt on the RetroSQL list. I haven't looked at it yet. Retrosheet's games table has limited weather information as well.

Re: using retrosheet to figure total pitches thrown by pitcher

Colin Wyers 1236527602|%e %b %Y, %H:%M %Z|agohover
in discussion Datasets / Retrosheet » using retrosheet to figure total pitches thrown by pitcher

Probably more than you bargained for, but I finally wrote out a Retrosheet tutorial.

using retrosheet to figure total pitches thrown by pitcher

chiefjamesp 1236430157|%e %b %Y, %H:%M %Z|agohover
in discussion Datasets / Retrosheet » using retrosheet to figure total pitches thrown by pitcher

Hello! I was wondering if anyone could suggest some simple ways to get a txt file created using bevent into a database…I used bevent to print the pitch sequence and pitcher for each at bat…I was gonna import it into excel and use the len function to count the length of each pitch sequence (which would give me a list of pitcher and number pitches for each at bat) and then load it into my database and use sqlyog to sum pitches by pitcher id…I guess I am wondering if there is a way to just import the txt as a table into sql, and sum the pitches by pitcher id? Hope I explained my question almost clearly…sorry otherwise!

Weather Data

xeifrank 1236126911|%e %b %Y, %H:%M %Z|agohover
in discussion General / General Discussion » Weather Data

Are there any databases available that have weather data for each game?
Temperate and wind vector.
vr, Xei

Re: Quick Question about the Primary Position Table

chiefjamesp 1230885345|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » Quick Question about the Primary Position Table

right on, thank you very much for the clarification! aren't there outfield positional breakdowns for the retrosheet era? Is the the data from 1956-1995 too unreliable to be used?

Re: Quick Question about the Primary Position Table

Colin Wyers 1230780150|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » Quick Question about the Primary Position Table

I probably didn't write that as clearly as I could/should have. Prior to 1996, fielding numbers in the Databank are only listed as OF; after 1995, they're broken down into the three OF positions as well. For those years where we have CF, LF and RF breakdowns, we're uninterested in the OF data, so that was discarded.

Re: Quick Question about the Primary Position Table

chiefjamesp 1230699551|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » Quick Question about the Primary Position Table

I have another (probably pretty lame) question about the from statement in the primary position query

"FROM (SELECT * from fielding
WHERE IF(yearID>1995 AND POS = "OF",1,0) != 1 ORDER BY G Desc) f"

I can't really figure out whats going on there.

Re: sql for positional adjustments?

Colin Wyers 1230694222|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » sql for positional adjustments?

The CASE is what I'd use for a one-off query. The other option is to set up a table with the positional adjustments, and then to use a JOIN statement.

Re: Adding CSV to existing database

TucsonRoyal 1230670454|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » Adding CSV to existing database

figured it out

Adding CSV to existing database

TucsonRoyal 1230668420|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » Adding CSV to existing database

I have a new CSV I want to add to an existing database (sql database created with the help of Colin's articles), what is the easiest way to do this? The first row of data is the columns headings.

primary pos data from: http://tangotiger.net/bdb/

Re: sql for positional adjustments?

chiefjamesp 1230601855|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » sql for positional adjustments?

hey lar, thanks a ton! the case statement helped me do exactly what i was trying to do!

Re: sql for positional adjustments?

lar 1230585816|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » sql for positional adjustments?

Try using a CASE statement… it's meant for instances almost exactly like this, where you have a list of possible values and something different to do depending on what the value is…

for example

select playerid, yearid,
    CASE
        WHEN pos = 'C' THEN (innings/162) * 12.5
        WHEN pos = 'SS' THEN (innings/162) * 7.5
        -- etc
        ELSE 1  -- use 'ELSE' as the catch-all, to be used on anything that wasn't listed above
    END as POS_ADJUSTMENT
from fielding;

there might be a more elegant way to do it with MySQL variables, but using CASE is a little more generic

Re: sql for positional adjustments?

TucsonRoyal 1230584529|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » sql for positional adjustments?

Use a nested IF statement

If pos = 'c'
then tot = 12.5
else
if pos = 'ss'
then tot = 7.5
…….

It will be pretty long, but should work. If is the only method I know, but there might be another method.

sql for positional adjustments?

chiefjamesp 1230526982|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » sql for positional adjustments?

I was trying to figure positional adjustments, but got hung up on the command to use. I was basically trying to do something like (Innings/162) * (if pos='c',12.5, if pos='ss',7.5 etc…), if that makes any sense (I'm obviously quite the sql noob)…there is obviously a much better way to do this that eludes me…

Re: SQL Command Help

Colin Wyers 1230515311|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » SQL Command Help

First, go ahead and set up the season stats views like so:

http://basql.wikidot.com/full-year-statistics

Then, do something like:

SELECT p1.playerID, p1.yearID, p1.ERA, p2.ERA
FROM pitch_year_total_avg p1, pitch_year_total_avg p2
WHERE p1.playerID = p2.playerID
AND p1.yearID = p2.yearID + 1;

SQL Command Help

John Signore 1230489104|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » SQL Command Help

Hey guys I was wondering if you could help me with what is probably a simple SQL query. What I was wanting to do was to get a listing of all pitchers ERA in year 1 as well as the same pitchers ERA in year 2. What I was wanting to do is have some cutoff (say greater than 150 innings pitched each season) so that I'm only getting pitchers that have pitched 150+ in each season. How would I go about this?

Re: Importing Retrosheet and Gameday Data into MySQL

TucsonRoyal 1229357562|%e %b %Y, %H:%M %Z|agohover
in discussion Databases / MySQL » Importing Retrosheet and Gameday Data into MySQL

I just found out it will not be available to be download for a bit. I have it on my computer and I am thinking of finding another way to make it available. If someone has a FTP site I would not have a problem moving it over to it. Let me know the information and I will while I am at work. I am also going to see if I can fit it on a DVD, really close in size. Contact me at wydiyd at hotmail dot com.