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 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 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 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)
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 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)