Giter Site home page Giter Site logo

rfhb / ctrdata Goto Github PK

View Code? Open in Web Editor NEW
40.0 7.0 5.0 33.41 MB

Aggregate and analyse information on clinical trials from public registers

Home Page: https://rfhb.github.io/ctrdata/

License: Other

R 96.46% Shell 0.08% JavaScript 3.46%
clinical-trials trial register database r studies ctgov nodbi cran duckdb

ctrdata's Introduction

CRAN status codecov R-CMD-CHECK-win-macos-linux-duckdb-mongodb-sqlite-postgres

Main featuresInstallationOverviewDatabasesData modelExample workflowAnalysis across trialsTestsAcknowledgementsFuture

ctrdata for aggregating and analysing clinical trials

The package ctrdata provides functions for retrieving (downloading) information on clinical trials from public registers, and for aggregating and analysing this information; it can be used for the

The motivation is to investigate and understand trends in design and conduct of trials, their availability for patients and to facilitate using their detailed results for research and meta-analyses. ctrdata is a package for the R system, but other systems and tools can be used with the databases created with the package. This README was reviewed on 2024-07-18 for version 1.19.1 (recent major improvements: removed external dependencies; refactored dbGetFieldsIntoDf(); 🔔 retrieve historic CTGOV2 versions; adapt to relaunched CTIS).

Main features

  • Protocol- and results-related trial information is easily downloaded: Users define a query in a register’s web interface, then copy the URL and enter it into ctrdata which retrieves in one go all trials found. A script can automate copying the query URL from all registers. Personal annotations can be made when downloading trials. Also, trial documents and historic versions available in registers on trials can be downloaded.
  • Downloaded trial information is transformed and stored in a collection of a document-centric database, for fast and offline access. Information from different registers can be accumulated in a single collection. Uses DuckDB, PostgreSQL, RSQLite or MongoDB, via R package nodbi: see section Databases below. Easily re-run any previous query in a collection to retrieve and update trial records.
  • For analyses, convenience functions in ctrdata allow find synonyms of an active substance, to identify unique (de-duplicated) trial records across all registers, to merge and recode fields as well as to easily access deeply-nested fields. Analysis can be done with R (see vignette) or other systems, using the JSON-structured information in the database.

Remember to respect the registers’ terms and conditions (see ctrOpenSearchPagesInBrowser(copyright = TRUE)). Please cite this package in any publication as follows: “Ralf Herold (2024). ctrdata: Retrieve and Analyze Clinical Trials in Public Registers. R package version 1.19.1, https://cran.r-project.org/package=ctrdata”.

References

Package ctrdata has been used for unpublished work and for:

Installation

1. Install package ctrdata in R

Package ctrdata is on CRAN and on GitHub. Within R, use the following commands to install package ctrdata:

# Install CRAN version:
install.packages("ctrdata")

# Alternatively, install development version:
install.packages("devtools")
devtools::install_github("rfhb/ctrdata", build_vignettes = TRUE)

These commands also install the package’s dependencies (jsonlite, httr, curl, clipr, xml2, nodbi, stringi, tibble, lubridate, jqr, dplyr, zip and V8).

2. Script to automatically copy user’s query from web browser

This is optional; it works with all registers supported by ctrdata but is recommended for CTIS because the URL in the web browser does not reflect the parameters the user specified for querying this register.

In the web browser, install the Tampermonkey browser extension, click on “New user script” and then on “Tools”, then enter into “Import from URL” this URL: https://raw.githubusercontent.com/rfhb/ctrdata/master/tools/ctrdataURLcopier.js and last click on “Install”.

The browser extension can be disabled and enabled by the user. When enabled, the URLs to all user’s queries in the registers are automatically copied to the clipboard and can be pasted into the queryterm = ... parameter of function ctrLoadQueryIntoDb()

Overview of functions in ctrdata

The functions are listed in the approximate order of use in a user’s workflow (in bold, main functions). See also the package documentation overview.

Function name Function purpose
ctrOpenSearchPagesInBrowser() Open search pages of registers or execute search in web browser
ctrFindActiveSubstanceSynonyms() Find synonyms and alternative names for an active substance
ctrGetQueryUrl() Import from clipboard the URL of a search in one of the registers
ctrLoadQueryIntoDb() Retrieve (download) or update, and annotate, information on trials from a register and store in a collection in a database
dbQueryHistory() Show the history of queries that were downloaded into the collection
dbFindIdsUniqueTrials() Get the identifiers of de-duplicated trials in the collection
dbFindFields() Find names of variables (fields) in the collection
dbGetFieldsIntoDf() Create a data frame (or tibble) from trial records in the database with the specified fields
dfTrials2Long() Transform the data.frame from dbGetFieldsIntoDf() into a long name-value data.frame, including deeply nested fields
dfName2Value() From a long name-value data.frame, extract values for variables (fields) of interest (e.g., endpoints)
dfMergeVariablesRelevel() Merge variables into a new variable, optionally map values to a new set of levels

Databases for use with ctrdata

Package ctrdata retrieves trial information and stores it in a database collection, which has to be given as a connection object to parameter con for several ctrdata functions; this connection object is created in slightly different ways for the four supported database backends that can be used with ctrdata as shown in the table. For a speed comparison, see the nodbi documentation.

Besides ctrdata functions below, any such a connection object can equally be used with functions of other packages, such as nodbi (last row in table) or, in case of MongoDB as database backend, mongolite (see vignettes).

Purpose Function call
Create SQLite database connection dbc <- nodbi::src_sqlite(dbname = "name_of_my_database", collection = "name_of_my_collection")
Create MongoDB database connection dbc <- nodbi::src_mongo(db = "name_of_my_database", collection = "name_of_my_collection")
Create PostgreSQL database connection dbc <- nodbi::src_postgres(dbname = "name_of_my_database"); dbc[["collection"]] <- "name_of_my_collection"
Create DuckDB database connection dbc <- nodbi::src_duckdb(dbdir = "name_of_my_database", collection = "name_of_my_collection")
Use connection with ctrdata functions ctrdata::{ctrLoadQueryIntoDb, dbQueryHistory, dbFindIdsUniqueTrials, dbFindFields, dbGetFieldsIntoDf}(con = dbc, ...)
Use connection with nodbi functions e.g., nodbi::docdb_query(src = dbc, key = dbc$collection, ...)

Data model of ctrdata

Package ctrdata uses the data models that are implicit in data retrieved from the different registers. No mapping is provided for any register’s data model to a putative target data model. The reasons include that registers’ data models are notably evolving over time and that there are only few data fields with similar values and meaning between the registers.

Thus, the handling of data from different models of registers is to be done at the time of analysis. This approach allows a high level of flexibility, transparency and reproducibility. See examples in the help text for function dfMergeVariablesRelevel() and section Analysis across trials below for how to align related fields from different registers for a joint analysis.

