### Connect to PostgreSQL using psql

Source: https://github.com/mit-lcp/eicu-code/blob/main/website/content/tutorials/install_eicu_locally.md

Demonstrates how to connect to a PostgreSQL database using the psql command-line tool. It assumes the default database name 'postgres' for new installations.

```bash
# connect to the default database
psql postgres
```

--------------------------------

### Create Tables in PostgreSQL for eICU

Source: https://github.com/mit-lcp/eicu-code/blob/main/website/content/tutorials/install_eicu_locally.md

Example psql command to create the ADMISSIONDX table within the eICU database. It includes dropping the table if it exists and then executing the CREATE TABLE statement with specified columns and data types.

```psql
-- drop the table if it already exists
DROP TABLE IF EXISTS ADMISSIONDX;

-- create the table
CREATE TABLE ADMISSIONDX
(
  ADMISSIONDXID BIGINT NOT NULL,
  PATIENTUNITSTAYID BIGINT NOT NULL,
  ADMITDXENTEREDYEAR SMALLINT NOT NULL,
  ADMITDXENTEREDTIME24 VARCHAR(8) NOT NULL,
  ADMITDXENTEREDTIME VARCHAR(20) NOT NULL,
  ADMITDXENTEREDOFFSET BIGINT NOT NULL,
  ADMITDXPATH VARCHAR(500) NOT NULL,
  ADMITDXNAME VARCHAR(255),
  ADMITDXTEXT VARCHAR(255)
) ;

```

--------------------------------

### Install Postgres on Mac OSX and Ubuntu

Source: https://github.com/mit-lcp/eicu-code/blob/main/website/content/tutorials/install_eicu_locally.md

Commands to install PostgreSQL on Mac OSX using Homebrew and on Ubuntu Linux using apt-get. These commands ensure the database management system is available for local use.

```shell
# On Mac OSX with Homebrew
brew install postgres
```

```shell
# On Ubuntu Linux
sudo apt-get install postgresql-9.4
```

--------------------------------

### Database Connection Setup

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/cross-reference-apache-treatments.ipynb

Establishes a connection to a PostgreSQL database named 'eicu'. It configures the connection parameters including username, database name, schema, host, and port. This connection is essential for executing SQL queries.

```python
# create a database connectionsqluser = 'postgres'
dbname = 'eicu'
schema_name = 'eicu_crd'
sqlhost = 'localhost'
sqlport = 5432
# Connect to the database
con = psycopg2.connect(dbname=dbname, user=sqluser, host=sqlhost, port=sqlport)
```

--------------------------------

### Database Connection Setup (Python)

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/apache.ipynb

This code snippet shows how to establish a database connection using configuration settings from an 'config.ini' file. It utilizes the 'configobj' library to read connection parameters, ensuring sensitive information is kept separate from the main code.

```python
# Create a database connection using settings from config file
config='../db/config.ini'

```

--------------------------------

### Add Index to PostgreSQL Table for Performance

Source: https://github.com/mit-lcp/eicu-code/blob/main/website/content/tutorials/install_eicu_locally.md

Example psql command to create an index on the APACHEPATIENTRESULTS table to improve query performance. It first drops the existing index if present and then creates a new index on the PATIENTUNITSTAYID column.

```psql
-- drop the existing index if it exists
DROP INDEX IF EXISTS APACHEPATIENTRESULTS_idx01;

-- create the index
CREATE INDEX APACHEPATIENTRESULTS_idx01
  ON APACHEPATIENTRESULTS (PATIENTUNITSTAYID)
```

--------------------------------

### Create eICU Database Schema in PostgreSQL

Source: https://github.com/mit-lcp/eicu-code/blob/main/website/content/tutorials/install_eicu_locally.md

SQL commands to create a new user, create the eICU database owned by that user, connect to the eICU database, and create the EICU schema if it doesn't already exist. It also sets the default search path to EICU for convenience.

```sql
CREATE USER EICU;
CREATE DATABASE EICU OWNER EICU;
# connect to the database
\c EICU
CREATE SCHEMA IF NOT EXISTS EICU;

SET search_path TO EICU;
```

--------------------------------

### Standard Header for eICU Code

Source: https://github.com/mit-lcp/eicu-code/blob/main/styleguide.md

