Filling a MySQL Game Log Database from R
# retrosheet_gamelog <- function(season) {
# require(abdwr3edata)
# require(fs)
# dir <- tempdir()
# glheaders <- retro_gl_header
# remote <- paste0(
# "http://www.retrosheet.org/gamelogs/gl",
# season,
# ".zip"
# )
# local <- path(dir, paste0("gl", season, ".zip"))
# download.file(url = remote, destfile = local)
# unzip(local, exdir = dir)
# local_txt <- gsub(".zip", ".txt", local)
# gamelog <- here::here(local_txt) |>
# read_csv(col_names = names(glheaders))
# file.remove(local)
# file.remove(local_txt)
# return(gamelog)
# }
# retrosheet for 2012 season
# gl2012 <- retrosheet_gamelog(2012)
gl2012 <- read_rds('./data/gl2012.rds')
gl2012 |>
glimpse()
## Rows: 2,430
## Columns: 161
## $ Date <dbl> 20120328, 20120329, 20120404, 20120405, 201…
## $ DoubleHeader <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ DayOfWeek <chr> "Wed", "Thu", "Wed", "Thu", "Thu", "Thu", "…
## $ VisitingTeam <chr> "SEA", "SEA", "SLN", "TOR", "BOS", "WAS", "…
## $ VisitingTeamLeague <chr> "AL", "AL", "NL", "AL", "AL", "NL", "NL", "…
## $ VisitingTeamGameNumber <dbl> 1, 2, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 3, 1, 1…
## $ HomeTeam <chr> "OAK", "OAK", "MIA", "CLE", "DET", "CHN", "…
## $ HomeTeamLeague <chr> "AL", "AL", "NL", "AL", "AL", "NL", "NL", "…
## $ HomeTeamGameNumber <dbl> 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1…
## $ VisitorRunsScored <dbl> 3, 1, 4, 7, 2, 2, 0, 0, 1, 5, 0, 2, 7, 6, 2…
## $ HomeRunsScore <dbl> 1, 4, 1, 4, 3, 1, 4, 1, 0, 3, 5, 4, 3, 7, 3…
## $ LengthInOuts <dbl> 66, 51, 54, 96, 52, 54, 51, 51, 54, 54, 51,…
## $ DayNight <chr> "N", "N", "N", "D", "D", "D", "D", "D", "D"…
## $ CompletionInfo <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ ForfeitInfo <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ ProtestInfo <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ ParkID <chr> "TOK01", "TOK01", "MIA02", "CLE08", "DET05"…
## $ Attendance <dbl> 44227, 43391, 36601, 43190, 45027, 41176, 4…
## $ Duration <dbl> 184, 143, 162, 314, 187, 155, 164, 159, 134…
## $ VisitorLineScore <chr> "00010000002", "000000100", "210000010", "0…
## $ HomeLineScore <chr> "00010000000", "00000031x", "000000010", "0…
## $ VisitorAB <dbl> 39, 30, 37, 56, 32, 32, 29, 31, 32, 33, 31,…
## $ VisitorH <dbl> 9, 3, 13, 10, 5, 4, 3, 4, 8, 7, 4, 6, 13, 9…
## $ VisitorD <dbl> 1, 0, 3, 3, 2, 1, 1, 1, 1, 2, 1, 0, 2, 1, 1…
## $ VisitorT <dbl> 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0…
## $ VisitorHR <dbl> 1, 1, 0, 2, 0, 0, 0, 0, 0, 1, 0, 1, 0, 1, 1…
## $ VisitorRBI <dbl> 3, 1, 4, 7, 2, 2, 0, 0, 1, 5, 0, 2, 6, 5, 2…
## $ VisitorSH <dbl> 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0…
## $ VisitorSF <dbl> 0, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 2, 0, 0…
## $ VisitorHBP <dbl> 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1…
## $ VisitorBB <dbl> 0, 1, 3, 7, 1, 6, 2, 3, 2, 5, 0, 4, 4, 7, 1…
## $ VisitorIBB <dbl> 0, 0, 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ VisitorK <dbl> 4, 7, 5, 16, 8, 10, 8, 8, 8, 10, 11, 6, 6, …
## $ VisitorSB <dbl> 2, 0, 1, 0, 1, 1, 0, 0, 1, 0, 0, 0, 2, 0, 0…
## $ VisitorCS <dbl> 1, 0, 1, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 1…
## $ VisitorGDP <dbl> 1, 0, 0, 3, 0, 0, 0, 0, 2, 0, 0, 1, 2, 1, 0…
## $ VisitorCI <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ VisitorLOB <dbl> 4, 3, 10, 10, 5, 9, 4, 7, 7, 7, 4, 7, 9, 12…
## $ VisitorPitchers <dbl> 3, 4, 4, 8, 5, 3, 3, 3, 2, 6, 3, 3, 4, 4, 3…
## $ VisitorER <dbl> 1, 4, 1, 4, 3, 1, 4, 1, 0, 3, 5, 4, 3, 7, 3…
## $ VisitorTER <dbl> 1, 4, 1, 4, 3, 1, 4, 1, 0, 3, 5, 4, 3, 7, 3…
## $ VisitorWP <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 1…
## $ VisitorBalks <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ VisitorPO <dbl> 33, 24, 27, 48, 25, 27, 24, 24, 27, 27, 24,…
## $ VisitorA <dbl> 7, 7, 10, 20, 7, 9, 13, 11, 13, 14, 9, 13, …
## $ VisitorE <dbl> 1, 0, 0, 1, 0, 1, 1, 1, 0, 0, 0, 0, 1, 0, 1…
## $ VisitorPassed <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0…
## $ VisitorDB <dbl> 0, 0, 1, 3, 3, 1, 1, 2, 1, 1, 2, 2, 1, 1, 0…
## $ VisitorTP <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ HomeAB <dbl> 39, 30, 30, 49, 31, 33, 30, 29, 28, 31, 31,…
## $ HomeH <dbl> 6, 7, 4, 7, 10, 6, 10, 7, 2, 5, 9, 6, 9, 12…
## $ HomeD <dbl> 3, 1, 1, 2, 2, 0, 4, 1, 0, 1, 1, 1, 0, 0, 1…
## $ HomeT <dbl> 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0…
## $ HomeHR <dbl> 0, 3, 0, 1, 0, 0, 1, 0, 0, 1, 0, 1, 1, 2, 1…
## $ HomeRBI <dbl> 1, 4, 1, 4, 3, 1, 4, 1, 0, 3, 5, 4, 3, 7, 3…
## $ HomeSH <dbl> 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 1…
## $ HomeSF <dbl> 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1…
## $ HomeHBP <dbl> 2, 0, 1, 1, 1, 0, 1, 0, 2, 0, 0, 0, 0, 0, 0…
## $ HomeBB <dbl> 0, 2, 0, 11, 4, 1, 4, 4, 0, 3, 1, 3, 1, 7, …
## $ HomeIBB <dbl> 0, 0, 0, 0, 1, 0, 2, 0, 0, 0, 1, 0, 0, 3, 0…
## $ HomeK <dbl> 10, 5, 6, 12, 4, 7, 5, 6, 6, 7, 8, 3, 7, 12…
## $ HomeSB <dbl> 2, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0…
## $ HomeCS <dbl> 1, 1, 0, 1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0…
## $ HomeGDP <dbl> 0, 0, 1, 3, 3, 1, 1, 2, 1, 1, 1, 1, 1, 1, 0…
## $ HomeCI <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ HomeLOB <dbl> 7, 4, 3, 10, 9, 6, 9, 8, 3, 4, 3, 3, 7, 10,…
## $ HomePitchers <dbl> 6, 2, 5, 6, 2, 3, 3, 5, 3, 4, 2, 4, 4, 7, 4…
## $ HomeER <dbl> 3, 1, 4, 7, 2, 2, 0, 0, 1, 4, 0, 2, 4, 6, 2…
## $ HomeTER <dbl> 3, 1, 4, 7, 2, 2, 0, 0, 1, 4, 0, 2, 4, 6, 2…
## $ HomeWP <dbl> 0, 0, 0, 1, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0…
## $ HomeBalks <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ HomePO <dbl> 33, 27, 27, 48, 27, 27, 27, 27, 27, 27, 27,…
## $ HomeA <dbl> 19, 9, 12, 22, 6, 8, 14, 6, 8, 10, 8, 10, 9…
## $ HomeE <dbl> 1, 0, 0, 0, 1, 1, 1, 0, 0, 3, 2, 0, 1, 1, 0…
## $ HomePassed <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0…
## $ HomeDB <dbl> 1, 0, 2, 3, 0, 0, 1, 0, 2, 1, 0, 1, 2, 1, 0…
## $ HomeTP <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ UmpireHID <chr> "hallt901", "nelsj901", "rapue901", "welkt9…
## $ UmpireHName <chr> "Tom Hallion", "Jeff Nelson", "Ed Rapuano",…
## $ Umpire1BID <chr> "nelsj901", "hudsm901", "herna901", "carlm9…
## $ Umpire1BName <chr> "Jeff Nelson", "Marvin Hudson", "Angel Hern…
## $ Umpire2BID <chr> "hudsm901", "belld901", "diazl901", "everm9…
## $ Umpire2BName <chr> "Marvin Hudson", "Dan Bellino", "Laz Diaz",…
## $ Umpire3BID <chr> "belld901", "hallt901", "hicke901", "schrp9…
## $ Umpire3BName <chr> "Dan Bellino", "Tom Hallion", "Ed Hickox", …
## $ UmpireLFID <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ UmpireLFName <chr> "(none)", "(none)", "(none)", "(none)", "(n…
## $ UmpireRFID <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ UmpireRFName <chr> "(none)", "(none)", "(none)", "(none)", "(n…
## $ VisitorManagerID <chr> "wedge001", "wedge001", "mathm001", "farrj0…
## $ VisitorManagerName <chr> "Eric Wedge", "Eric Wedge", "Mike Matheny",…
## $ HomeManagerID <chr> "melvb001", "melvb001", "guilo001", "actam8…
## $ HomeManagerName <chr> "Bob Melvin", "Bob Melvin", "Ozzie Guillen"…
## $ WinningPitcherID <chr> "wilht001", "colob001", "lohsk001", "perel0…
## $ WinningPitcherName <chr> "Tom Wilhelmsen", "Bartolo Colon", "Kyle Lo…
## $ LosingPitcherID <chr> "caria001", "kells001", "johnj009", "valdl0…
## $ LosingPitcherNAme <chr> "Andrew Carignan", "Shawn Kelley", "Josh Jo…
## $ SavingPitcherID <chr> "leagb001", "balfg001", "mottj001", NA, NA,…
## $ SavingPitcherName <chr> "Brandon League", "Grant Balfour", "Jason M…
## $ GameWinningRBIID <chr> "ackld001", "cespy001", "freed001", "arenj0…
## $ GameWinningRBIName <chr> "Dustin Ackley", "Yoenis Cespedes", "David …
## $ VisitorStartingPitcherID <chr> "hernf002", "vargj001", "lohsk001", "romer0…
## $ VisitorStartingPitcherName <chr> "Felix Hernandez", "Jason Vargas", "Kyle Lo…
## $ HomeStartingPitcherID <chr> "mccab001", "colob001", "johnj009", "mastj0…
## $ HomeStartingPitcherName <chr> "Brandon McCarthy", "Bartolo Colon", "Josh …
## $ VisitorBatting1PlayerID <chr> "figgc001", "figgc001", "furcr001", "escoy0…
## $ VisitorBatting1Name <chr> "Chone Figgins", "Chone Figgins", "Rafael F…
## $ VisitorBatting1Position <dbl> 5, 7, 6, 6, 8, 6, 6, 8, 8, 6, 7, 8, 7, 6, 8…
## $ VisitorBatting2PlayerID <chr> "ackld001", "ackld001", "beltc001", "johnk0…
## $ VisitorBatting2Name <chr> "Dustin Ackley", "Dustin Ackley", "Carlos B…
## $ VisitorBatting2Position <dbl> 4, 4, 9, 4, 4, 4, 8, 5, 5, 4, 8, 6, 4, 8, 5…
## $ VisitorBatting3PlayerID <chr> "suzui001", "suzui001", "hollm001", "bautj0…
## $ VisitorBatting3Name <chr> "Ichiro Suzuki", "Ichiro Suzuki", "Matt Hol…
## $ VisitorBatting3Position <dbl> 9, 9, 7, 9, 3, 5, 5, 2, 6, 8, 3, 2, 9, 4, 1…
## $ VisitorBatting4PlayerID <chr> "smoaj001", "smoaj001", "berkl001", "linda0…
## $ VisitorBatting4Name <chr> "Justin Smoak", "Justin Smoak", "Lance Berk…
## $ VisitorBatting4Position <dbl> 3, 3, 3, 3, 10, 3, 9, 4, 9, 9, 10, 10, 3, 5…
## $ VisitorBatting5PlayerID <chr> "montj003", "montj003", "freed001", "encae0…
## $ VisitorBatting5Name <chr> "Jesus Montero", "Jesus Montero", "David Fr…
## $ VisitorBatting5Position <dbl> 10, 10, 5, 10, 5, 9, 3, 3, 3, 7, 9, 7, 10, …
## $ VisitorBatting6PlayerID <chr> "carpm001", "seagk001", "moliy001", "lawrb0…
## $ VisitorBatting6Name <chr> "Mike Carp", "Kyle Seager", "Yadier Molina"…
## $ VisitorBatting6Position <dbl> 7, 5, 2, 5, 9, 7, 7, 7, 7, 3, 4, 9, 5, 9, 9…
## $ VisitorBatting7PlayerID <chr> "olivm001", "olivm001", "jay-j001", "thame0…
## $ VisitorBatting7Name <chr> "Miguel Olivo", "Miguel Olivo", "Jon Jay", …
## $ VisitorBatting7Position <dbl> 2, 2, 8, 7, 7, 8, 4, 9, 2, 5, 5, 5, 2, 10, …
## $ VisitorBatting8PlayerID <chr> "saunm001", "saunm001", "descd001", "arenj0…
## $ VisitorBatting8Name <chr> "Michael Saunders", "Michael Saunders", "Da…
## $ VisitorBatting8Position <dbl> 8, 8, 4, 2, 2, 2, 2, 6, 4, 2, 2, 3, 8, 2, 7…
## $ VisitorBatting9PlayerID <chr> "ryanb002", "ryanb002", "lohsk001", "rasmc0…
## $ VisitorBatting9Name <chr> "Brendan Ryan", "Brendan Ryan", "Kyle Lohse…
## $ VisitorBatting9Position <dbl> 6, 6, 1, 8, 6, 1, 1, 1, 1, 1, 6, 4, 6, 7, 4…
## $ HomeBatting1PlayerID <chr> "weekj001", "weekj001", "reyej001", "branm0…
## $ HomeBatting1Name <chr> "Jemile Weeks", "Jemile Weeks", "Jose Reyes…
## $ HomeBatting1Position <dbl> 4, 4, 6, 8, 8, 9, 4, 8, 7, 8, 6, 7, 4, 8, 4…
## $ HomeBatting2PlayerID <chr> "pennc001", "pennc001", "bonie001", "cabra0…
## $ HomeBatting2Name <chr> "Cliff Pennington", "Cliff Pennington", "Em…
## $ HomeBatting2Position <dbl> 6, 6, 8, 6, 9, 4, 6, 4, 9, 9, 4, 6, 7, 9, 6…
## $ HomeBatting3PlayerID <chr> "crisc001", "crisc001", "ramih003", "choos0…
## $ HomeBatting3Name <chr> "Coco Crisp", "Coco Crisp", "Hanley Ramirez…
## $ HomeBatting3Position <dbl> 7, 7, 5, 9, 5, 6, 3, 5, 8, 5, 3, 9, 9, 5, 8…
## $ HomeBatting4PlayerID <chr> "smits002", "gomej001", "stanm004", "santc0…
## $ HomeBatting4Name <chr> "Seth Smith", "Jonny Gomes", "Giancarlo Sta…
## $ HomeBatting4Position <dbl> 10, 10, 9, 2, 3, 7, 5, 3, 4, 7, 9, 8, 10, 1…
## $ HomeBatting5PlayerID <chr> "suzuk001", "suzuk001", "morrl001", "hafnt0…
## $ HomeBatting5Name <chr> "Kurt Suzuki", "Kurt Suzuki", "Logan Morris…
## $ HomeBatting5Position <dbl> 2, 2, 7, 10, 7, 5, 9, 7, 3, 2, 7, 2, 8, 6, …
## $ HomeBatting6PlayerID <chr> "reddj001", "cespy001", "sancg001", "duncs0…
## $ HomeBatting6Name <chr> "Josh Reddick", "Yoenis Cespedes", "Gaby Sa…
## $ HomeBatting6Position <dbl> 9, 8, 3, 7, 10, 3, 7, 9, 2, 3, 10, 10, 2, 3…
## $ HomeBatting7PlayerID <chr> "cespy001", "reddj001", "infao001", "kotcc0…
## $ HomeBatting7Name <chr> "Yoenis Cespedes", "Josh Reddick", "Omar In…
## $ HomeBatting7Position <dbl> 8, 9, 4, 3, 6, 8, 8, 2, 5, 4, 5, 5, 5, 4, 3…
## $ HomeBatting8PlayerID <chr> "alleb001", "donaj001", "buckj001", "kipnj0…
## $ HomeBatting8Name <chr> "Brandon Allen", "Josh Donaldson", "John Bu…
## $ HomeBatting8Position <dbl> 3, 5, 2, 4, 2, 2, 2, 6, 6, 6, 2, 3, 3, 2, 2…
## $ HomeBatting9PlayerID <chr> "sogae001", "kaaik001", "johnj009", "hannj0…
## $ HomeBatting9Name <chr> "Eric Sogard", "Kila Ka'aihue", "Josh Johns…
## $ HomeBatting9Position <dbl> 5, 3, 1, 5, 4, 1, 1, 1, 1, 1, 8, 4, 6, 7, 7…
## $ AdditionalInfo <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ AcquisitionInfo <chr> "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y"…