library(tidyverse)

## Registered S3 methods overwritten by 'ggplot2':
##   method         from 
##   [.quosures     rlang
##   c.quosures     rlang
##   print.quosures rlang

## Registered S3 method overwritten by 'rvest':
##   method            from
##   read_xml.response xml2

## ── Attaching packages ───────────────────────────────────────────────────────── tidyverse 1.2.1 ──

## ✔ ggplot2 3.1.1       ✔ purrr   0.3.2  
## ✔ tibble  2.1.1       ✔ dplyr   0.8.0.1
## ✔ tidyr   0.8.3       ✔ stringr 1.4.0  
## ✔ readr   1.3.1       ✔ forcats 0.4.0

## ── Conflicts ──────────────────────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()

library(RSQLite)
library(DBI)

Gettting to Know the Data

# R has trouble guessing some column types, so we explicitly tell it 
# the types of the problem columns
log <- read_csv("game_log.csv", 
                col_types = cols(.default = "c",
                                 v_league = "c", h_league = "c",
                                 `3b_umpire_id` = "c", `3b_umpire_name` = "c",
                                 `2b_umpire_id` = "c", `2b_umpire_name` = "c",
                                 `lf_umpire_id` = "c", `lf_umpire_name` = "c",
                                 `rf_umpire_id` = "c", `rf_umpire_name` = "c",
                                 completion = "c", winning_rbi_batter_id = "c",
                                 winning_rbi_batter_id_name = "c", protest = "c",
                                 v_first_catcher_interference = "c", 
                                 h_first_catcher_interference = "c"))
head(log)

## # A tibble: 6 x 161
##   date  number_of_game day_of_week v_name v_league v_game_number h_name
##   <chr> <chr>          <chr>       <chr>  <chr>    <chr>         <chr> 
## 1 1871… 0              Thu         CL1    <NA>     1             FW1   
## 2 1871… 0              Fri         BS1    <NA>     1             WS3   
## 3 1871… 0              Sat         CL1    <NA>     2             RC1   
## 4 1871… 0              Mon         CL1    <NA>     3             CH1   
## 5 1871… 0              Tue         BS1    <NA>     2             TRO   
## 6 1871… 0              Thu         CH1    <NA>     2             CL1   
## # … with 154 more variables: h_league <chr>, h_game_number <chr>,
## #   v_score <chr>, h_score <chr>, length_outs <chr>, day_night <chr>,
## #   completion <chr>, forefeit <chr>, protest <chr>, park_id <chr>,
## #   attendance <chr>, length_minutes <chr>, v_line_score <chr>,
## #   h_line_score <chr>, v_at_bats <chr>, v_hits <chr>, v_doubles <chr>,
## #   v_triples <chr>, v_homeruns <chr>, v_rbi <chr>,
## #   v_sacrifice_hits <chr>, v_sacrifice_flies <chr>, v_hit_by_pitch <chr>,
## #   v_walks <chr>, v_intentional_walks <chr>, v_strikeouts <chr>,
## #   v_stolen_bases <chr>, v_caught_stealing <chr>,
## #   v_grounded_into_double <chr>, v_first_catcher_interference <chr>,
## #   v_left_on_base <chr>, v_pitchers_used <chr>,
## #   v_individual_earned_runs <chr>, v_team_earned_runs <chr>,
## #   v_wild_pitches <chr>, v_balks <chr>, v_putouts <chr>, v_assists <chr>,
## #   v_errors <chr>, v_passed_balls <chr>, v_double_plays <chr>,
## #   v_triple_plays <chr>, h_at_bats <chr>, h_hits <chr>, h_doubles <chr>,
## #   h_triples <chr>, h_homeruns <chr>, h_rbi <chr>,
## #   h_sacrifice_hits <chr>, h_sacrifice_flies <chr>, h_hit_by_pitch <chr>,
## #   h_walks <chr>, h_intentional_walks <chr>, h_strikeouts <chr>,
## #   h_stolen_bases <chr>, h_caught_stealing <chr>,
## #   h_grounded_into_double <chr>, h_first_catcher_interference <chr>,
## #   h_left_on_base <chr>, h_pitchers_used <chr>,
## #   h_individual_earned_runs <chr>, h_team_earned_runs <chr>,
## #   h_wild_pitches <chr>, h_balks <chr>, h_putouts <chr>, h_assists <chr>,
## #   h_errors <chr>, h_passed_balls <chr>, h_double_plays <chr>,
## #   h_triple_plays <chr>, hp_umpire_id <chr>, hp_umpire_name <chr>,
## #   `1b_umpire_id` <chr>, `1b_umpire_name` <chr>, `2b_umpire_id` <chr>,
## #   `2b_umpire_name` <chr>, `3b_umpire_id` <chr>, `3b_umpire_name` <chr>,
## #   lf_umpire_id <chr>, lf_umpire_name <chr>, rf_umpire_id <chr>,
## #   rf_umpire_name <chr>, v_manager_id <chr>, v_manager_name <chr>,
## #   h_manager_id <chr>, h_manager_name <chr>, winning_pitcher_id <chr>,
## #   winning_pitcher_name <chr>, losing_pitcher_id <chr>,
## #   losing_pitcher_name <chr>, saving_pitcher_id <chr>,
## #   saving_pitcher_name <chr>, winning_rbi_batter_id <chr>,
## #   winning_rbi_batter_id_name <chr>, v_starting_pitcher_id <chr>,
## #   v_starting_pitcher_name <chr>, h_starting_pitcher_id <chr>,
## #   h_starting_pitcher_name <chr>, v_player_1_id <chr>,
## #   v_player_1_name <chr>, …