This is a required header block for all code files in the eICU repository. It includes fields for a title, description, eICU version, and optional references, aiding in code organization and understanding.

```sql
-- ------------------------------------------------------------------
-- Title: Short descriptive title.
-- Description: More detailed description explaining the purpose.
-- eICU version: Version of eICU Collaborative Research Database (e.g. v1.1). 
-- References: References to relevant academic papers etc (optional). 
-- ------------------------------------------------------------------
```

--------------------------------

### Import CSV Data into PostgreSQL Tables

Source: https://github.com/mit-lcp/eicu-code/blob/main/website/content/tutorials/install_eicu_locally.md

Demonstrates two methods for importing CSV data into PostgreSQL tables using the \COPY and COPY commands. The \COPY command imports from the local directory, while COPY allows specifying an absolute path.

```sql
-- Option 1: import with \COPY
\COPY ADMISSIONDX from 'ADMISSIONDX.csv' with DELIMITER ',' CSV HEADER
```

```sql
-- Alternative: import with COPY specifying path
COPY ADMISSIONDX
    FROM '/path/to/file/ADMISSIONDX.csv'
    DELIMITER ','
    CSV HEADER;
```

--------------------------------

### SQL Formatting Conventions

Source: https://github.com/mit-lcp/eicu-code/blob/main/styleguide.md

These are SQL formatting guidelines for the eICU Code Repository. They mandate uppercase for reserved keywords (e.g., SELECT, WHERE) and lowercase for table and column names, following the general principles of sqlstyle.guide.

```sql
SELECT column1, column2
FROM my_table
WHERE column1 = 'some_value';
```

--------------------------------

### Python Formatting Conventions (PEP8)

Source: https://github.com/mit-lcp/eicu-code/blob/main/styleguide.md

This snippet highlights the recommendation to follow PEP8 guidelines for Python code within the eICU repository. PEP8 provides standards for code layout, naming conventions, and comments to enhance readability.

```python
def my_function(param1, param2):
    # This is a comment following PEP8 guidelines
    result = param1 + param2
    return result
```

--------------------------------

### Count Patients in eICU Database (SQL)

Source: https://github.com/mit-lcp/eicu-code/blob/main/website/content/tutorials/install_eicu_locally.md

This SQL query counts the total number of patients stored in the eICU database. It assumes the database is accessible via the 'psql' command-line tool and the 'PATIENTS' table exists.

```sql
select count(PATIENTUNITSTAYID)
from PATIENTS;
```

--------------------------------

### Import Libraries for Data Analysis and Database Connection (Python)

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/apache.ipynb

This snippet imports essential Python libraries for data manipulation (numpy, pandas), visualization (matplotlib, pdvega), database interaction (psycopg2), configuration (configobj), and operating system functionalities. It's a common setup for data analysis projects involving databases.

```python
# 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
```

--------------------------------

### Get Unique Values for Age

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Extracts and sorts the unique values from the 'age' column of the 'patient_tab' DataFrame.

```python
# what are the unique values for age?
age_col = 'age'
patient_tab[age_col].sort_values().unique()
```

--------------------------------

### Makefile Help Command

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/duckdb/README.md

Displays help information for the Makefile used in the eICU database building process. This command lists available targets and their descriptions.

```bash
make help
```

--------------------------------

### Initialize eICU Database User, Database, and Schema

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/postgres/README.md

Initializes the PostgreSQL environment by creating the necessary user, database, and schema for the eICU data. It uses environment variables defined in the Makefile, but allows for custom overrides.

```bash
make initialize
```

```bash
make initialize DBUSER=myusername DBPASS=mypassword
```

--------------------------------

### Build PostgreSQL Database for eICU Data

Source: https://context7.com/mit-lcp/eicu-code/llms.txt

This script automates the process of cloning the repository, downloading eICU data from PhysioNet, and building a local PostgreSQL database. It includes steps for initialization, data loading, indexing, and validation. Requires approved PhysioNet access.

```bash
# Clone the repository
git clone https://github.com/mit-lcp/eicu-code.git
cd eicu-code/build-db/postgres

# Download data from PhysioNet (requires approved access)
make eicu-download physionetuser=your_username datadir=/path/to/data

# Verify data files are present
make eicu-check-gz datadir=/path/to/data

# Initialize PostgreSQL user, database, and schema
make initialize

# Build database with gzipped data (creates tables, loads data, adds indexes, validates)
make eicu-gz datadir=/path/to/data

# Query the database
psql -d eicu -c "SELECT COUNT(*) FROM eicu_crd.patient;"
```

