Monday, April 22, 2013

Same Name Game

Found this entry on a blog post about “pouring over” retro sheets to find some data. Not sure if the pouring over is thrown in there for dramatic effect or what. Honestly once you have the retro sheet loaded up in SQL Server you really do not have to pour over anything, just write a query.


I digress. Anyway the blog post wondered how many times a batter shared the same first name of the previous batter’s last name. The author of the blog stated that this has happened 130 times. After some “pouring over” I discovered the author had only reported 1 instance per game. 


I wanted to see how many actual plate appearances this same name game had happened. My data set is only 1952-2012 and I show 315 Times where  a batter had the same first name as the previous batter’s last name.


SELECT e.GAME_ID, e.YEAR_ID,p.lastname, p.firstname, p2.lastname, p2.firstname
FROM dbo.[events] e JOIN dbo.[lkup_people] p ON e.BAT_ID = p.id
JOIN dbo.[events] e2 ON e2.seq_events = e.seq_events+1
JOIN dbo.[lkup_people] p2 ON e2.BAT_ID = p2.id
WHERE p.lastname = p2.firstname
AND e.BAT_TEAM_ID = e2.BAT_TEAM_ID

image



Looking more closely at the Casey Blake/Blake DeWitt combo we see that Casey Blake reached base 48% of the time before Blake DeWitt stepped to the plate. Blake DeWitt would then reach base 38.5% of the time directly following Casey Blake. 


image



SELECT e.GAME_ID, e.YEAR_ID,p.lastname, p.firstname, p2.lastname, p2.firstname,e.EVENT_CD, e2.EVENT_CD
FROM dbo.[events] e JOIN dbo.[lkup_people] p ON e.BAT_ID = p.id
JOIN dbo.[events] e2 ON e2.seq_events = e.seq_events+1
JOIN dbo.[lkup_people] p2 ON e2.BAT_ID = p2.id
WHERE p.lastname = p2.firstname
AND e.BAT_TEAM_ID = e2.BAT_TEAM_ID

Monday, April 15, 2013

Servo Tests

Started playing with servo and the Arduino tonight. My goal is to make a web cam turret controlled by a joystick or game pad of some kind.



Here is a video of the servo with a mind of its own…as in hard-coded movement sequences.


[youtube http://www.youtube.com/watch?v=m3Zyj4mkX8g]


Here is a video with the servo hooked up to the potentiometer 


[youtube http://www.youtube.com/watch?v=Y9t6nbja2Yg]


My next step is to figure out how to hook up the gamepad to control the movements. I have a few different gamepads to try. Just need to make the time to test.

Thursday, April 11, 2013

Hitting bombs 0-2 with 2 outs

So started playing with the retrosheet data. Retrosheet is a non-profit organization that gathers baseball stats and presents them in consumable formats. 


Then some dude wrote this software called chadwick which allows you to parse the data and import it into a database. The article I found created a MySQL database which is ok for small stuff. The event data (every plate appearance since 1952) is 9.4 million rows. I didn’t really have the patience to learn how to optimize this using MySQL, so I quickly moved the data over to a SQL Server instance. So now I have a SQL Server database with every play from 1952-2012. 



I have run several queries against the data, created some stored procedures for Run Expectancy and such. One of the searches I am working on is who is the player that has hit the most Home runs with an 0-2 count with 2 outs. See the chart below.


image



Sammy “No Comprende” Sosa tops the list with 11 jacks on an 0-2 2 out count. In fact we have several who’s who of the steroid era in this list. Jose Valentin has the least amount of career bombs but hits our list at number 7. 7.32% of the time with a 0-2 2 out count he hits a home run, and a full 3.61% of his Home runs were hit on this count. Kendrys Morales has the fewest TotalABs at this count and is leading in PCT HRs on this count. His PCT will go down as he gets more ABs.


CTE used to get Initial Counts.


WITH HRCTE AS 
(select p.firstname + ' ' + p.lastname Player, COUNT(1) TotalHR, p.id
FROM events e JOIN lkup_people p ON e.BAT_ID = p.id
WHERE STRIKES_CT = 2
and BALLS_CT = 0
AND OUTS_CT = 2
AND EVENT_CD = 23
GROUP BY p.firstname + ' ' + p.lastname, p.id)
SELECT r.player, r.TotalHR, COUNT(1)
FROM events e JOIN HRCTE r on e.BAT_ID = r.id
WHERE STRIKES_CT = 2
and BALLS_CT = 0
AND OUTS_CT = 2
GROUP BY r.player, r.TotalHR
ORDER BY 2 DESC

Monday, April 1, 2013