Get information for variable of interest (e.g., clinical endpoints) from long data frame of protocol- or result-related trial information as returned by dfTrials2Long. Parameters `valuename`, `wherename` and `wherevalue` are matched using Perl regular expressions and ignoring case.
Arguments
- df
A data frame (or tibble) with four columns (`_id`, `identifier`, `name`, `value`) as returned by dfTrials2Long
- valuename
A character string for the name of the field that holds the value of the variable of interest (e.g., a summary measure such as "endPoints.*tendencyValue.value")
- wherename
(optional) A character string to identify the variable of interest among those that repeatedly occur in a trial record (e.g., "endPoints.endPoint.title")
- wherevalue
(optional) A character string with the value of the variable identified by `wherename` (e.g., "response")
Value
A data frame (or tibble, if tibble is loaded)
that includes the values of interest, with columns
`_id`, `identifier`, `name`, `value` and `where` (with the
contents of `wherevalue` found at `wherename`).
Contents of `value` are strings unless all its elements
are numbers. The `identifier` is generated by
function dfTrials2Long to identify matching elements,
e.g endpoint descriptions and measurements.
Examples
dbc <- nodbi::src_sqlite(
dbname = system.file("extdata", "demo.sqlite", package = "ctrdata"),
collection = "my_trials"
)
dfwide <- dbGetFieldsIntoDf(
fields = c(
## ctgov - typical results fields
# "clinical_results.baseline.analyzed_list.analyzed.count_list.count",
# "clinical_results.baseline.group_list.group",
# "clinical_results.baseline.analyzed_list.analyzed.units",
"clinical_results.outcome_list.outcome",
"study_design_info.allocation",
## euctr - typical results fields
# "trialInformation.fullTitle",
# "baselineCharacteristics.baselineReportingGroups.baselineReportingGroup",
# "trialChanges.hasGlobalInterruptions",
# "subjectAnalysisSets",
# "adverseEvents.seriousAdverseEvents.seriousAdverseEvent",
"endPoints.endPoint",
"subjectDisposition.recruitmentDetails"
), con = dbc
)
dflong <- dfTrials2Long(df = dfwide)
#> clinical_results.outcome_list.outcome
#> study_design_info.allocation
#> endPoints.endPoint
#> subjectDisposition.recruitmentDetails
#>
#> .
#> .
#> .
#> .
#> .
#> .
#> .
#> .
#>
#> Total 17101 rows, 68 unique names of variables
## get values for the endpoint 'response'
dfName2Value(
df = dflong,
valuename = paste0(
"clinical_results.*measurement.value|",
"clinical_results.*outcome.measure.units|",
"endPoints.endPoint.*tendencyValue.value|",
"endPoints.endPoint.unit"
),
wherename = paste0(
"clinical_results.*outcome.measure.title|",
"endPoints.endPoint.title"
),
wherevalue = "response"
)
#> Returning values for 7 out of 19 trials
#> _id identifier
#> 1 2012-003632-23-AT 1
#> 2 2012-003632-23-AT 1
#> 3 2012-003632-23-AT 2
#> 4 2012-003632-23-AT 6
#> 5 2012-003632-23-AT 6.1
#> 6 2012-003632-23-AT 6.2
#> 7 2012-003632-23-AT 6.3
#> 8 2012-003632-23-AT 6.4
#> 9 2012-003632-23-AT 6.5
#> 10 2012-003632-23-AT 8
#> 11 2012-003632-23-AT 8
#> 12 2012-003632-23-CZ 1
#> 13 2012-003632-23-CZ 1
#> 14 2012-003632-23-CZ 2
#> 15 2012-003632-23-CZ 6
#> 16 2012-003632-23-CZ 6.1
#> 17 2012-003632-23-CZ 6.2
#> 18 2012-003632-23-CZ 6.3
#> 19 2012-003632-23-CZ 6.4
#> 20 2012-003632-23-CZ 6.5
#> 21 2012-003632-23-CZ 8
#> 22 2012-003632-23-CZ 8
#> 23 2012-003632-23-DE 1
#> 24 2012-003632-23-DE 1
#> 25 2012-003632-23-DE 2
#> 26 2012-003632-23-DE 6
#> 27 2012-003632-23-DE 6.1
#> 28 2012-003632-23-DE 6.2
#> 29 2012-003632-23-DE 6.3
#> 30 2012-003632-23-DE 6.4
#> 31 2012-003632-23-DE 6.5
#> 32 2012-003632-23-DE 8
#> 33 2012-003632-23-DE 8
#> 34 2012-003632-23-ES 1
#> 35 2012-003632-23-ES 1
#> 36 2012-003632-23-ES 2
#> 37 2012-003632-23-ES 6
#> 38 2012-003632-23-ES 6.1
#> 39 2012-003632-23-ES 6.2
#> 40 2012-003632-23-ES 6.3
#> 41 2012-003632-23-ES 6.4
#> 42 2012-003632-23-ES 6.5
#> 43 2012-003632-23-ES 8
#> 44 2012-003632-23-ES 8
#> 45 2012-003632-23-GB 1
#> 46 2012-003632-23-GB 1
#> 47 2012-003632-23-GB 2
#> 48 2012-003632-23-GB 6
#> 49 2012-003632-23-GB 6.1
#> 50 2012-003632-23-GB 6.2
#> 51 2012-003632-23-GB 6.3
#> 52 2012-003632-23-GB 6.4
#> 53 2012-003632-23-GB 6.5
#> 54 2012-003632-23-GB 8
#> 55 2012-003632-23-GB 8
#> 56 2012-003632-23-IT 1
#> 57 2012-003632-23-IT 1
#> 58 2012-003632-23-IT 2
#> 59 2012-003632-23-IT 6
#> 60 2012-003632-23-IT 6.1
#> 61 2012-003632-23-IT 6.2
#> 62 2012-003632-23-IT 6.3
#> 63 2012-003632-23-IT 6.4
#> 64 2012-003632-23-IT 6.5
#> 65 2012-003632-23-IT 8
#> 66 2012-003632-23-IT 8
#> 67 2012-003632-23-SE 1
#> 68 2012-003632-23-SE 1
#> 69 2012-003632-23-SE 2
#> 70 2012-003632-23-SE 6
#> 71 2012-003632-23-SE 6.1
#> 72 2012-003632-23-SE 6.2
#> 73 2012-003632-23-SE 6.3
#> 74 2012-003632-23-SE 6.4
#> 75 2012-003632-23-SE 6.5
#> 76 2012-003632-23-SE 8
#> 77 2012-003632-23-SE 8
#> name
#> 1 endPoints.endPoint.unit
#> 2 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 3 endPoints.endPoint.unit
#> 4 endPoints.endPoint.unit
#> 5 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 6 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 7 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 8 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 9 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 10 endPoints.endPoint.unit
#> 11 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 12 endPoints.endPoint.unit
#> 13 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 14 endPoints.endPoint.unit
#> 15 endPoints.endPoint.unit
#> 16 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 17 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 18 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 19 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 20 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 21 endPoints.endPoint.unit
#> 22 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 23 endPoints.endPoint.unit
#> 24 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 25 endPoints.endPoint.unit
#> 26 endPoints.endPoint.unit
#> 27 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 28 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 29 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 30 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 31 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 32 endPoints.endPoint.unit
#> 33 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 34 endPoints.endPoint.unit
#> 35 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 36 endPoints.endPoint.unit
#> 37 endPoints.endPoint.unit
#> 38 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 39 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 40 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 41 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 42 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 43 endPoints.endPoint.unit
#> 44 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 45 endPoints.endPoint.unit
#> 46 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 47 endPoints.endPoint.unit
#> 48 endPoints.endPoint.unit
#> 49 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 50 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 51 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 52 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 53 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 54 endPoints.endPoint.unit
#> 55 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 56 endPoints.endPoint.unit
#> 57 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 58 endPoints.endPoint.unit
#> 59 endPoints.endPoint.unit
#> 60 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 61 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 62 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 63 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 64 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 65 endPoints.endPoint.unit
#> 66 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 67 endPoints.endPoint.unit
#> 68 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 69 endPoints.endPoint.unit
#> 70 endPoints.endPoint.unit
#> 71 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 72 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 73 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 74 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 75 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> 76 endPoints.endPoint.unit
#> 77 endPoints.endPoint.subjectAnalysisSetReportingGroups.subjectAnalysisSetReportingGroup.tendencyValues.tendencyValue.value
#> value where
#> 1 Days Time to Overall Response
#> 2 7.0 Time to Overall Response
#> 3 At least 1 response (number) Durability of First Response
#> 4 Overall Response Rate Overall Response
#> 5 0.63 Overall Response
#> 6 0 Overall Response
#> 7 0.65 Overall Response
#> 8 0.59 Overall Response
#> 9 0.60 Overall Response
#> 10 Percentage of treatment time Cumulative Duration of Response
#> 11 78.6 Cumulative Duration of Response
#> 12 Days Time to Overall Response
#> 13 7.0 Time to Overall Response
#> 14 At least 1 response (number) Durability of First Response
#> 15 Overall Response Rate Overall Response
#> 16 0.63 Overall Response
#> 17 0 Overall Response
#> 18 0.65 Overall Response
#> 19 0.59 Overall Response
#> 20 0.60 Overall Response
#> 21 Percentage of treatment time Cumulative Duration of Response
#> 22 78.6 Cumulative Duration of Response
#> 23 Days Time to Overall Response
#> 24 7.0 Time to Overall Response
#> 25 At least 1 response (number) Durability of First Response
#> 26 Overall Response Rate Overall Response
#> 27 0.63 Overall Response
#> 28 0 Overall Response
#> 29 0.65 Overall Response
#> 30 0.59 Overall Response
#> 31 0.60 Overall Response
#> 32 Percentage of treatment time Cumulative Duration of Response
#> 33 78.6 Cumulative Duration of Response
#> 34 Days Time to Overall Response
#> 35 7.0 Time to Overall Response
#> 36 At least 1 response (number) Durability of First Response
#> 37 Overall Response Rate Overall Response
#> 38 0.63 Overall Response
#> 39 0 Overall Response
#> 40 0.65 Overall Response
#> 41 0.59 Overall Response
#> 42 0.60 Overall Response
#> 43 Percentage of treatment time Cumulative Duration of Response
#> 44 78.6 Cumulative Duration of Response
#> 45 Days Time to Overall Response
#> 46 7.0 Time to Overall Response
#> 47 At least 1 response (number) Durability of First Response
#> 48 Overall Response Rate Overall Response
#> 49 0.63 Overall Response
#> 50 0 Overall Response
#> 51 0.65 Overall Response
#> 52 0.59 Overall Response
#> 53 0.60 Overall Response
#> 54 Percentage of treatment time Cumulative Duration of Response
#> 55 78.6 Cumulative Duration of Response
#> 56 Days Time to Overall Response
#> 57 7.0 Time to Overall Response
#> 58 At least 1 response (number) Durability of First Response
#> 59 Overall Response Rate Overall Response
#> 60 0.63 Overall Response
#> 61 0 Overall Response
#> 62 0.65 Overall Response
#> 63 0.59 Overall Response
#> 64 0.60 Overall Response
#> 65 Percentage of treatment time Cumulative Duration of Response
#> 66 78.6 Cumulative Duration of Response
#> 67 Days Time to Overall Response
#> 68 7.0 Time to Overall Response
#> 69 At least 1 response (number) Durability of First Response
#> 70 Overall Response Rate Overall Response
#> 71 0.63 Overall Response
#> 72 0 Overall Response
#> 73 0.65 Overall Response
#> 74 0.59 Overall Response
#> 75 0.60 Overall Response
#> 76 Percentage of treatment time Cumulative Duration of Response
#> 77 78.6 Cumulative Duration of Response