# Tidy-data & SQL with the MIMIC Dataset

## The Dataset

MIMIC (Medical Information Mart for Intensive Care) is a large dataset of patients who stayed in critical care units of the Beth Israel Deaconess Medical Center between 2001 and 2012. You can read more about the dataset [here](
https://mimic.physionet.org/). In this crash-course, we will use a small synthetic version of the dataset that contains 100 patients.

## Tidy Data and Relational Database Tables
During the DSI summer prep program, many of you [discovered](https://mathigon.org/course/intro-data-pipeline/wrangling) that tidy datasets are easy to manipulate, model, and visualize. Each has a specific structure: each variable is a column, each observation is a row, and each type of observational unit is a table.

The Data Scientist [Hadley Wickham](https://en.wikipedia.org/wiki/Hadley_Wickham) coined the idea of tidy-data to describe tables in this format, but the idea has been around for many years.

Below is a table containing information about drugs and their NDC (National Drug Code). Each drug can be in either tablet or capsule form, corresponding to distinct NDCs.

One way to represent the data might be:

| drug_name | tablet_ndc | capsule_ndc |
| :------------- | :---------- | :----------- |
| Aspirin | 00904404073 | 00904404074 |
| Bisacodyl | 00536338101 | NULL |
| Heparin | 63323026201 | 63323026202 |

However, this representation violates tidy data principles because each row is *two* observations instead of one. As tidy data, this table would look like

| drug_name | drug_delivery | ndc |
| :------------- | :---------- | :----------- |
| Aspirin | TAB | 00904404073 |
| Aspirin | CAP | 00904404074 |
| Bisacodyl | TAB | 00536338101 |
| Heparin | TAB | 63323026201 |
| Heparin | CAP | 63323026202 |

This example is an oversimplified version of the MIMIC Relational Database [prescriptions table](https://mimic.physionet.org/mimictables/prescriptions/). Most, if not all, of the tables in the MIMIC dataset are arranged in tidy-format. In general, well designed relational databases also have tables in this format. Organizing data in this way tends to make things like querying, EDA, and data cleaning easy. It also allows more flexibility for changes; for example, in the second table above, adding additional drug_delivery types (for example, an INJECTION and IV) is trivial, whereas the former table would need additional columns added to it.

Instead of coming up with different datasets with different representations, keeping things in tidy-format allows the same approach to be used in many different use cases. If there were many more drug delivery types in the above example, the first representation would become bloated with many columns and brittle since the columns' number and order may impact applications that use the database.

You can read more about tidy data in Hadley Wickam's [originial paper](https://vita.had.co.nz/papers/tidy-data.pdf) in which he proposed the concept or [this summary article](https://cran.r-project.org/web/packages/tidyr/vignettes/tidy-data.html).

SQL uses a different approach to Wickam's verbs for tidy data selection and transformation, but offers all the same functionality.

## First Steps

### Installing Postgres

We will be using PostgreSQL, a popular database management system. PostgreSQL client `pgsql` is already installed in gcloud-shell. For the material below and **hw6-sql-mimic**, you will be connecting to a PostgresSQL database server that we are hosting. However, if you want to run a local copy, you can download PostgreSQL [here](https://www.postgresql.org/download/).

### Using Postgres

There are three ways one typically interacts directly with a database.
1. Via a command-line-interface. This interface for Postgres is named [psql](https://www.postgresql.org/docs/9.3/app-psql.html). Almost all databases have a simple CLI.
2. Using a rich database-client with a [GUI](https://en.wikipedia.org/wiki/Graphical_user_interface), that allows easy review of database properties and query development. [pgAdmin](https://www.pgadmin.org/) is a popular tool for Postgres.
4. A language-specific software development kit (SDK). This refers to accessing a Postgres database from your code. In Python, a popular library (SDK) for connecting is [psycopg](https://www.psycopg.org/)

Options 1 and 2 provide an easy interactive way to inspect and explore a database and perform most database administration tasks. Below and in the homework, we will use them to explore the MIMIC dataset.

### Connecting to the DATA1050 database

#### psql
1. Run the following command and check that the `psql` client is installed. If it is not already installed, check out [this guide](https://blog.timescale.com/tutorials/how-to-install-psql-on-mac-ubuntu-debian-windows/). Note: gcloud-shell has psql preinstalled.

2. Run the following command to connect to the database. 

psql --host=data1050.cdzzrqlrfv5z.us-east-1.rds.amazonaws.com --port=5432 \
--dbname=mimic --username=student

3. Enter `data1050` as your password. You should now see a REPL with a `mimic=>` prompt:

psql (13.0 (Debian 13.0-1.pgdg100+1), server 11.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

mimic=>

4. Enter the query below at the psql prompt `mimic=>` (do not forget the `;` at the end).

SELECT * FROM patients;

The result should look similar to this:
![](https://i.imgur.com/04rJ3Ms.png)
The prompt **`:`** at the end of displayed patients rows indicates you are now in the REPL for a different program called [`less`](https://en.wikipedia.org/wiki/Less_(Unix)). You can type `q` to exit out of that REPL and return to the psql REPL `=>`.  However, before you go, please note: you do many other [wonderful things](https://en.wikipedia.org/wiki/Less_(Unix)#Frequently_used_commands) from the less prompt `:` It allows one to search for patterns and easily navigate through large listings and files.
    
>*Note:* REPL stands for Read-Eval-Print Loop and is an interactive environment that allows one to type commands and see their results. E.g., the bash prompt, psql, ipython are all examples of REPLs! You can read more about REPLs [here](https://en.wikipedia.org/wiki/Read%E2%80%93eval%E2%80%93print_loop).

5. **Working with .sql files** You can also write your queries in .sql files and run them via the include command `\i`
Below is a small example:

mimic=> \i my_sql_query.sql
row_id | subject_id | gender | dob | dod | dod_hosp | dod_ssn | expire_flag
--------+------------+--------+---------------------+---------------------+---------------------+---------------------+-------------
9467 | 10006 | F | 2094-03-05 00:00:00 | 2165-08-12 00:00:00 | 2165-08-12 00:00:00 | 2165-08-12 00:00:00 | 1
9472 | 10011 | F | 2090-06-05 00:00:00 | 2126-08-28 00:00:00 | 2126-08-28 00:00:00 | | 1
9474 | 10013 | F | 2038-09-03 00:00:00 | 2125-10-07 00:00:00 | 2125-10-07 00:00:00 | 2125-10-07 00:00:00 | 1
(3 rows)

Where my_sql_query.sql contains the following lines:

-- Multiline expressions and comments, all work, Whoo!
select * from patients
limit 3;

You can also run a file directly from the command line:

psql --host=data1050.cdzzrqlrfv5z.us-east-1.rds.amazonaws.com --port=5432 \
--username=student --dbname=mimic \
--file my_sql_query.sql

You can [store pgsql passwords](https://www.postgresql.org/docs/13/libpq-pgpass.html) to avoid being prompted for them all of the time. For example, after running the following terminal commands in Unix (and on the Mac), you will no longer be prompted for a password when you run the above `psql` commands.

echo "\
#hostname:port:database:username:password
data1050.cdzzrqlrfv5z.us-east-1.rds.amazonaws.com:5432:mimic:student:data1050
" > ~/.pgpass
chmod 0600 ~/.pgpass

 
However, always BE CAREFUL and AVOID exposing .pgpass files (for example, via a push to a public git repo!).


Here are some additional `psql` commands to get started: 
   * `q` exit to exit a `:` prompt
   * `\dt` to list all tables in the database 
   * `\?` to display all available slash `\` commands
   * `\h` to display help on all available SQL commands
   * `\h XYZ` to display help on the SQL command XYZ
   * `\q` to ?quit using psql from the `=>` prompt


#### pgAdmin

1. Follow the instructions [here](https://www.pgadmin.org/) to install and launch pgAdmin.
2. Under the Servers dropdown, select Create -> Server. 

![](https://i.imgur.com/YGwvh3p.png)

3. Enter a nick-name for the server (this can be any name of your choice). 

![](https://i.imgur.com/Ibu28Oe.png =300x)

4. Navigate to the Connections tab and enter the following credentials: 
Host: data1050.cdzzrqlrfv5z.us-east-1.rds.amazonaws.com
Port: 5432
Username: student
Password: data1050

![](https://i.imgur.com/h2ZJP7V.png =300x)

5. Click Save. You should now see the new server on the left with a mimic database and some other databases. You can view the tables in the database under mimic → Schemas → public → Tables. 
6. pgAdmin is a powerful tool with many features; you can read more about them [here](https://www.pgadmin.org/docs/pgadmin4/4.25/index.html). We will focus on the most critical feature: the Query Tool. Right-click the mimic database and select Query Tool. 

![](https://i.imgur.com/Hle7eIr.png)

7. In the Query Tool window, you can type in any SQL query and execute it by clicking the top menu bar's play button. 

## SQL Crashcourse by Examples
Please try out these queries with either the psql REPL or the pgAdmin Query Tool. 

### SQL basics

#### SELECT
SELECT statements in SQL allow us to retrieve data from a database table. A basic SQL query has the following format.

SELECT <columns> [mandatory]
FROM <table> [mandatory]

- Show the id, date of birth, and gender of all patient records

SELECT subject_id, dob, gender FROM patients

- Show records of all ICU stays (* allows us to select *all* columns)

SELECT * FROM icustays

    *Note:* In a real application, you should control what your queries return and avoid using * as a rule. For example, say your table currently has 3 columns, and you use * to retrieve all 3 columns. However, if another column gets added, your application will break because of the assumption that there are 3 columns. 

#### WHERE
The WHERE clause filters records that satisfy a specified condition.

- Find the patient record for patient with id 10011

SELECT * FROM patients
WHERE subject_id = 10011;

- Show records of all female patients

SELECT * FROM patients
WHERE gender = 'F';

#### ORDER BY
The ORDER BY keyword sorts returned records. Ascending order is the default; use the DESC keyword to sort in descending order. 

- Show all patient admissions sorted by admission time (with most recent admissions at the top). 

SELECT * FROM admissions
ORDER BY admittime DESC;

#### LIMIT
The LIMIT keyword limits the number of records returned. 
- Show the top 5 most recent admissions.

SELECT * FROM admissions
ORDER BY admittime DESC
LIMIT 5;

### Working with Multiple Tables

#### JOIN

The JOIN clause combines information from two tables based on a related column between them.

- Show the patient subject_id, admission time, diagnosis, and patient date of birth for each admission record. 

    This example requires us to combine the admissions table with the patients table because admission time and diagnosis are columns in the admissions table. However, date of birth is a column in the patients table. 

SELECT patients.subject_id, admissions.admittime, admissions.diagnosis, patients.dob FROM admissions
INNER JOIN patients
ON admissions.subject_id = patients.subject_id;

    In this example, we used INNER JOIN to return rows with subject_id that exist in *both* tables. You can read about other types of JOINs [here](https://www.w3schools.com/sql/sql_join.asp).

    *Note:* It is generally considered good practice to qualify all column names when writing queries with JOINs in order to avoid duplicate column names. 

One useful technique is to create [alaises](https://www.w3schools.com/sql/sql_alias.asp) for tables to avoid repeatedly typing table names. So, we can simplify the query above to:

SELECT p.subject_id, a.admittime, a.diagnosis, p.dob FROM admissions a
INNER JOIN patients p
ON a.subject_id = p.subject_id;

### Aggregation Functions

The most common aggregate functions are SUM, COUNT, and AVG. These functions produce summary data based on multiple rows.

- Find the total number of admissions

SELECT COUNT(row_id) FROM admissions;

- Find the total number of female patients

SELECT COUNT(row_id) FROM patients
WHERE gender = 'F';

We can use the GROUP BY clause combined with the aggregate functions to produce summary rows given specific columns. 

- Find the number of admissions for each admission_type

SELECT admission_type, COUNT(row_id) FROM admissions
GROUP BY admission_type;

Furthermore, we can filter our output with a condition using the HAVING clause.

- Find the number of admissions with withh admission_type = "EMERGENCY"

SELECT COUNT(row_id) FROM admissions
GROUP BY admission_type
HAVING admission_type = 'EMERGENCY';

### Working with NULLs
Missing values are ubiquitous in real-world data. In SQL, we can use the `IS NULL` and `IS NOT NULL` operators when working with NULLs.

- Count the number of patients that died in the hospital.

SELECT COUNT(subject_id) FROM patients
WHERE dod_hosp IS NOT NULL;

### Working with Dates
The `CAST` keyword converts values to a desired type. For example, we can convert the timestamps to dates (i.e., remove the hours and minutes parts and just keep the year, month, and date part). 

- Find the hadm_id and admission date for each admission record

SELECT hadm_id, CAST(admissions.admittime as date) AS admission_date
FROM admissions;

- Select the id of the 10 youngest patients

SELECT subject_id, CAST(dob as date) AS dob FROM patients
ORDER BY dob DESC
LIMIT 10;

### Advanced Topics

#### WITH
When writing complex queries, we often want to create temp tables for reference later within the main query. The [WITH clause](https://www.geeksforgeeks.org/sql-with-clause/) allows us to do this and write queries that are more organized and readable.

- For each patient, show their subject_id and their first admission time

WITH first_admissions AS
(SELECT subject_id, MIN(CAST(admissions.admittime AS date)) AS first_admission_time
FROM admissions
GROUP BY subject_id)
SELECT subject_id, first_admission_time FROM first_admissions;

    Since each patient may have multiple admission records, we need to use the `GROUP BY` statement to create a first admissions table. Using the `WITH` clause, we can conveniently reference the table of first admissions any time in our main query. 
    
#### CASE

`CASE` statements allow us to use conditional values in our queries. They have the following structure

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

If no conditions are true, it returns the value in the ELSE clause.

- Group the patients by the number of times they have been admitted to the hospital. 

WITH visit_counts AS
(SELECT subject_id, COUNT(*) AS visit_count
FROM admissions
GROUP BY subject_id)
SELECT CASE
WHEN visit_count = 1 THEN '1 visit'
WHEN visit_count = 2 THEN '2 visits'
WHEN visit_count > 2 THEN '>2 visits'
END AS visit_count_group,
COUNT(subject_id)
FROM visit_counts
GROUP BY visit_count_group;