rm(list=ls()) dir_name <- readline("Introduce the name of the directory please: ") #/Users/gnl/Documents/CTB UPM/UNCOVER/uncover_harmonization setwd(dir_name) source("dependency_installer.R") source("connection_parameters.R") source("necessary_functions_connection.R") #source("required_folder_checker.R") #source("argument_hasher.R") dep_list = c("jsonlite", "stringr","DSI","DSOpal","DSLite", "fields", "metafor", "ggplot2", "gridExtra", "data.table", "dsBaseClient", "openxlsx") install_dependencies(dep_list) codebook_file <- "20220315_Data Harmonisation.xlsb.xlsx" codebook_demo <- read.xlsx(codebook_file , sheet = 2 ) codebook_com_and_rf <- read.xlsx(codebook_file , sheet = 3 ) codebook_home_med <- read.xlsx(codebook_file , sheet = 4 ) codebook_si_sympt <- read.xlsx(codebook_file , sheet = 5 ) codebook_treatments <- read.xlsx(codebook_file , sheet = 6 ) codebook_labo <- read.xlsx(codebook_file , sheet = 7 ) codebook_complications <- read.xlsx(codebook_file , sheet = 8 ) codebook_imaging_data <- read.xlsx(codebook_file , sheet = 9 ) codebook_lifestyle_diet <- read.xlsx(codebook_file , sheet = 10 ) codebook_dates <- read.xlsx(codebook_file , sheet = 11 ) codebook <- rbind(codebook_demo , codebook_com_and_rf) codebook <- rbind(codebook , codebook_home_med) codebook <- rbind(codebook , codebook_si_sympt) codebook <- rbind(codebook , codebook_treatments) codebook <- rbind(codebook , codebook_labo) codebook <- rbind(codebook , codebook_complications) codebook <- rbind(codebook , codebook_imaging_data) codebook_lifestyle_diet <- codebook_lifestyle_diet[, !names(codebook_lifestyle_diet) %in% c("X2", "X4" , "X10")] codebook <- rbind(codebook , codebook_lifestyle_diet) codebook <- rbind(codebook , codebook_dates) codebook_col_names <- as.data.frame(codebook$Harmonised.variable.name) names(codebook_col_names) <- c("col_names") categoric_vars = c("DMRGENDR", "DMRBORN", "DMRRETH1", "DMROCCU", "DMRHREDU", "DSXOS", "DSXHO", "DSXIC", "TRXAV","TRXRIB","TRXLR","TRXRM","TRXIA","TRXIB","TRXCH","TRXAB","TRXCS","TRXHEP","TRXAF","TRXCP","TRXOT","TRXECM","TRXIV","TRXNIV","TRXNO","TRXOX","TRXRR","TRXTR","TRXVA","TRXPE","TRXPV","TRXIT","TRXNMB","TRXAC","TRXINA","TRXIS","TRXIM","TRXVC","TRXVD","TRXZN", "CSXCOT","CSXCTR","SMXASAH","SMXFEA","SMXCOA","SMXSTA","SMXSBA","SMXRNA","SMXMYA","SMXARA","SMXCPA","SMXAPA","SMXINA","SMXNAA","SMXDIA","SMXFAA","SMXHEA","SMXCNA","SMXACA","SMXSLA","SMXTLA","SMXSYA","SMXWHA","SMXLYA","SMXANA","SMXIWA","SMXSRA","SMXBLA","CMXPRG","CMXCVD","CMXCMP","CMXHT","CMXDI","CMXCKD","CMXCLD","CMXCPD","CMXASM","CMXCND","CMXRHE","CMXCCI","CMXCBD","CMXDE","CMXPU","CMXST","CMXLY","CMXAP","RFXSM","RFXFSM","RFXOB","RFXTB","RFXIMD","RFXHIV","RFXAIDS","RFXUI","RFXHC","RFXONC","RFXMN", "HMRACI","HMRARB","HMRAHO","HMRNS","HMROS","HMRCS","HMRIS","HMRAV","HMRAB","HMRCOV","IMDXCT","IMDXCTCR","IMDXCTTE","IMDXCTAB","IMDXXR","IMDXPN", "COXRD","COXAR","COXPM","COXMOD","COXPT","COXEC","COXSH","COXIO","COXPE","COXST","COXDIC","COXRIO","COXKF","COXHF","COXBC") #---------------------------------------------------------------------------- #Test if column names are valid check_column_names <- function(codebook_param, colnames){ str_res <- "The column names:" valid_colnames <- c() for(i in 1:(nrow(colnames))){ colname <- colnames[i,1] number_of_column <- check_valid_name(colname , colnames) if(number_of_column != 1){ str_res<- paste(str_res, colname, sep=" ") }else{ valid_colnames <- c(valid_colnames, colname) } } str_res<- paste(str_res,"are not registered in the harmonized data codebook \n", sep=" ") result <- list("not_colnames" = str_res , "colnames" = valid_colnames) return (result) } #Test if a single variable name is valid check_valid_name <- function(col_name , col_names){ valid <- 0 if(col_name %in% codebook_col_names$col_names){ valid <- length(grep(col_name, col_names)) } return (valid) } remove_space <- function(x){ searchString <- ' ' replacementString <- '' res = sub(searchString,replacementString,x) return(res) } remove_spaces_from_ds <- function(ds){ res<- lapply(ds,remove_space ) return(as.data.frame(res)) } is_number <- function(x){ res <- FALSE if(length(x)!=0){ x <- str_replace(x,",",".") aux <- as.numeric(x) if(!is.na(aux)) res <- TRUE } return(res) } check_values_not_categoric <- function(values, colname , codebook_param){ valid_vals <- values possible_vals <- possible_values(colname, codebook_param) for(i in 1:length(values)){ res<- FALSE value <- values[[i]] if(is_number(value)){ value <- str_replace(value,",",".") value <- as.numeric(value) } if(is.null(value)){ res <- TRUE } else if( value == "NA" | value == "nan" | value == ".") res <- TRUE else{ if(nrow(possible_vals) == 2 & (!grepl("DAT",colname, fixed=TRUE)) & colname !="CSXCTR"){ if(colname=="LBXBEH" | colname=="LBXBEHn" | colname=="LBXBEM"){ lower = possible_vals[1,1][[1]] higher = possible_vals[2,1][[1]] }else{ bounds <- as.data.frame(strsplit(possible_vals[1,1], '-')) lowerAux <- str_replace(bounds [1,1],",",".") higherAux <- str_replace(bounds [2,1],",",".") lower <- as.numeric(remove_space(lowerAux)) higher <- as.numeric(remove_space(higherAux)) } if ((value >= lower & value <= higher)) res <- TRUE } if(nrow(possible_vals) == 3 | colname=="CSXCTR"){ if(value == 0 | value == 1) res <- TRUE } if(nrow(possible_vals) > 3){ lower <- strtoi(remove_space(possible_vals[1,1])) higher_b <- nrow(possible_vals)-1 higher <- strtoi(remove_space(possible_vals[higher_b,1])) if ((value >= lower & value <= higher)) res <- TRUE } } if(res == FALSE) valid_vals[i] <- res } return(valid_vals) } possible_values <- function(colname , codebook_param){ if(colname=="LBXBEH" | colname=="LBXBEHn" | colname=="LBXBEM") res <- t(as.data.frame(list(-20,20))) else{ possible_value <- subset(codebook_param,codebook_param$Harmonised.variable.name==colname)[1,5] res <- strsplit(possible_value,split="/") } return(as.data.frame(res)) } possible_values_categoric <- function(codebook_param){ possible_value <- subset(harmonised_data,harmonised_data$Harmonised.variable.name==x)[1,4] res <- strsplit(x=possible_value,split="/") return(as.data.frame(res)) } check_values_categoric <- function(values, colname){ possible_vals <- possible_values_categoric(colname) res <- TRUE for(i in 1:length(values)){ if(!(values[[i]] %in% as.matrix(remove_spaces_from_ds(possible_vals)))){ res <- FALSE } } return(res) } get_values_from_quantiles <- function(x){ data_summary <- ds.summary(x) low_quantile <- data_summary$HM$`quantiles & mean`[[1]] high_quantile <- data_summary$HM$`quantiles & mean`[[7]] return(list(low_quantile,high_quantile)) } notify_error <- function(invalid_name_list,invalid_value_list,wrong_categoric, wrong_categoric_values, missing_numeric, codebook_param){ res <- "" if(length(invalid_name_list) !=0){ res <- "There are invalid values in the numeric fields:" for(i in 1:length(invalid_name_list)){ res <- paste(res, invalid_name_list[i], sep=" ") } res <- paste(res, "\n", sep="") for(i in 1:length(invalid_name_list)){ res <- paste(res, error_message(invalid_name_list[i], invalid_value_list[[i]]), sep=" ") } } if(length(wrong_categoric)!=0){ res <- paste(res, "\n############################################################################ \n", sep="") res <- paste(res,"\nThe following categoric values are invalid:", sep=" ") for(i in 1:length(wrong_categoric)){ res <- paste(res, wrong_categoric[i], sep=" ") } res <- paste(res, "\n", sep="") for(i in 1:length(wrong_categoric)){ res <- paste(res, error_message_categoric(wrong_categoric[i], wrong_categoric_values[[i]], codebook_param), sep=" ") } } if(length(missing_numeric)!=0){ res <- paste(res, "\n############################################################################ \n", sep="") res<- paste(res, "\nThe following fields are missing a numeric field:") for(i in 1:length(wrong_categoric)){ res <- paste(res, missing_numeric[i], sep=" ") } } res <- paste(res, "\n", sep="") return(res) } error_message_categoric <- function(colname, invalid_values, codebook_param){ res<- "\nValues in the field" res<- paste(res, colname, sep=" ") res<- paste(res, "should be", sep=" ") range <- possible_values_categoric(colname , codebook_param) for(i in 1:nrow(range)){ res <- paste(res, remove_space(range[i,1]), sep=" ") } res<- paste(res, "\nBut values were:", sep=" ") for(j in 1:length(invalid_values)){ res<- paste(res, invalid_values[[j]], sep=" ") } res<- paste(res, "\n\n", sep="") return(res) } error_message <- function(colname, invalid_values){ res<- "\nValues in the field" res<- paste(res, colname, sep=" ") res<- paste(res, "should be", sep=" ") if(grepl("numeric", colname,fixed=TRUE)) new_colname <- strsplit(x=colname,split="_")[[1]][1] else new_colname <- colname range <- subset(harmonised_data, harmonised_data$Harmonised.variable.name == new_colname) range <- range[5] range <- as.data.frame(strsplit(range[1,1], '/')) #Range of values or null if(nrow(range) == 2 & !grepl("DAT",colname, fixed=TRUE)){ bounds <- as.data.frame(strsplit(range[1,1], '-')) lower <- remove_space(bounds [1,1]) higher <- remove_space(bounds [2,1]) res<- paste(res, "numbers between", sep=" ") res<- paste(res, lower, sep=" ") res<- paste(res, "and", sep=" ") res<- paste(res, higher, sep=" ") res<- paste(res, "(both included)", sep=" ") } if(nrow(range) == 3){ res<- paste(res, "0 or 1", sep=" ") } if(nrow(range) > 3){ lower <- strtoi(remove_space(range[1,1])) higher_b <- nrow(range)-1 higher <- strtoi(remove_space(range[higher_b,1])) res<- paste(res, "numbers between", sep=" ") res<- paste(res, lower, sep=" ") res<- paste(res, "and", sep=" ") res<- paste(res, higher, sep=" ") res<- paste(res, "(both included)", sep=" ") } if(grepl("DAT",colname, fixed=TRUE)){ res<- paste(res, "dates with the following format: dd/mm/yyyy", sep=" ") } res<- paste(res, "\nBut values were:", sep=" ") for(j in 1:length(invalid_values)){ res<- paste(res, invalid_values[[j]], sep=" ") } res<- paste(res, "\n", sep="") if(!is_number(invalid_values[[1]])) res<- paste(res, "(It's missing a \"numeric\" field)", sep="") res<- paste(res, "\n", sep="") return(res) } check_valid_values <- function(valid_colnames , codebook_param){ invalid_name_list <- c() cannot_analyse_list <- c() invalid_values_list <- list() wrong_categoric_values <- list() wrong_categoric <- c() missing_numeric <- c() j<- 1 k <- 1 for(i in 1:(nrow(valid_colnames))){ data_available <- FALSE data_table <- "empty" colname <- valid_colnames[i, 1] if(grepl("DMRBORN",colname, fixed=TRUE) | (grepl("DAT",colname, fixed=TRUE)) | (grepl("ISO",colname, fixed=TRUE)) | (grepl("BEF", colname, fixed=TRUE))){ next } column <- "data$" column <- paste(column, colname, sep="") tryCatch( error = function(cnd) { print(paste("Unable to analyse data" , cnd , sep = " ")) res <- FALSE }, { data_table <- as.data.frame(ds.table(column)) data_available <- TRUE } ) if(data_available){ cannot_analyse_list <- c(cannot_analyse_list, colname) }else{ if (data_table[[1]] == "All studies failed for reasons identified below") values <- get_values_from_quantiles(column) else values <- row.names(data_table) numeric_col<- paste(colname,"_numeric", sep="") if( colname %in% categoric_vars ){ has_numeric <- numeric_col %in% valid_colnames$`valid_data_colnames(data_colnames)` if(!has_numeric) missing_numeric <- c(missing_numeric, colname) if (data_table[[1]] == "All studies failed for reasons identified below"){ cannot_analyse_list <- c(cannot_analyse_list,colname) }else if(!check_values_categoric(values,colname)){ print("Wrong categoric value:") print(colname) wrong_categoric <- c(wrong_categoric, colname) wrong_categoric_values[[k]] <- values k <- k+1 } }else{ if(grepl("numeric", colname,fixed=TRUE)) new_colname <- strsplit(x=colname,split="_")[[1]][1] else new_colname <- colname valid <- check_values_not_categoric(values, new_colname , codebook_param) if (FALSE %in% valid){ invalid_name_list <- c(invalid_name_list,colname) invalid_values_list[[j]] <- values j <- j+1 } #print(colname) #print(values) }#else # print("This variable has a numeric version") } } missing_numeric if(length(invalid_name_list)>0 | length(wrong_categoric)>0 | length(missing_numeric)>0){ res <- notify_error(invalid_name_list, invalid_values_list, wrong_categoric, wrong_categoric_values, missing_numeric, codebook_param) }else{ res <- "All values are valid \n" } if(length(cannot_analyse_list)>0){ res <- paste(res, "\n############################################################################ \n", sep="") res <- paste(res, notify_unable_analyse(cannot_analyse_list), sep="\n" ) } return(res) } notify_unable_analyse <- function(x){ res <- "\nCould not obtain data from the fields:" for(i in 1:length(x)){ res <- paste(res, x[i], sep=" ") } return (res) } auxConnections <- connect() connections <- auxConnections[[1]] inp <- auxConnections[[2]] #Conexión a la base de datos ds.dim("data", datasources = connections) colnames <- ds.colnames("data") colnames ds.dataFrameSubset(df.name = "data", V1.name = "data$LBXAPTTA", V2.name = "130", Boolean.operator = "<=", newobj = "inRangeHigh", keep.NAs = TRUE, datasources = connections) lengthHigh <- ds.length(x='inRangeHigh$LBXAPTTA', datasources = connections) ds.dataFrameSubset(df.name = "inRangeHigh", V1.name = "inRangeHigh$LBXAPTTA", V2.name = "11", Boolean.operator = ">=", newobj = "inRange", keep.NAs = TRUE, datasources = connections) lengthBuenos <- ds.length(x='inRange$LBXAPTTA', datasources = connections) summary <- ds.summary("inRange$LBXAPTTA") #---------------------------------------------------------------------------- #Check valid column names datastructure_name <- "data" data_colnames <- ds.colnames(x=datastructure_name, datasources= connections) data_colnames <- as.data.frame(data_colnames) check_valid_columns <- check_column_names(codebook ,data_colnames) valid_columns <- as.data.frame(check_valid_columns$colnames) result <- "" result<-check_valid_values(valid_columns, codebook) print(check_valid_columns) datashield.logout(connections) cat(result) file_name<- paste(hospital_name,"_invalid_values.txt", sep="") dir.create("../invalid_values", showWarnings = FALSE) setwd("../invalid_values") cat(check_valid_columns,file=file_name,sep="\n") cat(result,file=file_name,append=TRUE) datashield.logout(connections)