omop_eda.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EDA of the MIMIC dataset\n",
    "\n",
    "In order to connect to the full database, you will need to install the [pymonetdb](https://pypi.python.org/pypi/pymonetdb) package. You can do this in your terminal with 'conda install pymonetdb' (if you have Anaconda, use this one as it is more stable) or 'pip install pymonetdb'.\n",
    "\n",
    "Last step before running the notebook: you need access credentials to this database and replace 'fleming' and the specified password by the ones you were provided with.\n",
    "\n",
    "Access are only available to people that passed the CITI “Data or Specimens Only Research” and completed the different steps to be granted an access. If you wish to request an access, please refer to this [page](https://mimic.physionet.org/gettingstarted/access/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-22T21:11:06.461972Z",
     "start_time": "2018-04-22T21:11:06.107839Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get your credentials\n",
    "import numpy as np\n",
    "omop_login = np.load('full_omop_login.npy').item()\n",
    "\n",
    "# Open a connection to OMOP\n",
    "from fleming_lib.tools import connect_to_omop\n",
    "conn = connect_to_omop(login_dict=omop_login)\n",
    "\n",
    "# Get ready for SQL query processing\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a golden rule, DO NOT retrieve entire tables unless it's absolutely necessary!! \n",
    "\n",
    "Stats can be computed more efficiently through SQL directly (the database is on a cluster that is most likely way more powerful than your local machine). Downloading of entire tables should only occur for model training and testing ideally."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Person table\n",
    "Information related to each patient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "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>gender_concept_id</th>\n",
       "      <th>year_of_birth</th>\n",
       "      <th>month_of_birth</th>\n",
       "      <th>day_of_birth</th>\n",
       "      <th>birth_datetime</th>\n",
       "      <th>race_concept_id</th>\n",
       "      <th>ethnicity_concept_id</th>\n",
       "      <th>location_id</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>care_site_id</th>\n",
       "      <th>person_source_value</th>\n",
       "      <th>gender_source_value</th>\n",
       "      <th>gender_source_concept_id</th>\n",
       "      <th>race_source_value</th>\n",
       "      <th>race_source_concept_id</th>\n",
       "      <th>ethnicity_source_value</th>\n",
       "      <th>ethnicity_source_concept_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62063589</td>\n",
       "      <td>8507</td>\n",
       "      <td>2138</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>2138-07-17</td>\n",
       "      <td>8515</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>None</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62063590</td>\n",
       "      <td>8507</td>\n",
       "      <td>2025</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>2025-04-11</td>\n",
       "      <td>8527</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>None</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62063591</td>\n",
       "      <td>8532</td>\n",
       "      <td>2143</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>2143-05-12</td>\n",
       "      <td>8527</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>F</td>\n",
       "      <td>None</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62063592</td>\n",
       "      <td>8507</td>\n",
       "      <td>2103</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2103-02-02</td>\n",
       "      <td>8515</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>None</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62063593</td>\n",
       "      <td>8532</td>\n",
       "      <td>2109</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>2109-06-21</td>\n",
       "      <td>8527</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>F</td>\n",
       "      <td>None</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62063594</td>\n",
       "      <td>8532</td>\n",
       "      <td>2121</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>2121-05-23</td>\n",
       "      <td>8527</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>F</td>\n",
       "      <td>None</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62063595</td>\n",
       "      <td>8507</td>\n",
       "      <td>2117</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>2117-11-20</td>\n",
       "      <td>8527</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>None</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62063596</td>\n",
       "      <td>8507</td>\n",
       "      <td>2108</td>\n",
       "      <td>1</td>\n",
       "      <td>26</td>\n",
       "      <td>2108-01-26</td>\n",
       "      <td>4218674</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>None</td>\n",
       "      <td>UNKNOWN/NOT SPECIFIED</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62063597</td>\n",
       "      <td>8532</td>\n",
       "      <td>2103</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>2103-06-28</td>\n",
       "      <td>38003599</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>F</td>\n",
       "      <td>None</td>\n",
       "      <td>BLACK/AFRICAN AMERICAN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62063598</td>\n",
       "      <td>8532</td>\n",
       "      <td>2128</td>\n",
       "      <td>2</td>\n",
       "      <td>22</td>\n",
       "      <td>2128-02-22</td>\n",
       "      <td>8527</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>F</td>\n",
       "      <td>None</td>\n",
       "      <td>WHITE</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  gender_concept_id  year_of_birth  month_of_birth  day_of_birth  \\\n",
       "0   62063589               8507           2138               7            17   \n",
       "1   62063590               8507           2025               4            11   \n",
       "2   62063591               8532           2143               5            12   \n",
       "3   62063592               8507           2103               2             2   \n",
       "4   62063593               8532           2109               6            21   \n",
       "5   62063594               8532           2121               5            23   \n",
       "6   62063595               8507           2117              11            20   \n",
       "7   62063596               8507           2108               1            26   \n",
       "8   62063597               8532           2103               6            28   \n",
       "9   62063598               8532           2128               2            22   \n",
       "\n",
       "  birth_datetime  race_concept_id  ethnicity_concept_id location_id  \\\n",
       "0     2138-07-17             8515                     0        None   \n",
       "1     2025-04-11             8527                     0        None   \n",
       "2     2143-05-12             8527                     0        None   \n",
       "3     2103-02-02             8515                     0        None   \n",
       "4     2109-06-21             8527                     0        None   \n",
       "5     2121-05-23             8527                     0        None   \n",
       "6     2117-11-20             8527                     0        None   \n",
       "7     2108-01-26          4218674                     0        None   \n",
       "8     2103-06-28         38003599                     0        None   \n",
       "9     2128-02-22             8527                     0        None   \n",
       "\n",
       "  provider_id care_site_id person_source_value gender_source_value  \\\n",
       "0        None         None                None                   M   \n",
       "1        None         None                None                   M   \n",
       "2        None         None                None                   F   \n",
       "3        None         None                None                   M   \n",
       "4        None         None                None                   F   \n",
       "5        None         None                None                   F   \n",
       "6        None         None                None                   M   \n",
       "7        None         None                None                   M   \n",
       "8        None         None                None                   F   \n",
       "9        None         None                None                   F   \n",
       "\n",
       "  gender_source_concept_id       race_source_value race_source_concept_id  \\\n",
       "0                     None                   ASIAN                   None   \n",
       "1                     None                   WHITE                   None   \n",
       "2                     None                   WHITE                   None   \n",
       "3                     None                   ASIAN                   None   \n",
       "4                     None                   WHITE                   None   \n",
       "5                     None                   WHITE                   None   \n",
       "6                     None                   WHITE                   None   \n",
       "7                     None   UNKNOWN/NOT SPECIFIED                   None   \n",
       "8                     None  BLACK/AFRICAN AMERICAN                   None   \n",
       "9                     None                   WHITE                   None   \n",
       "\n",
       "  ethnicity_source_value ethnicity_source_concept_id  \n",
       "0                   None                        None  \n",
       "1                   None                        None  \n",
       "2                   None                        None  \n",
       "3                   None                        None  \n",
       "4                   None                        None  \n",
       "5                   None                        None  \n",
       "6                   None                        None  \n",
       "7                   None                        None  \n",
       "8                   None                        None  \n",
       "9                   None                        None  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from person limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ethnicity can be aggregated apparently, and we would need the age of patient when they were first processed by the hospital"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ethnicity</th>\n",
       "      <th>gender</th>\n",
       "      <th>avg_age</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AMERICAN INDIAN/ALASKA NATIVE</td>\n",
       "      <td>F</td>\n",
       "      <td>33.774500</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMERICAN INDIAN/ALASKA NATIVE</td>\n",
       "      <td>M</td>\n",
       "      <td>14.740480</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGN...</td>\n",
       "      <td>F</td>\n",
       "      <td>60.501000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGN...</td>\n",
       "      <td>M</td>\n",
       "      <td>43.835000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ASIAN</td>\n",
       "      <td>F</td>\n",
       "      <td>32.566360</td>\n",
       "      <td>572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ASIAN</td>\n",
       "      <td>M</td>\n",
       "      <td>32.689960</td>\n",
       "      <td>732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ASIAN - ASIAN INDIAN</td>\n",
       "      <td>F</td>\n",
       "      <td>66.120250</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ASIAN - ASIAN INDIAN</td>\n",
       "      <td>M</td>\n",
       "      <td>57.036081</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ASIAN - CAMBODIAN</td>\n",
       "      <td>F</td>\n",
       "      <td>63.049750</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ASIAN - CAMBODIAN</td>\n",
       "      <td>M</td>\n",
       "      <td>47.545167</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ASIAN - CHINESE</td>\n",
       "      <td>F</td>\n",
       "      <td>56.832010</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ASIAN - CHINESE</td>\n",
       "      <td>M</td>\n",
       "      <td>56.453319</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ASIAN - FILIPINO</td>\n",
       "      <td>F</td>\n",
       "      <td>77.334364</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ASIAN - FILIPINO</td>\n",
       "      <td>M</td>\n",
       "      <td>48.639250</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ASIAN - JAPANESE</td>\n",
       "      <td>F</td>\n",
       "      <td>52.125000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ASIAN - JAPANESE</td>\n",
       "      <td>M</td>\n",
       "      <td>188.497500</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>ASIAN - KOREAN</td>\n",
       "      <td>F</td>\n",
       "      <td>46.891571</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ASIAN - KOREAN</td>\n",
       "      <td>M</td>\n",
       "      <td>60.970750</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>ASIAN - OTHER</td>\n",
       "      <td>F</td>\n",
       "      <td>21.613000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>ASIAN - OTHER</td>\n",
       "      <td>M</td>\n",
       "      <td>48.321800</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>ASIAN - THAI</td>\n",
       "      <td>M</td>\n",
       "      <td>56.907667</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>ASIAN - VIETNAMESE</td>\n",
       "      <td>F</td>\n",
       "      <td>70.215071</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>ASIAN - VIETNAMESE</td>\n",
       "      <td>M</td>\n",
       "      <td>49.583926</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>BLACK/AFRICAN</td>\n",
       "      <td>F</td>\n",
       "      <td>47.653059</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>BLACK/AFRICAN</td>\n",
       "      <td>M</td>\n",
       "      <td>48.932600</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>BLACK/AFRICAN AMERICAN</td>\n",
       "      <td>F</td>\n",
       "      <td>54.096100</td>\n",
       "      <td>1914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>BLACK/AFRICAN AMERICAN</td>\n",
       "      <td>M</td>\n",
       "      <td>45.179972</td>\n",
       "      <td>1671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>BLACK/CAPE VERDEAN</td>\n",
       "      <td>F</td>\n",
       "      <td>54.610195</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>BLACK/CAPE VERDEAN</td>\n",
       "      <td>M</td>\n",
       "      <td>57.905329</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>BLACK/HAITIAN</td>\n",
       "      <td>F</td>\n",
       "      <td>70.695079</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>HISPANIC/LATINO - SALVADORAN</td>\n",
       "      <td>F</td>\n",
       "      <td>47.343000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>HISPANIC/LATINO - SALVADORAN</td>\n",
       "      <td>M</td>\n",
       "      <td>44.076200</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>MIDDLE EASTERN</td>\n",
       "      <td>F</td>\n",
       "      <td>78.932556</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>MIDDLE EASTERN</td>\n",
       "      <td>M</td>\n",
       "      <td>52.249632</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>MULTI RACE ETHNICITY</td>\n",
       "      <td>F</td>\n",
       "      <td>53.843020</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>MULTI RACE ETHNICITY</td>\n",
       "      <td>M</td>\n",
       "      <td>42.371950</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>NATIVE HAWAIIAN OR OTHER PACIFIC ISLANDER</td>\n",
       "      <td>F</td>\n",
       "      <td>51.417571</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>NATIVE HAWAIIAN OR OTHER PACIFIC ISLANDER</td>\n",
       "      <td>M</td>\n",
       "      <td>38.625000</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>OTHER</td>\n",
       "      <td>F</td>\n",
       "      <td>44.207101</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>OTHER</td>\n",
       "      <td>M</td>\n",
       "      <td>44.370024</td>\n",
       "      <td>761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>PATIENT DECLINED TO ANSWER</td>\n",
       "      <td>F</td>\n",
       "      <td>50.586856</td>\n",
       "      <td>209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>PATIENT DECLINED TO ANSWER</td>\n",
       "      <td>M</td>\n",
       "      <td>51.822450</td>\n",
       "      <td>289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>PORTUGUESE</td>\n",
       "      <td>F</td>\n",
       "      <td>53.894375</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>PORTUGUESE</td>\n",
       "      <td>M</td>\n",
       "      <td>55.820150</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>SOUTH AMERICAN</td>\n",
       "      <td>F</td>\n",
       "      <td>51.348750</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>SOUTH AMERICAN</td>\n",
       "      <td>M</td>\n",
       "      <td>39.629250</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>UNABLE TO OBTAIN</td>\n",
       "      <td>F</td>\n",
       "      <td>73.469511</td>\n",
       "      <td>327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>UNABLE TO OBTAIN</td>\n",
       "      <td>M</td>\n",
       "      <td>61.472920</td>\n",
       "      <td>465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>UNKNOWN/NOT SPECIFIED</td>\n",
       "      <td>F</td>\n",
       "      <td>70.368658</td>\n",
       "      <td>1742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>UNKNOWN/NOT SPECIFIED</td>\n",
       "      <td>M</td>\n",
       "      <td>60.507435</td>\n",
       "      <td>2494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>WHITE</td>\n",
       "      <td>F</td>\n",
       "      <td>72.295292</td>\n",
       "      <td>13952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>WHITE</td>\n",
       "      <td>M</td>\n",
       "      <td>60.924719</td>\n",
       "      <td>18122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>WHITE - BRAZILIAN</td>\n",
       "      <td>F</td>\n",
       "      <td>45.002550</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>WHITE - BRAZILIAN</td>\n",
       "      <td>M</td>\n",
       "      <td>43.086455</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>WHITE - EASTERN EUROPEAN</td>\n",
       "      <td>F</td>\n",
       "      <td>62.820000</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>WHITE - EASTERN EUROPEAN</td>\n",
       "      <td>M</td>\n",
       "      <td>78.855467</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>WHITE - OTHER EUROPEAN</td>\n",
       "      <td>F</td>\n",
       "      <td>68.949161</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>WHITE - OTHER EUROPEAN</td>\n",
       "      <td>M</td>\n",
       "      <td>61.438684</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>WHITE - RUSSIAN</td>\n",
       "      <td>F</td>\n",
       "      <td>120.492000</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>WHITE - RUSSIAN</td>\n",
       "      <td>M</td>\n",
       "      <td>89.267783</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>81 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            ethnicity gender     avg_age  \\\n",
       "0                       AMERICAN INDIAN/ALASKA NATIVE      F   33.774500   \n",
       "1                       AMERICAN INDIAN/ALASKA NATIVE      M   14.740480   \n",
       "2   AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGN...      F   60.501000   \n",
       "3   AMERICAN INDIAN/ALASKA NATIVE FEDERALLY RECOGN...      M   43.835000   \n",
       "4                                               ASIAN      F   32.566360   \n",
       "5                                               ASIAN      M   32.689960   \n",
       "6                                ASIAN - ASIAN INDIAN      F   66.120250   \n",
       "7                                ASIAN - ASIAN INDIAN      M   57.036081   \n",
       "8                                   ASIAN - CAMBODIAN      F   63.049750   \n",
       "9                                   ASIAN - CAMBODIAN      M   47.545167   \n",
       "10                                    ASIAN - CHINESE      F   56.832010   \n",
       "11                                    ASIAN - CHINESE      M   56.453319   \n",
       "12                                   ASIAN - FILIPINO      F   77.334364   \n",
       "13                                   ASIAN - FILIPINO      M   48.639250   \n",
       "14                                   ASIAN - JAPANESE      F   52.125000   \n",
       "15                                   ASIAN - JAPANESE      M  188.497500   \n",
       "16                                     ASIAN - KOREAN      F   46.891571   \n",
       "17                                     ASIAN - KOREAN      M   60.970750   \n",
       "18                                      ASIAN - OTHER      F   21.613000   \n",
       "19                                      ASIAN - OTHER      M   48.321800   \n",
       "20                                       ASIAN - THAI      M   56.907667   \n",
       "21                                 ASIAN - VIETNAMESE      F   70.215071   \n",
       "22                                 ASIAN - VIETNAMESE      M   49.583926   \n",
       "23                                      BLACK/AFRICAN      F   47.653059   \n",
       "24                                      BLACK/AFRICAN      M   48.932600   \n",
       "25                             BLACK/AFRICAN AMERICAN      F   54.096100   \n",
       "26                             BLACK/AFRICAN AMERICAN      M   45.179972   \n",
       "27                                 BLACK/CAPE VERDEAN      F   54.610195   \n",
       "28                                 BLACK/CAPE VERDEAN      M   57.905329   \n",
       "29                                      BLACK/HAITIAN      F   70.695079   \n",
       "..                                                ...    ...         ...   \n",
       "51                       HISPANIC/LATINO - SALVADORAN      F   47.343000   \n",
       "52                       HISPANIC/LATINO - SALVADORAN      M   44.076200   \n",
       "53                                     MIDDLE EASTERN      F   78.932556   \n",
       "54                                     MIDDLE EASTERN      M   52.249632   \n",
       "55                               MULTI RACE ETHNICITY      F   53.843020   \n",
       "56                               MULTI RACE ETHNICITY      M   42.371950   \n",
       "57          NATIVE HAWAIIAN OR OTHER PACIFIC ISLANDER      F   51.417571   \n",
       "58          NATIVE HAWAIIAN OR OTHER PACIFIC ISLANDER      M   38.625000   \n",
       "59                                              OTHER      F   44.207101   \n",
       "60                                              OTHER      M   44.370024   \n",
       "61                         PATIENT DECLINED TO ANSWER      F   50.586856   \n",
       "62                         PATIENT DECLINED TO ANSWER      M   51.822450   \n",
       "63                                         PORTUGUESE      F   53.894375   \n",
       "64                                         PORTUGUESE      M   55.820150   \n",
       "65                                     SOUTH AMERICAN      F   51.348750   \n",
       "66                                     SOUTH AMERICAN      M   39.629250   \n",
       "67                                   UNABLE TO OBTAIN      F   73.469511   \n",
       "68                                   UNABLE TO OBTAIN      M   61.472920   \n",
       "69                              UNKNOWN/NOT SPECIFIED      F   70.368658   \n",
       "70                              UNKNOWN/NOT SPECIFIED      M   60.507435   \n",
       "71                                              WHITE      F   72.295292   \n",
       "72                                              WHITE      M   60.924719   \n",
       "73                                  WHITE - BRAZILIAN      F   45.002550   \n",
       "74                                  WHITE - BRAZILIAN      M   43.086455   \n",
       "75                           WHITE - EASTERN EUROPEAN      F   62.820000   \n",
       "76                           WHITE - EASTERN EUROPEAN      M   78.855467   \n",
       "77                             WHITE - OTHER EUROPEAN      F   68.949161   \n",
       "78                             WHITE - OTHER EUROPEAN      M   61.438684   \n",
       "79                                    WHITE - RUSSIAN      F  120.492000   \n",
       "80                                    WHITE - RUSSIAN      M   89.267783   \n",
       "\n",
       "    count  \n",
       "0      20  \n",
       "1      25  \n",
       "2       1  \n",
       "3       1  \n",
       "4     572  \n",
       "5     732  \n",
       "6      20  \n",
       "7      37  \n",
       "8       4  \n",
       "9       6  \n",
       "10    104  \n",
       "11    119  \n",
       "12     11  \n",
       "13      4  \n",
       "14      5  \n",
       "15      2  \n",
       "16      7  \n",
       "17      4  \n",
       "18      5  \n",
       "19     10  \n",
       "20      3  \n",
       "21     14  \n",
       "22     27  \n",
       "23     17  \n",
       "24     15  \n",
       "25   1914  \n",
       "26   1671  \n",
       "27     77  \n",
       "28     82  \n",
       "29     38  \n",
       "..    ...  \n",
       "51      4  \n",
       "52     10  \n",
       "53      9  \n",
       "54     19  \n",
       "55     51  \n",
       "56     60  \n",
       "57      7  \n",
       "58      8  \n",
       "59    495  \n",
       "60    761  \n",
       "61    209  \n",
       "62    289  \n",
       "63     16  \n",
       "64     20  \n",
       "65      4  \n",
       "66      4  \n",
       "67    327  \n",
       "68    465  \n",
       "69   1742  \n",
       "70   2494  \n",
       "71  13952  \n",
       "72  18122  \n",
       "73     20  \n",
       "74     22  \n",
       "75      7  \n",
       "76     15  \n",
       "77     31  \n",
       "78     38  \n",
       "79     59  \n",
       "80     46  \n",
       "\n",
       "[81 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select \n",
    "    p.race_source_value as ethnicity, p.gender_source_value as gender, \n",
    "    avg((v.first_visit_date - p.birth_datetime)/365.25) as avg_age,\n",
    "    count(0) as count \n",
    "from \n",
    "    person p\n",
    "    \n",
    "    left outer join\n",
    "\n",
    "    (select \n",
    "        person_id, min(visit_start_date) as first_visit_date\n",
    "    from\n",
    "        visit_occurrence\n",
    "    group by \n",
    "        person_id) v\n",
    "    \n",
    "    on p.person_id = v.person_id\n",
    "    \n",
    "group by\n",
    "    p.race_source_value, p.gender_source_value\n",
    "order by\n",
    "    p.race_source_value, p.gender_source_value    \n",
    "    ;\"\"\"\n",
    "#query = \"select datepart(yyyy, birth_datetime) from person limit 5;\"\n",
    "person_age_by_ethnicity_gender = pd.read_sql_query(query, conn)\n",
    "person_age_by_ethnicity_gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-21T13:50:03.053385Z",
     "start_time": "2018-04-21T13:50:02.894334Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>person_id</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62063589</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62063590</td>\n",
       "      <td>76.522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62063591</td>\n",
       "      <td>47.841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62063592</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62063593</td>\n",
       "      <td>65.938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62063594</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62063595</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62063596</td>\n",
       "      <td>41.787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62063597</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62063598</td>\n",
       "      <td>50.143</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   person_id     age\n",
       "0   62063589   0.000\n",
       "1   62063590  76.522\n",
       "2   62063591  47.841\n",
       "3   62063592   0.000\n",
       "4   62063593  65.938\n",
       "5   62063594   0.000\n",
       "6   62063595   0.000\n",
       "7   62063596  41.787\n",
       "8   62063597   0.000\n",
       "9   62063598  50.143"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select \n",
    "    p.person_id, (v.visit_start_date - p.birth_datetime)/365.25 as age\n",
    "from \n",
    "    person p\n",
    "    \n",
    "    left outer join\n",
    "    \n",
    "    visit_occurrence v\n",
    "    \n",
    "    on p.person_id = v.person_id\n",
    "    \n",
    "limit 10    \n",
    "    ;\"\"\"\n",
    "#query = \"select datepart(yyyy, birth_datetime) from person limit 5;\"\n",
    "temp = pd.read_sql_query(query, conn)\n",
    "temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Death table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>person_id</th>\n",
       "      <th>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",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62063384</td>\n",
       "      <td>2198-02-18</td>\n",
       "      <td>2198-02-18 03:55:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62063393</td>\n",
       "      <td>2182-07-31</td>\n",
       "      <td>2182-07-31 06:45:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62063403</td>\n",
       "      <td>2145-03-19</td>\n",
       "      <td>2145-03-19 07:00:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62063425</td>\n",
       "      <td>2156-08-26</td>\n",
       "      <td>2156-08-26 12:00:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62063429</td>\n",
       "      <td>2182-02-28</td>\n",
       "      <td>2182-02-28 14:50:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62063436</td>\n",
       "      <td>2147-11-11</td>\n",
       "      <td>2147-11-11 22:57:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62063437</td>\n",
       "      <td>2171-06-27</td>\n",
       "      <td>2171-06-27 17:10:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62063444</td>\n",
       "      <td>2123-07-16</td>\n",
       "      <td>2123-07-16 23:19:00</td>\n",
       "      <td>38003569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62063447</td>\n",
       "      <td>2129-12-20</td>\n",
       "      <td>2129-12-20 01:37: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",
       "1   62063384  2198-02-18 2198-02-18 03:55:00               38003569   \n",
       "2   62063393  2182-07-31 2182-07-31 06:45:00               38003569   \n",
       "3   62063403  2145-03-19 2145-03-19 07:00:00               38003569   \n",
       "4   62063425  2156-08-26 2156-08-26 12:00:00               38003569   \n",
       "5   62063429  2182-02-28 2182-02-28 14:50:00               38003569   \n",
       "6   62063436  2147-11-11 2147-11-11 22:57:00               38003569   \n",
       "7   62063437  2171-06-27 2171-06-27 17:10:00               38003569   \n",
       "8   62063444  2123-07-16 2123-07-16 23:19:00               38003569   \n",
       "9   62063447  2129-12-20 2129-12-20 01:37:00               38003569   \n",
       "\n",
       "  cause_concept_id cause_source_value cause_source_concept_id  \n",
       "0             None               None                    None  \n",
       "1             None               None                    None  \n",
       "2             None               None                    None  \n",
       "3             None               None                    None  \n",
       "4             None               None                    None  \n",
       "5             None               None                    None  \n",
       "6             None               None                    None  \n",
       "7             None               None                    None  \n",
       "8             None               None                    None  \n",
       "9             None               None                    None  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from death limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Visit_occurrence table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "hidden": 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>visit_occurrence_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>visit_concept_id</th>\n",
       "      <th>visit_start_date</th>\n",
       "      <th>visit_start_datetime</th>\n",
       "      <th>visit_end_date</th>\n",
       "      <th>visit_end_datetime</th>\n",
       "      <th>visit_type_concept_id</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>care_site_id</th>\n",
       "      <th>visit_source_value</th>\n",
       "      <th>visit_source_concept_id</th>\n",
       "      <th>admitting_concept_id</th>\n",
       "      <th>admitting_source_value</th>\n",
       "      <th>admitting_source_concept_id</th>\n",
       "      <th>discharge_to_concept_id</th>\n",
       "      <th>discharge_to_source_value</th>\n",
       "      <th>discharge_to_source_concept_id</th>\n",
       "      <th>preceding_visit_occurrence_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>213</td>\n",
       "      <td>62063589</td>\n",
       "      <td>9201</td>\n",
       "      <td>2138-07-17</td>\n",
       "      <td>2138-07-17 19:04:00</td>\n",
       "      <td>2138-07-21</td>\n",
       "      <td>2138-07-21 15:48:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>NEWBORN</td>\n",
       "      <td>2001033356</td>\n",
       "      <td>8940</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>2001031830</td>\n",
       "      <td>8536</td>\n",
       "      <td>HOME</td>\n",
       "      <td>2001036536</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>214</td>\n",
       "      <td>62063590</td>\n",
       "      <td>262</td>\n",
       "      <td>2101-10-20</td>\n",
       "      <td>2101-10-20 17:09:00</td>\n",
       "      <td>2101-10-31</td>\n",
       "      <td>2101-10-31 13:58:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>2001033355</td>\n",
       "      <td>8870</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>2001031826</td>\n",
       "      <td>8863</td>\n",
       "      <td>SNF</td>\n",
       "      <td>2001036544</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>215</td>\n",
       "      <td>62063591</td>\n",
       "      <td>262</td>\n",
       "      <td>2191-03-15</td>\n",
       "      <td>2191-03-15 13:10:00</td>\n",
       "      <td>2191-03-23</td>\n",
       "      <td>2191-03-23 18:41:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>2001033355</td>\n",
       "      <td>8870</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>2001031826</td>\n",
       "      <td>8536</td>\n",
       "      <td>HOME WITH HOME IV PROVIDR</td>\n",
       "      <td>2001036538</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>216</td>\n",
       "      <td>62063592</td>\n",
       "      <td>9201</td>\n",
       "      <td>2103-02-02</td>\n",
       "      <td>2103-02-02 04:31:00</td>\n",
       "      <td>2103-02-04</td>\n",
       "      <td>2103-02-04 12:15:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>NEWBORN</td>\n",
       "      <td>2001033356</td>\n",
       "      <td>8940</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>2001031830</td>\n",
       "      <td>8536</td>\n",
       "      <td>HOME</td>\n",
       "      <td>2001036536</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>217</td>\n",
       "      <td>62063593</td>\n",
       "      <td>9201</td>\n",
       "      <td>2175-05-30</td>\n",
       "      <td>2175-05-30 07:15:00</td>\n",
       "      <td>2175-06-15</td>\n",
       "      <td>2175-06-15 16:00:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>ELECTIVE</td>\n",
       "      <td>2001033354</td>\n",
       "      <td>8940</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>2001031830</td>\n",
       "      <td>8536</td>\n",
       "      <td>HOME HEALTH CARE</td>\n",
       "      <td>2001036537</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>218</td>\n",
       "      <td>62063594</td>\n",
       "      <td>9201</td>\n",
       "      <td>2121-05-23</td>\n",
       "      <td>2121-05-23 15:05:00</td>\n",
       "      <td>2121-05-27</td>\n",
       "      <td>2121-05-27 11:57:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>NEWBORN</td>\n",
       "      <td>2001033356</td>\n",
       "      <td>8940</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>2001031830</td>\n",
       "      <td>8536</td>\n",
       "      <td>HOME</td>\n",
       "      <td>2001036536</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>219</td>\n",
       "      <td>62063595</td>\n",
       "      <td>9201</td>\n",
       "      <td>2117-11-20</td>\n",
       "      <td>2117-11-20 10:22:00</td>\n",
       "      <td>2117-11-24</td>\n",
       "      <td>2117-11-24 14:20:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>NEWBORN</td>\n",
       "      <td>2001033356</td>\n",
       "      <td>8940</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>2001031830</td>\n",
       "      <td>8536</td>\n",
       "      <td>HOME</td>\n",
       "      <td>2001036536</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>220</td>\n",
       "      <td>62063596</td>\n",
       "      <td>262</td>\n",
       "      <td>2149-11-09</td>\n",
       "      <td>2149-11-09 11:13:00</td>\n",
       "      <td>2149-11-14</td>\n",
       "      <td>2149-11-14 10:15:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>2001033355</td>\n",
       "      <td>8870</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>2001031826</td>\n",
       "      <td>4216643</td>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>2001036535</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>221</td>\n",
       "      <td>62063597</td>\n",
       "      <td>9201</td>\n",
       "      <td>2103-06-28</td>\n",
       "      <td>2103-06-28 11:36:00</td>\n",
       "      <td>2103-07-06</td>\n",
       "      <td>2103-07-06 12:10:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>NEWBORN</td>\n",
       "      <td>2001033356</td>\n",
       "      <td>8940</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>2001031830</td>\n",
       "      <td>8863</td>\n",
       "      <td>SHORT TERM HOSPITAL</td>\n",
       "      <td>2001036543</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>222</td>\n",
       "      <td>62063598</td>\n",
       "      <td>262</td>\n",
       "      <td>2178-04-15</td>\n",
       "      <td>2178-04-15 20:46:00</td>\n",
       "      <td>2178-05-11</td>\n",
       "      <td>2178-05-11 19:00:00</td>\n",
       "      <td>44818518</td>\n",
       "      <td>None</td>\n",
       "      <td>67099830</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>2001033355</td>\n",
       "      <td>8870</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>2001031826</td>\n",
       "      <td>8536</td>\n",
       "      <td>HOME HEALTH CARE</td>\n",
       "      <td>2001036537</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   visit_occurrence_id  person_id  visit_concept_id visit_start_date  \\\n",
       "0                  213   62063589              9201       2138-07-17   \n",
       "1                  214   62063590               262       2101-10-20   \n",
       "2                  215   62063591               262       2191-03-15   \n",
       "3                  216   62063592              9201       2103-02-02   \n",
       "4                  217   62063593              9201       2175-05-30   \n",
       "5                  218   62063594              9201       2121-05-23   \n",
       "6                  219   62063595              9201       2117-11-20   \n",
       "7                  220   62063596               262       2149-11-09   \n",
       "8                  221   62063597              9201       2103-06-28   \n",
       "9                  222   62063598               262       2178-04-15   \n",
       "\n",
       "  visit_start_datetime visit_end_date  visit_end_datetime  \\\n",
       "0  2138-07-17 19:04:00     2138-07-21 2138-07-21 15:48:00   \n",
       "1  2101-10-20 17:09:00     2101-10-31 2101-10-31 13:58:00   \n",
       "2  2191-03-15 13:10:00     2191-03-23 2191-03-23 18:41:00   \n",
       "3  2103-02-02 04:31:00     2103-02-04 2103-02-04 12:15:00   \n",
       "4  2175-05-30 07:15:00     2175-06-15 2175-06-15 16:00:00   \n",
       "5  2121-05-23 15:05:00     2121-05-27 2121-05-27 11:57:00   \n",
       "6  2117-11-20 10:22:00     2117-11-24 2117-11-24 14:20:00   \n",
       "7  2149-11-09 11:13:00     2149-11-14 2149-11-14 10:15:00   \n",
       "8  2103-06-28 11:36:00     2103-07-06 2103-07-06 12:10:00   \n",
       "9  2178-04-15 20:46:00     2178-05-11 2178-05-11 19:00:00   \n",
       "\n",
       "   visit_type_concept_id provider_id  care_site_id visit_source_value  \\\n",
       "0               44818518        None      67099830            NEWBORN   \n",
       "1               44818518        None      67099830          EMERGENCY   \n",
       "2               44818518        None      67099830          EMERGENCY   \n",
       "3               44818518        None      67099830            NEWBORN   \n",
       "4               44818518        None      67099830           ELECTIVE   \n",
       "5               44818518        None      67099830            NEWBORN   \n",
       "6               44818518        None      67099830            NEWBORN   \n",
       "7               44818518        None      67099830          EMERGENCY   \n",
       "8               44818518        None      67099830            NEWBORN   \n",
       "9               44818518        None      67099830          EMERGENCY   \n",
       "\n",
       "   visit_source_concept_id  admitting_concept_id     admitting_source_value  \\\n",
       "0               2001033356                  8940  PHYS REFERRAL/NORMAL DELI   \n",
       "1               2001033355                  8870       EMERGENCY ROOM ADMIT   \n",
       "2               2001033355                  8870       EMERGENCY ROOM ADMIT   \n",
       "3               2001033356                  8940  PHYS REFERRAL/NORMAL DELI   \n",
       "4               2001033354                  8940  PHYS REFERRAL/NORMAL DELI   \n",
       "5               2001033356                  8940  PHYS REFERRAL/NORMAL DELI   \n",
       "6               2001033356                  8940  PHYS REFERRAL/NORMAL DELI   \n",
       "7               2001033355                  8870       EMERGENCY ROOM ADMIT   \n",
       "8               2001033356                  8940  PHYS REFERRAL/NORMAL DELI   \n",
       "9               2001033355                  8870       EMERGENCY ROOM ADMIT   \n",
       "\n",
       "   admitting_source_concept_id  discharge_to_concept_id  \\\n",
       "0                   2001031830                     8536   \n",
       "1                   2001031826                     8863   \n",
       "2                   2001031826                     8536   \n",
       "3                   2001031830                     8536   \n",
       "4                   2001031830                     8536   \n",
       "5                   2001031830                     8536   \n",
       "6                   2001031830                     8536   \n",
       "7                   2001031826                  4216643   \n",
       "8                   2001031830                     8863   \n",
       "9                   2001031826                     8536   \n",
       "\n",
       "   discharge_to_source_value  discharge_to_source_concept_id  \\\n",
       "0                       HOME                      2001036536   \n",
       "1                        SNF                      2001036544   \n",
       "2  HOME WITH HOME IV PROVIDR                      2001036538   \n",
       "3                       HOME                      2001036536   \n",
       "4           HOME HEALTH CARE                      2001036537   \n",
       "5                       HOME                      2001036536   \n",
       "6                       HOME                      2001036536   \n",
       "7               DEAD/EXPIRED                      2001036535   \n",
       "8        SHORT TERM HOSPITAL                      2001036543   \n",
       "9           HOME HEALTH CARE                      2001036537   \n",
       "\n",
       "  preceding_visit_occurrence_id  \n",
       "0                          None  \n",
       "1                          None  \n",
       "2                          None  \n",
       "3                          None  \n",
       "4                          None  \n",
       "5                          None  \n",
       "6                          None  \n",
       "7                          None  \n",
       "8                          None  \n",
       "9                          None  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from visit_occurrence limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Measurement table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>measurement_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>measurement_concept_id</th>\n",
       "      <th>measurement_concept_name</th>\n",
       "      <th>measurement_concept_code</th>\n",
       "      <th>measurement_concept_code_system</th>\n",
       "      <th>measurement_date</th>\n",
       "      <th>measurement_datetime</th>\n",
       "      <th>measurement_type_concept_id</th>\n",
       "      <th>measurement_type_concept_name</th>\n",
       "      <th>...</th>\n",
       "      <th>unit_concept_code_system</th>\n",
       "      <th>range_low</th>\n",
       "      <th>range_high</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>visit_occurrence_id</th>\n",
       "      <th>visit_detail_id</th>\n",
       "      <th>measurement_source_value</th>\n",
       "      <th>measurement_source_concept_id</th>\n",
       "      <th>unit_source_value</th>\n",
       "      <th>value_source_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27861030</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3023314</td>\n",
       "      <td>Hematocrit [Volume Fraction] of Blood by Autom...</td>\n",
       "      <td>4544-3</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>2117-09-15 10:20:00</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>...</td>\n",
       "      <td>UCUM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>51221</td>\n",
       "      <td>2001031483</td>\n",
       "      <td>%</td>\n",
       "      <td>32.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27851085</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3000330</td>\n",
       "      <td>Specific gravity of Urine by Test strip</td>\n",
       "      <td>5811-5</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-13</td>\n",
       "      <td>2117-09-13 08:21:00</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>51498</td>\n",
       "      <td>2001031633</td>\n",
       "      <td>None</td>\n",
       "      <td>1.006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27860999</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3003282</td>\n",
       "      <td>Leukocytes [#/volume] in Blood by Manual count</td>\n",
       "      <td>804-5</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14 01:21:00</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>...</td>\n",
       "      <td>UCUM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>51301</td>\n",
       "      <td>2001031563</td>\n",
       "      <td>K/uL</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27851103</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3014576</td>\n",
       "      <td>Chloride serum/plasma</td>\n",
       "      <td>2075-0</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14 01:21:00</td>\n",
       "      <td>2000000011</td>\n",
       "      <td>Labs - Chemistry</td>\n",
       "      <td>...</td>\n",
       "      <td>UCUM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>50902</td>\n",
       "      <td>2001031166</td>\n",
       "      <td>mEq/L</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>27831861</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3005897</td>\n",
       "      <td>Protein [Mass/volume] in Urine by Test strip</td>\n",
       "      <td>5804-0</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-12 08:41:00</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>...</td>\n",
       "      <td>UCUM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>51492</td>\n",
       "      <td>2001031627</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>27861037</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3020416</td>\n",
       "      <td>Erythrocytes [#/volume] in Blood by Automated ...</td>\n",
       "      <td>789-8</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>2117-09-15 10:20:00</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>...</td>\n",
       "      <td>UCUM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>51279</td>\n",
       "      <td>2001031541</td>\n",
       "      <td>m/uL</td>\n",
       "      <td>3.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>27822002</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3024629</td>\n",
       "      <td>Glucose urine dipstick</td>\n",
       "      <td>5792-7</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:53:00</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>...</td>\n",
       "      <td>UCUM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>51478</td>\n",
       "      <td>2001031613</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>27812669</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3024128</td>\n",
       "      <td>Total Bilirubin serum/plasma</td>\n",
       "      <td>1975-2</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:22:00</td>\n",
       "      <td>2000000011</td>\n",
       "      <td>Labs - Chemistry</td>\n",
       "      <td>...</td>\n",
       "      <td>UCUM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>50885</td>\n",
       "      <td>2001031149</td>\n",
       "      <td>mg/dL</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>27831860</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3022621</td>\n",
       "      <td>pH of Urine by Test strip</td>\n",
       "      <td>5803-2</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-12 08:41:00</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>...</td>\n",
       "      <td>UCUM</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>51491</td>\n",
       "      <td>2001031626</td>\n",
       "      <td>units</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>27861005</td>\n",
       "      <td>62105861</td>\n",
       "      <td>3000330</td>\n",
       "      <td>Specific gravity of Urine by Test strip</td>\n",
       "      <td>5811-5</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14 10:08:00</td>\n",
       "      <td>2000000009</td>\n",
       "      <td>Labs - Hemato</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>51498</td>\n",
       "      <td>2001031633</td>\n",
       "      <td>None</td>\n",
       "      <td>1.006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   measurement_id  person_id  measurement_concept_id  \\\n",
       "0        27861030   62105861                 3023314   \n",
       "1        27851085   62105861                 3000330   \n",
       "2        27860999   62105861                 3003282   \n",
       "3        27851103   62105861                 3014576   \n",
       "4        27831861   62105861                 3005897   \n",
       "5        27861037   62105861                 3020416   \n",
       "6        27822002   62105861                 3024629   \n",
       "7        27812669   62105861                 3024128   \n",
       "8        27831860   62105861                 3022621   \n",
       "9        27861005   62105861                 3000330   \n",
       "\n",
       "                            measurement_concept_name measurement_concept_code  \\\n",
       "0  Hematocrit [Volume Fraction] of Blood by Autom...                   4544-3   \n",
       "1            Specific gravity of Urine by Test strip                   5811-5   \n",
       "2     Leukocytes [#/volume] in Blood by Manual count                    804-5   \n",
       "3                              Chloride serum/plasma                   2075-0   \n",
       "4       Protein [Mass/volume] in Urine by Test strip                   5804-0   \n",
       "5  Erythrocytes [#/volume] in Blood by Automated ...                    789-8   \n",
       "6                             Glucose urine dipstick                   5792-7   \n",
       "7                       Total Bilirubin serum/plasma                   1975-2   \n",
       "8                          pH of Urine by Test strip                   5803-2   \n",
       "9            Specific gravity of Urine by Test strip                   5811-5   \n",
       "\n",
       "  measurement_concept_code_system measurement_date measurement_datetime  \\\n",
       "0                           LOINC       2117-09-15  2117-09-15 10:20:00   \n",
       "1                           LOINC       2117-09-13  2117-09-13 08:21:00   \n",
       "2                           LOINC       2117-09-14  2117-09-14 01:21:00   \n",
       "3                           LOINC       2117-09-14  2117-09-14 01:21:00   \n",
       "4                           LOINC       2117-09-12  2117-09-12 08:41:00   \n",
       "5                           LOINC       2117-09-15  2117-09-15 10:20:00   \n",
       "6                           LOINC       2117-09-11  2117-09-11 08:53:00   \n",
       "7                           LOINC       2117-09-11  2117-09-11 08:22:00   \n",
       "8                           LOINC       2117-09-12  2117-09-12 08:41:00   \n",
       "9                           LOINC       2117-09-14  2117-09-14 10:08:00   \n",
       "\n",
       "   measurement_type_concept_id measurement_type_concept_name  \\\n",
       "0                   2000000009                 Labs - Hemato   \n",
       "1                   2000000009                 Labs - Hemato   \n",
       "2                   2000000009                 Labs - Hemato   \n",
       "3                   2000000011              Labs - Chemistry   \n",
       "4                   2000000009                 Labs - Hemato   \n",
       "5                   2000000009                 Labs - Hemato   \n",
       "6                   2000000009                 Labs - Hemato   \n",
       "7                   2000000011              Labs - Chemistry   \n",
       "8                   2000000009                 Labs - Hemato   \n",
       "9                   2000000009                 Labs - Hemato   \n",
       "\n",
       "         ...          unit_concept_code_system range_low  range_high  \\\n",
       "0        ...                              UCUM      None        None   \n",
       "1        ...                              None      None        None   \n",
       "2        ...                              UCUM      None        None   \n",
       "3        ...                              UCUM      None        None   \n",
       "4        ...                              UCUM      None        None   \n",
       "5        ...                              UCUM      None        None   \n",
       "6        ...                              UCUM      None        None   \n",
       "7        ...                              UCUM      None        None   \n",
       "8        ...                              UCUM      None        None   \n",
       "9        ...                              None      None        None   \n",
       "\n",
       "   provider_id  visit_occurrence_id visit_detail_id measurement_source_value  \\\n",
       "0         None                47371            None                    51221   \n",
       "1         None                47371            None                    51498   \n",
       "2         None                47371            None                    51301   \n",
       "3         None                47371            None                    50902   \n",
       "4         None                47371            None                    51492   \n",
       "5         None                47371            None                    51279   \n",
       "6         None                47371            None                    51478   \n",
       "7         None                47371            None                    50885   \n",
       "8         None                47371            None                    51491   \n",
       "9         None                47371            None                    51498   \n",
       "\n",
       "  measurement_source_concept_id unit_source_value value_source_value  \n",
       "0                    2001031483                 %               32.2  \n",
       "1                    2001031633              None              1.006  \n",
       "2                    2001031563              K/uL               10.5  \n",
       "3                    2001031166             mEq/L                103  \n",
       "4                    2001031627             mg/dL                100  \n",
       "5                    2001031541              m/uL               3.86  \n",
       "6                    2001031613             mg/dL                300  \n",
       "7                    2001031149             mg/dL                0.5  \n",
       "8                    2001031626             units                6.5  \n",
       "9                    2001031633              None              1.006  \n",
       "\n",
       "[10 rows x 27 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select\n",
    "    *\n",
    "from\n",
    "    measurement\n",
    "limit\n",
    "    10;\"\"\"\n",
    "\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>person_id</th>\n",
       "      <th>measurement_datetime</th>\n",
       "      <th>measurement_id</th>\n",
       "      <th>value</th>\n",
       "      <th>unit</th>\n",
       "      <th>measurement_type</th>\n",
       "      <th>measurement_source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [person_id, measurement_datetime, measurement_id, value, unit, measurement_type, measurement_source]\n",
       "Index: []"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select\n",
    "    m.person_id, m.measurement_datetime, m.measurement_id, m.value_source_value as value, m.unit_source_value as unit, \n",
    "    c.concept_name as measurement_type, c1.concept_name as measurement_source\n",
    "from\n",
    "    measurement m\n",
    "    left outer join \n",
    "    (select concept_id, concept_name from concept) c\n",
    "    on m.measurement_type_concept_id = c.concept_id\n",
    "    left outer join \n",
    "    (select concept_id, concept_name from concept) c1\n",
    "    on m.measurement_source_concept_id = c1.concept_id\n",
    "where \n",
    "    unit_source_value = '%mmHg%'\n",
    "order by\n",
    "    person_id, measurement_datetime\n",
    "limit\n",
    "    10;\"\"\"\n",
    "bpm = pd.read_sql_query(query, conn)\n",
    "bpm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Procedure_occurrence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "hidden": 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>procedure_occurrence_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>procedure_concept_id</th>\n",
       "      <th>procedure_date</th>\n",
       "      <th>procedure_datetime</th>\n",
       "      <th>procedure_type_concept_id</th>\n",
       "      <th>modifier_concept_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>visit_occurrence_id</th>\n",
       "      <th>visit_detail_id</th>\n",
       "      <th>procedure_source_value</th>\n",
       "      <th>procedure_source_concept_id</th>\n",
       "      <th>qualifier_source_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>509041</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4033552</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:46:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>Critical care services</td>\n",
       "      <td>2514441</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>509039</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4033552</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:46:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>Critical care services</td>\n",
       "      <td>2514441</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>509038</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4033552</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:46:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>Critical care services</td>\n",
       "      <td>2514441</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>509037</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4203130</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:46:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>2514414</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>509043</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4313303</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:46:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>2514409</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>509036</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4027135</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:46:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>2514408</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>509042</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4313303</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:46:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>Hospital inpatient services</td>\n",
       "      <td>2514409</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>509040</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4033552</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:46:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>Critical care services</td>\n",
       "      <td>2514441</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>342915</td>\n",
       "      <td>62095228</td>\n",
       "      <td>4040551</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>2150-04-17 15:34:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47029</td>\n",
       "      <td>None</td>\n",
       "      <td>Diagnostic ultrasound</td>\n",
       "      <td>2211779</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>342913</td>\n",
       "      <td>62095228</td>\n",
       "      <td>4033552</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>2150-04-17 15:34:00</td>\n",
       "      <td>257</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47029</td>\n",
       "      <td>None</td>\n",
       "      <td>Critical care services</td>\n",
       "      <td>2514441</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   procedure_occurrence_id  person_id  procedure_concept_id procedure_date  \\\n",
       "0                   509041   62105861               4033552     2117-09-11   \n",
       "1                   509039   62105861               4033552     2117-09-11   \n",
       "2                   509038   62105861               4033552     2117-09-11   \n",
       "3                   509037   62105861               4203130     2117-09-11   \n",
       "4                   509043   62105861               4313303     2117-09-11   \n",
       "5                   509036   62105861               4027135     2117-09-11   \n",
       "6                   509042   62105861               4313303     2117-09-11   \n",
       "7                   509040   62105861               4033552     2117-09-11   \n",
       "8                   342915   62095228               4040551     2150-04-17   \n",
       "9                   342913   62095228               4033552     2150-04-17   \n",
       "\n",
       "   procedure_datetime  procedure_type_concept_id modifier_concept_id quantity  \\\n",
       "0 2117-09-11 11:46:00                        257                None     None   \n",
       "1 2117-09-11 11:46:00                        257                None     None   \n",
       "2 2117-09-11 11:46:00                        257                None     None   \n",
       "3 2117-09-11 11:46:00                        257                None     None   \n",
       "4 2117-09-11 11:46:00                        257                None     None   \n",
       "5 2117-09-11 11:46:00                        257                None     None   \n",
       "6 2117-09-11 11:46:00                        257                None     None   \n",
       "7 2117-09-11 11:46:00                        257                None     None   \n",
       "8 2150-04-17 15:34:00                        257                None     None   \n",
       "9 2150-04-17 15:34:00                        257                None     None   \n",
       "\n",
       "  provider_id  visit_occurrence_id visit_detail_id  \\\n",
       "0        None                47371            None   \n",
       "1        None                47371            None   \n",
       "2        None                47371            None   \n",
       "3        None                47371            None   \n",
       "4        None                47371            None   \n",
       "5        None                47371            None   \n",
       "6        None                47371            None   \n",
       "7        None                47371            None   \n",
       "8        None                47029            None   \n",
       "9        None                47029            None   \n",
       "\n",
       "        procedure_source_value  procedure_source_concept_id  \\\n",
       "0       Critical care services                      2514441   \n",
       "1       Critical care services                      2514441   \n",
       "2       Critical care services                      2514441   \n",
       "3  Hospital inpatient services                      2514414   \n",
       "4  Hospital inpatient services                      2514409   \n",
       "5  Hospital inpatient services                      2514408   \n",
       "6  Hospital inpatient services                      2514409   \n",
       "7       Critical care services                      2514441   \n",
       "8        Diagnostic ultrasound                      2211779   \n",
       "9       Critical care services                      2514441   \n",
       "\n",
       "  qualifier_source_value  \n",
       "0                   None  \n",
       "1                   None  \n",
       "2                   None  \n",
       "3                   None  \n",
       "4                   None  \n",
       "5                   None  \n",
       "6                   None  \n",
       "7                   None  \n",
       "8                   None  \n",
       "9                   None  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from procedure_occurrence limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Condition_occurrence table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "hidden": 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>condition_occurrence_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>condition_concept_id</th>\n",
       "      <th>condition_start_date</th>\n",
       "      <th>condition_start_datetime</th>\n",
       "      <th>condition_end_date</th>\n",
       "      <th>condition_end_datetime</th>\n",
       "      <th>condition_type_concept_id</th>\n",
       "      <th>stop_reason</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>visit_occurrence_id</th>\n",
       "      <th>visit_detail_id</th>\n",
       "      <th>condition_source_value</th>\n",
       "      <th>condition_source_concept_id</th>\n",
       "      <th>condition_status_source_value</th>\n",
       "      <th>condition_status_concept_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5622381</td>\n",
       "      <td>62105861</td>\n",
       "      <td>443597</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000194</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>5853</td>\n",
       "      <td>44827888</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5622371</td>\n",
       "      <td>62105861</td>\n",
       "      <td>439770</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000184</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>25013</td>\n",
       "      <td>44822934</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5622382</td>\n",
       "      <td>62105861</td>\n",
       "      <td>373999</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000195</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>25053</td>\n",
       "      <td>44820684</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5622374</td>\n",
       "      <td>62105861</td>\n",
       "      <td>26727</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000187</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>5780</td>\n",
       "      <td>44819811</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5622373</td>\n",
       "      <td>62105861</td>\n",
       "      <td>197320</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000186</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>5849</td>\n",
       "      <td>44826731</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5622379</td>\n",
       "      <td>62105861</td>\n",
       "      <td>200687</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000192</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>25043</td>\n",
       "      <td>44834549</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5622377</td>\n",
       "      <td>62105861</td>\n",
       "      <td>195847</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000190</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>5363</td>\n",
       "      <td>44822024</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5622383</td>\n",
       "      <td>62105861</td>\n",
       "      <td>376683</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000196</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>36201</td>\n",
       "      <td>44831148</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5622386</td>\n",
       "      <td>62105861</td>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>44818709</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>V1351</td>\n",
       "      <td>44824960</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5622385</td>\n",
       "      <td>62105861</td>\n",
       "      <td>134441</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 08:59:00</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17 16:45:00</td>\n",
       "      <td>38000198</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>7078</td>\n",
       "      <td>44831472</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   condition_occurrence_id  person_id  condition_concept_id  \\\n",
       "0                  5622381   62105861                443597   \n",
       "1                  5622371   62105861                439770   \n",
       "2                  5622382   62105861                373999   \n",
       "3                  5622374   62105861                 26727   \n",
       "4                  5622373   62105861                197320   \n",
       "5                  5622379   62105861                200687   \n",
       "6                  5622377   62105861                195847   \n",
       "7                  5622383   62105861                376683   \n",
       "8                  5622386   62105861                     0   \n",
       "9                  5622385   62105861                134441   \n",
       "\n",
       "  condition_start_date condition_start_datetime condition_end_date  \\\n",
       "0           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "1           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "2           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "3           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "4           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "5           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "6           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "7           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "8           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "9           2117-09-11      2117-09-11 08:59:00         2117-09-17   \n",
       "\n",
       "  condition_end_datetime  condition_type_concept_id stop_reason provider_id  \\\n",
       "0    2117-09-17 16:45:00                   38000194        None        None   \n",
       "1    2117-09-17 16:45:00                   38000184        None        None   \n",
       "2    2117-09-17 16:45:00                   38000195        None        None   \n",
       "3    2117-09-17 16:45:00                   38000187        None        None   \n",
       "4    2117-09-17 16:45:00                   38000186        None        None   \n",
       "5    2117-09-17 16:45:00                   38000192        None        None   \n",
       "6    2117-09-17 16:45:00                   38000190        None        None   \n",
       "7    2117-09-17 16:45:00                   38000196        None        None   \n",
       "8    2117-09-17 16:45:00                   44818709        None        None   \n",
       "9    2117-09-17 16:45:00                   38000198        None        None   \n",
       "\n",
       "   visit_occurrence_id visit_detail_id condition_source_value  \\\n",
       "0                47371            None                   5853   \n",
       "1                47371            None                  25013   \n",
       "2                47371            None                  25053   \n",
       "3                47371            None                   5780   \n",
       "4                47371            None                   5849   \n",
       "5                47371            None                  25043   \n",
       "6                47371            None                   5363   \n",
       "7                47371            None                  36201   \n",
       "8                47371            None                  V1351   \n",
       "9                47371            None                   7078   \n",
       "\n",
       "   condition_source_concept_id condition_status_source_value  \\\n",
       "0                     44827888                          None   \n",
       "1                     44822934                          None   \n",
       "2                     44820684                          None   \n",
       "3                     44819811                          None   \n",
       "4                     44826731                          None   \n",
       "5                     44834549                          None   \n",
       "6                     44822024                          None   \n",
       "7                     44831148                          None   \n",
       "8                     44824960                          None   \n",
       "9                     44831472                          None   \n",
       "\n",
       "  condition_status_concept_id  \n",
       "0                        None  \n",
       "1                        None  \n",
       "2                        None  \n",
       "3                        None  \n",
       "4                        None  \n",
       "5                        None  \n",
       "6                        None  \n",
       "7                        None  \n",
       "8                        None  \n",
       "9                        None  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from condition_occurrence limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Observation table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "hidden": 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>observation_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>observation_concept_id</th>\n",
       "      <th>observation_date</th>\n",
       "      <th>observation_datetime</th>\n",
       "      <th>observation_type_concept_id</th>\n",
       "      <th>value_as_number</th>\n",
       "      <th>value_as_string</th>\n",
       "      <th>value_as_concept_id</th>\n",
       "      <th>qualifier_concept_id</th>\n",
       "      <th>unit_concept_id</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>visit_occurrence_id</th>\n",
       "      <th>visit_detail_id</th>\n",
       "      <th>observation_source_value</th>\n",
       "      <th>observation_source_concept_id</th>\n",
       "      <th>unit_source_value</th>\n",
       "      <th>qualifier_source_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3519795</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 00:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>20 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>98703</td>\n",
       "      <td>47371</td>\n",
       "      <td>67158807</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3519818</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14 00:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>22 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>98703</td>\n",
       "      <td>47371</td>\n",
       "      <td>67031333</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3519817</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-13</td>\n",
       "      <td>2117-09-13 04:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>Impaired Skin  - Dressing Change #1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>98703</td>\n",
       "      <td>47371</td>\n",
       "      <td>67031333</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3519816</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14 00:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>22 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>98703</td>\n",
       "      <td>47371</td>\n",
       "      <td>67031333</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3519790</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 00:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>20 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>96516</td>\n",
       "      <td>47371</td>\n",
       "      <td>67158807</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3519809</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-13</td>\n",
       "      <td>2117-09-13 00:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>22 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>98703</td>\n",
       "      <td>47371</td>\n",
       "      <td>67031333</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3519799</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 00:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>20 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>98703</td>\n",
       "      <td>47371</td>\n",
       "      <td>67158807</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3519801</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-13</td>\n",
       "      <td>2117-09-13 01:40:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>22 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>98703</td>\n",
       "      <td>47371</td>\n",
       "      <td>67031333</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3519812</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14 00:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>20 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>100652</td>\n",
       "      <td>47371</td>\n",
       "      <td>67031333</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3519807</td>\n",
       "      <td>62105861</td>\n",
       "      <td>4085802</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 00:00:00</td>\n",
       "      <td>38000280</td>\n",
       "      <td>None</td>\n",
       "      <td>20 Gauge Insertion Date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>95926</td>\n",
       "      <td>47371</td>\n",
       "      <td>67158807</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   observation_id  person_id  observation_concept_id observation_date  \\\n",
       "0         3519795   62105861                 4085802       2117-09-11   \n",
       "1         3519818   62105861                 4085802       2117-09-14   \n",
       "2         3519817   62105861                 4085802       2117-09-13   \n",
       "3         3519816   62105861                 4085802       2117-09-14   \n",
       "4         3519790   62105861                 4085802       2117-09-11   \n",
       "5         3519809   62105861                 4085802       2117-09-13   \n",
       "6         3519799   62105861                 4085802       2117-09-11   \n",
       "7         3519801   62105861                 4085802       2117-09-13   \n",
       "8         3519812   62105861                 4085802       2117-09-14   \n",
       "9         3519807   62105861                 4085802       2117-09-11   \n",
       "\n",
       "  observation_datetime  observation_type_concept_id value_as_number  \\\n",
       "0  2117-09-11 00:00:00                     38000280            None   \n",
       "1  2117-09-14 00:00:00                     38000280            None   \n",
       "2  2117-09-13 04:00:00                     38000280            None   \n",
       "3  2117-09-14 00:00:00                     38000280            None   \n",
       "4  2117-09-11 00:00:00                     38000280            None   \n",
       "5  2117-09-13 00:00:00                     38000280            None   \n",
       "6  2117-09-11 00:00:00                     38000280            None   \n",
       "7  2117-09-13 01:40:00                     38000280            None   \n",
       "8  2117-09-14 00:00:00                     38000280            None   \n",
       "9  2117-09-11 00:00:00                     38000280            None   \n",
       "\n",
       "                       value_as_string value_as_concept_id  \\\n",
       "0              20 Gauge Insertion Date                None   \n",
       "1              22 Gauge Insertion Date                None   \n",
       "2  Impaired Skin  - Dressing Change #1                None   \n",
       "3              22 Gauge Insertion Date                None   \n",
       "4              20 Gauge Insertion Date                None   \n",
       "5              22 Gauge Insertion Date                None   \n",
       "6              20 Gauge Insertion Date                None   \n",
       "7              22 Gauge Insertion Date                None   \n",
       "8              20 Gauge Insertion Date                None   \n",
       "9              20 Gauge Insertion Date                None   \n",
       "\n",
       "  qualifier_concept_id unit_concept_id  provider_id  visit_occurrence_id  \\\n",
       "0                 None            None        98703                47371   \n",
       "1                 None            None        98703                47371   \n",
       "2                 None            None        98703                47371   \n",
       "3                 None            None        98703                47371   \n",
       "4                 None            None        96516                47371   \n",
       "5                 None            None        98703                47371   \n",
       "6                 None            None        98703                47371   \n",
       "7                 None            None        98703                47371   \n",
       "8                 None            None       100652                47371   \n",
       "9                 None            None        95926                47371   \n",
       "\n",
       "   visit_detail_id observation_source_value observation_source_concept_id  \\\n",
       "0         67158807                     None                          None   \n",
       "1         67031333                     None                          None   \n",
       "2         67031333                     None                          None   \n",
       "3         67031333                     None                          None   \n",
       "4         67158807                     None                          None   \n",
       "5         67031333                     None                          None   \n",
       "6         67158807                     None                          None   \n",
       "7         67031333                     None                          None   \n",
       "8         67031333                     None                          None   \n",
       "9         67158807                     None                          None   \n",
       "\n",
       "  unit_source_value qualifier_source_value  \n",
       "0              None                   None  \n",
       "1              None                   None  \n",
       "2              None                   None  \n",
       "3              None                   None  \n",
       "4              None                   None  \n",
       "5              None                   None  \n",
       "6              None                   None  \n",
       "7              None                   None  \n",
       "8              None                   None  \n",
       "9              None                   None  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from observation limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Drug_exposure table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-22T21:30:20.155034Z",
     "start_time": "2018-04-22T21:30:19.527676Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>drug_exposure_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>drug_concept_id</th>\n",
       "      <th>drug_exposure_start_date</th>\n",
       "      <th>drug_exposure_start_datetime</th>\n",
       "      <th>drug_exposure_end_date</th>\n",
       "      <th>drug_exposure_end_datetime</th>\n",
       "      <th>verbatim_end_date</th>\n",
       "      <th>drug_type_concept_id</th>\n",
       "      <th>stop_reason</th>\n",
       "      <th>...</th>\n",
       "      <th>route_concept_id</th>\n",
       "      <th>lot_number</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>visit_occurrence_id</th>\n",
       "      <th>visit_detail_id</th>\n",
       "      <th>drug_source_value</th>\n",
       "      <th>drug_source_concept_id</th>\n",
       "      <th>route_source_value</th>\n",
       "      <th>dose_unit_source_value</th>\n",
       "      <th>quantity_source_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>65134749</td>\n",
       "      <td>62105861</td>\n",
       "      <td>40168991</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>00008092355</td>\n",
       "      <td>None</td>\n",
       "      <td>IV</td>\n",
       "      <td>VIAL</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>65133109</td>\n",
       "      <td>62105861</td>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>00338067104</td>\n",
       "      <td>None</td>\n",
       "      <td>IV</td>\n",
       "      <td>BAG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>65133110</td>\n",
       "      <td>62105861</td>\n",
       "      <td>19079524</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>00338004903</td>\n",
       "      <td>None</td>\n",
       "      <td>IV</td>\n",
       "      <td>mL</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>65136366</td>\n",
       "      <td>62105861</td>\n",
       "      <td>19078557</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>00002821501</td>\n",
       "      <td>None</td>\n",
       "      <td>IV DRIP</td>\n",
       "      <td>mL</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65134747</td>\n",
       "      <td>62105861</td>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-13</td>\n",
       "      <td>2117-09-13</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>00338008504</td>\n",
       "      <td>None</td>\n",
       "      <td>IV</td>\n",
       "      <td>mL</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>65136379</td>\n",
       "      <td>62105861</td>\n",
       "      <td>40232756</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4128794</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>00406055262</td>\n",
       "      <td>None</td>\n",
       "      <td>PO/NG</td>\n",
       "      <td>TAB</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>65134755</td>\n",
       "      <td>62105861</td>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>IV</td>\n",
       "      <td>BAG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>65134753</td>\n",
       "      <td>62105861</td>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>IV</td>\n",
       "      <td>BAG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>65134751</td>\n",
       "      <td>62105861</td>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>IV</td>\n",
       "      <td>BAG</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>65134750</td>\n",
       "      <td>62105861</td>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-12</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>2117-09-14</td>\n",
       "      <td>None</td>\n",
       "      <td>38000177</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>4112421</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>47371</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>IV</td>\n",
       "      <td>VIAL</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   drug_exposure_id  person_id  drug_concept_id drug_exposure_start_date  \\\n",
       "0          65134749   62105861         40168991               2117-09-12   \n",
       "1          65133109   62105861                0               2117-09-14   \n",
       "2          65133110   62105861         19079524               2117-09-14   \n",
       "3          65136366   62105861         19078557               2117-09-11   \n",
       "4          65134747   62105861                0               2117-09-12   \n",
       "5          65136379   62105861         40232756               2117-09-11   \n",
       "6          65134755   62105861                0               2117-09-12   \n",
       "7          65134753   62105861                0               2117-09-12   \n",
       "8          65134751   62105861                0               2117-09-12   \n",
       "9          65134750   62105861                0               2117-09-12   \n",
       "\n",
       "  drug_exposure_start_datetime drug_exposure_end_date  \\\n",
       "0                   2117-09-12             2117-09-14   \n",
       "1                   2117-09-14             2117-09-15   \n",
       "2                   2117-09-14             2117-09-15   \n",
       "3                   2117-09-11             2117-09-11   \n",
       "4                   2117-09-12             2117-09-13   \n",
       "5                   2117-09-11             2117-09-17   \n",
       "6                   2117-09-12             2117-09-15   \n",
       "7                   2117-09-12             2117-09-15   \n",
       "8                   2117-09-12             2117-09-15   \n",
       "9                   2117-09-12             2117-09-14   \n",
       "\n",
       "  drug_exposure_end_datetime verbatim_end_date  drug_type_concept_id  \\\n",
       "0                 2117-09-14              None              38000177   \n",
       "1                 2117-09-15              None              38000177   \n",
       "2                 2117-09-15              None              38000177   \n",
       "3                 2117-09-11              None              38000177   \n",
       "4                 2117-09-13              None              38000177   \n",
       "5                 2117-09-17              None              38000177   \n",
       "6                 2117-09-15              None              38000177   \n",
       "7                 2117-09-15              None              38000177   \n",
       "8                 2117-09-15              None              38000177   \n",
       "9                 2117-09-14              None              38000177   \n",
       "\n",
       "  stop_reason          ...          route_concept_id  lot_number provider_id  \\\n",
       "0        None          ...                   4112421        None        None   \n",
       "1        None          ...                   4112421        None        None   \n",
       "2        None          ...                   4112421        None        None   \n",
       "3        None          ...                   4112421        None        None   \n",
       "4        None          ...                   4112421        None        None   \n",
       "5        None          ...                   4128794        None        None   \n",
       "6        None          ...                   4112421        None        None   \n",
       "7        None          ...                   4112421        None        None   \n",
       "8        None          ...                   4112421        None        None   \n",
       "9        None          ...                   4112421        None        None   \n",
       "\n",
       "  visit_occurrence_id  visit_detail_id drug_source_value  \\\n",
       "0               47371             None       00008092355   \n",
       "1               47371             None       00338067104   \n",
       "2               47371             None       00338004903   \n",
       "3               47371             None       00002821501   \n",
       "4               47371             None       00338008504   \n",
       "5               47371             None       00406055262   \n",
       "6               47371             None                 0   \n",
       "7               47371             None                 0   \n",
       "8               47371             None                 0   \n",
       "9               47371             None                 0   \n",
       "\n",
       "  drug_source_concept_id  route_source_value dose_unit_source_value  \\\n",
       "0                   None                  IV                   VIAL   \n",
       "1                   None                  IV                    BAG   \n",
       "2                   None                  IV                     mL   \n",
       "3                   None             IV DRIP                     mL   \n",
       "4                   None                  IV                     mL   \n",
       "5                   None               PO/NG                    TAB   \n",
       "6                   None                  IV                    BAG   \n",
       "7                   None                  IV                    BAG   \n",
       "8                   None                  IV                    BAG   \n",
       "9                   None                  IV                   VIAL   \n",
       "\n",
       "  quantity_source_value  \n",
       "0                     1  \n",
       "1                     1  \n",
       "2                   500  \n",
       "3                     1  \n",
       "4                  1000  \n",
       "5                     1  \n",
       "6                     1  \n",
       "7                     1  \n",
       "8                     1  \n",
       "9                     1  \n",
       "\n",
       "[10 rows x 24 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from drug_exposure limit 10;', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "hidden": 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>note_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>note_date</th>\n",
       "      <th>note_datetime</th>\n",
       "      <th>note_type_concept_id</th>\n",
       "      <th>note_class_concept_id</th>\n",
       "      <th>note_title</th>\n",
       "      <th>note_text</th>\n",
       "      <th>encoding_concept_id</th>\n",
       "      <th>language_concept_id</th>\n",
       "      <th>provider_id</th>\n",
       "      <th>visit_occurrence_id</th>\n",
       "      <th>note_source_value</th>\n",
       "      <th>visit_detail_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>55679581</td>\n",
       "      <td>62105861</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>NaT</td>\n",
       "      <td>44814637</td>\n",
       "      <td>0</td>\n",
       "      <td>Report</td>\n",
       "      <td>Admission Date:  [**2117-9-11**]              ...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47371</td>\n",
       "      <td>Discharge summary</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>56833725</td>\n",
       "      <td>62105861</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11 11:12:00</td>\n",
       "      <td>44814641</td>\n",
       "      <td>0</td>\n",
       "      <td>CHEST (PA &amp; LAT)</td>\n",
       "      <td>[**2117-9-11**] 11:12 AM\\n CHEST (PA &amp; LAT)   ...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47371</td>\n",
       "      <td>Radiology</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56200264</td>\n",
       "      <td>62095228</td>\n",
       "      <td>2150-04-19</td>\n",
       "      <td>2150-04-19 06:28:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Physician Resident Progress Note</td>\n",
       "      <td>Chief Complaint:\\n   24 Hour Events:\\n CALLED ...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>97707.0</td>\n",
       "      <td>47029</td>\n",
       "      <td>Physician</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>56200473</td>\n",
       "      <td>62095228</td>\n",
       "      <td>2150-04-18</td>\n",
       "      <td>2150-04-18 05:30:00</td>\n",
       "      <td>44814644</td>\n",
       "      <td>0</td>\n",
       "      <td>Nursing Progress Note</td>\n",
       "      <td>59 year old male with HCV-related cirrhosis, g...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>94923.0</td>\n",
       "      <td>47029</td>\n",
       "      <td>Nursing</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>56198294</td>\n",
       "      <td>62095228</td>\n",
       "      <td>2150-04-19</td>\n",
       "      <td>2150-04-19 02:35:00</td>\n",
       "      <td>44814644</td>\n",
       "      <td>0</td>\n",
       "      <td>Nursing Progress Note</td>\n",
       "      <td>59 year old male with HCV-related cirrhosis, g...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>94923.0</td>\n",
       "      <td>47029</td>\n",
       "      <td>Nursing</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>56200709</td>\n",
       "      <td>62095228</td>\n",
       "      <td>2150-04-18</td>\n",
       "      <td>2150-04-18 02:47:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Physician Resident Admission Note</td>\n",
       "      <td>Chief Complaint:  coffee ground emesis, light ...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>95486.0</td>\n",
       "      <td>47029</td>\n",
       "      <td>Physician</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>55650161</td>\n",
       "      <td>62095228</td>\n",
       "      <td>2150-04-21</td>\n",
       "      <td>NaT</td>\n",
       "      <td>44814637</td>\n",
       "      <td>0</td>\n",
       "      <td>Report</td>\n",
       "      <td>Admission Date:  [**2150-4-17**]              ...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47029</td>\n",
       "      <td>Discharge summary</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>56200262</td>\n",
       "      <td>62095228</td>\n",
       "      <td>2150-04-19</td>\n",
       "      <td>2150-04-19 06:28:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Physician Resident Progress Note</td>\n",
       "      <td>Chief Complaint:\\n   24 Hour Events:\\n CALLED ...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>97707.0</td>\n",
       "      <td>47029</td>\n",
       "      <td>Physician</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>55815916</td>\n",
       "      <td>62095228</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>NaT</td>\n",
       "      <td>44814642</td>\n",
       "      <td>0</td>\n",
       "      <td>Report</td>\n",
       "      <td>Sinus rhythm\\nProlonged QT interval is nonspec...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47029</td>\n",
       "      <td>ECG</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>56198312</td>\n",
       "      <td>62095228</td>\n",
       "      <td>2150-04-18</td>\n",
       "      <td>2150-04-18 02:47:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Physician Resident Admission Note</td>\n",
       "      <td>Chief Complaint:  coffee ground emesis, light ...</td>\n",
       "      <td>0</td>\n",
       "      <td>40639385</td>\n",
       "      <td>100299.0</td>\n",
       "      <td>47029</td>\n",
       "      <td>Physician</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    note_id  person_id   note_date       note_datetime  note_type_concept_id  \\\n",
       "0  55679581   62105861  2117-09-17                 NaT              44814637   \n",
       "1  56833725   62105861  2117-09-11 2117-09-11 11:12:00              44814641   \n",
       "2  56200264   62095228  2150-04-19 2150-04-19 06:28:00                     0   \n",
       "3  56200473   62095228  2150-04-18 2150-04-18 05:30:00              44814644   \n",
       "4  56198294   62095228  2150-04-19 2150-04-19 02:35:00              44814644   \n",
       "5  56200709   62095228  2150-04-18 2150-04-18 02:47:00                     0   \n",
       "6  55650161   62095228  2150-04-21                 NaT              44814637   \n",
       "7  56200262   62095228  2150-04-19 2150-04-19 06:28:00                     0   \n",
       "8  55815916   62095228  2150-04-17                 NaT              44814642   \n",
       "9  56198312   62095228  2150-04-18 2150-04-18 02:47:00                     0   \n",
       "\n",
       "   note_class_concept_id                         note_title  \\\n",
       "0                      0                             Report   \n",
       "1                      0                   CHEST (PA & LAT)   \n",
       "2                      0   Physician Resident Progress Note   \n",
       "3                      0              Nursing Progress Note   \n",
       "4                      0              Nursing Progress Note   \n",
       "5                      0  Physician Resident Admission Note   \n",
       "6                      0                             Report   \n",
       "7                      0   Physician Resident Progress Note   \n",
       "8                      0                             Report   \n",
       "9                      0  Physician Resident Admission Note   \n",
       "\n",
       "                                           note_text  encoding_concept_id  \\\n",
       "0  Admission Date:  [**2117-9-11**]              ...                    0   \n",
       "1  [**2117-9-11**] 11:12 AM\\n CHEST (PA & LAT)   ...                    0   \n",
       "2  Chief Complaint:\\n   24 Hour Events:\\n CALLED ...                    0   \n",
       "3  59 year old male with HCV-related cirrhosis, g...                    0   \n",
       "4  59 year old male with HCV-related cirrhosis, g...                    0   \n",
       "5  Chief Complaint:  coffee ground emesis, light ...                    0   \n",
       "6  Admission Date:  [**2150-4-17**]              ...                    0   \n",
       "7  Chief Complaint:\\n   24 Hour Events:\\n CALLED ...                    0   \n",
       "8  Sinus rhythm\\nProlonged QT interval is nonspec...                    0   \n",
       "9  Chief Complaint:  coffee ground emesis, light ...                    0   \n",
       "\n",
       "   language_concept_id  provider_id  visit_occurrence_id  note_source_value  \\\n",
       "0             40639385          NaN                47371  Discharge summary   \n",
       "1             40639385          NaN                47371          Radiology   \n",
       "2             40639385      97707.0                47029         Physician    \n",
       "3             40639385      94923.0                47029            Nursing   \n",
       "4             40639385      94923.0                47029            Nursing   \n",
       "5             40639385      95486.0                47029         Physician    \n",
       "6             40639385          NaN                47029  Discharge summary   \n",
       "7             40639385      97707.0                47029         Physician    \n",
       "8             40639385          NaN                47029                ECG   \n",
       "9             40639385     100299.0                47029         Physician    \n",
       "\n",
       "  visit_detail_id  \n",
       "0            None  \n",
       "1            None  \n",
       "2            None  \n",
       "3            None  \n",
       "4            None  \n",
       "5            None  \n",
       "6            None  \n",
       "7            None  \n",
       "8            None  \n",
       "9            None  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from note limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Note_nlp table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "hidden": 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>note_nlp_id</th>\n",
       "      <th>note_id</th>\n",
       "      <th>section_concept_id</th>\n",
       "      <th>snippet</th>\n",
       "      <th>lexical_variant</th>\n",
       "      <th>note_nlp_concept_id</th>\n",
       "      <th>note_nlp_source_concept_id</th>\n",
       "      <th>nlp_system</th>\n",
       "      <th>nlp_date</th>\n",
       "      <th>nlp_datetime</th>\n",
       "      <th>term_exists</th>\n",
       "      <th>term_temporal</th>\n",
       "      <th>term_modifiers</th>\n",
       "      <th>offset_begin</th>\n",
       "      <th>offset_end</th>\n",
       "      <th>section_source_value</th>\n",
       "      <th>section_source_concept_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>118083470</td>\n",
       "      <td>55680405</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Discharge Medications:\\n1. aspirin 325 mg Tabl...</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2936</td>\n",
       "      <td>3584</td>\n",
       "      <td>Discharge Medications:</td>\n",
       "      <td>2001042498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>118083471</td>\n",
       "      <td>55680405</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Discharge Disposition:\\nHome\\n\\n</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3584</td>\n",
       "      <td>3613</td>\n",
       "      <td>Discharge Disposition:</td>\n",
       "      <td>2001042483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>118083472</td>\n",
       "      <td>55680405</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Discharge Diagnosis:\\nAbdominal Aortic Aneurys...</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3613</td>\n",
       "      <td>3678</td>\n",
       "      <td>Discharge Diagnosis:</td>\n",
       "      <td>2001042489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>118083473</td>\n",
       "      <td>55680405</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Discharge Condition:\\n</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3678</td>\n",
       "      <td>3699</td>\n",
       "      <td>Discharge Condition:</td>\n",
       "      <td>2001042490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>118083474</td>\n",
       "      <td>55680405</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Mental Status: Clear and coherent.\\n</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3699</td>\n",
       "      <td>3734</td>\n",
       "      <td>Mental Status:</td>\n",
       "      <td>2001042516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>118083475</td>\n",
       "      <td>55680405</td>\n",
       "      <td>2005203326</td>\n",
       "      <td>None</td>\n",
       "      <td>Level of Consciousness: Alert and interactive.\\n</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3734</td>\n",
       "      <td>3781</td>\n",
       "      <td>Level of Consciousness:</td>\n",
       "      <td>2001042517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>118083476</td>\n",
       "      <td>55680405</td>\n",
       "      <td>2005203328</td>\n",
       "      <td>None</td>\n",
       "      <td>Activity Status: Ambulatory - Independent.\\n\\n\\n</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3781</td>\n",
       "      <td>3826</td>\n",
       "      <td>Activity Status:</td>\n",
       "      <td>2001042519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>118083477</td>\n",
       "      <td>55680405</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Discharge Instructions:\\nYou were admitted to ...</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3826</td>\n",
       "      <td>4195</td>\n",
       "      <td>Discharge Instructions:</td>\n",
       "      <td>2001042495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>118083478</td>\n",
       "      <td>55680405</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Medications:\\n??????\\tTake Aspirin 325mg (ente...</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4195</td>\n",
       "      <td>5283</td>\n",
       "      <td>Medications:</td>\n",
       "      <td>2001042670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>118083479</td>\n",
       "      <td>55680405</td>\n",
       "      <td>2005203559</td>\n",
       "      <td>None</td>\n",
       "      <td>What activities you can and cannot do:\\n??????...</td>\n",
       "      <td>4307844</td>\n",
       "      <td>0</td>\n",
       "      <td>UIMA Section Extractor v1.0</td>\n",
       "      <td>2018-04-14</td>\n",
       "      <td>2018-04-14 23:36:50.552</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5283</td>\n",
       "      <td>5981</td>\n",
       "      <td>What activities you can and cannot do:</td>\n",
       "      <td>2001042750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   note_nlp_id   note_id  section_concept_id snippet  \\\n",
       "0    118083470  55680405                   0    None   \n",
       "1    118083471  55680405                   0    None   \n",
       "2    118083472  55680405                   0    None   \n",
       "3    118083473  55680405                   0    None   \n",
       "4    118083474  55680405                   0    None   \n",
       "5    118083475  55680405          2005203326    None   \n",
       "6    118083476  55680405          2005203328    None   \n",
       "7    118083477  55680405                   0    None   \n",
       "8    118083478  55680405                   0    None   \n",
       "9    118083479  55680405          2005203559    None   \n",
       "\n",
       "                                     lexical_variant  note_nlp_concept_id  \\\n",
       "0  Discharge Medications:\\n1. aspirin 325 mg Tabl...              4307844   \n",
       "1                   Discharge Disposition:\\nHome\\n\\n              4307844   \n",
       "2  Discharge Diagnosis:\\nAbdominal Aortic Aneurys...              4307844   \n",
       "3                             Discharge Condition:\\n              4307844   \n",
       "4               Mental Status: Clear and coherent.\\n              4307844   \n",
       "5   Level of Consciousness: Alert and interactive.\\n              4307844   \n",
       "6   Activity Status: Ambulatory - Independent.\\n\\n\\n              4307844   \n",
       "7  Discharge Instructions:\\nYou were admitted to ...              4307844   \n",
       "8  Medications:\\n??????\\tTake Aspirin 325mg (ente...              4307844   \n",
       "9  What activities you can and cannot do:\\n??????...              4307844   \n",
       "\n",
       "   note_nlp_source_concept_id                   nlp_system    nlp_date  \\\n",
       "0                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "1                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "2                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "3                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "4                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "5                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "6                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "7                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "8                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "9                           0  UIMA Section Extractor v1.0  2018-04-14   \n",
       "\n",
       "             nlp_datetime term_exists term_temporal term_modifiers  \\\n",
       "0 2018-04-14 23:36:50.552        None          None           None   \n",
       "1 2018-04-14 23:36:50.552        None          None           None   \n",
       "2 2018-04-14 23:36:50.552        None          None           None   \n",
       "3 2018-04-14 23:36:50.552        None          None           None   \n",
       "4 2018-04-14 23:36:50.552        None          None           None   \n",
       "5 2018-04-14 23:36:50.552        None          None           None   \n",
       "6 2018-04-14 23:36:50.552        None          None           None   \n",
       "7 2018-04-14 23:36:50.552        None          None           None   \n",
       "8 2018-04-14 23:36:50.552        None          None           None   \n",
       "9 2018-04-14 23:36:50.552        None          None           None   \n",
       "\n",
       "   offset_begin  offset_end                    section_source_value  \\\n",
       "0          2936        3584                  Discharge Medications:   \n",
       "1          3584        3613                  Discharge Disposition:   \n",
       "2          3613        3678                    Discharge Diagnosis:   \n",
       "3          3678        3699                    Discharge Condition:   \n",
       "4          3699        3734                          Mental Status:   \n",
       "5          3734        3781                 Level of Consciousness:   \n",
       "6          3781        3826                        Activity Status:   \n",
       "7          3826        4195                 Discharge Instructions:   \n",
       "8          4195        5283                            Medications:   \n",
       "9          5283        5981  What activities you can and cannot do:   \n",
       "\n",
       "   section_source_concept_id  \n",
       "0                 2001042498  \n",
       "1                 2001042483  \n",
       "2                 2001042489  \n",
       "3                 2001042490  \n",
       "4                 2001042516  \n",
       "5                 2001042517  \n",
       "6                 2001042519  \n",
       "7                 2001042495  \n",
       "8                 2001042670  \n",
       "9                 2001042750  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from note_nlp limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Cohort_definition table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "hidden": 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>cohort_definition_id</th>\n",
       "      <th>cohort_definition_name</th>\n",
       "      <th>cohort_definition_description</th>\n",
       "      <th>definition_type_concept_id</th>\n",
       "      <th>cohort_definition_syntax</th>\n",
       "      <th>subject_concept_id</th>\n",
       "      <th>cohort_initiation_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>No particular cohort</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-04-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>angus severe</td>\n",
       "      <td>jerome description</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2018-04-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>angus shock</td>\n",
       "      <td>jerome description</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2018-04-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>accp severe</td>\n",
       "      <td>jerome description</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2018-04-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>accp shock</td>\n",
       "      <td>jerome description</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2018-04-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>sepsis3 severe</td>\n",
       "      <td>jerome description</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2018-04-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>sepsis3 shock</td>\n",
       "      <td>jerome description</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2018-04-14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cohort_definition_id cohort_definition_name cohort_definition_description  \\\n",
       "0                     0   No particular cohort                          None   \n",
       "1                     1           angus severe            jerome description   \n",
       "2                     2            angus shock            jerome description   \n",
       "3                     3            accp severe            jerome description   \n",
       "4                     4             accp shock            jerome description   \n",
       "5                     5         sepsis3 severe            jerome description   \n",
       "6                     6          sepsis3 shock            jerome description   \n",
       "\n",
       "   definition_type_concept_id cohort_definition_syntax  subject_concept_id  \\\n",
       "0                           0                     None                   0   \n",
       "1                           0                     None                   8   \n",
       "2                           0                     None                   8   \n",
       "3                           0                     None                   8   \n",
       "4                           0                     None                   8   \n",
       "5                           0                     None                   8   \n",
       "6                           0                     None                   8   \n",
       "\n",
       "  cohort_initiation_date  \n",
       "0             2018-04-14  \n",
       "1             2018-04-14  \n",
       "2             2018-04-14  \n",
       "3             2018-04-14  \n",
       "4             2018-04-14  \n",
       "5             2018-04-14  \n",
       "6             2018-04-14  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from cohort_definition limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Cohort_attribute table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "hidden": 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>cohort_definition_id</th>\n",
       "      <th>cohort_start_date</th>\n",
       "      <th>cohort_end_date</th>\n",
       "      <th>subject_id</th>\n",
       "      <th>attribute_definition_id</th>\n",
       "      <th>value_as_number</th>\n",
       "      <th>value_as_concept_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>67158807</td>\n",
       "      <td>2</td>\n",
       "      <td>0.117072</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-11</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>67031333</td>\n",
       "      <td>2</td>\n",
       "      <td>4.256701</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>2117-09-15</td>\n",
       "      <td>2117-09-17</td>\n",
       "      <td>67031335</td>\n",
       "      <td>2</td>\n",
       "      <td>1.950764</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>67158811</td>\n",
       "      <td>2</td>\n",
       "      <td>0.101181</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>67040892</td>\n",
       "      <td>2</td>\n",
       "      <td>0.010822</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>67040893</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000567</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>2150-04-17</td>\n",
       "      <td>2150-04-19</td>\n",
       "      <td>67040894</td>\n",
       "      <td>2</td>\n",
       "      <td>1.931088</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>2150-04-19</td>\n",
       "      <td>2150-04-21</td>\n",
       "      <td>67040895</td>\n",
       "      <td>2</td>\n",
       "      <td>2.141435</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>2108-04-06</td>\n",
       "      <td>2108-04-06</td>\n",
       "      <td>67158816</td>\n",
       "      <td>2</td>\n",
       "      <td>0.174479</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>2108-04-06</td>\n",
       "      <td>2108-04-11</td>\n",
       "      <td>66892538</td>\n",
       "      <td>2</td>\n",
       "      <td>4.977639</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cohort_definition_id cohort_start_date cohort_end_date  subject_id  \\\n",
       "0                     0        2117-09-11      2117-09-11    67158807   \n",
       "1                     0        2117-09-11      2117-09-15    67031333   \n",
       "2                     0        2117-09-15      2117-09-17    67031335   \n",
       "3                     0        2150-04-17      2150-04-17    67158811   \n",
       "4                     0        2150-04-17      2150-04-17    67040892   \n",
       "5                     0        2150-04-17      2150-04-17    67040893   \n",
       "6                     0        2150-04-17      2150-04-19    67040894   \n",
       "7                     0        2150-04-19      2150-04-21    67040895   \n",
       "8                     0        2108-04-06      2108-04-06    67158816   \n",
       "9                     0        2108-04-06      2108-04-11    66892538   \n",
       "\n",
       "   attribute_definition_id  value_as_number  value_as_concept_id  \n",
       "0                        2         0.117072                    0  \n",
       "1                        2         4.256701                    0  \n",
       "2                        2         1.950764                    0  \n",
       "3                        2         0.101181                    0  \n",
       "4                        2         0.010822                    0  \n",
       "5                        2         0.000567                    0  \n",
       "6                        2         1.931088                    0  \n",
       "7                        2         2.141435                    0  \n",
       "8                        2         0.174479                    0  \n",
       "9                        2         4.977639                    0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from cohort_attribute limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Attribute_definition table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "hidden": 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>attribute_definition_id</th>\n",
       "      <th>attribute_name</th>\n",
       "      <th>attribute_description</th>\n",
       "      <th>attribute_type_concept_id</th>\n",
       "      <th>attribute_syntax</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Callout Delay</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Visit Detail Delay</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Visit Occurrence Delay</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>duration_hours</td>\n",
       "      <td>ventdurations - duration_hours</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>sofa</td>\n",
       "      <td>sofa - sofa</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>respiration</td>\n",
       "      <td>sofa - respiration</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>coagulation</td>\n",
       "      <td>sofa - coagulation</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>liver</td>\n",
       "      <td>sofa - liver</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>cardiovascular</td>\n",
       "      <td>sofa - cardiovascular</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>cns</td>\n",
       "      <td>sofa - cns</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   attribute_definition_id          attribute_name  \\\n",
       "0                        1           Callout Delay   \n",
       "1                        2      Visit Detail Delay   \n",
       "2                        3  Visit Occurrence Delay   \n",
       "3                        4          duration_hours   \n",
       "4                        5                    sofa   \n",
       "5                        6             respiration   \n",
       "6                        7             coagulation   \n",
       "7                        8                   liver   \n",
       "8                        9          cardiovascular   \n",
       "9                       10                     cns   \n",
       "\n",
       "            attribute_description  attribute_type_concept_id attribute_syntax  \n",
       "0                            None                          0             None  \n",
       "1                            None                          0             None  \n",
       "2                            None                          0             None  \n",
       "3  ventdurations - duration_hours                          0             None  \n",
       "4                     sofa - sofa                          0             None  \n",
       "5              sofa - respiration                          0             None  \n",
       "6              sofa - coagulation                          0             None  \n",
       "7                    sofa - liver                          0             None  \n",
       "8           sofa - cardiovascular                          0             None  \n",
       "9                      sofa - cns                          0             None  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from attribute_definition limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Care_site table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "hidden": 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>care_site_id</th>\n",
       "      <th>care_site_name</th>\n",
       "      <th>place_of_service_concept_id</th>\n",
       "      <th>location_id</th>\n",
       "      <th>care_site_source_value</th>\n",
       "      <th>place_of_service_source_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>67099738</td>\n",
       "      <td>CCU ward #7</td>\n",
       "      <td>4149943</td>\n",
       "      <td>None</td>\n",
       "      <td>CCU</td>\n",
       "      <td>Coronary care unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>67099739</td>\n",
       "      <td>CCU ward #37</td>\n",
       "      <td>4149943</td>\n",
       "      <td>None</td>\n",
       "      <td>CCU</td>\n",
       "      <td>Coronary care unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>67099740</td>\n",
       "      <td>CCU ward #57</td>\n",
       "      <td>4149943</td>\n",
       "      <td>None</td>\n",
       "      <td>CCU</td>\n",
       "      <td>Coronary care unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>67099741</td>\n",
       "      <td>CSRU ward #33</td>\n",
       "      <td>4138949</td>\n",
       "      <td>None</td>\n",
       "      <td>CSRU</td>\n",
       "      <td>Cardiac surgery recovery unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>67099742</td>\n",
       "      <td>CSRU ward #12</td>\n",
       "      <td>4138949</td>\n",
       "      <td>None</td>\n",
       "      <td>CSRU</td>\n",
       "      <td>Cardiac surgery recovery unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>67099743</td>\n",
       "      <td>CSRU ward #14</td>\n",
       "      <td>4138949</td>\n",
       "      <td>None</td>\n",
       "      <td>CSRU</td>\n",
       "      <td>Cardiac surgery recovery unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>67099744</td>\n",
       "      <td>CSRU ward #23</td>\n",
       "      <td>4138949</td>\n",
       "      <td>None</td>\n",
       "      <td>CSRU</td>\n",
       "      <td>Cardiac surgery recovery unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>67099745</td>\n",
       "      <td>CSRU ward #15</td>\n",
       "      <td>4138949</td>\n",
       "      <td>None</td>\n",
       "      <td>CSRU</td>\n",
       "      <td>Cardiac surgery recovery unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>67099746</td>\n",
       "      <td>CSRU ward #47</td>\n",
       "      <td>4138949</td>\n",
       "      <td>None</td>\n",
       "      <td>CSRU</td>\n",
       "      <td>Cardiac surgery recovery unit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>67099747</td>\n",
       "      <td>MICU ward #52</td>\n",
       "      <td>40481392</td>\n",
       "      <td>None</td>\n",
       "      <td>MICU</td>\n",
       "      <td>Medical intensive care unit</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   care_site_id care_site_name  place_of_service_concept_id location_id  \\\n",
       "0      67099738    CCU ward #7                      4149943        None   \n",
       "1      67099739   CCU ward #37                      4149943        None   \n",
       "2      67099740   CCU ward #57                      4149943        None   \n",
       "3      67099741  CSRU ward #33                      4138949        None   \n",
       "4      67099742  CSRU ward #12                      4138949        None   \n",
       "5      67099743  CSRU ward #14                      4138949        None   \n",
       "6      67099744  CSRU ward #23                      4138949        None   \n",
       "7      67099745  CSRU ward #15                      4138949        None   \n",
       "8      67099746  CSRU ward #47                      4138949        None   \n",
       "9      67099747  MICU ward #52                     40481392        None   \n",
       "\n",
       "  care_site_source_value  place_of_service_source_value  \n",
       "0                    CCU             Coronary care unit  \n",
       "1                    CCU             Coronary care unit  \n",
       "2                    CCU             Coronary care unit  \n",
       "3                   CSRU  Cardiac surgery recovery unit  \n",
       "4                   CSRU  Cardiac surgery recovery unit  \n",
       "5                   CSRU  Cardiac surgery recovery unit  \n",
       "6                   CSRU  Cardiac surgery recovery unit  \n",
       "7                   CSRU  Cardiac surgery recovery unit  \n",
       "8                   CSRU  Cardiac surgery recovery unit  \n",
       "9                   MICU    Medical intensive care unit  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from care_site limit 10;', conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concept table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept_id</th>\n",
       "      <th>concept_name</th>\n",
       "      <th>domain_id</th>\n",
       "      <th>vocabulary_id</th>\n",
       "      <th>concept_class_id</th>\n",
       "      <th>standard_concept</th>\n",
       "      <th>concept_code</th>\n",
       "      <th>valid_start_date</th>\n",
       "      <th>valid_end_date</th>\n",
       "      <th>invalid_reason</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2103359</td>\n",
       "      <td>Excision of rib, partial</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>21600</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2103847</td>\n",
       "      <td>Excision, radial head</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>24130</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2104737</td>\n",
       "      <td>Tenotomy, adductor of hip, percutaneous (separ...</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>27000</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2105444</td>\n",
       "      <td>Arthrodesis, ankle, open</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>27870</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2105876</td>\n",
       "      <td>Strapping; hand or finger</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>29280</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2106077</td>\n",
       "      <td>Arthroscopy, subtalar joint, surgical; with de...</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>29906</td>\n",
       "      <td>2008-04-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2106527</td>\n",
       "      <td>Laryngoscopy, flexible; diagnostic</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>31575</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>42740408</td>\n",
       "      <td>Speculoscopy</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>None</td>\n",
       "      <td>0031T</td>\n",
       "      <td>2003-03-31</td>\n",
       "      <td>2009-04-06</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>42740457</td>\n",
       "      <td>Starch granules, feces</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>None</td>\n",
       "      <td>89225</td>\n",
       "      <td>2004-03-31</td>\n",
       "      <td>2011-05-05</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2108119</td>\n",
       "      <td>Transfusion, blood or blood components</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>36430</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   concept_id                                       concept_name  domain_id  \\\n",
       "0     2103359                           Excision of rib, partial  Procedure   \n",
       "1     2103847                              Excision, radial head  Procedure   \n",
       "2     2104737  Tenotomy, adductor of hip, percutaneous (separ...  Procedure   \n",
       "3     2105444                           Arthrodesis, ankle, open  Procedure   \n",
       "4     2105876                          Strapping; hand or finger  Procedure   \n",
       "5     2106077  Arthroscopy, subtalar joint, surgical; with de...  Procedure   \n",
       "6     2106527                 Laryngoscopy, flexible; diagnostic  Procedure   \n",
       "7    42740408                                       Speculoscopy  Procedure   \n",
       "8    42740457                             Starch granules, feces  Procedure   \n",
       "9     2108119             Transfusion, blood or blood components  Procedure   \n",
       "\n",
       "  vocabulary_id concept_class_id standard_concept concept_code  \\\n",
       "0          CPT4             CPT4                S        21600   \n",
       "1          CPT4             CPT4                S        24130   \n",
       "2          CPT4             CPT4                S        27000   \n",
       "3          CPT4             CPT4                S        27870   \n",
       "4          CPT4             CPT4                S        29280   \n",
       "5          CPT4             CPT4                S        29906   \n",
       "6          CPT4             CPT4                S        31575   \n",
       "7          CPT4             CPT4             None        0031T   \n",
       "8          CPT4             CPT4             None        89225   \n",
       "9          CPT4             CPT4                S        36430   \n",
       "\n",
       "  valid_start_date valid_end_date invalid_reason  \n",
       "0       1970-01-01     2099-12-31           None  \n",
       "1       1970-01-01     2099-12-31           None  \n",
       "2       1970-01-01     2099-12-31           None  \n",
       "3       1970-01-01     2099-12-31           None  \n",
       "4       1970-01-01     2099-12-31           None  \n",
       "5       2008-04-01     2099-12-31           None  \n",
       "6       1970-01-01     2099-12-31           None  \n",
       "7       2003-03-31     2009-04-06              D  \n",
       "8       2004-03-31     2011-05-05              D  \n",
       "9       1970-01-01     2099-12-31           None  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from concept limit 10;', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept_id</th>\n",
       "      <th>concept_name</th>\n",
       "      <th>domain_id</th>\n",
       "      <th>vocabulary_id</th>\n",
       "      <th>concept_class_id</th>\n",
       "      <th>standard_concept</th>\n",
       "      <th>concept_code</th>\n",
       "      <th>valid_start_date</th>\n",
       "      <th>valid_end_date</th>\n",
       "      <th>invalid_reason</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001019318</td>\n",
       "      <td>label:[ABG Chloride]dbsource:[carevue]linksto:...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>ABG's</td>\n",
       "      <td>None</td>\n",
       "      <td>4193</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001019976</td>\n",
       "      <td>label:[Chloride (100-112)]dbsource:[carevue]li...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>788</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001020851</td>\n",
       "      <td>label:[Calcium Chloride]dbsource:[carevue]link...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5753</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2001021134</td>\n",
       "      <td>label:[Chloride  (100-112)]dbsource:[carevue]l...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>3747</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2001021240</td>\n",
       "      <td>label:[Chloride]dbsource:[carevue]linksto:[cha...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>1523</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2001022289</td>\n",
       "      <td>label:[Potassium Chloride]dbsource:[carevue]li...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3578</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2001022320</td>\n",
       "      <td>label:[Sodium Chloride]dbsource:[carevue]links...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3624</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2001024119</td>\n",
       "      <td>label:[Sodium Chloride]dbsource:[carevue]links...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>30296</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2001024120</td>\n",
       "      <td>label:[Potassium Chloride]dbsource:[carevue]li...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>30297</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2001024123</td>\n",
       "      <td>label:[Calcium Chloride 10%]dbsource:[carevue]...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>30300</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2001024229</td>\n",
       "      <td>label:[Calcium Chloride]dbsource:[carevue]link...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>30022</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2001027125</td>\n",
       "      <td>label:[Potassium Chloride]dbsource:[metavision...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>Medications</td>\n",
       "      <td>None</td>\n",
       "      <td>225166</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2001028179</td>\n",
       "      <td>label:[Chloride (serum) (soft)]dbsource:[metav...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>228385</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2001028180</td>\n",
       "      <td>label:[Chloride (whole blood) (soft)]dbsource:...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>228386</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2001029930</td>\n",
       "      <td>label:[Chloride (serum)]dbsource:[metavision]l...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>220602</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2001030340</td>\n",
       "      <td>label:[Chloride (whole blood)]dbsource:[metavi...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_items</td>\n",
       "      <td>Labs</td>\n",
       "      <td>None</td>\n",
       "      <td>226536</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2001031069</td>\n",
       "      <td>label:[Chloride, Whole Blood]fluid:[Blood]loin...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_labitems</td>\n",
       "      <td>Blood Gas</td>\n",
       "      <td>None</td>\n",
       "      <td>50806</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2001031102</td>\n",
       "      <td>label:[Chloride, Ascites]fluid:[Ascites]loinc:...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_labitems</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>50839</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2001031166</td>\n",
       "      <td>label:[Chloride]fluid:[Blood]loinc:[2075-0]</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_labitems</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>50902</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2001031277</td>\n",
       "      <td>label:[Chloride, CSF]fluid:[Cerebrospinal Flui...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_labitems</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>51013</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2001031294</td>\n",
       "      <td>label:[Chloride, Body Fluid]fluid:[Other Body ...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_labitems</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>51030</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2001031314</td>\n",
       "      <td>label:[Chloride, Pleural]fluid:[Pleural]loinc:...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_labitems</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>51050</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2001031326</td>\n",
       "      <td>label:[Chloride, Stool]fluid:[Stool]loinc:[151...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_labitems</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>51062</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2001031342</td>\n",
       "      <td>label:[Chloride, Urine]fluid:[Urine]loinc:[207...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>MIMIC d_labitems</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>None</td>\n",
       "      <td>51078</td>\n",
       "      <td>1979-01-01</td>\n",
       "      <td>2099-01-01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>44806562</td>\n",
       "      <td>Chloride output measurement</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>SNOMED</td>\n",
       "      <td>Procedure</td>\n",
       "      <td>S</td>\n",
       "      <td>792231000000102</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>45887972</td>\n",
       "      <td>Chloride</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4 Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>1011335</td>\n",
       "      <td>2014-10-10</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2212089</td>\n",
       "      <td>Basic metabolic panel (Calcium, ionized) This ...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>80047</td>\n",
       "      <td>2008-04-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2212090</td>\n",
       "      <td>Basic metabolic panel (Calcium, total) This pa...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>80048</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2212093</td>\n",
       "      <td>Comprehensive metabolic panel This panel must ...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>80053</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2212092</td>\n",
       "      <td>Electrolyte panel This panel must include the ...</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>CPT4</td>\n",
       "      <td>S</td>\n",
       "      <td>80051</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>40796899</td>\n",
       "      <td>Chloride | Red Blood Cells</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP46467-4</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>40797329</td>\n",
       "      <td>Chloride | Peritoneal fluid</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP50363-8</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>40773309</td>\n",
       "      <td>Chloride | Vitreous fluid</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP42259-9</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>40776710</td>\n",
       "      <td>Chloride | Urine</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP42860-4</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>40774111</td>\n",
       "      <td>Chloride | XXX</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP50000-6</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>40791705</td>\n",
       "      <td>Chloride | Pleural fluid</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP70655-3</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>40797427</td>\n",
       "      <td>Vinyl Chloride | Air</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP51616-8</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>40779954</td>\n",
       "      <td>Methylene Chloride | Bld-Ser-Plas</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP41734-2</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>40786714</td>\n",
       "      <td>Vinyl Chloride | Urine</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP42792-9</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>40773737</td>\n",
       "      <td>Chloride | Cerebral spinal fluid</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP46442-7</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>40773733</td>\n",
       "      <td>Chloride | Amniotic Fluid</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP46427-8</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>40781062</td>\n",
       "      <td>Chloride | Blood arterial</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP52289-3</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>40780989</td>\n",
       "      <td>Methylene Chloride | Air</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP51600-2</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>40793137</td>\n",
       "      <td>Chloride | Water</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP42059-3</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>40785869</td>\n",
       "      <td>Chloride</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP15483-8</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>40795337</td>\n",
       "      <td>Chloride | Mother's milk</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP101154-5</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>40787101</td>\n",
       "      <td>Chloride | Saliva</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP46476-5</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>40787586</td>\n",
       "      <td>Methylene Chloride | Water</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP51401-5</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>40783494</td>\n",
       "      <td>Chloride | Stool</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP43906-4</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>40794133</td>\n",
       "      <td>Vinyl Chloride | Water</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP51441-1</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>40792024</td>\n",
       "      <td>Chloride | Gastric fluid</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP100823-6</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>40790331</td>\n",
       "      <td>Chloride | Dialysis fluid</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP46460-9</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>40777672</td>\n",
       "      <td>Chloride | Blood venous</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP52288-5</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>40777610</td>\n",
       "      <td>Chloride | Dialysis fluid peritoneal</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP51827-1</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>40788396</td>\n",
       "      <td>Chloride | Total parental nutrition</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP70038-2</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>40793591</td>\n",
       "      <td>Chloride | Body Fluid</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP46464-1</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>40793588</td>\n",
       "      <td>Chloride | Bld-Ser-Plas</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>LOINC Hierarchy</td>\n",
       "      <td>C</td>\n",
       "      <td>LP46433-6</td>\n",
       "      <td>1970-01-01</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>46235783</td>\n",
       "      <td>Chloride [Moles/volume] in Serum, Plasma or Blood</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>Lab Test</td>\n",
       "      <td>S</td>\n",
       "      <td>77138-6</td>\n",
       "      <td>2015-05-08</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>21494529</td>\n",
       "      <td>Chloride intake 24 hour Estimated</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>Clinical Observation</td>\n",
       "      <td>S</td>\n",
       "      <td>81080-4</td>\n",
       "      <td>2016-06-24</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>21494238</td>\n",
       "      <td>Chloride intake 24 hour Measured</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>LOINC</td>\n",
       "      <td>Clinical Observation</td>\n",
       "      <td>S</td>\n",
       "      <td>80983-0</td>\n",
       "      <td>2016-06-24</td>\n",
       "      <td>2099-12-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>118 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     concept_id                                       concept_name  \\\n",
       "0    2001019318  label:[ABG Chloride]dbsource:[carevue]linksto:...   \n",
       "1    2001019976  label:[Chloride (100-112)]dbsource:[carevue]li...   \n",
       "2    2001020851  label:[Calcium Chloride]dbsource:[carevue]link...   \n",
       "3    2001021134  label:[Chloride  (100-112)]dbsource:[carevue]l...   \n",
       "4    2001021240  label:[Chloride]dbsource:[carevue]linksto:[cha...   \n",
       "5    2001022289  label:[Potassium Chloride]dbsource:[carevue]li...   \n",
       "6    2001022320  label:[Sodium Chloride]dbsource:[carevue]links...   \n",
       "7    2001024119  label:[Sodium Chloride]dbsource:[carevue]links...   \n",
       "8    2001024120  label:[Potassium Chloride]dbsource:[carevue]li...   \n",
       "9    2001024123  label:[Calcium Chloride 10%]dbsource:[carevue]...   \n",
       "10   2001024229  label:[Calcium Chloride]dbsource:[carevue]link...   \n",
       "11   2001027125  label:[Potassium Chloride]dbsource:[metavision...   \n",
       "12   2001028179  label:[Chloride (serum) (soft)]dbsource:[metav...   \n",
       "13   2001028180  label:[Chloride (whole blood) (soft)]dbsource:...   \n",
       "14   2001029930  label:[Chloride (serum)]dbsource:[metavision]l...   \n",
       "15   2001030340  label:[Chloride (whole blood)]dbsource:[metavi...   \n",
       "16   2001031069  label:[Chloride, Whole Blood]fluid:[Blood]loin...   \n",
       "17   2001031102  label:[Chloride, Ascites]fluid:[Ascites]loinc:...   \n",
       "18   2001031166        label:[Chloride]fluid:[Blood]loinc:[2075-0]   \n",
       "19   2001031277  label:[Chloride, CSF]fluid:[Cerebrospinal Flui...   \n",
       "20   2001031294  label:[Chloride, Body Fluid]fluid:[Other Body ...   \n",
       "21   2001031314  label:[Chloride, Pleural]fluid:[Pleural]loinc:...   \n",
       "22   2001031326  label:[Chloride, Stool]fluid:[Stool]loinc:[151...   \n",
       "23   2001031342  label:[Chloride, Urine]fluid:[Urine]loinc:[207...   \n",
       "24     44806562                        Chloride output measurement   \n",
       "25     45887972                                           Chloride   \n",
       "26      2212089  Basic metabolic panel (Calcium, ionized) This ...   \n",
       "27      2212090  Basic metabolic panel (Calcium, total) This pa...   \n",
       "28      2212093  Comprehensive metabolic panel This panel must ...   \n",
       "29      2212092  Electrolyte panel This panel must include the ...   \n",
       "..          ...                                                ...   \n",
       "88     40796899                         Chloride | Red Blood Cells   \n",
       "89     40797329                        Chloride | Peritoneal fluid   \n",
       "90     40773309                          Chloride | Vitreous fluid   \n",
       "91     40776710                                   Chloride | Urine   \n",
       "92     40774111                                     Chloride | XXX   \n",
       "93     40791705                           Chloride | Pleural fluid   \n",
       "94     40797427                               Vinyl Chloride | Air   \n",
       "95     40779954                  Methylene Chloride | Bld-Ser-Plas   \n",
       "96     40786714                             Vinyl Chloride | Urine   \n",
       "97     40773737                   Chloride | Cerebral spinal fluid   \n",
       "98     40773733                          Chloride | Amniotic Fluid   \n",
       "99     40781062                          Chloride | Blood arterial   \n",
       "100    40780989                           Methylene Chloride | Air   \n",
       "101    40793137                                   Chloride | Water   \n",
       "102    40785869                                           Chloride   \n",
       "103    40795337                           Chloride | Mother's milk   \n",
       "104    40787101                                  Chloride | Saliva   \n",
       "105    40787586                         Methylene Chloride | Water   \n",
       "106    40783494                                   Chloride | Stool   \n",
       "107    40794133                             Vinyl Chloride | Water   \n",
       "108    40792024                           Chloride | Gastric fluid   \n",
       "109    40790331                          Chloride | Dialysis fluid   \n",
       "110    40777672                            Chloride | Blood venous   \n",
       "111    40777610               Chloride | Dialysis fluid peritoneal   \n",
       "112    40788396                Chloride | Total parental nutrition   \n",
       "113    40793591                              Chloride | Body Fluid   \n",
       "114    40793588                            Chloride | Bld-Ser-Plas   \n",
       "115    46235783  Chloride [Moles/volume] in Serum, Plasma or Blood   \n",
       "116    21494529                  Chloride intake 24 hour Estimated   \n",
       "117    21494238                   Chloride intake 24 hour Measured   \n",
       "\n",
       "       domain_id     vocabulary_id      concept_class_id standard_concept  \\\n",
       "0    Measurement     MIMIC d_items                 ABG's             None   \n",
       "1    Measurement     MIMIC d_items             Chemistry             None   \n",
       "2    Measurement     MIMIC d_items                  None             None   \n",
       "3    Measurement     MIMIC d_items             Chemistry             None   \n",
       "4    Measurement     MIMIC d_items             Chemistry             None   \n",
       "5    Measurement     MIMIC d_items                  None             None   \n",
       "6    Measurement     MIMIC d_items                  None             None   \n",
       "7    Measurement     MIMIC d_items                  None             None   \n",
       "8    Measurement     MIMIC d_items                  None             None   \n",
       "9    Measurement     MIMIC d_items                  None             None   \n",
       "10   Measurement     MIMIC d_items                  None             None   \n",
       "11   Measurement     MIMIC d_items           Medications             None   \n",
       "12   Measurement     MIMIC d_items                  Labs             None   \n",
       "13   Measurement     MIMIC d_items                  Labs             None   \n",
       "14   Measurement     MIMIC d_items                  Labs             None   \n",
       "15   Measurement     MIMIC d_items                  Labs             None   \n",
       "16   Measurement  MIMIC d_labitems             Blood Gas             None   \n",
       "17   Measurement  MIMIC d_labitems             Chemistry             None   \n",
       "18   Measurement  MIMIC d_labitems             Chemistry             None   \n",
       "19   Measurement  MIMIC d_labitems             Chemistry             None   \n",
       "20   Measurement  MIMIC d_labitems             Chemistry             None   \n",
       "21   Measurement  MIMIC d_labitems             Chemistry             None   \n",
       "22   Measurement  MIMIC d_labitems             Chemistry             None   \n",
       "23   Measurement  MIMIC d_labitems             Chemistry             None   \n",
       "24   Measurement            SNOMED             Procedure                S   \n",
       "25   Measurement              CPT4        CPT4 Hierarchy                C   \n",
       "26   Measurement              CPT4                  CPT4                S   \n",
       "27   Measurement              CPT4                  CPT4                S   \n",
       "28   Measurement              CPT4                  CPT4                S   \n",
       "29   Measurement              CPT4                  CPT4                S   \n",
       "..           ...               ...                   ...              ...   \n",
       "88   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "89   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "90   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "91   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "92   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "93   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "94   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "95   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "96   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "97   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "98   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "99   Measurement             LOINC       LOINC Hierarchy                C   \n",
       "100  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "101  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "102  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "103  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "104  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "105  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "106  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "107  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "108  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "109  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "110  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "111  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "112  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "113  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "114  Measurement             LOINC       LOINC Hierarchy                C   \n",
       "115  Measurement             LOINC              Lab Test                S   \n",
       "116  Measurement             LOINC  Clinical Observation                S   \n",
       "117  Measurement             LOINC  Clinical Observation                S   \n",
       "\n",
       "        concept_code valid_start_date valid_end_date invalid_reason  \n",
       "0               4193       1979-01-01     2099-01-01           None  \n",
       "1                788       1979-01-01     2099-01-01           None  \n",
       "2               5753       1979-01-01     2099-01-01           None  \n",
       "3               3747       1979-01-01     2099-01-01           None  \n",
       "4               1523       1979-01-01     2099-01-01           None  \n",
       "5               3578       1979-01-01     2099-01-01           None  \n",
       "6               3624       1979-01-01     2099-01-01           None  \n",
       "7              30296       1979-01-01     2099-01-01           None  \n",
       "8              30297       1979-01-01     2099-01-01           None  \n",
       "9              30300       1979-01-01     2099-01-01           None  \n",
       "10             30022       1979-01-01     2099-01-01           None  \n",
       "11            225166       1979-01-01     2099-01-01           None  \n",
       "12            228385       1979-01-01     2099-01-01           None  \n",
       "13            228386       1979-01-01     2099-01-01           None  \n",
       "14            220602       1979-01-01     2099-01-01           None  \n",
       "15            226536       1979-01-01     2099-01-01           None  \n",
       "16             50806       1979-01-01     2099-01-01           None  \n",
       "17             50839       1979-01-01     2099-01-01           None  \n",
       "18             50902       1979-01-01     2099-01-01           None  \n",
       "19             51013       1979-01-01     2099-01-01           None  \n",
       "20             51030       1979-01-01     2099-01-01           None  \n",
       "21             51050       1979-01-01     2099-01-01           None  \n",
       "22             51062       1979-01-01     2099-01-01           None  \n",
       "23             51078       1979-01-01     2099-01-01           None  \n",
       "24   792231000000102       2014-04-01     2099-12-31           None  \n",
       "25           1011335       2014-10-10     2099-12-31           None  \n",
       "26             80047       2008-04-01     2099-12-31           None  \n",
       "27             80048       1970-01-01     2099-12-31           None  \n",
       "28             80053       1970-01-01     2099-12-31           None  \n",
       "29             80051       1970-01-01     2099-12-31           None  \n",
       "..               ...              ...            ...            ...  \n",
       "88         LP46467-4       1970-01-01     2099-12-31           None  \n",
       "89         LP50363-8       1970-01-01     2099-12-31           None  \n",
       "90         LP42259-9       1970-01-01     2099-12-31           None  \n",
       "91         LP42860-4       1970-01-01     2099-12-31           None  \n",
       "92         LP50000-6       1970-01-01     2099-12-31           None  \n",
       "93         LP70655-3       1970-01-01     2099-12-31           None  \n",
       "94         LP51616-8       1970-01-01     2099-12-31           None  \n",
       "95         LP41734-2       1970-01-01     2099-12-31           None  \n",
       "96         LP42792-9       1970-01-01     2099-12-31           None  \n",
       "97         LP46442-7       1970-01-01     2099-12-31           None  \n",
       "98         LP46427-8       1970-01-01     2099-12-31           None  \n",
       "99         LP52289-3       1970-01-01     2099-12-31           None  \n",
       "100        LP51600-2       1970-01-01     2099-12-31           None  \n",
       "101        LP42059-3       1970-01-01     2099-12-31           None  \n",
       "102        LP15483-8       1970-01-01     2099-12-31           None  \n",
       "103       LP101154-5       1970-01-01     2099-12-31           None  \n",
       "104        LP46476-5       1970-01-01     2099-12-31           None  \n",
       "105        LP51401-5       1970-01-01     2099-12-31           None  \n",
       "106        LP43906-4       1970-01-01     2099-12-31           None  \n",
       "107        LP51441-1       1970-01-01     2099-12-31           None  \n",
       "108       LP100823-6       1970-01-01     2099-12-31           None  \n",
       "109        LP46460-9       1970-01-01     2099-12-31           None  \n",
       "110        LP52288-5       1970-01-01     2099-12-31           None  \n",
       "111        LP51827-1       1970-01-01     2099-12-31           None  \n",
       "112        LP70038-2       1970-01-01     2099-12-31           None  \n",
       "113        LP46464-1       1970-01-01     2099-12-31           None  \n",
       "114        LP46433-6       1970-01-01     2099-12-31           None  \n",
       "115          77138-6       2015-05-08     2099-12-31           None  \n",
       "116          81080-4       2016-06-24     2099-12-31           None  \n",
       "117          80983-0       2016-06-24     2099-12-31           None  \n",
       "\n",
       "[118 rows x 10 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select \n",
    "    *\n",
    "from\n",
    "    concept\n",
    "where\n",
    "    concept_name LIKE '%Chloride%'\n",
    "and \n",
    "    domain_id = 'Measurement'\n",
    "    ;\"\"\"\n",
    "\n",
    "search = pd.read_sql_query(query, conn)\n",
    "search"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Filter your data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Limitations\n",
    "Starting with the limitations, we need to make sure our dataset only integrates people that do not originally depend on medical critical assistance equipment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-22T19:53:27.683208Z",
     "start_time": "2018-04-22T19:53:27.601456Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept_id</th>\n",
       "      <th>concept_code</th>\n",
       "      <th>domain_id</th>\n",
       "      <th>concept_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001018843</td>\n",
       "      <td>128</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>label:[Code Status]dbsource:[carevue]linksto:[...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001030812</td>\n",
       "      <td>223758</td>\n",
       "      <td>Measurement</td>\n",
       "      <td>label:[Code Status]dbsource:[metavision]linkst...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   concept_id concept_code    domain_id  \\\n",
       "0  2001018843          128  Measurement   \n",
       "1  2001030812       223758  Measurement   \n",
       "\n",
       "                                        concept_name  \n",
       "0  label:[Code Status]dbsource:[carevue]linksto:[...  \n",
       "1  label:[Code Status]dbsource:[metavision]linkst...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select \n",
    "    concept_id, concept_code, domain_id, concept_name\n",
    "from\n",
    "    concept\n",
    "where\n",
    "    concept_id in (2001018843, 2001030812)\n",
    "    ;\"\"\"\n",
    "\n",
    "limitations = pd.read_sql_query(query, conn)\n",
    "limitations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-22T20:00:52.007082Z",
     "start_time": "2018-04-22T20:00:51.717415Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>L3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32697</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      L3\n",
       "0  32697"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select \n",
    "    count(distinct person_id)\n",
    "from\n",
    "    measurement\n",
    "where\n",
    "    measurement_source_concept_id in (2001018843, 2001030812)\n",
    "    ;\"\"\"\n",
    "\n",
    "temp = pd.read_sql_query(query, conn)\n",
    "temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Age and ICU\n",
    "Moving to the next filters:\n",
    "- Age >= 15\n",
    "- Processed only by intensive care unit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-04-22T21:26:24.011051Z",
     "start_time": "2018-04-22T21:26:23.939940Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>L7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32392</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      L7\n",
       "0  32392"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "select\n",
    "    count(distinct p.person_id)\n",
    "from \n",
    "    person p\n",
    "    \n",
    "    inner join\n",
    "\n",
    "    (select \n",
    "        person_id, min(visit_start_date) as first_visit_date\n",
    "    from\n",
    "        visit_occurrence\n",
    "    where\n",
    "        visit_source_value = 'EMERGENCY'\n",
    "    group by \n",
    "        person_id) v\n",
    "    \n",
    "    on p.person_id = v.person_id\n",
    "    \n",
    "where\n",
    "    (v.first_visit_date - p.birth_datetime) >= 15 * 365.25\n",
    "    ;\"\"\"\n",
    "\n",
    "temp = pd.read_sql_query(query, conn)\n",
    "temp"
   ]
  }
 ],
 "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.1"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}