<jupyter_start><jupyter_text>medicationThe medications table reflects the active medication orders for patients. These are orders but do not necessarily reflect administration to the patient. For example, while existence of data in the infusionDrug table confirms a patient received a continuous infusion, existence of the same data in this table only indicates that the infusion was *ordered* for the patient. Most orders are fulfilled, but not all. Furthermore, many orders are done *pro re nata*, or PRN, which means "when needed". Administration of these orders is difficult to quantify.In the US, all orders must be reviewed by a pharmacist. The majority of hospitals have an HL7 medication interface system in place which automatically synchronizes the orders with eCareManager (the source of this database) as they are verified by the pharmacist in the source pharmacy system. For hospitals without a medication interface, the eICU staff may enter a selection of medications to facilitate population management and completeness for reporting purposes.<jupyter_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'] + ';'<jupyter_output>Database: eicu
Username: alistairewj<jupyter_text>Examine a single patient<jupyter_code>patientunitstayid = 237395
query = query_schema + """
select *
from medication
where patientunitstayid = {}
order by drugorderoffset
""".format(patientunitstayid)

df = pd.read_sql_query(query, con)
df.head()
df.columns
# Look at a subset of columns
cols = ['medicationid','patientunitstayid',
        'drugorderoffset','drugorderoffset', 'drugstopoffset',
        'drugivadmixture', 'drugordercancelled', 'drugname','drughiclseqno', 'gtc',
       'dosage','routeadmin','loadingdose', 'prn']
df[cols].head().T<jupyter_output><empty_output><jupyter_text>Here we can see that, roughly on ICU admission, the patient had an order for vancomycin, aztreonam, and tobramycin.  Identifying patients admitted on a single drug Let's look for patients who have an order for vancomycin using exact text matching.<jupyter_code>drug = 'VANCOMYCIN'
query = query_schema + """
select 
  distinct patientunitstayid
from medication
where drugname like '%{}%'
""".format(drug)

df_drug = pd.read_sql_query(query, con)
print('{} unit stays with {}.'.format(df_drug.shape[0], drug))<jupyter_output>29737 unit stays with VANCOMYCIN.<jupyter_text>Exact text matching is fairly weak, as there's no systematic reason to prefer upper case or lower case. Let's relax the case matching.<jupyter_code>drug = 'VANCOMYCIN'
query = query_schema + """
select 
  distinct patientunitstayid
from medication
where drugname ilike '%{}%'
""".format(drug)

df_drug = pd.read_sql_query(query, con)
print('{} unit stays with {}.'.format(df_drug.shape[0], drug))<jupyter_output>41867 unit stays with VANCOMYCIN.<jupyter_text>HICL codes are used to group together drugs which have the same underlying ingredient (i.e. most frequently this is used to group brand name drugs with the generic name drugs). We can see above the HICL for vancomycin is 10093, so let's try grabbing that.<jupyter_code>hicl = 10093
query = query_schema + """
select 
  distinct patientunitstayid
from medication
where drughiclseqno = {}
""".format(hicl)

df_hicl = pd.read_sql_query(query, con)
print('{} unit stays with HICL = {}.'.format(df_hicl.shape[0], hicl))<jupyter_output>9716 unit stays with HICL = 10093.<jupyter_text>No luck! I wonder what we missed? Let's go back to the original query, this time retaining HICL and the name of the drug.<jupyter_code>drug = 'VANCOMYCIN'
query = query_schema + """
select 
  drugname, drughiclseqno, count(*) as n
from medication
where drugname ilike '%{}%'
group by drugname, drughiclseqno
order by n desc
""".format(drug)

df_drug = pd.read_sql_query(query, con)
df_drug.head()<jupyter_output><empty_output><jupyter_text>It appears there are more than one HICL - we can group by HICL in this query to get an idea.<jupyter_code>df_drug['drughiclseqno'].value_counts()<jupyter_output><empty_output><jupyter_text>Unfortunately, we can't be sure that these HICLs always identify only vancomycin. For example, let's look at drugnames for HICL = 1403.<jupyter_code>hicl = 1403
query = query_schema + """
select 
  drugname, count(*) as n
from medication
where drughiclseqno = {}
group by drugname
order by n desc
""".format(hicl)

df_hicl = pd.read_sql_query(query, con)
df_hicl.head()<jupyter_output><empty_output><jupyter_text>This HICL seems more focused on the use of creams than on vancomycin. Let's instead inspect the top 3.<jupyter_code>for hicl in [4042, 10093, 37442]:
    query = query_schema + """
    select 
      drugname, count(*) as n
    from medication
    where drughiclseqno = {}
    group by drugname
    order by n desc
    """.format(hicl)

    df_hicl = pd.read_sql_query(query, con)
    print('HICL {}'.format(hicl))
    print('Number of rows: {}'.format(df_hicl['n'].sum()))
    print('Top 5 rows by frequency:')
    print(df_hicl.head())
    print()<jupyter_output>HICL 4042
Number of rows: 68951
Top 5 rows by frequency:
                         drugname     n
0  VANCOMYCIN HCL 1000 MG IV SOLR  9661
1                      vancomycin  7826
2                        VANCOCIN  4613
3     VANCOMYCIN HCL 10 G IV SOLR  3891
4                  VANCOMYCIN HCL  3064

HICL 10093
Number of rows: 18553
Top 5 rows by frequency:
                                           drugname     n
0                 VANCOmycin 1 GM in NS 250 mL IVPB  3977
1  VANCOMYCIN 1.25 GM IN NS 250 ML IVPB (REPACKAGE)  2037
2               VANCOmycin 1.25 GM in NS 500ML IVPB  1970
3                VANCOmycin 1.5 GM in NS 500ML IVPB  1944
4   VANCOMYCIN 1.5 GM IN NS 250 ML IVPB (REPACKAGE)  1475

HICL 37442
Number of rows: 3977
Top 5 rows by frequency:
                                          drugname     n
0         VANCOMYCIN 1 G MINI-BAG PLUS (VIAL MATE)  1856
1  VANCOMYCIN HCL IN DEXTROSE 750 MG/150ML IV SOLN   822
2                        VANCOMYCIN 5gm VIAL (MDV)   525
3         [...]<jupyter_text>This is fairly convincing that these only refer to vancomycin. An alternative approach is to acquire the code book for HICL codes and look up vancomycin there.  Hospitals with data available<jupyter_code>query = query_schema + """
with t as
(
select distinct patientunitstayid
from medication
)
select 
  pt.hospitalid
  , count(distinct pt.patientunitstayid) as number_of_patients
  , count(distinct t.patientunitstayid) as number_of_patients_with_tbl
from patient pt
left join t
  on pt.patientunitstayid = t.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')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09497021839841165, 'fraction_numerical': 0.024045885726891682, 'mean_word_length': 3.5769813225643614, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '297213', 'license': 'MIT', 'n_tokens_mistral': 3023, 'n_tokens_neox': 2792, 'n_words': 895, 'path': 'notebooks/medication.ipynb', 'repo_name': 'vishwas1234567/eicu-code', 'size': '9066'}