<jupyter_start><jupyter_text>nurseChartingThe nurseCharting table is the largest table in eICU-CRD, and contains information entered in a semi-structured form by care staff. The three columns `nursingchartcelltypecat`, `nursingchartcelltypevallabel` and `nursingchartcelltypevalname` provide an organised structure for the data, but values entered in `nursingchartvalue` are free text entry and therefore fairly unstructured. Nurse charting data can be entered directy into the system or can represent interfaced data from charting in the bedside EMR.At the moment, publicly available data for this table has been chosen based upon whether the information in `nursingchartvalue` is structured: data where this is highly structured has been made available (including scores such as the Glasgow Coma Scale or vital signs such as heart rate), conversely data where this is highly unstructured (free-text comments, nursing assessments) are not currently publicly available.<jupyter_code># Import libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import psycopg2
import getpass

# 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 = 141168
query = query_schema + """
select *
from nursecharting
where patientunitstayid = {}
order by nursingchartoffset
""".format(patientunitstayid)

df = pd.read_sql_query(query, con)
df.head()
df.columns
# Look at a subset of columns
cols = ['nursingchartid','patientunitstayid',
        'nursingchartoffset','nursingchartentryoffset',
        'nursingchartcelltypecat', 'nursingchartcelltypevallabel',
        'nursingchartcelltypevalname', 'nursingchartvalue']
df[cols].head()<jupyter_output><empty_output><jupyter_text>Plot patient vitals over time<jupyter_code>vitals = df['nursingchartcelltypevallabel'].value_counts()
vitals
# list of lists
# for each element, the list is:
#   [nursingchartcelltypevallabel, nursingchartcelltypevalname]
vitals = [['Heart Rate','Heart Rate'],
          ['O2 Saturation','O2 Saturation'],
          ['Temperature','Temperature (C)'],
          ['Non-Invasive BP','Non-Invasive BP Systolic'],
          ['Non-Invasive BP','Non-Invasive BP Diastolic'],
          ['Invasive BP','Invasive BP Systolic'],
          ['Invasive BP','Invasive BP Diastolic'],
          ['MAP (mmHg)','Value']]

plt.figure(figsize=[12,8])
for v in vitals:
    idx = (df['nursingchartcelltypevallabel'] == v[0]) & (df['nursingchartcelltypevalname'] == v[1])
    df_plot = df.loc[idx, :]
    
    if 'Systolic' in v[1]:
        marker = '^-'
    elif 'Diastolic' in v[1]:
        marker = 'v-'
    else:
        marker = 'o-'
    plt.plot(df_plot['nursingchartoffset'],
             pd.to_numeric(df_plot['nursingchartvalue'], errors='coerce'),
            marker, markersize=8, lw=2, label=v)
    
plt.xlabel('Time since ICU admission (minutes)')
plt.ylabel('Measurement value')
plt.legend(loc='upper right')
plt.show()<jupyter_output><empty_output><jupyter_text>Hospitals with data available<jupyter_code>query = query_schema + """
with t as
(
select distinct patientunitstayid
from nursecharting
)
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)
plt.figure(figsize=[10,6])
plt.hist(df['data completion'], bins=np.linspace(0, 100, 10))
plt.xlabel('Percent of patients with data in nursecharting')
plt.ylabel('Number of hospitals')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11960818009967349, 'fraction_numerical': 0.007561436672967864, 'mean_word_length': 3.991423670668954, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 16, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '408653', 'license': 'MIT', 'n_tokens_mistral': 1872, 'n_tokens_neox': 1769, 'n_words': 437, 'path': 'notebooks/nursecharting.ipynb', 'repo_name': 'vishwas1234567/eicu-code', 'size': '5819'}