You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
172 lines
7.4 KiB
172 lines
7.4 KiB
library(RPostgreSQL) |
|
library(rlang) |
|
library(plyr) |
|
library(dplyr) |
|
library(DBI) |
|
library(RCurl) |
|
|
|
con <- dbConnect(PostgreSQL(), dbname = 'xxDBxx', host = 'xxHOSTxx', port = 5432, user = 'xxUSERxx', password = 'xxPASSxx') |
|
|
|
glass <- getURL(paste0("https://xxDHIS2HOSTxx/api/29/analytics/events/aggregate/wsHfxYEgte5.csv?dimension=ou:isF1Ffne2Ik&dimension=pe:THIS_YEAR;LAST_5_YEARS&dimension=opkhFoeEz9m:IN:eco;sal&dimension=DYsNDR4nCWB&dimension=TRm4iaYikdK-oPyVHCMErH0&dimension=NoTuZ3jydbw:IN:ur;bl&dimension=JkrGmcLg6Zm.gaCDwmKm768&dimension=JkrGmcLg6Zm.C25kY3E21zv&stage=JkrGmcLg6Zm&displayProperty=NAME&outputType=EVENT&outputIdScheme=CODE"), userpwd="api:Pa55word!", httpauth = 1L) |
|
glass <- read.table(text = glass, sep =",", header = TRUE, stringsAsFactors = FALSE) |
|
|
|
glass |
|
|
|
# Renaming the header |
|
names(glass)[1] <- "pathogen" |
|
names(glass)[2] <- "sex" |
|
names(glass)[3] <- "age" |
|
names(glass)[4] <- "specimen" |
|
names(glass)[5] <- "antibiotic" |
|
names(glass)[6] <- "status" |
|
names(glass)[7] <- "country" |
|
names(glass)[8] <- "year" |
|
names(glass)[9] <- "value" |
|
|
|
# Replace NA with blank |
|
glass <- glass %>% replace(., is.na(.), "") |
|
|
|
# Replacing null status by replacing with X |
|
glass <- glass %>% mutate(status = ifelse(status == '', 'X', status)) |
|
|
|
# Replacing null age with UNK |
|
glass <- glass %>% mutate(age = ifelse(age == '', 'UNK', age)) |
|
|
|
# Changing gender with capital latter |
|
glass <- glass %>% mutate(sex = ifelse(sex == 'f', 'F', sex)) |
|
glass <- glass %>% mutate(sex = ifelse(sex == 'm', 'M', sex)) |
|
glass <- glass %>% mutate(sex = ifelse(sex == '', 'UNK', sex)) |
|
|
|
# glass |
|
|
|
# Renaming age from uid to code |
|
glass <- glass %>% mutate(age = ifelse(age == 'y6vwuHXxddO', '<1', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'jf5Zr4sleyX', '01<04', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'VUF8eXjrbjC', '05<14', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'LF7HNYtqIr5', '15<24', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'T51pNS4GE9a', '25<34', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'eQslOKgf0Wp', '35<44', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'G2ur1JRd6uL', '45<54', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'Dh89WgJK9vt', '55<64', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'dGbMkILwzzN', '65<74', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'Z6eawM1m33G', '75<84', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == 'VKJhxU3XeVU', '85<', age)) |
|
glass <- glass %>% mutate(age = ifelse(age == '', 'UNK', age)) |
|
|
|
glass |
|
|
|
# # Renaming specimen |
|
glass <- glass %>% mutate(specimen = ifelse(specimen == 'bl', 'BLOOD', specimen)) |
|
glass <- glass %>% mutate(specimen = ifelse(specimen == 'ur', 'URINE', specimen)) |
|
# glass <- glass %>% mutate(specimen = ifelse(specimen == 'st', 'STOOL', specimen)) |
|
# glass <- glass %>% mutate(specimen = ifelse(specimen == 'gn', 'GENITAL', specimen)) |
|
|
|
glass$code = paste(glass$pathogen, glass$specimen, glass$sex, glass$age, glass$antibiotic, glass$year, sep="=") |
|
|
|
glass |
|
|
|
# year <- tail(glass$year, 1) |
|
glass_unique <- paste0(unlist(unique(glass$year)), collapse = ',') |
|
# glass_unique <- unique(glass_unique) |
|
# glass_unique <- paste0(unlist(glass_unique), collapse = ',') |
|
|
|
glass_unique |
|
|
|
# //////////////---------------- Sample file starting -------------///////////////////// |
|
|
|
Sample_file <- getURL(paste0("https://xxDHIS2HOSTxx/api/29/analytics/events/aggregate/wsHfxYEgte5.csv?dimension=ou:isF1Ffne2Ik&dimension=pe:THIS_YEAR;LAST_5_YEARS&dimension=JkrGmcLg6Zm.DYsNDR4nCWB&dimension=JkrGmcLg6Zm.TRm4iaYikdK-oPyVHCMErH0&dimension=JkrGmcLg6Zm.NoTuZ3jydbw:IN:ur;bl&stage=JkrGmcLg6Zm&displayProperty=NAME&outputType=EVENT&outputIdScheme=CODE"), userpwd="api:Pa55word!", httpauth = 1L) |
|
Sample_file <- read.table(text = Sample_file, sep =",", header = TRUE, stringsAsFactors = FALSE) |
|
|
|
Sample_file |
|
|
|
|
|
# Renaming the header |
|
names(Sample_file)[1] <- "sex" |
|
names(Sample_file)[2] <- "age" |
|
names(Sample_file)[3] <- "specimen" |
|
names(Sample_file)[4] <- "country" |
|
names(Sample_file)[5] <- "year" |
|
names(Sample_file)[6] <- "value" |
|
|
|
# Replacing null age with UNK |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == '', 'UNK', age)) |
|
|
|
# Changing gender with capital latter |
|
Sample_file <- Sample_file %>% mutate(sex = ifelse(sex == 'f', 'F', sex)) |
|
Sample_file <- Sample_file %>% mutate(sex = ifelse(sex == 'm', 'M', sex)) |
|
Sample_file <- Sample_file %>% mutate(sex = ifelse(sex == '', 'UNK', sex)) |
|
|
|
# Renaming age from uid to code |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'y6vwuHXxddO', '<1', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'jf5Zr4sleyX', '01<04', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'VUF8eXjrbjC', '05<14', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'LF7HNYtqIr5', '15<24', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'T51pNS4GE9a', '25<34', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'eQslOKgf0Wp', '35<44', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'G2ur1JRd6uL', '45<54', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'Dh89WgJK9vt', '55<64', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'dGbMkILwzzN', '65<74', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'Z6eawM1m33G', '75<84', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == 'VKJhxU3XeVU', '85<', age)) |
|
Sample_file <- Sample_file %>% mutate(age = ifelse(age == '', 'UNK', age)) |
|
|
|
# # Renaming specimen |
|
Sample_file <- Sample_file %>% mutate(specimen = ifelse(specimen == 'bl', 'BLOOD', specimen)) |
|
Sample_file <- Sample_file %>% mutate(specimen = ifelse(specimen == 'ur', 'URINE', specimen)) |
|
Sample_file <- Sample_file %>% mutate(specimen = ifelse(specimen == 'st', 'STOOL', specimen)) |
|
Sample_file <- Sample_file %>% mutate(specimen = ifelse(specimen == 'gn', 'GENITAL', specimen)) |
|
|
|
Sample_file$code = paste(Sample_file$specimen, Sample_file$sex, Sample_file$age, Sample_file$year, sep="=") |
|
|
|
Sample_file |
|
|
|
# ========================PROCCESSING==================== |
|
|
|
# RIS starting execution ---------------------------------- |
|
delete_RIS_data <- function(glass_unique){ |
|
|
|
query <- paste("delete from ris where year in ", paste("(", glass_unique, ")")) |
|
# query <- paste("delete from irsxes where year in 2015, 2016") |
|
dbSendQuery(con, query) |
|
|
|
print('========= RIS REPORT =========') |
|
msg <- paste("Successfully deleted RIS data for: ", glass_unique) |
|
print(msg) |
|
} |
|
|
|
# Save to database |
|
insert_RIS_data <- function(){ |
|
dbWriteTable(con, name = 'ris', value = as.data.frame(glass), row.names = FALSE, append = TRUE) |
|
msg <- paste("Successfully uploaded RIS data for: ", glass_unique) |
|
print(msg) |
|
} |
|
|
|
delete_RIS_data(glass_unique) |
|
insert_RIS_data() |
|
# ========================== END for RIS ================================ |
|
|
|
# Sample file starting execution ---------------------------------- |
|
delete_SF_data <- function(glass_unique){ |
|
query <- paste("delete from sample_files where year in ", paste("(", glass_unique, ")")) |
|
dbSendQuery(con, query) |
|
|
|
print('========= SAMPLE FILE REPORT =========') |
|
msg <- paste("Successfully deleted Sample File data for: ", glass_unique) |
|
print(msg) |
|
} |
|
|
|
|
|
# Save to database |
|
insert_SF_data <- function(){ |
|
myquery <- dbWriteTable(con, name = 'sample_files', value = as.data.frame(Sample_file), row.names = FALSE, append = TRUE) |
|
msg <- paste("Successfully uploaded Sample File data for: ", glass_unique) |
|
print(msg) |
|
} |
|
|
|
delete_SF_data(glass_unique) |
|
insert_SF_data() |
|
# ========================== END for Sample file ================================ |
|
|
|
|
|
dbDisconnect(con) |
|
|
|
|