--------------------------------

### Download eICU Data using Makefile

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/duckdb/README.md

Downloads the eICU data from PhysioNet. Requires a PhysioNet username and a specified data directory path. The `make` command handles the download process.

```bash
physionetuser=<PHYSIONETUSER> make eicu-download datadir=<DATA_PATH>
```

--------------------------------

### Build eICU Database with Gzipped Data using Makefile

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/duckdb/README.md

Builds the eICU database in DuckDB by loading gzipped data. This command initiates the process of creating tables, loading data, and validating it.

```bash
make eicu-gz datadir=<DATA_PATH>
```

--------------------------------

### Filter DataFrame Rows by Non-Null 'mvstart' in Pandas

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/cross-reference-apache-treatments.ipynb

This Python code snippet filters a Pandas DataFrame to select only the rows where the 'mvstart' column is not null. This is useful for isolating records with valid mechanical ventilation start times. The output is a subset of the original DataFrame.

```python
df.loc[~df['mvstart'].isnull(),:]
```

--------------------------------

### Query Mechanical Ventilation Data

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/cross-reference-apache-treatments.ipynb

Retrieves mechanical ventilation start and end times for patients from the 'treatment' table. It uses a SQL query to identify treatments related to mechanical ventilation and groups the results by patient unit stay ID. The output is a pandas DataFrame.

```python
query_schema = 'set search_path to public,eicu_crd_phi;'
query = query_schema + """
with t1 as
(
select
    patientunitstayid
    , treatmentoffset
    , case
        when treatmentstring like 'pulmonary|ventilation and oxygenation|mechanical ventilation%' then 1
        when treatmentstring like 'surgery|pulmonary therapies|mechanical ventilation%' then 1
        when treatmentstring like 'toxicology|drug overdose|mechanical ventilation%' then 1
    else 0 end as mechvent
from treatment
)
select 
    patientunitstayid
    , min(treatmentoffset) as mvstart
    , max(treatmentoffset) as mvend
from t1
where mechvent = 1
group by patientunitstayid
order by patientunitstayid
"""

tr = pd.read_sql_query(query, con)
```

--------------------------------

### Build eICU Database with Uncompressed Data using Makefile

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/duckdb/README.md

Builds the eICU database in DuckDB by loading non-compressed data. This command initiates the process of creating tables, loading data, and validating it.

```bash
make eicu datadir=<DATA_PATH>
```

--------------------------------

### Connect to eICU Database (Python)

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/apache.ipynb

Establishes a connection to the eICU database using psycopg2. It supports reading connection details from a configuration file or using default credentials. Handles password prompts if authentication fails.

```python
import os
import getpass
import psycopg2
from configobj import ConfigObj

conn_info = dict()
config_file_path = 'config.ini'  # Assuming config is a path to a file

if os.path.isfile(config_file_path):
    config = ConfigObj(config_file_path)
    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'

print('Database: {}'.format(conn_info['dbname']))
print('Username: {}'.format(conn_info["sqluser"]))

try:
    if conn_info["sqlpass"] == '':
        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"])
    else:
        con = psycopg2.connect(dbname=conn_info["dbname"],
                               host=conn_info["sqlhost"],
                               port=conn_info["sqlport"],
                               user=conn_info["sqluser"],
                               password=conn_info["sqlpass"])
except psycopg2.OperationalError:
    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'] + ';' 
```

--------------------------------

### Extract Basic Patient Demographics (SQL)

Source: https://context7.com/mit-lcp/eicu-code/llms.txt

This SQL query extracts fundamental patient demographics from the eICU-CRD, including age, admission diagnosis, gender, hospital mortality status, and ICU length of stay. It includes a filter to exclude age-masked patients and provides an example of the expected output format.

