library(tidyverse)## -- Attaching packages -------------------------------------------- tidyverse 1.3.0 --## v ggplot2 3.3.2 v purrr 0.3.4
## v tibble 3.0.3 v dplyr 1.0.1
## v tidyr 1.1.1 v stringr 1.4.0
## v readr 1.3.1 v forcats 0.5.0## -- Conflicts ----------------------------------------------- tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()library(RSQLite)
library(DBI)
library(RSQL)
library(dbplyr)##
## Attaching package: 'dbplyr'## The following objects are masked from 'package:dplyr':
##
## ident, sqllibrary(dplyr)
library(odbc)# 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 h_league
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 1871~ 0 Thu CL1 <NA> 1 FW1 <NA>
## 2 1871~ 0 Fri BS1 <NA> 1 WS3 <NA>
## 3 1871~ 0 Sat CL1 <NA> 2 RC1 <NA>
## 4 1871~ 0 Mon CL1 <NA> 3 CH1 <NA>
## 5 1871~ 0 Tue BS1 <NA> 2 TRO <NA>
## 6 1871~ 0 Thu CH1 <NA> 2 CL1 <NA>
## # ... with 153 more variables: 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>,
## # v_player_1_def_pos <chr>, ...dim(log)## [1] 171907 161It looks like the game log has 170,000+ games recorded. Also it looks like these games are chronologically ordered and occur between 1871 and 2016.
For each game we have:
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 7This seems to be a list of people with IDs. The IDs look like they match with IDs in the game log. There are debut dates for players, managers, coaches, and umpires.
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.
park <- read.csv("park_codes.csv")
head(park)## park_id name
## 1 ALB01 Riverside Park
## 2 ALT01 Columbia Park
## 3 ANA01 Angel Stadium of Anaheim
## 4 ARL01 Arlington Stadium
## 5 ARL02 Rangers Ballpark in Arlington
## 6 ATL01 Atlanta-Fulton County Stadium
## aka city state start
## 1 Albany NY 09/11/1880
## 2 Altoona PA 04/30/1884
## 3 Edison Field; Anaheim Stadium Anaheim CA 04/19/1966
## 4 Arlington TX 04/21/1972
## 5 The Ballpark in Arlington; Ameriquest Fl Arlington TX 04/11/1994
## 6 Atlanta GA 04/12/1966
## end league notes
## 1 05/30/1882 NL TRN:9/11/80;6/15&9/10/1881;5/16-5/18&5/30/1882
## 2 05/31/1884 UA
## 3 AL
## 4 10/03/1993 AL
## 5 AL
## 6 09/23/1996 NLdim(park)## [1] 252 9This 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")
head(team)## team_id league start end city nickname franch_id seq
## 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 1872 1874 Baltimore Canaries BL1 1
## 6 BL2 AA 1882 1891 Baltimore Orioles BL2 1dim(team)## [1] 150 8This seems to be a list of all teams, with team_ids that seem to match the game log.
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:
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.
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:
It also looks like we have about 1000 games where the home team doesn’t have a value for league.
conn <- dbConnect(SQLite(), "mlb.db")
dbWriteTable(conn = conn, name = "game_log",
value = log, row.names = FALSE, header = TRUE, overwrite=TRUE)
dbWriteTable(conn = conn, name = "person_codes",
value = person, row.names = FALSE, header = TRUE, overwrite=TRUE)
dbWriteTable(conn = conn, name = "team_codes",
value = team, row.names = FALSE, header = TRUE, overwrite=TRUE)
dbWriteTable(conn = conn, name = "park_codes",
value = park, row.names = FALSE, header = TRUE, overwrite=TRUE)
# Confirm that all of the tables are in
dbListTables(conn)## [1] "appearance_type" "game" "game_log"
## [4] "games_log" "league" "park"
## [7] "park_code" "park_codes" "person"
## [10] "person_appearance" "person_code" "person_codes"
## [13] "team" "team_appearance" "team_code"
## [16] "team_codes"# Create the new column within game_log
alter_game_log_command <- "
ALTER TABLE game_log
ADD COLUMN game_id TEXT;
"
dbExecute(conn, alter_game_log_command)## [1] 0# Use string concatenation to update this new column
update_game_log_command <- "
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, update_game_log_command)## [1] 171907# Make sure that your queries worked
check_query_1 <- "
SELECT
game_id,
date,
h_name,
number_of_game
FROM game_log
LIMIT 5;
"
check <- dbGetQuery(conn, check_query_1)
head(check)## game_id date h_name number_of_game
## 1 18710504FW10 18710504 FW1 0
## 2 18710505WS30 18710505 WS3 0
## 3 18710506RC10 18710506 RC1 0
## 4 18710508CH10 18710508 CH1 0
## 5 18710509TRO0 18710509 TRO 0The following are opportunities for normalization of our data:
create_person_command <- "
CREATE TABLE IF NOT EXISTS person (
person_id TEXT PRIMARY KEY,
first_name TEXT,
last_name TEXT
);
"
dbExecute(conn, create_person_command)## [1] 0insert_to_person <- "
INSERT OR IGNORE INTO person
SELECT
id,
first,
last
FROM person_codes;
"
dbExecute(conn, insert_to_person)## [1] 0check_query_2 <- "SELECT * FROM person LIMIT 5;"
check <- dbGetQuery(conn, check_query_2)
head(check)## person_id first_name last_name
## 1 aardd001 David Aardsma
## 2 aaroh101 Hank Aaron
## 3 aarot101 Tommie Aaron
## 4 aased001 Don Aase
## 5 abada001 Andy Abadcreate_park_command <- "
CREATE TABLE IF NOT EXISTS park (
park_id TEXT PRIMARY KEY,
name TEXT,
nickname TEXT,
city TEXT,
state TEXT,
notes TEXT
);
"
dbExecute(conn, create_park_command)## [1] 0insert_to_park <- "
INSERT OR IGNORE INTO park
SELECT
park_id,
name,
aka,
city
FROM park_codes;
"
dbExecute(conn, insert_to_park)## [1] 0check_query_3 <- "SELECT * FROM park LIMIT 5;"
check_3 <- dbGetQuery(conn, check_query_3)create_league_command <- "
CREATE TABLE IF NOT EXISTS league (
league_id TEXT PRIMARY KEY,
name TEXT
);
"
dbExecute(conn, create_league_command)## [1] 0insert_to_league <- '
INSERT OR IGNORE INTO league
VALUES
("NL", "National League"),
("AL", "American League"),
("AA", "American Association"),
("FL", "Federal League"),
("PL", "Players League"),
("UA", "Union Association")
;
'
dbExecute(conn, insert_to_league)## [1] 0check_query_4 <- "SELECT * FROM league"
check <- dbGetQuery(conn, check_query_4)
head(check)## league_id name
## 1 NL National League
## 2 AL American League
## 3 AA American Association
## 4 FL Federal League
## 5 PL Players League
## 6 UA Union Associationappearance_drop_precaution <- "DROP TABLE IF EXISTS appearance_type;"
dbExecute(conn, appearance_drop_precaution)## [1] 0appearance_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)
check_query_5 <- "SELECT * FROM appearance_type;"
check <- dbGetQuery(conn, check_query_5)
head(check)## appearance_type_id name category
## 1 O1 Batter 1 offense
## 2 O2 Batter 2 offense
## 3 O3 Batter 3 offense
## 4 O4 Batter 4 offense
## 5 O5 Batter 5 offense
## 6 O6 Batter 6 offensecreate_team_command <- "
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)
);
"
dbExecute(conn, create_team_command)## [1] 0insert_to_team <- "
INSERT OR IGNORE INTO team
SELECT
team_id,
league,
city,
nickname,
franch_id
FROM team_codes;
"
dbExecute(conn, insert_to_team)## [1] 0check_query_6 <- "SELECT * FROM team LIMIT 5;"
check <- dbGetQuery(conn, check_query_6)
head(check)## team_id league_id city nickname franch_id
## 1 ALT UA Altoona Mountain Cities ALT
## 2 ARI NL Arizona Diamondbacks ARI
## 3 BFN NL Buffalo Bisons BFN
## 4 BFP PL Buffalo Bisons BFP
## 5 BL1 Baltimore Canaries BL1create_game_command <- "
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)
);
"
dbExecute(conn, create_game_command)## [1] 0insert_to_game <- '
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;
'
dbExecute(conn, insert_to_game)## [1] 0check_query_7 <- "SELECT * FROM game LIMIT 5;"
check <- dbGetQuery(conn, check_query_7)
head(check)## game_id date number_of_game park_id length_outs day completion
## 1 18710504FW10 18710504 0 FOR01 54 1 <NA>
## 2 18710505WS30 18710505 0 WAS01 54 1 <NA>
## 3 18710506RC10 18710506 0 RCK01 54 1 <NA>
## 4 18710508CH10 18710508 0 CHI01 54 1 <NA>
## 5 18710509TRO0 18710509 0 TRO01 54 1 <NA>
## forefeit protest attendance legnth_minutes additional_info acquisition_info
## 1 <NA> <NA> 200 120 <NA> Y
## 2 <NA> <NA> 5000 145 HTBF Y
## 3 <NA> <NA> 1000 140 <NA> Y
## 4 <NA> <NA> 5000 150 <NA> Y
## 5 <NA> <NA> 3250 145 HTBF Ycreate_team_appearance_command <- "
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)
);
"
dbExecute(conn, create_team_appearance_command)## [1] 0insert_to_team_appearance <- "
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;
"
dbExecute(conn, insert_to_team_appearance)## [1] 0check_query_8 <- "
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;
"
check <- dbGetQuery(conn, check_query_8)
head(check)## team_id game_id home league_id score line_score at_bats hits doubles
## 1 CL1 18710504FW10 0 <NA> 0 000000000 30 4 1
## 2 FW1 18710504FW10 1 <NA> 2 010010000 31 4 1
## 3 MIA 20161002WAS0 0 NL 7 000230020 38 14 1
## 4 WAS 20161002WAS0 1 NL 10 03023002x 30 10 2
## triples homeruns rbi sacrifice_hits sacrifice_flies hit_by_pitch walks
## 1 0 0 0 0 0 0 1
## 2 0 0 2 0 0 0 1
## 3 1 2 7 1 0 0 3
## 4 0 1 10 1 1 1 8
## intentional_walks strikeouts stolen_bases caught_stealing
## 1 NA 6 1 NA
## 2 NA 0 0 NA
## 3 2 10 1 1
## 4 0 3 2 0
## grounded_into_double first_catcher_interference left_on_base pitchers_used
## 1 -1 NA 4 1
## 2 -1 NA 3 1
## 3 1 0 8 7
## 4 1 0 7 6
## individual_earned_runs team_earned_runs wild_pitches balks putouts assists
## 1 1 1 0 0 27 9
## 2 0 0 0 0 27 3
## 3 10 10 1 0 24 11
## 4 7 7 1 0 27 11
## errors passed_balls double_plays triple_plays
## 1 0 3 0 0
## 2 3 1 1 0
## 3 0 0 1 0
## 4 0 0 1 0drop_person_appearance_precaution <- "DROP TABLE IF EXISTS person_appearance"
dbExecute(conn, drop_person_appearance_precaution)## [1] 0create_person_appearance_command <- "
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)
);
"
dbExecute(conn, create_person_appearance_command)## [1] 0insert_to_person_appearance <- '
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, insert_to_person_appearance)## [1] 1646118for (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)
}
}This particular solution incorporates a bit of regular expression and some nested for loops. If you need to review for-loops again, refer to our older mission on control structures. Regular expression is a technique where we look for specific patterns in a character vector and replace it with another pattern. In this case, we need to replace all the instances of “%s” and “%f” with a new letter and number, respectively. In particular, the gsub() function replaces all instances of “%s” and “%f” respectively. With each run of the nested loop, we get a properly formatted query that fulfills our needs. Using loops is one potential solution, but it’s also possible to vectorize this work and recreate what we need.
# Check the current status of the db file
dbListTables(conn)## [1] "appearance_type" "game" "game_log"
## [4] "games_log" "league" "park"
## [7] "park_code" "park_codes" "person"
## [10] "person_appearance" "person_code" "person_codes"
## [13] "team" "team_appearance" "team_code"
## [16] "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" "games_log"
## [4] "league" "park" "park_code"
## [7] "person" "person_appearance" "person_code"
## [10] "team" "team_appearance" "team_code"dbDisconnect(conn)