<jupyter_start><jupyter_text>Plot example data for a single patientThis is a modification of the notebook available from [this](https://github.com/MIT-LCP/mimic-iii-paper) repository. It has been modified so as to run on a SQLite3 instance of the MIMIC III demo database. SQLite3 is not handling dates correctly, so I handle dates, times, and deltas in Pandas.<jupyter_code># Import libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import sqlite3 as sq
import random
%matplotlib inline
# Config
sqluser = 'postgres'
dbname = 'mimic'
schema_name = 'mimiciii'
# Connect to MIMIC
con = sq.connect("../notebooks/mimic3.db")
cur = con.cursor()
#cur.execute('SET search_path to ' + schema_name)
# cur.close()
# con.close()
ids = list(pd.read_sql("""SELECT icustay_id FROM icustays""", con).icustay_id)
# Select a single ICU stay
icustay_id = random.choice(ids)
icustay_id
# Load admissions
query = """
SELECT ad.subject_id
  , ad.hadm_id
  , ad.admission_type
  , ad.diagnosis
  , ic.icustay_id
  , ic.first_careunit
  , ic.last_careunit
  , ic.intime as icu_intime
  , ad.hospital_expire_flag
  , pa.expire_flag
FROM admissions ad
INNER JOIN icustays ic
ON ad.subject_id = ic.subject_id
INNER JOIN patients pa
ON ad.subject_id = pa.subject_id
WHERE ic.icustay_id = """ + str(icustay_id) + """ 
ORDER BY ic.intime
"""

admissions = pd.read_sql_query(query,con)
# First few rows of admissions
admissions.head()
# Load chartevents
query = """
SELECT ce.icustay_id
  , ce.charttime
  , ie.intime
  , di.label
  , ce.value
  , ce.valuenum
  , ce.valueuom
FROM chartevents ce
INNER join d_items di
ON ce.itemid = di.itemid
INNER join icustays ie
ON ce.icustay_id = ie.icustay_id
WHERE ce.icustay_id = """ + str(icustay_id) + """
AND error != 1
ORDER BY ce.charttime
"""

charts = pd.read_sql_query(query,con)
# First few rows of charts
charts.head()
# Load outputevents
query = """
SELECT oe.icustay_id
  , oe.charttime
  , ie.intime
  , di.label
  , oe.value
  , oe.valueuom
FROM outputevents oe 
INNER JOIN icustays ie
  ON oe.icustay_id = ie.icustay_id
INNER JOIN d_items di
  ON oe.itemid = di.itemid
WHERE oe.icustay_id = """ + str(icustay_id) + """
ORDER BY oe.charttime
"""

outputs = pd.read_sql_query(query,con)
# First few rows of outputs
outputs.head()
# Load inputevents
query = """
SELECT inp.icustay_id
  , inp.starttime
  , inp.endtime
  , ie.intime
  , di.label
  , inp.amount
  , inp.amountuom
  , inp.rate
  , inp.rateuom
FROM inputevents_mv inp 
INNER join icustays ie
  ON inp.icustay_id = ie.icustay_id
INNER join d_items di
  ON inp.itemid = di.itemid
WHERE inp.icustay_id = """ + str(icustay_id) + """
AND lower(inp.statusdescription) != 'rewritten'
ORDER BY inp.starttime
"""

inputs = pd.read_sql_query(query,con)
# First few rows of inputs
inputs.head()
# Load labevents
query = """
SELECT le.subject_id
  , ie.icustay_id
  , le.charttime
  , ie.intime 
  , di.label
  , le.value
  , le.valuenum
  , le.valueuom
FROM labevents le
INNER join icustays ie
  ON le.subject_id = ie.subject_id
  AND le.charttime >= ie.intime
  AND le.charttime <= ie.outtime
INNER JOIN d_labitems di
  ON le.itemid = di.itemid
WHERE ie.icustay_id = """ + str(icustay_id) + """
ORDER BY le.charttime
"""

labs = pd.read_sql_query(query,con)
# First few rows of labs
print(charts.dtypes)
inputs.head()
pd.to_datetime(outputs["charttime"], format="%Y-%m-%d %H:%M:%S").dtype<jupyter_output><empty_output><jupyter_text>Dates are not converting<jupyter_code>admissions["icu_intime"] = admissions["icu_intime"].apply(pd.to_datetime) #format="%Y-%m-%d %H:%M:%S")
charts[["charttime", "intime"]] = charts[["charttime", "intime"]].apply(pd.to_datetime)#, format="%Y-%m-%d %H:%M:%S")
outputs[["charttime", "intime"]] = outputs[["charttime", "intime"]].apply(pd.to_datetime)#, format="%Y-%m-%d %H:%M:%S")
inputs[["starttime", "intime", "endtime"]] = inputs[["starttime", "intime", "endtime"]].apply(pd.to_datetime)#, format="%Y-%m-%d %H:%M:%S")
labs[["charttime", "intime"]] = labs[["charttime", "intime"]].apply(pd.to_datetime)#, format="%Y-%m-%d %H:%M:%S")
outputs.dtypes
labs["icutime"] = labs.charttime - labs.intime
inputs["icustarttime"] = inputs.starttime - inputs.intime
inputs["icuendtime"] = inputs.endtime - inputs.intime
outputs["icutime"]= outputs.charttime - outputs.intime 
charts["icutime"] = charts.charttime - charts.intime
# Snip data to specified number of days
maxdays = 5;
charts = charts.loc[charts.icutime.dt.days<=maxdays]
outputs = outputs.loc[outputs.icutime.dt.days<=maxdays]
inputs = inputs.loc[inputs.icustarttime.dt.days<=maxdays]
labs = labs.loc[labs.icutime.dt.days<=maxdays]
# Create column with minutes from ICU intime
charts['icutimehr'] = (charts['icutime'].dt.seconds/60/60)+(charts['icutime'].dt.days*24)
outputs['icutimehr'] = (outputs['icutime'].dt.seconds/60/60)+(outputs['icutime'].dt.days*24)
inputs['icustarttimehr'] = (inputs['icustarttime'].dt.seconds/60/60)+(inputs['icustarttime'].dt.days*24)
inputs['icuendtimehr'] = (inputs['icuendtime'].dt.seconds/60/60)+(inputs['icuendtime'].dt.days*24)
labs['icutimehr'] = (labs['icutime'].dt.seconds/60/60)+(labs['icutime'].dt.days*24)
# What are the most commonly measured variables in charts?
charts['label'].value_counts().head()
# What are the most commonly measured variables in charts?
outputs['label'].value_counts().head()
# What are the most commonly measured variables in charts?
inputs['label'].value_counts().head()
# What are the most commonly measured variables in charts?
labs.mean()
# Get average values
hr_mean = charts.valuenum[charts.label=='Heart Rate'].mean()
bp_mean = charts.icutimehr[charts.label=='Non Invasive Blood Pressure mean'].mean()
temp_mean = ((charts.valuenum[charts.label=='Temperature Fahrenheit']-32)/1.8).mean()

# Print averages
print("Mean HR is: {0:.2f}".format(hr_mean))
print("Mean BP is: {0:.2f}".format(bp_mean))
print("Mean temp, C is: {0:.2f}".format(temp_mean))

# Plot sample data over first 24 hours from admission to ICU
# Credit: <NAME> for styling (http://www.randalolson.com/2014/06/28/)

# Prepare the size of the figure
fig = plt.figure(figsize=(22, 20))
plt.rcParams.update({'font.size': 22})

# "Tableau 20" colors as RGB.   
tableau20 = [(31, 119, 180), (174, 199, 232), (255, 127, 14), (255, 187, 120),    
             (44, 160, 44), (152, 223, 138), (214, 39, 40), (255, 152, 150),    
             (148, 103, 189), (197, 176, 213), (140, 86, 75), (196, 156, 148),    
             (227, 119, 194), (247, 182, 210), (127, 127, 127), (199, 199, 199),    
             (188, 189, 34), (219, 219, 141), (23, 190, 207), (158, 218, 229)]  
  
# Scale the RGB values to the [0, 1] range, which is the format matplotlib accepts.    
for i in range(len(tableau20)):    
    r, g, b = tableau20[i]    
    tableau20[i] = (r / 255., g / 255., b / 255.)
    
# Remove the plot frame lines. 
ax = plt.subplot(111)    
ax.spines["top"].set_visible(False)    
ax.spines["bottom"].set_visible(True)    
ax.spines["right"].set_visible(False)    
ax.spines["left"].set_visible(True)    
  
# Ensure that the axis ticks only show up on the bottom and left of the plot.      
ax.get_xaxis().tick_bottom()    
ax.get_yaxis().tick_left() 
ax.axis([0,60,0,225])

# Plot vital signs
plt.plot(charts.icutimehr[charts.label=='Heart Rate'], 
         charts.valuenum[charts.label=='Heart Rate'],
         color=tableau20[6], lw=2.5,
         marker='o', markersize=6, label='Heart rate')

plt.plot(charts.icutimehr[charts.label=='O2 saturation pulseoxymetry'], 
         charts.valuenum[charts.label=='O2 saturation pulseoxymetry'],
         color=tableau20[1], lw=2.5, 
         marker='o', markersize=6, label='O2 saturation')

plt.plot(charts.icutimehr[charts.label=='Non Invasive Blood Pressure mean'], 
         charts.valuenum[charts.label=='Non Invasive Blood Pressure mean'],
         color=tableau20[4], lw=2.5,
         marker='o', markersize=6, label='NIBP, mean')

plt.plot(charts.icutimehr[charts.label=='Respiratory Rate'], 
         charts.valuenum[charts.label=='Respiratory Rate'],
         color=tableau20[2], lw=2.5,
         marker='o', markersize=6, label='Respiratory rate')

# for i, txt in enumerate(charts.value[charts.label=='Temperature Fahrenheit'].values):
#         plt.annotate(txt,(charts.icutimehr[charts.label=='Temperature Fahrenheit'].
#                            values[i],140),fontsize=15)

# Plot input/output events
plt.plot(inputs.icustarttimehr[inputs.amountuom=='mL'], 
         inputs.amount[inputs.amountuom=='mL'].cumsum()/100, 
         color=tableau20[9], lw=2.5,
         marker='o', markersize=6, label='Intake volume, dL')

plt.plot(outputs.icutimehr, 
         outputs.value.cumsum()/100, 
         color=tableau20[10], lw=2.5,
         marker='o', markersize=6, label='Output volume, dL')

# Plot intravenous meds
plt.text(-10,150,'NaCl 0.9%',fontsize=17)
for i,row in inputs.loc[(inputs["label"] =='NaCl 0.9%') & (inputs["rate"] > 0)].iterrows():
    plt.plot([row['icustarttimehr'],row['icuendtimehr']],[150]*2,
             color=tableau20[16], lw=4,marker='o', markersize=6)
    plt.text(row['icustarttimehr'],150,
             str(round(row['rate'],1)) + ' ' + str(row['rateuom']),
             fontsize=15)

plt.text(-10,145,'Amiodarone',fontsize=17)
for i,row in inputs.loc[(inputs["label"] =='Amiodarone 600/500') & (inputs["rate"] > 0)].iterrows():
    plt.plot([row['icustarttimehr'],row['icuendtimehr']],[145]*2,
             color=tableau20[16], lw=4,marker='o', markersize=6)
    plt.text(row['icustarttimehr'],145,
             str(round(row['rate'],1)) + ' ' + str(row['rateuom']),
             fontsize=15)    

plt.text(-10,140,'Dextrose 5%',fontsize=17)
for i,row in inputs.loc[(inputs["label"] =='Dextrose 5%') 
                        & (inputs["rate"] > 0) & (inputs["rate"] < 500)].iterrows():
    plt.plot([row['icustarttimehr'],row['icuendtimehr']],[140]*2,
             color=tableau20[16], lw=4,marker='o', markersize=6)
    plt.text(row['icustarttimehr'],140,
             str(round(row['rate'],1)) + ' ' + str(row['rateuom']),
             fontsize=15)    

plt.text(-10,165,'Morphine Sulfate',fontsize=17)
plt.plot(inputs.icustarttimehr[inputs.label=='Morphine Sulfate'],
          [165]*len(inputs[inputs.label=='Morphine Sulfate']),
          color=tableau20[16], lw=0, marker='o', markersize=6)   
    
plt.text(-10,160,'Vancomycin (1 dose)',fontsize=17)
plt.plot(inputs.icustarttimehr[inputs.label=='Vancomycin'],
          [160]*len(inputs[inputs.label=='Vancomycin']),
          color=tableau20[16], lw=0, marker='o', markersize=6)
    
plt.text(-10,155,'Piperacillin (1 dose)',fontsize=17)
plt.plot(inputs.icustarttimehr[inputs.label=='Piperacillin/Tazobactam (Zosyn)'],
         [155]*len(inputs[inputs.label=='Piperacillin/Tazobactam (Zosyn)']),
         color=tableau20[16], lw=0, marker='o', markersize=6)


# Plot labs
plt.text(-10,175,'Neutrophil, %',fontsize=17)
for i, txt in enumerate(labs.value[labs.label=='NEUTROPHILS'].values):
        plt.annotate(txt, (labs.icutimehr[labs.label=='NEUTROPHILS'].
                           values[i],175),fontsize=17) 

plt.text(-10,180,'White blood cell, K/uL',fontsize=17)
for i, txt in enumerate(labs.value[labs.label=='WHITE BLOOD CELLS'].values):
        plt.annotate(txt, (labs.icutimehr[labs.label=='WHITE BLOOD CELLS'].
                           values[i],180),fontsize=17)

plt.text(-10,185,'Creatinine, mg/dL',fontsize=17)        
for i, txt in enumerate(labs.value[labs.label=='CREATININE'].values):
        plt.annotate(txt, (labs.icutimehr[labs.label=='CREATININE'].
                           values[i],185),fontsize=17)

plt.text(-10,190,'Platelet, K/uL',fontsize=17)
for i, txt in enumerate(labs.value[labs.label=='PLATELET COUNT'].values):
        plt.annotate(txt, (labs.icutimehr[labs.label=='PLATELET COUNT'].
                           values[i],190),fontsize=17)

# Plot Glasgow Coma Scale
plt.text(-10,200,'GCS: Eye',fontsize=17)
for i, txt in enumerate(charts.value[charts.label=='GCS - Eye Opening'].values):
    if np.mod(i,2)==0 and i < 65:
        plt.annotate(txt, (charts.icutimehr[charts.label=='GCS - Eye Opening'].
                           values[i],200),fontsize=17)

plt.text(-10,205,'GCS: Motor',fontsize=17)
for i, txt in enumerate(charts.value[charts.label=='GCS - Motor Response'].values):
    if np.mod(i,2)==0 and i < 65:
        plt.annotate(txt, (charts.icutimehr[charts.label=='GCS - Motor Response'].
                           values[i],205),fontsize=17)

plt.text(-10,210,'GCS: Verbal',fontsize=17)  
for i, txt in enumerate(charts.value[charts.label=='GCS - Verbal Response'].values):
    if np.mod(i,2)==0 and i < 65:
        plt.annotate(txt, (charts.icutimehr[charts.label=='GCS - Verbal Response'].
                           values[i],210),fontsize=17)

# Plot code status
plt.text(-10,220,'Code status',fontsize=17) 
for i, txt in enumerate(charts.value[charts.label=='Code Status'].values):
        plt.annotate(txt, (charts.icutimehr[charts.label=='Code Status'].
                           values[i],220),fontsize=17)
        
plt.legend(loc=5,fontsize=18)
plt.xlabel('Time after admission to the intensive care unit, hours', fontsize=22)
plt.ylabel('Measurement, absolute value', fontsize=22)
plt.yticks(np.arange(0, 140, 20))

# Save the figure
fig.savefig('examplepatient_%s.pdf'%icustay_id, bbox_inches='tight')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17090121058137797, 'fraction_numerical': 0.04274398445673293, 'mean_word_length': 4.096344249809596, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 14, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '141315', 'license': 'CC0-1.0', 'n_tokens_mistral': 5480, 'n_tokens_neox': 4868, 'n_words': 962, 'path': 'notebooks/sqlite_example-patient.ipynb', 'repo_name': 'Melbourne-BMDS/mimic34md2020_materials', 'size': '13388'}