```sql
-- Execute basic demographics query
SELECT
    pt.patientunitstayid,
    pt.age,
    pt.apacheadmissiondx,
    CASE WHEN pt.gender = 'Male' THEN 1
         WHEN pt.gender = 'Female' THEN 2
         ELSE NULL END AS gender,
    CASE WHEN pt.hospitaldischargestatus = 'Alive' THEN 0
         WHEN pt.hospitaldischargestatus = 'Expired' THEN 1
         ELSE NULL END AS hosp_mortality,
    ROUND(pt.unitdischargeoffset/60) AS icu_los_hours
FROM eicu_crd.patient pt
WHERE pt.age != '> 89'  -- Filter out age-masked patients if needed
ORDER BY pt.patientunitstayid
LIMIT 10;
```

--------------------------------

### Create and Query Pivoted Vital Signs Table - SQL

Source: https://context7.com/mit-lcp/eicu-code/llms.txt

This SQL code snippet defines a process to create a table named 'pivoted_vital' by extracting and cleaning vital signs from the 'nursecharting' table. It handles heart rate, O2 saturation, non-invasive blood pressure, and temperature, applying validation to ensure data falls within expected physiological ranges. It also includes an example query to retrieve vital signs for a specific patient within the first 24 hours.

```sql
-- Create pivoted vital signs table
DROP TABLE IF EXISTS pivoted_vital CASCADE;
CREATE TABLE pivoted_vital AS
WITH nc AS (
    SELECT
        patientunitstayid,
        nursingchartoffset,
        nursingchartentryoffset,
        CASE WHEN nursingchartcelltypevallabel = 'Heart Rate'
             AND nursingchartcelltypevalname = 'Heart Rate'
             AND nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
             AND nursingchartvalue NOT IN ('-','.')
             THEN cast(nursingchartvalue AS numeric)
             ELSE NULL END AS heartrate,
        CASE WHEN nursingchartcelltypevallabel = 'O2 Saturation'
             AND nursingchartcelltypevalname = 'O2 Saturation'
             AND nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
             THEN cast(nursingchartvalue AS numeric)
             ELSE NULL END AS o2saturation,
        CASE WHEN nursingchartcelltypevallabel = 'Non-Invasive BP'
             AND nursingchartcelltypevalname = 'Non-Invasive BP Systolic'
             AND nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
             THEN cast(nursingchartvalue AS numeric)
             ELSE NULL END AS nibp_systolic,
        CASE WHEN nursingchartcelltypevallabel = 'Temperature'
             AND nursingchartcelltypevalname = 'Temperature (C)'
             AND nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
             THEN cast(nursingchartvalue AS numeric)
             ELSE NULL END AS temperature
    FROM nursecharting
    WHERE nursingchartcelltypecat IN ('Vital Signs','Scores','Other Vital Signs and Infusions')
)
SELECT
    patientunitstayid,
    nursingchartoffset AS chartoffset,
    avg(CASE WHEN heartrate >= 25 AND heartrate <= 225 THEN heartrate ELSE NULL END) AS heartrate,
    avg(CASE WHEN o2saturation >= 0 AND o2saturation <= 100 THEN o2saturation ELSE NULL END) AS spo2,
    avg(CASE WHEN nibp_systolic >= 25 AND nibp_systolic <= 250 THEN nibp_systolic ELSE NULL END) AS nibp_systolic,
    avg(CASE WHEN temperature >= 25 AND temperature <= 46 THEN temperature ELSE NULL END) AS temperature
FROM nc
WHERE heartrate IS NOT NULL OR o2saturation IS NOT NULL
      OR nibp_systolic IS NOT NULL OR temperature IS NOT NULL
GROUP BY patientunitstayid, nursingchartoffset
ORDER BY patientunitstayid, nursingchartoffset;

-- Query vital signs for specific patient
SELECT * FROM pivoted_vital
WHERE patientunitstayid = 141765
  AND chartoffset BETWEEN 0 AND 1440  -- First 24 hours
ORDER BY chartoffset;
```

--------------------------------

### Establish PostgreSQL Database Connection with Configuration

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/infusiondrug.ipynb

This Python code establishes a connection to a PostgreSQL database. It first attempts to read connection details (username, password, host, port, database name, schema) from a configuration file ('../db/config.ini'). If the file is not found, it defaults to a predefined set of connection parameters. The code also includes logic to prompt for a password if it's missing and attempts to connect using different authentication methods. Finally, it sets the search path for the database.

```python
# 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'] + ';'
```

