ISOSurvey_Parser.R
2.24 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
#------------------------------------------------------------------------------------------------------
#-----------------------------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 <- xlsx::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
}