# Summarise concept id counts

# 2 Introduction

In this vignette, we will explore the OmopSketch functions designed to provide information about the number of counts of concepts in tables. Specifically, there are two key functions that facilitate this, `summariseConceptIdCounts()` and `tableConceptIdCounts()`. The former one creates a summary statistics results with the number of counts per each concept in the clinical table, and the latter one displays the result in a table.

## 2.1 Create a mock cdm

Let’s see an example of the previous functions. To start with, we will load essential packages and create a mock cdm using `mockOmopSketch()`.

    library(duckdb)
    library(OmopSketch)
    library(dplyr)

    cdm <- mockOmopSketch()

    cdm
    #>
    #> ── # OMOP CDM reference (duckdb) of mockOmopSketch ─────────────────────────────
    #> • omop tables: person, observation_period, cdm_source, concept, vocabulary,
    #> concept_relationship, concept_synonym, concept_ancestor, drug_strength,
    #> condition_occurrence, death, drug_exposure, measurement, observation,
    #> procedure_occurrence, visit_occurrence, device_exposure
    #> • cohort tables: -
    #> • achilles tables: -
    #> • other tables: -

# 3 Summarise concept id counts

We now use the `summariseConceptIdCounts()` function from the OmopSketch package to retrieve counts for each concept id and name, as well as for each source concept id and name, across the clinical tables.

    summariseConceptIdCounts(cdm, omopTableName = "drug_exposure") |>
      select(group_level, variable_name, variable_level, estimate_name, estimate_value, additional_name, additional_level) |>
      glimpse()
    #> Rows: 216
    #> Columns: 7
    #> $ group_level      <chr> "drug_exposure", "drug_exposure", "drug_exposure", "d…
    #> $ variable_name    <chr> "celecoxib 200 MG Oral Capsule [Celebrex]", "meningoc…
    #> $ variable_level   <chr> "1118088", "40213180", "40213296", "933724", "1102527…
    #> $ estimate_name    <chr> "count_records", "count_records", "count_records", "c…
    #> $ estimate_value   <chr> "100", "100", "100", "100", "100", "100", "100", "100…
    #> $ additional_name  <chr> "source_concept_id &&& source_concept_name", "source_…
    #> $ additional_level <chr> "0 &&& No matching concept", "0 &&& No matching conce…

By default, the function returns the number of records (`estimate_name == "count_records"`) for each concept_id. To include counts by person, you can set the `countBy` argument to `"person"` or to c`("record", "person")` to obtain both record and person counts.

    summariseConceptIdCounts(cdm,
      omopTableName = "drug_exposure",
      countBy = c("record", "person")
    ) |>
      select( variable_name, estimate_name, estimate_value)
    #> # A tibble: 432 × 3
    #>    variable_name        estimate_name  estimate_value
    #>    <chr>                <chr>          <chr>
    #>  1 Memantine            count_records  100
    #>  2 Memantine            count_subjects 66
    #>  3 zoster vaccine, live count_records  100
    #>  4 zoster vaccine, live count_subjects 64
    #>  5 Terfenadine          count_records  100
    #>  6 Terfenadine          count_subjects 65
    #>  7 fexofenadine         count_records  100
    #>  8 fexofenadine         count_subjects 60
    #>  9 Loratadine           count_records  100
    #> 10 Loratadine           count_subjects 60
    #> # ℹ 422 more rows

Further stratification can be applied using the `interval`, `sex`, and `ageGroup` arguments. The interval argument supports “overall” (no time stratification), “years”, “quarters”, or “months”.

    summariseConceptIdCounts(cdm,
      omopTableName = "condition_occurrence",
      countBy = "person",
      interval = "years",
      sex = TRUE,
      ageGroup = list("<=50" = c(0, 50), ">50" = c(51, Inf))
    ) |>
      select(group_level, strata_level, variable_name, estimate_name, additional_level) |>
      glimpse()
    #> Rows: 17,078
    #> Columns: 5
    #> $ group_level      <chr> "condition_occurrence", "condition_occurrence", "cond…
    #> $ strata_level     <chr> "overall", "overall", "overall", "overall", "overall"…
    #> $ variable_name    <chr> "Escherichia coli urinary tract infection", "Gastroin…
    #> $ estimate_name    <chr> "count_subjects", "count_subjects", "count_subjects",…
    #> $ additional_level <chr> "0 &&& No matching concept", "0 &&& No matching conce…

We can also filter the clinical table to a specific time window by setting the dateRange argument.

    summarisedResult <- summariseConceptIdCounts(cdm,
                                                 omopTableName = "condition_occurrence",
                                                 dateRange = as.Date(c("1990-01-01", "2010-01-01")))
    summarisedResult |>
      omopgenerics::settings()|>
      glimpse()
    #> Rows: 1
    #> Columns: 10
    #> $ result_id          <int> 1
    #> $ result_type        <chr> "summarise_concept_id_counts"
    #> $ package_name       <chr> "OmopSketch"
    #> $ package_version    <chr> "0.5.0"
    #> $ group              <chr> "omop_table"
    #> $ strata             <chr> ""
    #> $ additional         <chr> "source_concept_id &&& source_concept_name"
    #> $ min_cell_count     <chr> "0"
    #> $ study_period_end   <chr> "2010-01-01"
    #> $ study_period_start <chr> "1990-01-01"

Finally, you can summarise concept counts on a subset of records by specifying the `sample` argument.

    summariseConceptIdCounts(cdm,
                             omopTableName = "condition_occurrence",
                             sample = 50) |>
      select(group_level, variable_name, estimate_name) |>
      glimpse()
    #> Rows: 37
    #> Columns: 3
    #> $ group_level   <chr> "condition_occurrence", "condition_occurrence", "conditi…
    #> $ variable_name <chr> "Laceration of hand", "Acute bacterial sinusitis", "Frac…
    #> $ estimate_name <chr> "count_records", "count_records", "count_records", "coun…