--------------------------------

### Configure Database Connection using config.ini in Python

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/allergy.ipynb

Establishes a connection to the eICU database using settings from a `config.ini` file. If the configuration file is not found, it falls back to default connection parameters. This snippet handles secure password input if necessary and sets the database search path.

```python
# 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'] + ';' 
```

--------------------------------

### Python: Establish Database Connection for eICU

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/intakeoutput.ipynb

This Python code configures and establishes a connection to the eICU database using psycopg2. It prioritizes loading connection details from a local 'config.ini' file. If the file is not found, it falls back to default connection parameters and prompts for a password if necessary. The 'search_path' is then set to include specified schemas.

```python
# 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'] + ';'
```

--------------------------------

### Build DuckDB Database for eICU Data

Source: https://context7.com/mit-lcp/eicu-code/llms.txt

This script facilitates the creation of a portable DuckDB database from the eICU data. It automates downloading, integrity checks, and building the database file, optimized for analytical queries and easier deployment. Assumes data may already be downloaded.

```bash
# Navigate to DuckDB build directory
cd eicu-code/build-db/duckdb

# Download data (if not already downloaded)
make eicu-download physionetuser=your_username datadir=/path/to/data

# Check data integrity
make eicu-check-gz datadir=/path/to/data

# Build DuckDB database (creates tables, loads data, validates)
make eicu-gz datadir=/path/to/data

# Query using DuckDB CLI
duckdb eicu.duckdb "SELECT COUNT(*) FROM patient;"
```

--------------------------------

### Display First Rows of Patient Table

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Shows the first five rows of the 'patient_tab' DataFrame using the `.head()` method, providing a glimpse of the loaded patient data.

```python
# display the first few rows of the dataframe
patient_tab.head()
```

--------------------------------

### Clone eICU Repository using Git

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/duckdb/README.md

Clones the eICU code repository from GitHub to a local directory. This is a prerequisite for accessing the database building scripts.

```bash
git clone https://github.com/mit-lcp/eicu-code.git
```

--------------------------------

### Generate PostgreSQL Table Partitioning Statements

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/postgres/README-partition.md

This script generates SQL statements to create partitions for the 'vitalperiodic' table. It calculates cumulative observation counts and determines partition ranges using window functions. The output is a series of `CREATE TABLE ... PARTITION OF ...` statements, suitable for dynamic table partitioning in PostgreSQL 10.0+.

```sql
with t1 as
(
  select
    patientunitstayid
    , numobs
    , (SUM(numobs) OVER (order by patientunitstayid)) as numobs_cumulative
  from count_vitalperiodic
)
, t2 as
(
  select
  patientunitstayid
  , numobs_cumulative
  , floor( (numobs_cumulative)/10000000) as rn
  , lead( floor((numobs_cumulative)/10000000) ) OVER (order by patientunitstayid) as rn_lead
  from t1
)
, t3 as
(
  select
     lag(patientunitstayid) over (order by patientunitstayid) as pid_lower
    , patientunitstayid as pid_upper
    , numobs_cumulative
    , rn
    , rn_lead
  from t2
  where rn != rn_lead
)
-- next two staging tables add in an upper limit of (pid_lower = highest pid, pid_upper = 9999999999)
, last_row1 as
(
  select
    pid_upper as pid_lower
    , 999999999 as pid_upper
    -- note we add 1 to rn_lead to that this is the highest partition value
    , rn_lead+1 as rn_lead
    , ROW_NUMBER() over (order by rn_lead desc) as rn_last_row
  from t3
)
, last_row2 as
(
  select
    last_row1.*
  from last_row1
  where rn_last_row = 1
)
, t4 as
(
  select coalesce(t3.pid_lower, 0) as pid_lower, t3.pid_upper, t3.rn_lead from t3
  UNION
  select lr.pid_lower, lr.pid_upper, lr.rn_lead from last_row2 lr
)
select
  'CREATE TABLE vitalperiodic_' || cast(rn_lead as text) || ' PARTITION OF vitalperiodic FOR VALUES FROM (' || cast(pid_lower as text) || ') TO (' || cast(pid_upper as text) || ');' as txt
from t4
order by rn_lead;
```

--------------------------------

