Ernie Banks’ Performance In Double Headers (Using MySQL and R)

Chicago Cubs’ legend Ernie Banks passed away this week at age 83, leaving behind a legacy of 12 All-Star appearances, two NL MVP awards, and 512 home runs over a 19-year Hall of Fame career. He also earned 67.5 career rWAR, and is considered one of the greatest shortstops in history. However, the most memorable moment in his career is arguably a single quote, before an otherwise irrelevant game:

It’s a great day for a ballgame; let’s play two!

In the days since his passing, “Let’s play two!” has been the popular refrain for anyone referencing his career. Hearing it said so many times, I had an idea – how well did Banks perform when he DID play two?

Unfortunately, nothing in this world is original, and at least one blogger, William Juliano of the Yankees-centric The Captain’s Blog, got to it back in 2011. In an effort to find my own spin on this idea, it seemed simple enough that I could figure these numbers out on my own using MySQL and R, and the Retrosheet database, and reasonably explain how others can do the same.

I’m not an expert in the area, despite having some past database experience, and I consider this as much a learning experience for myself as it is for anyone reading, so anyone with comments or suggestions, please let me know.

Prerequisites

Someone attempting these queries and calculations should have some prior experience with SQL and relational databases, and some understanding of basic statistics. An installation of MySQL and the R statistics package are used by me, and I find programs like Sequel Pro and RStudio are incredibly helpful interfaces for these languages.

As far as the data itself, we’ll be using Retrosheet’s Event Files. Ascii-text file versions of these event logs can be found on their website, for free, linked above. However, the easier (and faster) solution would be to install the SQL dump generously maintained by Baseball Heat Maps, an absolutely awesome site. I’ll be using the schema in their version, but at worst only the column names would change slightly from version to version.

Finding the Games

In Retrosheet, we want to find all games played by Ernie Banks that were part of a double header. Fortunately, unique Retrosheet Game IDs are formatted in such a way as to help with this.

CHN195405221 -> CHN 1954 05 22 1

The prefix is home team’s Retrosheet ID, in this case the Chicago Cubs. The next eight digits are the date of the game, in YYYY/MM/DD format. The final number identifies the number of the game played in that stadium, on that day. On days where only one game is played, this final digit is a 0. In the first game of a double header, this number is 1, and 2 for the second game. This query returns all Game IDs of the relevant games:

SELECT DISTINCT e.GAME_ID
FROM EVENTS as e
WHERE SUBSTRING(e.GAME_ID, 12, 1) != 0
 AND e.BAT_ID = 'banke101';

Next, we’ll want to gather a list of all individual ‘events’ (roughly, plate appearances) that occurred while Banks was at the plate, in each of these games. Since I’ll be calculating some basic stats (Batting Average, On-Base Percentage, Slugging Percentage, and OPS), I want to check if each event falls under a certain category (for these purposes, AB, H, 2B, 3B, HR, BB, IBB, HBP, SO, SF, SH, and Reaching on Defensive Indifference).

Almost all of these events can be determined by the ‘EVENT_CD’ field. It’s an integer value that relates to the data-use table found here. By checking if the EVENT_CD = 3, for instance, one can see if the plate appearance ended in a strikeout. Other fields used here are the ‘AB_FL’ (was this an at-bat?),  ‘SF_FL’ (sacrifice fly), and ‘SH_FL’ (sacrifice hit).

Removing the ‘DISTINCT’ keyword from the previous query, and adding the necessary IF statements, this query returns the results of every Ernie Banks plate appearance in a double header.

SELECT SUBSTRING(e.GAME_ID, 4, 4) as Year, SUBSTRING(e.GAME_ID, 12, 1) as Game,
 IF(e.AB_FL='T', 1, 0) as AB,
 IF(e.EVENT_CD>=20,IF(e.EVENT_CD<=23,1,0),0) as H,
 IF(e.EVENT_CD=21,1,0) as 2B,
 IF(e.EVENT_CD=22,1,0) as 3B,
 IF(e.EVENT_CD=23,1,0) as HR,
 IF(e.EVENT_CD=14,1,0) as BB,
 IF(e.EVENT_CD=15,1,0) as IBB,
 IF(e.EVENT_CD=16,1,0) as HBP,
 IF(e.EVENT_CD=3,1,0) as SO,
 IF(e.SF_FL='T',1,0) as SF,
 IF(e.SH_FL='T',1,0) as SH,
 IF(e.EVENT_CD=5,1,0) as DI