## 3.1 Display the results

Finally, concept counts can be visualised using `tableConceptIdCounts()`. By default, it generates an interactive reactable table, but DT datatables are also supported.

    result <- summariseConceptIdCounts(cdm,
      omopTableName = "measurement",
      countBy = "record"
    )
    tableConceptIdCounts(result, type = "reactable")

    tableConceptIdCounts(result, type = "datatable")

The `display` argument in tableConceptIdCounts() controls which concept counts are shown. Available options include `display = "overall"`. It is the default option and it shows both standard and source concept counts.

    tableConceptIdCounts(result, display = "overall")

If `display = "standard"` the table shows only standard concept_id and concept_name counts.

    tableConceptIdCounts(result, display = "standard")

If `display = "source"` the table shows only source concept_id and concept_name counts.

    tableConceptIdCounts(result, display = "source")
    #> Warning: Values from `estimate_value` are not uniquely identified; output will contain
    #> list-cols.
    #> • Use `values_fn = list` to suppress this warning.
    #> • Use `values_fn = {summary_fun}` to summarise duplicates.
    #> • Use the following dplyr code to identify duplicates.
    #>   {data} |>
    #>   dplyr::summarise(n = dplyr::n(), .by = c(cdm_name, group_level,
    #>   source_concept_name, source_concept_id, result_id, group_name, estimate_type,
    #>   estimate_name)) |>
    #>   dplyr::filter(n > 1L)

If `display = "missing source"` the table shows only counts for concept ids that are missing a corresponding source concept id.

    tableConceptIdCounts(result, display = "missing source")

If `display = "missing standard"` the table shows only counts for source concept ids that are missing a mapped standard concept id.

    tableConceptIdCounts(result, display = "missing standard")
    #> Warning: `result` does not contain any `summarise_concept_id_counts` data.

## 3.2 Display the most frequent concepts

You can use the `tableTopConceptCounts()` function to display the most frequent concepts in a OMOP CDM table in formatted table. By default, the function returns a gt table, but you can also choose from other output formats, including flextable, datatable, and reactable.

    result <- summariseConceptIdCounts(cdm,
      omopTableName = "drug_exposure",
      countBy = "record"
    )
    tableTopConceptCounts(result, type = "gt")

Top | Cdm name
--- | ---
mockOmopSketch
drug_exposure
1 | Standard: Memantine (701322) Source: No matching concept (0) 100
2 | Standard: zoster vaccine, live (40213260) Source: No matching concept (0) 100
3 | Standard: Terfenadine (1150836) Source: No matching concept (0) 100
4 | Standard: fexofenadine (1153428) Source: No matching concept (0) 100
5 | Standard: Loratadine (1107830) Source: No matching concept (0) 100
6 | Standard: pneumococcal conjugate vaccine, 13 valent (40213198) Source: No matching concept (0) 100
7 | Standard: Acetaminophen (1125315) Source: No matching concept (0) 100
8 | Standard: {28 (Norethindrone 0.35 MG Oral Tablet) } Pack [Errin 28 Day] (19133679) Source: No matching concept (0) 100
9 | Standard: 12 HR Cefaclor 500 MG Extended Release Oral Tablet (19074841) Source: No matching concept (0) 100
10 | Standard: Albuterol (1154343) Source: No matching concept (0) 100

### 3.2.1 Customising the number of top concepts

By default, the function shows the top 10 concepts. You can change this using the `top` argument:

    tableTopConceptCounts(result, top = 5)

Top | Cdm name
--- | ---
mockOmopSketch
drug_exposure
1 | Standard: Memantine (701322) Source: No matching concept (0) 100
2 | Standard: zoster vaccine, live (40213260) Source: No matching concept (0) 100
3 | Standard: Terfenadine (1150836) Source: No matching concept (0) 100
4 | Standard: fexofenadine (1153428) Source: No matching concept (0) 100
5 | Standard: Loratadine (1107830) Source: No matching concept (0) 100

### 3.2.2 Choosing the count type

If your summary includes both record and person counts, you must specify which type to display using the `countBy` argument:

    result <- summariseConceptIdCounts(cdm,
      omopTableName = "drug_exposure",
      countBy = c("record", "person")
    )
    tableTopConceptCounts(result, countBy = "person")

Top | Cdm name
--- | ---
mockOmopSketch
drug_exposure
1 | Standard: Fexofenadine hydrochloride 30 MG Oral Tablet (40223821) Source: No matching concept (0) 74
2 | Standard: dienogest (19054876) Source: No matching concept (0) 72
3 | Standard: {28 (Norethindrone 0.35 MG Oral Tablet) } Pack [Errin 28 Day] (19133679) Source: No matching concept (0) 69
4 | Standard: Hydrocortisone 10 MG/ML Topical Cream (19008572) Source: No matching concept (0) 69
5 | Standard: 1 ML medroxyprogesterone acetate 150 MG/ML Injection (40224805) Source: No matching concept (0) 69
6 | Standard: Isoflurane (782043) Source: No matching concept (0) 68
7 | Standard: Etonogestrel 68 MG Drug Implant (1519937) Source: No matching concept (0) 68
8 | Standard: Amoxicillin 500 MG Oral Tablet (19073188) Source: No matching concept (0) 68
9 | Standard: Astemizole 10 MG Oral Tablet (1150771) Source: No matching concept (0) 68
10 | Standard: Acetaminophen 21.7 MG/ML / Dextromethorphan Hydrobromide 1 MG/ML / doxylamine succinate 0.417 MG/ML Oral Solution (40229134) Source: No matching concept (0) 68