### Connect to SQLite Database

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Establishes a connection to a SQLite database file named 'eicu_demo.sqlite3' located in the current directory and creates a cursor object for executing SQL queries.

```python
# Connect to the database - which is assumed to be in the current directory
fn = 'eicu_demo.sqlite3'
con = sqlite3.connect(fn)
cur = con.cursor()
```

--------------------------------

### Owl Carousel Initialization with jQuery

Source: https://github.com/mit-lcp/eicu-code/blob/main/website/themes/eicu-data/layouts/index.html

This JavaScript snippet initializes the Owl Carousel plugin to create a responsive image carousel. It configures options such as looping, margins, navigation, autoplay with hover pause, and defines responsive item counts for different screen sizes. This is commonly used for displaying a set of images or content blocks in a sliding format.

```javascript
$('.owl-carousel').owlCarousel({
  loop:true,
  margin:10,
  nav:true,
  autoplay:true,
  autoplayHoverPause:true,
  autoplayTimeout:3000,
  responsive:{
    0:{
      items:1
    },
    1000:{
      items:3
    },
  }
})
```

--------------------------------

### Create count_vitalperiodic table

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/postgres/README-partition.md

This SQL snippet creates a new table named 'count_vitalperiodic' by counting the number of observations for each 'patientunitstayid' in the 'vitalperiodic' table. This is a preparatory step for determining table partitions.

```sql
create table count_vitalperiodic as (
  select patientunitstayid, count(*) as numobs 
  from vitalperiodic 
  group by patientunitstayid 
  order by patientunitstayid
);
```

--------------------------------

### Import Libraries for Data Analysis

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Imports essential Python libraries for data manipulation (pandas), visualization (matplotlib), database interaction (psycopg2, sqlite3), and operating system functionalities (os).

```python
# Import libraries
import pandas as pd
import matplotlib.pyplot as plt
import psycopg2
import os
import sqlite3
```

--------------------------------

### Describe Admission Weights

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Calculates and displays descriptive statistics for the 'admissionweight' column after outlier handling.

```python
# describe the admission weights
patient_tab[adweight_col].describe()
```

--------------------------------

### Import Libraries for eICU Data Analysis

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/03-plot-timeseries.ipynb

Imports essential Python libraries for data manipulation (pandas), plotting (matplotlib.pyplot), database interaction (psycopg2), and operating system functionalities (os). These are foundational for accessing and visualizing the eICU data.

```python
# Import libraries
import pandas as pd
import matplotlib.pyplot as plt
import psycopg2
import os
```

--------------------------------

### Select Specific Columns from Patient Table

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Selects and displays the first five rows of a subset of columns ('uniquepid', 'patientunitstayid', 'gender', 'age', 'unitdischargestatus') from the 'patient_tab' DataFrame.

```python
# select a limited number of columns to view
columns = ['uniquepid', 'patientunitstayid','gender','age','unitdischargestatus']
patient_tab[columns].head()
```

--------------------------------

### Import Libraries for Data Analysis and Database Connection

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/nursecharting.ipynb

This snippet imports necessary Python libraries for data manipulation (numpy, pandas), plotting (matplotlib), database interaction (psycopg2), password handling (getpass), configuration file parsing (configobj), and operating system interactions (os). It sets up the environment for data analysis and database connectivity.

```python
# 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
```

--------------------------------

### Query Hospitals with Admission Drug Data using SQL and Python

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/admissiondrug.ipynb

This code snippet retrieves a list of hospitals along with the count of patients and the count of patients who have admission drug records. It joins the 'patient' and 'admissiondrug' tables and groups the results by hospital ID. The results are then processed in Python to calculate data completion percentages.

```python
query = query_schema + ""
select 
  pt.hospitalid
  , count(pt.patientunitstayid) as number_of_patients
  , count(ad.patientunitstayid) as number_of_patients_with_admdrug
from patient pt
left join admissiondrug ad
  on pt.patientunitstayid = ad.patientunitstayid
group by pt.hospitalid
"""replace(
    patientunitstayid
)

df = pd.read_sql_query(query, con)
df['data completion'] = df['number_of_patients_with_admdrug'] / df['number_of_patients'] * 100.0
df.sort_values('number_of_patients_with_admdrug', ascending=False, inplace=True)
df.head(n=10)
```

--------------------------------

