<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 allergy
allergyEnteredOffset
allergyNoteType
specialtyType
userType
rxIncluded
writtenIneICU
drugName
allergyType
allergyName
drugHiclSeqno
We recommend configuring the config.ini file to allow for connection to the database without specifying your password each time.
Step2:  Examine a single patient
Step4:  Here we can see that this patient had an admission note highlighting they were allergic to nickel, tetracycline, ampicillin, and clindamycin.
Identifying patients allergic to tetracycline
Let's look for patients who were allergic to tetracycline.
Step6:  However, it's also possible that they used mixed case, pluralization, or specified more than that specific string. We can use a string comparison to look for allergies like tetracycline.
Step8:  It's also possible that they specified the allergy under the drugname column.
Step10:  Since we may not capture all spellings or brands for tetracycline, we can try to use the HICL code to identify other observations. Above, we can see the HICL for tetracycline is 5236.
Step12:  Let's combine all these methods and summarize how many patients are identified using each method.
Step14:  As we can see, using the allergyname column was the most effective, and always identified patients allergic to tetracycline. Unfortunately we know this is an incomplete search, as likely providers will document brand names rather than generic names from time to time. For example, tetracyclin is marketed under the name Sumycin among others. We can search for Sumycin in the data
Step16:  Happily, in this case, only 1 patient is excluded by not searching for Sumycin, but in general it may be more.
Hospitals with data available
<ASSISTANT_TASK:>
Python Code:
# Import libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import psycopg2
import getpass
import pdvega
# for configuring connection 
from configobj import ConfigObj
import os
%matplotlib inline
# Create a database connection using settings from config file
config='../db/config.ini'
# connection info
conn_info = dict()
if os.path.isfile(config):
    config = ConfigObj(config)
    conn_info["sqluser"] = config['username']
    conn_info["sqlpass"] = config['password']
    conn_info["sqlhost"] = config['host']
    conn_info["sqlport"] = config['port']
    conn_info["dbname"] = config['dbname']
    conn_info["schema_name"] = config['schema_name']
else:
    conn_info["sqluser"] = 'postgres'
    conn_info["sqlpass"] = ''
    conn_info["sqlhost"] = 'localhost'
    conn_info["sqlport"] = 5432
    conn_info["dbname"] = 'eicu'
    conn_info["schema_name"] = 'public,eicu_crd'
    
# Connect to the eICU database
print('Database: {}'.format(conn_info['dbname']))
print('Username: {}'.format(conn_info["sqluser"]))
if conn_info["sqlpass"] == '':
    # try connecting without password, i.e. peer or OS authentication
    try:
        if (conn_info["sqlhost"] == 'localhost') & (conn_info["sqlport"]=='5432'):
            con = psycopg2.connect(dbname=conn_info["dbname"],
                                   user=conn_info["sqluser"])            
        else:
            con = psycopg2.connect(dbname=conn_info["dbname"],
                                   host=conn_info["sqlhost"],
                                   port=conn_info["sqlport"],
                                   user=conn_info["sqluser"])
    except:
        conn_info["sqlpass"] = getpass.getpass('Password: ')
        con = psycopg2.connect(dbname=conn_info["dbname"],
                               host=conn_info["sqlhost"],
                               port=conn_info["sqlport"],
                               user=conn_info["sqluser"],
                               password=conn_info["sqlpass"])
query_schema = 'set search_path to ' + conn_info['schema_name'] + ';'
Explanation: allergy
allergyEnteredOffset
allergyNoteType
specialtyType
userType
rxIncluded
writtenIneICU
drugName
allergyType
allergyName
drugHiclSeqno
We recommend configuring the config.ini file to allow for connection to the database without specifying your password each time.
End of explanation
patientunitstayid = 287822
query = query_schema + 
select *
from allergy
where patientunitstayid = {}
.format(patientunitstayid)
df = pd.read_sql_query(query, con)
df.head()
# Look at a subset of columns
cols = ['allergyid','patientunitstayid','allergyoffset','allergyenteredoffset',
        'allergynotetype', 'usertype', 'writtenineicu',
       'drugname','allergytype','allergyname']
df[cols].head()
Explanation: Examine a single patient
End of explanation
drug = 'Tetracycline'
query = query_schema + 
select 
  allergyid, patientunitstayid
  , allergyoffset, allergyenteredoffset
  , allergytype, allergyname
  , drugname, drughiclseqno
from allergy
where allergyname = '{}'
.format(drug)
df = pd.read_sql_query(query, con)
df.set_index('allergyid',inplace=True)
print('{} unit stays with allergy to {}.'.format(df['patientunitstayid'].nunique(), drug))
df.head()
Explanation: Here we can see that this patient had an admission note highlighting they were allergic to nickel, tetracycline, ampicillin, and clindamycin.
Identifying patients allergic to tetracycline
Let's look for patients who were allergic to tetracycline.
End of explanation
drug = 'Tetracycline'
query = query_schema + 
select 
  allergyid, patientunitstayid
  , allergyoffset, allergyenteredoffset
  , allergytype, allergyname
  , drugname, drughiclseqno
