R/processSheet.R

In mihamn/VEH0122: Takes raw car ownership data and transforms into graphs

## process each sheet
df_global = NULL
# carcolumns needs to be defined by searching for Cars , as columns are inconsistent
#carcolumns <- 4*c(1:4) -2

processSheet <- function(sh) {
df_raw <- readODS(sh)
df_raw <- topAndTail(df_raw)
print(sh)
quarters <- paste0(sh,c("Q1","Q2","Q3","Q4"))
carcolumns <- getCarColumns(df_raw[1,])
df_raw <- df_raw[-1,]
colnames(df_raw)[c(1,carcolumns)] <- c("Postcodes",quarters)
#df_raw <- rbind(df_raw[,1],as.numeric(df_raw[,2:ncol(df_raw)]))
df_raw <-df_raw[,c(1,carcolumns)]
df_raw <- cbind.data.frame(df_raw[,1],apply(df_raw[,2:ncol(df_raw),drop=FALSE], 2, function(x) suppressWarnings(as.numeric(x))),stringsAsFactors=F)
if (is.null(df_global)) {
    df_global <<- df_raw
} else {
    df_global <<- df_global %>% dplyr::full_join(df_raw)
    } # if null df_global
}

## add_new_postcodes <- function(newcodes) {
##     get_blank_row <- function() {
##         blank_row <- df_global[nrow(df_global),]
##         blank_row[,1:ncol(blank_row)] <- rep(x=NA,times=ncol(blank_row))
##         }
##     append_row <- function(new_postcode) {
##         blank_row <- get_blank_row()
##         blank_row[1] <- new_postcode
##         print(paste0("printing ",new_postcode))
##         df_global <<- rbind(df_global,blank_row)
##         }
##     apply(newcodes[,1,drop=F],1,function(x) append_row(x))
## }

## build_contingency_cars <- function(df_raw) {
## colnames(df_raw)[1] <- "Postcodes"
## thissheet_postcodes <- df_raw$Postcodes
## extra_postcodes <- df_raw[!(df_raw$Postcodes %in% df_global$Postcodes),]
## add_new_postcodes(extra_postcodes)
## }

#' Read in ownership stats from a downloaded file.
#' @description Read in sheet from downloaded file.
readODS <- function(sh) {
return(readODS::read_ods(path=file_downloadpath,sheet=sh))
}

topAndTail <- function(sh_raw) {
    includecolumns <- which(!(is.na(sh_raw[,2])))
    sh_raw <- sh_raw[includecolumns,]
    topcolumn <- grep("Postcode District.*",sh_raw[,1])
#    topcolumn <-which(sh_raw[grep(sh_raw[1,],"Postcode District.*"),1]) + 1
    sh_raw <- sh_raw[topcolumn:nrow(sh_raw),]
    return(sh_raw)
} # function topAndTail