### Load and Display Admission Diagnosis Data (Python)

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/02-demographics-and-severity-of-illness.ipynb

Executes the SQL query to fetch data from the 'admissiondx' table into a pandas DataFrame and displays the first few rows using `.head()`. This allows for initial review of admission diagnosis information.

```python
# run the query and assign the output to a variable
admissiondx = pd.read_sql_query(query,con)
admissiondx.head()
```

--------------------------------

### Load Lab Data using Pandas

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/03-plot-timeseries.ipynb

Executes a SQL query using pandas to load data from the 'lab' table into a DataFrame. It requires a pre-defined 'query' string and a database connection object 'con'.

```python
# run the query and assign the output to a variable
lab = pd.read_sql_query(query,con)
```

--------------------------------

### Plot Distribution of Weight Changes

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Generates a histogram to visualize the distribution of the calculated 'weight_change' column.

```python
# plot the weight changes
figsize = (18,8)
patient_tab['weight_change'].plot(kind='hist',
                             figsize=figsize,
                             fontsize=fontsize,
                             bins=50)
```

--------------------------------

### Display First Few Rows of Lab Dataframe

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/03-plot-timeseries.ipynb

Displays the first few rows of the 'lab' DataFrame using the .head() method. This is useful for a quick inspection of the loaded data.

```python
# display the first few rows of the dataframe
lab.head()
```

--------------------------------

### Query eICU Data for Patient Intake/Output Availability (Python)

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/intakeoutput.ipynb

This Python script queries the eICU database to calculate the percentage of patients with intake and output data for each hospital. It uses pandas for data manipulation and SQL for querying. The output is a sorted DataFrame showing hospital IDs, patient counts, and data completion percentages.

```python
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 intakeoutput 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)
```

--------------------------------

### Find Tetracycline Allergies by Drug Name (SQL)

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/allergy.ipynb

This query searches for Tetracycline allergies by checking the `drugname` column using a case-insensitive partial match. It reports the number of unit stays and lists the counts for each `drugname`. Requires `query_schema` and `con`.

```python
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()
```

--------------------------------

### Calculate Weight Change

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Computes the difference between 'admissionweight' and 'dischargeweight' and stores it in a new column named 'weight_change'.

```python
patient_tab['weight_change'] = patient_tab[adweight_col] - patient_tab[disweight_col]
```

--------------------------------

### List Columns of Patient Table

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Retrieves and displays the names of all columns present in the 'patient_tab' DataFrame.

```python
# list all of the columns in the table
patient_tab.columns
```

--------------------------------

### Calculating Hospital Data Completion in Python

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/nursecharting.ipynb

This SQL query and pandas script calculates the percentage of patients in each hospital that have data available in the 'nursecharting' table. It joins the 'patient' table with a subquery counting distinct patient unit stays from 'nursecharting'. The results are then sorted by data completion and displayed. Requires pandas and a SQL connection.

```python
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)
```

--------------------------------

### Query Patient Table Data

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Constructs and prints an SQL query to select all columns from the 'patient' table. This query is intended to load patient data for further analysis.

```python
# query to load data from the patient table
query =
"""
SELECT *
FROM patient
"""

print(query)
```

--------------------------------

### Examine Single Patient Drug Records (Python)

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/admissiondrug.ipynb

This snippet retrieves all drug administration records for a specific patient unit stay ID from the 'admissiondrug' table. It then displays the first few records and demonstrates how to select and view a subset of columns. The code assumes 'query_schema' and a database connection 'con' are pre-defined.

```python
patientunitstayid = 2704494
```

```python
query = query_schema + """
select *
from admissiondrug
where patientunitstayid = {}
order by drugoffset
""".format(patientunitstayid)

df = pd.read_sql_query(query, con)
df.head()
```

```python
# Look at a subset of columns
cols = ['admissiondrugid','patientunitstayid','drugoffset','drugenteredoffset','drugname','drughiclseqno']
df[cols].head()
```

--------------------------------

### Load Patient Table into Pandas

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Executes the SQL query to retrieve all data from the 'patient' table and loads it into a pandas DataFrame named 'patient_tab'.

```python
# run the query and assign the output to a variable
patient_tab = pd.read_sql_query(query,con)
```

--------------------------------

