1.1a_create_transfused_cohort.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.1a_create_transfused_cohort\n",
    "+ python 2.7.x\n",
    "+ from the mimic iii PostgreSQL database \n",
    "+ label admissions as **transfused** or **control** based on the ICD9 codes and inputs (labeled with our custom dictionary). \n",
    "+ all the notes for each admission (hadm_id) get ordered by time and concatenated  into one note per admission.\n",
    "+ create tables for transfused `transfused_notes_sink` and control `ctrl_notes_sink`  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## import libraries, connect to mimic database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn.commit();\n",
    "cur.close();\n",
    "conn.close();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "last ran:  2019-12-24 23:53:36.145380\n",
      "Python Version: 3.7.3 (\n",
      "operating system: darwin\n",
      "pandas version: 0.24.2\n",
      "sqlalchemy version: 1.3.3\n",
      "psycopg2 version: 2.7.6.1\n",
      "tqdm version: 4.32.1\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "\n",
    "import time\n",
    "from datetime import datetime\n",
    "import datetime\n",
    "\n",
    "import pandas as pd\n",
    "import random\n",
    "\n",
    "from tqdm import tnrange, tqdm_notebook\n",
    "from time import sleep\n",
    "\n",
    "from importlib_metadata import version\n",
    "\n",
    "# things to connect to the posgres database\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine, update, event\n",
    "\n",
    "POSTGRES_CONNECT = os.environ.get(\"POSTGRES_CONNECT\")\n",
    "POSTGRES_ENGINE = os.environ.get(\"POSTGRES_ENGINE\")\n",
    "conn = psycopg2.connect(POSTGRES_CONNECT)\n",
    "cur = conn.cursor();\n",
    "cur.execute(\"\"\"SET search_path = mimiciii;\"\"\")\n",
    "engine = create_engine(POSTGRES_ENGINE)\n",
    "\n",
    "libraries = ['pandas','sqlalchemy','psycopg2','tqdm']\n",
    "print('last ran: ',datetime.now() )\n",
    "print(\"Python Version:\", sys.version[0:7])\n",
    "print( \"operating system:\", sys.platform)\n",
    "\n",
    "for lib in libraries:\n",
    "    print(lib + ' version: ' + version(lib))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create ICD-9 based groups of admissions \n",
    "+ use the identified ICD-9 codes\n",
    "+ create transfused group\n",
    "+ create grey group\n",
    "+ create control group (everything that's not transfused or grey)\n",
    "\n",
    "###  1.1.1  create tranfused group from icd-9 codes\n",
    "table that lists procedures by admission\n",
    "+ pull out rows from `procedures_icd` that\n",
    "       + have one of these icd9 codes [9901, 9903,9904, 9905, 9907]\n",
    "       + exist in the `patients_adult` table \n",
    "+ admissions (hadm_id) = 7514"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\"\n",
    "DROP TABLE IF EXISTS mimiciii.transfusion_icd9;\n",
    "\n",
    "SELECT p.*, i.icd9_code, i.hadm_id\n",
    "    INTO transfusion_icd9\n",
    "FROM mimiciii.procedures_icd i\n",
    "INNER JOIN mimiciii.patients_adult p\n",
    "    ON i.subject_id=p.subject_id\n",
    "    WHERE i.icd9_code IN ('9901','9903','9904','9905','9907');\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print counts of transfusion admissions using only icd9 selection criteria. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " transf_admissions_count  code_count\n",
      "                    7514           5\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\"\n",
    "SELECT COUNT(DISTINCT hadm_id) AS transf_admissions_count, \n",
    "COUNT(distinct icd9_code) AS code_count\n",
    "FROM mimiciii.transfusion_icd9\n",
    ";\"\"\")\n",
    "\n",
    "print(pd.DataFrame(cur.fetchall(), columns=[ 'transf_admissions_count','code_count']).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.2 create grey group from icd-9 codes\n",
    "create grey group from table that lists procedures (icd-9 codes) by admission\n",
    "\n",
    "+ pull out rows from `procedures_icd` that\n",
    "       + have one of these icd9 codes [9900, 9902]\n",
    "       + exist in the `patients_adult` table \n",
    "+ unique admissions (hadm_id) = 64"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\"\n",
    "DROP TABLE IF EXISTS mimiciii.grey_icd9;\n",
    "\n",
    "SELECT p.*, i.icd9_code, i.hadm_id\n",
    "    INTO mimiciii.grey_icd9\n",
    "FROM mimiciii.procedures_icd i\n",
    "INNER JOIN mimiciii.patients_adult p\n",
    "    ON i.subject_id=p.subject_id\n",
    "    WHERE i.icd9_code IN ('9900','9902');\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print counts of grey admissions using only icd9 selection criteria. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " grey_admissions_count  code_count\n",
      "                    64           2\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\"\n",
    "SELECT COUNT(DISTINCT hadm_id) AS grey_admissions_count, \n",
    "    COUNT(DISTINCT icd9_code) AS code_count\n",
    "FROM mimiciii.grey_icd9;\"\"\")\n",
    "\n",
    "print(pd.DataFrame(cur.fetchall(), columns=[ 'grey_admissions_count','code_count']).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.3 create control icd9 group `ctrl_icd9`  \n",
    "\n",
    "from table that lists procedures (icd-9 codes) by admission\n",
    "+ keep all admissions that are not in the `transfusion_icd9` or the `grey_icd9` tables\n",
    "+ are in the `patients_adult` table\n",
    "+ this way we end up with only admissions that have never been assigned one of our transfusion or grey icd9 procedure codes\n",
    "+ the 'IS NOT TRUE' is there because of Null values, otherwise we would use 'NOT IN'\n",
    "+ unique admissions = 34269"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\"\n",
    "DROP TABLE IF EXISTS mimiciii.ctrl_icd9;\n",
    "\n",
    "SELECT p.*, c.icd9_code, c.hadm_id\n",
    "    INTO mimiciii.ctrl_icd9 \n",
    "FROM mimiciii.procedures_icd c\n",
    "\n",
    "INNER JOIN mimiciii.patients_adult p\n",
    "    ON c.subject_id=p.subject_id\n",
    "\n",
    "    WHERE (c.subject_id IN (\n",
    "            SELECT  x.subject_id \n",
    "            FROM mimiciii.grey_icd9 x))\n",
    "        IS NOT TRUE\n",
    "        \n",
    "    AND (c.subject_id IN (\n",
    "            SELECT  t.subject_id \n",
    "            FROM mimiciii.transfusion_icd9 t))\n",
    "        IS NOT TRUE\n",
    "    ;\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print counts of ctrl admissions using only icd9 selection criteria. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " ctrl_admissions_count  code_count\n",
      "                 34269        1871\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\"\n",
    "SELECT COUNT(DISTINCT hadm_id), \n",
    "COUNT(DISTINCT icd9_code) AS code_count\n",
    "FROM mimiciii.ctrl_icd9;\"\"\")\n",
    "\n",
    "print(pd.DataFrame(cur.fetchall(), columns=[ 'ctrl_admissions_count','code_count']).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Label each input event as transfused, grey, or control\n",
    "+ load in the D_items identified as  transufe group and Grey group from xlsx sheet\n",
    "+ use all adult input events to find\n",
    "\n",
    "1. transfused inputs (T) = inputs ever been assigned a transfuse label\n",
    "2. grey inputs (G) = inputs that have been assigned a grey label\n",
    "3. control inputs (N) = inputs that have only been assigned labels that are NOT transfue or grey"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.4 Create `transfusion_items_dict`\n",
    "Import the labeled D_items from csv\n",
    "This involves importing a csv file into a new postgres table. Sometimes creating new tables in this way does not like to work with python and it's easier to just do it at the postgres command line, but your mileage may vary. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# create new empty table in mimiciii schema with the following vars\n",
    "\n",
    "cur.execute(\"\"\"\n",
    "DROP TABLE IF EXISTS mimiciii.transfusion_items_dict;\n",
    "\n",
    "CREATE TABLE mimiciii.transfusion_items_dict \n",
    "(Notes varchar,\n",
    " GRP char(1), \n",
    " ROW_ID int, \n",
    " ITEMID int,\n",
    " LABEL varchar, \n",
    " ABBREVIATION varchar,\n",
    " DBSOURCE varchar,\n",
    " LINKSTO varchar,\n",
    " CATEGORY varchar,\n",
    " UNITNAME varchar,\n",
    " PARAM_TYPE varchar,\n",
    " CONCEPTID varchar,\n",
    " ref varchar);\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Run this command in the postgres command line to create the table if python is giving issues\n",
    "COPY mimiciii.transfusion_items_dict \n",
    "FROM 'D:\\\\20180717D_ITEMS_related_to_blood_full.csv' \n",
    "DELIMITER ',' CSV HEADER;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify that the table has been created correctly. It should have 132 rows total:\n",
    "+ T=54\n",
    "+ G=40\n",
    "+ N=38 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "grp  count\n",
      "  N     38\n",
      "  T     54\n",
      "  G     40\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\"\n",
    "SELECT grp, count(*) \n",
    "    FROM mimiciii.transfusion_items_dict\n",
    "GROUP BY grp;\"\"\")\n",
    "\n",
    "colnames = [desc[0] for desc in cur.description]\n",
    "print(pd.DataFrame(cur.fetchall(), columns=colnames).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.5 join these labels with d_items \n",
    "This will give us a table of labeled D-items based on the SME review of inputs that were transfused, non-transfused or grey. The new labeled items dict we just imported only includes the relevant transfusion-related inputs, so everything that isn't in the new dict, gets a grp label of 'N'for non-transfused (control)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\"\n",
    "DROP TABLE IF EXISTS mimiciii.D_items_labeled;\n",
    "\n",
    "SELECT i.*, d.notes\n",
    "    ,CASE WHEN grp IS NULL THEN 'N' ELSE grp END \n",
    "    \n",
    "    INTO mimiciii.D_items_labeled\n",
    "FROM mimiciii.transfusion_items_dict d\n",
    "\n",
    "RIGHT JOIN mimiciii.D_items i\n",
    "    ON i.itemid=d.itemid\n",
    ";\"\"\")\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.6 Label all inputs\n",
    "Join new D_items with inputs_all to give each input a grp label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\"DROP TABLE IF EXISTS mimiciii.inputs_all_labeled;\"\"\")\n",
    "\n",
    "cur.execute(\"\"\"\n",
    "SELECT d.label,d.grp, i.*\n",
    "    INTO mimiciii.inputs_all_labeled\n",
    "FROM mimiciii.D_items_labeled d\n",
    "    RIGHT JOIN mimiciii.inputs_all i\n",
    "    ON i.itemid=d.itemid\n",
    ";\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Print the number of inputs (non-lab charted items) for each of the groups.  Expected values are below:\n",
    "+ N = 289,352,348\n",
    "+ T = 153,154\n",
    "+ G = 3872"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "grp      count\n",
      "  G       3872\n",
      "  N  289352348\n",
      "  T     153154\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\"\n",
    "SELECT grp, count(*) \n",
    "    FROM mimiciii.inputs_all_labeled\n",
    "GROUP BY grp;\"\"\")\n",
    "\n",
    "colnames = [desc[0] for desc in cur.description]\n",
    "print(pd.DataFrame(cur.fetchall(), columns=colnames).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.7 Create the full list of admissions in transfused group  `transfused_hadm_id`\n",
    "\n",
    "+ Create a table (`transfused_hadm_id`) of transfuse group admission ids (**hadm_id**) from the icd9 (`transfusion_icd9`) and the non-lab chart events (`inputs_all_labeled` **grp=T**) criteria\n",
    "+ Transfusion admissions_count = 21541"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\" DROP TABLE IF EXISTS MIMICIII.transfused_hadm_id;\n",
    "\n",
    "SELECT DISTINCT hadm_id\n",
    "    INTO mimiciii.transfused_hadm_id\n",
    "    \n",
    "FROM mimiciii.inputs_all_labeled c\n",
    "    \n",
    "    WHERE grp='T'\n",
    "    AND c.hadm_id IS NOT NULL\n",
    "    UNION\n",
    "\n",
    "SELECT DISTINCT hadm_id\n",
    "FROM mimiciii.transfusion_icd9\n",
    "    WHERE hadm_id IS NOT NULL\n",
    ";\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print total Transfused admissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " transfusion admissions_count\n",
      "                        21541\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\"\n",
    "SELECT count(DISTINCT hadm_id) \n",
    "FROM mimiciii.transfused_hadm_id\n",
    ";\"\"\")\n",
    "print(pd.DataFrame(cur.fetchall(), columns=[ 'transfusion admissions_count']).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.8 Create the full list of admissions in the grey group  `grey_hadm_id`\n",
    "\n",
    "+ Create a table (`grey_hadm_id`) of grey group admission ids (**hadm_id**) from the icd9 (`grey_icd9`) and the non-lab chart events (`inputs_all_labeled` **grp = G**) criteria\n",
    "+ grey admissions_count = 2373"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\" DROP TABLE IF EXISTS MIMICIII.grey_hadm_id;\n",
    "\n",
    "SELECT DISTINCT hadm_id\n",
    "    INTO mimiciii.grey_hadm_id\n",
    "FROM mimiciii.inputs_all_labeled c\n",
    "\n",
    "    WHERE grp='G'\n",
    "    AND hadm_id IS NOT NULL\n",
    "    UNION\n",
    "\n",
    "SELECT DISTINCT hadm_id\n",
    "FROM mimiciii.grey_icd9\n",
    "    WHERE hadm_id IS NOT NULL\n",
    ";\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print the grey admissions count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " grey admissions_count\n",
      "                  2373\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\"\n",
    "SELECT count(DISTINCT hadm_id) \n",
    "FROM mimiciii.grey_hadm_id\n",
    ";\"\"\")\n",
    "print(pd.DataFrame(cur.fetchall(), columns=[ 'grey admissions_count']).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.9 Create list of ctrl admissions `ctrl_ids`\n",
    "\n",
    "+ make a list of hadm_ids admissions not in the transfused or grey groups. \n",
    "+ join all admissions from `ctrl_icd9` and `inputs_all_labeled` = N\n",
    "+ this basically pulls every admission **50,328**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\" DROP TABLE IF EXISTS mimiciii.ctrl_idsa;\n",
    "\n",
    "SELECT DISTINCT i.hadm_id\n",
    "    INTO mimiciii.ctrl_idsa\n",
    "FROM mimiciii.inputs_all_labeled i\n",
    "\n",
    "    WHERE i.grp='N' \n",
    "    AND i.hadm_id IS NOT NULL\n",
    "    UNION\n",
    "    \n",
    "SELECT DISTINCT y.hadm_id\n",
    "FROM mimiciii.ctrl_icd9 y\n",
    "    WHERE y.hadm_id IS NOT NULL\n",
    ";\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print the count of preliminary control admissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " admissions\n",
      "      50328\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\" SELECT COUNT(DISTINCT hadm_id)\n",
    "FROM  mimiciii.ctrl_idsa;\"\"\")\n",
    "\n",
    "ncount=cur.fetchall()\n",
    "print( pd.DataFrame(ncount, columns=[ 'admissions']).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1.10 remove admissions that belong to the `transfused_hadm_id` table or the `grey_hadm_id`\n",
    "+ admissions = 28,128"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\" DROP TABLE IF EXISTS mimiciii.ctrl_ids;\n",
    "\n",
    "SELECT DISTINCT i.hadm_id\n",
    "    INTO mimiciii.ctrl_ids\n",
    "FROM mimiciii.ctrl_idsa i\n",
    "\n",
    "    WHERE  i.hadm_id NOT IN (\n",
    "           SELECT  x.hadm_id \n",
    "           FROM mimiciii.transfused_hadm_id x)\n",
    "        \n",
    "    AND i.hadm_id NOT IN (\n",
    "            SELECT g.hadm_id\n",
    "            FROM mimiciii.grey_hadm_id g)\n",
    ";\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print the number of control admissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " admissions\n",
      "      28128\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\" SELECT COUNT(DISTINCT hadm_id)\n",
    "FROM  mimiciii.ctrl_ids;\"\"\")\n",
    "\n",
    "ncount=cur.fetchall()\n",
    "print( pd.DataFrame(ncount, columns=[ 'admissions']).to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1.11 Clean Up, Commit, and Close"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}