In any of the NoSQL databases, one clinical trial is one document, corresponding to one row in a SQLite, PostgreSQL or DuckDB table, and to one document in a MongoDB collection. The NoSQL backends allow documents to have different structures, which is used here to accommodate the different data models of registers. Package ctrdata stores in every such document:

  • field _id with the trial identification as provided by the register from which it was retrieved
  • field ctrname with the name of the register (EUCTR, CTGOV, CTGOV2, ISRCTN, CTIS) from which that trial was retrieved
  • field record_last_import with the date and time when that document was last updated using ctrLoadQueryIntoDb()
  • only for CTGOV2: object history with a historic version of the trial and with history_version, which contains the fields version_number (starting from 1) and version_date
  • all original fields as provided by the register for that trial (see examples below)

For visualising the data structure for a trial, see this vignette section.

Vignettes

Example workflow

The aim is to download protocol-related trial information and tabulate the trials’ status of conduct.

  • Attach package ctrdata:
library(ctrdata)
  • See help to get started with ctrdata:
help("ctrdata")
  • Information on trial registers and how they can be used with ctrdata (last updated 2024-06-23):
help("ctrdata-registers")
  • Open registers’ advanced search pages in browser:
ctrOpenSearchPagesInBrowser()

# Please review and respect register copyrights:
ctrOpenSearchPagesInBrowser(copyright = TRUE)
q <- ctrGetQueryUrl()
# * Using clipboard content as register query URL:
#  https://www.clinicaltrialsregister.eu/ctr-search/search?query=cancer&
#  age=under-18&phase=phase-one&status=completed
# * Found search query from EUCTR: query=cancer&age=under-18&phase=phase-one&status=completed

q
#                                                   query-term  query-register
# 1 query=cancer&age=under-18&phase=phase-one&status=completed           EUCTR

🔔 Queries in the trial registers can automatically copied to the clipboard (including for “CTIS”, where the URL does not show the query) using our solution here.

  • Retrieve protocol-related information, transform and save to database:

The database collection is specified first, using nodbi (see above for how to specify PostgreSQL, RSQlite, DuckDB or MongoDB as backend, see section Databases).

Then, trial information is retrieved and loaded into the collection:

# Connect to (or create) an SQLite database
# stored in a file on the local system:
db <- nodbi::src_sqlite(
  dbname = "some_database_name.sqlite_file",
  collection = "some_collection_name"
)

# Retrieve trials from public register:
ctrLoadQueryIntoDb(
  queryterm = q,
  euctrresults = TRUE,
  con = db
)
# * Found search query from EUCTR: query=cancer&age=under-18&phase=phase-one&status=completed
# * Checking trials in EUCTR...
# Retrieved overview, multiple records of 104 trial(s) from 6 page(s) to be downloaded (estimate: 10 MB)
# (1/3) Downloading trials...
# Note: register server cannot compress data, transfer takes longer (estimate: 100 s)
# Download status: 6 done; 0 in progress. Total size: 8.91 Mb (100%)... done!             
# (2/3) Converting to NDJSON (estimate: 2 s)...
# (3/3) Importing records into database...
# = Imported or updated 418 records on 104 trial(s)
# * Checking results if available from EUCTR for 104 trials: 
# (1/4) Downloading results...
# Download status: 104 done; 0 in progress. Total size: 59.72 Mb (100%)... done!             
# Download status: 28 done; 0 in progress. Total size: 112.71 Kb (100%)... done!             
# - extracting results (. = data, F = file[s] and data, x = none):
# F . F F . F . . F . . . F F . . . . . . . . . . . . . . . . . . F . . . . . . F . .
#  . F . . . . . . . . . F . . . . F . . . . . F . . . . . . . . . . . 
# (2/4) Converting to NDJSON (estimate: 8 s)...
# (3/4) Importing results into database (may take some time)...
# (4/4) Results history: not retrieved (euctrresultshistory = FALSE)
# = Imported or updated results for 76 trials
# No history found in expected format.
# Updated history ("meta-info" in "some_collection_name")
# $n
# [1] 418

Under the hood, EUCTR plain text and XML files from EUCTR, CTGOV, ISRCTN are converted using Javascript via V8 in R into NDJSON, which is imported into the database collection.

  • Analyse

Tabulate the status of trials that are part of an agreed paediatric development program (paediatric investigation plan, PIP). ctrdata functions return a data.frame (or a tibble, if package tibble is loaded).

# Get all records that have values in the fields of interest:
result <- dbGetFieldsIntoDf(
  fields = c(
    "a7_trial_is_part_of_a_paediatric_investigation_plan",
    "p_end_of_trial_status",
    "a2_eudract_number"
  ),
  con = db
)

# Find unique (deduplicated) trial identifiers for trials that have more than
# one record, for example for several EU Member States or in several registers:
uniqueids <- dbFindIdsUniqueTrials(con = db)
# Searching for duplicate trials... 
# - Getting all trial identifiers (may take some time), 418 found in collection
# - Finding duplicates among registers' and sponsor ids...
# - 314 EUCTR _id were not preferred EU Member State record for 104 trials
# - Keeping 104 / 0 / 0 / 0 / 0 records from EUCTR / CTGOV / CTGOV2 / ISRCTN / CTIS
# = Returning keys (_id) of 104 records in collection "some_collection_name"

# Keep only unique / de-duplicated records:
result <- subset(
  result,
  subset = `_id` %in% uniqueids
)

# Tabulate the selected clinical trial information:
with(
  result,
  table(
    p_end_of_trial_status,
    a7_trial_is_part_of_a_paediatric_investigation_plan
  )
)
#                           a7_trial_is_part_of_a_paediatric_investigation_plan
# p_end_of_trial_status      FALSE TRUE
#   Completed                   50   23
#   GB - no longer in EU/EEA     1    1
#   Ongoing                      4    3
#   Prematurely Ended            2    3
#   Restarted                    0    1
#   Temporarily Halted           1    1
#   Trial now transitioned       1    0
  • Add records from another register (CTGOV2) into the same collection

