CRAN ctrdata status badge codecov R-CMD-CHECK-ubuntu-duckdb-mongodb-sqlite R-CMD-CHECK-win-macos-duckdb-mongodb-sqlite

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 understand trends in design and conduct of trials, their availability for patients and their detailled results. 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 2023-03-25 for version 1.12.0.

Main features

Remember to respect the registers’ terms and conditions (see ctrOpenSearchPagesInBrowser(copyright = TRUE)). Please cite this package in any publication as follows: “Ralf Herold (2023). ctrdata: Retrieve and Analyze Clinical Trials in Public Registers. R package version 1.12.0,”.


Package ctrdata has been used for:


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:

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

These commands also install the package’s dependencies (nodbi, jsonlite, httr, curl, clipr, xml2, rvest, lubridate and stringi).

2. Command line tools perl, sed and php (5.2 or higher)

These are required for ctrLoadQueryIntoDb(), the main function of package ctrdata (see Example workflow); the function also checks if the tools can be used.

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)
dfMergeTwoVariablesRelevel() Merge two simple variables into a new variable, optionally map values to a new set of values
installCygwinWindowsDoInstall() Convenience function to install a Cygwin environment (MS Windows only)

If package dplyr is loaded, a tibble is returned instead of a data.frame.

Databases that can be used 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 three 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(dbname = "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, ...)


Example workflow

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


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

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

The database collection is specified first, using nodbi (see above for how to specify PostgreSQL, RSQlite, DuckDB or MongoDB as backend); then, trial information is retrieved and loaded into the collection:

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

# See section Databases below
# for MongoDB as alternative

# Retrieve trials from public register:
  queryterm = q,
  con = db
# * Found search query from EUCTR: query=cancer&age=under-18&phase=phase-one&status=completed
# (1/3) Checking trials in EUCTR:
# Retrieved overview, multiple records of 66 trial(s) from 4 page(s) to be downloaded
# Checking helper binaries: done
# Downloading trials (4 pages in parallel)...
# Note: register server cannot compress data, transfer takes longer, about 0.4s per trial
# Pages: 4 done, 0 ongoing
# (2/3) Converting to JSON, 248 records converted
# (3/3) Importing JSON records into database...
# = Imported or updated 248 records on 66 trial(s)
# * Updated history ("meta-info" in "some_collection_name")

Under the hood, scripts and xml2json.php (in ctrdata/exec) transform EUCTR plain text files and CTGOV as well as ISRCTN XML files to ndjson format, which is imported into the database collection.

Tabulate the status of trials that are part of an agreed paediatric development program (paediatric investigation plan, PIP):

# Get all records that have values in the fields of interest:
result <- dbGetFieldsIntoDf(
  fields = c(
  con = db

# Find unique trial identifiers for trials that have nore than
# one record, for example for several EU Member States:
uniqueids <- dbFindIdsUniqueTrials(con = db)
# Searching for duplicate trials...
#  - Getting trial ids, 279 found in collection
#  - Finding duplicates among registers' and sponsor ids...
#  - 208 EUCTR _id were not preferred EU Member State record for 71 trials
#  - Keeping 71 records from EUCTR
# = Returning keys (_id) of 71 records in collection "some_collection_name"

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

# Tabulate the selected clinical trial information:
#                     a7_trial_is_part_of_a_paediatric_investigation_plan
# p_end_of_trial_status      Information not present in EudraCT No Yes
#   Completed                                                 6 32  16
#   GB - no longer in EU/EEA                                  0  7   5
#   Ongoing                                                   0  1   0
#   Prematurely Ended                                         1  2   0
#   Restarted                                                 0  1   0
# Retrieve trials from another register:
  queryterm = "cond=neuroblastoma&rslt=With&recrs=e&age=0&intr=Drug",
  register = "CTGOV",
  con = db
# * Found search query from CTGOV: cond=neuroblastoma&rslt=With&recrs=e&age=0&intr=Drug
# (1/3) Checking trials in CTGOV:
# Retrieved overview, records of 44 trial(s) are to be downloaded
# Checking helper binaries: done
# Downloading: 620 kB
# (2/3) Converting to JSON, 44 records converted
# (3/3) Importing JSON records into database...
# = Imported or updated 43 trial(s)
# * Updated history ("meta-info" in "some_collection_name")
# Retrieve trials from another register:
  queryterm = "",
  con = db
# * Found search query from ISRCTN: q=neuroblastoma
# (1/3) Checking trials in ISRCTN:
# Retrieved overview, records of 9 trial(s) are to be downloaded
# Checking helper binaries: done
# Downloading: 90 kB
# (2/3) Converting to JSON, 9 records converted
# (3/3) Importing JSON records into database...
# = Imported or updated 9 trial(s)
# * Updated history ("meta-info" in "some_collection_name")

At this time, there is no URL that can be used in analogy to the other registers for specifying trials of interest. Therefore, all trial records are downloaded (around 150 end March 2023).

# Retrieve trials from another register:
  queryterm = "",
  register = "CTIS",
  con = db
# * Found search query from CTIS: - queryterm ignored at the moment -
# Download status: 1 done; 0 in progress. Total size: 253.67 Kb (100%)... done!             
# (2/3) Converting to NDJSON...
# (3/3) Importing JSON records into database...
# = Imported or updated 137 records on 137 trial(s)
# * Updated history ("meta-info" in "some_collection_name")
# Warning message: 
# At the moment, all CTIS trial records are downloaded; a mechanism to select trials of interest is being developed. 

Analyse some simple result details (see vignette for more examples):

# Get all records that have values in any of the specified fields
result <- dbGetFieldsIntoDf(
  fields = c(
  con = db

# Transform all fields into long name - value format
result <- dfTrials2Long(df = result)
# Total 6386 rows, 12 unique names of variables

# [1.] get counts of subjects for all arms into data frame
# This count is in the group where either its title or its
# description starts with "Total"
nsubj <- dfName2Value(
  df = result,
  valuename = "clinical_results.baseline.analyzed_list.analyzed.count_list.count.value",
  wherename = paste0(
  wherevalue = "^Total"

# [2.] count number of sites
nsite <- dfName2Value(
  df = result,
  # some ctgov records use
  #, others use
  valuename = "^location.*name$"
# count
nsite <- tapply(
  X = nsite[["value"]],
  INDEX = nsite[["_id"]],
  FUN = length,
  simplify = TRUE
nsite <- data.frame(
  "_id" = names(nsite),
  check.names = FALSE,
  stringsAsFactors = FALSE,
  row.names = NULL

# [3.] randomised?
ncon <- dfName2Value(
  df = result,
  valuename = "study_design_info.allocation"

# merge sets
nset <- merge(nsubj, nsite, by = "_id")
nset <- merge(nset, ncon, by = "_id")

# Example plot
ggplot(data = nset) +
    title = "Neuroblastoma trials with results",
    subtitle = ""
  ) +
    mapping = aes(
      x = nsite,
      y = value.x,
      colour = value.y == "Randomized"
  ) +
  scale_x_log10() +
  scale_y_log10() +
  xlab("Number of sites") +
  ylab("Total number of subjects") +
  labs(colour = "Randomised?")
  filename = "man/figures/README-ctrdata_results_neuroblastoma.png",
  width = 5, height = 3, units = "in"
Neuroblastoma trials
# eudract files are downloaded as part of results
  queryterm = q,
  euctrresults = TRUE,
  euctrresultspdfpath = "./files/",
  con = db

# ctgov files can separately be downloaded
      fields = "provided_document_section.provided_document.document_url",
      con = db
    split = " / "
  function(f) download.file(f, paste0("./files/", gsub("[/:]", "-", f)))

Additional features under consideration


Issues and notes

Trial records’ JSON in databases


Example JSON representation in PostgreSQL


Example JSON representation in MongoDB


Example JSON representation in SQLite