R/nis_sql_hosp.r

In jjinho/rnis: R Functions for the Nationwide Inpatient Sample

#' Query NIS SQLite Hospital File by Year
#'
#' Query NIS Hospital File by year and returns a Dataframe
#' @param year 				NIS Year
#' @param nis_path		Path to the NIS DB files. Defaults to "~/NIS"
#' @keywords nis
#' @export
#' @examples
#' nis_sql_hosp(year)
nis_sql_hosp <- function(year, nis_path="~/NIS") {  
  # Get path to DB by year
	db_path <- gsub("path", nis_path, "path/y_/nis_y_.db")
	db_path <- gsub("y_", toString(year), db_path)

  # Connection
  con <- RSQLite::dbConnect(RSQLite::SQLite(), db_path)
  
	nis_hospital_query <- "SELECT * FROM hospital_y_;"
	
	# Put the correct year
  nis_hospital_query <- gsub("y_", toString(year), nis_hospital_query)
  
  # Perform the query
  q <- RSQLite::dbSendQuery(con, nis_hospital_query)
  
  # Get the data
  hospital_df <- RSQLite::dbFetch(q, n=-1)
  
	# Clear the query to prevent memory leaks
  RSQLite::dbClearResult(q)

	format_2005 <- c("Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char")
	format_2006 <- c("Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char")
	format_2007 <- c("Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char")
	format_2008 <- c("Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char")
	format_2009 <- c("Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char")
	format_2010 <- c("Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char")
	format_2011 <- c("Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char")
	format_2012 <- c("Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char","Char")

	format_nis_hospital_	<- function(df, format) {
		counter = 1
		for(f in format) {
			# Remove unknowns
			df[,counter][grep("-[0-9]+", df[,counter])] <- NA
			
			if(f == "Num") {
				# Num
				df[,counter] <- as.numeric(df[,counter])
			} else {
				# Char
				df[,counter] <- as.factor(df[,counter])
			}
			counter <- counter + 1
		}
		return(df)
	}
	
	if(year == 2005) hospital_df <- format_nis_hospital_(hospital_df, format_2005)
	if(year == 2006) hospital_df <- format_nis_hospital_(hospital_df, format_2006)
	if(year == 2007) hospital_df <- format_nis_hospital_(hospital_df, format_2007)
	if(year == 2008) hospital_df <- format_nis_hospital_(hospital_df, format_2008)
	if(year == 2009) hospital_df <- format_nis_hospital_(hospital_df, format_2009)
	if(year == 2010) hospital_df <- format_nis_hospital_(hospital_df, format_2010)
	if(year == 2011) hospital_df <- format_nis_hospital_(hospital_df, format_2011)
	if(year == 2012) hospital_df <- format_nis_hospital_(hospital_df, format_2012)
	
	return(hospital_df)
}