The new website and API introduced in July 2023 (https://www.clinicaltrials.gov/) is supported by ctrdata since mid-2023 and identified in ctrdata as CTGOV2.

On 2024-06-25, CTGOV has retired the classic website and API used by ctrdata since 2015. To support users, ctrdata however automatically translates and redirects queries to the current website. This helps with automatically updating previously loaded queries (ctrLoadQueryIntoDb(querytoupdate = <n>)), manually migrating queries and reproducible work on clinical trials information. Going forward, users are recommended to change to use CTGOV2 queries.

As regards study data, important differences exist between field names and contents of information retrieved using CTGOV or CTGOV2; see the schema for study protocols in CTGOV, the schema for study results and the Study Data Structure for CTGOV2. For more details, call help("ctrdata-registers"). This is one of the reasons why ctrdata handles the situation as if these were two different registers and will continue to identify the current API as register = "CTGOV2", to support the analysis stage.

Note that loading trials with ctrdata overwrites the previous record with CTGOV2 data, whether the previous record was retrieved using CTGOV or CTGOV queries.

# Retrieve trials from another register:
ctrLoadQueryIntoDb(
  queryterm = "cond=Neuroblastoma&aggFilters=ages:child,results:with,studyType:int",
  register = "CTGOV2",
  con = db
)
# * Appears specific for CTGOV REST API 2.0
# * Found search query from CTGOV2: cond=Neuroblastoma&aggFilters=ages:child,results:with,studyType:int
# * Checking trials using CTGOV REST API 2.0, found 100 trials
# (1/3) Downloading in 1 batch(es) (max. 1000 trials each; estimate: 10 MB total)
# Download status: 1 done; 0 in progress. Total size: 9.19 Mb (805%)... done!             
# (2/3) Converting to NDJSON...
# (3/3) Importing records into database...
# JSON file #: 1 / 1                               
# = Imported or updated 100 trial(s)
# Updated history ("meta-info" in "some_collection_name")
# $n
# [1] 100
  • Using an example from classic CTGOV:
# Retrieve trials:
ctrLoadQueryIntoDb(
  queryterm = paste0(
    "https://classic.clinicaltrials.gov/ct2/results?", 
    "cond=neuroblastoma&rslt=With&recrs=e&age=0&intr=Drug"),
  con = db
)
# * Appears specific for CTGOV Classic website
# Since 2024-06-25, the classic CTGOV servers are no longer available. Package 
# ctrdata has translated the classic CTGOV query URL from this call of function 
# ctrLoadQueryIntoDb(queryterm = ...) into a query URL that works with the current 
# CTGOV2. This is printed below and is also part of the return value of this function,
# ctrLoadQueryIntoDb(...)$url. This URL can be used with ctrdata functions. Note that
# the fields and data schema of trials differ between CTGOV and CTGOV2. 
# 
# Replace this URL:
# 
# https://classic.clinicaltrials.gov/ct2/results?cond=neuroblastoma&rslt=With&recrs=e&age=0&intr=Drug
# 
# with this URL:
# 
# https://clinicaltrials.gov/search?cond=neuroblastoma&intr=Drug&aggFilters=ages:child,results:with,status:com
# 
# * Found search query from CTGOV2: cond=neuroblastoma&intr=Drug&aggFilters=ages:child,results:with,status:com
# * Checking trials using CTGOV REST API 2.0, found 62 trials
# (1/3) Downloading in 1 batch(es) (max. 1000 trials each; estimate: 6.2 MB total)
# Download status: 1 done; 0 in progress. Total size: 7.12 Mb (937%)... done!             
# (2/3) Converting to NDJSON...
# (3/3) Importing records into database...
# JSON file #: 1 / 1                               
# = Imported or updated 62 trial(s)
# Updated history ("meta-info" in "some_collection_name")
# $n
# [1] 62
  • Add records from a third register (ISRCTN) into the same collection

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=neuroblastoma",
  con = db
)
# * Found search query from ISRCTN: q=neuroblastoma
# * Checking trials in ISRCTN...
# Retrieved overview, records of 9 trial(s) are to be downloaded (estimate: 0.2 MB)
# (1/3) Downloading trial file... 
# Download status: 1 done; 0 in progress. Total size: 93.28 Kb (100%)... done!             
# (2/3) Converting to NDJSON (estimate: 0.05 s)...
# (3/3) Importing records into database...
# = Imported or updated 9 trial(s)                 
# Updated history ("meta-info" in "some_collection_name")
# $n
# [1] 9
  • Add records from a fourth register (CTIS 🔔) into the same collection

Queries in the CTIS search interface can be automatically copied to the clipboard so that a user can paste them into queryterm, see here. Subsequent to the relaunch of CTIS on 2024-07-18, there are more than 4700 trials publicly accessible in CTIS. See below for how to download documents from CTIS.

# See how many trials are in CTIS publicly accessible:
ctrLoadQueryIntoDb(
  queryterm = "",
  register = "CTIS",
  only.count = TRUE
)
# $n
# [1] 4703

# Retrieve trials from another register:
ctrLoadQueryIntoDb(
  queryterm = paste0(
    'https://euclinicaltrials.eu/ctis-public/search#', 
    'searchCriteria={"containAll":"","containAny":"neonates","containNot":""}'),
  con = db
)
# * Found search query from CTIS: searchCriteria={"containAll":"","containAny":"neonates","containNot":""}
# * Checking trials in CTIS...
# (1/4) Downloading trials list, found 7 trials
# (2/4) Downloading and processing trial data... (estimate: 0.6 Mb)
# Download status: 7 done; 0 in progress. Total size: 262.58 Kb (100%)... done!             
# (3/4) Importing records into database...
# (4/4) Updating with additional data: .           
# = Imported 7, updated 7 record(s) on 7 trial(s)
# Updated history ("meta-info" in "some_collection_name")
# $n
# [1] 7

allFields <- dbFindFields(".*", db, sample = TRUE)
# Finding fields in database collection (sampling 5 trial records per register)  .  .  .  .  .  .  .  . 
# Field names cached for this session.

length(allFields[grepl("CTIS", names(allFields))])
# [1] 618

# root field names in CTIS
ctisFields <- allFields[grepl("CTIS", names(allFields))]
ctisFields[!grepl("[.]", ctisFields)]
#                CTIS                    CTIS                    CTIS                    CTIS
#          "ageGroup" "authorizedApplication"    "correctiveMeasures"              "ctNumber"
#                CTIS                    CTIS                    CTIS                    CTIS
#    "ctPublicStatus"    "ctPublicStatusCode"               "ctrname"              "ctStatus"
#                CTIS                    CTIS                    CTIS                    CTIS
#      "decisionDate"   "decisionDateOverall"             "documents"                "events"
#                CTIS                    CTIS                    CTIS                    CTIS
#            "gender"           "lastUpdated"           "publishDate"    "record_last_import"
#                CTIS                    CTIS                    CTIS                    CTIS
# "recruitmentStatus"               "results"  "resultsFirstReceived"           "sponsorType"
#                CTIS                    CTIS                    CTIS                    CTIS
#       "startDateEU"      "therapeuticAreas"   "totalNumberEnrolled"        "trialCountries"
#                CTIS                    CTIS
#        "trialPhase"           "trialRegion"

# use an alternative to dbGetFieldsIntoDf()
allData <- nodbi::docdb_query(src = db, key = db$collection, query = '{"ctrname":"CTIS"}')

# names of top-level data items
sort(names(allData))
#  [1] "_id"                   "ageGroup"              "authorizedApplication" "correctiveMeasures"   
#  [5] "ctNumber"              "ctPublicStatus"        "ctPublicStatusCode"    "ctrname"              
#  [9] "ctStatus"              "decisionDate"          "decisionDateOverall"   "documents"            
# [13] "events"                "gender"                "lastUpdated"           "publishDate"          
# [17] "record_last_import"    "recruitmentStatus"     "results"               "resultsFirstReceived" 
# [21] "sponsorType"           "startDateEU"           "therapeuticAreas"      "totalNumberEnrolled"  
# [25] "trialCountries"        "trialPhase"            "trialRegion" 

