def patient_counts_by_year_of_birth_and_gender(inspector, person_ids=None, return_columns=None):
"""
Returns patient counts stratified by year of birth and gender for the database or alternativily, for a supplied list of person_ids.
Parameters
----------
person_ids : list of int, optional
list of person_ids [int]. If None (default), get the gender distribution for all individuals in the person table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['gender_concept_id','gender','year_of_birth', 'count']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
PE09: Number of patients by gender, stratified by year of birth::
SELECT
gender_concept_id,
c.concept_name AS gender_name,
year_of_birth,
COUNT(p.person_id) AS num_persons
FROM
person p
INNER JOIN
concept c ON p.gender_concept_id = c.concept_id
GROUP BY
gender_concept_id,
c.concept_name,
year_of_birth
ORDER BY
concept_name,
year_of_birth;
"""
p = _alias(inspector.tables['person'], 'p')
c = _alias(inspector.tables['concept'], 'c')
columns = [c.c.concept_id, c.c.concept_name.label('gender'),p.c.year_of_birth,_func.count(p.c.year_of_birth).label('count')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
if not person_ids:
statement = _select(columns).\
where(c.c.concept_id == p.c.gender_concept_id).\
group_by(p.c.year_of_birth, c.c.concept_name).\
order_by(p.c.year_of_birth, c.c.concept_name)
else:
statement = _select(columns).\
where(_and_(\
p.c.person_id.in_(person_ids),\
c.c.concept_id == p.c.gender_concept_id)).\
group_by(p.c.year_of_birth, c.c.concept_name).\
order_by(p.c.year_of_birth, c.c.concept_name)
return inspector.execute(statement)
def patient_counts_by_zip_code(inspector, person_ids=None, return_columns=None):
"""
Returns patient counts grouped by zip code for the database or alternativily, for a supplied list of person_ids.
Parameters
----------
person_ids : list of int, optional
list of person_ids [int]. If None (default), get the gender distribution for all individuals in the person table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['state','zip_code', 'count']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
PE08: Number of patients grouped by zip code of residence::
SELECT
state,
NVL( zip, '9999999' ) AS zip,
count(\*) Num_Persons_count
FROM
person
LEFT OUTER JOIN
location
USING( location_id )
GROUP BY
state,
NVL( zip, '9999999' )
ORDER BY 1, 2;
"""
p = _alias(inspector.tables['person'], 'p')
l = _alias(inspector.tables['location'], 'l')
j = _join(p, l, p.c.location_id == l.c.location_id)
columns = [j.c.l_state,j.c.l_zip,_func.count(j.c.l_zip).label('count')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
if not person_ids:
statement = _select(columns).\
select_from(j).\
group_by(j.c.l_state,j.c.l_zip).\
order_by(j.c.l_state, j.c.l_zip)
else:
statement = _select(columns).\
select_from(j).\
where(\
j.c.p_person_id.in_(person_ids)).\
group_by(j.c.l_state, j.c.l_zip).\
order_by(j.c.l_state, j.c.l_zip)
return inspector.execute(statement)
def patient_counts_by_gender(inspector, person_ids=None, return_columns=None):
"""
Returns patient counts grouped by gender for the database or alternativily, for a supplied list of person_ids.
Parameters
----------
person_ids : list of int, optional
list of person_ids [int]. If None (default), get the gender distribution for all individuals in the person table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['gender_concept_id','gender','count']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
PE03: Number of patients grouped by gender::
SELECT
person.GENDER_CONCEPT_ID,
concept.CONCEPT_NAME AS gender_name,
COUNT(person.person_ID) AS num_persons_count
FROM
person
INNER JOIN
concept ON person.GENDER_CONCEPT_ID = concept.CONCEPT_ID
GROUP BY
person.GENDER_CONCEPT_ID, concept.CONCEPT_NAME;
"""
c = _alias(inspector.tables['concept'], 'c')
p = _alias(inspector.tables['person'], 'p')
columns = [p.c.gender_concept_id,c.c.concept_name.label('gender'),_func.count(p.c.gender_concept_id).label('count')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
if not person_ids:
statement = _select(columns).\
where(p.c.gender_concept_id == c.c.concept_id).\
group_by(p.c.gender_concept_id)
else:
statement = _select(columns).\
where(_and_(\
p.c.gender_concept_id == c.c.concept_id,\
p.c.person_id.in_(person_ids))).\
group_by(p.c.gender_concept_id)
return inspector.execute(statement)
def patient_counts_by_year_of_birth(inspector, person_ids=None, return_columns=None):
"""
Returns patient counts grouped by year of birth for the database or alternativily, for a supplied list of person_ids.
Parameters
----------
person_ids : list of int, optional
list of person_ids [int]. If None (default), get the gender distribution for all individuals in the person table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['year_of_birth', 'count']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
PE06: Number of patients grouped by year of birth::
SELECT
year_of_birth,
COUNT(person_id) AS Num_Persons_count
FROM
person
GROUP BY
year_of_birth
ORDER BY
year_of_birth;
"""
p = _alias(inspector.tables['person'], 'p')
columns = [p.c.year_of_birth,_func.count(p.c.year_of_birth).label('count')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
if not person_ids:
statement = _select(columns).\
group_by(p.c.year_of_birth).\
order_by(p.c.year_of_birth)
else:
statement = _select(columns).\
where(\
p.c.person_id.in_(person_ids)).\
group_by(p.c.year_of_birth).\
order_by(p.c.year_of_birth)
return inspector.execute(statement)
def anatomical_site_by_keyword(keyword, inspector, return_columns=None):
"""
Retrieves anitomical site concepts given a keyword. Results of this query are useful for `condition_concepts_occurring_at_anatomical_site_concept_id`
Parameters
----------
keyword : str
search string. ex 'Epiglottis'
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id', 'concept_name', 'concept_code', 'concept_class_id', 'standard_concept', 'vocabulary_id', 'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
See Also
--------
condition_concepts_occurring_at_anatomical_site_concept_id
Notes
-----
Original SQL
C10: Find an anatomical site by keyword::
SELECT
C.concept_id Anatomical_site_ID,
C.concept_name Anatomical_site_Name,
C.concept_code Anatomical_site_Code,
C.concept_class_id Anatomical_site_Class,
C.standard_concept Anatomical_standard_concept,
C.vocabulary_id Anatomical_site_Vocab_ID,
V.vocabulary_name Anatomical_site_Vocab_Name
FROM
concept C,
vocabulary V
WHERE
LOWER(C.concept_class_id) = 'body structure' AND
LOWER(C.concept_name) like '%epiglottis%' AND
C.vocabulary_id = V.vocabulary_id AND
sysdate BETWEEN C.valid_start_date AND C.valid_end_date;
"""
c = _alias(inspector.tables['concept'], 'c')
v = _alias(inspector.tables['vocabulary'], 'v')
concept_class_id = 'Body Structure'
columns = [c.c.concept_id, c.c.concept_name, c.c.concept_code, c.c.concept_class_id,\
c.c.standard_concept, c.c.vocabulary_id, v.c.vocabulary_name]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns)\
.where(_and_(\
c.c.concept_class_id == concept_class_id,\
_func.lower(c.c.concept_name).ilike('%{}%'.format(keyword.lower())),\
c.c.vocabulary_id == v.c.vocabulary_id))
return inspector.execute(statement)
def drug_concepts_for_ingredient_concept_id(concept_id, inspector,return_columns=None):
"""
Get all drugs that contain a given ingredient.
Parameters
----------
concept_id : int
concept_id corresponding to a drug ingredient
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['ingredient_concept_id', 'ingredient_name', \
'ingredient_concept_code', 'ingredient_concept_class_id', \
'drug_concept_id', 'drug_name','drug_concept_code', \
'drug_concept_class_id']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
D04: Find drugs by ingredient::
SELECT
A.concept_id Ingredient_concept_id,
A.concept_Name Ingredient_name,
A.concept_Code Ingredient_concept_code,
A.concept_Class_id Ingredient_concept_class,
D.concept_id Drug_concept_id,
D.concept_Name Drug_name,
D.concept_Code Drug_concept_code,
D.concept_Class_id Drug_concept_class
FROM
concept_ancestor CA,
concept A,
concept D
WHERE
CA.ancestor_concept_id = A.concept_id
AND CA.descendant_concept_id = D.concept_id
AND sysdate BETWEEN A.valid_start_date AND A.valid_end_date
AND sysdate BETWEEN D.valid_start_date AND D.valid_end_date
AND CA.ancestor_concept_id = 966991;
"""
a = _alias(inspector.tables['concept'],'a')
d = _alias(inspector.tables['concept'], 'd')
ca = _alias(inspector.tables['concept_ancestor'] ,'ca')
columns = [a.c.concept_id.label('ingredient_concept_id'), a.c.concept_name.label('ingredient_name'), \
a.c.concept_code.label('ingredient_concept_code'), a.c.concept_class_id.label('ingredient_concept_class_id'), \
d.c.concept_id.label('drug_concept_id'), d.c.concept_name.label('drug_name'),\
d.c.concept_code.label('drug_concept_code'), d.c.concept_class_id.label('drug_concept_class_id')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).where(_and_(ca.c.ancestor_concept_id==a.c.concept_id,\
ca.c.descendant_concept_id == d.c.concept_id, ca.c.ancestor_concept_id == concept_id))
return inspector.execute(statement)
def descendants_for_concept_id(concept_id, inspector, return_columns=None):
"""
Find all descendant concepts for a concept_id.
Parameters
----------
concept_id : int
concept_id of interest from the concept table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['descendant_concept_id', 'descendant_concept_name', 'descendant_concept_code', 'descendant_concept_class_id', 'vocabulary_id', 'min_levels_of_separation', 'max_levels_of_separation']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
G09: Find descendants for a given concept::
SELECT
C.concept_id as descendant_concept_id,
C.concept_name as descendant_concept_name,
C.concept_code as descendant_concept_code,
C.concept_class_id as descendant_concept_class_id,
C.vocabulary_id,
VA.vocabulary_name,
A.min_levels_of_separation,
A.max_levels_of_separation
FROM
concept_ancestor A,
concept C,
vocabulary VA
WHERE
A.ancestor_concept_id = C.concept_id AND
C.vocabulary_id = VA.vocabulary_id AND A.ancestor_concept_id<>A.descendant_concept_id AND A.descendant_concept_id = 192671 AND
sysdate BETWEEN valid_start_date AND valid_end_date
ORDER BY 5,7;
"""
a = _alias(inspector.tables['concept_ancestor'], 'a')
c = _alias(inspector.tables['concept'], 'c')
va = _alias(inspector.tables['vocabulary'], 'va')
columns = [c.c.concept_id.label('descendant_concept_id'), c.c.concept_name.label('descendant_concept_name'), c.c.concept_code.label('descendant_concept_code'), c.c.concept_class_id.label('descendant_concept_class_id'),\
c.c.vocabulary_id, va.c.vocabulary_name, a.c.min_levels_of_separation, \
a.c.max_levels_of_separation]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(_and_(\
a.c.descendant_concept_id == c.c.concept_id,\
c.c.vocabulary_id == va.c.vocabulary_id, \
a.c.ancestor_concept_id != a.c.descendant_concept_id, \
a.c.ancestor_concept_id == concept_id)). \
order_by(c.c.vocabulary_id, a.c.min_levels_of_separation)
return inspector.execute(statement)
def children_for_concept_id(concept_id, inspector, return_columns=None):
"""
Find all child concepts for a concept_id.
Parameters
----------
concept_id : int
concept_id of interest from the concept table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['child_concept_id','child_concept_name', 'child_concept_code', 'child_concept_class_id', 'child_concept_vocabulary_id', 'child_concept_vocab_name']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
G11: Find children for a given concept::
SELECT
D.concept_id Child_concept_id,
D.concept_name Child_concept_name,
D.concept_code Child_concept_code,
D.concept_class_id Child_concept_class_id,
D.vocabulary_id Child_concept_vocab_ID,
VS.vocabulary_name Child_concept_vocab_name
FROM
concept_ancestor CA,
concept D,
vocabulary VS
WHERE
CA.ancestor_concept_id = 192671 AND
CA.min_levels_of_separation = 1 AND
CA.descendant_concept_id = D.concept_id AND
D.vocabulary_id = VS.vocabulary_id AND
sysdate BETWEEN D.valid_start_date AND D.valid_end_date;
"""
ca = _alias(inspector.tables['concept_ancestor'], 'ca')
d = _alias(inspector.tables['concept'], 'd')
vs = _alias(inspector.tables['vocabulary'], 'vs')
levels_of_sep = 1
columns = [d.c.concept_id.label('child_concept_id'), d.c.concept_name.label('child_concept_name'), d.c.concept_code.label('child_concept_code'), d.c.concept_class_id.label('child_concept_class_id'),\
d.c.vocabulary_id.label('child_concept_vocabulary_id'), vs.c.vocabulary_name.label('child_concept_vocab_name')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(_and_(\
ca.c.ancestor_concept_id == concept_id,\
ca.c.min_levels_of_separation == levels_of_sep, \
ca.c.descendant_concept_id == d.c.concept_id,\
d.c.vocabulary_id == vs.c.vocabulary_id))
return inspector.execute(statement)
def ingredients_for_drug_concept_ids(concept_ids, inspector, return_columns=None):
"""
Get ingredients for brand or generic drug concept_ids.
Parameters
----------
concept_id : list of int
concept_ids corresponding to brand or generic drug concept_ids
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['drug_concept_id', 'drug_name', 'drug_concept_code', 'drug_concept_class', 'ingredient_concept_id', 'ingredient_name', 'ingredient_concept_code', 'ingredient_concept_class']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
D03: Find ingredients of a drug::
SELECT
D.Concept_Id drug_concept_id,
D.Concept_Name drug_name,
D.Concept_Code drug_concept_code,
D.Concept_Class_id drug_concept_class,
A.Concept_Id ingredient_concept_id,
A.Concept_Name ingredient_name,
A.Concept_Code ingredient_concept_code,
A.Concept_Class_id ingredient_concept_class
FROM
full_201706_omop_v5.concept_ancestor CA,
full_201706_omop_v5.concept A,
full_201706_omop_v5.concept D
WHERE
CA.descendant_concept_id = D.concept_id
AND CA.ancestor_concept_id = A.concept_id
AND LOWER(A.concept_class_id) = 'ingredient'
AND sysdate BETWEEN A.VALID_START_DATE AND A.VALID_END_DATE
AND sysdate BETWEEN D.VALID_START_DATE AND D.VALID_END_DATE
AND CA.descendant_concept_id IN (939355, 19102189, 19033566)
"""
a = _alias(inspector.tables['concept'],'a')
d = _alias(inspector.tables['concept'], 'd')
ca = _alias(inspector.tables['concept_ancestor'] ,'ca')
columns = [d.c.concept_id.label('drug_concept_id'), d.c.concept_id.label('drug_name'), d.c.concept_code.label('drug_concept_code'), d.c.concept_class_id.label('drug_concept_class'), a.c.concept_id.label('ingredient_concept_id'), a.c.concept_name.label('ingredient_name'), a.c.concept_code.label('ingredient_concept_code'), a.c.concept_class_id.label('ingredient_concept_class')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(_and_(\
ca.c.descendant_concept_id == d.c.concept_id,\
ca.c.ancestor_concept_id == a.c.concept_id,\
a.c.concept_class_id == 'Ingredient',\
ca.c.descendant_concept_id.in_(concept_ids)))
return inspector.execute(statement)
def concepts_for_concept_ids(concept_ids, inspector, return_columns=None):
"""
Returns concept information for a list of concept_ids
Parameters
----------
concept_ids : list of int
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id', 'concept_name', 'concept_code', 'concept_class_id', 'standard_concept', 'vocabulary_id', 'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
G01: Find concept by concept ID::
SELECT
C.concept_id,
C.concept_name,
C.concept_code,
C.concept_class_id,
C.standard_concept,
C.vocabulary_id,
V.vocabulary_name
FROM
concept C,
vocabulary V
WHERE
C.concept_id = 192671 AND
C.vocabulary_id = V.vocabulary_id AND
sysdate BETWEEN valid_start_date AND valid_end_date;
"""
concept = inspector.tables['concept']
vocabulary = inspector.tables['vocabulary']
columns = [
concept.concept_id, concept.concept_name, concept.concept_code,
concept.concept_class_id, concept.standard_concept,
concept.vocabulary_id, vocabulary.vocabulary_name
]
if return_columns:
col_names = ['concept_id', 'concept_name', 'concept_code', 'concept_class_id',\
'standard_concept', 'vocabulary_id', 'vocabulary_name']
filtered_col_names = list(
filter(lambda x: x in col_names, return_columns))
columns = [col for col in columns if col.key in filtered_col_names]
statement = _select(columns).where(
concept.concept_id.in_(list(concept_ids))).where(
concept.vocabulary_id == vocabulary.vocabulary_id)
return inspector.execute(statement)
def drug_classes_for_drug_concept_id(concept_id, inspector,return_columns=None):
"""
Returns drug classes for drug or ingredient concept_ids.
Parameters
----------
concept_id : int
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id','concept_name','concept_code','concept_class_id', 'vocabulary_id', 'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
D08: Find drug classes for a drug or ingredient::
SELECT
c1.concept_id Class_Concept_Id,
c1.concept_name Class_Name,
c1.concept_code Class_Code,
c1.concept_class_id Classification,
c1.vocabulary_id Class_vocabulary_id,
v1.vocabulary_name Class_vocabulary_name,
ca.min_levels_of_separation Levels_of_Separation
FROM
concept_ancestor ca,
concept c1,
vocabulary v1
WHERE
ca.ancestor_concept_id = c1.concept_id
AND c1.vocabulary_id IN ('NDFRT', 'ETC', 'ATC', 'VA Class')
AND c1.concept_class_id IN ('ATC','VA Class','Mechanism of Action','Chemical Structure','ETC','Physiologic Effect')
AND c1.vocabulary_id = v1.vocabulary_id
AND ca.descendant_concept_id = 1545999
AND sysdate BETWEEN c1.valid_start_date AND c1.valid_end_date;
"""
c = _alias(inspector.tables['concept'],'c')
v = _alias(inspector.tables['vocabulary'], 'v')
ca = _alias(inspector.tables['concept_ancestor'] ,'ca')
columns = [c.c.concept_id, c.c.concept_name, c.c.concept_code, c.c.concept_class_id, v.c.vocabulary_name, ca.c.min_levels_of_separation]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(_and_(\
ca.c.ancestor_concept_id == c.c.concept_id,\
c.c.vocabulary_id.in_(['ATC','VA Class','Mechanism of Action','Chemical Structure','ETC','Physiologic Effect']),\
c.c.vocabulary_id == v.c.vocabulary_id,\
ca.c.descendant_concept_id == concept_id))
return inspector.execute(statement)
def counts_by_years_of_coverage(inspector):
"""
Returns counts of payer coverage based on continuous coverage (payer_plan_period_start_date - payer_plan_period_end_date)365.25.
Note this method may count patients with more than one insurance plan multiple times. Ex pt with Medicare Parts A, B, and D.
Parameters
----------
inspector : inspectomop.inspector.Inspector
Returns
-------
df : pandas.DataFrame
Notes
-----
Original SQL
PP01: Continuous years with patient counts::
SELECT
floor((p.payer_plan_period_end_date - p.payer_plan_period_start_date)/365) AS year_int,
count(1) AS num_patients
FROM
payer_plan_period p
GROUP BY
floor((p.payer_plan_period_end_date - p.payer_plan_period_start_date)/365)
ORDER BY 1;
"""
p = _alias(inspector.tables['payer_plan_period'], 'p')
columns = [
p.c.payer_plan_period_end_date, p.c.payer_plan_period_start_date
]
statement = _select(columns)
results = inspector.execute(statement).as_pandas()
results['payer_plan_period_end_date'] = _pd.to_datetime(
results['payer_plan_period_end_date'])
results['payer_plan_period_start_date'] = _pd.to_datetime(
results['payer_plan_period_start_date'])
results['coverage_years'] = results[
'payer_plan_period_end_date'] - results['payer_plan_period_start_date']
results['coverage_years'] = [
_np.floor(cov.days / 365.25) for cov in results['coverage_years']
]
results = results[['coverage_years', 'payer_plan_period_start_date'
]].groupby('coverage_years', as_index=False).count()
results.rename(mapper={'payer_plan_period_start_date': 'count'},
inplace=True,
axis=1)
return results
def patient_counts_by_care_site_type(inspector, return_columns=None):
"""
Returns pateints counts by facility type.
Parameters
----------
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['place_of_service', 'place_of_service_concept_id', 'patient_count']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
CS02: Patient count per care site place of service::
SELECT
cs.place_of_service_concept_id,
count(1) num_patients
FROM
care_site cs,
person p
WHERE
p.care_site_id = cs.care_site_id
GROUP BY
cs.place_of_service_concept_id
ORDER BY 1;
"""
c = _alias(inspector.tables['concept'], 'c')
cs = _alias(inspector.tables['care_site'], 'cs')
p = _alias(inspector.tables['person'], 'p')
columns = [
c.c.concept_name.label('place_of_service'),
cs.c.place_of_service_concept_id,
_func.count(cs.c.place_of_service_concept_id).label('patient_count')
]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(_and_(\
p.c.care_site_id == cs.c.care_site_id)).\
group_by(cs.c.place_of_service_concept_id)
return inspector.execute(statement)
def facility_counts_by_type(inspector, return_columns=None):
"""
Returns facility counts by type in the OMOP CDM i.e. # Inpatient Hospitals, Offices, etc.
Parameters
----------
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['place_of_service', 'place_of_service_concept_id', 'facility_count']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
Notes
-----
Original SQL
CS01: Care site place of service counts::
SELECT
cs.place_of_service_concept_id,
count(1) places_of_service_count
FROM care_site cs
GROUP BY
cs.place_of_service_concept_id
ORDER BY 1;
"""
c = _alias(inspector.tables['concept'], 'c')
cs = _alias(inspector.tables['care_site'], 'cs')
columns = [
c.c.concept_name.label('place_of_service'),
cs.c.place_of_service_concept_id,
_func.count(cs.c.place_of_service_concept_id).label('facility_count')
]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(c.c.concept_id == cs.c.place_of_service_concept_id).\
group_by(cs.c.place_of_service_concept_id)
return inspector.execute(statement)
def ingredient_concept_ids_for_ingredient_names(ingredient_names, inspector,return_columns=None):
"""
Get concept_ids for a list of ingredients.
Parameters
----------
ingredient_names : list of str
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['ingredient_name', 'concept_id']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL::
SELECT
concept_id,
concept_name
FROM
concept,
WHERE
vocabulary_id = 'RxNorm'
AND concept_class_id = 'Ingredient'
AND lower(concept_name) IN ('ingredient_name_1')
"""
concept = inspector.tables['concept']
vocab_id = 'RxNorm'
concept_class_id = 'Ingredient'
columns = [concept.concept_name.label('ingredient_name'),concept.concept_id]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(_and_(\
concept.vocabulary_id == vocab_id,\
concept.concept_class_id == concept_class_id,\
_func.lower(concept.concept_name).in_(map(str.lower,ingredient_names))))
return inspector.execute(statement)
def patient_distribution_by_plan_type(inspector):
"""
Returns counts of payer coverage by plan type.
Parameters
----------
inspector : inspectomop.inspector.Inspector
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
PP02: Patient distribution by plan type::
SELECT
t.plan_source_value,
t.pat_cnt AS num_patients,
100.00\*t.pat_cnt/ (sum(t.pat_cnt) over()) perc_of_total_count
FROM (
SELECT
p.plan_source_value,
count(1) AS pat_cnt
FROM
payer_plan_period p
GROUP BY
p.plan_source_value
) t
ORDER BY
t.plan_source_value;
"""
p = _alias(inspector.tables['payer_plan_period'], 'p')
columns = [
p.c.plan_source_value,
_func.count(p.c.plan_source_value).label('count')
]
statement = _select(columns).group_by(p.c.plan_source_value)
return inspector.execute(statement)
def conditions_caused_by_pathogen_or_causative_agent_concept_id(
concept_id, inspector, return_columns=None):
"""
Retreives all conditions caused by a pathogen or other causative agent concept_id.
Parameters
----------
concept_id : int
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['condition_concept_id', 'condition_name',\
'condition_concept_code', 'condition_concept_class_id',\
'condition_vocab_id', 'condition_vocab_name',\
'causative_agent_concept_id', 'causative_agent_concept_name',\
'causative_agent_concept_code', 'causative_agent_concept_class_id',\
'causative_agent_vocab_id', 'causative_agent_vocab_name']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
See Also
--------
disease_causing_agents_for_keyword, pathogen_concept_for_keyword
Notes
-----
Original SQL
C09: Find all SNOMED-CT condition concepts that can be caused by a given pathogen or causative agent::
SELECT
A.concept_Id Condition_ID,
A.concept_Name Condition_name,
A.concept_Code Condition_code,
A.concept_Class_id Condition_class,
A.vocabulary_id Condition_vocab_ID,
VA.vocabulary_name Condition_vocab_name,
D.concept_Id Causative_agent_ID,
D.concept_Name Causative_agent_Name,
D.concept_Code Causative_agent_Code,
D.concept_Class_id Causative_agent_Class,
D.vocabulary_id Causative_agent_vocab_ID,
VS.vocabulary_name Causative_agent_vocab_name
FROM
concept_relationship CR,
concept A,
concept D,
vocabulary VA,
vocabulary VS
WHERE
CR.relationship_ID = 'Has causative agent' AND
CR.concept_id_1 = A.concept_id AND
A.vocabulary_id = VA.vocabulary_id AND
CR.concept_id_2 = D.concept_id AND
D.concept_id = 4248851 AND
D.vocabulary_id = VS.vocabulary_id AND
sysdate BETWEEN CR.valid_start_date AND CR.valid_end_date;
"""
a = _alias(inspector.tables['concept'], 'a')
d = _alias(inspector.tables['concept'], 'd')
cr = _alias(inspector.tables['concept_relationship'], 'cr')
va = _alias(inspector.tables['vocabulary'], 'va')
vs = _alias(inspector.tables['vocabulary'], 'vs')
relationship_id = 'Has causative agent'
columns = [a.c.concept_id.label('condition_concept_id'), a.c.concept_name.label('condition_name'),\
a.c.concept_code.label('condition_concept_code'), a.c.concept_class_id.label('condition_concept_class_id'),\
a.c.vocabulary_id.label('condition_vocab_id'), va.c.vocabulary_name.label('condition_vocab_name'),\
d.c.concept_id.label('causative_agent_concept_id'), d.c.concept_name.label('causative_agent_concept_name'),\
d.c.concept_code.label('causative_agent_concept_code'), d.c.concept_class_id.label('causative_agent_concept_class_id'),\
d.c.vocabulary_id.label('causative_agent_vocab_id'), vs.c.vocabulary_name.label('causative_agent_vocab_name')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns)\
.where(_and_(\
cr.c.relationship_id == relationship_id,\
cr.c.concept_id_1 == a.c.concept_id,\
a.c.vocabulary_id == va.c.vocabulary_id,\
cr.c.concept_id_2 == d.c.concept_id,\
d.c.concept_id == concept_id,\
d.c.vocabulary_id == vs.c.vocabulary_id))
return inspector.execute(statement)
def indications_for_drug_concept_id(concept_id, inspector,return_columns=None):
"""
Find all indications for a drug given a concept_id. Returns matches from NDFRT, FDB, and corresponding SNOMED conditions.
*Note: The results set should be filtered by 'c_domain_id' == 'Condition'
Parameters
----------
concept_id : int
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['c_concept_id', 'c_concept_name',\
'c_domain_id', 'min_levels_of_separation',\
'an_concept_id', 'an_concept_name',\
'an_vocab', 'de_concept_id',\
de_concept_name', 'de_vocab']
Returns
-------
results : inspectomop.results.Results
Notes
-----
SQL (see 2nd example for actual implimentation)
D13: Find indications as condition concepts for a drug::
select
a.min_levels_of_separation as a_min,
an.concept_id as an_id,
an.concept_name as an_name,
an.vocabulary_id as an_vocab,
an.domain_id as an_domain,
an.concept_class_id as an_class,
de.concept_id as de_id,
de.concept_name as de_name,
de.vocabulary_id as de_vocab,
de.domain_id as de_domain,
de.concept_class_id as de_class
from
concept an
join
concept_ancestor a on a.ancestor_concept_id=an.concept_id
join
concept de on de.concept_id=a.descendant_concept_id
where
an.concept_class_id in ('Ind / CI', 'Indication') -- One is for NDFRT, the other for FDB Indications
and de.vocabulary_id in ('RxNorm', 'RxNorm Extension') -- You don't need that if you join directly with DRUG_EXPOSURE
and lower(an.concept_name) like '%diabetes%'
To tie directly to SNOMED concepts, this query is used
select
c.concept_id as c_id,
c.concept_name as c_name,
c.vocabulary_id as c_vocab,
c.domain_id as c_domain,
c.concept_class_id as c_class, -- Condition
de.concept_id as de_id,
de.concept_name as de_name,
de.vocabulary_id as de_vocab,
de.domain_id as de_domain,
de.concept_class_id as de_class -- Drug
from
concept an -- Indications
join
concept_ancestor a on a.ancestor_concept_id=an.concept_id -- connect to
join
concept de on de.concept_id=a.descendant_concept_id -- ...drug
join
concept_relationship r on r.concept_id_1=an.concept_id -- connect to
join
concept c on c.concept_id=r.concept_id_2 and c.domain_id='Condition' -- Snomed Conditions
where
an.concept_class_id in ('Ind / CI', 'Indication')
and de.vocabulary_id in ('RxNorm', 'RxNorm Extension')
and lower(c.concept_name) like '%diabet%'
"""
c = _alias(inspector.tables['concept'],'c')
de = _alias(inspector.tables['concept'],'de')
an = _alias(inspector.tables['concept'],'an')
a = _alias(inspector.tables['concept_ancestor'],'a')
r = _alias(inspector.tables['concept_relationship'],'r')
j1 = _join(a, an, a.c.ancestor_concept_id == an.c.concept_id)
j2 = _join(de, j1, de.c.concept_id == a.c.descendant_concept_id)
j3 = _join(j2, r, r.c.concept_id_1 == an.c.concept_id)
domain_id = 'Condition'
j4 = _join(j3, c, c.c.concept_id == r.c.concept_id_2)
concept_class_ids = ['Ind / CI', 'Indication']
vocab_ids= ['RxNorm', 'RxNorm Extension']
columns = [c.c.concept_id.label('c_concept_id'), c.c.concept_name.label('c_concept_name'),\
c.c.domain_id.label('c_domain_id'),a.c.min_levels_of_separation,\
an.c.concept_id.label('an_concept_id'), an.c.concept_name.label('an_concept_name'),\
an.c.vocabulary_id.label('an_vocab'), de.c.concept_id.label('de_concept_id'),\
de.c.concept_name.label('de_concept_name'),de.c.vocabulary_id.label('de_vocab')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
select_from(j4).where(_and_(\
de.c.concept_id == concept_id,\
an.c.concept_class_id.in_(concept_class_ids),\
de.c.vocabulary_id.in_(vocab_ids)
))
return inspector.execute(statement)
def condition_concepts_occurring_at_anatomical_site_concept_id(
concept_id, inspector, return_columns=None):
"""
Retrieves condition concepts that occur at a given anatomical site. Input concept_id should be a concept of
class 'Body Structure'
Parameters
----------
concept_id : int
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['cond_concept_id', 'cond_concept_name',\
'cond_concept_code', 'cond_concept_class_id',\
'cond_vocab_id', 'cond_vocab_name',\
'anat_site_concept_id','anat_site_concept_name',\
'anat_site_concept_code', 'anat_site_concept_class_id',\
'anat_site_vocab_id', 'anat_site_vocab_name']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
See Also
--------
anatomical_site_by_keyword
Notes
-----
Original SQL
C11: Find all SNOMED-CT condition concepts that are occurring at an anatomical site::
SELECT
A.concept_Id Condition_ID,
A.concept_Name Condition_name,
A.concept_Code Condition_code,
A.concept_Class_id Condition_class,
A.vocabulary_id Condition_vocab_ID,
VA.vocabulary_name Condition_vocab_name,
D.concept_Id Anatomical_site_ID,
D.concept_Name Anatomical_site_Name,
D.concept_Code Anatomical_site_Code,
D.concept_Class_id Anatomical_site_Class,
D.vocabulary_id Anatomical_site_vocab_ID,
VS.vocabulary_name Anatomical_site_vocab_name
FROM
concept_relationship CR,
concept A,
concept D,
vocabulary VA,
vocabulary VS
WHERE
CR.relationship_ID = 'Has finding site' AND
CR.concept_id_1 = A.concept_id AND
A.vocabulary_id = VA.vocabulary_id AND
CR.concept_id_2 = D.concept_id AND
D.concept_id = 4103720 --input AND
D.vocabulary_id = VS.vocabulary_id AND
sysdate BETWEEN CR.valid_start_date AND CR.valid_end_date;
"""
a = _alias(inspector.tables['concept'], 'a')
d = _alias(inspector.tables['concept'], 'd')
cr = _alias(inspector.tables['concept_relationship'], 'cr')
va = _alias(inspector.tables['vocabulary'], 'va')
vs = _alias(inspector.tables['vocabulary'], 'vs')
relationship_id = 'Has finding site'
columns = [a.c.concept_id.label('cond_concept_id'), a.c.concept_name.label('cond_concept_name'),\
a.c.concept_code.label('cond_concept_code'), a.c.concept_class_id.label('cond_concept_class_id'),\
a.c.vocabulary_id.label('cond_vocab_id'), va.c.vocabulary_name.label('cond_vocab_name'),\
d.c.concept_id.label('anat_site_concept_id'),d.c.concept_name.label('anat_site_concept_name'),\
d.c.concept_code.label('anat_site_concept_code'), d.c.concept_class_id.label('anat_site_concept_class_id'),\
d.c.vocabulary_id.label('anat_site_vocab_id'), vs.c.vocabulary_name.label('anat_site_vocab_name')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns)\
.where(_and_(\
cr.c.relationship_id == relationship_id,\
cr.c.concept_id_1 == a.c.concept_id,\
a.c.vocabulary_id == va.c.vocabulary_id,\
cr.c.concept_id_2 == d.c.concept_id,\
d.c.concept_id == concept_id,\
d.c.vocabulary_id == vs.c.vocabulary_id))
return inspector.execute(statement)
def place_of_service_counts_for_condition_concept_id(condition_concept_id,
inspector,
return_columns=None):
"""
Provides counts of conditions stratified by place_of_service (Office, Inpatient Hospital, etc.)
Parameters
----------
condition_concept_id : int
concept_id from the conditions table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['condition_concept_id', 'condition_concept_id,'place_of_service_concept_id', 'place_of_service', 'place_freq']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
Notes
-----
SQL Modified from:
CO04: Count In what place of service where condition diagnoses::
SELECT
c.concept_id AS condition_concept_id,
c.concept_name AS condition_concept_id,
cs.place_of_service_concept_id AS place_of_service_concept_id,
c_place.concept_name AS place_of_service,
count(cs.place_of_service_concept_id) AS place_freq
FROM
main.concept AS c, main.concept AS c_place,
(SELECT
co.condition_concept_id AS condition_concept_id,
co.visit_occurrence_id AS s1_visit_id
FROM
main.condition_occurrence AS co
WHERE
co.condition_concept_id = :condition_concept_id_1
AND co.visit_occurrence_id IS NOT NULL)
JOIN
main.visit_occurrence AS vo
ON
s1_visit_id = vo.visit_occurrence_id
JOIN
main.care_site AS cs
ON
vo.care_site_id = cs.care_site_id
WHERE
c_place.concept_id = cs.place_of_service_concept_id
AND c.concept_id = :concept_id_1
GROUP BY c.concept_name
"""
co = _alias(inspector.tables['condition_occurrence'], 'co')
cs = _alias(inspector.tables['care_site'], 'cs')
vo = _alias(inspector.tables['visit_occurrence'], 'vo')
c_place = _alias(inspector.tables['concept'], 'c_place')
c = _alias(inspector.tables['concept'], 'c')
s1 = _select([
co.c.condition_concept_id,
co.c.visit_occurrence_id.label('s1_visit_id')
]).where(
_and_(co.c.condition_concept_id == condition_concept_id,
co.c.visit_occurrence_id != None))
j1 = _join(s1, vo, s1.c.s1_visit_id == vo.c.visit_occurrence_id)
j2 = _join(j1, cs, j1.c.vo_care_site_id == cs.c.care_site_id)
columns = [
c.c.concept_id.label('condition_concept_id'),
c.c.concept_name.label('condition_concept_id'),
j2.c.cs_place_of_service_concept_id.label(
'place_of_service_concept_id'),
c_place.c.concept_name.label('place_of_service'),
_func.count(j2.c.cs_place_of_service_concept_id).label('place_freq')
]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
select_from(j2).\
where(_and_(c_place.c.concept_id == j2.c.cs_place_of_service_concept_id, c.c.concept_id == condition_concept_id)).group_by(c.c.concept_name)
return inspector.execute(statement)
def observation_concepts_for_keyword(keyword, inspector,return_columns=None):
"""
Search for LOINC and UCUM concepts by keyword.
Parameters
----------
keyword : str
e.x. 'LDL'
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id','concept_name','concept_code', 'concept_class_id', 'vocabulary_id',
'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
O1: Find a Observation from a keyword::
SELECT
T.Entity_Concept_Id,
T.Entity_Name,
T.Entity_Code,
T.Entity_Type,
T.Entity_concept_class_id,
T.Entity_vocabulary_id,
T.Entity_vocabulary_name
FROM (
SELECT
C.concept_id Entity_Concept_Id,
C.concept_name Entity_Name,
C.concept_code Entity_Code,
'Concept' Entity_Type,
C.concept_class_id Entity_concept_class_id,
C.vocabulary_id Entity_vocabulary_id,
V.vocabulary_name Entity_vocabulary_name,
C.valid_start_date,
C.valid_end_date
FROM
concept C,
vocabulary V
WHERE
C.vocabulary_id IN ('LOINC', 'UCUM') AND
C.concept_class_id IS NOT NULL AND
C.standard_concept = 'S' AND
C.vocabulary_id = V.vocabulary_id
) T
WHERE
REGEXP_INSTR(LOWER(REPLACE(REPLACE(T.Entity_Name, ' ', ''), '-', '')),
LOWER(REPLACE(REPLACE('LDL' , ' ', ''), '-', ''))) > 0 AND
sysdate BETWEEN T.valid_start_date AND T.valid_end_date
"""
c = _alias(inspector.tables['concept'], 'c')
v = _alias(inspector.tables['vocabulary'], 'v')
vocab_ids = ['LOINC', 'UCUM']
standard_concept = 'S'
s1 = _select([c.c.concept_id,c.c.concept_name,c.c.concept_code,\
c.c.concept_class_id, c.c.vocabulary_id, v.c.vocabulary_name]).\
where(_and_(\
c.c.vocabulary_id.in_(vocab_ids),\
c.c.concept_class_id != None,\
c.c.standard_concept == standard_concept,\
c.c.vocabulary_id == v.c.vocabulary_id))
columns = [s1.c.concept_id,s1.c.concept_name,s1.c.concept_code, s1.c.concept_class_id, s1.c.vocabulary_id,s1.c.vocabulary_name]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).select_from(s1).\
where(_func.lower(s1.c.concept_name).ilike('%{}%'.format(keyword.lower())))
return inspector.execute(statement)
def related_concepts_for_concept_id(concept_id,
inspector,
return_columns=None):
"""
Find all concepts related to a concept_id.
Parameters
----------
concept_id : int
concept_id of interest from the concept table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['relationship_polarity','relationship_id', 'relationship_name', 'concept_id', 'concept_name', 'concept_code', 'concept_class_id', 'vocabulary_id', 'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
G07: Find concepts that have a relationship with a given concept::
SELECT
'Relates to' relationship_polarity,
CR.relationship_ID,
RT.relationship_name,
D.concept_Id concept_id,
D.concept_Name concept_name,
D.concept_Code concept_code,
D.concept_class_id concept_class_id,
D.vocabulary_id concept_vocab_ID,
VS.vocabulary_name concept_vocab_name
FROM
concept_relationship CR,
concept A,
concept D,
vocabulary VA,
vocabulary VS,
relationship RT
WHERE
CR.concept_id_1 = A.concept_id AND
A.vocabulary_id = VA.vocabulary_id AND
CR.concept_id_2 = D.concept_id AND
D.vocabulary_id = VS.vocabulary_id AND
CR.relationship_id = RT.relationship_ID AND
A.concept_id = 192671 AND
sysdate BETWEEN CR.valid_start_date AND CR.valid_end_date
UNION ALL
SELECT
'Is related by' relationship_polarity,
CR.relationship_ID,
RT.relationship_name,
A.concept_Id concept_id,
A.concept_name concept_name,
A.concept_code concept_code,
A.concept_class_id concept_class_id,
A.vocabulary_id concept_vocab_ID,
VA.Vocabulary_Name concept_vocab_name
FROM
concept_relationship CR,
concept A,
concept D,
vocabulary VA,
vocabulary VS,
relationship RT
WHERE
CR.concept_id_1 = A.concept_id AND
A.vocabulary_id = VA.vocabulary_id AND
CR.concept_id_2 = D.concept_id AND
D.vocabulary_id = VS.vocabulary_id AND
CR.relationship_id = RT.relationship_ID AND
D.concept_id = 192671 AND
sysdate BETWEEN CR.valid_start_date AND CR.valid_end_date;
"""
cr = _alias(inspector.tables['concept_relationship'], 'cr')
a = _alias(inspector.tables['concept'], 'ca')
d = _alias(inspector.tables['concept'], 'cd')
va = _alias(inspector.tables['vocabulary'], 'va')
vs = _alias(inspector.tables['vocabulary'], 'vs')
rt = _alias(inspector.tables['relationship'], 'rt')
columns = [cr.c.relationship_id, rt.c.relationship_name, \
d.c.concept_id, d.c.concept_name, \
d.c.concept_code, d.c.concept_class_id,\
d.c.vocabulary_id, vs.c.vocabulary_name]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
relates_to = _select([_literal_column("\'Relates to\'").label('relationship_polarity')] + columns).where(_and_(cr.c.concept_id_1 == a.c.concept_id, \
a.c.vocabulary_id == va.c.vocabulary_id, cr.c.concept_id_2 == d.c.concept_id, \
d.c.vocabulary_id == vs.c.vocabulary_id, cr.c.relationship_id == rt.c.relationship_id, \
a.c.concept_id == concept_id))
related_by = _select([_literal_column("\'Is related by\'").label('relationship_polarity')] + columns).where(_and_(cr.c.concept_id_1 == a.c.concept_id, \
a.c.vocabulary_id == va.c.vocabulary_id, cr.c.concept_id_2 == d.c.concept_id, \
d.c.vocabulary_id == vs.c.vocabulary_id, cr.c.relationship_id == rt.c.relationship_id, \
d.c.concept_id == concept_id))
statement = _union_all(relates_to, related_by)
return inspector.execute(statement)
def condition_concepts_for_keyword(keyword, inspector, return_columns=None):
"""
Retrieves standard concepts for a condition/keyword.
Parameters
----------
keyword : str
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id', 'concept_name', 'concept_code', 'concept_class_id', 'vocabulary_id']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
Notes
-----
Original SQL
C02: Find a condition by keyword::
SELECT
T.Entity_Concept_Id,
T.Entity_Name,
T.Entity_Code,
T.Entity_Type,
T.Entity_concept_class,
T.Entity_vocabulary_id,
T.Entity_vocabulary_name
FROM (
SELECT
C.concept_id Entity_Concept_Id,
C.concept_name Entity_Name,
C.CONCEPT_CODE Entity_Code,
'Concept' Entity_Type,
C.concept_class_id Entity_concept_class,
C.vocabulary_id Entity_vocabulary_id,
V.vocabulary_name Entity_vocabulary_name,
NULL Entity_Mapping_Type,
C.valid_start_date,
C.valid_end_date
FROM concept C
JOIN vocabulary V ON C.vocabulary_id = V.vocabulary_id
LEFT JOIN concept_synonym S ON C.concept_id = S.concept_id
WHERE
(C.vocabulary_id IN ('SNOMED', 'MedDRA') OR LOWER(C.concept_class_id) = 'clinical finding' ) AND
C.concept_class_id IS NOT NULL AND
( LOWER(C.concept_name) like '%myocardial infarction%' OR
LOWER(S.concept_synonym_name) like '%myocardial infarction%' )
) T
WHERE sysdate BETWEEN valid_start_date AND valid_end_date
ORDER BY 6,2;
"""
c = inspector.tables['concept']
cs = inspector.tables['concept_synonym']
v = inspector.tables['vocabulary']
vocab_ids = ['SNOMED', 'MedDRA']
concept_class_id = 'clinical_finding'
columns = [
c.concept_id, c.concept_name, c.concept_code, c.concept_class_id,
v.vocabulary_id
]
j = _join(c, v, c.vocabulary_id == v.vocabulary_id)
j2 = _join(j, cs, c.concept_id == cs.concept_id, isouter=True)
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns)\
.select_from(j2)\
.where(_and_(\
_or_(c.vocabulary_id.in_(vocab_ids), _func.lower(c.concept_class_id)==concept_class_id),\
c.concept_class_id != None,\
_or_(_func.lower(c.concept_name).ilike('%{}%'.format(keyword.lower())),\
_func.lower(cs.concept_synonym_name).ilike('%{}%'.format(keyword.lower())))))\
.distinct()
return inspector.execute(statement)
def procedure_concepts_for_keyword(keyword, inspector, return_columns=None):
"""
Search for all concepts in the procedure domain (includes SNOMED-CT procedures, ICD9 procedures, CPT procedures and HCPCS procedures)
for a given keyword.
Parameters
----------
keyword : str
e.x. 'artery bypass'
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id', 'concept_name', 'concept_code', 'concept_class_id', 'vocabulary_id', 'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
P02: Find a procedure from a keyword::
SELECT C.concept_id Entity_Concept_Id,
C.concept_name Entity_Name,
C.concept_code Entity_Code,
'Concept' Entity_Type,
C.concept_class_id Entity_concept_class_id,
C.vocabulary_id Entity_vocabulary_id,
V.vocabulary_name Entity_vocabulary_name
FROM
concept C
INNER JOIN
vocabulary V ON C.vocabulary_id = V.vocabulary_id
LEFT OUTER JOIN
concept_synonym S ON C.concept_id = S.concept_id
WHERE
(C.vocabulary_id IN ('ICD9Proc', 'CPT4', 'HCPCS') OR LOWER(C.concept_class_id) = 'procedure') AND
C.concept_class_id IS NOT NULL AND
C.standard_concept = 'S' AND
(REGEXP_INSTR(LOWER(C.concept_name), LOWER('artery bypass')) > 0 OR
REGEXP_INSTR(LOWER(S.concept_synonym_name), LOWER('artery bypass')) > 0) AND
sysdate BETWEEN C.valid_start_date AND C.valid_end_date;
"""
c = _alias(inspector.tables['concept'], 'c')
v = _alias(inspector.tables['vocabulary'], 'v')
s = _alias(inspector.tables['concept_synonym'])
vocab_ids = ['SNOMED', 'ICD9Proc', 'ICD10PCS', 'CPT4', 'HCPCS']
standard_concept = 'S'
concept_domain = 'Procedure'
j = _join(c, s, c.c.concept_id == s.c.concept_id)
s1 = _select([c.c.concept_id,c.c.concept_name,c.c.concept_code,\
c.c.concept_class_id, c.c.vocabulary_id, v.c.vocabulary_name]).\
select_from(j).\
where(_and_(\
c.c.vocabulary_id.in_(vocab_ids),\
c.c.concept_class_id != None,\
c.c.domain_id == concept_domain,\
c.c.standard_concept == standard_concept,\
- seec.c.vocabulary_id == v.c.vocabulary_id))
columns = [
s1.c.concept_id, s1.c.concept_name, s1.c.concept_code,
s1.c.concept_class_id, s1.c.vocabulary_id, s1.c.vocabulary_name
]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).select_from(s1).\
where(\
_func.lower(s1.c.concept_name).ilike('%{}%'.format(keyword.lower())))
return inspector.execute(statement)
def siblings_for_concept_id(concept_id, inspector, return_columns=None):
"""
Find all sibling concepts for a concept_id i.e.(concepts that share common parents).
This may or may not result in concepts that have a close clinical relationship, especially if
the query concept_id is already high up in the hierarchy or has multiple parents that diverge in
their meaning.
Parameters
----------
concept_id : int
concept_id of interest from the concept table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['sibling_concept_id', 'sibling_concept_name','sibling_concept_code','sibling_concept_class_id',
'sibling_concept_vocabulary_id,'parent_concept_id','parent_concept_name']
Returns
-------
results : inspectomop.results.Results
Notes
-----
SQL::
SELECT
s.concept_id AS sibling_concept_id,
s.concept_name AS sibling_concept_name,
a.concept_id AS parent_concept_id,
a.concept_name AS parent_concept_name
FROM
main.concept AS s,
main.concept AS a,
main.concept_ancestor AS ca,
main.vocabulary AS va,
main.concept AS d,
main.concept_ancestor AS ca2
WHERE
ca.descendant_concept_id = concept_id AND
ca.min_levels_of_separation = 1 AND
ca.ancestor_concept_id = a.concept_id AND
a.vocabulary_id = va.vocabulary_id AND
ca.descendant_concept_id = d.concept_id AND
ca2.ancestor_concept_id = ca.ancestor_concept_id AND
s.concept_id = ca2.descendant_concept_id
"""
ca = _alias(inspector.tables['concept_ancestor'], 'ca')
ca2 = _alias(inspector.tables['concept_ancestor'], 'ca2')
a = _alias(inspector.tables['concept'], 'a')
d = _alias(inspector.tables['concept'], 'd')
s = _alias(inspector.tables['concept'], 's')
va = _alias(inspector.tables['vocabulary'], 'va')
levels_of_sep = 1
columns = [s.c.concept_id.label('sibling_concept_id'), s.c.concept_name.label('sibling_concept_name'),\
s.c.concept_code.label('sibling_concept_code'),s.c.concept_class_id.label('sibling_concept_class_id'),\
s.c.vocabulary_id.label('sibling_concept_vocabulary_id'),a.c.concept_id.label('parent_concept_id'), a.c.concept_name.label('parent_concept_name')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(_and_(\
ca.c.descendant_concept_id == concept_id,\
ca.c.min_levels_of_separation == levels_of_sep, \
ca.c.ancestor_concept_id == a.c.concept_id,\
a.c.vocabulary_id == va.c.vocabulary_id,\
ca.c.descendant_concept_id == d.c.concept_id,\
ca2.c.ancestor_concept_id == ca.c.ancestor_concept_id,\
s.c.concept_id == ca2.c.descendant_concept_id)
)
return inspector.execute(statement)
def condition_concept_for_concept_id(concept_id,
inspector,
return_columns=None):
"""
Retrieves the condition concept for a condition_concept_id.
Parameters
----------
concept_id : int
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id', 'concept_name', 'concept_code', 'concept_class_id', 'vocabulary_id', 'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
See Also
--------
inspectomop.queries.general.concepts_for_concept_ids
Notes
-----
Original SQL
C01: Find condition by concept ID::
SELECT
C.concept_id Condition_concept_id,
C.concept_name Condition_concept_name,
C.concept_code Condition_concept_code,
C.concept_class_id Condition_concept_class,
C.vocabulary_id Condition_concept_vocab_ID,
V.vocabulary_name Condition_concept_vocab_name,
CASE C.vocabulary_id
WHEN 'SNOMED' THEN CASE lower(C.concept_class_id)
WHEN 'clinical finding' THEN 'Yes' ELSE 'No' END
WHEN 'MedDRA' THEN 'Yes'
ELSE 'No'
END Is_Disease_Concept_flag
FROM
concept C,
vocabulary V
WHERE
C.concept_id = 192671 AND
C.vocabulary_id = V.vocabulary_id AND
sysdate BETWEEN valid_start_date AND valid_end_date;
"""
c = _alias(inspector.tables['concept'], 'c')
v = _alias(inspector.tables['vocabulary'], 'v')
domain_id = 'Condition'
standard_concept = 'S'
columns = [c.c.concept_id, c.c.concept_name, c.c.concept_code, c.c.concept_class_id,\
c.c.vocabulary_id, v.c.vocabulary_name]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns)\
.where(_and_(\
c.c.concept_id == concept_id,\
c.c.vocabulary_id == v.c.vocabulary_id,\
c.c.domain_id == domain_id,\
c.c.standard_concept == standard_concept))
return inspector.execute(statement)
def parents_for_concept_id(concept_id, inspector, return_columns=None):
"""
Find all parent concepts for a concept_id. (Ancestors whose level of separation is 1)
Parameters
----------
concept_id : int
concept_id of interest from the concept table
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['parent_concept_id', 'parent_concept_name', 'parent_concept_code', 'parent_concept_class_id', 'parent_concept_vocabulary_id', 'parent_concept_vocab_name']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
G10: Find parents for a given concept::
SELECT
A.concept_id Parent_concept_id,
A.concept_name Parent_concept_name,
A.concept_code Parent_concept_code,
A.concept_class_id Parent_concept_class_id,
A.vocabulary_id Parent_concept_vocab_ID,
VA.vocabulary_name Parent_concept_vocab_name
FROM
concept_ancestor CA,
concept A,
concept D,
vocabulary VA
WHERE
CA.descendant_concept_id = 192671 AND
CA.min_levels_of_separation = 1 AND
CA.ancestor_concept_id = A.concept_id AND
A.vocabulary_id = VA.vocabulary_id AND
CA.descendant_concept_id = D.concept_id AND
sysdate BETWEEN A.valid_start_date AND A.valid_end_date;
"""
ca = _alias(inspector.tables['concept_ancestor'], 'ca')
a = _alias(inspector.tables['concept'], 'a')
d = _alias(inspector.tables['concept'], 'd')
va = _alias(inspector.tables['vocabulary'], 'va')
levels_of_sep = 1
columns = [a.c.concept_id.label('parent_concept_id'), a.c.concept_name.label('parent_concept_name'), a.c.concept_code.label('parent_concept_code'), a.c.concept_class_id.label('parent_concept_class_id'),\
a.c.vocabulary_id.label('parent_concept_vocabulary_id'), va.c.vocabulary_name.label('parent_concept_vocab_name')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns).\
where(_and_(\
ca.c.descendant_concept_id == concept_id,\
ca.c.min_levels_of_separation == levels_of_sep, \
ca.c.ancestor_concept_id == a.c.concept_id,\
a.c.vocabulary_id == va.c.vocabulary_id,\
ca.c.descendant_concept_id == d.c.concept_id))
return inspector.execute(statement)
def disease_causing_agents_for_keyword(keyword,
inspector,
return_columns=None):
"""
Retrieves disease causing agents by keyword. The concept_class_id can be any of: 'Pharmaceutical / biologic product',\
'Physical object', 'Special concept', 'Event', 'Physical force', or 'Substance'.
Results of queries from `disease_causing_agents_for_keyword()` and `pathogen_concept_for_keyword()`\
can be used to search for conditions caused by the offending pathogens/agents using \
`conditions_caused_by_pathogen_or_causative_agent_concept_id()`
Parameters
----------
keyword : str
search string. ex 'Radiation'
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id', 'concept_name', 'concept_code',\
'concept_class_id', 'standard_concept', 'vocabulary_id', 'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
See Also
--------
pathogen_concept_for_keyword, conditions_caused_by_pathogen_or_causative_agent_concept_id
Notes
-----
Original SQL
C08: Find a disease causing agent by keyword::
SELECT
C.concept_id Agent_Concept_ID,
C.concept_name Agent_Concept_Name,
C.concept_code Agent_concept_code,
C.concept_class_id Agent_concept_class,
C.standard_concept Agent_Standard_Concept,
C.vocabulary_id Agent_Concept_Vocab_ID,
V.vocabulary_name Agent_Concept_Vocab_Name
FROM
concept C,
vocabulary V
WHERE
LOWER(C.concept_class_id) in ('pharmaceutical / biologic product','physical object','special concept','event', 'physical force','substance') AND
LOWER(C.concept_name) like '%radiation%' AND
C.vocabulary_id = V.vocabulary_id AND
sysdate BETWEEN C.valid_start_date AND C.valid_end_date;
"""
c = _alias(inspector.tables['concept'], 'c')
v = _alias(inspector.tables['vocabulary'], 'v')
concept_class_ids = [
'pharmaceutical / biologic product', 'physical object',
'special concept', 'event', 'physical force', 'substance'
]
columns = [c.c.concept_id,c.c.concept_name, c.c.concept_code,\
c.c.concept_class_id, c.c.standard_concept, c.c.vocabulary_id, v.c.vocabulary_name]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns)\
.where(_and_(\
_func.lower(c.c.concept_class_id).in_(concept_class_ids),\
_func.lower(c.c.concept_name).ilike('%{}%'.format(keyword.lower())),\
c.c.vocabulary_id == v.c.vocabulary_id))
return inspector.execute(statement)
def pathogen_concept_for_keyword(keyword, inspector, return_columns=None):
"""
Retrieves pathogen concepts based on a keyword with 'Organsim' as the concept_class_id.
Parameters
----------
keyword : str
search string. ex 'Helicobacter Pylori'
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['concept_id', 'concept_name', 'concept_code',\
'concept_class_id', 'standard_concept', 'vocabulary_id', 'vocabulary_name']
Returns
-------
results : inspectomop.results.Results
a cursor-like object with methods such as fetchone(), fetchmany() etc.
See Also
--------
disease_causing_agents_for_keyword, conditions_caused_by_pathogen_or_causative_agent_concept_id
Notes
-----
Original SQL
C07: Find a pathogen by keyword::
SELECT
C.concept_id Pathogen_Concept_ID,
C.concept_name Pathogen_Concept_Name,
C.concept_code Pathogen_concept_code,
C.concept_class_id Pathogen_concept_class,
C.standard_concept Pathogen_Standard_Concept,
C.vocabulary_id Pathogen_Concept_Vocab_ID,
V.vocabulary_name Pathogen_Concept_Vocab_Name
FROM
concept C,
vocabulary V
WHERE
LOWER(C.concept_class_id) = 'organism' AND
LOWER(C.concept_name) like '%trypanosoma%' AND
C.vocabulary_id = V.vocabulary_id AND
sysdate BETWEEN C.valid_start_date AND C.valid_end_date;
"""
c = _alias(inspector.tables['concept'], 'c')
v = _alias(inspector.tables['vocabulary'], 'v')
vocab_id = 'SNOMED'
concept_class_id = 'Organism'
columns = [c.c.concept_id,c.c.concept_name, c.c.concept_code,\
c.c.concept_class_id, c.c.standard_concept, c.c.vocabulary_id, v.c.vocabulary_name]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
statement = _select(columns)\
.where(_and_(\
c.c.concept_class_id == concept_class_id,\
_func.lower(c.c.concept_name).ilike('%{}%'.format(keyword.lower())),\
c.c.vocabulary_id == v.c.vocabulary_id))
return inspector.execute(statement)
def standard_vocab_for_source_code(source_code,
source_vocab_id,
inspector,
return_columns=None):
"""
Convert source code to all mapped standard vocabulary concepts.
Parameters
----------
source_codes : str
alphanumeric source_code to query on e.g ICD-9 '250.00'
source_vocab_id : str
- vocabulary_id from the vocabulary table e.g 'ICD9CM'
- see https://github.com/OHDSI/CommonDataModel/wiki/VOCABULARY for a full list
inspector : inspectomop.inspector.Inspector
return_columns : list of str, optional
- optional subset of columns to return from the query
- columns : ['domain_id', 'concept_id', 'concept_name', 'concept_code', 'concept_class_id', 'vocabulary_id', 'target_concept_domain']
Returns
-------
results : inspectomop.results.Results
Notes
-----
Original SQL
G05: Translate a code from a source to a standard vocabulary::
SELECT DISTINCT
c1.domain_id,
c2.concept_id as Concept_Id,
c2.concept_name as Concept_Name,
c2.concept_code as Concept_Code,
c2.concept_class_id as Concept_Class,
c2.vocabulary_id as Concept_Vocabulary_ID,
c2.domain_id as Target_concept_Domain
FROM
concept_relationship cr
JOIN
concept c1 ON c1.concept_id = cr.concept_id_1
JOIN
concept c2 ON c2.concept_id = cr.concept_id_2
WHERE
cr.relationship_id = 'Maps to' AND
c1.concept_code IN ('070.0') AND
c1.vocabulary_id = 'ICD9CM' AND
sysdate BETWEEN cr.valid_start_date AND cr.valid_end_date;
"""
c1 = _alias(inspector.tables['concept'], 'c1')
c2 = _alias(inspector.tables['concept'], 'c2')
cr = _alias(inspector.tables['concept_relationship'], 'cr')
columns = [c1.c.domain_id, c2.c.concept_id, \
c2.c.concept_name, c2.c.concept_code, c2.c.concept_class_id,\
c2.c.vocabulary_id, c2.c.domain_id.label('target_concept_domain')]
if return_columns:
columns = [col for col in columns if col.name in return_columns]
j1 = _join(cr, c1, c1.c.concept_id == cr.c.concept_id_1)
j2 = _join(j1, c2, c2.c.concept_id == cr.c.concept_id_2)
relationship_id = 'Maps to'
statement = _select(columns).\
distinct().\
select_from(j2).\
where(_and_(\
cr.c.relationship_id == relationship_id,\
c1.c.concept_code == source_code,\
c1.c.vocabulary_id == source_vocab_id))
return inspector.execute(statement)
