Imanol-Mikel Barba Sabariego / DDS · Files

#------------------------------------------------------------------------------------------------------
#-----------------------------Parser Excel - ISO survey------------------------------------------------
#------------------------------------------------------------------------------------------------------


#' Get data frame from an excel file
#'
#' Check if the file exists and then parse it into a data.frame
#' @param file path to excel file
#' @param sheet index of sheet to parse
#'
#' @return data.frame

#'
#' @examples
#' Cert_PerCountry <- ParseExcelFileRaw("./data/ISO/iso_27001_iso_survey2015_preprocessed.xlsx", 1)
#' Sites_PerCountry <- ParseExcelFileRaw("./data/ISO/iso_27001_iso_survey2015_preprocessed.xlsx", 2)
#' Cert_PerSector <- ParseExcelFileRaw("./data/ISO/iso_27001_iso_survey2015_preprocessed.xlsx", 3)

ParseExcelFileRaw <- function(file, sheet){

  if (!file.exists(file)) {
    stop(paste("Error, file [", file, "] not found"))
  }

  dataset <- read.xlsx2(file, sheet,colClasses = c("character","numeric","numeric","numeric","numeric","numeric","numeric","numeric","numeric","numeric","numeric"), header = TRUE, as.data.frame = TRUE)

  dataset
}

#' Process raw data from ISO survey
#'

#' Proccess the raw data from ISO survey to replace NAs, normalizate country names and filter years

#' @param dataset.raw raw data from ISO Survey excel file

#' @param years List of years to return, c("X2006", "X2010", ...)

#'
#' @return data.frame

#'
#' @examples
#'
#' Cert_PerCountry <- ProccesISOSurveyRaw(Cert_PerCountry, c("X2010", "X2011", "X2012", "X2013", "X2014", "X2015"))
#' Sites_PerCountry <- ProccesISOSurveyRaw(Sites_PerCountry, c("X2010", "X2011", "X2012", "X2013", "X2014", "X2015"))
#' Cert_PerSector <- ProccesISOSurveyRaw(Cert_PerSector, c("X2010", "X2011", "X2012", "X2013", "X2014", "X2015"))
ProccesISOSurveyByCountryRaw <- function(dataset.raw, years){

  #NAs to 0s
  dataset <- dataset.raw

  dataset[is.na(dataset)] <- 0

  #Translate country names to 2 letter code

  CountryNames <- GetCountryAbrev()

  dataset <- merge(x = dataset, y = CountryNames, by = "Country", all.x = TRUE)

  vars <- names(dataset)
  years_checked <- intersect(vars, years)
  dataset <- dataset[,c(c("Country", "country_short"), years_checked)]

  dataset

}