rm(list = ls())
###############################input data
dir_path <- "C:\\Users\\liyix\\OneDrive\\Desktop\\code\\"
dir_path_name <- list.files(pattern = ".csv*",dir_path,full.names = T, recursive = F)
#dir_path_name
#install.packages("ctrdata")
data_1 <- read.csv(grep("data.csv",dir_path_name,value = T),header = T,stringsAsFactors = F)
#View(data_1)
#grep("remdesivir",data_1$Name)
#virus_list <- unique(data_1$search_label)
#dim(data_1) #[1] 476 28
data_1 <- unique(data_1$Name)
#length(data_1) #[1] 406
grep("remdesivir",data_1)
## [1] 381
###################################
library(ctrdata)
##
## Information on this package and how to use it:
## https://cran.r-project.org/package=ctrdata
##
## Please respect the requirements and the copyrights of the
## clinical trial registers when using their information. Call
## ctrOpenSearchPagesInBrowser(copyright = TRUE) and visit
## https://www.clinicaltrialsregister.eu/disclaimer.html
## https://clinicaltrials.gov/ct2/about-site/terms-conditions#Use
## https://www.isrctn.com/page/faqs#usingISRCTN
##
## Version: 1.10.2
help("ctrdata-package")
## 打开httpd帮助服务器…
## 好了
help("ctrdata-registers")
#ctrOpenSearchPagesInBrowser() #Open registers??? advanced search pages in browser\
#length(data_1)
for (i in 381:381) {
print(i/length(data_1))
tryCatch({
#i = 381
virus <- data_1[i]
dir.create(paste0(dir_path, i,"_", virus,"\\"))
dir_path_new <- paste0(dir_path, i,"_", virus,"\\")
#getwd()
setwd(dir_path_new)
###################################################____1_EUCRT
#####____0_prepare
#Retrieve protocol-related information, transform and save to database:
db_1 <- nodbi::src_sqlite(
dbname = paste0("eucrt","_", virus,".sqlite_file"),
collection = "virus")
db_1
#"https://www.clinicaltrialsregister.eu/ctr-search/search?query=cancer&age=under-18&phase=phase-one&status=completed"
#url_1 <- "https://www.clinicaltrialsregister.eu/ctr-search/search?query= COVID-19"
#q <- ctrGetQueryUrl(url_1)
#q
## Retrieve trials from public register:
#ctrLoadQueryIntoDb(
# queryterm = q,
# con = db_1)
################################
ctrLoadQueryIntoDb(
queryterm = virus,
register = "EUCTR",
con = db_1)
#Analyse
#Find names of fields in the database collection
#fields_1 <- data.frame(dbFindFields(namepart = "_", con = db_1))
#print(dim(fields_1)) #[1] 411 1
#write.csv(fields_1, paste0(dir_path,Sys.Date(),"-","EUCTR_fields_all.csv"),row.names = FALSE,na = "")
# Get all records that have values in the fields of interest:
#list_1 <- c(4,58,59,95,308,310,468,315,323,324,325)
#list_1
col_name_1 <- c("a3_full_title_of_the_trial","a52_us_nct_clinicaltrialsgov_registry_number",
"a53_who_universal_trial_reference_number_utrn","dimp.d31_product_name",
"e63_therapy","e65_efficacy","x5_trial_status",
"e71_human_pharmacology_phase_i","e72_therapeutic_exploratory_phase_ii",
"e73_therapeutic_confirmatory_phase_iii", "e74_therapeutic_use_phase_iv")
result_EUCTR <- dbGetFieldsIntoDf(
fields = col_name_1,
stopifnodata = FALSE,
con = db_1)
#class(result_EUCTR)
#dim(result_EUCTR)
#str(result_EUCTR)
#View(result_EUCTR)
result_EUCTR$dimp.d31_product_name <- as.character(result_EUCTR$dimp.d31_product_name)
result_EUCTR[] <- sapply(result_EUCTR[], as.character)
print(head(result_EUCTR[],3))
#result_EUCTR$dimp.d311131_other_medicinal_product_type <- as.character(result_EUCTR$dimp.d311131_other_medicinal_product_type)
write.table(result_EUCTR, paste0(dir_path_new,Sys.Date(),"-","EUCTR_result_",virus,".txt"),row.names = FALSE,na = "",sep = "\t")
# one record, for example for several EU Member States:
#uniqueids <- dbFindIdsUniqueTrials(con = db_1)
#uniqueids
#nodbi::docdb_get(db_1, "virus")
#nodbi::docdb_delete(db_1, "virus")
#rm(db_1)
#unlink("eucrt.sqlite_file",recursive=TRUE)
#file.remove("eucrt.sqlite_file")
#?file.remove
##################################################_____2_CTGOV
#Search used in this example:
#https://clinicaltrials.gov/ct2/results?cond=neuroblastoma&rslt=With&recrs=e&age=0&intr=Drug
# Retrieve trials from another register:
db_2 <- nodbi::src_sqlite(
dbname = paste0("CTGOV","_", virus,".sqlite_file"),
collection = "virus")
db_2
virus_1 <- paste0("Study Title = virus&intr=", virus)
ctrLoadQueryIntoDb(
queryterm = virus_1,
register = "CTGOV",
con = db_2)
#Analyse
#Find names of fields in the database collection
#fields_2 <- data.frame(dbFindFields(namepart = ".", con = db_2))
#dim(fields_2)
#View(fields_2)
#write.csv(fields_2, paste0(dir_path,Sys.Date(),"-","CTGOV_fields_all.csv"),row.names = FALSE,na = "")
# Get all records that have values in the fields of interest:
#list_2 <- c(10,254,255,295,299)
#list_2
col_name_2 <- c("biospec_retention", "intervention.intervention_name",
"intervention.intervention_type", "brief_title", "id_info.nct_id")
#?dbGetFieldsIntoDf
result_CTGOV <- dbGetFieldsIntoDf(
fields = col_name_2,
stopifnodata = FALSE,
con = db_2)
#class(result_CTGOV)
#dim(result_CTGOV)
#str(result_CTGOV)
#View(result_CTGOV)
result_CTGOV[] <- sapply(result_CTGOV[], as.character)
print(head(result_CTGOV,3))
#dim(result_CTGOV)
write.table(result_CTGOV, paste0(dir_path_new,Sys.Date(),"-","CTGOV_result_",virus,".txt"),row.names = FALSE,na = "",sep = "\t")
# one record, for example for several EU Member States:
#uniqueids_2 <- dbFindIdsUniqueTrials(con = db_2)
#uniqueids_2
#file.remove("ctgov.sqlite_file")
##############################################______3_(ISRCTN)
db_3 <- nodbi::src_sqlite(
dbname = paste0("ISRCTN","_", virus,".sqlite_file"),
collection = "virus")
db_3
#Add records from another register (ISRCTN) into the same database
#Search used in this example: https://www.isrctn.com/search?q=neuroblastoma
# Retrieve trials from another register:
#ctrLoadQueryIntoDb(queryterm = "https://www.isrctn.com/search?q=covid-19",
# con = db_3)
ctrLoadQueryIntoDb(
queryterm = virus,
register = "ISRCTN",
con = db_3)
#Analyse
#Find names of fields in the database collection
#fields_3 <- data.frame(dbFindFields(namepart = ".", con = db_3))
#dim(fields_3)
#write.csv(fields_3, paste0(dir_path,Sys.Date(),"-","ISRCTN_fields_all.csv"),row.names = FALSE,na = "")
# Get all records that have values in the fields of interest:
#list_3 <- c(18:23,86)
#list_3
col_name_3 <- c("interventions", "interventions.intervention",
"interventions.intervention.description", "interventions.intervention.drugNames" ,
"interventions.intervention.interventionType", "interventions.intervention.phase",
"trialDesign.trialType")
result_ISRCTN <- dbGetFieldsIntoDf(
fields = col_name_3 ,
stopifnodata = FALSE,
con = db_3)
#class(result_ISRCTN);dim(result_ISRCTN); str(result_ISRCTN)
#View(result_ISRCTN)
#result_ISRCTN$'_id'[1]
result_ISRCTN[] <- sapply(result_ISRCTN[], as.character)
#str(result_ISRCTN)
#result_ISRCTN$interventions <- NULL
print(head(result_ISRCTN,3))
write.table(result_ISRCTN, paste0(dir_path_new,Sys.Date(),"-","ISRCTN_result_",virus,".txt"),row.names = FALSE,na = "",sep = "\t")
}, error = function(e) {
cat("ERROR :",conditionMessage(e), "\n")
#cat("ERROR :", conditionMessage(e),"---",i,"---",gsub("\\:","-",Sys.time()),file = "error.txt", append = TRUE, "\n")
#cat(message('** ERR at ', Sys.time(), " **"),file = "test_1.txt", append = TRUE)
#print(e)
})
Sys.sleep(20)
}
## [1] 0.9384236
## * Found search query from EUCTR: query=remdesivir
## Checking helper binaries: done
## (1/3) Checking trials in EUCTR:
## Retrieved overview, multiple records of 22 trial(s) from 2 page(s) to be downloaded
## Downloading trials (2 pages in parallel)...
## Note: register server cannot compress data, transfer takes longer, about 4s per trial
## Pages: 0 done...
Pages: 1 done, 1 ongoing
Pages: 2 done, 0 ongoing
## (2/3) Converting to JSON..., 73 records converted
## (3/3) Importing JSON records into database...
## JSON file #: 1 / 2
JSON file #: 2 / 2
= Imported or updated 73 records on 22 trial(s)
## No history found in expected format.
## Updated history ("meta-info" in "virus")
## a3_full_title_of_the_trial...
## a52_us_nct_clinicaltrialsgov_registry_number...
## a53_who_universal_trial_reference_number_utrn...
## dimp.d31_product_name... .
## e63_therapy...
## e65_efficacy...
## x5_trial_status...
## e71_human_pharmacology_phase_i...
## e72_therapeutic_exploratory_phase_ii...
## e73_therapeutic_confirmatory_phase_iii...
## e74_therapeutic_use_phase_iv...
## _id
## 1 2020-000841-15-DE
## 2 2020-000841-15-ES
## 3 2020-000841-15-FR
## a3_full_title_of_the_trial
## 1 A Phase 3 Randomized Study to Evaluate the Safety and Antiviral Activity of Remdesivir GS-5734 in Participants with Severe COVID-19
## 2 A Phase 3 Randomized Study to Evaluate the Safety and Antiviral Activity of Remdesivir GS-5734 in Participants with Severe COVID-19
## 3 A Phase 3 Randomized Study to Evaluate the Safety and Antiviral Activity of Remdesivir GS-5734 in Participants with Severe COVID-19
## a52_us_nct_clinicaltrialsgov_registry_number
## 1 <NA>
## 2 <NA>
## 3 <NA>
## a53_who_universal_trial_reference_number_utrn dimp.d31_product_name
## 1 <NA> Remdesivir
## 2 <NA> Remdesivir
## 3 <NA> Remdesivir
## e63_therapy e65_efficacy x5_trial_status e71_human_pharmacology_phase_i
## 1 FALSE TRUE Completed FALSE
## 2 FALSE TRUE Completed FALSE
## 3 FALSE TRUE Completed FALSE
## e72_therapeutic_exploratory_phase_ii e73_therapeutic_confirmatory_phase_iii
## 1 FALSE TRUE
## 2 FALSE TRUE
## 3 FALSE TRUE
## e74_therapeutic_use_phase_iv
## 1 FALSE
## 2 FALSE
## 3 FALSE
## * Found search query from CTGOV: Study Title = virus&intr=remdesivir
## Checking helper binaries: done
## (1/3) Checking trials in CTGOV:
## Retrieved overview, records of 107 trial(s) are to be downloaded (estimate: 0.86 MB)
## Downloading trials
##
Downloading: 5.5 kB
Downloading: 5.5 kB
Downloading: 5.5 kB
Downloading: 5.5 kB
Downloading: 5.5 kB
Downloading: 5.5 kB
Downloading: 16 kB
Downloading: 16 kB
Downloading: 24 kB
Downloading: 24 kB
Downloading: 24 kB
Downloading: 24 kB
Downloading: 32 kB
Downloading: 32 kB
Downloading: 40 kB
Downloading: 40 kB
Downloading: 41 kB
Downloading: 41 kB
Downloading: 49 kB
Downloading: 49 kB
Downloading: 49 kB
Downloading: 49 kB
Downloading: 57 kB
Downloading: 57 kB
Downloading: 57 kB
Downloading: 57 kB
Downloading: 57 kB
Downloading: 57 kB
Downloading: 66 kB
Downloading: 66 kB
Downloading: 68 kB
Downloading: 68 kB
Downloading: 74 kB
Downloading: 74 kB
Downloading: 74 kB
Downloading: 74 kB
Downloading: 82 kB
Downloading: 82 kB
Downloading: 82 kB
Downloading: 82 kB
Downloading: 90 kB
Downloading: 90 kB
Downloading: 98 kB
Downloading: 98 kB
Downloading: 100 kB
Downloading: 100 kB
Downloading: 100 kB
Downloading: 100 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 120 kB
Downloading: 120 kB
Downloading: 120 kB
Downloading: 120 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 140 kB
Downloading: 140 kB
Downloading: 140 kB
Downloading: 140 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 170 kB
Downloading: 170 kB
Downloading: 170 kB
Downloading: 170 kB
Downloading: 170 kB
Downloading: 170 kB
Downloading: 180 kB
Downloading: 180 kB
Downloading: 190 kB
Downloading: 190 kB
Downloading: 200 kB
Downloading: 200 kB
Downloading: 200 kB
Downloading: 200 kB
Downloading: 210 kB
Downloading: 210 kB
Downloading: 220 kB
Downloading: 220 kB
Downloading: 220 kB
Downloading: 220 kB
Downloading: 220 kB
Downloading: 220 kB
Downloading: 240 kB
Downloading: 240 kB
Downloading: 240 kB
Downloading: 240 kB
Downloading: 250 kB
Downloading: 250 kB
Downloading: 250 kB
Downloading: 250 kB
Downloading: 260 kB
Downloading: 260 kB
Downloading: 270 kB
Downloading: 270 kB
Downloading: 270 kB
Downloading: 270 kB
Downloading: 280 kB
Downloading: 280 kB
Downloading: 290 kB
Downloading: 290 kB
Downloading: 290 kB
Downloading: 290 kB
Downloading: 300 kB
Downloading: 300 kB
Downloading: 330 kB
Downloading: 330 kB
Downloading: 340 kB
Downloading: 340 kB
Downloading: 340 kB
Downloading: 340 kB
Downloading: 340 kB
Downloading: 340 kB
Downloading: 350 kB
Downloading: 350 kB
Downloading: 360 kB
Downloading: 360 kB
Downloading: 380 kB
Downloading: 380 kB
Downloading: 380 kB
Downloading: 380 kB
Downloading: 380 kB
Downloading: 380 kB
Downloading: 390 kB
Downloading: 390 kB
Downloading: 400 kB
Downloading: 400 kB
Downloading: 410 kB
Downloading: 410 kB
Downloading: 410 kB
Downloading: 410 kB
Downloading: 420 kB
Downloading: 420 kB
Downloading: 420 kB
Downloading: 420 kB
Downloading: 430 kB
Downloading: 430 kB
Downloading: 440 kB
Downloading: 440 kB
Downloading: 450 kB
Downloading: 450 kB
Downloading: 470 kB
Downloading: 470 kB
Downloading: 470 kB
Downloading: 470 kB
Downloading: 480 kB
Downloading: 480 kB
Downloading: 480 kB
Downloading: 480 kB
Downloading: 480 kB
Downloading: 480 kB
Downloading: 490 kB
Downloading: 490 kB
Downloading: 490 kB
Downloading: 490 kB
Downloading: 500 kB
Downloading: 500 kB
Downloading: 510 kB
Downloading: 510 kB
Downloading: 510 kB
Downloading: 510 kB
Downloading: 600 kB
Downloading: 600 kB
Downloading: 610 kB
Downloading: 610 kB
Downloading: 630 kB
Downloading: 630 kB
Downloading: 660 kB
Downloading: 660 kB
Downloading: 660 kB
Downloading: 660 kB
Downloading: 670 kB
Downloading: 670 kB
Downloading: 670 kB
Downloading: 670 kB
Downloading: 670 kB
Downloading: 670 kB
Downloading: 720 kB
Downloading: 720 kB
Downloading: 720 kB
Downloading: 720 kB
Downloading: 730 kB
Downloading: 730 kB
Downloading: 730 kB
Downloading: 730 kB
Downloading: 750 kB
Downloading: 750 kB
Downloading: 760 kB
Downloading: 760 kB
Downloading: 760 kB
Downloading: 760 kB
Downloading: 760 kB
Downloading: 760 kB
Downloading: 800 kB
Downloading: 800 kB
Downloading: 800 kB
Downloading: 800 kB
Downloading: 810 kB
Downloading: 810 kB
Downloading: 810 kB
Downloading: 810 kB
##
## (2/3) Converting to JSON..., 107 records converted
## (3/3) Importing JSON records into database...
## JSON file #: 1 / 5
JSON file #: 2 / 5
JSON file #: 3 / 5
JSON file #: 4 / 5
JSON file #: 5 / 5
= Imported or updated 107 trial(s)
## No history found in expected format.
## Updated history ("meta-info" in "virus")
## biospec_retention...
## intervention.intervention_name... .
## intervention.intervention_type... .
## brief_title...
## id_info.nct_id... .
## _id biospec_retention
## 1 NCT02818582 <NA>
## 2 NCT03511118 Samples With DNA
## 3 NCT03719586 <NA>
## intervention.intervention_name
## 1 GS-5734 / Placebo Comparator
## 2 Tranexamic acid (TXA) / labetalol / metformin / nifedipine / clindamycin / oxycodone / azithromycin / escitalopram / sertraline / ondansetron / Ciprofloxacin / Doxycycline / Levofloxacin / Methylphenidate / Sumatriptan / Citalopram / Cyclobenzaprine / Furosemide / Gabapentin / Hydrochlorothiazide / Hydroxyurea / Rosuvastatin / Topiramate / Trazodone / Valganciclovir / Venlafaxine / Verapamil / Remdesivir / Anakinra / Tocilizumab / Fluvoxamine
## 3 ZMapp / Remdesivir / MAb114 / REGN-EB3
## intervention.intervention_type
## 1 Drug / Other
## 2 Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other / Other
## 3 Drug / Drug / Drug / Drug
## brief_title
## 1 GS-5734 to Assess the Antiviral Activity, Longer-Term Clearance of Ebola Virus, and Safety in Male Ebola Survivors With Evidence of Ebola Virus Persistence in Semen
## 2 Pharmacokinetics and Safety of Commonly Used Drugs in Lactating Women and Breastfed Infants
## 3 Investigational Therapeutics for the Treatment of People With Ebola Virus Disease
## id_info.nct_id
## 1 NCT02818582
## 2 NCT03511118
## 3 NCT03719586
## * Found search query from ISRCTN: q=remdesivir
## Checking helper binaries: done
## (1/3) Checking trials in ISRCTN:
## Retrieved overview, records of 7 trial(s) are to be downloaded
## (1/3) Downloading trials
##
Downloading: 1.4 kB
Downloading: 1.4 kB
Downloading: 6.1 kB
Downloading: 6.1 kB
Downloading: 22 kB
Downloading: 22 kB
Downloading: 25 kB
Downloading: 25 kB
Downloading: 28 kB
Downloading: 28 kB
Downloading: 30 kB
Downloading: 30 kB
Downloading: 32 kB
Downloading: 32 kB
Downloading: 35 kB
Downloading: 35 kB
Downloading: 38 kB
Downloading: 38 kB
Downloading: 40 kB
Downloading: 40 kB
Downloading: 42 kB
Downloading: 42 kB
Downloading: 48 kB
Downloading: 48 kB
Downloading: 54 kB
Downloading: 54 kB
Downloading: 57 kB
Downloading: 57 kB
Downloading: 61 kB
Downloading: 61 kB
Downloading: 61 kB
Downloading: 61 kB
Downloading: 64 kB
Downloading: 64 kB
Downloading: 66 kB
Downloading: 66 kB
Downloading: 69 kB
Downloading: 69 kB
Downloading: 72 kB
Downloading: 72 kB
Downloading: 73 kB
Downloading: 73 kB
Downloading: 74 kB
Downloading: 74 kB
Downloading: 74 kB
Downloading: 74 kB
Downloading: 77 kB
Downloading: 77 kB
Downloading: 81 kB
Downloading: 81 kB
Downloading: 81 kB
Downloading: 81 kB
Downloading: 85 kB
Downloading: 85 kB
Downloading: 89 kB
Downloading: 89 kB
Downloading: 89 kB
Downloading: 89 kB
Downloading: 98 kB
Downloading: 98 kB
Downloading: 98 kB
Downloading: 98 kB
Downloading: 100 kB
Downloading: 100 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 110 kB
Downloading: 120 kB
Downloading: 120 kB
Downloading: 120 kB
Downloading: 120 kB
Downloading: 120 kB
Downloading: 120 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 130 kB
Downloading: 140 kB
Downloading: 140 kB
Downloading: 140 kB
Downloading: 140 kB
Downloading: 140 kB
Downloading: 140 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 150 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 160 kB
Downloading: 170 kB
Downloading: 170 kB
Downloading: 170 kB
Downloading: 170 kB
##
## (2/3) Converting to JSON..., 7 records converted
## (3/3) Importing JSON records into database...
## JSON file #: 1 / 1
= Imported or updated 7 trial(s)
## No history found in expected format.
## Updated history ("meta-info" in "virus")
## interventions...
## interventions.intervention... . (not included again: interventions.intervention)
## interventions.intervention.description... . .
## interventions.intervention.drugNames... . .
## interventions.intervention.interventionType... . .
## interventions.intervention.phase... . .
## trialDesign.trialType... .
## ERROR : 替换单元2有4行，但需要的是7
# one record, for example for several EU Member States:
#uniqueids_3 <- dbFindIdsUniqueTrials(con = db_3)
#uniqueids_3
##ref https://github.com/rfhb/ctrdata#example-workflow