FROM EVENTS as e
WHERE SUBSTRING(e.GAME_ID, 12, 1) != 0
 AND e.BAT_ID = 'banke101';

For ease of use, we want to group these counting stats by year, and by game in the double header (either 1 or 2). Add a COUNT(*) selection to count the number of games played under each year/game combination, a SUM() around each IF statement, and a GROUP BY at the end of the query.

SELECT SUBSTRING(e.GAME_ID, 4, 4) as Year, SUBSTRING(e.GAME_ID, 12, 1) as Game,
 COUNT(DISTINCT e.GAME_ID) as Games,
 SUM(IF(e.AB_FL='T', 1, 0)) as AB,
 SUM(IF(e.EVENT_CD>=20,IF(e.EVENT_CD<=23,1,0),0)) as H,
 SUM(IF(e.EVENT_CD=21,1,0)) as 2B,
 SUM(IF(e.EVENT_CD=22,1,0)) as 3B,
 SUM(IF(e.EVENT_CD=23,1,0)) as HR,
 SUM(IF(e.EVENT_CD=14,1,0)) as BB,
 SUM(IF(e.EVENT_CD=15,1,0)) as IBB,
 SUM(IF(e.EVENT_CD=16,1,0)) as HBP,
 SUM(IF(e.EVENT_CD=3,1,0)) as SO,
 SUM(IF(e.SF_FL='T',1,0)) as SF,
 SUM(IF(e.SH_FL='T',1,0)) as SH,
 SUM(IF(e.EVENT_CD=5,1,0)) as DI
FROM EVENTS as e
WHERE SUBSTRING(e.GAME_ID, 12, 1) != 0
 AND e.BAT_ID = 'banke101'
GROUP BY Year, Game;

Export these results to ‘banks.csv’, and open RStudio to manipulate the data.

Calculating Rates

Once in RStudio, the first thing to do is install and import the “plyr” library, which will be used split and aggregate the data, and then import the data itself from ‘Banks.csv’:

install.packages("plyr")
library("plyr")

data <- read.csv( file = "Desktop/banks.csv", header=TRUE, sep="," )

A new column calculating the number of plate appearances in each row should be calculated for convenience:

data$PA <- data$AB + data$BB + data$IBB + data$HBP + data$SH + data$SF + data$DI

New data frames are needed, as we want to look at Banks’ year-by-year performance, his career performance in either the first or second game of double header, and the 19-year total. The ddply() function will be used to split by year or game and summarize that data, and summarize() will be used for the single career total.

year_stats <- ddply(data, .(Year), summarize, Games = sum(Games), PA = sum(PA), AB = sum(AB), H = sum(H), X2B = sum(X2B), X3B = sum(X3B), HR = sum(HR), BB = sum(BB), IBB = sum(IBB), HBP = sum(HBP), SO = sum(SO), SF = sum(SF), SH = sum(SH), DI = sum(DI))
game_stats <- ddply(data, .(Game), summarize, Games = sum(Games), PA = sum(PA), AB = sum(AB), H = sum(H), X2B = sum(X2B), X3B = sum(X3B), HR = sum(HR), BB = sum(BB), IBB = sum(IBB), HBP = sum(HBP), SO = sum(SO), SF = sum(SF), SH = sum(SH), DI = sum(DI))
total_stats <- summarize(data, Games = sum(Games), PA = sum(PA), AB = sum(AB), H = sum(H), X2B = sum(X2B), X3B = sum(X3B), HR = sum(HR), BB = sum(BB), IBB = sum(IBB), HBP = sum(HBP), SO = sum(SO), SF = sum(SF), SH = sum(SH), DI = sum(DI))

View()-ing each of these data frames, you’ll notice the aggregate of each counting stat category.

The final steps are to append the necessary rate stat columns to each of these new data frames. For simplicity’s sake, we’ll stick with the basics: BA, OBP, SLG, and OPS. The “By Year” stats and the “Double Header Game” stats are calculated in a slightly different way than the total line, due to having more than one row as a result.

Instead of “summarizing” this data again (which creates additional data frames), the “transform” function should be used to append new columns to existing frames:

year_stats <- ddply(year_stats, .(Year), transform, BA = H/AB, OBP = (H+BB+IBB+HBP)/(AB+BB+IBB+HBP+SF), SLG = (H+X2B+(2*X3B)+(3*HR))/AB) 
year_stats$OPS <- year_stats$OBP + year_stats$SLG
game_stats <- ddply(game_stats, .(Game), transform, BA = H/AB, OBP = (H+BB+IBB+HBP)/(AB+BB+IBB+HBP+SF), SLG = (H+X2B+(2*X3B)+(3*HR))/AB) 
game_stats$OPS <- game_stats$OBP + game_stats$SLG