dim(log)

## [1] 171907    161

It looks like the game log has a record of over 170,000 games. It looks like these games are chronologically ordered and occur between 1871 and 2016.

For each game we have:

general information on the game
team level stats for each team
a list of players from each team, numbered, with their defensive positions
the umpires that officiated the game
some ‘awards’, like winning and losing pitcher

We have a game_log_fields.txt file that tell us that the player number corresponds with the order in which they batted.

It’s worth noting that there is no natural primary key column for this table.

person <- read_csv("person_codes.csv")

## Parsed with column specification:
## cols(
##   id = col_character(),
##   last = col_character(),
##   first = col_character(),
##   player_debut = col_character(),
##   mgr_debut = col_character(),
##   coach_debut = col_character(),
##   ump_debut = col_character()
## )

head(person)

## # A tibble: 6 x 7
##   id       last    first    player_debut mgr_debut coach_debut ump_debut
##   <chr>    <chr>   <chr>    <chr>        <chr>     <chr>       <chr>    
## 1 aardd001 Aardsma David    04/06/2004   <NA>      <NA>        <NA>     
## 2 aaroh101 Aaron   Hank     04/13/1954   <NA>      <NA>        <NA>     
## 3 aarot101 Aaron   Tommie   04/10/1962   <NA>      04/06/1979  <NA>     
## 4 aased001 Aase    Don      07/26/1977   <NA>      <NA>        <NA>     
## 5 abada001 Abad    Andy     09/10/2001   <NA>      <NA>        <NA>     
## 6 abadf001 Abad    Fernando 07/28/2010   <NA>      <NA>        <NA>

dim(person)

## [1] 20494     7

This seems to be a list of people with IDs. The IDs look like they match up with those used in the game log. There are debut dates, for players, managers, coaches and umpires. We can see that some people might have been one or more of these roles.

It also looks like coaches and managers are two different things in baseball. After some research, managers are what would be called a ‘coach’ or ‘head coach’ in other sports, and coaches are more specialized, like base coaches. It also seems like coaches aren’t recorded in the game log.

park <- read_csv("park_codes.csv")

## Parsed with column specification:
## cols(
##   park_id = col_character(),
##   name = col_character(),
##   aka = col_character(),
##   city = col_character(),
##   state = col_character(),
##   start = col_character(),
##   end = col_character(),
##   league = col_character(),
##   notes = col_character()
## )

head(park)

## # A tibble: 6 x 9
##   park_id name      aka         city   state start end   league notes      
##   <chr>   <chr>     <chr>       <chr>  <chr> <chr> <chr> <chr>  <chr>      
## 1 ALB01   Riversid… <NA>        Albany NY    09/1… 05/3… NL     TRN:9/11/8…
## 2 ALT01   Columbia… <NA>        Altoo… PA    04/3… 05/3… UA     <NA>       
## 3 ANA01   Angel St… Edison Fie… Anahe… CA    04/1… <NA>  AL     <NA>       
## 4 ARL01   Arlingto… <NA>        Arlin… TX    04/2… 10/0… AL     <NA>       
## 5 ARL02   Rangers … The Ballpa… Arlin… TX    04/1… <NA>  AL     <NA>       
## 6 ATL01   Atlanta-… <NA>        Atlan… GA    04/1… 09/2… NL     <NA>