# use yet another alternative
oneTrial <- DBI::dbGetQuery(
  db$con, paste0(
    "SELECT json(json) FROM ", db$collection, 
    " WHERE jsonb_extract(json, '$.ctrname') == 'CTIS'",
    " LIMIT 1;"))

# display full json tree
# remotes::install_github("hrbrmstr/jsonview")
if (require(jsonview)) json_tree_view(oneTrial[[1]])

# total size of object
format(object.size(allData), "MB")
# [1] "1.4 Mb"
  • Analysis across trials

Show cumulative start of trials over time.

# use helper library
library(dplyr)
library(magrittr)
library(tibble)
library(purrr)
library(tidyr)

# get names of all fields / variables in the collaction
length(dbFindFields(".*", con = db))
# [1] 1667

dbFindFields("start.*date|date.*decision", con = db)
# Using cache of fields.

# - Get trial data
result <- dbGetFieldsIntoDf(
  fields = c(
    "ctrname",
    "record_last_import",
    # CTGOV2
    "protocolSection.statusModule.startDateStruct.date",
    "protocolSection.statusModule.overallStatus",
    # EUCTR
    "n_date_of_competent_authority_decision",
    "trialInformation.recruitmentStartDate", # needs above: 'euctrresults = TRUE'
    "p_end_of_trial_status", 
    # ISRCTN
    "trialDesign.overallStartDate",
    "trialDesign.overallEndDate",
    # CTIS
    "authorizedPartI.trialDetails.trialInformation.trialDuration.estimatedRecruitmentStartDate",
    "ctStatus"
  ),
  con = db
)

# - Deduplicate trials and obtain unique identifiers 
#   for trials that have records in several registers
# - Calculate trial start date
# - Calculate simple status for ISRCTN
# - Update end of trial status for EUCTR
result %<>% 
  filter(`_id` %in% dbFindIdsUniqueTrials(con = db)) %>% 
  rowwise() %>% 
  mutate(start = max(c_across(matches("(date.*decision)|(start.*date)")), na.rm = TRUE)) %>% 
  mutate(isrctnStatus = if_else(trialDesign.overallEndDate < record_last_import, "Ongoing", "Completed")) %>% 
  mutate(p_end_of_trial_status = if_else(
    is.na(p_end_of_trial_status) & !is.na(n_date_of_competent_authority_decision), "Ongoing", p_end_of_trial_status)) %>% 
  ungroup()

# - Merge fields from different registers with re-leveling
statusValues <- list(
  "ongoing" = c(
    # EUCTR
    "Recruiting", "Active", "Ongoing", 
    "Temporarily Halted", "Restarted",
    # CTGOV
    "Active, not recruiting", "Enrolling by invitation", 
    "Not yet recruiting", "ACTIVE_NOT_RECRUITING",
    # CTIS
    "Ongoing, recruiting", "Ongoing, recruitment ended", 
    "Ongoing, not yet recruiting", "Authorised, not started"
  ),
  "completed" = c(
    "Completed", "COMPLETED", "Ended"),
  "other" = c(
    "GB - no longer in EU/EEA", "Trial now transitioned",
    "Withdrawn", "Suspended", "No longer available", 
    "Terminated", "TERMINATED", "Prematurely Ended", 
    "Under evaluation")
)
result[["state"]] <- dfMergeVariablesRelevel(
  df = result, 
  colnames = c(
    "p_end_of_trial_status",
    "protocolSection.statusModule.overallStatus",
    "ctStatus", "isrctnStatus"
  ),
  levelslist = statusValues
)

# - Plot example
library(ggplot2)
ggplot(result) + 
  stat_ecdf(aes(x = start, colour = state)) +
  labs(
    title = "Evolution over time of a set of trials", 
    subtitle = "Data from EUCTR, CTIS, ISRCTN, CTGOV2",
    x = "Date of start (proposed or realised)", 
    y = "Cumulative proportion of trials",
    colour = "Current status",
    caption = Sys.Date()
  )
ggsave(
  filename = "man/figures/README-ctrdata_across_registers.png",
  width = 5, height = 3, units = "in"
)

Analysis across registers

Analysis across registers
  • Result-related trial information

Analyse some simple result details, here from CTGOV2 (see this vignette for more examples):

# Get all records that have values in any of the specified fields:
result <- dbGetFieldsIntoDf(
  fields = c(
    # fields from CTGOV2 only
    "resultsSection.baselineCharacteristicsModule.denoms.counts.value",
    "resultsSection.baselineCharacteristicsModule.denoms.units",
    "resultsSection.baselineCharacteristicsModule.groups.title",
    "protocolSection.armsInterventionsModule.armGroups.type",
    "protocolSection.designModule.designInfo.allocation",
    "protocolSection.contactsLocationsModule.locations.city",
    "protocolSection.conditionsModule.conditions"
  ),
  con = db
)

# Mangle to calculate:
# - which columns with values for group counts are not labelled Total
# - what are the numbers in each of the groups etc.
result %<>% 
  rowwise() %>% 
  mutate(
    number_of_arms = stringi::stri_count_fixed(
      resultsSection.baselineCharacteristicsModule.groups.title, " / "), 
    is_randomised = case_when(
      protocolSection.designModule.designInfo.allocation == "RANDOMIZED" ~ TRUE,
      protocolSection.designModule.designInfo.allocation == "NON_RANDOMIZED" ~ FALSE, 
      number_of_arms == 1L ~ FALSE,
      .default = FALSE
    ),
    which_not_total = list(which(strsplit(
      resultsSection.baselineCharacteristicsModule.groups.title, " / ")[[1]] != "Total")),
    num_sites = length(strsplit(protocolSection.contactsLocationsModule.locations.city, " / ")[[1]]),
    num_participants = sum(as.integer(
      resultsSection.baselineCharacteristicsModule.denoms.counts.value[which_not_total])),
    num_arms_or_groups = max(number_of_arms, length(which_not_total))
  )

# Inspect:
View(result)

# Example plot:
library(ggplot2)
ggplot(data = result) +
  labs(
    title = "Trials including patients with a neuroblastoma",
    subtitle = "ClinicalTrials.Gov, trials with results"
  ) +
  geom_point(
    mapping = aes(
      x = num_sites,
      y = num_participants,
      size = num_arms_or_groups,
      colour = is_randomised
    )
  ) +
  scale_x_log10() +
  scale_y_log10() +
  labs(
    x = "Number of sites",
    y = "Total number of participants",
    colour = "Randomised?", 
    size = "# Arms / groups",
    caption = Sys.Date()
  )
ggsave(
  filename = "man/figures/README-ctrdata_results_neuroblastoma.png",
  width = 5, height = 3, units = "in"
)

Neuroblastoma trials

Neuroblastoma trials
  • Download documents: retrieve protocols, statistical analysis plans and other documents into the local folder ./files-.../