from allergy
where lower(allergyname) like '%{}%'
.format(drug.lower())
df = pd.read_sql_query(query, con)
df.set_index('allergyid',inplace=True)
print('{} unit stays with allergy to {}.'.format(df['patientunitstayid'].nunique(), drug))
df['allergyname'].value_counts()
Explanation: However, it's also possible that they used mixed case, pluralization, or specified more than that specific string. We can use a string comparison to look for allergies like tetracycline.
End of explanation
drug = 'Tetracycline'
query = query_schema + 
select 
  allergyid, patientunitstayid
  , allergyoffset, allergyenteredoffset
  , allergytype, allergyname
  , drugname, drughiclseqno
from allergy
where lower(drugname) like '%{}%'
.format(drug.lower())
df = pd.read_sql_query(query, con)
df.set_index('allergyid',inplace=True)
print('{} unit stays with allergy to {} specified in drugname.'.format(df['patientunitstayid'].nunique(), drug))
print(df['drugname'].value_counts())
df.head()
Explanation: It's also possible that they specified the allergy under the drugname column.
End of explanation
hicl = 5236
query = query_schema + 
select 
  allergyid, patientunitstayid
  , allergyoffset, allergyenteredoffset
  , allergytype, allergyname
  , drugname, drughiclseqno
from allergy
where drughiclseqno = {}
.format(hicl)
df = pd.read_sql_query(query, con)
df.set_index('allergyid',inplace=True)
print('{} unit stays with allergy to HICL {} specified in drugname.'.format(df['patientunitstayid'].nunique(), hicl))
print(df['drugname'].value_counts())
df.head()
Explanation: Since we may not capture all spellings or brands for tetracycline, we can try to use the HICL code to identify other observations. Above, we can see the HICL for tetracycline is 5236.
End of explanation
hicl = 5236
drugname = 'Tetracycline'
allergyname = 'Tetracycline'
query = query_schema + 
select 
  patientunitstayid
  , max(case when drughiclseqno = {} then 1 else 0 end) as hicl_match
  , max(case when lower(drugname) like '%{}%' then 1 else 0 end) as drug_match
  , max(case when lower(allergyname) like '%{}%' then 1 else 0 end) as allergy_match
from allergy
group by patientunitstayid
.format(hicl, drugname.lower(), allergyname.lower())
df = pd.read_sql_query(query, con)
# drop non-matching rows
idx = (df['hicl_match'] == 1) | (df['drug_match'] == 1) | (df['allergy_match'] == 1)
df = df.loc[idx, :]
df.groupby(['hicl_match', 'drug_match', 'allergy_match']).count()
Explanation: Let's combine all these methods and summarize how many patients are identified using each method.
End of explanation
drug = 'Sumycin'
query = query_schema + 
select 
  allergyid, patientunitstayid
  , allergyoffset, allergyenteredoffset
  , allergytype, allergyname
  , drugname, drughiclseqno
from allergy
where lower(allergyname) like '%{}%'
.format(drug.lower())
df = pd.read_sql_query(query, con)
df.set_index('allergyid',inplace=True)
print('{} unit stays with allergy to {}.'.format(df['patientunitstayid'].nunique(), drug))
df['allergyname'].value_counts()
Explanation: As we can see, using the allergyname column was the most effective, and always identified patients allergic to tetracycline. Unfortunately we know this is an incomplete search, as likely providers will document brand names rather than generic names from time to time. For example, tetracyclin is marketed under the name Sumycin among others. We can search for Sumycin in the data:
End of explanation
query = query_schema + 
select 
  pt.hospitalid
  , count(distinct pt.patientunitstayid) as number_of_patients
  , count(distinct a.patientunitstayid) as number_of_patients_with_tbl
from patient pt
left join allergy a
  on pt.patientunitstayid = a.patientunitstayid
group by pt.hospitalid
.format(patientunitstayid)
df = pd.read_sql_query(query, con)
df['data completion'] = df['number_of_patients_with_tbl'] / df['number_of_patients'] * 100.0
df.sort_values('number_of_patients_with_tbl', ascending=False, inplace=True)
df.head(n=10)
df[['data completion']].vgplot.hist(bins=10,
                                    var_name='Number of hospitals',
                                    value_name='Percent of patients with data')
Explanation: Happily, in this case, only 1 patient is excluded by not searching for Sumycin, but in general it may be more.
Hospitals with data available
End of explanation
<END_TASK>