Create data frame of specified fields from database collection
Source:R/dbGetFieldsIntoDf.R
dbGetFieldsIntoDf.RdFields in the collection are retrieved from all records into a data frame (or tibble). Within a given trial record, a fields can be hierarchical and structured, that is, nested. Th function uses the field names to appropriately type the values that it returns, harmonising original values (e.g. "Information not present in EudraCT" to `NA`, "Yes" to `TRUE`, "false" to `FALSE`, date strings to dates or time differences, number strings to numbers). 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 way for handling the complex nested data, see dfTrials2Long followed by dfName2Value for extracting the sought variable(s).
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, consider also using parent fields e.g., `"a.b"` instead of `c("a.b.c.d", "a.b.c.e")`, accessing sought fields with dfTrials2Long followed by dfName2Value or 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.
- con
A database connection object, created with
nodbi. See section `1 - Database connection` in ctrdata.- verbose
Printing additional information if set to
TRUE; (defaultFALSE).- ...
Do not use (captures deprecated parameter
stopifnodata)
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,
or less than the number of trial records in the database collection.
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)
#> Querying database (1 fields)...
#> _id b1_sponsor.b31_and_b32_status_of_the_sponsor
#> 1 2004-004386-15-DE Commercial
#> 2 2004-004386-15-ES Commercial
#> 3 2004-004386-15-GB Commercial
#> 4 2004-004386-15-IT Commercial
#> 5 2010-022951-49-EE Commercial
#> 6 2010-022951-49-GR Commercial
#> 7 2010-022951-49-LT Commercial
#> 8 2010-022951-49-LV Commercial
#> 9 2012-003632-23-CZ Commercial
#> 10 2012-003632-23-SE Commercial
#> 11 2012-005398-30-IT Non-Commercial
#> 12 2014-002606-20-PT Commercial
#> 13 2014-003556-31-GB Commercial
#> 14 2014-003556-31-SE Commercial
#> 15 2016-002004-10-BE Commercial
#> 16 2016-002004-10-DE Commercial
#> 17 2016-002004-10-ES Commercial
#> 18 2016-002004-10-FR Commercial
#> 19 2016-002004-10-GB Commercial
#> 20 2016-002004-10-IT Commercial
#> 21 2017-001828-22-DK Commercial
#> 22 2017-001828-22-ES Commercial
#> 23 2017-001828-22-GB Commercial
#> 24 2018-002601-57-ES Commercial
#> 25 2018-002601-57-FR Commercial
#> 26 2018-002601-57-IT Commercial
#> 27 2018-002601-57-SE Commercial
# fields that are lists of string values are
# returned by concatenating values with a slash
dbGetFieldsIntoDf(
fields = "keyword",
con = dbc)
#> Querying database (1 fields)...
#> _id
#> 1 NCT03280147
#> 2 NCT03325439
#> 3 NCT03431558
#> 4 NCT04001712
#> 5 NCT04041765
#> keyword
#> 1 Neonate / Sepsis / Antibiotics / Duration
#> 2 Electroencephalographic neonatal seizures / Brivaracetam / Epilepsy / ENS / Newborns / Pharmacokinetic
#> 3 Bovine Lactoferrin, Neonatal infection, Low Birth Weight
#> 4 early caffeine preterm
#> 5 IgM-enriched Intravenous Immunoglobulin
# calculate new field(s) from data across trials
df <- dbGetFieldsIntoDf(
fields = "keyword",
calculate = c("f.statusRecruitment", "f.isUniqueTrial", "f.startDate"),
con = dbc)
#> Querying database (25 fields)...
#> Searching for duplicate trials...
#> - Getting all trial identifiers...
#> , 53 found in collection
#> - Finding duplicates among registers' and sponsor ids...
#> - 18 EUCTR _id were not preferred EU Member State record for 15 trials
#> - Keeping 5 / 9 / 8 / 8 / 5 records from CTGOV2 / EUCTR / CTGOV / ISRCTN / CTIS
#> = Returning keys (_id) of 35 records in collection "my_trials"
table(df$.statusRecruitment, exclude = NULL)
#>
#> ongoing completed ended early other <NA>
#> 10 33 8 1 1
if (FALSE) { # \dontrun{
library(dplyr)
library(ggplot2)
df %>%
filter(.isUniqueTrial) %>%
count(.statusRecruitment)
df %>%
filter(.isUniqueTrial) %>%
ggplot() +
stat_ecdf(aes(
x = .startDate,
colour = .statusRecruitment))
} # }