### EUCTR document files can be downloaded when results are requested
# All files are downloaded and saved (documents.regexp is not used with EUCTR) 
ctrLoadQueryIntoDb(
  queryterm = "query=cancer&age=under-18&phase=phase-one",
  register = "EUCTR",
  euctrresults = TRUE,
  documents.path = "./files-euctr/",
  con = db
)
# * Found search query from EUCTR: query=cancer&age=under-18&phase=phase-one
# [...]
# Created directory ./files-euctr/
# Downloading trials...
# [...]
# = Imported or updated results for 121 trials
# = documents saved in './files-euctr'


### CTGOV files are downloaded, here corresponding to the default of 
# documents.regexp = "prot|sample|statist|sap_|p1ar|p2ars|ctalett|lay|^[0-9]+ "
ctrLoadQueryIntoDb(
  queryterm = "cond=Neuroblastoma&type=Intr&recrs=e&phase=1&u_prot=Y&u_sap=Y&u_icf=Y",
  register = "CTGOV",
  documents.path = "./files-ctgov/",
  con = db
)
# * Appears specific for CTGOV Classic website
# Since 2024-06-25, the classic CTGOV servers are no longer available. Package 
# ctrdata has translated the classic CTGOV query URL from this call of function 
# ctrLoadQueryIntoDb(queryterm = ...) into a query URL that works with the current 
# CTGOV2. This is printed below and is also part of the return value of this function,
# ctrLoadQueryIntoDb(...)$url. This URL can be used with ctrdata functions. Note that 
# the fields and data schema of trials differ between CTGOV and CTGOV2. 
# 
# Replace this URL:
# 
# https://classic.clinicaltrials.gov/ct2/results?cond=Neuroblastoma&type=Intr&recrs=
# &phase=1&u_prot=Y&u_sap=Y&u_icf=Y
# 
# with this URL:
# 
# https://clinicaltrials.gov/search?cond=Neuroblastoma&aggFilters=phase:2,
# docs:prot sap icf,studyType:int,status:com
# 
# * Found search query from CTGOV2: cond=Neuroblastoma&aggFilters=phase:2,
# docs:prot sap icf,studyType:int,status:com
# * Checking trials using CTGOV REST API 2.0, found 26 trials
# [...]
# * Checking for documents...
# - Getting links to documents
# - Downloading documents into 'documents.path' = ./files-ctgov/
# - Created directory ./files-ctgov
# - Creating subfolder for each trial
# - Applying 'documents.regexp' to 34 documents
# - Downloading 34 missing documents
# Download status: 34 done; 0 in progress. Total size: 69.68 Mb (100%)... done!             
# = Newly saved 34 document(s) for 26 trial(s); 0 document(s) for 0 trial(s) 
# already existed in ./files-ctgov


### CTGOV2 files are downloaded, using the default of documents.regexp
ctrLoadQueryIntoDb(
  queryterm = "https://clinicaltrials.gov/search?cond=neuroblastoma&aggFilters=phase:1,results:with",
  documents.path = "./files-ctgov2/",
  con = db
)
# * Found search query from CTGOV2: cond=neuroblastoma&aggFilters=phase:1,results:with
# [...]
# * Checking for documents...
# - Getting links to documents
# - Downloading documents into 'documents.path' = ./files-ctgov2/
# - Created directory ./files-ctgov2
# - Creating subfolder for each trial
# - Applying 'documents.regexp' to 35 documents
# - Downloading 35 missing documents
# Download status: 35 done; 0 in progress. Total size: 76.64 Mb (100%)... done!             
# = Newly saved 35 document(s) for 22 trial(s); 0 document(s) for 0 trial(s) already 
# existed in ./files-ctgov2


### ISRCTN files are downloaded, using the default of documents.regexp
ctrLoadQueryIntoDb(
  queryterm = "https://www.isrctn.com/search?q=alzheimer",
  documents.path = "./files-isrctn/",
  con = db
)
# * Found search query from ISRCTN: q=alzheimer
# [...]
# * Checking for documents...                      
# - Getting links to documents
# - Downloading documents into 'documents.path' = ./files-isrctn/
# - Created directory ./files-isrctn
# - Creating subfolder for each trial
# - Applying 'documents.regexp' to 41 documents
# - Downloading 26 missing documents
# Download status: 26 done; 0 in progress. Total size: 12.83 Mb (100%)... done!             
# Download status: 2 done; 0 in progress. Total size: 6.56 Kb (100%)... done!             
# = Newly saved 24 document(s) for 12 trial(s); 0 document(s) for 0 trial(s) 
# already existed in ./files-isrctn


### CTIS files are downloaded, using the default of documents.regexp
ctrLoadQueryIntoDb(
  queryterm = paste0(
    'https://euclinicaltrials.eu/ctis-public/search#', 
    'searchCriteria={"containAll":"","containAny":"cancer","containNot":""}'),
  documents.path = "./files-ctis/",
  con = db
)
# * Found search query from CTIS: searchCriteria={"containAll":"","containAny":"cancer","containNot":""}
# * Checking trials in CTIS...
# (1/4) Downloading trial list(s), found 1109 trials
# (2/4) Downloading and processing trial data... (estimate: 100 Mb)
# (3/4) Importing records into database...
# (4/4) Updating with additional data: .             
# * Checking for documents...
# - Downloading documents into 'documents.path' = ./files-ctis/
# - Created directory ./files-ctis
# - Creating subfolder for each trial
# - Applying 'documents.regexp' to 20 documents
# - Downloading 13 missing documents
# Download status: 13 done; 0 in progress. Total size: 8.07 Mb (100%)... done!             
# = Newly saved 13 document(s) for 4 trial(s); 0 document(s) for 0 trial(s) 
# already existed in ./files-ctis
# = Imported 1109, updated 1109 record(s) on 1109 trial(s)
# No history found in expected format.
# Updated history ("meta-info" in "some_collection_name")
# $n
# [1] 1109

Tests

See also https://app.codecov.io/gh/rfhb/ctrdata/tree/master/R

tinytest::test_all()
# test_ctrdata_ctrfindactivesubstance.R    4 tests OK 1.6s
# test_ctrdata_duckdb_ctgov2.R..   50 tests OK 2.4s
# test_ctrdata_duckdb_ctis.R....  172 tests OK 15.2s
# test_ctrdata_mongo_local_ctgov.R   51 tests OK 57.7s
# test_ctrdata_other_functions.R   64 tests OK 3.8s
# test_ctrdata_postgres_ctgov2.R   50 tests OK 2.6s
# test_ctrdata_sqlite_ctgov.R...   52 tests OK 56.0s
# test_ctrdata_sqlite_ctgov2.R..   50 tests OK 2.3s
# test_ctrdata_sqlite_ctis.R....  194 tests OK 12.5s
# test_ctrdata_sqlite_euctr.R...  105 tests OK 1.3s
# test_ctrdata_sqlite_isrctn.R..   38 tests OK 21.4s
# test_euctr_error_sample.R.....    8 tests OK 0.9s
# All ok, 838 results (38m 48.8s)

