<commit_before>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to the MIMIC database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is the MIMIC Critical Care Database?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MIMIC-III is an freely available relational database developed by the MIT Lab for Computational Physiology, comprising deidentified health data associated with >40,000 critical care patients. It includes demographics, vital signs, laboratory tests, medications, and more. MIMIC-III is used widely around the world in academic research, education, and industry. For further information, see: https://mimic.physionet.org/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Workshop overview "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "During the workshop, you will:\n",
    "\n",
    "- Learn about MIMIC-III, the publicly accessible critical care database \n",
    "- Create a local version of MIMIC-III with a small sample of patients using the Firefox SQLite Plugin\n",
    "- Explore the patient data using SQL\n",
    "- Plot and analyse the data using Python\n",
    "- Get inspiration for future research projects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set up a mini version of MIMIC-III on your computer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- MIMIC-III contains over 40,000 patients, but for the workshop we will be working with a subset of 9 patients. \n",
    "- To create the database on your computer, you will need to install Firefox and the Firefox SQLite Manager Add-on. Open Firefox, select \"Add-ons\" from the Tools menu, and then install SQLite Manager.\n",
    "- After restarting Firefox, select \"SQLite Manager\" from the tools menu. In SQLite Manager, click \"Connect Database\" in the menu, and select the \"data/mimicdata.sqlite\" database file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Start exploring the data with SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- SQL stands for \"structured query language\". It is the standard language used for querying relational databases, which are databases comprising of several tables linked together by IDs.\n",
    "- Queries are"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-1-0d3ffd1beb2f>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-1-0d3ffd1beb2f>\"\u001b[0;36m, line \u001b[0;32m1\u001b[0m\n\u001b[0;31m    SELECT *\u001b[0m\n\u001b[0m            ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "SELECT *\n",
    "FROM patients;\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "WHERE gender = 'F';\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "WHERE gender = 'F'\n",
    "AND subject_id = 40080;\n",
    "\n",
    "SELECT *\n",
    "FROM patients;\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id;\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F';\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND subject_id = 40080;\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "SELECT patients.*\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "\n",
    "SELECT admissions.*\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "SELECT patients.DOB, admissions.*\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "\n",
    "SELECT pat.DOB, adm.*\n",
    "FROM patients pat\n",
    "INNER JOIN admissions adm\n",
    "ON pat.subject_id = adm.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND pat.subject_id = 40080;\n",
    "\n",
    "\n",
    "SELECT *\n",
    "FROM chartevents\n",
    "WHERE subject_id = 40080;\n",
    "\n",
    "SELECT ce.*\n",
    "FROM chartevents ce\n",
    "WHERE subject_id = 40080;\n",
    "\n",
    "```sql\n",
    "SELECT ce.*, di.label\n",
    "FROM chartevents ce\n",
    "INNER JOIN d_items di\n",
    "  ON ce.itemid = di.itemid\n",
    "WHERE subject_id = 40080;\n",
    "```\n",
    "\n",
    "names=['row_id','subject_id','hadm_id','icustay_id','itemid','charttime','storetime','cgid','value','valuenum','uom','warning','error','resultstatus','stopped']\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {<commit_msg>update sql<commit_after>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to the MIMIC database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is the MIMIC Critical Care Database?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MIMIC-III is an freely available relational database developed by the MIT Lab for Computational Physiology, comprising deidentified health data associated with >40,000 critical care patients. It includes demographics, vital signs, laboratory tests, medications, and more. MIMIC-III is used widely around the world in academic research, education, and industry. For further information, see: https://mimic.physionet.org/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Workshop overview "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "During the workshop, you will:\n",
    "\n",
    "- Learn about MIMIC-III, the publicly accessible critical care database \n",
    "- Create a local version of MIMIC-III with a small sample of patients using the Firefox SQLite Plugin\n",
    "- Explore the patient data using SQL\n",
    "- Plot and analyse the data using Python\n",
    "- Get inspiration for future research projects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set up a mini version of MIMIC-III on your computer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- MIMIC-III contains over 40,000 patients, but for the workshop we will be working with a subset of 9 patients. \n",
    "- To create the database on your computer, you will need to install Firefox and the Firefox SQLite Manager Add-on. Open Firefox, select \"Add-ons\" from the Tools menu, and then install SQLite Manager.\n",
    "- After restarting Firefox, select \"SQLite Manager\" from the tools menu. In SQLite Manager, click \"Connect Database\" in the menu, and select the \"data/mimicdata.sqlite\" database file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Start exploring the data with SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- SQL stands for \"structured query language\". It is the standard language used for querying relational databases, which are databases comprising of several tables linked together by IDs.\n",
    "- __TIP__: queries are generally constructed using the following syntax:  \n",
    "```SELECT <columns> FROM <table> WHERE <constraint>```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select all of the columns ('*') from the patients table "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT *\n",
    "FROM patients;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select all of the columns ('*') from the patients table where the patient is female"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT *\n",
    "FROM patients\n",
    "WHERE gender = 'F';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select all of the columns ('*') from the patients table for a single patient"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT *\n",
    "FROM patients\n",
    "WHERE subject_id = 40080;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id;\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F';\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND subject_id = 40080;\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "SELECT *\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "SELECT patients.*\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "\n",
    "SELECT admissions.*\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "SELECT patients.DOB, admissions.*\n",
    "FROM patients\n",
    "INNER JOIN admissions\n",
    "ON patients.subject_id = admissions.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND patients.subject_id = 40080;\n",
    "\n",
    "\n",
    "SELECT pat.DOB, adm.*\n",
    "FROM patients pat\n",
    "INNER JOIN admissions adm\n",
    "ON pat.subject_id = adm.subject_id\n",
    "WHERE gender = 'F'\n",
    "AND pat.subject_id = 40080;\n",
    "\n",
    "\n",
    "SELECT *\n",
    "FROM chartevents\n",
    "WHERE subject_id = 40080;\n",
    "\n",
    "SELECT ce.*\n",
    "FROM chartevents ce\n",
    "WHERE subject_id = 40080;\n",
    "\n",
    "```sql\n",
    "SELECT ce.*, di.label\n",
    "FROM chartevents ce\n",
    "INNER JOIN d_items di\n",
    "  ON ce.itemid = di.itemid\n",
    "WHERE subject_id = 40080;\n",
    "```\n",
    "\n",
    "names=['row_id','subject_id','hadm_id','icustay_id','itemid','charttime','storetime','cgid','value','valuenum','uom','warning','error','resultstatus','stopped']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-3-8a4e303a2b6b>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-3-8a4e303a2b6b>\"\u001b[0;36m, line \u001b[0;32m1\u001b[0m\n\u001b[0;31m    SELECT *\u001b[0m\n\u001b[0m            ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
{'fraction_non_alphanumeric': 0.1772425505635325, 'fraction_numerical': 0.02315886984715146, 'mean_word_length': 2.7957808379724582, 'pattern_counts': {'":': 123, '<': 10, '<?xml version=': 0, '>': 12, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, '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}}
{'dir': 'git-commits-cleaned', 'id': '6469721', 'n_tokens_mistral': 4872, 'n_tokens_neox': 4508, 'n_words': 1162}