#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.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
guns <- read_csv("b_guns.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
drugs <- read_csv("c_drugs.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
mala <- read_csv("d_mala.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
educ <- read_csv("e_educ.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
land <- read_csv("f_land.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
labor <- read_csv("g_labor.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
health <- read_csv("h_health.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
smoking <- read_csv("i_smoking.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
util <- read_csv("j_util.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
lic <- read_csv("k_lic.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
forf <- read_csv("l_forf.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
reg <- read_csv("n_reg.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
courts <- read_csv("o_courts.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
abor <- read_csv("p_abor.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
enfor <- read_csv("r_enfor.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
marr <- read_csv("s_marr.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
elec <- read_csv("t_elec.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
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
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_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
guns_meta <- read_csv("b_guns_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
drugs_meta <- read_csv("c_drugs_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
mala_meta <- read_csv("d_mala_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
educ_meta <- read_csv("e_educ_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
land_meta <- read_csv("f_land_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
labor_meta <- read_csv("g_labor_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
health_meta <- read_csv("h_health_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
smoking_meta <- read_csv("i_smoking_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
util_meta <- read_csv("j_util_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
lic_meta <- read_csv("k_lic_metadata.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
forf_meta <- read_csv("l_forf_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
reg_meta <- read_csv("n_reg_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
courts_meta <- read_csv("o_courts_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
abor_meta <- read_csv("p_abor_metadata.csv", skip = 0, na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
enfor_meta <- read_csv("r_enfor_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
marr_meta <- read_csv("s_marr_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"), guess_max = 10000)
elec_meta <- read_csv("t_elec_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_licenses <- read_csv("licensure.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"))
k_lic_metadata_licenses <- read_csv("licensure_metadata.csv", na = c("", ".", "..", "#VALUE!", "#DIV/0!"))