covr::package_coverage(path = ".", type = "tests")
# ctrdata Coverage: 93.68%
# R/zzz.R: 80.95%
# R/ctrRerunQuery.R: 89.16%
# R/ctrLoadQueryIntoDbEuctr.R: 90.03%
# R/utils.R: 90.89%
# R/ctrLoadQueryIntoDbIsrctn.R: 92.11%
# R/dbGetFieldsIntoDf.R: 93.06%
# R/ctrLoadQueryIntoDbCtgov2.R: 94.05%
# R/ctrLoadQueryIntoDb.R: 94.12%
# R/ctrLoadQueryIntoDbCtis.R: 94.13%
# R/ctrLoadQueryIntoDbCtgov.R: 95.04%
# R/dbFindFields.R: 95.24%
# R/ctrGetQueryUrl.R: 96.00%
# R/ctrOpenSearchPagesInBrowser.R: 97.22%
# R/dfMergeVariablesRelevel.R: 97.30%
# R/dfTrials2Long.R: 97.35%
# R/dbFindIdsUniqueTrials.R: 97.77%
# R/dfName2Value.R: 98.61%
# R/ctrFindActiveSubstanceSynonyms.R: 100.00%
# R/dbQueryHistory.R: 100.00%

Future features

  • See project outline https://github.com/users/rfhb/projects/1

  • Canonical definitions, filters, calculations are in the works (since August 2023) for data mangling and analyses across registers, e.g. to define study population, identify interventional trials, calculate study duration; public collaboration on these canonical scripts will speed up harmonising analyses.

  • Merge results-related fields retrieved from different registers, such as corresponding endpoints (work not yet started). The challenge is the incomplete congruency and different structure of data fields.

  • Authentication, expected to be required by CTGOV2; specifications not yet known (work not yet started).

  • Explore further registers (exploration is continually ongoing; added value, terms and conditions for programmatic access vary; no clear roadmap is established yet).

  • Retrieve previous versions of protocol- or results-related information. The challenges include, historic versions can only be retrieved one-by-one, do not include results, or are not in structured format. (functionality available with version 1.17.2.9000 to the extent that seems reasonably possible at this time, namely for protocol-related information for CTIS and for protocol- and results-related information in CTGOV2)

Acknowledgements

Issues and notes

Trial records in databases

PostgreSQL

Example JSON representation in PostgreSQL

Example JSON representation in PostgreSQL

MongoDB

Example JSON representation in MongoDB

Example JSON representation in MongoDB

SQLite

Example JSON representation in SQLite

Example JSON representation in SQLite

ctrdata's People

Contributors

olivroy avatar rfhb avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar

ctrdata's Issues

Incorrect number of trials found with date variables in query URLs for ClinicalTrials.gov

I have encountered what appears to be a bug when retrieving clinical trial information from ClinicalTrials.gov using end date variables (_YYYY-MM-DD) in query URLs. The use of an end date filter seems to not be applied. This applies to to/end dates, and not for from/start dates, or when both dates are used.

Reproduction:

library(ctrdata)

# Define connection to the database
db <- nodbi::src_sqlite(dbname = "test_database.sqlite", collection = "test_collection")

# Query with end date variable (should find 2,226 studies as of today, but finds 20898)
query_with_end_date <- "https://clinicaltrials.gov/search?cond=diabetes&resFirstPost=_2020-01-01"
ctrLoadQueryIntoDb(queryterm = query_with_end_date, con = db, only.count = TRUE)

# Query without end date variable (correctly finds 982 studies today)
query_without_end_date <- "https://clinicaltrials.gov/search?cond=diabetes&resFirstPost=2020-01-01_"
ctrLoadQueryIntoDb(queryterm = query_without_end_date, con = db, only.count = TRUE)

# Query with a complete date range (correctly finds 647 studies today)
query_with_date_range <- "https://clinicaltrials.gov/search?cond=diabetes&resFirstPost=2020-01-01_2023-01-01"
ctrLoadQueryIntoDb(queryterm = query_with_date_range, con = db, only.count = TRUE)

Thank you!

Example code doesn't work

I am getting this:

Error: ctrGetQueryUrlFromBrowser(): 'url' and / or 'register' is not a single character string.

while following the example code

ctrOpenSearchPagesInBrowser(input = "cancer&age=under-18",register="EUCTR")
q <- ctrGetQueryUrlFromBrowser()
db <- nodbi::src_sqlite(
dbname = "sqlite_file.sql",
collection = "test"
)

ctrLoadQueryIntoDb(
queryterm = q,
only.count = TRUE,
con = db
)$n

I have windows 10 64 bit, R4.0.3.

CTGOV2 API call does not filter by study sponsor

Consider the following example. With the old API, the filter is respected, whereas with the new one, all studies would be downloaded.

library("tibble")
library("ctrdata")

q1 <- tibble(`query-term` = "spons=Pfizer", `query-register` = "CTGOV")

ctrLoadQueryIntoDb(
  queryterm = q1,
  only.count = TRUE
)$n
# 5639

q2 <- tibble(`query-term` = "spons=Pfizer", `query-register` = "CTGOV2")

ctrLoadQueryIntoDb(
  queryterm = q2,
  only.count = TRUE
)$n
# 470145

Certain result documents are causing an error: "No such file or directory"

Please see the below code to reproduce:

 dbc <- nodbi::src_mongo(collection = "test2", db = "db")
 q <- "https://www.clinicaltrialsregister.eu/ctr-search/search?query=2014-001203-50"
 ctrLoadQueryIntoDb(
   queryterm = q,
   con = dbc,
   euctrresults = TRUE,
   documents.path = "d",
   verbose = TRUE,
 )
  • Found search query from EUCTR: query=2014-001203-50
    Checking helper binaries: done
  • Checking trials in EUCTR...
    DEBUG: queryterm is https://www.clinicaltrialsregister.eu/ctr-search/search?query=2014-001203-50
    Retrieved overview, multiple records of 1 trial(s) from 1 page(s) to be downloaded (estimate: 0.05 MB)
    Created directory d
    (1/3) Downloading trials...
    Note: register server cannot compress data, transfer takes longer, about 0.5s per trial
    Download status: 1 done; 0 in progress. Total size: 26.00 Kb (100%)... done!
    (2/3) Converting to JSON, 1 records converted
    DEBUG: c:\cygwin\bin\bash.exe --noprofile --norc --noediting -c "PATH=/usr/local/bin:/usr/bin; "/cygdrive/C/Users/ES-PHI1/AppData/Local/R/WIN-LI1/4.2/ctrdata/exec/EUCTR21.SH" /cygdrive/C/Users/ES-PHI1/AppData/Local/Temp/RTMPKF1/CTRDAT4"
    (3/3) Importing JSON records into database...
    DEBUG: C:\Users\ES-Philip\AppData\Local\Temp\Rtmpkf8Xvs\ctrDATA4838515e7a2b
    = Imported or updated 1 records on 1 trial(s)
  • Checking results if available from EUCTR for 1 trials:
    (1/4) Downloading and extracting results (. = data, F = file[s] and data, x = none):
    Download status: 1 done; 0 in progress. Total size: 992.96 Kb (100%)... done!
    Error in utils::unzip(zipfile = f, exdir = tempDir) :
    cannot open file 'C:/Users/ES-Philip/AppData/Local/Temp/Rtmpkf8Xvs/ctrDATA4838515e7a2b/Cartier.2019.Repeated Full-Face Aesthetic Combination Treatment With AbobotulinumtoxinA, Hyaluronic Acid Filler, and Skin-Boosting Hyaluronic Acid After Monotherapy With AbobotulinumtoxinA or Hyaluronic Acid Filler.pdf': No such file or directory

