omop_build_data.py.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-16T12:36:47.876830Z",
     "start_time": "2018-05-16T12:36:46.980073Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[INFO] adding /home/paulroujansky/git/DataForGood/batch4_diafoirus_fleming to sys.path\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "# Open a connection to OMOP\n",
    "from fleming_lib.tools import connect_to_omop\n",
    "conn = connect_to_omop()\n",
    "\n",
    "# Get ready for SQL query processing\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Building the data\n",
    "\n",
    "## Filters\n",
    "Prealably, we apply certain filters to our patients in order to build a given base cohort.\n",
    "\n",
    "Criteria: patients need to:\n",
    "- enter an ICU block (?)\n",
    "- be above 15\n",
    "- have an IGS II score above 20\n",
    "- not be \"limited\" (see https://docs.google.com/document/d/12Co20pVyEfQrSa9fBaKpUe1_TLoMTJzK74vK1NY2vIQ/edit)\n",
    "\n",
    "\n",
    "## Type of data\n",
    "\n",
    "1. Meta data: age, gender, ethnicity...\n",
    "\n",
    "2. Measurements: measurements (both categorical and continuous numerical variables), each associated to a given timestamp\n",
    "\n",
    "3. Conditions: type of illness (categorical variables) diagnosed at a given timestamp\n",
    "\n",
    "4. Final label: death\n",
    "\n",
    "\n",
    "In order to fit our model, the ideal would be to build a matrix containing features at a given timestamp.\n",
    "\n",
    "Each one of the L meta data is based on a mapping:\n",
    "- Meta 1 = Age\n",
    "- ...\n",
    "- Meta L = Ethnicity\n",
    "\n",
    "Some meta data might change across age (ex: age if patient stays for too long in the hospital).\n",
    "\n",
    "Each one of the N measures is based on a mapping:\n",
    "- Measure 1 = Heart Rate (bpm)\n",
    "- Measure 2 = Temperate (°F)\n",
    "...\n",
    "- Measure N = Blood pH\n",
    "\n",
    "Units are assumed identical across time for every measures obviously!\n",
    "\n",
    "Equally, each one of the M conditions is based on a mapping:\n",
    "- Condition 1 = Diabete II\n",
    "- ...\n",
    "- Condition M = Obesity\n",
    "\n",
    "1 codes the presence of the condition while 0 codes the absence of it.\n",
    "\n",
    "\n",
    "Finally, Dead = 1 symbolises the death of the patient and potentially is the last sample for that patient in the table.\n",
    "\n",
    "| Timestamp | Meta 1 | ... |  Meta L | Measure 1 | ... | Measure N | Condition 1 | ... | Condition M | Dead |\n",
    "| --- | --- | --- | --- | --- | --- | --- | --- | --- | ---  | --- |\n",
    "|2018-04-22 12:15 | 60 | ... | 30 | 10 | ... | 140 | 1 | ... | 0 | 0 |\n",
    "|2018-04-22 18:33 | 60 | ... | 30 | 40 | ... | 130 | 1 | ... | 1 | 0 |\n",
    "|2018-04-23 15:00 | 60 | ... | 30 | 50 | ... | 100 | 1 | ... | 1 | 1 |\n",
    "\n",
    "Data might potentially be missing for a given timestamp: this is an issue we might want to deal with, with great caution!\n",
    "\n",
    "\n",
    "## HOW-TO:\n",
    "\n",
    "The idea is to extract / build the relevant data independently and then aggregate it in the above matrix structure a posteriori (in SQL or Python).\n",
    "\n",
    "Where to find the data:\n",
    "- meta data can be found in the `person` table\n",
    "- measures can be found in the `measurement` table\n",
    "- conditions can be found in the `condition_occurrence` table\n",
    "- death status can be found in the `death` table\n",
    "\n",
    "Additional information might worth taking into account such as the current unit the patient is in (info in `visit_occurrence`, `visit_detail` and elsewhere).\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-28T16:50:11.963835Z",
     "start_time": "2018-04-28T16:50:11.958144Z"
    }
   },
   "source": [
    "# Filters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Build a table containing the ID (`patient_id`) of patient in the base cohort (see `omop_create table.ipynb` to build a table)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Meta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Measures\n",
    "\n",
    "We only take the measures defined in https://docs.google.com/spreadsheets/d/18DAAFagLvbwgmFobmtUyAnRnyjIPCQ1PKO7PxGgvKzY/edit#gid=451798074."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-28T16:55:45.961921Z",
     "start_time": "2018-04-28T16:55:43.038227Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>person_id</th>\n",
       "      <th>L3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62073122</td>\n",
       "      <td>988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62080413</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62106943</td>\n",
       "      <td>2404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62102837</td>\n",
       "      <td>1176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62081774</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62102210</td>\n",
       "      <td>8161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62090850</td>\n",
       "      <td>364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62073886</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62069455</td>\n",
       "      <td>2690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62085688</td>\n",
       "      <td>176</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   person_id    L3\n",
       "0   62073122   988\n",
       "1   62080413   600\n",
       "2   62106943  2404\n",
       "3   62102837  1176\n",
       "4   62081774    93\n",
       "5   62102210  8161\n",
       "6   62090850   364\n",
       "7   62073886   444\n",
       "8   62069455  2690\n",
       "9   62085688   176"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT person_id, COUNT(measurement_id)\n",
    "FROM measurement\n",
    "WHERE\n",
    "    measurement_concept_id IN\n",
    "    (3022318,   -- heart_rhythm\n",
    "     3024171,   -- respiratory_rate\n",
    "     3028354,   -- vent_settings\n",
    "     3012888,   -- diastolic_bp\n",
    "     3027598,   -- map_bp\n",
    "     3004249,   -- systolic_bp\n",
    "     3027018,   -- heart_rate\n",
    "     3020891,   -- temperature\n",
    "     3016502,   -- spo2\n",
    "     3020716,   -- fio2\n",
    "     3032652    -- glasgow coma scale\n",
    "    )\n",
    "GROUP BY person_id\n",
    "LIMIT 10\n",
    ";\"\"\"\n",
    "\n",
    "temp = pd.read_sql_query(query, conn)\n",
    "temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Example of selection for patient 62073122."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-28T16:55:53.321347Z",
     "start_time": "2018-04-28T16:55:48.966396Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>person_id</th>\n",
       "      <th>measurement_datetime</th>\n",
       "      <th>measurement_concept_name</th>\n",
       "      <th>value_source_value</th>\n",
       "      <th>unit_source_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>115</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>36.111099243164062</td>\n",
       "      <td>Deg. C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>100.66699981689453</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>22</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>100</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>BP systolic</td>\n",
       "      <td>130</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>86</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 16:30:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>97</td>\n",
       "      <td>Deg. F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 17:00:00</td>\n",
       "      <td>BP systolic</td>\n",
       "      <td>118</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 17:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>100</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 17:00:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>85.333297729492188</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 17:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>69</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 17:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>20</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 17:00:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>117</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 17:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 18:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>95</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 18:00:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>122</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 18:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>70</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 18:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 18:00:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>88</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 18:00:00</td>\n",
       "      <td>BP systolic</td>\n",
       "      <td>124</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 18:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>18</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 19:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 19:00:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>117</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 19:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>23</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 19:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>100</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 20:00:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>36.333301544189453</td>\n",
       "      <td>Deg. C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 20:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>89</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-06 20:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 01:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 01:00:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>105</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 01:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>62</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 02:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>19</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 02:00:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>103</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 02:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 02:00:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>85.333297729492188</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 02:00:00</td>\n",
       "      <td>BP systolic</td>\n",
       "      <td>116</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 02:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>98</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 02:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>70</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 03:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>70</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 03:00:00</td>\n",
       "      <td>BP systolic</td>\n",
       "      <td>113</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 03:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>97</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 03:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 03:00:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>84.333297729492188</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 03:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>21</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 03:00:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>106</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>94</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>18</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>97</td>\n",
       "      <td>Deg. F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>60</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>88</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>109</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>BP systolic</td>\n",
       "      <td>144</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 04:00:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>36.111099243164062</td>\n",
       "      <td>Deg. C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 05:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>100</td>\n",
       "      <td>%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 05:00:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>88.333297729492188</td>\n",
       "      <td>mmHg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 05:00:00</td>\n",
       "      <td>Heart rate</td>\n",
       "      <td>100</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>62073122</td>\n",
       "      <td>2108-04-07 05:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>16</td>\n",
       "      <td>BPM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    person_id measurement_datetime             measurement_concept_name  \\\n",
       "0    62073122  2108-04-06 16:30:00                           Heart rate   \n",
       "1    62073122  2108-04-06 16:30:00                     Body temperature   \n",
       "2    62073122  2108-04-06 16:30:00                  Mean blood pressure   \n",
       "3    62073122  2108-04-06 16:30:00                     Respiratory rate   \n",
       "4    62073122  2108-04-06 16:30:00  Oxygen saturation in Arterial blood   \n",
       "5    62073122  2108-04-06 16:30:00                    Heart rate rhythm   \n",
       "6    62073122  2108-04-06 16:30:00                          BP systolic   \n",
       "7    62073122  2108-04-06 16:30:00                         BP diastolic   \n",
       "8    62073122  2108-04-06 16:30:00                     Body temperature   \n",
       "9    62073122  2108-04-06 17:00:00                          BP systolic   \n",
       "10   62073122  2108-04-06 17:00:00  Oxygen saturation in Arterial blood   \n",
       "11   62073122  2108-04-06 17:00:00                  Mean blood pressure   \n",
       "12   62073122  2108-04-06 17:00:00                         BP diastolic   \n",
       "13   62073122  2108-04-06 17:00:00                     Respiratory rate   \n",
       "14   62073122  2108-04-06 17:00:00                           Heart rate   \n",
       "15   62073122  2108-04-06 17:00:00                    Heart rate rhythm   \n",
       "16   62073122  2108-04-06 18:00:00  Oxygen saturation in Arterial blood   \n",
       "17   62073122  2108-04-06 18:00:00                           Heart rate   \n",
       "18   62073122  2108-04-06 18:00:00                         BP diastolic   \n",
       "19   62073122  2108-04-06 18:00:00                    Heart rate rhythm   \n",
       "20   62073122  2108-04-06 18:00:00                  Mean blood pressure   \n",
       "21   62073122  2108-04-06 18:00:00                          BP systolic   \n",
       "22   62073122  2108-04-06 18:00:00                     Respiratory rate   \n",
       "23   62073122  2108-04-06 19:00:00                    Heart rate rhythm   \n",
       "24   62073122  2108-04-06 19:00:00                           Heart rate   \n",
       "25   62073122  2108-04-06 19:00:00                     Respiratory rate   \n",
       "26   62073122  2108-04-06 19:00:00  Oxygen saturation in Arterial blood   \n",
       "27   62073122  2108-04-06 20:00:00                     Body temperature   \n",
       "28   62073122  2108-04-06 20:00:00                         BP diastolic   \n",
       "29   62073122  2108-04-06 20:00:00                    Heart rate rhythm   \n",
       "..        ...                  ...                                  ...   \n",
       "70   62073122  2108-04-07 01:00:00                    Heart rate rhythm   \n",
       "71   62073122  2108-04-07 01:00:00                           Heart rate   \n",
       "72   62073122  2108-04-07 01:00:00                         BP diastolic   \n",
       "73   62073122  2108-04-07 02:00:00                     Respiratory rate   \n",
       "74   62073122  2108-04-07 02:00:00                           Heart rate   \n",
       "75   62073122  2108-04-07 02:00:00                    Heart rate rhythm   \n",
       "76   62073122  2108-04-07 02:00:00                  Mean blood pressure   \n",
       "77   62073122  2108-04-07 02:00:00                          BP systolic   \n",
       "78   62073122  2108-04-07 02:00:00  Oxygen saturation in Arterial blood   \n",
       "79   62073122  2108-04-07 02:00:00                         BP diastolic   \n",
       "80   62073122  2108-04-07 03:00:00                         BP diastolic   \n",
       "81   62073122  2108-04-07 03:00:00                          BP systolic   \n",
       "82   62073122  2108-04-07 03:00:00  Oxygen saturation in Arterial blood   \n",
       "83   62073122  2108-04-07 03:00:00                    Heart rate rhythm   \n",
       "84   62073122  2108-04-07 03:00:00                  Mean blood pressure   \n",
       "85   62073122  2108-04-07 03:00:00                     Respiratory rate   \n",
       "86   62073122  2108-04-07 03:00:00                           Heart rate   \n",
       "87   62073122  2108-04-07 04:00:00  Oxygen saturation in Arterial blood   \n",
       "88   62073122  2108-04-07 04:00:00                     Respiratory rate   \n",
       "89   62073122  2108-04-07 04:00:00                    Heart rate rhythm   \n",
       "90   62073122  2108-04-07 04:00:00                     Body temperature   \n",
       "91   62073122  2108-04-07 04:00:00                         BP diastolic   \n",
       "92   62073122  2108-04-07 04:00:00                  Mean blood pressure   \n",
       "93   62073122  2108-04-07 04:00:00                           Heart rate   \n",
       "94   62073122  2108-04-07 04:00:00                          BP systolic   \n",
       "95   62073122  2108-04-07 04:00:00                     Body temperature   \n",
       "96   62073122  2108-04-07 05:00:00  Oxygen saturation in Arterial blood   \n",
       "97   62073122  2108-04-07 05:00:00                  Mean blood pressure   \n",
       "98   62073122  2108-04-07 05:00:00                           Heart rate   \n",
       "99   62073122  2108-04-07 05:00:00                     Respiratory rate   \n",
       "\n",
       "    value_source_value unit_source_value  \n",
       "0                  115               BPM  \n",
       "1   36.111099243164062            Deg. C  \n",
       "2   100.66699981689453              mmHg  \n",
       "3                   22               BPM  \n",
       "4                  100                 %  \n",
       "5          Sinus Tachy              None  \n",
       "6                  130              mmHg  \n",
       "7                   86              mmHg  \n",
       "8                   97            Deg. F  \n",
       "9                  118              mmHg  \n",
       "10                 100                 %  \n",
       "11  85.333297729492188              mmHg  \n",
       "12                  69              mmHg  \n",
       "13                  20               BPM  \n",
       "14                 117               BPM  \n",
       "15         Sinus Tachy              None  \n",
       "16                  95                 %  \n",
       "17                 122               BPM  \n",
       "18                  70              mmHg  \n",
       "19         Sinus Tachy              None  \n",
       "20                  88              mmHg  \n",
       "21                 124              mmHg  \n",
       "22                  18               BPM  \n",
       "23         Sinus Tachy              None  \n",
       "24                 117               BPM  \n",
       "25                  23               BPM  \n",
       "26                 100                 %  \n",
       "27  36.333301544189453            Deg. C  \n",
       "28                  89              mmHg  \n",
       "29         Sinus Tachy              None  \n",
       "..                 ...               ...  \n",
       "70         Sinus Tachy              None  \n",
       "71                 105               BPM  \n",
       "72                  62              mmHg  \n",
       "73                  19               BPM  \n",
       "74                 103               BPM  \n",
       "75         Sinus Tachy              None  \n",
       "76  85.333297729492188              mmHg  \n",
       "77                 116              mmHg  \n",
       "78                  98                 %  \n",
       "79                  70              mmHg  \n",
       "80                  70              mmHg  \n",
       "81                 113              mmHg  \n",
       "82                  97                 %  \n",
       "83         Sinus Tachy              None  \n",
       "84  84.333297729492188              mmHg  \n",
       "85                  21               BPM  \n",
       "86                 106               BPM  \n",
       "87                  94                 %  \n",
       "88                  18               BPM  \n",
       "89         Sinus Tachy              None  \n",
       "90                  97            Deg. F  \n",
       "91                  60              mmHg  \n",
       "92                  88              mmHg  \n",
       "93                 109               BPM  \n",
       "94                 144              mmHg  \n",
       "95  36.111099243164062            Deg. C  \n",
       "96                 100                 %  \n",
       "97  88.333297729492188              mmHg  \n",
       "98                 100               BPM  \n",
       "99                  16               BPM  \n",
       "\n",
       "[100 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT person_id, measurement_datetime, measurement_concept_name, value_source_value, unit_source_value\n",
    "FROM measurement\n",
    "WHERE\n",
    "    measurement_concept_id IN\n",
    "    (3022318,   -- heart_rhythm\n",
    "     3024171,   -- respiratory_rate\n",
    "     3028354,   -- vent_settings\n",
    "     3012888,   -- diastolic_bp\n",
    "     3027598,   -- map_bp\n",
    "     3004249,   -- systolic_bp\n",
    "     3027018,   -- heart_rate\n",
    "     3020891,   -- temperature\n",
    "     3016502,   -- spo2\n",
    "     3020716,   -- fio2\n",
    "     3032652    -- glasgow coma scale\n",
    "    )\n",
    "AND person_id = 62073122\n",
    "ORDER BY measurement_datetime\n",
    "LIMIT 100\n",
    ";\"\"\"\n",
    "\n",
    "temp = pd.read_sql_query(query, conn)\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}