dim(park)

## [1] 252   9

This seems to be a list of all baseball parks. There are IDs which seem to match with the game log, as well as names, nicknames, city and league.

team <- read_csv("team_codes.csv")

## Parsed with column specification:
## cols(
##   team_id = col_character(),
##   league = col_character(),
##   start = col_double(),
##   end = col_double(),
##   city = col_character(),
##   nickname = col_character(),
##   franch_id = col_character(),
##   seq = col_double()
## )

head(team)

## # A tibble: 6 x 8
##   team_id league start   end city      nickname        franch_id   seq
##   <chr>   <chr>  <dbl> <dbl> <chr>     <chr>           <chr>     <dbl>
## 1 ALT     UA      1884  1884 Altoona   Mountain Cities ALT           1
## 2 ARI     NL      1998     0 Arizona   Diamondbacks    ARI           1
## 3 BFN     NL      1879  1885 Buffalo   Bisons          BFN           1
## 4 BFP     PL      1890  1890 Buffalo   Bisons          BFP           1
## 5 BL1     <NA>    1872  1874 Baltimore Canaries        BL1           1
## 6 BL2     AA      1882  1891 Baltimore Orioles         BL2           1

dim(team)

## [1] 150   8

This seems to be a list of all teams, with team_ids which seem to match the game log.

Defensive Positions

