omop_create dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OMOP Create Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:15:29.946447Z",
     "start_time": "2018-05-17T11:15:29.888971Z"
    }
   },
   "outputs": [],
   "source": [
    "import __init__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:15:31.472628Z",
     "start_time": "2018-05-17T11:15:30.464946Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime\n",
    "\n",
    "# Open a connection to OMOP\n",
    "from fleming_lib.tools import connect_to_omop\n",
    "conn = connect_to_omop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select list of patients"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "- not be \"limited\" (Concept_id: 2001018843 and 2001030812 and 4127294)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T15:29:28.813833Z",
     "start_time": "2018-05-13T15:29:18.630795Z"
    }
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select\n",
    "    distinct person_id\n",
    "from \n",
    "    measurement \n",
    "where \n",
    "    measurement_source_concept_id in (2001018843, 2001030812, 4127294)  \n",
    "    ;\"\"\"\n",
    "\n",
    "limitation = pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Age >= 15 \n",
    "- Processed only by intensive care unit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T15:29:42.798722Z",
     "start_time": "2018-05-13T15:29:30.261447Z"
    }
   },
   "outputs": [],
   "source": [
    "# in ICU\n",
    "query = \"\"\"\n",
    "with icu as (\n",
    "   select\n",
    "       care_site_id, care_site_name, place_of_service_source_value\n",
    "   from\n",
    "       care_site\n",
    "   where\n",
    "       lower(place_of_service_source_value) like '%intensive%'\n",
    "    or\n",
    "        place_of_service_source_value = 'Cardiac surgery recovery unit' -- c'est aussi ICU\n",
    "    or\n",
    "        place_of_service_source_value = 'Coronary care unit'  -- c'est aussi ICU\n",
    "   and\n",
    "       lower(place_of_service_source_value) not like '%neonatal%'    -- removing Neonatal ICUs\n",
    "   )\n",
    "select\n",
    "   distinct vd.person_id\n",
    "from\n",
    "   visit_detail vd\n",
    "join\n",
    "   icu\n",
    "on\n",
    "   icu.care_site_id = vd.care_site_id\n",
    ";\"\"\"\n",
    "\n",
    "icu = pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T15:29:57.049058Z",
     "start_time": "2018-05-13T15:29:42.800204Z"
    }
   },
   "outputs": [],
   "source": [
    "# age > 15\n",
    "query = \"\"\"\n",
    "with person_age as (\n",
    "    select\n",
    "        p.person_id, (v.visit_start_date - p.birth_datetime)/365.25 age\n",
    "    from\n",
    "        person p\n",
    "    left outer join\n",
    "        visit_occurrence v\n",
    "    on\n",
    "        p.person_id = v.person_id)\n",
    "select\n",
    "    person_id, age\n",
    "from\n",
    "    person_age\n",
    "where\n",
    "    age > 15            -- including persons older than 15\n",
    "and \n",
    "    age < 150             -- removing any outliers\n",
    ";\"\"\"\n",
    "\n",
    "age = pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cohort fusion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-12T16:16:49.321014Z",
     "start_time": "2018-05-12T16:16:47.834Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[62063367,\n",
       " 62063368,\n",
       " 62063369,\n",
       " 62063370,\n",
       " 62063371,\n",
       " 62063372,\n",
       " 62063373,\n",
       " 62063374,\n",
       " 62063377,\n",
       " 62063378]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_patient = list(set(icu.person_id) & set(age.person_id) & set(limitation.person_id))\n",
    "list_patient[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-12T14:27:35.874463Z",
     "start_time": "2018-05-12T14:27:35.860631Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31056"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(list_patient)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build dataset for each patient"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract patients meta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:57.731950Z",
     "start_time": "2018-05-13T14:01:43.507644Z"
    }
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select\n",
    "    distinct p.person_id, p.gender_source_value gender, p.race_source_value race, p.birth_datetime\n",
    "from\n",
    "    person p\n",
    "    ;\"\"\"\n",
    "\n",
    "meta = pd.read_sql_query(query, conn)\n",
    "\n",
    "# Note: ethnicity is empty so we do not add it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:57.737351Z",
     "start_time": "2018-05-13T14:01:57.733580Z"
    }
   },
   "outputs": [],
   "source": [
    "meta['birth_datetime'] = pd.to_datetime(meta['birth_datetime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:57.773054Z",
     "start_time": "2018-05-13T14:01:57.740104Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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>gender</th>\n",
       "      <th>race</th>\n",
       "      <th>birth_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62065089</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2107-06-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62106569</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2047-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62073299</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2190-04-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62096906</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2182-09-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62102055</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2087-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62106786</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2161-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62083063</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2125-12-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62078234</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2110-06-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62065073</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2074-05-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62100837</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>2136-07-22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   person_id gender   race birth_datetime\n",
       "0   62065089      F  ASIAN     2107-06-29\n",
       "1   62106569      M  ASIAN     2047-11-01\n",
       "2   62073299      F  ASIAN     2190-04-23\n",
       "3   62096906      M  ASIAN     2182-09-07\n",
       "4   62102055      M  ASIAN     2087-02-28\n",
       "5   62106786      F  ASIAN     2161-03-31\n",
       "6   62083063      F  ASIAN     2125-12-16\n",
       "7   62078234      M  ASIAN     2110-06-19\n",
       "8   62065073      F  ASIAN     2074-05-27\n",
       "9   62100837      F  ASIAN     2136-07-22"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta.iloc[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:57.831728Z",
     "start_time": "2018-05-13T14:01:57.775255Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Counts:\n",
      "gender\n",
      "------------------------------\n",
      "M    26121\n",
      "F    20399\n",
      "Name: gender, dtype: int64\n",
      "\n",
      "race\n",
      "------------------------------\n",
      "WHITE                                                       32074\n",
      "UNKNOWN/NOT SPECIFIED                                        4236\n",
      "BLACK/AFRICAN AMERICAN                                       3585\n",
      "HISPANIC OR LATINO                                           1350\n",
      "ASIAN                                                        1304\n",
      "OTHER                                                        1256\n",
      "UNABLE TO OBTAIN                                              792\n",
      "PATIENT DECLINED TO ANSWER                                    498\n",
      "ASIAN - CHINESE                                               223\n",
      "BLACK/CAPE VERDEAN                                            159\n",
      "HISPANIC/LATINO - PUERTO RICAN                                146\n",
      "MULTI RACE ETHNICITY                                          111\n",
      "WHITE - RUSSIAN                                               105\n",
      "BLACK/HAITIAN                                                  71\n",
      "WHITE - OTHER EUROPEAN                                         69\n",
      "HISPANIC/LATINO - DOMINICAN                                    60\n",
      "ASIAN - ASIAN INDIAN                                           57\n",
      "AMERICAN INDIAN/ALASKA NATIVE                                  45\n",
      "WHITE - BRAZILIAN                                              42\n",
      "ASIAN - VIETNAMESE                                             41\n",
      "PORTUGUESE                                                     36\n",
      "BLACK/AFRICAN                                                  32\n",
      "MIDDLE EASTERN                                                 28\n",
      "HISPANIC/LATINO - GUATEMALAN                                   25\n",
      "WHITE - EASTERN EUROPEAN                                       22\n",
      "NATIVE HAWAIIAN OR OTHER PACIFIC ISLANDER                      15\n",
      "ASIAN - OTHER                                                  15\n",
      "HISPANIC/LATINO - CUBAN                                        15\n",
      "ASIAN - FILIPINO                                               15\n",
      "HISPANIC/LATINO - SALVADORAN                                   14\n",
      "HISPANIC/LATINO - MEXICAN                                      11\n",
      "ASIAN - KOREAN                                                 11\n",
      "ASIAN - CAMBODIAN                                              10\n",
      "HISPANIC/LATINO - CENTRAL AMERICAN (OTHER)                      9\n",
      "SOUTH AMERICAN                                                  8\n",
      "HISPANIC/LATINO - COLOMBIAN                                     8\n",
      "CARIBBEAN ISLAND                                                7\n",
      "ASIAN - JAPANESE                                                7\n",
      "HISPANIC/LATINO - HONDURAN                                      3\n",
      "ASIAN - THAI                                                    3\n",
      "AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE        2\n",
      "Name: race, dtype: int64\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('Counts:')\n",
    "for name in ['gender', 'race']:\n",
    "    print(name + '\\n' + '-'*30)\n",
    "    print(meta[name].value_counts())\n",
    "    print('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:57.837755Z",
     "start_time": "2018-05-13T14:01:57.834125Z"
    }
   },
   "outputs": [],
   "source": [
    "from fleming_lib.utils import add_categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:57.899263Z",
     "start_time": "2018-05-13T14:01:57.840414Z"
    }
   },
   "outputs": [],
   "source": [
    "# Dictionary containing unique categories for each categorical variable\n",
    "categories = dict()\n",
    "\n",
    "categorical_variables = ['gender', 'race']\n",
    "\n",
    "categories = add_categories(categories, meta, categorical_variables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:57.906329Z",
     "start_time": "2018-05-13T14:01:57.901278Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'gender': Index(['F', 'M', 'NaN'], dtype='object'),\n",
       " 'race': Index(['AMERICAN INDIAN/ALASKA NATIVE',\n",
       "        'AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE', 'ASIAN',\n",
       "        'ASIAN - ASIAN INDIAN', 'ASIAN - CAMBODIAN', 'ASIAN - CHINESE',\n",
       "        'ASIAN - FILIPINO', 'ASIAN - JAPANESE', 'ASIAN - KOREAN',\n",
       "        'ASIAN - OTHER', 'ASIAN - THAI', 'ASIAN - VIETNAMESE', 'BLACK/AFRICAN',\n",
       "        'BLACK/AFRICAN AMERICAN', 'BLACK/CAPE VERDEAN', 'BLACK/HAITIAN',\n",
       "        'CARIBBEAN ISLAND', 'HISPANIC OR LATINO',\n",
       "        'HISPANIC/LATINO - CENTRAL AMERICAN (OTHER)',\n",
       "        'HISPANIC/LATINO - COLOMBIAN', 'HISPANIC/LATINO - CUBAN',\n",
       "        'HISPANIC/LATINO - DOMINICAN', 'HISPANIC/LATINO - GUATEMALAN',\n",
       "        'HISPANIC/LATINO - HONDURAN', 'HISPANIC/LATINO - MEXICAN',\n",
       "        'HISPANIC/LATINO - PUERTO RICAN', 'HISPANIC/LATINO - SALVADORAN',\n",
       "        'MIDDLE EASTERN', 'MULTI RACE ETHNICITY',\n",
       "        'NATIVE HAWAIIAN OR OTHER PACIFIC ISLANDER', 'OTHER',\n",
       "        'PATIENT DECLINED TO ANSWER', 'PORTUGUESE', 'SOUTH AMERICAN',\n",
       "        'UNABLE TO OBTAIN', 'UNKNOWN/NOT SPECIFIED', 'WHITE',\n",
       "        'WHITE - BRAZILIAN', 'WHITE - EASTERN EUROPEAN',\n",
       "        'WHITE - OTHER EUROPEAN', 'WHITE - RUSSIAN', 'NaN'],\n",
       "       dtype='object')}"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:57.971369Z",
     "start_time": "2018-05-13T14:01:57.911994Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from fleming_lib.utils import to_categorical, to_onehot\n",
    "\n",
    "# Convert categorical variable to 'categorical' type\n",
    "meta = to_categorical(meta, categorical_variables, categories)\n",
    "\n",
    "# One-hot encode categorical variables\n",
    "meta = to_onehot(meta, categorical_variables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:58.002231Z",
     "start_time": "2018-05-13T14:01:57.977481Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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>birth_datetime</th>\n",
       "      <th>gender_F</th>\n",
       "      <th>gender_M</th>\n",
       "      <th>gender_NaN</th>\n",
       "      <th>race_AMERICAN INDIAN/ALASKA NATIVE</th>\n",
       "      <th>race_AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE</th>\n",
       "      <th>race_ASIAN</th>\n",
       "      <th>race_ASIAN - ASIAN INDIAN</th>\n",
       "      <th>race_ASIAN - CAMBODIAN</th>\n",
       "      <th>...</th>\n",
       "      <th>race_PORTUGUESE</th>\n",
       "      <th>race_SOUTH AMERICAN</th>\n",
       "      <th>race_UNABLE TO OBTAIN</th>\n",
       "      <th>race_UNKNOWN/NOT SPECIFIED</th>\n",
       "      <th>race_WHITE</th>\n",
       "      <th>race_WHITE - BRAZILIAN</th>\n",
       "      <th>race_WHITE - EASTERN EUROPEAN</th>\n",
       "      <th>race_WHITE - OTHER EUROPEAN</th>\n",
       "      <th>race_WHITE - RUSSIAN</th>\n",
       "      <th>race_NaN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62065089</td>\n",
       "      <td>2107-06-29</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62106569</td>\n",
       "      <td>2047-11-01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62073299</td>\n",
       "      <td>2190-04-23</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62096906</td>\n",
       "      <td>2182-09-07</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62102055</td>\n",
       "      <td>2087-02-28</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62106786</td>\n",
       "      <td>2161-03-31</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62083063</td>\n",
       "      <td>2125-12-16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62078234</td>\n",
       "      <td>2110-06-19</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62065073</td>\n",
       "      <td>2074-05-27</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62100837</td>\n",
       "      <td>2136-07-22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   person_id birth_datetime  gender_F  gender_M  gender_NaN  \\\n",
       "0   62065089     2107-06-29         1         0           0   \n",
       "1   62106569     2047-11-01         0         1           0   \n",
       "2   62073299     2190-04-23         1         0           0   \n",
       "3   62096906     2182-09-07         0         1           0   \n",
       "4   62102055     2087-02-28         0         1           0   \n",
       "5   62106786     2161-03-31         1         0           0   \n",
       "6   62083063     2125-12-16         1         0           0   \n",
       "7   62078234     2110-06-19         0         1           0   \n",
       "8   62065073     2074-05-27         1         0           0   \n",
       "9   62100837     2136-07-22         1         0           0   \n",
       "\n",
       "   race_AMERICAN INDIAN/ALASKA NATIVE  \\\n",
       "0                                   0   \n",
       "1                                   0   \n",
       "2                                   0   \n",
       "3                                   0   \n",
       "4                                   0   \n",
       "5                                   0   \n",
       "6                                   0   \n",
       "7                                   0   \n",
       "8                                   0   \n",
       "9                                   0   \n",
       "\n",
       "   race_AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGNIZED TRIBE  race_ASIAN  \\\n",
       "0                                                  0                       1   \n",
       "1                                                  0                       1   \n",
       "2                                                  0                       1   \n",
       "3                                                  0                       1   \n",
       "4                                                  0                       1   \n",
       "5                                                  0                       1   \n",
       "6                                                  0                       1   \n",
       "7                                                  0                       1   \n",
       "8                                                  0                       1   \n",
       "9                                                  0                       1   \n",
       "\n",
       "   race_ASIAN - ASIAN INDIAN  race_ASIAN - CAMBODIAN    ...     \\\n",
       "0                          0                       0    ...      \n",
       "1                          0                       0    ...      \n",
       "2                          0                       0    ...      \n",
       "3                          0                       0    ...      \n",
       "4                          0                       0    ...      \n",
       "5                          0                       0    ...      \n",
       "6                          0                       0    ...      \n",
       "7                          0                       0    ...      \n",
       "8                          0                       0    ...      \n",
       "9                          0                       0    ...      \n",
       "\n",
       "   race_PORTUGUESE  race_SOUTH AMERICAN  race_UNABLE TO OBTAIN  \\\n",
       "0                0                    0                      0   \n",
       "1                0                    0                      0   \n",
       "2                0                    0                      0   \n",
       "3                0                    0                      0   \n",
       "4                0                    0                      0   \n",
       "5                0                    0                      0   \n",
       "6                0                    0                      0   \n",
       "7                0                    0                      0   \n",
       "8                0                    0                      0   \n",
       "9                0                    0                      0   \n",
       "\n",
       "   race_UNKNOWN/NOT SPECIFIED  race_WHITE  race_WHITE - BRAZILIAN  \\\n",
       "0                           0           0                       0   \n",
       "1                           0           0                       0   \n",
       "2                           0           0                       0   \n",
       "3                           0           0                       0   \n",
       "4                           0           0                       0   \n",
       "5                           0           0                       0   \n",
       "6                           0           0                       0   \n",
       "7                           0           0                       0   \n",
       "8                           0           0                       0   \n",
       "9                           0           0                       0   \n",
       "\n",
       "   race_WHITE - EASTERN EUROPEAN  race_WHITE - OTHER EUROPEAN  \\\n",
       "0                              0                            0   \n",
       "1                              0                            0   \n",
       "2                              0                            0   \n",
       "3                              0                            0   \n",
       "4                              0                            0   \n",
       "5                              0                            0   \n",
       "6                              0                            0   \n",
       "7                              0                            0   \n",
       "8                              0                            0   \n",
       "9                              0                            0   \n",
       "\n",
       "   race_WHITE - RUSSIAN  race_NaN  \n",
       "0                     0         0  \n",
       "1                     0         0  \n",
       "2                     0         0  \n",
       "3                     0         0  \n",
       "4                     0         0  \n",
       "5                     0         0  \n",
       "6                     0         0  \n",
       "7                     0         0  \n",
       "8                     0         0  \n",
       "9                     0         0  \n",
       "\n",
       "[10 rows x 47 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta.iloc[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:58.012201Z",
     "start_time": "2018-05-13T14:01:58.009385Z"
    }
   },
   "outputs": [],
   "source": [
    "meta_names = meta.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract measures"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-12T17:47:55.054519Z",
     "start_time": "2018-05-12T17:47:55.045083Z"
    }
   },
   "source": [
    "Among measures, we need to get all the available classes for each categorical variables (in order to one-hot encode properly each batch of data i.e. for each patient):\n",
    "    - \"Heart rate rhythm\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:01:58.721751Z",
     "start_time": "2018-05-13T14:01:58.014501Z"
    }
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select\n",
    "    distinct m.measurement_concept_name, m.value_source_value\n",
    "from \n",
    "    measurement m \n",
    "where\n",
    "    m.measurement_concept_id in\n",
    "    (3022318   -- heart_rhythm\n",
    "    )\n",
    "order by\n",
    "    m.measurement_concept_name, m.value_source_value\n",
    ";\"\"\"\n",
    "\n",
    "unique_categ_values = pd.read_sql_query(query, conn)\n",
    "\n",
    "categorical_variables = ['Heart rate rhythm']\n",
    "\n",
    "# Adding categories of each categorical variables to dict 'categories'\n",
    "for var in categorical_variables:\n",
    "    tmp = unique_categ_values.loc[unique_categ_values.measurement_concept_name == var]\n",
    "    tmp.drop('measurement_concept_name', axis=1, inplace=True)\n",
    "    tmp.rename(index=str, columns={'value_source_value': var}, inplace=True)\n",
    "    categories = add_categories(categories, tmp, var)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### SQL requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:15:38.877912Z",
     "start_time": "2018-05-17T11:15:38.873844Z"
    }
   },
   "outputs": [],
   "source": [
    "patient_id = (62063368, 62106569)  # person_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:16:21.803090Z",
     "start_time": "2018-05-17T11:16:16.882400Z"
    }
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select\n",
    "    distinct m.person_id, m.measurement_datetime, m.measurement_concept_name, m.value_source_value, m.unit_source_value, d.death_datetime\n",
    "from \n",
    "    measurement m \n",
    "left join \n",
    "    death d on d.person_id = m.person_id\n",
    "where\n",
    "    m.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 m.person_id in {}\n",
    "order by m.person_id, m.measurement_datetime\n",
    ";\"\"\".format(patient_id)\n",
    "\n",
    "measures = pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:15:44.812306Z",
     "start_time": "2018-05-17T11:15:44.775075Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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",
       "      <th>death_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>38</td>\n",
       "      <td>BPM</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>97</td>\n",
       "      <td>%</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>107.33300018310547</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Glasgow coma scale</td>\n",
       "      <td>15</td>\n",
       "      <td>points</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>36.388900756835938</td>\n",
       "      <td>Deg. C</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>88</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>BP systolic</td>\n",
       "      <td>146</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>97.5</td>\n",
       "      <td>Deg. F</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:30:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>110</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   person_id measurement_datetime             measurement_concept_name  \\\n",
       "0   62063368  2188-11-12 10:00:00                     Respiratory rate   \n",
       "1   62063368  2188-11-12 10:00:00  Oxygen saturation in Arterial blood   \n",
       "2   62063368  2188-11-12 10:00:00                  Mean blood pressure   \n",
       "3   62063368  2188-11-12 10:00:00                   Glasgow coma scale   \n",
       "4   62063368  2188-11-12 10:00:00                     Body temperature   \n",
       "5   62063368  2188-11-12 10:00:00                         BP diastolic   \n",
       "6   62063368  2188-11-12 10:00:00                          BP systolic   \n",
       "7   62063368  2188-11-12 10:00:00                     Body temperature   \n",
       "8   62063368  2188-11-12 10:00:00                    Heart rate rhythm   \n",
       "9   62063368  2188-11-12 10:30:00                  Mean blood pressure   \n",
       "\n",
       "   value_source_value unit_source_value      death_datetime  \n",
       "0                  38               BPM 2188-11-22 12:00:00  \n",
       "1                  97                 % 2188-11-22 12:00:00  \n",
       "2  107.33300018310547              mmHg 2188-11-22 12:00:00  \n",
       "3                  15            points 2188-11-22 12:00:00  \n",
       "4  36.388900756835938            Deg. C 2188-11-22 12:00:00  \n",
       "5                  88              mmHg 2188-11-22 12:00:00  \n",
       "6                 146              mmHg 2188-11-22 12:00:00  \n",
       "7                97.5            Deg. F 2188-11-22 12:00:00  \n",
       "8         Sinus Tachy              None 2188-11-22 12:00:00  \n",
       "9                 110              mmHg 2188-11-22 12:00:00  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "measures.iloc[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Change type datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:16:22.800085Z",
     "start_time": "2018-05-17T11:16:22.778856Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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",
       "      <th>death_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Respiratory rate</td>\n",
       "      <td>38</td>\n",
       "      <td>BPM</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Oxygen saturation in Arterial blood</td>\n",
       "      <td>97</td>\n",
       "      <td>%</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>107.33300018310547</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Glasgow coma scale</td>\n",
       "      <td>15</td>\n",
       "      <td>points</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>36.388900756835938</td>\n",
       "      <td>Deg. C</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>BP diastolic</td>\n",
       "      <td>88</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>BP systolic</td>\n",
       "      <td>146</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Body temperature</td>\n",
       "      <td>97.5</td>\n",
       "      <td>Deg. F</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:00:00</td>\n",
       "      <td>Heart rate rhythm</td>\n",
       "      <td>Sinus Tachy</td>\n",
       "      <td>None</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-12 10:30:00</td>\n",
       "      <td>Mean blood pressure</td>\n",
       "      <td>110</td>\n",
       "      <td>mmHg</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   person_id measurement_datetime             measurement_concept_name  \\\n",
       "0   62063368  2188-11-12 10:00:00                     Respiratory rate   \n",
       "1   62063368  2188-11-12 10:00:00  Oxygen saturation in Arterial blood   \n",
       "2   62063368  2188-11-12 10:00:00                  Mean blood pressure   \n",
       "3   62063368  2188-11-12 10:00:00                   Glasgow coma scale   \n",
       "4   62063368  2188-11-12 10:00:00                     Body temperature   \n",
       "5   62063368  2188-11-12 10:00:00                         BP diastolic   \n",
       "6   62063368  2188-11-12 10:00:00                          BP systolic   \n",
       "7   62063368  2188-11-12 10:00:00                     Body temperature   \n",
       "8   62063368  2188-11-12 10:00:00                    Heart rate rhythm   \n",
       "9   62063368  2188-11-12 10:30:00                  Mean blood pressure   \n",
       "\n",
       "   value_source_value unit_source_value      death_datetime  \n",
       "0                  38               BPM 2188-11-22 12:00:00  \n",
       "1                  97                 % 2188-11-22 12:00:00  \n",
       "2  107.33300018310547              mmHg 2188-11-22 12:00:00  \n",
       "3                  15            points 2188-11-22 12:00:00  \n",
       "4  36.388900756835938            Deg. C 2188-11-22 12:00:00  \n",
       "5                  88              mmHg 2188-11-22 12:00:00  \n",
       "6                 146              mmHg 2188-11-22 12:00:00  \n",
       "7                97.5            Deg. F 2188-11-22 12:00:00  \n",
       "8         Sinus Tachy              None 2188-11-22 12:00:00  \n",
       "9                 110              mmHg 2188-11-22 12:00:00  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "measures['death_datetime'] = pd.to_datetime(measures['death_datetime'])\n",
    "measures['measurement_datetime'] = pd.to_datetime(measures['measurement_datetime'])\n",
    "measures.iloc[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add target value (y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:16:24.187897Z",
     "start_time": "2018-05-17T11:16:24.183320Z"
    }
   },
   "outputs": [],
   "source": [
    "from fleming_lib.metrics import add_target, add_super_target"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:16:25.433413Z",
     "start_time": "2018-05-17T11:16:24.667480Z"
    }
   },
   "outputs": [],
   "source": [
    "measures = measures.groupby('person_id').apply(add_target)\n",
    "measures = measures.groupby('person_id').apply(add_super_target)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Row to columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:16:28.083479Z",
     "start_time": "2018-05-17T11:16:28.057171Z"
    }
   },
   "outputs": [],
   "source": [
    "measures = measures.pivot_table(index=['measurement_datetime','target','super_target','person_id'], columns='measurement_concept_name', values='value_source_value', aggfunc='first')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reset index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:16:29.254424Z",
     "start_time": "2018-05-17T11:16:29.246615Z"
    }
   },
   "outputs": [],
   "source": [
    "measures.reset_index(inplace=True)\n",
    "measures.columns.name = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:17:21.853072Z",
     "start_time": "2018-05-17T11:17:21.814003Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>measurement_datetime</th>\n",
       "      <th>target</th>\n",
       "      <th>super_target</th>\n",
       "      <th>person_id</th>\n",
       "      <th>BP diastolic</th>\n",
       "      <th>BP systolic</th>\n",
       "      <th>Body temperature</th>\n",
       "      <th>Glasgow coma scale</th>\n",
       "      <th>Heart rate</th>\n",
       "      <th>Heart rate rhythm</th>\n",
       "      <th>Mean blood pressure</th>\n",
       "      <th>Mean pressure Respiratory system airway Calculated</th>\n",
       "      <th>Oxygen concentration breathed</th>\n",
       "      <th>Oxygen saturation in Arterial blood</th>\n",
       "      <th>Respiratory rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2111-04-13 16:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LBBB (Left Bundle Branch Block)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2111-04-13 16:40:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>93</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2111-04-13 16:42:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2111-04-13 16:45:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>SR (Sinus Rhythm)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2111-04-13 16:47:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>96</td>\n",
       "      <td>181</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2111-04-13 17:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>84</td>\n",
       "      <td>175</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2111-04-13 17:01:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88</td>\n",
       "      <td>LBBB (Left Bundle Branch Block)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2111-04-13 17:15:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>92</td>\n",
       "      <td>185</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>LBBB (Left Bundle Branch Block)</td>\n",
       "      <td>114</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2111-04-13 17:30:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>96</td>\n",
       "      <td>184</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88</td>\n",
       "      <td>LBBB (Left Bundle Branch Block)</td>\n",
       "      <td>114</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2111-04-13 17:45:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>103</td>\n",
       "      <td>197</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>LBBB (Left Bundle Branch Block)</td>\n",
       "      <td>114</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  measurement_datetime  target  super_target  person_id BP diastolic  \\\n",
       "0  2111-04-13 16:00:00       0             0   62106569          NaN   \n",
       "1  2111-04-13 16:40:00       0             0   62106569          NaN   \n",
       "2  2111-04-13 16:42:00       0             0   62106569          NaN   \n",
       "3  2111-04-13 16:45:00       0             0   62106569          NaN   \n",
       "4  2111-04-13 16:47:00       0             0   62106569           96   \n",
       "5  2111-04-13 17:00:00       0             0   62106569           84   \n",
       "6  2111-04-13 17:01:00       0             0   62106569          NaN   \n",
       "7  2111-04-13 17:15:00       0             0   62106569           92   \n",
       "8  2111-04-13 17:30:00       0             0   62106569           96   \n",
       "9  2111-04-13 17:45:00       0             0   62106569          103   \n",
       "\n",
       "  BP systolic Body temperature Glasgow coma scale Heart rate  \\\n",
       "0         NaN              NaN                NaN        NaN   \n",
       "1         NaN              NaN                NaN         93   \n",
       "2         NaN              NaN                NaN        NaN   \n",
       "3         NaN              NaN                NaN         92   \n",
       "4         181              NaN                NaN        NaN   \n",
       "5         175              NaN                NaN         87   \n",
       "6         NaN             99.1                NaN         88   \n",
       "7         185              NaN                NaN         87   \n",
       "8         184              NaN                NaN         88   \n",
       "9         197              NaN                NaN         90   \n",
       "\n",
       "                  Heart rate rhythm Mean blood pressure  \\\n",
       "0  LBBB (Left Bundle Branch Block)                  NaN   \n",
       "1                               NaN                 NaN   \n",
       "2                               NaN                 NaN   \n",
       "3                 SR (Sinus Rhythm)                 NaN   \n",
       "4                               NaN                 115   \n",
       "5                               NaN                 102   \n",
       "6  LBBB (Left Bundle Branch Block)                  NaN   \n",
       "7  LBBB (Left Bundle Branch Block)                  114   \n",
       "8  LBBB (Left Bundle Branch Block)                  114   \n",
       "9  LBBB (Left Bundle Branch Block)                  114   \n",
       "\n",
       "  Mean pressure Respiratory system airway Calculated  \\\n",
       "0                                                NaN   \n",
       "1                                                NaN   \n",
       "2                                                NaN   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "5                                                NaN   \n",
       "6                                                NaN   \n",
       "7                                                NaN   \n",
       "8                                                NaN   \n",
       "9                                                NaN   \n",
       "\n",
       "  Oxygen concentration breathed Oxygen saturation in Arterial blood  \\\n",
       "0                           NaN                                 NaN   \n",
       "1                           NaN                                  96   \n",
       "2                           NaN                                 NaN   \n",
       "3                           NaN                                  97   \n",
       "4                           NaN                                 NaN   \n",
       "5                           NaN                                  96   \n",
       "6                           NaN                                  97   \n",
       "7                           NaN                                  96   \n",
       "8                           NaN                                  95   \n",
       "9                           NaN                                  96   \n",
       "\n",
       "  Respiratory rate  \n",
       "0              NaN  \n",
       "1              NaN  \n",
       "2               25  \n",
       "3               24  \n",
       "4              NaN  \n",
       "5               21  \n",
       "6               22  \n",
       "7               21  \n",
       "8               20  \n",
       "9               22  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "measures.iloc[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Change types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:03:27.715179Z",
     "start_time": "2018-05-13T14:03:27.707101Z"
    }
   },
   "outputs": [],
   "source": [
    "from fleming_lib.utils import to_categorical, convert_frac, to_numeric, to_onehot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Numerical variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:03:31.559146Z",
     "start_time": "2018-05-13T14:03:31.476403Z"
    }
   },
   "outputs": [],
   "source": [
    "numerical_variables = ['BP diastolic', 'BP systolic', 'Body temperature', 'Heart rate', 'Mean blood pressure', 'Glasgow coma scale', 'Oxygen concentration breathed', 'Mean pressure Respiratory system airway Calculated', 'Oxygen saturation in Arterial blood', 'Respiratory rate']\n",
    "\n",
    "measures = convert_frac(measures, numerical_variables)\n",
    "measures = to_numeric(measures, numerical_variables)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-12T14:38:39.891408Z",
     "start_time": "2018-05-12T14:38:39.883725Z"
    }
   },
   "source": [
    "Categorical variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:03:32.172695Z",
     "start_time": "2018-05-13T14:03:32.155459Z"
    }
   },
   "outputs": [],
   "source": [
    "categorical_variables = ['Heart rate rhythm']\n",
    "\n",
    "measures = to_categorical(measures, categorical_variables, categories)\n",
    "# One-hot encode categorical variables\n",
    "measures = to_onehot(measures, categorical_variables)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add meta data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:08:44.087086Z",
     "start_time": "2018-05-13T14:08:44.058008Z"
    }
   },
   "outputs": [],
   "source": [
    "data = pd.merge(measures, meta, on='person_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:08:46.757488Z",
     "start_time": "2018-05-13T14:08:46.709689Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>measurement_datetime</th>\n",
       "      <th>target</th>\n",
       "      <th>super_target</th>\n",
       "      <th>person_id</th>\n",
       "      <th>BP diastolic</th>\n",
       "      <th>BP systolic</th>\n",
       "      <th>Body temperature</th>\n",
       "      <th>Glasgow coma scale</th>\n",
       "      <th>Heart rate</th>\n",
       "      <th>Mean blood pressure</th>\n",
       "      <th>...</th>\n",
       "      <th>race_PORTUGUESE</th>\n",
       "      <th>race_SOUTH AMERICAN</th>\n",
       "      <th>race_UNABLE TO OBTAIN</th>\n",
       "      <th>race_UNKNOWN/NOT SPECIFIED</th>\n",
       "      <th>race_WHITE</th>\n",
       "      <th>race_WHITE - BRAZILIAN</th>\n",
       "      <th>race_WHITE - EASTERN EUROPEAN</th>\n",
       "      <th>race_WHITE - OTHER EUROPEAN</th>\n",
       "      <th>race_WHITE - RUSSIAN</th>\n",
       "      <th>race_NaN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2111-04-13 16:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2111-04-13 16:40:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>93.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2111-04-13 16:42:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2111-04-13 16:45:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2111-04-13 16:47:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>96.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>115.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2111-04-13 17:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>84.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2111-04-13 17:01:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2111-04-13 17:15:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>92.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2111-04-13 17:30:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>96.0</td>\n",
       "      <td>184.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2111-04-13 17:45:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>103.0</td>\n",
       "      <td>197.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 112 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  measurement_datetime  target  super_target  person_id  BP diastolic  \\\n",
       "0  2111-04-13 16:00:00       0             0   62106569           NaN   \n",
       "1  2111-04-13 16:40:00       0             0   62106569           NaN   \n",
       "2  2111-04-13 16:42:00       0             0   62106569           NaN   \n",
       "3  2111-04-13 16:45:00       0             0   62106569           NaN   \n",
       "4  2111-04-13 16:47:00       0             0   62106569          96.0   \n",
       "5  2111-04-13 17:00:00       0             0   62106569          84.0   \n",
       "6  2111-04-13 17:01:00       0             0   62106569           NaN   \n",
       "7  2111-04-13 17:15:00       0             0   62106569          92.0   \n",
       "8  2111-04-13 17:30:00       0             0   62106569          96.0   \n",
       "9  2111-04-13 17:45:00       0             0   62106569         103.0   \n",
       "\n",
       "   BP systolic  Body temperature  Glasgow coma scale  Heart rate  \\\n",
       "0          NaN               NaN                 NaN         NaN   \n",
       "1          NaN               NaN                 NaN        93.0   \n",
       "2          NaN               NaN                 NaN         NaN   \n",
       "3          NaN               NaN                 NaN        92.0   \n",
       "4        181.0               NaN                 NaN         NaN   \n",
       "5        175.0               NaN                 NaN        87.0   \n",
       "6          NaN              99.1                 NaN        88.0   \n",
       "7        185.0               NaN                 NaN        87.0   \n",
       "8        184.0               NaN                 NaN        88.0   \n",
       "9        197.0               NaN                 NaN        90.0   \n",
       "\n",
       "   Mean blood pressure    ...     race_PORTUGUESE  race_SOUTH AMERICAN  \\\n",
       "0                  NaN    ...                   0                    0   \n",
       "1                  NaN    ...                   0                    0   \n",
       "2                  NaN    ...                   0                    0   \n",
       "3                  NaN    ...                   0                    0   \n",
       "4                115.0    ...                   0                    0   \n",
       "5                102.0    ...                   0                    0   \n",
       "6                  NaN    ...                   0                    0   \n",
       "7                114.0    ...                   0                    0   \n",
       "8                114.0    ...                   0                    0   \n",
       "9                114.0    ...                   0                    0   \n",
       "\n",
       "   race_UNABLE TO OBTAIN  race_UNKNOWN/NOT SPECIFIED  race_WHITE  \\\n",
       "0                      0                           0           0   \n",
       "1                      0                           0           0   \n",
       "2                      0                           0           0   \n",
       "3                      0                           0           0   \n",
       "4                      0                           0           0   \n",
       "5                      0                           0           0   \n",
       "6                      0                           0           0   \n",
       "7                      0                           0           0   \n",
       "8                      0                           0           0   \n",
       "9                      0                           0           0   \n",
       "\n",
       "   race_WHITE - BRAZILIAN  race_WHITE - EASTERN EUROPEAN  \\\n",
       "0                       0                              0   \n",
       "1                       0                              0   \n",
       "2                       0                              0   \n",
       "3                       0                              0   \n",
       "4                       0                              0   \n",
       "5                       0                              0   \n",
       "6                       0                              0   \n",
       "7                       0                              0   \n",
       "8                       0                              0   \n",
       "9                       0                              0   \n",
       "\n",
       "   race_WHITE - OTHER EUROPEAN  race_WHITE - RUSSIAN  race_NaN  \n",
       "0                            0                     0         0  \n",
       "1                            0                     0         0  \n",
       "2                            0                     0         0  \n",
       "3                            0                     0         0  \n",
       "4                            0                     0         0  \n",
       "5                            0                     0         0  \n",
       "6                            0                     0         0  \n",
       "7                            0                     0         0  \n",
       "8                            0                     0         0  \n",
       "9                            0                     0         0  \n",
       "\n",
       "[10 rows x 112 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.iloc[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compute age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:08:50.416280Z",
     "start_time": "2018-05-13T14:08:50.413445Z"
    }
   },
   "outputs": [],
   "source": [
    "from fleming_lib.metrics import add_age, add_rolling_avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:09:01.885144Z",
     "start_time": "2018-05-13T14:09:01.690320Z"
    }
   },
   "outputs": [],
   "source": [
    "data = data.groupby('person_id').apply(add_age)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:09:06.501184Z",
     "start_time": "2018-05-13T14:09:06.453704Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>measurement_datetime</th>\n",
       "      <th>target</th>\n",
       "      <th>super_target</th>\n",
       "      <th>person_id</th>\n",
       "      <th>BP diastolic</th>\n",
       "      <th>BP systolic</th>\n",
       "      <th>Body temperature</th>\n",
       "      <th>Glasgow coma scale</th>\n",
       "      <th>Heart rate</th>\n",
       "      <th>Mean blood pressure</th>\n",
       "      <th>...</th>\n",
       "      <th>race_SOUTH AMERICAN</th>\n",
       "      <th>race_UNABLE TO OBTAIN</th>\n",
       "      <th>race_UNKNOWN/NOT SPECIFIED</th>\n",
       "      <th>race_WHITE</th>\n",
       "      <th>race_WHITE - BRAZILIAN</th>\n",
       "      <th>race_WHITE - EASTERN EUROPEAN</th>\n",
       "      <th>race_WHITE - OTHER EUROPEAN</th>\n",
       "      <th>race_WHITE - RUSSIAN</th>\n",
       "      <th>race_NaN</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2111-04-13 16:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2111-04-13 16:40:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>93.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2111-04-13 16:42:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2111-04-13 16:45:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2111-04-13 16:47:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>96.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>115.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2111-04-13 17:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>84.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2111-04-13 17:01:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2111-04-13 17:15:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>92.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2111-04-13 17:30:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>96.0</td>\n",
       "      <td>184.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2111-04-13 17:45:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>103.0</td>\n",
       "      <td>197.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 112 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  measurement_datetime  target  super_target  person_id  BP diastolic  \\\n",
       "0  2111-04-13 16:00:00       0             0   62106569           NaN   \n",
       "1  2111-04-13 16:40:00       0             0   62106569           NaN   \n",
       "2  2111-04-13 16:42:00       0             0   62106569           NaN   \n",
       "3  2111-04-13 16:45:00       0             0   62106569           NaN   \n",
       "4  2111-04-13 16:47:00       0             0   62106569          96.0   \n",
       "5  2111-04-13 17:00:00       0             0   62106569          84.0   \n",
       "6  2111-04-13 17:01:00       0             0   62106569           NaN   \n",
       "7  2111-04-13 17:15:00       0             0   62106569          92.0   \n",
       "8  2111-04-13 17:30:00       0             0   62106569          96.0   \n",
       "9  2111-04-13 17:45:00       0             0   62106569         103.0   \n",
       "\n",
       "   BP systolic  Body temperature  Glasgow coma scale  Heart rate  \\\n",
       "0          NaN               NaN                 NaN         NaN   \n",
       "1          NaN               NaN                 NaN        93.0   \n",
       "2          NaN               NaN                 NaN         NaN   \n",
       "3          NaN               NaN                 NaN        92.0   \n",
       "4        181.0               NaN                 NaN         NaN   \n",
       "5        175.0               NaN                 NaN        87.0   \n",
       "6          NaN              99.1                 NaN        88.0   \n",
       "7        185.0               NaN                 NaN        87.0   \n",
       "8        184.0               NaN                 NaN        88.0   \n",
       "9        197.0               NaN                 NaN        90.0   \n",
       "\n",
       "   Mean blood pressure  ...   race_SOUTH AMERICAN  race_UNABLE TO OBTAIN  \\\n",
       "0                  NaN  ...                     0                      0   \n",
       "1                  NaN  ...                     0                      0   \n",
       "2                  NaN  ...                     0                      0   \n",
       "3                  NaN  ...                     0                      0   \n",
       "4                115.0  ...                     0                      0   \n",
       "5                102.0  ...                     0                      0   \n",
       "6                  NaN  ...                     0                      0   \n",
       "7                114.0  ...                     0                      0   \n",
       "8                114.0  ...                     0                      0   \n",
       "9                114.0  ...                     0                      0   \n",
       "\n",
       "   race_UNKNOWN/NOT SPECIFIED  race_WHITE  race_WHITE - BRAZILIAN  \\\n",
       "0                           0           0                       0   \n",
       "1                           0           0                       0   \n",
       "2                           0           0                       0   \n",
       "3                           0           0                       0   \n",
       "4                           0           0                       0   \n",
       "5                           0           0                       0   \n",
       "6                           0           0                       0   \n",
       "7                           0           0                       0   \n",
       "8                           0           0                       0   \n",
       "9                           0           0                       0   \n",
       "\n",
       "   race_WHITE - EASTERN EUROPEAN  race_WHITE - OTHER EUROPEAN  \\\n",
       "0                              0                            0   \n",
       "1                              0                            0   \n",
       "2                              0                            0   \n",
       "3                              0                            0   \n",
       "4                              0                            0   \n",
       "5                              0                            0   \n",
       "6                              0                            0   \n",
       "7                              0                            0   \n",
       "8                              0                            0   \n",
       "9                              0                            0   \n",
       "\n",
       "   race_WHITE - RUSSIAN  race_NaN   age  \n",
       "0                     0         0  63.4  \n",
       "1                     0         0  63.4  \n",
       "2                     0         0  63.4  \n",
       "3                     0         0  63.4  \n",
       "4                     0         0  63.4  \n",
       "5                     0         0  63.4  \n",
       "6                     0         0  63.4  \n",
       "7                     0         0  63.4  \n",
       "8                     0         0  63.4  \n",
       "9                     0         0  63.4  \n",
       "\n",
       "[10 rows x 112 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.iloc[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data enhancement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add rolling mean "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T14:10:14.657555Z",
     "start_time": "2018-05-13T14:10:13.070048Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>measurement_datetime</th>\n",
       "      <th>target</th>\n",
       "      <th>super_target</th>\n",
       "      <th>person_id</th>\n",
       "      <th>BP diastolic</th>\n",
       "      <th>BP systolic</th>\n",
       "      <th>Body temperature</th>\n",
       "      <th>Glasgow coma scale</th>\n",
       "      <th>Heart rate</th>\n",
       "      <th>Mean blood pressure</th>\n",
       "      <th>...</th>\n",
       "      <th>race_UNABLE TO OBTAIN</th>\n",
       "      <th>race_UNKNOWN/NOT SPECIFIED</th>\n",
       "      <th>race_WHITE</th>\n",
       "      <th>race_WHITE - BRAZILIAN</th>\n",
       "      <th>race_WHITE - EASTERN EUROPEAN</th>\n",
       "      <th>race_WHITE - OTHER EUROPEAN</th>\n",
       "      <th>race_WHITE - RUSSIAN</th>\n",
       "      <th>race_NaN</th>\n",
       "      <th>age</th>\n",
       "      <th>Respiratory rate avg h-2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2111-04-13 16:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2111-04-13 16:40:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>93.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2111-04-13 16:42:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2111-04-13 16:45:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>25.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2111-04-13 16:47:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>96.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>115.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>24.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2111-04-13 17:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>84.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>24.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2111-04-13 17:01:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>23.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2111-04-13 17:15:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>92.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>23.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2111-04-13 17:30:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>96.0</td>\n",
       "      <td>184.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>22.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2111-04-13 17:45:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62106569</td>\n",
       "      <td>103.0</td>\n",
       "      <td>197.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>22.166667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 113 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  measurement_datetime  target  super_target  person_id  BP diastolic  \\\n",
       "0  2111-04-13 16:00:00       0             0   62106569           NaN   \n",
       "1  2111-04-13 16:40:00       0             0   62106569           NaN   \n",
       "2  2111-04-13 16:42:00       0             0   62106569           NaN   \n",
       "3  2111-04-13 16:45:00       0             0   62106569           NaN   \n",
       "4  2111-04-13 16:47:00       0             0   62106569          96.0   \n",
       "5  2111-04-13 17:00:00       0             0   62106569          84.0   \n",
       "6  2111-04-13 17:01:00       0             0   62106569           NaN   \n",
       "7  2111-04-13 17:15:00       0             0   62106569          92.0   \n",
       "8  2111-04-13 17:30:00       0             0   62106569          96.0   \n",
       "9  2111-04-13 17:45:00       0             0   62106569         103.0   \n",
       "\n",
       "   BP systolic  Body temperature  Glasgow coma scale  Heart rate  \\\n",
       "0          NaN               NaN                 NaN         NaN   \n",
       "1          NaN               NaN                 NaN        93.0   \n",
       "2          NaN               NaN                 NaN         NaN   \n",
       "3          NaN               NaN                 NaN        92.0   \n",
       "4        181.0               NaN                 NaN         NaN   \n",
       "5        175.0               NaN                 NaN        87.0   \n",
       "6          NaN              99.1                 NaN        88.0   \n",
       "7        185.0               NaN                 NaN        87.0   \n",
       "8        184.0               NaN                 NaN        88.0   \n",
       "9        197.0               NaN                 NaN        90.0   \n",
       "\n",
       "   Mean blood pressure            ...             race_UNABLE TO OBTAIN  \\\n",
       "0                  NaN            ...                                 0   \n",
       "1                  NaN            ...                                 0   \n",
       "2                  NaN            ...                                 0   \n",
       "3                  NaN            ...                                 0   \n",
       "4                115.0            ...                                 0   \n",
       "5                102.0            ...                                 0   \n",
       "6                  NaN            ...                                 0   \n",
       "7                114.0            ...                                 0   \n",
       "8                114.0            ...                                 0   \n",
       "9                114.0            ...                                 0   \n",
       "\n",
       "   race_UNKNOWN/NOT SPECIFIED  race_WHITE  race_WHITE - BRAZILIAN  \\\n",
       "0                           0           0                       0   \n",
       "1                           0           0                       0   \n",
       "2                           0           0                       0   \n",
       "3                           0           0                       0   \n",
       "4                           0           0                       0   \n",
       "5                           0           0                       0   \n",
       "6                           0           0                       0   \n",
       "7                           0           0                       0   \n",
       "8                           0           0                       0   \n",
       "9                           0           0                       0   \n",
       "\n",
       "   race_WHITE - EASTERN EUROPEAN  race_WHITE - OTHER EUROPEAN  \\\n",
       "0                              0                            0   \n",
       "1                              0                            0   \n",
       "2                              0                            0   \n",
       "3                              0                            0   \n",
       "4                              0                            0   \n",
       "5                              0                            0   \n",
       "6                              0                            0   \n",
       "7                              0                            0   \n",
       "8                              0                            0   \n",
       "9                              0                            0   \n",
       "\n",
       "   race_WHITE - RUSSIAN  race_NaN   age  Respiratory rate avg h-2  \n",
       "0                     0         0  63.4                       NaN  \n",
       "1                     0         0  63.4                       NaN  \n",
       "2                     0         0  63.4                       NaN  \n",
       "3                     0         0  63.4                 25.000000  \n",
       "4                     0         0  63.4                 24.500000  \n",
       "5                     0         0  63.4                 24.500000  \n",
       "6                     0         0  63.4                 23.333333  \n",
       "7                     0         0  63.4                 23.000000  \n",
       "8                     0         0  63.4                 22.600000  \n",
       "9                     0         0  63.4                 22.166667  \n",
       "\n",
       "[10 rows x 113 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = data.groupby('person_id').apply(add_rolling_avg, column='Respiratory rate', window=2)\n",
    "\n",
    "data.iloc[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:18:42.947079Z",
     "start_time": "2018-05-17T11:18:42.246499Z"
    }
   },
   "outputs": [],
   "source": [
    "import time\n",
    "import warnings\n",
    "\n",
    "from fleming_lib.metrics import add_rolling_avg, add_target, add_super_target, add_age\n",
    "from fleming_lib.utils import to_categorical, to_onehot, to_numeric, convert_frac, add_categories, add_missing_columns, check_length\n",
    "\n",
    "\n",
    "def create_dataset(list_patients, n_patients_per_batch=10, verbose=False):\n",
    "    \"\"\"Create list of dataset given a list of patients.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    list_patients : list of int\n",
    "        List of patients ID.\n",
    "    n_patients_per_batch : int (default=10)\n",
    "        Number of patients to sequentially load data for, in order not to cause timeout if the query is too long to\n",
    "        process by the server.\n",
    "    verbose : bool (default=False)\n",
    "        Verbosity level.\n",
    "    \n",
    "    Returns\n",
    "    -------\n",
    "    dataset : pd.DataFrame\n",
    "        Dataset containing all data associated to each patient.\n",
    "        \n",
    "    \"\"\"\n",
    "    t0 = time.time()\n",
    "    frame = []\n",
    "    \n",
    "    if not isinstance(list_patients, list):\n",
    "        list_patients = [list_patients]\n",
    "        \n",
    "    n_patients = len(list_patients)\n",
    "    \n",
    "    # Extract meta data\n",
    "    # -----------------\n",
    "    if verbose:\n",
    "        msg = 'Extracting meta data...'\n",
    "        delta_t = str(int(time.time() - t0)) + ' s'\n",
    "        print('{:100s} [{:10s}]'.format(msg, delta_t), end='\\r')\n",
    "            \n",
    "    query = \"\"\"\n",
    "    select\n",
    "        distinct p.person_id, p.gender_source_value gender, p.race_source_value race, p.birth_datetime\n",
    "    from\n",
    "        person p\n",
    "        ;\"\"\"\n",
    "\n",
    "    meta = pd.read_sql_query(query, conn)\n",
    "\n",
    "    # Dictionary containing unique categories for each categorical variable\n",
    "    categories = dict()\n",
    "    \n",
    "    # Convert categorical variable to 'categorical' type\n",
    "    categorical_variables = ['gender', 'race']\n",
    "    # Extracting categories for each categorical variable\n",
    "    categories = add_categories(categories, meta, categorical_variables)\n",
    "    \n",
    "    meta = to_categorical(meta, categorical_variables, categories)\n",
    "    meta = to_onehot(meta, categorical_variables)\n",
    "    # One-hot column names\n",
    "    meta_names = meta.columns\n",
    "    \n",
    "    # Extract unique measurements values from categorical variables\n",
    "    # (here 'Heart rate rhythm')\n",
    "    query = \"\"\"\n",
    "        select\n",
    "            distinct m.measurement_concept_name, m.value_source_value\n",
    "        from \n",
    "            measurement m \n",
    "        where\n",
    "            m.measurement_concept_id in\n",
    "            (3022318   -- heart_rhythm\n",
    "            )\n",
    "        order by\n",
    "            m.measurement_concept_name, m.value_source_value\n",
    "        ;\"\"\"\n",
    "\n",
    "    unique_categ_values = pd.read_sql_query(query, conn)\n",
    "    categorical_variables = ['Heart rate rhythm']\n",
    "    # Adding categories of each categorical variables to dict 'categories'\n",
    "    for var in categorical_variables:\n",
    "        tmp = unique_categ_values.loc[unique_categ_values.measurement_concept_name == var]\n",
    "        tmp.drop('measurement_concept_name', axis=1, inplace=True)\n",
    "        tmp.rename(index=str, columns={'value_source_value': var}, inplace=True)\n",
    "        categories = add_categories(categories, tmp, var)\n",
    "    \n",
    "    # Create sublist of patients (batch)\n",
    "    n_iter = int(n_patients / n_patients_per_batch)\n",
    "    sublists_patients = [list_patients[i: i+n_patients_per_batch]\n",
    "                         for i in range(0, n_patients, n_patients_per_batch)]\n",
    "    n_sublists = len(sublists_patients)\n",
    "    \n",
    "    # Extracting data for each patient\n",
    "    for i, sublist_patients in enumerate(sublists_patients):\n",
    "        if verbose:\n",
    "            base_msg = 'Batch {}/{}'.format(i+1, n_sublists)\n",
    "            msg = base_msg\n",
    "            delta_t = str(int(time.time() - t0)) + ' s'\n",
    "            print('{:100s} [{:10s}]'.format(msg, delta_t), end='\\r')\n",
    "        \n",
    "        # Extract measures\n",
    "        # ----------------\n",
    "        if verbose:\n",
    "            add_msg = 'Extracting measures...'\n",
    "            msg = base_msg + ' - ' + add_msg\n",
    "            delta_t = str(int(time.time() - t0)) + ' s'\n",
    "            print('{:100s} [{:10s}]'.format(msg, delta_t), end='\\r')\n",
    "            \n",
    "        if len(sublist_patients) == 1:\n",
    "            match_person = \"m.person_id = {}\".format(sublist_patients[0])\n",
    "        else:\n",
    "            match_person = \"m.person_id in {}\".format(tuple(sublist_patients))\n",
    "            \n",
    "        query = \"\"\"\n",
    "        select\n",
    "            distinct m.person_id, m.measurement_datetime, m.measurement_concept_name, m.value_source_value, m.unit_source_value, d.death_datetime\n",
    "        from \n",
    "            measurement m \n",
    "        left join \n",
    "            death d on d.person_id = m.person_id\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 {}\n",
    "        order by measurement_datetime\n",
    "            ;\"\"\".format(match_person)\n",
    "        \n",
    "        df = pd.read_sql_query(query, conn)\n",
    "        \n",
    "        # Check if data is empty for a patient\n",
    "        check_length(df)\n",
    "\n",
    "        if verbose:\n",
    "            add_msg = 'Formatting data...'\n",
    "            msg = base_msg + ' - ' + add_msg\n",
    "            delta_t = str(int(time.time() - t0)) + ' s'\n",
    "            print('{:100s} [{:10s}]'.format(msg, delta_t), end='\\r')\n",
    "\n",
    "        df['death_datetime'] = pd.to_datetime(df['death_datetime'])\n",
    "        df['measurement_datetime'] = pd.to_datetime(df['measurement_datetime'])\n",
    "        \n",
    "        # Add target: patient's death' status, relative to the measurement datetime (target) and to the hospital stay (super_target)\n",
    "        df = df.groupby('person_id').apply(add_target)\n",
    "        df = df.groupby('person_id').apply(add_super_target)\n",
    "        \n",
    "        # Convert to timeseries matrix\n",
    "        df = df.pivot_table(index= ['measurement_datetime','target','super_target','person_id'], columns='measurement_concept_name', values='value_source_value', aggfunc='first')\n",
    "        df.reset_index(inplace=True)\n",
    "        df.columns.name = None\n",
    "        \n",
    "        # Convert types\n",
    "        # -------------\n",
    "        # Convert to numerical\n",
    "        numerical_variables = ['BP diastolic', 'BP systolic', 'Body temperature', 'Heart rate', 'Mean blood pressure', 'Glasgow coma scale', 'Oxygen concentration breathed', 'Mean pressure Respiratory system airway Calculated', 'Oxygen saturation in Arterial blood', 'Respiratory rate']\n",
    "           \n",
    "        df = add_missing_columns(df, numerical_variables)\n",
    "    \n",
    "        df = convert_frac(df, numerical_variables)\n",
    "        df = to_numeric(df, numerical_variables)\n",
    "        \n",
    "        # Convert to categorical and one-hot encode\n",
    "        categorical_variables = ['Heart rate rhythm']\n",
    "        \n",
    "        df = add_missing_columns(df, categorical_variables)\n",
    "        \n",
    "        df = to_categorical(df, categorical_variables, categories)\n",
    "        df = to_onehot(df, categorical_variables)\n",
    "        \n",
    "        # Add meta data to measures\n",
    "        # -------------------------\n",
    "        if verbose:\n",
    "            add_msg = 'Adding meta data...'\n",
    "            msg = base_msg + ' - ' + add_msg\n",
    "            delta_t = str(int(time.time() - t0)) + ' s'\n",
    "            print('{:100s} [{:10s}]'.format(msg, delta_t), end='\\r')\n",
    "            \n",
    "        df = pd.merge(df, meta, how='inner', on='person_id')\n",
    "        \n",
    "        # Add additional features\n",
    "        # -----------------------\n",
    "        if verbose:\n",
    "            add_msg = 'Adding additional features...'\n",
    "            msg = base_msg + ' - ' + add_msg\n",
    "            delta_t = str(int(time.time() - t0)) + ' s'\n",
    "            print('{:100s} [{:10s}]'.format(msg, delta_t), end='\\r')\n",
    "        \n",
    "        # - age\n",
    "        df = df.groupby('person_id').apply(add_age, round_to_dec=1)\n",
    "        # - 2h rolling average respiratory rate\n",
    "        df = df.groupby('person_id').apply(add_rolling_avg, column='Respiratory rate', window=2)\n",
    "        \n",
    "        frame.append(df)\n",
    "        \n",
    "        if verbose:\n",
    "            add_msg = 'Done'\n",
    "            msg = base_msg + ' - ' + add_msg\n",
    "            delta_t = str(int(time.time() - t0)) + ' s'\n",
    "            print('{:100s} [{:10s}]'.format(msg, delta_t), end='\\r')\n",
    "            print('')\n",
    "            \n",
    "    # Concat dataframes\n",
    "    dataset = pd.concat(frame)\n",
    "    dataset = dataset.reindex_axis(frame[0].columns, axis=1)\n",
    "    \n",
    "    return dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-05T16:35:42.217549Z",
     "start_time": "2018-05-05T16:35:42.210301Z"
    }
   },
   "source": [
    "### Example on a patient who indeed died"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-12T17:50:46.298791Z",
     "start_time": "2018-05-12T17:50:46.227938Z"
    }
   },
   "outputs": [],
   "source": [
    "query = \"\"\"SELECT * FROM death WHERE person_id = 62063368 LIMIT 10;\"\"\"\n",
    "df = pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-12T17:50:46.454764Z",
     "start_time": "2018-05-12T17:50:46.438422Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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>death_date</th>\n",
       "      <th>death_datetime</th>\n",
       "      <th>death_type_concept_id</th>\n",
       "      <th>cause_concept_id</th>\n",
       "      <th>cause_source_value</th>\n",
       "      <th>cause_source_concept_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62063368</td>\n",
       "      <td>2188-11-22</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   person_id  death_date      death_datetime  death_type_concept_id  \\\n",
       "0   62063368  2188-11-22 2188-11-22 12:00:00               38003569   \n",
       "\n",
       "  cause_concept_id cause_source_value cause_source_concept_id  \n",
       "0             None               None                    None  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:20:49.301527Z",
     "start_time": "2018-05-17T11:19:08.359738Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Batch 1/1 - Done                                                                                     [17 s      ]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jeremydesir/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:215: FutureWarning: '.reindex_axis' is deprecated and will be removed in a future version. Use '.reindex' instead.\n"
     ]
    }
   ],
   "source": [
    "dataset = create_dataset([62063368, 62063384, 62063393], verbose=True, n_patients_per_batch=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Export dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:21:05.468906Z",
     "start_time": "2018-05-17T11:21:05.372931Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dataset.to_csv('dataset_omop.csv', sep='\\t', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Export each dataframe dataset corresponding to a given patient to CSV format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:21:06.397804Z",
     "start_time": "2018-05-17T11:21:06.289325Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for patient_id in dataset.person_id.unique():\n",
    "    fname = 'dataset_omop_{}'.format(patient_id)\n",
    "    dataset.loc[dataset.person_id == patient_id].to_csv(fname, sep='\\t', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T15:25:59.872948Z",
     "start_time": "2018-05-13T15:25:59.856428Z"
    }
   },
   "source": [
    "Pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:21:10.744295Z",
     "start_time": "2018-05-17T11:21:10.739912Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pickle as pkl\n",
    "\n",
    "pkl.dump(dataset, open('dataset_omop.pkl', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-17T11:21:11.368420Z",
     "start_time": "2018-05-17T11:21:11.331126Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for patient_id in dataset.person_id.unique():\n",
    "    fname = 'dataset_omop_{}'.format(patient_id)\n",
    "    pkl.dump(dataset.loc[dataset.person_id == patient_id], open(fname, 'wb'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T15:29:06.689227Z",
     "start_time": "2018-05-13T15:29:06.670632Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('dataset_omop.csv', sep='\\t', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-05-13T15:29:07.419287Z",
     "start_time": "2018-05-13T15:29:07.408377Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pkl.load(open('dataset_omop.pkl', 'rb'))"
   ]
  }
 ],
 "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.5"
  },
  "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": false,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {
    "height": "43px",
    "left": "39px",
    "right": "1606px",
    "top": "107.133px",
    "width": "210px"
   },
   "toc_section_display": "block",
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}