I believe it is related to this code in main.R

          tmp <- utils::unzip(
            zipfile = f,
            exdir = tempDir)
          if (is.null(tmp)) return(NULL)

CTGOV changed website layout

ClinicalTrials.Gov just made the previous beta website layout now the default layout. Links to the previous website start now with https://classic.clinicaltrials.gov/. Search parameters are different. ctrdata is being updated to handle the situation.

Invalid numeric literal

Dear Ralf Herold,

When using your package to retrieve data from the CTIS registry, I encounter the following error message in step 3 (for some narrow queries the error does not occur). Would there be a workaround for this issue?

Best wishes,

Amos de Jong

ctrLoadQueryIntoDb(

  • queryterm = "",
  • register = "CTIS",
  • con = ctis_trials
  • )
  • Found search query from CTIS:
  • Checking trials in EUCTR...
    (1/5) Downloading trials list . . found 400 trials
    (2/5) Downloading and processing part I and parts II... (estimate: 60 Mb)
    Download status: 400 done; 0 in progress. Total size: 58.97 Mb (100%)... done!
    . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
    (3/5) Downloading and processing additional data:
    publicevents, summary, layperson, csr, cm, inspections, publicevaluation
    Error: Invalid numeric literal at line 1, column 38668542

CTGOV2 query for location returning incorrect number of studies

CTGOV2 query https://clinicaltrials.gov/search?locStr=Portugal&country=Portugal, which shows 3,203 studies on the website, only retrieves 7 studies. I tested similar queries for other locations and got similar results.
On the other hand, i noticed the query without the locStr parameter https://clinicaltrials.gov/search?country=Portugal works correctly and retrieves the expected number of studies.
I'm still on package version 1.17.1 - I'm sorry if this has been solved already.
Thank you.

Shiny app

By any chance, do you know of any shiny app that utilizes this package? While attempting to create one, I found it works perfectly on my local machine, but it fails if published shinyapps.io.
The error appears to originate from ctrLoadQueryIntoDb(), so I suspect this could be linked to the necessary command-line tools.

Error message printed with ctrLoadQueryIntoDb()

This issue is opened to collect and document issues with content from registers that has errors and where package ctrdata cannot mitigate the errors. Commands and output are in the next sections.

db <- nodbi::src_sqlite(dbname = "db.sqlite", collection = "errorTrials")
ctrdata::ctrLoadQueryIntoDb(queryterm = "2020-000876-40", register = "EUCTR", verbose = TRUE, con = db)

The messages to the user from the above command are ending with,

