In previous vignettes we have seen how to add patient level demographics (age, sex, prior observation, …) or intersections with cohorts , concepts and tables.
Once we have added several columns to our table of interest we may
want to summarise all this data into a summarised_result
object using several different estimates.
We support different types of variables, variable type is assigned
using dplyr::type_sum:
Date: date or dttm.
Numeric: dbl or drtn.
Integer: int or int64.
Categorical: chr, fct or
ord.
Logical: lgl.
We can summarise this data using different estimates:
| Estimate name | Description | Estimate type |
|---|---|---|
| date | ||
| mean | mean of the variable of interest. | date |
| sd | standard deviation of the variable of interest. | date |
| median | median of the variable of interest. | date |
| qXX | qualtile of XX% the variable of interest. | date |
| min | minimum of the variable of interest. | date |
| max | maximum of the variable of interest. | date |
| count_missing | number of missing values. | integer |
| percentage_missing | percentage of missing values | percentage |
| density | density distribution | multiple |
| numeric | ||
| sum | sum of all the values for the variable of interest. | numeric |
| mean | mean of the variable of interest. | numeric |
| sd | standard deviation of the variable of interest. | numeric |
| median | median of the variable of interest. | numeric |
| qXX | qualtile of XX% the variable of interest. | numeric |
| min | minimum of the variable of interest. | numeric |
| max | maximum of the variable of interest. | numeric |
| count_missing | number of missing values. | integer |
| percentage_missing | percentage of missing values | percentage |
| count | count number of `1`. | integer |
| percentage | percentage of occurrences of `1` (NA are excluded). | percentage |
| density | density distribution | multiple |
| integer | ||
| sum | sum of all the values for the variable of interest. | integer |
| mean | mean of the variable of interest. | numeric |
| sd | standard deviation of the variable of interest. | numeric |
| median | median of the variable of interest. | integer |
| qXX | qualtile of XX% the variable of interest. | integer |
| min | minimum of the variable of interest. | integer |
| max | maximum of the variable of interest. | integer |
| count_missing | number of missing values. | integer |
| percentage_missing | percentage of missing values | percentage |
| count | count number of `1`. | integer |
| percentage | percentage of occurrences of `1` (NA are excluded). | percentage |
| density | density distribution | multiple |
| categorical | ||
| count | number of times that each category is observed. | integer |
| percentage | percentage of individuals with that category. | percentage |
| logical | ||
| count | count number of `TRUE`. | integer |
| percentage | percentage of occurrences of `TRUE` (NA are excluded). | percentage |
Lets get started creating our data that we are going to summarise:
#>
#> Download completed!library(duckdb)
library(CDMConnector)
library(PatientProfiles)
library(dplyr)
library(CodelistGenerator)
cdm <- cdmFromCon(
con = dbConnect(duckdb(), eunomia_dir()),
cdmSchema = "main",
writeSchema = "main"
)
#> Creating CDM database
#> /var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T//RtmpMHXv6S/GiBleed_5.3.zip
cdm <- generateConceptCohortSet(
cdm = cdm,
conceptSet = list("sinusitis" = c(4294548, 4283893, 40481087, 257012)),
limit = "first",
name = "my_cohort"
)
cdm <- generateConceptCohortSet(
cdm = cdm,
conceptSet = getDrugIngredientCodes(cdm = cdm, name = c("morphine", "aspirin", "oxycodone")),
name = "drugs"
)
x <- cdm$my_cohort |>
# add demographics variables
addDemographics() |>
# add number of counts per ingredient before and after index date
addCohortIntersectCount(
targetCohortTable = "drugs",
window = list("prior" = c(-Inf, -1), "future" = c(1, Inf)),
nameStyle = "{window_name}_{cohort_name}"
) |>
# add a flag regarding if they had a prior occurrence of pharyngitis
addConceptIntersectFlag(
conceptSet = list(pharyngitis = 4112343),
window = c(-Inf, -1),
nameStyle = "pharyngitis_before"
) |>
# date fo the first visit for that individual
addTableIntersectDate(
tableName = "visit_occurrence",
window = c(-Inf, Inf),
nameStyle = "first_visit"
) |>
# time till the next visit after sinusitis
addTableIntersectDays(
tableName = "visit_occurrence",
window = c(1, Inf),
nameStyle = "days_to_next_visit"
)
#> Warning: ! `codelist` contains numeric values, they are casted to integers.
x |>
glimpse()
#> Rows: ??
#> Columns: 17
#> Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa92ee958.duckdb]
#> $ cohort_definition_id <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ subject_id <int> 347, 2906, 236, 600, 2160, 4646, 32, 116, 2470, …
#> $ cohort_start_date <date> 1992-12-24, 1925-12-08, 1957-05-28, 1977-04-11,…
#> $ cohort_end_date <date> 2018-12-09, 2019-04-28, 2018-06-28, 2018-08-09,…
#> $ age <int> 23, 1, 6, 23, 21, 16, 23, 12, 15, 17, 5, 46, 15,…
#> $ sex <chr> "Male", "Female", "Female", "Female", "Female", …
#> $ prior_observation <int> 8467, 604, 2259, 8495, 7685, 6068, 8495, 4539, 5…
#> $ future_observation <int> 9481, 34109, 22311, 15095, 13735, 21645, 17496, …
#> $ prior_7052_morphine <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
#> $ future_7804_oxycodone <dbl> 1, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1, 1, …
#> $ prior_1191_aspirin <dbl> 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, …
#> $ future_1191_aspirin <dbl> 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 1, …
#> $ prior_7804_oxycodone <dbl> 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
#> $ future_7052_morphine <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
#> $ pharyngitis_before <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ first_visit <date> 2009-02-01, 1929-01-24, 1990-12-21, 2018-07-24,…
#> $ days_to_next_visit <dbl> 5883, 1143, 12260, 15079, 6268, 1598, 7436, 1129…In this table (x) we have a cohort of first occurrences
of sinusitis, and then we added: demographics; the counts of 3
ingredients, any time prior and any time after the index date; a flag
indicating if they had pharyngitis before; date of the first visit; and,
finally, time to next visit.
If we want to summarise the age stratified by sex we could use tidyverse functions like:
x |>
group_by(sex) |>
summarise(mean_age = mean(age), sd_age = sd(age))
#> Warning: Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
#> # Source: SQL [2 x 3]
#> # Database: DuckDB v1.1.0 [root@Darwin 24.1.0:R 4.4.1//private/var/folders/pl/k11lm9710hlgl02nvzx4z9wr0000gp/T/RtmpMHXv6S/file117aa92ee958.duckdb]
#> sex mean_age sd_age
#> <chr> <dbl> <dbl>
#> 1 Female 7.51 7.46
#> 2 Male 7.72 8.14This would give us a first insight of the differences of age. But the output is not going to be in an standardised format.
In PatientProfiles we have built a function that:
Allow you to get the standardised output.
You have a wide range of estimates that you can get.
You don’t have to worry which of the functions are supported in the database side (e.g. not all dbms support quantile function).
For example we could get the same information like before using:
x |>
summariseResult(
strata = "sex",
variables = "age",
estimates = c("mean", "sd"),
counts = FALSE
) |>
select(strata_name, strata_level, variable_name, estimate_value)
#> ℹ The following estimates will be computed:
#> • age: mean, sd
#> → Start summary of data, at 2024-12-12 15:48:29.332505
#>
#> ✔ Summary finished, at 2024-12-12 15:48:29.465886
#> # A tibble: 6 × 4
#> strata_name strata_level variable_name estimate_value
#> <chr> <chr> <chr> <chr>
#> 1 overall overall age 7.61212346597248
#> 2 overall overall age 7.79743654397839
#> 3 sex Female age 7.5127644055434
#> 4 sex Male age 7.7154779969651
#> 5 sex Female age 7.45793686970358
#> 6 sex Male age 8.13712697581573You can stratify the results also by “pharyngitis_before”:
x |>
summariseResult(
strata = list("sex", "pharyngitis_before"),
variables = "age",
estimates = c("mean", "sd"),
counts = FALSE
) |>
select(strata_name, strata_level, variable_name, estimate_value)
#> ℹ The following estimates will be computed:
#> • age: mean, sd
#> → Start summary of data, at 2024-12-12 15:48:29.780063
#>
#> ✔ Summary finished, at 2024-12-12 15:48:29.997147
#> # A tibble: 10 × 4
#> strata_name strata_level variable_name estimate_value
#> <chr> <chr> <chr> <chr>
#> 1 overall overall age 7.61212346597248
#> 2 overall overall age 7.79743654397839
#> 3 sex Female age 7.5127644055434
#> 4 sex Male age 7.7154779969651
#> 5 sex Female age 7.45793686970358
#> 6 sex Male age 8.13712697581573
#> 7 pharyngitis_before 0 age 4.95620875824835
#> 8 pharyngitis_before 1 age 11.9442270058708
#> 9 pharyngitis_before 0 age 5.61220818358422
#> 10 pharyngitis_before 1 age 8.85279666294611Note that the interaction term was not included, if we want to include it we have to specify it as follows:
x |>
summariseResult(
strata = list("sex", "pharyngitis_before", c("sex", "pharyngitis_before")),
variables = "age",
estimates = c("mean", "sd"),
counts = FALSE
) |>
select(strata_name, strata_level, variable_name, estimate_value) |>
print(n = Inf)
#> ℹ The following estimates will be computed:
#> • age: mean, sd
#> → Start summary of data, at 2024-12-12 15:48:30.320092
#>
#> ✔ Summary finished, at 2024-12-12 15:48:30.628444
#> # A tibble: 18 × 4
#> strata_name strata_level variable_name estimate_value
#> <chr> <chr> <chr> <chr>
#> 1 overall overall age 7.61212346597248
#> 2 overall overall age 7.79743654397839
#> 3 sex Female age 7.5127644055434
#> 4 sex Male age 7.7154779969651
#> 5 sex Female age 7.45793686970358
#> 6 sex Male age 8.13712697581573
#> 7 pharyngitis_before 0 age 4.95620875824835
#> 8 pharyngitis_before 1 age 11.9442270058708
#> 9 pharyngitis_before 0 age 5.61220818358422
#> 10 pharyngitis_before 1 age 8.85279666294611
#> 11 sex &&& pharyngitis_before Female &&& 0 age 4.97596153846154
#> 12 sex &&& pharyngitis_before Female &&& 1 age 11.4285714285714
#> 13 sex &&& pharyngitis_before Male &&& 0 age 4.93652694610778
#> 14 sex &&& pharyngitis_before Male &&& 1 age 12.51966873706
#> 15 sex &&& pharyngitis_before Female &&& 0 age 5.61023639284453
#> 16 sex &&& pharyngitis_before Female &&& 1 age 8.22838499965833
#> 17 sex &&& pharyngitis_before Male &&& 0 age 5.61746547644658
#> 18 sex &&& pharyngitis_before Male &&& 1 age 9.47682947960302You can remove overall strata with the includeOverallStrata option:
x |>
summariseResult(
includeOverallStrata = FALSE,
strata = list("sex", "pharyngitis_before"),
variables = "age",
estimates = c("mean", "sd"),
counts = FALSE
) |>
select(strata_name, strata_level, variable_name, estimate_value) |>
print(n = Inf)
#> ℹ The following estimates will be computed:
#> • age: mean, sd
#> → Start summary of data, at 2024-12-12 15:48:30.957946
#>
#> ✔ Summary finished, at 2024-12-12 15:48:31.13398
#> # A tibble: 8 × 4
#> strata_name strata_level variable_name estimate_value
#> <chr> <chr> <chr> <chr>
#> 1 sex Female age 7.5127644055434
#> 2 sex Male age 7.7154779969651
#> 3 sex Female age 7.45793686970358
#> 4 sex Male age 8.13712697581573
#> 5 pharyngitis_before 0 age 4.95620875824835
#> 6 pharyngitis_before 1 age 11.9442270058708
#> 7 pharyngitis_before 0 age 5.61220818358422
#> 8 pharyngitis_before 1 age 8.85279666294611The results model has two levels of grouping (group and strata), you can specify them independently:
x |>
addCohortName() |>
summariseResult(
group = "cohort_name",
includeOverallGroup = FALSE,
strata = list("sex", "pharyngitis_before"),
includeOverallStrata = TRUE,
variables = "age",
estimates = c("mean", "sd"),
counts = FALSE
) |>
select(group_name, group_level, strata_name, strata_level, variable_name, estimate_value) |>
print(n = Inf)
#> ℹ The following estimates will be computed:
#> • age: mean, sd
#> → Start summary of data, at 2024-12-12 15:48:31.513849
#>
#> ✔ Summary finished, at 2024-12-12 15:48:31.867669
#> # A tibble: 10 × 6
#> group_name group_level strata_name strata_level variable_name estimate_value
#> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 cohort_name sinusitis overall overall age 7.61212346597…
#> 2 cohort_name sinusitis overall overall age 7.79743654397…
#> 3 cohort_name sinusitis sex Female age 7.51276440554…
#> 4 cohort_name sinusitis sex Male age 7.71547799696…
#> 5 cohort_name sinusitis sex Female age 7.45793686970…
#> 6 cohort_name sinusitis sex Male age 8.13712697581…
#> 7 cohort_name sinusitis pharyngiti… 0 age 4.95620875824…
#> 8 cohort_name sinusitis pharyngiti… 1 age 11.9442270058…
#> 9 cohort_name sinusitis pharyngiti… 0 age 5.61220818358…
#> 10 cohort_name sinusitis pharyngiti… 1 age 8.85279666294…We can add or remove number subjects and records (if a person identifier is found) counts with the counts parameter:
x |>
summariseResult(
variables = "age",
estimates = c("mean", "sd"),
counts = TRUE
) |>
select(strata_name, strata_level, variable_name, estimate_value) |>
print(n = Inf)
#> ℹ The following estimates will be computed:
#> • age: mean, sd
#> → Start summary of data, at 2024-12-12 15:48:32.182613
#>
#> ✔ Summary finished, at 2024-12-12 15:48:32.263015
#> # A tibble: 4 × 4
#> strata_name strata_level variable_name estimate_value
#> <chr> <chr> <chr> <chr>
#> 1 overall overall number records 2689
#> 2 overall overall number subjects 2689
#> 3 overall overall age 7.61212346597248
#> 4 overall overall age 7.79743654397839If you want to specify different groups of estimates per different groups of variables you can use lists:
x |>
summariseResult(
strata = "pharyngitis_before",
includeOverallStrata = FALSE,
variables = list(c("age", "prior_observation"), "sex"),
estimates = list(c("mean", "sd"), c("count", "percentage")),
counts = FALSE
) |>
select(strata_name, strata_level, variable_name, estimate_value) |>
print(n = Inf)
#> ℹ The following estimates will be computed:
#> • age: mean, sd
#> • prior_observation: mean, sd
#> • sex: count, percentage
#> → Start summary of data, at 2024-12-12 15:48:32.575989
#>
#> ✔ Summary finished, at 2024-12-12 15:48:32.717959
#> # A tibble: 16 × 4
#> strata_name strata_level variable_name estimate_value
#> <chr> <chr> <chr> <chr>
#> 1 pharyngitis_before 0 age 4.95620875824835
#> 2 pharyngitis_before 1 age 11.9442270058708
#> 3 pharyngitis_before 0 age 5.61220818358422
#> 4 pharyngitis_before 1 age 8.85279666294611
#> 5 pharyngitis_before 0 sex 832
#> 6 pharyngitis_before 1 sex 539
#> 7 pharyngitis_before 0 sex 835
#> 8 pharyngitis_before 1 sex 483
#> 9 pharyngitis_before 0 sex 49.9100179964007
#> 10 pharyngitis_before 1 sex 52.7397260273973
#> 11 pharyngitis_before 0 sex 50.0899820035993
#> 12 pharyngitis_before 1 sex 47.2602739726027
#> 13 pharyngitis_before 0 prior_observation 1986.83443311338
#> 14 pharyngitis_before 1 prior_observation 4542.85812133072
#> 15 pharyngitis_before 0 prior_observation 2053.24325390978
#> 16 pharyngitis_before 1 prior_observation 3228.06460521219An example of a complete analysis would be:
drugs <- settings(cdm$drugs)$cohort_name
x |>
addCohortName() |>
summariseResult(
group = "cohort_name",
includeOverallGroup = FALSE,
strata = list("pharyngitis_before"),
includeOverallStrata = TRUE,
variables = list(
c(
"age", "prior_observation", "future_observation", paste0("prior_", drugs),
paste0("future_", drugs), "days_to_next_visit"
),
c("sex", "pharyngitis_before"),
c("first_visit", "cohort_start_date", "cohort_end_date")
),
estimates = list(
c("median", "q25", "q75"),
c("count", "percentage"),
c("median", "q25", "q75", "min", "max")
),
counts = TRUE
) |>
select(group_name, group_level, strata_name, strata_level, variable_name, estimate_value)
#> ℹ The following estimates will be computed:
#> • age: median, q25, q75
#> • prior_observation: median, q25, q75
#> • future_observation: median, q25, q75
#> • prior_1191_aspirin: median, q25, q75
#> • prior_7052_morphine: median, q25, q75
#> • prior_7804_oxycodone: median, q25, q75
#> • future_1191_aspirin: median, q25, q75
#> • future_7052_morphine: median, q25, q75
#> • future_7804_oxycodone: median, q25, q75
#> • days_to_next_visit: median, q25, q75
#> • sex: count, percentage
#> • pharyngitis_before: count, percentage
#> • first_visit: median, q25, q75, min, max
#> • cohort_start_date: median, q25, q75, min, max
#> • cohort_end_date: median, q25, q75, min, max
#> ! Table is collected to memory as not all requested estimates are supported on
#> the database side
#> → Start summary of data, at 2024-12-12 15:48:33.126055
#>
#> ✔ Summary finished, at 2024-12-12 15:48:33.208131
#> # A tibble: 159 × 6
#> group_name group_level strata_name strata_level variable_name estimate_value
#> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 cohort_name sinusitis overall overall number recor… 2689
#> 2 cohort_name sinusitis overall overall number subje… 2689
#> 3 cohort_name sinusitis overall overall cohort_start… 1968-05-06
#> 4 cohort_name sinusitis overall overall cohort_start… 1956-07-05
#> 5 cohort_name sinusitis overall overall cohort_start… 1978-09-04
#> 6 cohort_name sinusitis overall overall cohort_start… 1908-10-30
#> 7 cohort_name sinusitis overall overall cohort_start… 2018-02-13
#> 8 cohort_name sinusitis overall overall cohort_end_d… 2018-12-14
#> 9 cohort_name sinusitis overall overall cohort_end_d… 2018-08-02
#> 10 cohort_name sinusitis overall overall cohort_end_d… 2019-04-06
#> # ℹ 149 more rows