Sample R Code for Importing and Merging Data and Metadata CSV Files

#script to import state policy data and metadata into a single, large, relational, tidy database
#set working directory
#install "tidyverse" package suite

library(tidyverse)

fiscal <- read_csv("a_fiscal_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
guns <- read_csv("b_guns_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
drugs <- read_csv("c_drugs_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
mala <- read_csv("d_mala_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
educ <- read_csv("e_educ_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
land <- read_csv("f_land_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
labor <- read_csv("g_labor_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
health <- read_csv("h_health_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
smoking <- read_csv("i_smoking_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
util <- read_csv("j_util_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
lic <- read_csv("k_lic_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
forf <- read_csv("l_forf_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
reg <- read_csv("n_reg_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
courts <- read_csv("o_courts_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
abor <- read_csv("p_abor_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
enfor <- read_csv("r_enfor_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
marr <- read_csv("s_marr_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
elec <- read_csv("t_elec_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
summary <- read_csv("summary_17.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"))
drugs$State[drugs$State == "NewHampshire"] = "New Hampshire"
drugs$State[drugs$State == "NewJersey"] = "New Jersey"
drugs$State[drugs$State == "NewMexico"] = "New Mexico"
drugs$State[drugs$State == "NewYork"] = "New York"
drugs$State[drugs$State == "NorthCarolina"] = "North Carolina"
drugs$State[drugs$State == "NorthDakota"] = "North Dakota"
drugs$State[drugs$State == "RhodeIsland"] = "Rhode Island"
drugs$State[drugs$State == "SouthCarolina"] = "South Carolina"
drugs$State[drugs$State == "SouthDakota"] = "South Dakota"
drugs$State[drugs$State == "WestVirginia"] = "West Virginia"

#merging all datasets, excluding summary
fulldata <- full_join(fiscal, guns, by = c("State" = "state", "Year" = "year")) %>% full_join(., drugs, by = c("State", "Year")) %>% full_join(., mala, by = c("State", "Year")) %>% full_join(., educ, by = c("State", "Year")) %>% full_join(., land, by = c("State", "Year")) %>% full_join(., labor, by = c("State", "Year")) %>% full_join(., health, by = c("State", "Year")) %>% full_join(., smoking, by = c("State", "Year")) %>% full_join(., util, by = c("State", "Year")) %>% full_join(., lic, by = c("State", "Year")) %>% full_join(., forf, by = c("State", "Year")) %>% full_join(., reg, by = c("State", "Year")) %>% full_join(., courts, by = c("State", "Year")) %>% full_join(., abor, by = c("State", "Year")) %>% full_join(., enfor, by = c("State", "Year")) %>% full_join(., marr, by = c("State", "Year")) %>% full_join(., elec, by = c("State", "Year")) %>% arrange(., Year, State)

fiscal_meta <- read_csv("a_fiscal_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
guns_meta <- read_csv("b_guns_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
drugs_meta <- read_csv("c_drugs_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
mala_meta <- read_csv("d_mala_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
educ_meta <- read_csv("e_educ_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
land_meta <- read_csv("f_land_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
labor_meta <- read_csv("g_labor_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
health_meta <- read_csv("h_health_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
smoking_meta <- read_csv("i_smoking_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
util_meta <- read_csv("j_util_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
lic_meta <- read_csv("k_lic_17_metadata.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
forf_meta <- read_csv("l_forf_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
reg_meta <- read_csv("n_reg_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
courts_meta <- read_csv("o_courts_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
abor_meta <- read_csv("p_abor_17_metadata.csv", skip = 1, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
enfor_meta <- read_csv("r_enfor_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
marr_meta <- read_csv("s_marr_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
elec_meta <- read_csv("t_elec_17_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)

fullmeta <- bind_rows(fiscal_meta, guns_meta, drugs_meta, mala_meta, educ_meta, land_meta, labor_meta, health_meta, smoking_meta, util_meta, lic_meta, forf_meta, reg_meta, courts_meta, abor_meta, enfor_meta, marr_meta, elec_meta)

#if desired
k_lic_metadata_licenses <- read_csv("k_lic_17_metadata_licenses.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"))
k_lic_metadata_kloccs2 <- read_csv("k_lic_17_metadata_kloccs2.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"))