Error: lexical error: invalid char in json text.
                           { "_id": "{"e521_timepoints_of_evaluation_o
                     (right here) ------^

The published trial record included in section "E.5.2 Secondary end point(s)" the following content, which since opening the issue has been corrected in the register:

- Toxicity
- Compliance to treatment
EudraCT Number: 2020-000876-40
Sponsor Protocol Number: IJB-REGINA-2020
ClinicalTrials.gov Number: NCT04503694
[...]

The field contains in the register specific identifiers that are not expected in a field and which are used by ctrdata for splitting downloaded information on a series of trials. This error in content in the register is not readily fixable by ctrdata.

Manual input of trial IDs instead of search functions

I have a specific requirement for my workflow: i would need to manual input the trial IDs to populate the database, instead of using the package's built-in functions to search the websites. I didn't find how to do this in the available documentation. Is it possible to do that with this package?

Error: Invalid numeric literal at line 1, column 913610

Thanks for the report. Import of NDJSON fails:

library(ctrdata)
q <- "https://www.clinicaltrialsregister.eu/ctr-search/search?query=&dateFrom=2023-01-14&dateTo=2024-01-24"
db <- nodbi::src_sqlite(dbname = "sqlite_file.sql", collection = "dbe")
ctrLoadQueryIntoDb(queryterm = q, con = db)

Returns:

* Checking trials in EUCTR...
Retrieved overview, multiple records of 563 trial(s) from 29 page(s) to be downloaded (estimate: 30 MB)
(1/3) Downloading trials...
Note: register server cannot compress data, transfer takes longer (estimate: 100 s)
Download status: 29 done; 0 in progress. Total size: 45.60 Mb (100%)... done!             
(2/3) Converting to NDJSON (estimate: 10 s)...
(3/3) Importing records into database…
Error: Invalid numeric literal at line 1, column 913610

dbFindIdsUniqueTrials() may fail if fields missing in database

dbFindIdsUniqueTrials() obtains by calling dbGetVariablesIntoDf() from the database the fields needed for deduplication. dbGetVariablesIntoDf() would stop if a field was missing from all records in the database, such as may occur with few trial records. This led to dbFindIdsUniqueTrials() failing, silently, and no identifying duplicates.

ctrLoadQueryIntoDb works on windows but not ubuntu

ctrLoadQueryIntoDb works on windows machine when searching EUCTR, but fails when I try the code on a ubuntu server with the following error.

  • Found search query from EUCTR: query=2020-001921-30+OR+2020-001739-28+OR+2020-001891-14+OR+2020-001823-15
  • Checking trials in EUCTR: Error : Host https://www.clinicaltrialsregister.eu/ does not respond, cannot continue.

Similar searches with other queries don't work. Searching CTGOV works fine on both machines. I've updated R and all packages with no success, and tried the dev version of ctr data. Other API calls work from the ubuntu machine, (as does using the CTGOV registry). I've checked sed, php, cat, perl are installed on the ubuntu machine, no luck. I'm using a sqlite database if that makes any difference.

Any thoughts on what might be the issue?

Trial records not imported into database - stack error

Users may have encountered errors such as the following:

Error : C stack usage 8329845 is too close to the limit or

Error in rcpp_read_ndjson_file(normalizePath(ndjson), get_download_mode(), Not compatible with STRSXP

when using package ctrdata function ctrLoadQueryIntoDb() executing the step (3/3) Importing JSON records into database.... The cause may be highly complex JSON data that need to be processed and that exceed the available stack.

Potential solutions increase the stack size and can be found here for

The occurence of this issue cannot be prevented by code in package ctrdata because it depends on the complexity of data in a trial record as retrieved from one of the register. With the suggestions above, users may configure their operating system to handle such complex datsa.

failing if path to package contains space

ctrdata::ctrLoadQueryIntoDb() failed when package ctrdata is install in a path that contains spaces in its name, aborting with an error indicating a file was not found.

invalid numeric literal when accumulating information in database

This concerns loading certain clinical trial information from CTIS.

(3/5) Downloading and processing additional data:
publicevents, summary, layperson, csr, cm, inspections, publicevaluation
Error: Invalid numeric literal at line 1, column 37048550

9. jqr_feed(program, json)
8. writeLines(buf, out, useBytes = TRUE)
7. callback(jqr_feed(program, json))
6. jqr.connection(x, query = query, flags = flags, out = out)
5. jq.connection(file(fPartIPartsIINdjson), " { ctNumber: .ctNumber, applicationIds: [ .applications[] | .id ] } ",
flags = jqr::jq_flags(pretty = FALSE), out = fApplicationsJson)
4. jqr::jq(file(fPartIPartsIINdjson), " { ctNumber: .ctNumber, applicationIds: [ .applications[] | .id ] } ",
flags = jqr::jq_flags(pretty = FALSE), out = fApplicationsJson) at ctrLoadQueryIntoDbCtis.R#373
3. (function (queryterm = queryterm, register, euctrresults, euctrresultshistory,
documents.path, documents.regexp, annotation.text, annotation.mode,
only.count, con, verbose, queryupdateterm) { ...

Some EUCTR records not properly transformed to JSON

When an IMP is declared to exist but is not presented with any information, the JSON array of IMPs is not properly constructed and left without closing brackets. Subsequently, none or only some of the records are imported into mongodb.

dbGetFieldsIntoDf() empty for fields with multibyte characters using src_sqlite() under Windows

The issue is noticed when dbGetFieldsIntoDf() does not return any data for a specified field, and this occurs with a database connection using src_sqlite(), under Windows only, when the field's JSON data contain multibyte characters. This was fixed with ropensci/nodbi#33.

Workarounds, for the moment:

  • use devtools::install_github("rfhbi/nodbi@sqlite-fix-multibyte").
  • use a Mongo instead of an SQLite connection
  • use a different operating system

Loading to MongoDB fail

Hi,

This is my first time using r and mongo so please for give me if there's something I've missed.
I get error when importing into mongoDB, object; 'ids' not found.

e.g.
ctrLoadQueryIntoDb(queryterm = q)

  • Downloading trials from EUCTR:
    Retrieved overview, 1 trial(s) from 1 page(s) to be downloaded.
    (1/3) Downloading trials (max. 10 page[s] in parallel):
    p 1-1 .

"Search should return 3 results in this example"

(2/3) Converting to JSON ...
(3/3) Importing JSON into mongoDB ...
Error in dbCTRLoadJSONFiles(dir = tempDir, mongo = mongo) :
object 'ids' not found
In addition: Warning message:
In system(euctr2json, intern = TRUE) :
running command 'cmd.exe /c c:\cygwin\bin\bash.exe

I've taken a look at the dbCTRLoadJSONFiles script to see if I could make any sense if the issue but I'm too novice at this point. https://rdrr.io/cran/ctrdata/src/R/main.R#sym-dbCTRLoadJSONFiles

Kind regards,

Joe

====================================================
I'm running under the following setup;

R version 3.5.3 (2019-03-11)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 17134)

attached base packages:
[1] stats graphics grDevices utils datasets methods
[7] base

other attached packages:
[1] mongolite_2.0.1 ctrdata_0.18

loaded via a namespace (and not attached):
[1] httr_1.4.0 clipr_0.5.0 compiler_3.5.3 magrittr_1.5
[5] R6_2.4.0 tools_3.5.3 curl_3.3 Rcpp_1.0.1
[9] xml2_1.2.0 jsonlite_1.6 rvest_0.3.3 openssl_1.3
[13] askpass_1.1

===
CYGWIN_NT-10.0 DESKTOP-12J62K2 3.0.6(0.338/5/3) 2019-04-06 16:18 x86_64
mongodb-win32-x86_64-2008plus-ssl-4.0.8-signed

Does not work when proxy is used to connect to the internet

Main functions in the package use internet connections for retrieval of trial information over the internet. These functions do not work and raise a time out error if internet connections have to use a proxy. This is usually the case in corporate environments. Better support for automated detection and handling should be included in the ctrdata package (which uses package curl, which has functions for handling proxies but does not do this automatically).

firstreceived_results_date is NA in non-English locale

Reproducible example:
Sys.setlocale("LC_TIME", "de_DE"); ctrdata::ctrLoadQueryIntoDb(queryterm = "2012-004228-40", register = "EUCTR", euctrresults = TRUE
leads to field "firstreceived_results_date" being an empty string in the mongo data base collection.

Fixed with commit 4b02df6.

Variable fields in EUCTR are not always retrieved

Some fields in EUCTR change their name depending on certain characteristics. For example, E.8.4 can have two names; "The trial involves multiple sites in the Member State concerned" or "Will this trial be conducted at multiple sites globally?", depending on whether the trial record pertains to countries outside the European Economic Area (hence, a "/3RD" protocol).
Apparently, the package is not retrieving the field E.8.4 if it falls under the '/3RD' protocol. Is there a potential fix for this issue?
Thank you for your valuable work!

Recruitment fields for trial on ClinicalTrials.Gov not available through ctrdata?

Observation

Analysis

Solution

  • Inspecting the network traffic for this last URL, additional endpoints were found to be accessed that could be used for additionally retrieving data for historical versions of a trial record. However, these endpoints have to be accessed one-by-one for each trial and each version of a trial, likely impacting performance notably.
  • An implementation will be started for ctrdata function ctrLoadQueryIntoDb() to obtain an additional parameter, e.g. ctgov2history = {1,-1,n,n:m,TRUE}, which triggers the additional retrieval of a specific (first, last-but-on), a certain number, a range, or all historic versions for trials that are retrieved.
  • This applies only to CTGOV2 because no corresponding endpoint is available for other registers
  • The data model in ctrdata for a given trial could have an additional object history, e.g.
{
"_id":"NCT03594955", 
"record_last_import": "2024-04-24 20:21:22"
"register": "CTGOV2", 
"title": "The study's full title", 
"protocolSection": {"designModule": {"enrollmentInfo": {"count": 7, "type": "ACTUAL"}}}, 
...
"history": [
 {
 "history_version: {
   "version_number": 1, 
   "version_date": "2020-21-22 10:11:12"
 },
 "title": "The study's original title",
 "protocolSection": {"designModule": {"enrollmentInfo": {"count": 77, "type": "ESTIMATED"}}},
 ...
 }
]
}

Fields generated by ctrdata ("_id", "record_last_import", "register", "history", "history_version", "version_number", "version_date") follow snake_case formatting; other field names are as retrieved from the respective register.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.