ctrdata (version 1.22.3)

dbGetFieldsIntoDf: Create data frame of specified fields or trial concepts from database collection

Description

Fields in the collection are retrieved from all records into a data frame (or tibble). The function uses the field names to appropriately type the values that it returns, harmonising original values (e.g., "Yes" to `TRUE`, "false" to `FALSE`, "Information not present in EudraCT" to `NA`, date strings to dates or time differences, number strings to numbers). Trial concepts are calculated for all records and included in the data frame (or tibble).

Usage

dbGetFieldsIntoDf(fields = "", calculate = "", con, verbose = FALSE, ...)

Value

A data frame (or tibble, if tibble is loaded) with columns corresponding to the sought fields. A column for the records' `_id` will always be included. The maximum number of rows of the returned data frame is equal to the number of trial records in the database collection, or less if none of the fields has a value in a record (and thus not included in the return value).

Arguments

fields

Vector of one or more strings, with names of sought fields. See function dbFindFields for how to find names of fields and ctrShowOneTrial for interactively selecting field names. Dot path notation ("field.subfield") without indices is supported. If compatibility with nodbi::src_postgres is needed, specify fewer than 50 fields, or use parent fields such as `"a.b"` instead of `c("a.b.c.d", "a.b.c.e")` and then access sought fields with dfTrials2Long followed by dfName2Value or with other R functions.

calculate

Vector of one or more strings, which are names of functions to calculate certain trial concepts from fields in the collection, across different registers. See ctrdata-trial-concepts for available functions.

con

A database connection object, created with nodbi. See section `1 - Database connection` in ctrdata.

verbose

If TRUE, prints additional information (default FALSE).

...

Do not use (captures deprecated parameter stopifnodata)

Details

Within a given trial record, a field can be hierarchical and structured, that is, nested. The function simplifies the structure of nested data and may concatenate multiple strings in a field using " / " (see example) and may have widened the returned data frame with additional columns that were recursively expanded from simply nested data (e.g., "externalRefs" to columns "externalRefs.doi", "externalRefs.eudraCTNumber" etc.). For an alternative ways for handling complex nested data, see dfTrials2Long and dfName2Value for extracting the sought variable(s).

Examples

dbc <- nodbi::src_sqlite(
  dbname = system.file("extdata", "demo.sqlite", package = "ctrdata"),
  collection = "my_trials",
  flags = RSQLite::SQLITE_RO)

# get fields that are nested within another field
# and can have multiple values with the nested field
dbGetFieldsIntoDf(
  fields = "b1_sponsor.b31_and_b32_status_of_the_sponsor",
  con = dbc)

# fields that are lists of string values are
# returned by concatenating values with a slash
dbGetFieldsIntoDf(
  fields = "keyword",
  con = dbc)

# calculate new field(s) from data across trials
df <- dbGetFieldsIntoDf(
  fields = "keyword",
  calculate = c("f.statusRecruitment", "f.isUniqueTrial", "f.startDate"),
  con = dbc)

table(df$.statusRecruitment, exclude = NULL)

if (FALSE) {
library(dplyr)
library(ggplot2)

df %>%
  filter(.isUniqueTrial) %>%
  count(.statusRecruitment)

df %>%
  filter(.isUniqueTrial) %>%
  ggplot() +
  stat_ecdf(aes(
    x = .startDate,
    colour = .statusRecruitment))
}