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 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 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 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 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_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 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_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 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 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 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 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 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 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 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 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 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 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 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 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 source_codes_for_concept_ids(concept_ids, inspector, return_columns=None): """ Retreives source condition concepts for OMOP concept_ids. i.e SNOMED-CT --> ICD-9-CM, ICD-10-CM Parameters ---------- source_code : list of int integer list of concept_ids to translate inspector : inspectomop.inspector.Inspector return_columns : list of str, optional - optional subset of columns to return from the query - columns : ['concept_id','concept_code', 'concept_name',\ 'vocab_id', 'vocab_name',\ 'domain_id','source_concept_id',\ 'source_concept_name', 'source_concept_code',\ 'source_concept_class_id','source_vocab_id', \ 'source_vocab_name'] Returns ------- results : inspectomop.results.Results a cursor-like object with methods such as fetchone(), fetchmany() etc. Notes ----- Original SQL C06: Translate a given condition to source codes:: set search_path to full_201612_omop_v5; SELECT DISTINCT c1.concept_code, c1.concept_name, c1.vocabulary_id source_vocabulary_id, VS.vocabulary_name source_vocabulary_description, C1.domain_id, C2.concept_id target_concept_id, C2.concept_name target_Concept_Name, C2.concept_code target_Concept_Code, C2.concept_class_id target_Concept_Class, C2.vocabulary_id target_Concept_Vocab_ID, VT.vocabulary_name target_Concept_Vocab_Name FROM concept_relationship cr, concept c1, concept c2, vocabulary VS, vocabulary VT WHERE cr.concept_id_1 = c1.concept_id AND cr.relationship_id = 'Maps to' AND cr.concept_id_2 = c2.concept_id AND c1.vocabulary_id = VS.vocabulary_id AND c1.domain_id = 'Condition' AND c2.vocabulary_id = VT.vocabulary_id AND c1.concept_id = 312327 AND c1.vocabulary_id = 'SNOMED' AND sysdate BETWEEN c2.valid_start_date AND c2.valid_end_date; """ c1 = _alias(inspector.tables['concept'], 'c1') c2 = _alias(inspector.tables['concept'], 'c2') cr = _alias(inspector.tables['concept_relationship'], 'cr') vs = _alias(inspector.tables['vocabulary'], 'vs') vt = _alias(inspector.tables['vocabulary'], 'vt') vocab_id = 'SNOMED' domain_id = 'Condition' relationship_id = 'Maps to' columns = [c1.c.concept_id.label('concept_id'), c1.c.concept_code.label('concept_code'), c1.c.concept_name.label('concept_name'),\ c1.c.vocabulary_id.label('vocab_id'), vs.c.vocabulary_name.label('vocab_name'),\ c1.c.domain_id.label('domain_id'),c2.c.concept_id.label('source_concept_id'),\ c2.c.concept_name.label('source_concept_name'), c2.c.concept_code.label('source_concept_code'),\ c2.c.concept_class_id.label('source_concept_class_id'), c2.c.vocabulary_id.label('source_vocab_id'), \ vt.c.vocabulary_name.label('source_vocab_name')] if return_columns: columns = [col for col in columns if col.name in return_columns] statement = _select(columns)\ .where(_and_(\ cr.c.concept_id_1 == c1.c.concept_id,\ cr.c.relationship_id == relationship_id,\ cr.c.concept_id_2 == c2.c.concept_id,\ c1.c.vocabulary_id == vs.c.vocabulary_id,\ c1.c.domain_id == domain_id,\ c2.c.vocabulary_id == vt.c.vocabulary_id,\ c1.c.concept_id.in_(concept_ids),\ c2.c.vocabulary_id == vocab_id))\ .distinct() 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)