Hello,
I've got some files exported from a work data system that I'm trying to streamline importing into R
What I'd like to do is to be able to run
> importData("district/hospital_0618.xls")
and have the output be:
> hospital
# A tibble: 4,332 x 5
date facility agent ddd pt.days
<date> <chr> <chr> <dbl> <dbl>
<data here>
Code as follows:
importData <- function(arg1) { ### arg1 = folder/name_date.xls
arg2 <- str_match(arg1, "/(.*?)_")[,2] ### extract "name" out of arg1
assign(arg2, 0, envir =.GlobalEnv) ### help with this bit - use arg2 as the name for the extracted data
qcolname <- unlist(c(read_excel(arg1, col_names = FALSE, range = "A3:H3"))) ## files in standard format, this extracts the colnames
arg2 <<- read_excel(arg1, col_names = qcolname, skip = 5) ## read the files without the header
arg2 <<- drop_na(arg2) ## dump the lines with blank cells
arg2 <<- unite(arg2, Month, Year, col = "date", sep = "-") ## unite Month and Year columns to "date" as %b-%Y
arg2$date <<- parse_date(arg2$date, "%b-%Y") ## make it into a date so it works
arg2 <<- arg2 %>% ## rename the columns so they're easier to work with
rename("facility" = `Facility`) %>%
rename("agent" = `Abstract Product`) %>%
rename("atc.class" = `Sub-class`) %>%
rename("ddd" = `DDD`) %>%
rename("pt.days" = `Patient Days`) %>%
rename("usage" = `DDDs / 1000 Patient Days`) %>%
select(date, facility, agent, ddd, pt.days) ## yes, I know I've dropped them
}
Not quite sure how to pass the output of the trimmed string to a variable name that carries on outside the function.
Thanks in advance.