### Configure Plotting Settings

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Sets up plotting aesthetics for matplotlib, including enabling inline plots and configuring font sizes for better readability of visualizations.

```python
# Plot settings
%matplotlib inline
plt.style.use('ggplot')
fontsize = 20 # size for x and y ticks
plt.rcParams['legend.fontsize'] = fontsize
plt.rcParams.update({'font.size': fontsize})
```

--------------------------------

### Establish PostgreSQL Database Connection

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/hospital.ipynb

Configures and establishes a connection to a PostgreSQL database, likely used for the eICU data. It supports reading connection details from a configuration file or using default values. Error handling is included for password authentication.

```python
# 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'] + ';' 
```

--------------------------------

### Determine partition boundaries using cumulative counts

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/postgres/README-partition.md

This SQL query identifies the 'patientunitstayid' values that will serve as boundaries for table partitions. It calculates cumulative observation counts and assigns a partition number ('rn') based on a target of 10 million rows per partition. It identifies rows where the partition number changes, indicating a boundary.

```sql
with t1 as
(
  select
    patientunitstayid
    , numobs
    , (SUM(numobs) OVER (order by patientunitstayid)) as numobs_cumulative
  from count_vitalperiodic
)
, t2 as
(
  select
  patientunitstayid
  , numobs_cumulative
  , floor( (numobs_cumulative)/10000000) as rn
  , lead( floor((numobs_cumulative)/10000000) ) OVER (order by patientunitstayid) as rn_lead
  from t1
)
select
  patientunitstayid
  , numobs_cumulative
  , rn
  , rn_lead
from t2
where rn != rn_lead
order by patientunitstayid;
```

--------------------------------

### Describe Discharge Weights

Source: https://github.com/mit-lcp/eicu-code/blob/main/notebooks/demo/01-explore-patient-table.ipynb

Calculates and displays descriptive statistics for the 'dischargeweight' column after outlier handling.

```python
# describe the discharge weights
patient_tab[disweight_col].describe()
```

--------------------------------

### Generate Partitioned Tables for vitalperiodic in PostgreSQL

Source: https://github.com/mit-lcp/eicu-code/blob/main/build-db/postgres/README-partition.md

This SQL script generates `CREATE TABLE` statements for partitioning the `vitalperiodic` table in PostgreSQL 9.4. It calculates cumulative observation counts and divides them into partitions, creating new tables that inherit from the main `vitalperiodic` table. The partitioning is based on ranges of `patientunitstayid`.

```sql
with t1 as
(
  select
    patientunitstayid
    , numobs
    , (SUM(numobs) OVER (order by patientunitstayid)) as numobs_cumulative
  from count_vitalperiodic
)
, t2 as
(
  select
  patientunitstayid
  , numobs_cumulative
  , floor( (numobs_cumulative)/10000000) as rn
  , lead( floor((numobs_cumulative)/10000000) ) OVER (order by patientunitstayid) as rn_lead
  from t1
)
, t3 as
(
  select
     lag(patientunitstayid) over (order by patientunitstayid) as pid_lower
    , patientunitstayid as pid_upper
    , numobs_cumulative
    , rn
    , rn_lead
  from t2
  where rn != rn_lead
)
-- next two staging tables add in an upper limit of (pid_lower = highest pid, pid_upper = 9999999999)
, last_row1 as
(
  select
    pid_upper as pid_lower
    , 999999999 as pid_upper
    -- note we add 1 to rn_lead to that this is the highest partition value
    , rn_lead+1 as rn_lead
    , ROW_NUMBER() over (order by rn_lead desc) as rn_last_row
  from t3
)
, last_row2 as
(
  select
    last_row1.*
  from last_row1
  where rn_last_row = 1
)
, t4 as
(
  select coalesce(t3.pid_lower, 0) as pid_lower, t3.pid_upper, t3.rn_lead from t3
  UNION
  select lr.pid_lower, lr.pid_upper, lr.rn_lead from last_row2 lr
)
select
  'CREATE TABLE vitalperiodic_' || cast(rn_lead as text) || ' ( CHECK ( patientunitstayid >= ' || cast(pid_lower as text) || ' AND patientunitstayid < ' || cast(pid_upper as text) || ' )) INHERITS vitalperiodic;' as txt
from t4
order by rn_lead;
```