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": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/bin/bash: /opt/miniconda3/bin/conda: Permission denied\n",
      "\u001b[33mWARNING: pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.\u001b[0m\n",
      "\u001b[33mWARNING: Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(\"Can't connect to HTTPS URL because the SSL module is not available.\")': /simple/jupyterthemes/\u001b[0m\n",
      "\u001b[33mWARNING: Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(\"Can't connect to HTTPS URL because the SSL module is not available.\")': /simple/jupyterthemes/\u001b[0m\n",
      "\u001b[33mWARNING: Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(\"Can't connect to HTTPS URL because the SSL module is not available.\")': /simple/jupyterthemes/\u001b[0m\n",
      "\u001b[33mWARNING: Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(\"Can't connect to HTTPS URL because the SSL module is not available.\")': /simple/jupyterthemes/\u001b[0m\n",
      "\u001b[33mWARNING: Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(\"Can't connect to HTTPS URL because the SSL module is not available.\")': /simple/jupyterthemes/\u001b[0m\n",
      "Could not fetch URL https://pypi.org/simple/jupyterthemes/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/jupyterthemes/ (Caused by SSLError(\"Can't connect to HTTPS URL because the SSL module is not available.\")) - skipping\n",
      "\u001b[31mERROR: Could not find a version that satisfies the requirement jupyterthemes (from versions: none)\u001b[0m\n",
      "\u001b[31mERROR: No matching distribution found for jupyterthemes\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:\n",
      "/root/.jupyter/custom &\n",
      "/root/.local/share/jupyter/nbextensions\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; python_version > \"2.7\" and platform_python_implementation != \"Jython\" in /usr/local/lib/python3.6/dist-packages (from JayDeBeApi) (0.7.5)\n"
     ]
    }
   ],
   "source": [
    "!pip install JayDeBeApi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Package                Version\n",
      "---------------------- -----------\n",
      "absl-py                0.9.0\n",
      "asn1crypto             0.24.0\n",
      "astunparse             1.6.3\n",
      "attrs                  19.3.0\n",
      "backcall               0.1.0\n",
      "bleach                 3.1.5\n",
      "cachetools             4.1.0\n",
      "certifi                2020.4.5.1\n",
      "chardet                3.0.4\n",
      "cryptography           2.1.4\n",
      "cycler                 0.10.0\n",
      "decorator              4.4.2\n",
      "defusedxml             0.6.0\n",
      "entrypoints            0.3\n",
      "gast                   0.3.3\n",
      "google-auth            1.14.2\n",
      "google-auth-oauthlib   0.4.1\n",
      "google-pasta           0.2.0\n",
      "grpcio                 1.28.1\n",
      "h5py                   2.10.0\n",
      "idna                   2.6\n",
      "importlib-metadata     1.6.0\n",
      "ipykernel              5.1.1\n",
      "ipython                7.14.0\n",
      "ipython-genutils       0.2.0\n",
      "ipywidgets             7.5.1\n",
      "JayDeBeApi             1.2.3\n",
      "jedi                   0.17.0\n",
      "Jinja2                 2.11.2\n",
      "joblib                 0.15.1\n",
      "JPype1                 0.7.5\n",
      "jsonschema             3.2.0\n",
      "jupyter                1.0.0\n",
      "jupyter-client         6.1.3\n",
      "jupyter-console        6.1.0\n",
      "jupyter-core           4.6.3\n",
      "jupyter-http-over-ws   0.0.8\n",
      "jupyterthemes          0.20.0\n",
      "Keras-Preprocessing    1.1.0\n",
      "keyring                10.6.0\n",
      "keyrings.alt           3.0\n",
      "kiwisolver             1.2.0\n",
      "lesscpy                0.14.0\n",
      "Markdown               3.2.1\n",
      "MarkupSafe             1.1.1\n",
      "matplotlib             3.2.1\n",
      "mistune                0.8.4\n",
      "nbconvert              5.6.1\n",
      "nbformat               4.4.0\n",
      "notebook               6.0.3\n",
      "numpy                  1.18.4\n",
      "oauthlib               3.1.0\n",
      "opt-einsum             3.2.1\n",
      "packaging              20.3\n",
      "pandas                 1.0.4\n",
      "pandocfilters          1.4.2\n",
      "parso                  0.7.0\n",
      "pexpect                4.8.0\n",
      "pickleshare            0.7.5\n",
      "pip                    20.1.1\n",
      "ply                    3.11\n",
      "prometheus-client      0.7.1\n",
      "prompt-toolkit         3.0.5\n",
      "protobuf               3.11.3\n",
      "ptyprocess             0.6.0\n",
      "pyasn1                 0.4.8\n",
      "pyasn1-modules         0.2.8\n",
      "pycrypto               2.6.1\n",
      "Pygments               2.6.1\n",
      "pygobject              3.26.1\n",
      "pyparsing              2.4.7\n",
      "pyrsistent             0.16.0\n",
      "python-dateutil        2.8.1\n",
      "pytz                   2020.1\n",
      "pyxdg                  0.25\n",
      "pyzmq                  19.0.1\n",
      "qtconsole              4.7.3\n",
      "QtPy                   1.9.0\n",
      "requests               2.23.0\n",
      "requests-oauthlib      1.3.0\n",
      "rsa                    4.0\n",
      "scikit-learn           0.23.1\n",
      "scipy                  1.4.1\n",
      "SecretStorage          2.3.1\n",
      "Send2Trash             1.5.0\n",
      "setuptools             46.1.3\n",
      "six                    1.14.0\n",
      "tensorboard            2.2.1\n",
      "tensorboard-plugin-wit 1.6.0.post3\n",
      "tensorflow             2.2.0\n",
      "tensorflow-estimator   2.2.0\n",
      "termcolor              1.1.0\n",
      "terminado              0.8.3\n",
      "testpath               0.4.4\n",
      "threadpoolctl          2.1.0\n",
      "tornado                6.0.4\n",
      "traitlets              4.3.3\n",
      "urllib3                1.25.9\n",
      "wcwidth                0.1.9\n",
      "webencodings           0.5.1\n",
      "Werkzeug               1.0.1\n",
      "wheel                  0.30.0\n",
      "widgetsnbextension     3.5.1\n",
      "wrapt                  1.12.1\n",
      "zipp                   3.1.0\n"
     ]
    }
   ],
   "source": [
    "!pip list JPype1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Set environment variables, if necessary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import jaydebeapi\n",
    "url = \"jdbc:IRIS://irisimlsvr:51773/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": 7,
   "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 = 'SQLUser.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>ID</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>...</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>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>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>2</td>\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>...</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>3</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>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>4</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>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>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>5</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>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>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>6</td>\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>...</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>7</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>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>8</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>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>9</td>\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>...</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>10</td>\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>...</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>11</td>\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>...</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>12</td>\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>...</td>\n",
       "      <td>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>13</td>\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>...</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>14</td>\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>...</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>15</td>\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>...</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>16</td>\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>...</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>17</td>\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>...</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>18</td>\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>...</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>19</td>\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>...</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>20</td>\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>...</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 × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID          AdmitReason  \\\n",
       "0    1          SNOMED Code   \n",
       "1    2          SNOMED Code   \n",
       "2    3          SNOMED Code   \n",
       "3    4          SNOMED Code   \n",
       "4    5          SNOMED Code   \n",
       "5    6  Encounter Inpatient   \n",
       "6    7          SNOMED Code   \n",
       "7    8          SNOMED Code   \n",
       "8    9  Encounter Inpatient   \n",
       "9   10  Encounter Inpatient   \n",
       "10  11  Encounter Inpatient   \n",
       "11  12  Encounter Inpatient   \n",
       "12  13  Encounter Inpatient   \n",
       "13  14  Encounter Inpatient   \n",
       "14  15  Encounter Inpatient   \n",
       "15  16  Encounter Inpatient   \n",
       "16  17  Encounter Inpatient   \n",
       "17  18  Encounter Inpatient   \n",
       "18  19  Encounter Inpatient   \n",
       "19  20  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",
       "    MxNumAdmitsSixMonth  MxNumAdmitsThreeMonth  MxNumAdmitsTwelveMonth  \\\n",
       "0                     0                      0                       0   \n",
       "1                     0                      0                       0   \n",
       "2                     0                      0                       0   \n",
       "3                     0                      0                       0   \n",
       "4                     1                      1                       1   \n",
       "5                     0                      0                       0   \n",
       "6                     0                      0                       0   \n",
       "7                     1                      1                       1   \n",
       "8                     0                      0                       0   \n",
       "9                     0                      0                       1   \n",
       "10                    1                      1                       2   \n",
       "11                    0                      0                       0   \n",
       "12                    1                      1                       1   \n",
       "13                    2                      1                       2   \n",
       "14                    1                      0                       3   \n",
       "15                    1                      0                       4   \n",
       "16                    2                      1                       4   \n",
       "17                    2                      0                       3   \n",
       "18                    2                      1                       4   \n",
       "19                    2                      1                       5   \n",
       "\n",
       "    MxNumDiagOneMonth  MxNumDiagTwelveMonth  MxSmoker  MxStartDateDayOfMonth  \\\n",
       "0                   1                     1         0                     10   \n",
       "1                   1                     1         0                     10   \n",
       "2                   1                     2         0                      1   \n",
       "3                   0                     1         0                     31   \n",
       "4                   1                     2         0                      4   \n",
       "5                   1                     1         0                     12   \n",
       "6                   0                     0         0                      5   \n",
       "7                   1                     1         0                     12   \n",
       "8                   1                     1         0                     11   \n",
       "9                   1                     2         0                     15   \n",
       "10                  1                     3         0                     12   \n",
       "11                  0                     0         0                      4   \n",
       "12                  0                     0         0                     11   \n",
       "13                  1                     1         0                      7   \n",
       "14                  0                     1         0                     14   \n",
       "15                  0                     1         0                     27   \n",
       "16                  0                     1         0                      3   \n",
       "17                  0                     0         0                     22   \n",
       "18                  0                     0         0                     28   \n",
       "19                  1                     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 46 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": [
    "### 5. Make some views to split training and testing datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_sql(\"SELECT COUNT(*) FROM %s WHERE  ID>=11000\" % dataTable, conn)\n",
    "display(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "java.sql.SQLException: [SQLCODE: <-201>:<Table or view name not unique>]\r\n[Location: <ServerLoop>]",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.Update\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.InStream.readMessage\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.InStream.readMessage\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.processError\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.getServerError\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mException\u001b[0m: Java Exception",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mjava.sql.SQLException\u001b[0m                     Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    533\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 534\u001b[0;31m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mjava.sql.SQLException\u001b[0m: java.sql.SQLException: [SQLCODE: <-201>:<Table or view name not unique>]\r\n[Location: <ServerLoop>]",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-12-00b435a14b43>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Training set view\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mcurs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"CREATE VIEW SQLUser.ReadmissionTraining AS SELECT * FROM SQLUser.Readmission WHERE ID<11000\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0;31m# Prediction set\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mcurs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"CREATE VIEW SQLUser.ReadmissionPredict AS SELECT * FROM SQLUser.Readmission WHERE ID>=11000\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    534\u001b[0m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 536\u001b[0;31m             \u001b[0m_handle_sql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    537\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mis_rs\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    538\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgetResultSet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36m_handle_sql_exception_jpype\u001b[0;34m()\u001b[0m\n\u001b[1;32m    163\u001b[0m         \u001b[0mexc_type\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mInterfaceError\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    164\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 165\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    166\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    167\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_jdbc_connect_jpype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mjclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdriver_args\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjars\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlibs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb)\u001b[0m\n\u001b[1;32m     55\u001b[0m             \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtp\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     56\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 57\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     58\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    532\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_stmt_parms\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    533\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 534\u001b[0;31m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    536\u001b[0m             \u001b[0m_handle_sql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDatabaseError\u001b[0m: java.sql.SQLException: [SQLCODE: <-201>:<Table or view name not unique>]\r\n[Location: <ServerLoop>]"
     ]
    }
   ],
   "source": [
    "# Training set view\n",
    "curs.execute(\"CREATE VIEW SQLUser.ReadmissionTraining AS SELECT * FROM SQLUser.Readmission WHERE ID<11000\")\n",
    "# Prediction set\n",
    "curs.execute(\"CREATE VIEW SQLUser.ReadmissionPredict AS SELECT * FROM SQLUser.Readmission WHERE ID>=11000\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. 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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "curs.execute(\"DROP MODEL ReadmissionModel\")\n",
    "curs.execute(\"CREATE MODEL ReadmissionModel PREDICTING (MxWillReAdmit) FROM 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": 15,
   "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": 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>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>ReadmissionModel</td>\n",
       "      <td>ReadmissionModel2</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2020-07-24 14:51:55.344000</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           TRAINED_TIMESTAMP  \\\n",
       "0  ReadmissionModel  ReadmissionModel2   AutoML  2020-07-24 14:51:55.344000   \n",
       "\n",
       "       MODEL_TYPE                                         MODEL_INFO  \n",
       "0  classification  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": [
    "### 7. 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": 17,
   "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 ReadmissionPredict\", conn)\n",
    "display(df4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. 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": 18,
   "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>ReadmissionModel21</th>\n",
       "      <td>0.82</td>\n",
       "      <td>0.59</td>\n",
       "      <td>0.63</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "METRIC_NAME          Accuracy  F-Measure  Precision  Recall\n",
       "VALIDATION_RUN_NAME                                        \n",
       "ReadmissionModel21       0.82       0.59       0.63    0.55"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "curs.execute(\"VALIDATE MODEL ReadmissionModel FROM SQLUser.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)"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}