jupyter-samples/readmission-integratedml-jdbc.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Train an IntegratedML model on Readmission Dataset\n",
    "## Use JDBC to connect to InterSystems IRIS database\n",
    "This Notebook demonstrates:\n",
    "- Using the JayDeBeApi Python library to connect to InterSystems IRIS\n",
    "- Creating views to segment data into training and test sets\n",
    "- Defining and training an IntegratedML model to predict marketing campaign responses\n",
    "- Comparing the resulting model's predictions to data in the test set (that the model was not trained on)\n",
    "- Using the IntegratedML \"VALIDATE MODEL\" command to calculate accuracy metrics on the test set data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/bin/sh: 1: conda: not found\n",
      "Requirement already satisfied: jupyterthemes in /usr/local/lib/python3.6/dist-packages (0.20.0)\n",
      "Requirement already satisfied: ipython>=5.4.1 in /usr/local/lib/python3.6/dist-packages (from jupyterthemes) (7.14.0)\n",
      "Requirement already satisfied: lesscpy>=0.11.2 in /usr/local/lib/python3.6/dist-packages (from jupyterthemes) (0.15.0)\n",
      "Requirement already satisfied: jupyter-core in /usr/local/lib/python3.6/dist-packages (from jupyterthemes) (4.6.3)\n",
      "Requirement already satisfied: notebook>=5.6.0 in /usr/local/lib/python3.6/dist-packages (from jupyterthemes) (6.0.3)\n",
      "Requirement already satisfied: matplotlib>=1.4.3 in /usr/local/lib/python3.6/dist-packages (from jupyterthemes) (3.2.1)\n",
      "Requirement already satisfied: jedi>=0.10 in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (0.17.0)\n",
      "Requirement already satisfied: decorator in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (4.4.2)\n",
      "Requirement already satisfied: setuptools>=18.5 in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (46.1.3)\n",
      "Requirement already satisfied: backcall in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (0.1.0)\n",
      "Requirement already satisfied: pexpect in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (4.8.0)\n",
      "Requirement already satisfied: pygments in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (2.6.1)\n",
      "Requirement already satisfied: traitlets>=4.2 in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (4.3.3)\n",
      "Requirement already satisfied: pickleshare in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (0.7.5)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /usr/local/lib/python3.6/dist-packages (from ipython>=5.4.1->jupyterthemes) (3.0.5)\n",
      "Requirement already satisfied: ply in /usr/local/lib/python3.6/dist-packages (from lesscpy>=0.11.2->jupyterthemes) (3.11)\n",
      "Requirement already satisfied: six in /usr/local/lib/python3.6/dist-packages (from lesscpy>=0.11.2->jupyterthemes) (1.14.0)\n",
      "Requirement already satisfied: python-dateutil>=2.1 in /usr/local/lib/python3.6/dist-packages (from matplotlib>=1.4.3->jupyterthemes) (2.8.1)\n",
      "Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.6/dist-packages (from matplotlib>=1.4.3->jupyterthemes) (1.2.0)\n",
      "Requirement already satisfied: numpy>=1.11 in /usr/local/lib/python3.6/dist-packages (from matplotlib>=1.4.3->jupyterthemes) (1.18.4)\n",
      "Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.6/dist-packages (from matplotlib>=1.4.3->jupyterthemes) (0.10.0)\n",
      "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /usr/local/lib/python3.6/dist-packages (from matplotlib>=1.4.3->jupyterthemes) (2.4.7)\n",
      "Requirement already satisfied: nbconvert in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (5.6.1)\n",
      "Requirement already satisfied: nbformat in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (4.4.0)\n",
      "Requirement already satisfied: Send2Trash in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (1.5.0)\n",
      "Requirement already satisfied: tornado>=5.0 in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (6.0.4)\n",
      "Requirement already satisfied: jupyter-client>=5.3.4 in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (6.1.3)\n",
      "Requirement already satisfied: ipython-genutils in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (0.2.0)\n",
      "Requirement already satisfied: ipykernel in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (5.1.1)\n",
      "Requirement already satisfied: pyzmq>=17 in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (19.0.1)\n",
      "Requirement already satisfied: jinja2 in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (2.11.2)\n",
      "Requirement already satisfied: terminado>=0.8.1 in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (0.8.3)\n",
      "Requirement already satisfied: prometheus-client in /usr/local/lib/python3.6/dist-packages (from notebook>=5.6.0->jupyterthemes) (0.7.1)\n",
      "Requirement already satisfied: parso>=0.7.0 in /usr/local/lib/python3.6/dist-packages (from jedi>=0.10->ipython>=5.4.1->jupyterthemes) (0.7.0)\n",
      "Requirement already satisfied: wcwidth in /usr/local/lib/python3.6/dist-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=5.4.1->jupyterthemes) (0.1.9)\n",
      "Requirement already satisfied: ptyprocess in /usr/local/lib/python3.6/dist-packages (from terminado>=0.8.1->notebook>=5.6.0->jupyterthemes) (0.6.0)\n",
      "Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.6/dist-packages (from jinja2->notebook>=5.6.0->jupyterthemes) (1.1.1)\n",
      "Requirement already satisfied: bleach in /usr/local/lib/python3.6/dist-packages (from nbconvert->notebook>=5.6.0->jupyterthemes) (3.1.5)\n",
      "Requirement already satisfied: mistune<2,>=0.8.1 in /usr/local/lib/python3.6/dist-packages (from nbconvert->notebook>=5.6.0->jupyterthemes) (0.8.4)\n",
      "Requirement already satisfied: testpath in /usr/local/lib/python3.6/dist-packages (from nbconvert->notebook>=5.6.0->jupyterthemes) (0.4.4)\n",
      "Requirement already satisfied: pandocfilters>=1.4.1 in /usr/local/lib/python3.6/dist-packages (from nbconvert->notebook>=5.6.0->jupyterthemes) (1.4.2)\n",
      "Requirement already satisfied: entrypoints>=0.2.2 in /usr/local/lib/python3.6/dist-packages (from nbconvert->notebook>=5.6.0->jupyterthemes) (0.3)\n",
      "Requirement already satisfied: defusedxml in /usr/local/lib/python3.6/dist-packages (from nbconvert->notebook>=5.6.0->jupyterthemes) (0.6.0)\n",
      "Requirement already satisfied: jsonschema!=2.5.0,>=2.4 in /usr/local/lib/python3.6/dist-packages (from nbformat->notebook>=5.6.0->jupyterthemes) (3.2.0)\n",
      "Requirement already satisfied: importlib-metadata in /usr/local/lib/python3.6/dist-packages (from jsonschema!=2.5.0,>=2.4->nbformat->notebook>=5.6.0->jupyterthemes) (1.6.0)\n",
      "Requirement already satisfied: pyrsistent>=0.14.0 in /usr/local/lib/python3.6/dist-packages (from jsonschema!=2.5.0,>=2.4->nbformat->notebook>=5.6.0->jupyterthemes) (0.16.0)\n",
      "Requirement already satisfied: attrs>=17.4.0 in /usr/local/lib/python3.6/dist-packages (from jsonschema!=2.5.0,>=2.4->nbformat->notebook>=5.6.0->jupyterthemes) (19.3.0)\n",
      "Requirement already satisfied: packaging in /usr/local/lib/python3.6/dist-packages (from bleach->nbconvert->notebook>=5.6.0->jupyterthemes) (20.3)\n",
      "Requirement already satisfied: webencodings in /usr/local/lib/python3.6/dist-packages (from bleach->nbconvert->notebook>=5.6.0->jupyterthemes) (0.5.1)\n",
      "Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.6/dist-packages (from importlib-metadata->jsonschema!=2.5.0,>=2.4->nbformat->notebook>=5.6.0->jupyterthemes) (3.1.0)\n",
      "\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!conda activate\n",
    "!pip3 install jupyterthemes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Reset css and font defaults in:\r\n",
      "/root/.jupyter/custom &\r\n",
      "/root/.local/share/jupyter/nbextensions\r\n"
     ]
    }
   ],
   "source": [
    "!jt -r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "!ls /tf/intersystems-jdbc-3.1.0.jar\n",
    "RUN apt-get update && \\\n",
    "    DEBIAN_FRONTEND=noninteractive \\\n",
    "    apt-get -y install default-jre-headless && \\\n",
    "    apt-get clean && \\\n",
    "    rm -rf /var/lib/apt/lists/*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: JayDeBeApi in /usr/local/lib/python3.6/dist-packages (1.2.3)\n",
      "Requirement already satisfied: JPype1 in /usr/local/lib/python3.6/dist-packages (from JayDeBeApi) (1.3.0)\n",
      "Requirement already satisfied: typing-extensions in /usr/local/lib/python3.6/dist-packages (from JPype1->JayDeBeApi) (4.1.1)\n",
      "\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip install JayDeBeApi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Package                Version\r\n",
      "---------------------- -----------\r\n",
      "absl-py                0.9.0\r\n",
      "asn1crypto             0.24.0\r\n",
      "astunparse             1.6.3\r\n",
      "attrs                  19.3.0\r\n",
      "backcall               0.1.0\r\n",
      "bleach                 3.1.5\r\n",
      "cachetools             4.1.0\r\n",
      "certifi                2020.4.5.1\r\n",
      "chardet                3.0.4\r\n",
      "cryptography           2.1.4\r\n",
      "cycler                 0.10.0\r\n",
      "decorator              4.4.2\r\n",
      "defusedxml             0.6.0\r\n",
      "entrypoints            0.3\r\n",
      "gast                   0.3.3\r\n",
      "google-auth            1.14.2\r\n",
      "google-auth-oauthlib   0.4.1\r\n",
      "google-pasta           0.2.0\r\n",
      "grpcio                 1.28.1\r\n",
      "h5py                   2.10.0\r\n",
      "idna                   2.6\r\n",
      "importlib-metadata     1.6.0\r\n",
      "ipykernel              5.1.1\r\n",
      "ipython                7.14.0\r\n",
      "ipython-genutils       0.2.0\r\n",
      "ipywidgets             7.5.1\r\n",
      "JayDeBeApi             1.2.3\r\n",
      "jedi                   0.17.0\r\n",
      "Jinja2                 2.11.2\r\n",
      "joblib                 1.1.0\r\n",
      "JPype1                 1.3.0\r\n",
      "jsonschema             3.2.0\r\n",
      "jupyter                1.0.0\r\n",
      "jupyter-client         6.1.3\r\n",
      "jupyter-console        6.1.0\r\n",
      "jupyter-core           4.6.3\r\n",
      "jupyter-http-over-ws   0.0.8\r\n",
      "jupyterthemes          0.20.0\r\n",
      "Keras-Preprocessing    1.1.0\r\n",
      "keyring                10.6.0\r\n",
      "keyrings.alt           3.0\r\n",
      "kiwisolver             1.2.0\r\n",
      "lesscpy                0.15.0\r\n",
      "Markdown               3.2.1\r\n",
      "MarkupSafe             1.1.1\r\n",
      "matplotlib             3.2.1\r\n",
      "mistune                0.8.4\r\n",
      "nbconvert              5.6.1\r\n",
      "nbformat               4.4.0\r\n",
      "notebook               6.0.3\r\n",
      "numpy                  1.18.4\r\n",
      "oauthlib               3.1.0\r\n",
      "opt-einsum             3.2.1\r\n",
      "packaging              20.3\r\n",
      "pandas                 1.1.5\r\n",
      "pandocfilters          1.4.2\r\n",
      "parso                  0.7.0\r\n",
      "pexpect                4.8.0\r\n",
      "pickleshare            0.7.5\r\n",
      "pip                    21.3.1\r\n",
      "ply                    3.11\r\n",
      "prometheus-client      0.7.1\r\n",
      "prompt-toolkit         3.0.5\r\n",
      "protobuf               3.11.3\r\n",
      "ptyprocess             0.6.0\r\n",
      "pyasn1                 0.4.8\r\n",
      "pyasn1-modules         0.2.8\r\n",
      "pycrypto               2.6.1\r\n",
      "Pygments               2.6.1\r\n",
      "PyGObject              3.26.1\r\n",
      "pyodbc                 4.0.32\r\n",
      "pyparsing              2.4.7\r\n",
      "pyrsistent             0.16.0\r\n",
      "python-dateutil        2.8.1\r\n",
      "pytz                   2022.1\r\n",
      "pyxdg                  0.25\r\n",
      "pyzmq                  19.0.1\r\n",
      "qtconsole              4.7.3\r\n",
      "QtPy                   1.9.0\r\n",
      "requests               2.23.0\r\n",
      "requests-oauthlib      1.3.0\r\n",
      "rsa                    4.0\r\n",
      "scikit-learn           0.24.2\r\n",
      "scipy                  1.4.1\r\n",
      "SecretStorage          2.3.1\r\n",
      "Send2Trash             1.5.0\r\n",
      "setuptools             46.1.3\r\n",
      "six                    1.14.0\r\n",
      "tensorboard            2.2.1\r\n",
      "tensorboard-plugin-wit 1.6.0.post3\r\n",
      "tensorflow             2.2.0\r\n",
      "tensorflow-estimator   2.2.0\r\n",
      "termcolor              1.1.0\r\n",
      "terminado              0.8.3\r\n",
      "testpath               0.4.4\r\n",
      "threadpoolctl          3.1.0\r\n",
      "tornado                6.0.4\r\n",
      "traitlets              4.3.3\r\n",
      "typing_extensions      4.1.1\r\n",
      "urllib3                1.25.9\r\n",
      "wcwidth                0.1.9\r\n",
      "webencodings           0.5.1\r\n",
      "Werkzeug               1.0.1\r\n",
      "wheel                  0.30.0\r\n",
      "widgetsnbextension     3.5.1\r\n",
      "wrapt                  1.12.1\r\n",
      "zipp                   3.1.0\r\n"
     ]
    }
   ],
   "source": [
    "!pip list JPype1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Set environment variables, if necessary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import os\n",
    "#os.environ['JAVA_HOME']='C:\\Progra~1\\Java\\jdk1.8.0_241'\n",
    "#os.environ['CLASSPATH'] = 'C:\\interSystems\\IRIS20194\\dev\\java\\lib\\JDK18\\intersystems-jdbc-3.0.0.jar'\n",
    "#os.environ['HADOOP_HOME']='C:\\hadoop\\bin'  #winutil binary must be in Hadoop's Home"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Get jdbc connection and cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import jaydebeapi\n",
    "url = \"jdbc:IRIS://irisimlsvr:1972/USER\" #\"jdbc:IRIS://172.17.0.1:8091/USER\"\n",
    "driver = 'com.intersystems.jdbc.IRISDriver'\n",
    "user = \"SUPERUSER\"\n",
    "password = \"SYS\"\n",
    "#libx = \"C:/InterSystems/IRIS20194/dev/java/lib/JDK18\"\n",
    "#jarfile = \"C:/InterSystems/IRIS20194/dev/java/lib/JDK18/intersystems-jdbc-3.0.0.jar\"\n",
    "jarfile = \"./intersystems-jdbc-3.1.0.jar\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = jaydebeapi.connect(driver, url, [user, password], jarfile)\n",
    "curs = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. specify the source data table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataTable = 'Patient.Readmission'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Execute a query and display results in Pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "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>AdmitReason</th>\n",
       "      <th>Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN</th>\n",
       "      <th>Com_CEREBROVASCULAR_DISEASE</th>\n",
       "      <th>Com_CHRONIC_PULMONARY_DISEASE</th>\n",
       "      <th>Com_DEMENTIA</th>\n",
       "      <th>Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION</th>\n",
       "      <th>Com_HEMIPLEGIA_OR_PARAPLEGIA</th>\n",
       "      <th>Com_MILD_LIVER_DISEASE</th>\n",
       "      <th>Com_MYOCARDIAL_INFARCTION</th>\n",
       "      <th>Com_PERIPHERY_VASCULAR_DISEASE</th>\n",
       "      <th>...</th>\n",
       "      <th>MxNumAdmitsSixMonth</th>\n",
       "      <th>MxNumAdmitsThreeMonth</th>\n",
       "      <th>MxNumAdmitsTwelveMonth</th>\n",
       "      <th>MxNumDiagOneMonth</th>\n",
       "      <th>MxNumDiagTwelveMonth</th>\n",
       "      <th>MxSmoker</th>\n",
       "      <th>MxStartDateDayOfMonth</th>\n",
       "      <th>MxStartDateDayOfWeek</th>\n",
       "      <th>MxStartDateMonth</th>\n",
       "      <th>MxWillReAdmit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            AdmitReason  \\\n",
       "0           SNOMED Code   \n",
       "1           SNOMED Code   \n",
       "2           SNOMED Code   \n",
       "3           SNOMED Code   \n",
       "4           SNOMED Code   \n",
       "5   Encounter Inpatient   \n",
       "6           SNOMED Code   \n",
       "7           SNOMED Code   \n",
       "8   Encounter Inpatient   \n",
       "9   Encounter Inpatient   \n",
       "10  Encounter Inpatient   \n",
       "11  Encounter Inpatient   \n",
       "12  Encounter Inpatient   \n",
       "13  Encounter Inpatient   \n",
       "14  Encounter Inpatient   \n",
       "15  Encounter Inpatient   \n",
       "16  Encounter Inpatient   \n",
       "17  Encounter Inpatient   \n",
       "18  Encounter Inpatient   \n",
       "19  Encounter Inpatient   \n",
       "\n",
       "    Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN  \\\n",
       "0                                                   0                                      \n",
       "1                                                   0                                      \n",
       "2                                                   0                                      \n",
       "3                                                   0                                      \n",
       "4                                                   0                                      \n",
       "5                                                   0                                      \n",
       "6                                                   0                                      \n",
       "7                                                   0                                      \n",
       "8                                                   0                                      \n",
       "9                                                   0                                      \n",
       "10                                                  0                                      \n",
       "11                                                  0                                      \n",
       "12                                                  0                                      \n",
       "13                                                  0                                      \n",
       "14                                                  0                                      \n",
       "15                                                  0                                      \n",
       "16                                                  0                                      \n",
       "17                                                  0                                      \n",
       "18                                                  0                                      \n",
       "19                                                  0                                      \n",
       "\n",
       "    Com_CEREBROVASCULAR_DISEASE  Com_CHRONIC_PULMONARY_DISEASE  Com_DEMENTIA  \\\n",
       "0                             0                              0             0   \n",
       "1                             0                              0             0   \n",
       "2                             0                              0             0   \n",
       "3                             0                              0             0   \n",
       "4                             0                              0             0   \n",
       "5                             0                              0             0   \n",
       "6                             0                              0             0   \n",
       "7                             0                              0             0   \n",
       "8                             0                              0             0   \n",
       "9                             0                              0             0   \n",
       "10                            1                              0             0   \n",
       "11                            1                              0             0   \n",
       "12                            1                              0             0   \n",
       "13                            1                              0             0   \n",
       "14                            1                              0             0   \n",
       "15                            1                              0             0   \n",
       "16                            1                              0             0   \n",
       "17                            1                              0             0   \n",
       "18                            1                              0             0   \n",
       "19                            1                              0             0   \n",
       "\n",
       "    Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION  Com_HEMIPLEGIA_OR_PARAPLEGIA  \\\n",
       "0                                           0                             0   \n",
       "1                                           1                             0   \n",
       "2                                           0                             0   \n",
       "3                                           0                             0   \n",
       "4                                           0                             0   \n",
       "5                                           0                             0   \n",
       "6                                           0                             0   \n",
       "7                                           0                             0   \n",
       "8                                           0                             0   \n",
       "9                                           0                             0   \n",
       "10                                          0                             0   \n",
       "11                                          0                             0   \n",
       "12                                          0                             0   \n",
       "13                                          0                             0   \n",
       "14                                          0                             0   \n",
       "15                                          0                             0   \n",
       "16                                          0                             0   \n",
       "17                                          0                             0   \n",
       "18                                          0                             0   \n",
       "19                                          0                             0   \n",
       "\n",
       "    Com_MILD_LIVER_DISEASE  Com_MYOCARDIAL_INFARCTION  \\\n",
       "0                        0                          0   \n",
       "1                        0                          0   \n",
       "2                        0                          0   \n",
       "3                        0                          0   \n",
       "4                        0                          0   \n",
       "5                        0                          0   \n",
       "6                        0                          0   \n",
       "7                        0                          0   \n",
       "8                        0                          0   \n",
       "9                        0                          0   \n",
       "10                       0                          0   \n",
       "11                       0                          0   \n",
       "12                       0                          0   \n",
       "13                       0                          0   \n",
       "14                       0                          0   \n",
       "15                       0                          0   \n",
       "16                       0                          0   \n",
       "17                       0                          0   \n",
       "18                       0                          0   \n",
       "19                       0                          0   \n",
       "\n",
       "    Com_PERIPHERY_VASCULAR_DISEASE  ...  MxNumAdmitsSixMonth  \\\n",
       "0                                0  ...                    0   \n",
       "1                                0  ...                    0   \n",
       "2                                0  ...                    0   \n",
       "3                                1  ...                    0   \n",
       "4                                1  ...                    1   \n",
       "5                                0  ...                    0   \n",
       "6                                0  ...                    0   \n",
       "7                                0  ...                    1   \n",
       "8                                0  ...                    0   \n",
       "9                                0  ...                    0   \n",
       "10                               0  ...                    1   \n",
       "11                               0  ...                    0   \n",
       "12                               0  ...                    1   \n",
       "13                               0  ...                    2   \n",
       "14                               0  ...                    1   \n",
       "15                               0  ...                    1   \n",
       "16                               0  ...                    2   \n",
       "17                               0  ...                    2   \n",
       "18                               0  ...                    2   \n",
       "19                               0  ...                    2   \n",
       "\n",
       "    MxNumAdmitsThreeMonth  MxNumAdmitsTwelveMonth  MxNumDiagOneMonth  \\\n",
       "0                       0                       0                  1   \n",
       "1                       0                       0                  1   \n",
       "2                       0                       0                  1   \n",
       "3                       0                       0                  0   \n",
       "4                       1                       1                  1   \n",
       "5                       0                       0                  1   \n",
       "6                       0                       0                  0   \n",
       "7                       1                       1                  1   \n",
       "8                       0                       0                  1   \n",
       "9                       0                       1                  1   \n",
       "10                      1                       2                  1   \n",
       "11                      0                       0                  0   \n",
       "12                      1                       1                  0   \n",
       "13                      1                       2                  1   \n",
       "14                      0                       3                  0   \n",
       "15                      0                       4                  0   \n",
       "16                      1                       4                  0   \n",
       "17                      0                       3                  0   \n",
       "18                      1                       4                  0   \n",
       "19                      1                       5                  1   \n",
       "\n",
       "    MxNumDiagTwelveMonth  MxSmoker  MxStartDateDayOfMonth  \\\n",
       "0                      1         0                     10   \n",
       "1                      1         0                     10   \n",
       "2                      2         0                      1   \n",
       "3                      1         0                     31   \n",
       "4                      2         0                      4   \n",
       "5                      1         0                     12   \n",
       "6                      0         0                      5   \n",
       "7                      1         0                     12   \n",
       "8                      1         0                     11   \n",
       "9                      2         0                     15   \n",
       "10                     3         0                     12   \n",
       "11                     0         0                      4   \n",
       "12                     0         0                     11   \n",
       "13                     1         0                      7   \n",
       "14                     1         0                     14   \n",
       "15                     1         0                     27   \n",
       "16                     1         0                      3   \n",
       "17                     0         0                     22   \n",
       "18                     0         0                     28   \n",
       "19                     2         0                      6   \n",
       "\n",
       "    MxStartDateDayOfWeek  MxStartDateMonth MxWillReAdmit  \n",
       "0                      7                 7             0  \n",
       "1                      2                12             0  \n",
       "2                      3                10             0  \n",
       "3                      5                 8             1  \n",
       "4                      2                 9             0  \n",
       "5                      5                 8             0  \n",
       "6                      6                 8             1  \n",
       "7                      6                 8             0  \n",
       "8                      3                 3             0  \n",
       "9                      2                 9             0  \n",
       "10                     6                12             0  \n",
       "11                     6                 6             0  \n",
       "12                     1                 7             0  \n",
       "13                     5                10             0  \n",
       "14                     2                 2             0  \n",
       "15                     6                 5             0  \n",
       "16                     1                 7             0  \n",
       "17                     7                10             0  \n",
       "18                     2                11             0  \n",
       "19                     2                 2             0  \n",
       "\n",
       "[20 rows x 45 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from IPython.display import display\n",
    "\n",
    "df = pd.read_sql(\"select TOP 20 * from %s\" % dataTable, conn)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "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>Aggregate_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14047</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Aggregate_1\n",
       "0        14047"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Show number rows\n",
    "df1 = pd.read_sql(\"SELECT COUNT(*) FROM %s\" % dataTable, conn)\n",
    "display(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning before retrying"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "curs.execute(\"DROP VIEW Patient.ReadmissionTraining\")\n",
    "curs.execute(\"DROP VIEW Patient.ReadmissionPredict\")\n",
    "curs.execute(\"DROP MODEL ReadmissionModel\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Make some views to split training and testing datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "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>Aggregate_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3048</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Aggregate_1\n",
       "0         3048"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df1 = pd.read_sql(\"SELECT COUNT(*) FROM %s WHERE  ID>=11000\" % dataTable, conn)\n",
    "display(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Training set view\n",
    "curs.execute(\"CREATE VIEW Patient.ReadmissionTraining AS SELECT * FROM Patient.Readmission WHERE ID<11000\")\n",
    "# Prediction set\n",
    "curs.execute(\"CREATE VIEW Patient.ReadmissionPredict AS SELECT * FROM Patient.Readmission WHERE ID>=11000\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Look at Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "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>AdmitReason</th>\n",
       "      <th>Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN</th>\n",
       "      <th>Com_CEREBROVASCULAR_DISEASE</th>\n",
       "      <th>Com_CHRONIC_PULMONARY_DISEASE</th>\n",
       "      <th>Com_DEMENTIA</th>\n",
       "      <th>Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION</th>\n",
       "      <th>Com_HEMIPLEGIA_OR_PARAPLEGIA</th>\n",
       "      <th>Com_MILD_LIVER_DISEASE</th>\n",
       "      <th>Com_MYOCARDIAL_INFARCTION</th>\n",
       "      <th>Com_PERIPHERY_VASCULAR_DISEASE</th>\n",
       "      <th>...</th>\n",
       "      <th>MxNumAdmitsSixMonth</th>\n",
       "      <th>MxNumAdmitsThreeMonth</th>\n",
       "      <th>MxNumAdmitsTwelveMonth</th>\n",
       "      <th>MxNumDiagOneMonth</th>\n",
       "      <th>MxNumDiagTwelveMonth</th>\n",
       "      <th>MxSmoker</th>\n",
       "      <th>MxStartDateDayOfMonth</th>\n",
       "      <th>MxStartDateDayOfWeek</th>\n",
       "      <th>MxStartDateMonth</th>\n",
       "      <th>MxWillReAdmit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Encounter Inpatient</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            AdmitReason  \\\n",
       "0           SNOMED Code   \n",
       "1           SNOMED Code   \n",
       "2           SNOMED Code   \n",
       "3           SNOMED Code   \n",
       "4           SNOMED Code   \n",
       "5   Encounter Inpatient   \n",
       "6           SNOMED Code   \n",
       "7           SNOMED Code   \n",
       "8   Encounter Inpatient   \n",
       "9   Encounter Inpatient   \n",
       "10  Encounter Inpatient   \n",
       "11  Encounter Inpatient   \n",
       "12  Encounter Inpatient   \n",
       "13  Encounter Inpatient   \n",
       "14  Encounter Inpatient   \n",
       "15  Encounter Inpatient   \n",
       "16  Encounter Inpatient   \n",
       "17  Encounter Inpatient   \n",
       "18  Encounter Inpatient   \n",
       "19  Encounter Inpatient   \n",
       "\n",
       "    Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN  \\\n",
       "0                                                   0                                      \n",
       "1                                                   0                                      \n",
       "2                                                   0                                      \n",
       "3                                                   0                                      \n",
       "4                                                   0                                      \n",
       "5                                                   0                                      \n",
       "6                                                   0                                      \n",
       "7                                                   0                                      \n",
       "8                                                   0                                      \n",
       "9                                                   0                                      \n",
       "10                                                  0                                      \n",
       "11                                                  0                                      \n",
       "12                                                  0                                      \n",
       "13                                                  0                                      \n",
       "14                                                  0                                      \n",
       "15                                                  0                                      \n",
       "16                                                  0                                      \n",
       "17                                                  0                                      \n",
       "18                                                  0                                      \n",
       "19                                                  0                                      \n",
       "\n",
       "    Com_CEREBROVASCULAR_DISEASE  Com_CHRONIC_PULMONARY_DISEASE  Com_DEMENTIA  \\\n",
       "0                             0                              0             0   \n",
       "1                             0                              0             0   \n",
       "2                             0                              0             0   \n",
       "3                             0                              0             0   \n",
       "4                             0                              0             0   \n",
       "5                             0                              0             0   \n",
       "6                             0                              0             0   \n",
       "7                             0                              0             0   \n",
       "8                             0                              0             0   \n",
       "9                             0                              0             0   \n",
       "10                            1                              0             0   \n",
       "11                            1                              0             0   \n",
       "12                            1                              0             0   \n",
       "13                            1                              0             0   \n",
       "14                            1                              0             0   \n",
       "15                            1                              0             0   \n",
       "16                            1                              0             0   \n",
       "17                            1                              0             0   \n",
       "18                            1                              0             0   \n",
       "19                            1                              0             0   \n",
       "\n",
       "    Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION  Com_HEMIPLEGIA_OR_PARAPLEGIA  \\\n",
       "0                                           0                             0   \n",
       "1                                           1                             0   \n",
       "2                                           0                             0   \n",
       "3                                           0                             0   \n",
       "4                                           0                             0   \n",
       "5                                           0                             0   \n",
       "6                                           0                             0   \n",
       "7                                           0                             0   \n",
       "8                                           0                             0   \n",
       "9                                           0                             0   \n",
       "10                                          0                             0   \n",
       "11                                          0                             0   \n",
       "12                                          0                             0   \n",
       "13                                          0                             0   \n",
       "14                                          0                             0   \n",
       "15                                          0                             0   \n",
       "16                                          0                             0   \n",
       "17                                          0                             0   \n",
       "18                                          0                             0   \n",
       "19                                          0                             0   \n",
       "\n",
       "    Com_MILD_LIVER_DISEASE  Com_MYOCARDIAL_INFARCTION  \\\n",
       "0                        0                          0   \n",
       "1                        0                          0   \n",
       "2                        0                          0   \n",
       "3                        0                          0   \n",
       "4                        0                          0   \n",
       "5                        0                          0   \n",
       "6                        0                          0   \n",
       "7                        0                          0   \n",
       "8                        0                          0   \n",
       "9                        0                          0   \n",
       "10                       0                          0   \n",
       "11                       0                          0   \n",
       "12                       0                          0   \n",
       "13                       0                          0   \n",
       "14                       0                          0   \n",
       "15                       0                          0   \n",
       "16                       0                          0   \n",
       "17                       0                          0   \n",
       "18                       0                          0   \n",
       "19                       0                          0   \n",
       "\n",
       "    Com_PERIPHERY_VASCULAR_DISEASE  ...  MxNumAdmitsSixMonth  \\\n",
       "0                                0  ...                    0   \n",
       "1                                0  ...                    0   \n",
       "2                                0  ...                    0   \n",
       "3                                1  ...                    0   \n",
       "4                                1  ...                    1   \n",
       "5                                0  ...                    0   \n",
       "6                                0  ...                    0   \n",
       "7                                0  ...                    1   \n",
       "8                                0  ...                    0   \n",
       "9                                0  ...                    0   \n",
       "10                               0  ...                    1   \n",
       "11                               0  ...                    0   \n",
       "12                               0  ...                    1   \n",
       "13                               0  ...                    2   \n",
       "14                               0  ...                    1   \n",
       "15                               0  ...                    1   \n",
       "16                               0  ...                    2   \n",
       "17                               0  ...                    2   \n",
       "18                               0  ...                    2   \n",
       "19                               0  ...                    2   \n",
       "\n",
       "    MxNumAdmitsThreeMonth  MxNumAdmitsTwelveMonth  MxNumDiagOneMonth  \\\n",
       "0                       0                       0                  1   \n",
       "1                       0                       0                  1   \n",
       "2                       0                       0                  1   \n",
       "3                       0                       0                  0   \n",
       "4                       1                       1                  1   \n",
       "5                       0                       0                  1   \n",
       "6                       0                       0                  0   \n",
       "7                       1                       1                  1   \n",
       "8                       0                       0                  1   \n",
       "9                       0                       1                  1   \n",
       "10                      1                       2                  1   \n",
       "11                      0                       0                  0   \n",
       "12                      1                       1                  0   \n",
       "13                      1                       2                  1   \n",
       "14                      0                       3                  0   \n",
       "15                      0                       4                  0   \n",
       "16                      1                       4                  0   \n",
       "17                      0                       3                  0   \n",
       "18                      1                       4                  0   \n",
       "19                      1                       5                  1   \n",
       "\n",
       "    MxNumDiagTwelveMonth  MxSmoker  MxStartDateDayOfMonth  \\\n",
       "0                      1         0                     10   \n",
       "1                      1         0                     10   \n",
       "2                      2         0                      1   \n",
       "3                      1         0                     31   \n",
       "4                      2         0                      4   \n",
       "5                      1         0                     12   \n",
       "6                      0         0                      5   \n",
       "7                      1         0                     12   \n",
       "8                      1         0                     11   \n",
       "9                      2         0                     15   \n",
       "10                     3         0                     12   \n",
       "11                     0         0                      4   \n",
       "12                     0         0                     11   \n",
       "13                     1         0                      7   \n",
       "14                     1         0                     14   \n",
       "15                     1         0                     27   \n",
       "16                     1         0                      3   \n",
       "17                     0         0                     22   \n",
       "18                     0         0                     28   \n",
       "19                     2         0                      6   \n",
       "\n",
       "    MxStartDateDayOfWeek  MxStartDateMonth MxWillReAdmit  \n",
       "0                      7                 7             0  \n",
       "1                      2                12             0  \n",
       "2                      3                10             0  \n",
       "3                      5                 8             1  \n",
       "4                      2                 9             0  \n",
       "5                      5                 8             0  \n",
       "6                      6                 8             1  \n",
       "7                      6                 8             0  \n",
       "8                      3                 3             0  \n",
       "9                      2                 9             0  \n",
       "10                     6                12             0  \n",
       "11                     6                 6             0  \n",
       "12                     1                 7             0  \n",
       "13                     5                10             0  \n",
       "14                     2                 2             0  \n",
       "15                     6                 5             0  \n",
       "16                     1                 7             0  \n",
       "17                     7                10             0  \n",
       "18                     2                11             0  \n",
       "19                     2                 2             0  \n",
       "\n",
       "[20 rows x 45 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(pd.read_sql(\"select TOP 20 * from Patient.ReadmissionTraining\", conn))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "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>rowCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rowCount\n",
       "0     10999"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(pd.read_sql(\"SELECT COUNT(*) rowCount FROM Patient.ReadmissionTraining\", conn))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AdmitReason                                                                                object\n",
       "Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN        int64\n",
       "Com_CEREBROVASCULAR_DISEASE                                                                 int64\n",
       "Com_CHRONIC_PULMONARY_DISEASE                                                               int64\n",
       "Com_DEMENTIA                                                                                int64\n",
       "Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION                                                   int64\n",
       "Com_HEMIPLEGIA_OR_PARAPLEGIA                                                                int64\n",
       "Com_MILD_LIVER_DISEASE                                                                      int64\n",
       "Com_MYOCARDIAL_INFARCTION                                                                   int64\n",
       "Com_PERIPHERY_VASCULAR_DISEASE                                                              int64\n",
       "Com_RENAL_DISEASE                                                                           int64\n",
       "CurCom_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN     int64\n",
       "CurCom_CEREBROVASCULAR_DISEASE                                                              int64\n",
       "CurCom_CHRONIC_PULMONARY_DISEASE                                                            int64\n",
       "CurCom_DEMENTIA                                                                             int64\n",
       "CurCom_MILD_LIVER_DISEASE                                                                   int64\n",
       "CurCom_MYOCARDIAL_INFARCTION                                                                int64\n",
       "CurCom_PERIPHERY_VASCULAR_DISEASE                                                           int64\n",
       "CurCom_RENAL_DISEASE                                                                        int64\n",
       "DxAgeGroup                                                                                 object\n",
       "DxDischargeLocation                                                                        object\n",
       "DxEncounterType                                                                            object\n",
       "DxGenderViaPatient                                                                         object\n",
       "LOS                                                                                         int64\n",
       "MxAgeDischarged                                                                             int64\n",
       "MxAlcohol                                                                                   int64\n",
       "MxDrugs                                                                                     int64\n",
       "MxEncounterEndYear                                                                          int64\n",
       "MxEncounterStartYear                                                                        int64\n",
       "MxEndDateDayOfMonth                                                                         int64\n",
       "MxEndDateDayOfWeek                                                                          int64\n",
       "MxEndDateMonth                                                                              int64\n",
       "MxExSmoker                                                                                  int64\n",
       "MxNeverSmoked                                                                               int64\n",
       "MxNumAdmitsOneMonth                                                                         int64\n",
       "MxNumAdmitsSixMonth                                                                         int64\n",
       "MxNumAdmitsThreeMonth                                                                       int64\n",
       "MxNumAdmitsTwelveMonth                                                                      int64\n",
       "MxNumDiagOneMonth                                                                           int64\n",
       "MxNumDiagTwelveMonth                                                                        int64\n",
       "MxSmoker                                                                                    int64\n",
       "MxStartDateDayOfMonth                                                                       int64\n",
       "MxStartDateDayOfWeek                                                                        int64\n",
       "MxStartDateMonth                                                                            int64\n",
       "MxWillReAdmit                                                                               int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Choose ML framework with \"SET ML CONFIGURATION\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "curs.execute(\"SET ML CONFIGURATION %AutoML\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. Create and Train an IntegratedML Model using default settings\n",
    "IntegratedML only needs a model name, the name of the column that is the target column to predict, and a table (or SELECT query to specify input columns.\n",
    "\n",
    "This is a simple DDL query that executes immediately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = curs.execute(\"CREATE MODEL ReadmissionModel PREDICTING (MxWillReAdmit) FROM Patient.ReadmissionTraining\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the model is defined, you can TRAIN it, which invokes the AutoML machine learning procedure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "curs.execute(\"TRAIN MODEL ReadmissionModel\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once that finishes, you can see some information about the model in the \"ML_TRAINED_MODELS\" table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "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>MODEL_NAME</th>\n",
       "      <th>TRAINED_MODEL_NAME</th>\n",
       "      <th>PROVIDER</th>\n",
       "      <th>TRAINED_TIMESTAMP</th>\n",
       "      <th>MODEL_TYPE</th>\n",
       "      <th>MODEL_INFO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bc</td>\n",
       "      <td>bc_t1</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2022-04-22 07:54:38.592000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:TensorFlow Neural Network, Package:T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CampaignModel</td>\n",
       "      <td>CampaignModel_t1</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2022-04-22 08:01:09.859000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:TensorFlow Neural Network, Package:T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ReadmissionModel1</td>\n",
       "      <td>ReadmissionModel1_t1</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2022-04-22 08:15:12.216000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:Random Forest, Package:sklearn, Prob...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ReadmissionModel</td>\n",
       "      <td>ReadmissionModel_t1</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2022-04-22 10:22:01.750000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:Random Forest, Package:sklearn, Prob...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          MODEL_NAME    TRAINED_MODEL_NAME PROVIDER  \\\n",
       "0                 bc                 bc_t1   AutoML   \n",
       "1      CampaignModel      CampaignModel_t1   AutoML   \n",
       "2  ReadmissionModel1  ReadmissionModel1_t1   AutoML   \n",
       "3   ReadmissionModel   ReadmissionModel_t1   AutoML   \n",
       "\n",
       "            TRAINED_TIMESTAMP      MODEL_TYPE  \\\n",
       "0  2022-04-22 07:54:38.592000  classification   \n",
       "1  2022-04-22 08:01:09.859000  classification   \n",
       "2  2022-04-22 08:15:12.216000  classification   \n",
       "3  2022-04-22 10:22:01.750000  classification   \n",
       "\n",
       "                                          MODEL_INFO  \n",
       "0  ModelType:TensorFlow Neural Network, Package:T...  \n",
       "1  ModelType:TensorFlow Neural Network, Package:T...  \n",
       "2  ModelType:Random Forest, Package:sklearn, Prob...  \n",
       "3  ModelType:Random Forest, Package:sklearn, Prob...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df3 = pd.read_sql(\"SELECT * FROM INFORMATION_SCHEMA.ML_TRAINED_MODELS\", conn)\n",
    "display(df3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Compare model output to data it has not seen yet\n",
    "Now you can use SQL to SELECT data from another table, run the IntegratedML model on this new data, and see how well the predictions match the data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "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>PredictedReadmit</th>\n",
       "      <th>ActualReadmit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3043</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3044</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3045</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3046</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3047</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3048 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      PredictedReadmit  ActualReadmit\n",
       "0                    1              1\n",
       "1                    1              1\n",
       "2                    0              0\n",
       "3                    1              0\n",
       "4                    1              0\n",
       "...                ...            ...\n",
       "3043                 0              0\n",
       "3044                 0              0\n",
       "3045                 0              0\n",
       "3046                 0              0\n",
       "3047                 0              0\n",
       "\n",
       "[3048 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df4 = pd.read_sql(\"SELECT PREDICT(ReadmissionModel) AS PredictedReadmit, \\\n",
    "                  MxWillReAdmit AS ActualReadmit FROM Patient.ReadmissionPredict\", conn)\n",
    "display(df4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10. VALIDATE MODEL command calculates accuracy metrics\n",
    "You can certainly take that output above and calculate the accuracy using a standard formula, but IntegratedML has a built-in function to do that!\n",
    "\n",
    "Each time you run the command \"VALIDATE MODEL...\" it generates a set of metrics calculated on the data passed into the query. Since this table can be a bit difficult to read in its raw form we use a simple \"pivot\" call to arrange the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "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>METRIC_NAME</th>\n",
       "      <th>Accuracy</th>\n",
       "      <th>F-Measure</th>\n",
       "      <th>Precision</th>\n",
       "      <th>Recall</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VALIDATION_RUN_NAME</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CampaignModel_t1_v1</th>\n",
       "      <td>0.60</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ReadmissionModel1_t1_v1</th>\n",
       "      <td>0.83</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ReadmissionModel_t1_v1</th>\n",
       "      <td>0.82</td>\n",
       "      <td>0.58</td>\n",
       "      <td>0.63</td>\n",
       "      <td>0.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "METRIC_NAME              Accuracy  F-Measure  Precision  Recall\n",
       "VALIDATION_RUN_NAME                                            \n",
       "CampaignModel_t1_v1          0.60       0.20       0.81    0.11\n",
       "ReadmissionModel1_t1_v1      0.83       0.62       0.66    0.59\n",
       "ReadmissionModel_t1_v1       0.82       0.58       0.63    0.54"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "curs.execute(\"VALIDATE MODEL ReadmissionModel FROM Patient.ReadmissionPredict\")\n",
    "df5 = pd.read_sql(\"SELECT * FROM INFORMATION_SCHEMA.ML_VALIDATION_METRICS\", conn)\n",
    "df6 = df5.pivot(index='VALIDATION_RUN_NAME', columns='METRIC_NAME', values='METRIC_VALUE')\n",
    "display(df6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Query that highlights incorrectly predicted rows\n",
    "\n",
    "This query retrieves encounters the model incorrectly scored, along with the probability estimate of the prediction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "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>ReadmissionProbability</th>\n",
       "      <th>PredictedReadmission</th>\n",
       "      <th>ActualReadmission</th>\n",
       "      <th>AdmitReason</th>\n",
       "      <th>Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN</th>\n",
       "      <th>Com_CEREBROVASCULAR_DISEASE</th>\n",
       "      <th>Com_CHRONIC_PULMONARY_DISEASE</th>\n",
       "      <th>Com_DEMENTIA</th>\n",
       "      <th>Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION</th>\n",
       "      <th>Com_HEMIPLEGIA_OR_PARAPLEGIA</th>\n",
       "      <th>...</th>\n",
       "      <th>MxNumAdmitsSixMonth</th>\n",
       "      <th>MxNumAdmitsThreeMonth</th>\n",
       "      <th>MxNumAdmitsTwelveMonth</th>\n",
       "      <th>MxNumDiagOneMonth</th>\n",
       "      <th>MxNumDiagTwelveMonth</th>\n",
       "      <th>MxSmoker</th>\n",
       "      <th>MxStartDateDayOfMonth</th>\n",
       "      <th>MxStartDateDayOfWeek</th>\n",
       "      <th>MxStartDateMonth</th>\n",
       "      <th>MxWillReAdmit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.246878</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Encounter for problem</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.246878</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Encounter for problem</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.150000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.020000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>SNOMED Code</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>1</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>0.120000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Emergency Room admission (procedure)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>0.170000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Emergency Room admission (procedure)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>0.150000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Emergency Room admission (procedure)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>0.230000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Emergency Room admission (procedure)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>0.140000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Emergency Room admission (procedure)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>331 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ReadmissionProbability  PredictedReadmission  ActualReadmission  \\\n",
       "0                  0.246878                     0                  1   \n",
       "1                  0.246878                     0                  1   \n",
       "2                  0.150000                     0                  1   \n",
       "3                  0.020000                     0                  1   \n",
       "4                  0.000000                     0                  1   \n",
       "..                      ...                   ...                ...   \n",
       "326                0.120000                     0                  1   \n",
       "327                0.170000                     0                  1   \n",
       "328                0.150000                     0                  1   \n",
       "329                0.230000                     0                  1   \n",
       "330                0.140000                     0                  1   \n",
       "\n",
       "                              AdmitReason  \\\n",
       "0                   Encounter for problem   \n",
       "1                   Encounter for problem   \n",
       "2                             SNOMED Code   \n",
       "3                             SNOMED Code   \n",
       "4                             SNOMED Code   \n",
       "..                                    ...   \n",
       "326  Emergency Room admission (procedure)   \n",
       "327  Emergency Room admission (procedure)   \n",
       "328  Emergency Room admission (procedure)   \n",
       "329  Emergency Room admission (procedure)   \n",
       "330  Emergency Room admission (procedure)   \n",
       "\n",
       "     Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN  \\\n",
       "0                                                    1                                      \n",
       "1                                                    1                                      \n",
       "2                                                    0                                      \n",
       "3                                                    0                                      \n",
       "4                                                    0                                      \n",
       "..                                                 ...                                      \n",
       "326                                                  0                                      \n",
       "327                                                  0                                      \n",
       "328                                                  0                                      \n",
       "329                                                  0                                      \n",
       "330                                                  0                                      \n",
       "\n",
       "     Com_CEREBROVASCULAR_DISEASE  Com_CHRONIC_PULMONARY_DISEASE  Com_DEMENTIA  \\\n",
       "0                              0                              1             0   \n",
       "1                              0                              1             0   \n",
       "2                              0                              0             0   \n",
       "3                              0                              0             0   \n",
       "4                              0                              1             0   \n",
       "..                           ...                            ...           ...   \n",
       "326                            0                              0             0   \n",
       "327                            0                              0             0   \n",
       "328                            0                              0             0   \n",
       "329                            0                              0             0   \n",
       "330                            0                              0             0   \n",
       "\n",
       "     Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION  Com_HEMIPLEGIA_OR_PARAPLEGIA  \\\n",
       "0                                            0                             0   \n",
       "1                                            0                             0   \n",
       "2                                            0                             0   \n",
       "3                                            0                             0   \n",
       "4                                            0                             0   \n",
       "..                                         ...                           ...   \n",
       "326                                          0                             0   \n",
       "327                                          0                             0   \n",
       "328                                          0                             0   \n",
       "329                                          0                             0   \n",
       "330                                          0                             0   \n",
       "\n",
       "     ...  MxNumAdmitsSixMonth  MxNumAdmitsThreeMonth  MxNumAdmitsTwelveMonth  \\\n",
       "0    ...                    5                      3                      11   \n",
       "1    ...                    5                      3                      11   \n",
       "2    ...                    0                      0                       0   \n",
       "3    ...                    0                      0                       0   \n",
       "4    ...                    0                      0                       0   \n",
       "..   ...                  ...                    ...                     ...   \n",
       "326  ...                    3                      3                       3   \n",
       "327  ...                    4                      4                       4   \n",
       "328  ...                    5                      3                       5   \n",
       "329  ...                    0                      0                       0   \n",
       "330  ...                    1                      1                       1   \n",
       "\n",
       "     MxNumDiagOneMonth  MxNumDiagTwelveMonth  MxSmoker  MxStartDateDayOfMonth  \\\n",
       "0                    0                     0         0                     22   \n",
       "1                    0                     0         0                     19   \n",
       "2                    1                     2         0                     22   \n",
       "3                    1                     1         0                      3   \n",
       "4                    1                     1         0                      3   \n",
       "..                 ...                   ...       ...                    ...   \n",
       "326                  2                     4         0                     14   \n",
       "327                  2                     5         0                     14   \n",
       "328                  2                     6         0                     13   \n",
       "329                  0                     0         0                     14   \n",
       "330                  1                     1         0                     13   \n",
       "\n",
       "     MxStartDateDayOfWeek  MxStartDateMonth  MxWillReAdmit  \n",
       "0                       4                10              1  \n",
       "1                       4                11              1  \n",
       "2                       3                 1              1  \n",
       "3                       4                11              1  \n",
       "4                       7                 2              1  \n",
       "..                    ...               ...            ...  \n",
       "326                     5                11              1  \n",
       "327                     7                12              1  \n",
       "328                     2                 1              1  \n",
       "329                     7                12              1  \n",
       "330                     2                 1              1  \n",
       "\n",
       "[331 rows x 48 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(pd.read_sql('''\n",
    "SELECT\n",
    "\tPROBABILITY(ReadmissionModel FOR '1') ReadmissionProbability,\n",
    "\tPREDICT(ReadmissionModel) PredictedReadmission,\n",
    "\tMxWillReAdmit ActualReadmission,\n",
    "\t*\n",
    "FROM\n",
    "\tPatient.ReadmissionPredict\n",
    "WHERE\n",
    "\tMxWillReAdmit = 1\n",
    "\tAND MxWillReAdmit != PREDICT(ReadmissionModel)\n",
    "''', conn))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Query that pulls recent Encounters and filters by those with high probability of readmission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>ReadmissionProbability</th>\n",
       "      <th>PredictedReadmission</th>\n",
       "      <th>AdmitReason</th>\n",
       "      <th>Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN</th>\n",
       "      <th>Com_CEREBROVASCULAR_DISEASE</th>\n",
       "      <th>Com_CHRONIC_PULMONARY_DISEASE</th>\n",
       "      <th>Com_DEMENTIA</th>\n",
       "      <th>Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION</th>\n",
       "      <th>Com_HEMIPLEGIA_OR_PARAPLEGIA</th>\n",
       "      <th>Com_MILD_LIVER_DISEASE</th>\n",
       "      <th>...</th>\n",
       "      <th>MxNumAdmitsSixMonth</th>\n",
       "      <th>MxNumAdmitsThreeMonth</th>\n",
       "      <th>MxNumAdmitsTwelveMonth</th>\n",
       "      <th>MxNumDiagOneMonth</th>\n",
       "      <th>MxNumDiagTwelveMonth</th>\n",
       "      <th>MxSmoker</th>\n",
       "      <th>MxStartDateDayOfMonth</th>\n",
       "      <th>MxStartDateDayOfWeek</th>\n",
       "      <th>MxStartDateMonth</th>\n",
       "      <th>MxWillReAdmit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.315704</td>\n",
       "      <td>0</td>\n",
       "      <td>Encounter for problem</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.330000</td>\n",
       "      <td>0</td>\n",
       "      <td>Inpatient Visit</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.400000</td>\n",
       "      <td>0</td>\n",
       "      <td>Inpatient Follow-Up</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ReadmissionProbability  PredictedReadmission            AdmitReason  \\\n",
       "0                0.315704                     0  Encounter for problem   \n",
       "1                0.330000                     0        Inpatient Visit   \n",
       "2                0.400000                     0    Inpatient Follow-Up   \n",
       "\n",
       "   Com_ANY_MALIGNANCY_INCLUDING_LYMPHOMA_AND_LEUKEMIA_EXCEPT_MALIGNANT_NEOPLASM_OF_SKIN  \\\n",
       "0                                                  1                                      \n",
       "1                                                  0                                      \n",
       "2                                                  0                                      \n",
       "\n",
       "   Com_CEREBROVASCULAR_DISEASE  Com_CHRONIC_PULMONARY_DISEASE  Com_DEMENTIA  \\\n",
       "0                            0                              0             0   \n",
       "1                            0                              0             0   \n",
       "2                            0                              0             0   \n",
       "\n",
       "   Com_DIABETES_WITHOUT_CHRONIC_COMPLICATION  Com_HEMIPLEGIA_OR_PARAPLEGIA  \\\n",
       "0                                          0                             0   \n",
       "1                                          0                             0   \n",
       "2                                          0                             0   \n",
       "\n",
       "   Com_MILD_LIVER_DISEASE  ...  MxNumAdmitsSixMonth  MxNumAdmitsThreeMonth  \\\n",
       "0                       0  ...                    5                      2   \n",
       "1                       0  ...                    0                      0   \n",
       "2                       0  ...                    1                      1   \n",
       "\n",
       "   MxNumAdmitsTwelveMonth  MxNumDiagOneMonth  MxNumDiagTwelveMonth  MxSmoker  \\\n",
       "0                      13                  0                     5         0   \n",
       "1                       0                  1                     1         0   \n",
       "2                       1                  1                     1         0   \n",
       "\n",
       "   MxStartDateDayOfMonth  MxStartDateDayOfWeek  MxStartDateMonth  \\\n",
       "0                     24                     3                12   \n",
       "1                     30                     7                11   \n",
       "2                      8                     1                12   \n",
       "\n",
       "   MxWillReAdmit  \n",
       "0              0  \n",
       "1              1  \n",
       "2              0  \n",
       "\n",
       "[3 rows x 47 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(pd.read_sql('''\n",
    "SELECT \n",
    "\tPROBABILITY(ReadmissionModel FOR '1') ReadmissionProbability,\n",
    "\tPREDICT(ReadmissionModel) PredictedReadmission,\n",
    "\t*\n",
    "FROM\n",
    "\tPatient.ReadmissionPredict\n",
    "WHERE\n",
    "\tMxEncounterEndYear >=2019 AND\n",
    "\tMxEndDateMonth >= 12 AND\n",
    "    PROBABILITY(ReadmissionModel FOR '1') >= 0.25\n",
    "    ''', conn))"
   ]
  }
 ],
 "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}