In the game log, each player has a defensive position listed, which seems to be a number between 1-10. Doing some research around this, I found this [article] (http://probaseballinsider.com/baseball-instruction/baseball-basics/baseball-basics-positions/) which gives us a list of names for each numbered position:

Pitcher
Catcher
1st Base
2nd Base
3rd Base
Shortstop
Left Field
Center Field
Right Field

The 10th position isn’t included, it may be a way of describing a designated hitter that does not field. I can find a retrosheet page that indicates that position 0 is used for this, but we don’t have any position 0 in our data. I have chosen to make this an ‘Unknown Position’ so I’m not including data based on a hunch.

Leagues

Wikipedia tells us there are currently two leagues - the American (AL) and National (NL). Upon investigation of the data, we see that there are actually 4 more. After some googling, we come up with:

NL: National League
AL: American League
AA: American Association
FL: Federal League
PL: Players League
UA: Union Association

It also looks like we have about 1000 games where the home team doesn’t have a value for league.

Importing Data Into SQLite

conn <- dbConnect(SQLite(), "mlb.db")

dbWriteTable(conn = conn, name = "game_log", 
             value = log, row.names = FALSE, header = TRUE)

dbWriteTable(conn = conn, name = "person_codes", 
             value = person, row.names = FALSE, header = TRUE)

dbWriteTable(conn = conn, name = "team_codes", 
             value = team, row.names = FALSE, header = TRUE)

dbWriteTable(conn = conn, name = "park_codes", 
             value = park, row.names = FALSE, header = TRUE)

# Confirm that all of the tables are in 
dbListTables(conn)

## [1] "game_log"     "park_codes"   "person_codes" "team_codes"

# Create the new column within game_log
c1 <- "
  ALTER TABLE game_log
  ADD COLUMN game_id TEXT;
"

dbExecute(conn, c1)

## [1] 0

# Use string concatenation to update this new column
c2 <- "
  UPDATE game_log
  SET game_id = date || h_name || number_of_game
  /* WHERE prevents this if it has already been done */
  WHERE game_id IS NULL;
"

dbExecute(conn, c2)

## [1] 171907

# Make sure that your queries worked
q1 <- "
  SELECT
      game_id,
      date,
      h_name,
      number_of_game
  FROM game_log
  LIMIT 5;
"

check <- dbGetQuery(conn, q1)

Looking for Normalization Opportunities

The following are opportunities for normalization of our data:

In person_codes, all the debut dates will be able to be reproduced using game log data.
In team_codes, the start, end and sequence columns will be able to be reproduced using game log data.
In park_codes, the start and end years will be able to be reproduced using game log data. While technically the state is an attribute of the city, we might not want to have a an incomplete city/state table so we will leave this in.
There are lots of places in game log where we have a player ID followed by the players name. We will be able to remove this and use the name data in person_codes
In game_log, all offensive and defensive stats are repeated for the home team and the visiting team. We could break these out and have a table that lists each game twice, one for each team, and cut out this column repetition.
Similarly, in game_log, we have a listing for 9 players on each team with their positions - we can remove these and have one table that tracks player appearances and their positions.
We can do a similar thing with the umpires from game_log, instead of listing all four positions as columns, we can put the umpires either in their own table or make one table for players, umpires and managers.
We have several awards in game_log like winning pitcher and losing pitcher. We can either break these out into their own table, have a table for awards, or combine the awards in with general appearances like the players and umpires.

Creating Tables Without Foreign Keys

c3 <- "
  CREATE TABLE IF NOT EXISTS person (
      person_id TEXT PRIMARY KEY,
      first_name TEXT,
      last_name TEXT
  );
"

c4 <- "
  INSERT OR IGNORE INTO person
  SELECT
      id,
      first,
      last
  FROM person_codes;
"

q2 <- "
  SELECT * FROM person
  LIMIT 5;
"

dbExecute(conn, c3)

## [1] 0

dbExecute(conn, c4)

## [1] 20494

check <- dbGetQuery(conn, q2)

c5 <- "
  CREATE TABLE IF NOT EXISTS park (
      park_id TEXT PRIMARY KEY,
      name TEXT,
      nickname TEXT,
      city TEXT,
      state TEXT,
      notes TEXT
  );
"

c6 <- "
  INSERT OR IGNORE INTO park
  SELECT
      park_id,
      name,
      aka,
      city,
      state,
      notes
  FROM park_codes;
"

q3 <- "
  SELECT * FROM park
  LIMIT 5;
"

dbExecute(conn, c5)

## [1] 0

dbExecute(conn, c6)

## [1] 252

check <- dbGetQuery(conn, q3)

c7 <- "
  CREATE TABLE IF NOT EXISTS league (
      league_id TEXT PRIMARY KEY,
      name TEXT
  );
"

c8 <- '
  INSERT OR IGNORE INTO league
  VALUES
      ("NL", "National League"),
      ("AL", "American League"),
      ("AA", "American Association"),
      ("FL", "Federal League"),
      ("PL", "Players League"),
      ("UA", "Union Association")
  ;
'

q4 <- "
  SELECT * FROM league
"

dbExecute(conn, c7)

## [1] 0

dbExecute(conn, c8)

## [1] 6

check <- dbGetQuery(conn, q4)

c9 <- "DROP TABLE IF EXISTS appearance_type;"
dbExecute(conn, c9)

## [1] 0

appearance_type = read_csv('appearance_type.csv')

## Parsed with column specification:
## cols(
##   appearance_type_id = col_character(),
##   name = col_character(),
##   category = col_character()
## )

dbWriteTable(conn = conn, name = "appearance_type", 
             value = appearance_type,
             row.names = FALSE, header = TRUE)

q5 <- "SELECT * FROM appearance_type;"
check <- dbGetQuery(conn, q5)

Adding The Team and Game Tables

c11 <- "
  CREATE TABLE IF NOT EXISTS team (
      team_id TEXT PRIMARY KEY,
      league_id TEXT,
      city TEXT,
      nickname TEXT,
      franch_id TEXT,
      FOREIGN KEY (league_id) REFERENCES league(league_id)
  );
"

c12 <- "
  INSERT OR IGNORE INTO team
  SELECT
      team_id,
      league,
      city,
      nickname,
      franch_id
  FROM team_codes;
"

q6 <- "SELECT * FROM team LIMIT 5;"

dbExecute(conn, c11)

## [1] 0

dbExecute(conn, c12)

## [1] 149

check <- dbGetQuery(conn, q6)

c13 <- "
  CREATE TABLE IF NOT EXISTS game (
      game_id TEXT PRIMARY KEY,
      date TEXT,
      number_of_game INTEGER,
      park_id TEXT,
      length_outs INTEGER,
      day BOOLEAN,
      completion TEXT,
      forefeit TEXT,
      protest TEXT,
      attendance INTEGER,
      legnth_minutes INTEGER,
      additional_info TEXT,
      acquisition_info TEXT,
      FOREIGN KEY (park_id) REFERENCES park(park_id)
  );
"

c14 <- '
  INSERT OR IGNORE INTO game
  SELECT
      game_id,
      date,
      number_of_game,
      park_id,
      length_outs,
      CASE
          WHEN day_night = "D" THEN 1
          WHEN day_night = "N" THEN 0
          ELSE NULL
          END
          AS day,
      completion,
      forefeit,
      protest,
      attendance,
      length_minutes,
      additional_info,
      acquisition_info
  FROM game_log;
'

q7 <- "SELECT * FROM game LIMIT 5;"

dbExecute(conn, c13)

## [1] 0

dbExecute(conn, c14)

## [1] 171907

check <- dbGetQuery(conn, q7)

Adding the Team Appearance Table

c15 <- "
  CREATE TABLE IF NOT EXISTS team_appearance (
      team_id TEXT,
      game_id TEXT,
      home BOOLEAN,
      league_id TEXT,
      score INTEGER,
      line_score TEXT,
      at_bats INTEGER,
      hits INTEGER,
      doubles INTEGER,
      triples INTEGER,
      homeruns INTEGER,
      rbi INTEGER,
      sacrifice_hits INTEGER,
      sacrifice_flies INTEGER,
      hit_by_pitch INTEGER,
      walks INTEGER,
      intentional_walks INTEGER,
      strikeouts INTEGER,
      stolen_bases INTEGER,
      caught_stealing INTEGER,
      grounded_into_double INTEGER,
      first_catcher_interference INTEGER,
      left_on_base INTEGER,
      pitchers_used INTEGER,
      individual_earned_runs INTEGER,
      team_earned_runs INTEGER,
      wild_pitches INTEGER,
      balks INTEGER,
      putouts INTEGER,
      assists INTEGER,
      errors INTEGER,
      passed_balls INTEGER,
      double_plays INTEGER,
      triple_plays INTEGER,
      PRIMARY KEY (team_id, game_id),
      FOREIGN KEY (team_id) REFERENCES team(team_id),
      FOREIGN KEY (game_id) REFERENCES game(game_id),
      FOREIGN KEY (team_id) REFERENCES team(team_id)
  );
"

c16 <- "
  INSERT OR IGNORE INTO team_appearance
      SELECT
          h_name,
          game_id,
          1 AS home,
          h_league,
          h_score,
          h_line_score,
          h_at_bats,
          h_hits,
          h_doubles,
          h_triples,
          h_homeruns,
          h_rbi,
          h_sacrifice_hits,
          h_sacrifice_flies,
          h_hit_by_pitch,
          h_walks,
          h_intentional_walks,
          h_strikeouts,
          h_stolen_bases,
          h_caught_stealing,
          h_grounded_into_double,
          h_first_catcher_interference,
          h_left_on_base,
          h_pitchers_used,
          h_individual_earned_runs,
          h_team_earned_runs,
          h_wild_pitches,
          h_balks,
          h_putouts,
          h_assists,
          h_errors,
          h_passed_balls,
          h_double_plays,
          h_triple_plays
      FROM game_log
  
  UNION
  
      SELECT    
          v_name,
          game_id,
          0 AS home,
          v_league,
          v_score,
          v_line_score,
          v_at_bats,
          v_hits,
          v_doubles,
          v_triples,
          v_homeruns,
          v_rbi,
          v_sacrifice_hits,
          v_sacrifice_flies,
          v_hit_by_pitch,
          v_walks,
          v_intentional_walks,
          v_strikeouts,
          v_stolen_bases,
          v_caught_stealing,
          v_grounded_into_double,
          v_first_catcher_interference,
          v_left_on_base,
          v_pitchers_used,
          v_individual_earned_runs,
          v_team_earned_runs,
          v_wild_pitches,
          v_balks,
          v_putouts,
          v_assists,
          v_errors,
          v_passed_balls,
          v_double_plays,
          v_triple_plays
      from game_log;
"

q8 <- "
  SELECT * FROM team_appearance
  WHERE game_id = (
                   SELECT MIN(game_id) from game
                  )
     OR game_id = (
                   SELECT MAX(game_id) from game
                  )
  ORDER By game_id, home;
"

dbExecute(conn, c15)

## [1] 0

dbExecute(conn, c16)

## [1] 343814

check <- dbGetQuery(conn, q8)

Adding the Person Appearance Table

c17 <- "DROP TABLE IF EXISTS person_appearance"

dbExecute(conn, c17)

## [1] 0

c18 <- "
  CREATE TABLE person_appearance (
      appearance_id INTEGER PRIMARY KEY,
      person_id TEXT,
      team_id TEXT,
      game_id TEXT,
      appearance_type_id,
      FOREIGN KEY (person_id) REFERENCES person(person_id),
      FOREIGN KEY (team_id) REFERENCES team(team_id),
      FOREIGN KEY (game_id) REFERENCES game(game_id),
      FOREIGN KEY (appearance_type_id) REFERENCES appearance_type(appearance_type_id)
  );
"

c19 <- '
  INSERT OR IGNORE INTO person_appearance (
      game_id,
      team_id,
      person_id,
      appearance_type_id
  ) 
      SELECT
          game_id,
          NULL,
          hp_umpire_id,
          "UHP"
      FROM game_log
      WHERE hp_umpire_id IS NOT NULL    
  
  UNION
  
      SELECT
          game_id,
          NULL,
          [1b_umpire_id],
          "U1B"
      FROM game_log
      WHERE "1b_umpire_id" IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          NULL,
          [2b_umpire_id],
          "U2B"
      FROM game_log
      WHERE [2b_umpire_id] IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          NULL,
          [3b_umpire_id],
          "U3B"
      FROM game_log
      WHERE [3b_umpire_id] IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          NULL,
          lf_umpire_id,
          "ULF"
      FROM game_log
      WHERE lf_umpire_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          NULL,
          rf_umpire_id,
          "URF"
      FROM game_log
      WHERE rf_umpire_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          v_name,
          v_manager_id,
          "MM"
      FROM game_log
      WHERE v_manager_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          h_name,
          h_manager_id,
          "MM"
      FROM game_log
      WHERE h_manager_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          CASE
              WHEN h_score > v_score THEN h_name
              ELSE v_name
              END,
          winning_pitcher_id,
          "AWP"
      FROM game_log
      WHERE winning_pitcher_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          CASE
              WHEN h_score < v_score THEN h_name
              ELSE v_name
              END,
          losing_pitcher_id,
          "ALP"
      FROM game_log
      WHERE losing_pitcher_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          CASE
              WHEN h_score > v_score THEN h_name
              ELSE v_name
              END,
          saving_pitcher_id,
          "ASP"
      FROM game_log
      WHERE saving_pitcher_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          CASE
              WHEN h_score > v_score THEN h_name
              ELSE v_name
              END,
          winning_rbi_batter_id,
          "AWB"
      FROM game_log
      WHERE winning_rbi_batter_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          v_name,
          v_starting_pitcher_id,
          "PSP"
      FROM game_log
      WHERE v_starting_pitcher_id IS NOT NULL
  
  UNION
  
      SELECT
          game_id,
          h_name,
          h_starting_pitcher_id,
          "PSP"
      FROM game_log
      WHERE h_starting_pitcher_id IS NOT NULL;
'

dbExecute(conn, c18)

## [1] 0

dbExecute(conn, c19)

## [1] 1646118

for (letter in c("h", "v")) {
  for (num in 1:9) {
    template <- '
      INSERT INTO person_appearance (
          game_id,
          team_id,
          person_id,
          appearance_type_id
      ) 
          SELECT
              game_id,
              %s_name,
              %s_player_%f_id,
              "O%f"
          FROM game_log
          WHERE %s_player_%f_id IS NOT NULL
      
      UNION
      
          SELECT
              game_id,
              %s_name,
              %s_player_%f_id,
              "D" || CAST(%s_player_%f_def_pos AS INT)
          FROM game_log
          WHERE %s_player_%f_id IS NOT NULL;
    '
    # replace all of the %s and %f with the correct letter number
    template = gsub("%s", letter, template, fixed = TRUE)
    template = gsub("%f", num, template, fixed = TRUE)
    
    dbExecute(conn, template)
  }
}

Removing the Original Tables

# Check the current status of the db file
dbListTables(conn)

##  [1] "appearance_type"   "game"              "game_log"         
##  [4] "league"            "park"              "park_codes"       
##  [7] "person"            "person_appearance" "person_codes"     
## [10] "team"              "team_appearance"   "team_codes"

# Iterate through each of the tables
tables <- c("game_log", "park_codes",
            "team_codes", "person_codes")

for (t in tables) {
  drop_command = sprintf("DROP TABLE %s", t)
  dbExecute(conn, drop_command)
}

# Make sure that everything is gone
dbListTables(conn)

## [1] "appearance_type"   "game"              "league"           
## [4] "park"              "person"            "person_appearance"
## [7] "team"              "team_appearance"

dbDisconnect(conn)

Mission 376 Solutions