vignettes/pivot-concept-ancestor.Rmd
pivot-concept-ancestor.Rmd
conn <- connectAthena()Pivoting the Concept Ancestor table can provide a rich array representing the class hierarchy an OMOP Concept may have.
To exemplify, a test Drug concept is retrieved. Using more than 1 concept can lead to messy results since the classifications between randomly acquired concepts can be vastly different.
test_data <-
get_test_drug_concepts(conn = conn,
limit = 1)
#> [2021-05-26 16:10:14] Overriding cache... Querying Athena...
#> [2021-05-26 16:10:14] ✓ Open connection
#> [2021-05-26 16:10:14] ✓ JDBC connection
#> [2021-05-26 16:10:14] SQL: SELECT * FROM omop_vocabulary.concept c WHERE c.invalid_reason IS NULL AND c.vocabulary_id IN ('RxNorm', 'RxNorm Extension') AND c.standard_concept <> 'C' AND c.domain_id = 'Drug' ORDER BY RANDOM() LIMIT 1
#> [2021-05-26 16:10:14] Querying...
#> [2021-05-26 16:10:17] Querying...complete
#> [2021-05-26 16:10:17] ✓ Returned data has more than 0 rows
#> [2021-05-26 16:10:17] Caching resultset...
test_data
#> # A tibble: 1 x 10
#> concept_id concept_name domain_id vocabulary_id concept_class_id
#> <int> <chr> <chr> <chr> <chr>
#> 1 41249471 Medroxyprogesterone 150 M… Drug RxNorm Exten… Branded Drug
#> # … with 5 more variables: standard_concept <chr>, concept_code <chr>,
#> # valid_start_date <date>, valid_end_date <date>, invalid_reason <chr>The ancestors of this concept are derived.
test_data_ancestors <-
join_for_ancestors(data = test_data,
descendant_id_column = "concept_id",
conn = conn)
#> [2021-05-26 16:10:18] Target column: concept_id
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: DROP TABLE IF EXISTS patelm9.V20210526161018;
#> [2021-05-26 16:10:18] Sending...
#> [2021-05-26 16:10:18] Sending...complete
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] ✓ Data 'data' has more than 0 rows
#> [2021-05-26 16:10:18] Writing patelm9.V20210526161018...
#> [2021-05-26 16:10:18] Writing patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: SELECT a.*, b.ancestor_concept_id,b.min_levels_of_separation,b.max_levels_of_separation FROM patelm9.V20210526161018 a LEFT JOIN omop_vocabulary.concept_ancestor b ON a.concept_id = b.descendant_concept_id
#> [2021-05-26 16:10:18] Querying...
#> [2021-05-26 16:10:18] Querying...complete
#> [2021-05-26 16:10:18] ✓ Returned data has more than 0 rows
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: DROP TABLE IF EXISTS patelm9.V20210526161018;
#> [2021-05-26 16:10:18] Sending...
#> [2021-05-26 16:10:18] Sending...complete
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] Target column: ancestor_concept_id
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: DROP TABLE IF EXISTS patelm9.V20210526161018;
#> [2021-05-26 16:10:18] Sending...
#> [2021-05-26 16:10:18] Sending...complete
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] ✓ Data 'data' has more than 0 rows
#> [2021-05-26 16:10:18] Writing patelm9.V20210526161018...
#> [2021-05-26 16:10:18] Writing patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: SELECT a.*, b.* FROM patelm9.V20210526161018 a LEFT JOIN omop_vocabulary.concept b ON a.ancestor_concept_id = b.concept_id WHERE b.invalid_reason IS NULL
#> [2021-05-26 16:10:18] Querying...
#> [2021-05-26 16:10:18] Querying...complete
#> [2021-05-26 16:10:18] ✓ Returned data has more than 0 rows
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: DROP TABLE IF EXISTS patelm9.V20210526161018;
#> [2021-05-26 16:10:18] Sending...
#> [2021-05-26 16:10:18] Sending...complete
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...complete
test_data_ancestors
#> concept_id
#> 1 41249471
#> 2 41249471
#> 3 41249471
#> 4 41249471
#> 5 41249471
#> 6 41249471
#> 7 41249471
#> 8 41249471
#> 9 41249471
#> 10 41249471
#> 11 41249471
#> 12 41249471
#> 13 41249471
#> 14 41249471
#> 15 41249471
#> 16 41249471
#> 17 41249471
#> 18 41249471
#> 19 41249471
#> 20 41249471
#> 21 41249471
#> 22 41249471
#> 23 41249471
#> 24 41249471
#> 25 41249471
#> 26 41249471
#> concept_name domain_id
#> 1 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 2 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 3 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 4 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 5 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 6 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 7 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 8 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 9 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 10 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 11 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 12 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 13 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 14 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 15 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 16 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 17 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 18 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 19 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 20 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 21 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 22 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 23 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 24 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 25 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 26 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> vocabulary_id concept_class_id standard_concept concept_code
#> 1 RxNorm Extension Branded Drug S OMOP2447433
#> 2 RxNorm Extension Branded Drug S OMOP2447433
#> 3 RxNorm Extension Branded Drug S OMOP2447433
#> 4 RxNorm Extension Branded Drug S OMOP2447433
#> 5 RxNorm Extension Branded Drug S OMOP2447433
#> 6 RxNorm Extension Branded Drug S OMOP2447433
#> 7 RxNorm Extension Branded Drug S OMOP2447433
#> 8 RxNorm Extension Branded Drug S OMOP2447433
#> 9 RxNorm Extension Branded Drug S OMOP2447433
#> 10 RxNorm Extension Branded Drug S OMOP2447433
#> 11 RxNorm Extension Branded Drug S OMOP2447433
#> 12 RxNorm Extension Branded Drug S OMOP2447433
#> 13 RxNorm Extension Branded Drug S OMOP2447433
#> 14 RxNorm Extension Branded Drug S OMOP2447433
#> 15 RxNorm Extension Branded Drug S OMOP2447433
#> 16 RxNorm Extension Branded Drug S OMOP2447433
#> 17 RxNorm Extension Branded Drug S OMOP2447433
#> 18 RxNorm Extension Branded Drug S OMOP2447433
#> 19 RxNorm Extension Branded Drug S OMOP2447433
#> 20 RxNorm Extension Branded Drug S OMOP2447433
#> 21 RxNorm Extension Branded Drug S OMOP2447433
#> 22 RxNorm Extension Branded Drug S OMOP2447433
#> 23 RxNorm Extension Branded Drug S OMOP2447433
#> 24 RxNorm Extension Branded Drug S OMOP2447433
#> 25 RxNorm Extension Branded Drug S OMOP2447433
#> 26 RxNorm Extension Branded Drug S OMOP2447433
#> valid_start_date valid_end_date invalid_reason min_levels_of_separation
#> 1 2017-08-24 2099-12-31 <NA> 1
#> 2 2017-08-24 2099-12-31 <NA> 2
#> 3 2017-08-24 2099-12-31 <NA> 6
#> 4 2017-08-24 2099-12-31 <NA> 6
#> 5 2017-08-24 2099-12-31 <NA> 5
#> 6 2017-08-24 2099-12-31 <NA> 4
#> 7 2017-08-24 2099-12-31 <NA> 3
#> 8 2017-08-24 2099-12-31 <NA> 2
#> 9 2017-08-24 2099-12-31 <NA> 4
#> 10 2017-08-24 2099-12-31 <NA> 3
#> 11 2017-08-24 2099-12-31 <NA> 2
#> 12 2017-08-24 2099-12-31 <NA> 5
#> 13 2017-08-24 2099-12-31 <NA> 4
#> 14 2017-08-24 2099-12-31 <NA> 3
#> 15 2017-08-24 2099-12-31 <NA> 2
#> 16 2017-08-24 2099-12-31 <NA> 3
#> 17 2017-08-24 2099-12-31 <NA> 4
#> 18 2017-08-24 2099-12-31 <NA> 2
#> 19 2017-08-24 2099-12-31 <NA> 1
#> 20 2017-08-24 2099-12-31 <NA> 1
#> 21 2017-08-24 2099-12-31 <NA> 1
#> 22 2017-08-24 2099-12-31 <NA> 1
#> 23 2017-08-24 2099-12-31 <NA> 1
#> 24 2017-08-24 2099-12-31 <NA> 1
#> 25 2017-08-24 2099-12-31 <NA> 0
#> 26 2017-08-24 2099-12-31 <NA> 0
#> max_levels_of_separation ancestor_concept_id
#> 1 1 589944
#> 2 3 1500211
#> 3 7 21601386
#> 4 7 21602359
#> 5 6 21602471
#> 6 5 21602472
#> 7 4 21602496
#> 8 3 21602502
#> 9 5 21602537
#> 10 4 21602538
#> 11 3 21602540
#> 12 6 21603812
#> 13 5 21603813
#> 14 4 21603819
#> 15 3 21603821
#> 16 4 35807205
#> 17 5 35807466
#> 18 3 36217210
#> 19 2 36226749
#> 20 1 40141899
#> 21 1 40224804
#> 22 1 40224808
#> 23 1 41140960
#> 24 1 41206916
#> 25 0 41249471
#> 26 0 42902588
#> ancestor_concept_name
#> 1 Medroxyprogesterone 150 MG/ML [Depo Provera Grk]
#> 2 medroxyprogesterone
#> 3 ANTINEOPLASTIC AND IMMUNOMODULATING AGENTS
#> 4 GENITO URINARY SYSTEM AND SEX HORMONES
#> 5 SEX HORMONES AND MODULATORS OF THE GENITAL SYSTEM
#> 6 HORMONAL CONTRACEPTIVES FOR SYSTEMIC USE
#> 7 Progestogens
#> 8 medroxyprogesterone; parenteral
#> 9 PROGESTOGENS
#> 10 Pregnen (4) derivatives
#> 11 medroxyprogesterone; systemic
#> 12 ENDOCRINE THERAPY
#> 13 HORMONES AND RELATED AGENTS
#> 14 Progestogens
#> 15 medroxyprogesterone; systemic
#> 16 Endocrine therapeutic
#> 17 Antineoplastics by class effect
#> 18 Injectable Product
#> 19 medroxyprogesterone Injectable Product
#> 20 medroxyprogesterone Prefilled Syringe
#> 21 medroxyprogesterone acetate 150 MG/ML
#> 22 medroxyprogesterone acetate 160 MG/ML
#> 23 Medroxyprogesterone 160 MG/ML [Depo Provera Grk]
#> 24 Medroxyprogesterone Prefilled Syringe [Depo Provera Grk]
#> 25 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 26 medroxyprogesterone acetate 150 MG/ML Prefilled Syringe
#> ancestor_domain_id ancestor_vocabulary_id ancestor_concept_class_id
#> 1 Drug RxNorm Extension Branded Drug Comp
#> 2 Drug RxNorm Ingredient
#> 3 Drug ATC ATC 1st
#> 4 Drug ATC ATC 1st
#> 5 Drug ATC ATC 2nd
#> 6 Drug ATC ATC 3rd
#> 7 Drug ATC ATC 4th
#> 8 Drug ATC ATC 5th
#> 9 Drug ATC ATC 3rd
#> 10 Drug ATC ATC 4th
#> 11 Drug ATC ATC 5th
#> 12 Drug ATC ATC 2nd
#> 13 Drug ATC ATC 3rd
#> 14 Drug ATC ATC 4th
#> 15 Drug ATC ATC 5th
#> 16 Drug HemOnc Component Class
#> 17 Drug HemOnc Component Class
#> 18 Drug RxNorm Dose Form Group
#> 19 Drug RxNorm Clinical Dose Group
#> 20 Drug RxNorm Clinical Drug Form
#> 21 Drug RxNorm Clinical Drug Comp
#> 22 Drug RxNorm Clinical Drug Comp
#> 23 Drug RxNorm Extension Branded Drug Comp
#> 24 Drug RxNorm Extension Branded Drug Form
#> 25 Drug RxNorm Extension Branded Drug
#> 26 Drug RxNorm Clinical Drug
#> ancestor_standard_concept ancestor_concept_code ancestor_valid_start_date
#> 1 S OMOP3137713 2018-08-02
#> 2 S 6691 1970-01-01
#> 3 C L 1970-01-01
#> 4 C G 1970-01-01
#> 5 C G03 1970-01-01
#> 6 C G03A 1970-01-01
#> 7 C G03AC 1970-01-01
#> 8 C G03AC06 1970-01-01
#> 9 C G03D 1970-01-01
#> 10 C G03DA 1970-01-01
#> 11 C G03DA02 1970-01-01
#> 12 C L02 1970-01-01
#> 13 C L02A 1970-01-01
#> 14 C L02AB 1970-01-01
#> 15 C L02AB02 1970-01-01
#> 16 C 44970 2019-05-27
#> 17 C 46112 2019-05-27
#> 18 C 1151126 2016-08-01
#> 19 C 1159192 2016-08-01
#> 20 S 727610 2007-09-02
#> 21 S 1000125 2010-09-05
#> 22 S 1000155 2010-09-05
#> 23 S OMOP2338922 2017-08-24
#> 24 S OMOP2404878 2017-08-24
#> 25 S OMOP2447433 2017-08-24
#> 26 S 1359701 2012-12-03
#> ancestor_valid_end_date ancestor_invalid_reason
#> 1 2099-12-31 <NA>
#> 2 2099-12-31 <NA>
#> 3 2099-12-31 <NA>
#> 4 2099-12-31 <NA>
#> 5 2099-12-31 <NA>
#> 6 2099-12-31 <NA>
#> 7 2099-12-31 <NA>
#> 8 2099-12-31 <NA>
#> 9 2099-12-31 <NA>
#> 10 2099-12-31 <NA>
#> 11 2099-12-31 <NA>
#> 12 2099-12-31 <NA>
#> 13 2099-12-31 <NA>
#> 14 2099-12-31 <NA>
#> 15 2099-12-31 <NA>
#> 16 2099-12-31 <NA>
#> 17 2099-12-31 <NA>
#> 18 2099-12-31 <NA>
#> 19 2099-12-31 <NA>
#> 20 2099-12-31 <NA>
#> 21 2099-12-31 <NA>
#> 22 2099-12-31 <NA>
#> 23 2099-12-31 <NA>
#> 24 2099-12-31 <NA>
#> 25 2099-12-31 <NA>
#> 26 2099-12-31 <NA>The descendants are also derived.
test_data_descendants <-
join_for_descendants(
data = test_data,
ancestor_id_column = "concept_id",
conn = conn
)
#> [2021-05-26 16:10:18] Target column: concept_id
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: DROP TABLE IF EXISTS patelm9.V20210526161018;
#> [2021-05-26 16:10:18] Sending...
#> [2021-05-26 16:10:18] Sending...complete
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] ✓ Data 'data' has more than 0 rows
#> [2021-05-26 16:10:18] Writing patelm9.V20210526161018...
#> [2021-05-26 16:10:18] Writing patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: SELECT a.*, b.descendant_concept_id,b.min_levels_of_separation,b.max_levels_of_separation FROM patelm9.V20210526161018 a LEFT JOIN omop_vocabulary.concept_ancestor b ON a.concept_id = b.ancestor_concept_id
#> [2021-05-26 16:10:18] Querying...
#> [2021-05-26 16:10:18] Querying...complete
#> [2021-05-26 16:10:18] ✓ Returned data has more than 0 rows
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: DROP TABLE IF EXISTS patelm9.V20210526161018;
#> [2021-05-26 16:10:18] Sending...
#> [2021-05-26 16:10:18] Sending...complete
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] Target column: descendant_concept_id
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: DROP TABLE IF EXISTS patelm9.V20210526161018;
#> [2021-05-26 16:10:18] Sending...
#> [2021-05-26 16:10:18] Sending...complete
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] ✓ Data 'data' has more than 0 rows
#> [2021-05-26 16:10:18] Writing patelm9.V20210526161018...
#> [2021-05-26 16:10:18] Writing patelm9.V20210526161018...complete
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: SELECT a.*, b.* FROM patelm9.V20210526161018 a LEFT JOIN omop_vocabulary.concept b ON a.descendant_concept_id = b.concept_id WHERE b.invalid_reason IS NULL
#> [2021-05-26 16:10:18] Querying...
#> [2021-05-26 16:10:18] Querying...complete
#> [2021-05-26 16:10:18] ✓ Returned data has more than 0 rows
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...
#> [2021-05-26 16:10:18] ✓ Open connection
#> [2021-05-26 16:10:18] ✓ JDBC connection
#> [2021-05-26 16:10:18] SQL: DROP TABLE IF EXISTS patelm9.V20210526161018;
#> [2021-05-26 16:10:18] Sending...
#> [2021-05-26 16:10:18] Sending...complete
#> [2021-05-26 16:10:18] Dropping patelm9.V20210526161018...complete
test_data_descendants
#> concept_id concept_name
#> 1 41249471 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 2 41249471 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 3 41249471 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 4 41249471 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 5 41249471 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> domain_id vocabulary_id concept_class_id standard_concept concept_code
#> 1 Drug RxNorm Extension Branded Drug S OMOP2447433
#> 2 Drug RxNorm Extension Branded Drug S OMOP2447433
#> 3 Drug RxNorm Extension Branded Drug S OMOP2447433
#> 4 Drug RxNorm Extension Branded Drug S OMOP2447433
#> 5 Drug RxNorm Extension Branded Drug S OMOP2447433
#> valid_start_date valid_end_date invalid_reason min_levels_of_separation
#> 1 2017-08-24 2099-12-31 <NA> 1
#> 2 2017-08-24 2099-12-31 <NA> 0
#> 3 2017-08-24 2099-12-31 <NA> 1
#> 4 2017-08-24 2099-12-31 <NA> 0
#> 5 2017-08-24 2099-12-31 <NA> 1
#> max_levels_of_separation descendant_concept_id
#> 1 1 41335848
#> 2 0 41249471
#> 3 1 40937171
#> 4 0 41335849
#> 5 2 41336108
#> descendant_concept_name
#> 1 1 ML Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Box of 1
#> 2 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 3 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Box of 1
#> 4 1 ML Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 5 1 ML Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Box of 1 by Gerke
#> descendant_domain_id descendant_vocabulary_id descendant_concept_class_id
#> 1 Drug RxNorm Extension Quant Branded Box
#> 2 Drug RxNorm Extension Branded Drug
#> 3 Drug RxNorm Extension Branded Drug Box
#> 4 Drug RxNorm Extension Quant Branded Drug
#> 5 Drug RxNorm Extension Marketed Product
#> descendant_standard_concept descendant_concept_code
#> 1 S OMOP2533810
#> 2 S OMOP2447433
#> 3 S OMOP2135133
#> 4 S OMOP2533811
#> 5 S OMOP2534070
#> descendant_valid_start_date descendant_valid_end_date
#> 1 2017-08-24 2099-12-31
#> 2 2017-08-24 2099-12-31
#> 3 2017-08-24 2099-12-31
#> 4 2017-08-24 2099-12-31
#> 5 2017-08-24 2099-12-31
#> descendant_invalid_reason
#> 1 <NA>
#> 2 <NA>
#> 3 <NA>
#> 4 <NA>
#> 5 <NA>The construct a full concept lineage, the ancestors and descendants are harmonized. Harmonization is achieved by:
ancestor_ and descendant_ conceptsrelative_ while maintaining the original identity in a separate column
test_data_ancestors2 <-
test_data_ancestors %>%
rename_all(str_replace_all, "ancestor_", "relative_") %>%
mutate(relative_type = "ancestor") %>%
arrange(desc(min_levels_of_separation))
test_data_descendants2 <-
test_data_descendants %>%
rename_all(str_replace_all, "descendant_", "relative_") %>%
mutate(relative_type = "descendant") %>%
arrange(min_levels_of_separation)
test_data_relatives <-
bind_rows(test_data_ancestors2,
test_data_descendants2)
test_data_relatives
#> concept_id
#> 1 41249471
#> 2 41249471
#> 3 41249471
#> 4 41249471
#> 5 41249471
#> 6 41249471
#> 7 41249471
#> 8 41249471
#> 9 41249471
#> 10 41249471
#> 11 41249471
#> 12 41249471
#> 13 41249471
#> 14 41249471
#> 15 41249471
#> 16 41249471
#> 17 41249471
#> 18 41249471
#> 19 41249471
#> 20 41249471
#> 21 41249471
#> 22 41249471
#> 23 41249471
#> 24 41249471
#> 25 41249471
#> 26 41249471
#> 27 41249471
#> 28 41249471
#> 29 41249471
#> 30 41249471
#> 31 41249471
#> concept_name domain_id
#> 1 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 2 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 3 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 4 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 5 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 6 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 7 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 8 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 9 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 10 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 11 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 12 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 13 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 14 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 15 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 16 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 17 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 18 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 19 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 20 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 21 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 22 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 23 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 24 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 25 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 26 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 27 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 28 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 29 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 30 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> 31 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Drug
#> vocabulary_id concept_class_id standard_concept concept_code
#> 1 RxNorm Extension Branded Drug S OMOP2447433
#> 2 RxNorm Extension Branded Drug S OMOP2447433
#> 3 RxNorm Extension Branded Drug S OMOP2447433
#> 4 RxNorm Extension Branded Drug S OMOP2447433
#> 5 RxNorm Extension Branded Drug S OMOP2447433
#> 6 RxNorm Extension Branded Drug S OMOP2447433
#> 7 RxNorm Extension Branded Drug S OMOP2447433
#> 8 RxNorm Extension Branded Drug S OMOP2447433
#> 9 RxNorm Extension Branded Drug S OMOP2447433
#> 10 RxNorm Extension Branded Drug S OMOP2447433
#> 11 RxNorm Extension Branded Drug S OMOP2447433
#> 12 RxNorm Extension Branded Drug S OMOP2447433
#> 13 RxNorm Extension Branded Drug S OMOP2447433
#> 14 RxNorm Extension Branded Drug S OMOP2447433
#> 15 RxNorm Extension Branded Drug S OMOP2447433
#> 16 RxNorm Extension Branded Drug S OMOP2447433
#> 17 RxNorm Extension Branded Drug S OMOP2447433
#> 18 RxNorm Extension Branded Drug S OMOP2447433
#> 19 RxNorm Extension Branded Drug S OMOP2447433
#> 20 RxNorm Extension Branded Drug S OMOP2447433
#> 21 RxNorm Extension Branded Drug S OMOP2447433
#> 22 RxNorm Extension Branded Drug S OMOP2447433
#> 23 RxNorm Extension Branded Drug S OMOP2447433
#> 24 RxNorm Extension Branded Drug S OMOP2447433
#> 25 RxNorm Extension Branded Drug S OMOP2447433
#> 26 RxNorm Extension Branded Drug S OMOP2447433
#> 27 RxNorm Extension Branded Drug S OMOP2447433
#> 28 RxNorm Extension Branded Drug S OMOP2447433
#> 29 RxNorm Extension Branded Drug S OMOP2447433
#> 30 RxNorm Extension Branded Drug S OMOP2447433
#> 31 RxNorm Extension Branded Drug S OMOP2447433
#> valid_start_date valid_end_date invalid_reason min_levels_of_separation
#> 1 2017-08-24 2099-12-31 <NA> 6
#> 2 2017-08-24 2099-12-31 <NA> 6
#> 3 2017-08-24 2099-12-31 <NA> 5
#> 4 2017-08-24 2099-12-31 <NA> 5
#> 5 2017-08-24 2099-12-31 <NA> 4
#> 6 2017-08-24 2099-12-31 <NA> 4
#> 7 2017-08-24 2099-12-31 <NA> 4
#> 8 2017-08-24 2099-12-31 <NA> 4
#> 9 2017-08-24 2099-12-31 <NA> 3
#> 10 2017-08-24 2099-12-31 <NA> 3
#> 11 2017-08-24 2099-12-31 <NA> 3
#> 12 2017-08-24 2099-12-31 <NA> 3
#> 13 2017-08-24 2099-12-31 <NA> 2
#> 14 2017-08-24 2099-12-31 <NA> 2
#> 15 2017-08-24 2099-12-31 <NA> 2
#> 16 2017-08-24 2099-12-31 <NA> 2
#> 17 2017-08-24 2099-12-31 <NA> 2
#> 18 2017-08-24 2099-12-31 <NA> 1
#> 19 2017-08-24 2099-12-31 <NA> 1
#> 20 2017-08-24 2099-12-31 <NA> 1
#> 21 2017-08-24 2099-12-31 <NA> 1
#> 22 2017-08-24 2099-12-31 <NA> 1
#> 23 2017-08-24 2099-12-31 <NA> 1
#> 24 2017-08-24 2099-12-31 <NA> 1
#> 25 2017-08-24 2099-12-31 <NA> 0
#> 26 2017-08-24 2099-12-31 <NA> 0
#> 27 2017-08-24 2099-12-31 <NA> 0
#> 28 2017-08-24 2099-12-31 <NA> 0
#> 29 2017-08-24 2099-12-31 <NA> 1
#> 30 2017-08-24 2099-12-31 <NA> 1
#> 31 2017-08-24 2099-12-31 <NA> 1
#> max_levels_of_separation relative_concept_id
#> 1 7 21601386
#> 2 7 21602359
#> 3 6 21602471
#> 4 6 21603812
#> 5 5 21602472
#> 6 5 21602537
#> 7 5 21603813
#> 8 5 35807466
#> 9 4 21602496
#> 10 4 21602538
#> 11 4 21603819
#> 12 4 35807205
#> 13 3 1500211
#> 14 3 21602502
#> 15 3 21602540
#> 16 3 21603821
#> 17 3 36217210
#> 18 1 589944
#> 19 2 36226749
#> 20 1 40141899
#> 21 1 40224804
#> 22 1 40224808
#> 23 1 41140960
#> 24 1 41206916
#> 25 0 41249471
#> 26 0 42902588
#> 27 0 41249471
#> 28 0 41335849
#> 29 1 41335848
#> 30 1 40937171
#> 31 2 41336108
#> relative_concept_name
#> 1 ANTINEOPLASTIC AND IMMUNOMODULATING AGENTS
#> 2 GENITO URINARY SYSTEM AND SEX HORMONES
#> 3 SEX HORMONES AND MODULATORS OF THE GENITAL SYSTEM
#> 4 ENDOCRINE THERAPY
#> 5 HORMONAL CONTRACEPTIVES FOR SYSTEMIC USE
#> 6 PROGESTOGENS
#> 7 HORMONES AND RELATED AGENTS
#> 8 Antineoplastics by class effect
#> 9 Progestogens
#> 10 Pregnen (4) derivatives
#> 11 Progestogens
#> 12 Endocrine therapeutic
#> 13 medroxyprogesterone
#> 14 medroxyprogesterone; parenteral
#> 15 medroxyprogesterone; systemic
#> 16 medroxyprogesterone; systemic
#> 17 Injectable Product
#> 18 Medroxyprogesterone 150 MG/ML [Depo Provera Grk]
#> 19 medroxyprogesterone Injectable Product
#> 20 medroxyprogesterone Prefilled Syringe
#> 21 medroxyprogesterone acetate 150 MG/ML
#> 22 medroxyprogesterone acetate 160 MG/ML
#> 23 Medroxyprogesterone 160 MG/ML [Depo Provera Grk]
#> 24 Medroxyprogesterone Prefilled Syringe [Depo Provera Grk]
#> 25 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 26 medroxyprogesterone acetate 150 MG/ML Prefilled Syringe
#> 27 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 28 1 ML Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk]
#> 29 1 ML Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Box of 1
#> 30 Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Box of 1
#> 31 1 ML Medroxyprogesterone 150 MG/ML Prefilled Syringe [Depo Provera Grk] Box of 1 by Gerke
#> relative_domain_id relative_vocabulary_id relative_concept_class_id
#> 1 Drug ATC ATC 1st
#> 2 Drug ATC ATC 1st
#> 3 Drug ATC ATC 2nd
#> 4 Drug ATC ATC 2nd
#> 5 Drug ATC ATC 3rd
#> 6 Drug ATC ATC 3rd
#> 7 Drug ATC ATC 3rd
#> 8 Drug HemOnc Component Class
#> 9 Drug ATC ATC 4th
#> 10 Drug ATC ATC 4th
#> 11 Drug ATC ATC 4th
#> 12 Drug HemOnc Component Class
#> 13 Drug RxNorm Ingredient
#> 14 Drug ATC ATC 5th
#> 15 Drug ATC ATC 5th
#> 16 Drug ATC ATC 5th
#> 17 Drug RxNorm Dose Form Group
#> 18 Drug RxNorm Extension Branded Drug Comp
#> 19 Drug RxNorm Clinical Dose Group
#> 20 Drug RxNorm Clinical Drug Form
#> 21 Drug RxNorm Clinical Drug Comp
#> 22 Drug RxNorm Clinical Drug Comp
#> 23 Drug RxNorm Extension Branded Drug Comp
#> 24 Drug RxNorm Extension Branded Drug Form
#> 25 Drug RxNorm Extension Branded Drug
#> 26 Drug RxNorm Clinical Drug
#> 27 Drug RxNorm Extension Branded Drug
#> 28 Drug RxNorm Extension Quant Branded Drug
#> 29 Drug RxNorm Extension Quant Branded Box
#> 30 Drug RxNorm Extension Branded Drug Box
#> 31 Drug RxNorm Extension Marketed Product
#> relative_standard_concept relative_concept_code relative_valid_start_date
#> 1 C L 1970-01-01
#> 2 C G 1970-01-01
#> 3 C G03 1970-01-01
#> 4 C L02 1970-01-01
#> 5 C G03A 1970-01-01
#> 6 C G03D 1970-01-01
#> 7 C L02A 1970-01-01
#> 8 C 46112 2019-05-27
#> 9 C G03AC 1970-01-01
#> 10 C G03DA 1970-01-01
#> 11 C L02AB 1970-01-01
#> 12 C 44970 2019-05-27
#> 13 S 6691 1970-01-01
#> 14 C G03AC06 1970-01-01
#> 15 C G03DA02 1970-01-01
#> 16 C L02AB02 1970-01-01
#> 17 C 1151126 2016-08-01
#> 18 S OMOP3137713 2018-08-02
#> 19 C 1159192 2016-08-01
#> 20 S 727610 2007-09-02
#> 21 S 1000125 2010-09-05
#> 22 S 1000155 2010-09-05
#> 23 S OMOP2338922 2017-08-24
#> 24 S OMOP2404878 2017-08-24
#> 25 S OMOP2447433 2017-08-24
#> 26 S 1359701 2012-12-03
#> 27 S OMOP2447433 2017-08-24
#> 28 S OMOP2533811 2017-08-24
#> 29 S OMOP2533810 2017-08-24
#> 30 S OMOP2135133 2017-08-24
#> 31 S OMOP2534070 2017-08-24
#> relative_valid_end_date relative_invalid_reason relative_type
#> 1 2099-12-31 <NA> ancestor
#> 2 2099-12-31 <NA> ancestor
#> 3 2099-12-31 <NA> ancestor
#> 4 2099-12-31 <NA> ancestor
#> 5 2099-12-31 <NA> ancestor
#> 6 2099-12-31 <NA> ancestor
#> 7 2099-12-31 <NA> ancestor
#> 8 2099-12-31 <NA> ancestor
#> 9 2099-12-31 <NA> ancestor
#> 10 2099-12-31 <NA> ancestor
#> 11 2099-12-31 <NA> ancestor
#> 12 2099-12-31 <NA> ancestor
#> 13 2099-12-31 <NA> ancestor
#> 14 2099-12-31 <NA> ancestor
#> 15 2099-12-31 <NA> ancestor
#> 16 2099-12-31 <NA> ancestor
#> 17 2099-12-31 <NA> ancestor
#> 18 2099-12-31 <NA> ancestor
#> 19 2099-12-31 <NA> ancestor
#> 20 2099-12-31 <NA> ancestor
#> 21 2099-12-31 <NA> ancestor
#> 22 2099-12-31 <NA> ancestor
#> 23 2099-12-31 <NA> ancestor
#> 24 2099-12-31 <NA> ancestor
#> 25 2099-12-31 <NA> ancestor
#> 26 2099-12-31 <NA> ancestor
#> 27 2099-12-31 <NA> descendant
#> 28 2099-12-31 <NA> descendant
#> 29 2099-12-31 <NA> descendant
#> 30 2099-12-31 <NA> descendant
#> 31 2099-12-31 <NA> descendantThe preserve the ordering of the lineage, a levels_of_separation field of the factor datatype is created by combining a marker between relative_type and the min_levels_of_separation.
test_data_relatives2 <-
test_data_relatives %>%
unite(col = levels_of_separation,
relative_type,
min_levels_of_separation) %>%
mutate(levels_of_separation = factor(levels_of_separation,
levels = c("ancestor_3", "ancestor_2",
"ancestor_1", "ancestor_0",
"descendant_0", "descendant_1")))
test_data_relatives2$levels_of_separation
#> [1] <NA> <NA> <NA> <NA> <NA>
#> [6] <NA> <NA> <NA> ancestor_3 ancestor_3
#> [11] ancestor_3 ancestor_3 ancestor_2 ancestor_2 ancestor_2
#> [16] ancestor_2 ancestor_2 ancestor_1 ancestor_1 ancestor_1
#> [21] ancestor_1 ancestor_1 ancestor_1 ancestor_1 ancestor_0
#> [26] ancestor_0 descendant_0 descendant_0 descendant_1 descendant_1
#> [31] descendant_1
#> 6 Levels: ancestor_3 ancestor_2 ancestor_1 ancestor_0 ... descendant_1To properly pivot the resultset, the source concept and relative concepts require transformation into a single cell per concept. This is achieved by converting concepts into the strip format.
test_data_relatives3 <-
test_data_relatives2 %>%
merge_strip(into = "concept") %>%
merge_strip(into = "relative",
prefix = "relative_")
test_data_relatives3
#> # A tibble: 31 x 6
#> relative_id relative concept_id concept levels_of_separ… max_levels_of_s…
#> <int> <chr> <int> <chr> <fct> <int>
#> 1 21601386 [V] [C] 2… 41249471 [V] [S] … <NA> 7
#> 2 21602359 [V] [C] 2… 41249471 [V] [S] … <NA> 7
#> 3 21602471 [V] [C] 2… 41249471 [V] [S] … <NA> 6
#> 4 21603812 [V] [C] 2… 41249471 [V] [S] … <NA> 6
#> 5 21602472 [V] [C] 2… 41249471 [V] [S] … <NA> 5
#> 6 21602537 [V] [C] 2… 41249471 [V] [S] … <NA> 5
#> 7 21603813 [V] [C] 2… 41249471 [V] [S] … <NA> 5
#> 8 35807466 [V] [C] 3… 41249471 [V] [S] … <NA> 5
#> 9 21602496 [V] [C] 2… 41249471 [V] [S] … ancestor_3 4
#> 10 21602538 [V] [C] 2… 41249471 [V] [S] … ancestor_3 4
#> # … with 21 more rowsThe dataset can now be pivoted by the level of separation, hinging on the source concept.
output <-
test_data_relatives3 %>%
pivot_wider(id_col = concept,
names_from = levels_of_separation,
values_from = relative)
#> Warning: Values are not uniquely identified; output will contain list-cols.
#> * Use `values_fn = list` to suppress this warning.
#> * Use `values_fn = length` to identify where the duplicates arise
#> * Use `values_fn = {summary_fun}` to summarise duplicates
output
#> # A tibble: 1 x 8
#> concept `NA` ancestor_3 ancestor_2 ancestor_1 ancestor_0 descendant_0
#> <chr> <lis> <list> <list> <list> <list> <list>
#> 1 [V] [S] 412494… <chr… <chr [4]> <chr [5]> <chr [7]> <chr [2]> <chr [2]>
#> # … with 1 more variable: descendant_1 <list>Since concept has more than 1 relative at any given levels_of_separation, list-cols are returned in the output. To create a more readable output, an aggregate function on relative must be supplied to convert multiple relative values into a string of length 1.
output <-
test_data_relatives3 %>%
pivot_wider(id_col = concept,
names_from = levels_of_separation,
values_from = relative,
#Add aggregate
values_fn = list(relative = ~ paste(unique(.), collapse = "|")))
output
#> # A tibble: 1 x 8
#> concept `NA` ancestor_3 ancestor_2 ancestor_1 ancestor_0 descendant_0
#> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 [V] [S] … [V] [… [V] [C] 21… [V] [S] 15… [V] [S] 58… [V] [S] 41… [V] [S] 4124…
#> # … with 1 more variable: descendant_1 <chr>The resulting dataset contains a single row for the source concept provided with each relative cast across in the order from topmost ancestor to bottommost descendant aggregated into a pipe-separated string.