As the “OBP” and “SLG” columns don’t exist at the time of calling the ddply function, the “OPS” column has to be created in a separate step in the code above.

As for the total data frame, we won’t use a function to do the transform. For ease of writing, I attach() the “total_stats” data frame to cut down on typing, and detach it before the final line.

attach(total_stats)
total_stats$BA <- H/AB 
total_stats$OBP <- (H+BB+IBB+HBP)/(AB+BB+IBB+HBP+SF)
total_stats$SLG <- (H+X2B+(2*X3B)+(3*HR))/AB
detach(total_stats)
total_stats$OPS <- total_stats$OBP + total_stats$SLG

The Results

At this point, stat lines have been calculated and are ready for viewing. Below are my results:

year-by-year

Year Games PA AB H X2B X3B HR BB IBB HBP SO SF SH DI BA OBP SLG OPS
1 1953 2 8 7 1 0 0 0 1 0 0 1 0 0 0 0.143 0.250 0.143 0.393
2 1954 46 189 178 52 9 2 6 7 1 0 11 1 2 0 0.292 0.321 0.466 0.787
3 1955 37 153 142 49 8 2 14 8 1 0 16 2 0 0 0.345 0.379 0.725 1.104
4 1956 47 196 176 55 15 1 10 15 5 0 17 0 0 0 0.312 0.383 0.580 0.962
5 1957 56 232 204 54 7 0 17 18 5 3 30 2 0 0 0.265 0.345 0.549 0.894
6 1958 39 171 153 44 6 3 7 12 3 1 21 2 0 0 0.288 0.351 0.503 0.854
7 1959 28 123 111 21 3 0 5 6 5 1 14 0 0 0 0.189 0.268 0.351 0.620
8 1960 38 164 142 36 11 0 7 12 7 1 15 2 0 0 0.254 0.341 0.479 0.820
9 1961 36 153 136 39 6 1 6 7 7 1 25 0 0 2 0.287 0.358 0.478 0.836
10 1962 36 146 138 42 8 2 10 5 1 2 18 0 0 0 0.304 0.342 0.609 0.951
11 1963 30 110 98 23 4 0 4 6 2 0 17 3 1 0 0.235 0.284 0.398 0.682
12 1964 36 145 135 42 8 2 7 4 3 2 16 0 1 0 0.311 0.354 0.556 0.910
13 1965 41 166 143 36 5 2 9 15 4 1 12 3 0 0 0.252 0.337 0.503 0.841
14 1966 34 129 123 25 4 1 1 2 2 0 13 2 0 0 0.203 0.225 0.276 0.501
15 1967 40 158 146 42 8 0 10 6 3 0 25 1 2 0 0.288 0.327 0.548 0.875
16 1968 33 134 119 37 7 0 8 7 1 1 14 1 5 0 0.311 0.357 0.571 0.928
17 1969 29 115 101 31 2 1 3 10 1 1 18 1 1 0 0.307 0.377 0.436 0.813
18 1970 12 33 32 9 1 0 2 1 0 0 5 0 0 0 0.281 0.303 0.500 0.803
19 1971 6 15 14 3 0 0 1 0 0 0 3 0 1 0 0.214 0.214 0.429 0.643

Double Header Game

Game Games PA AB H X2B X3B HR BB IBB HBP SO SF SH DI BA OBP SLG OPS
1 1 319 1317 1188 337 63 7 63 76 29 7 149 11 6 0 0.284 0.343 0.508 0.850
2 2 307 1223 1110 304 49 10 64 66 22 7 142 9 7 2 0.274 0.329 0.509 0.838

Total

Games PA AB H X2B X3B HR BB IBB HBP SO SF SH DI BA OBP SLG OPS
1 626 2540 2298 641 112 17 127 142 51 14 291 20 13 2 0.279 0.336 0.508 0.844

It makes logical sense that a batter would be more fresh (AKA better) in the first game of a double header, as this data correlates. Ernie Banks was a career .274/.330/.500 hitter. In this surprisingly large sample (double headers were more common in the past), Banks was not too far off from his career line. However, he did in fact have a slightly higher OPS on days where he was able to “play two.”

Advertisements
Ernie Banks’ Performance In Double Headers (Using MySQL and R)

One thought on “Ernie Banks’ Performance In Double Headers (Using MySQL and R)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s