jupyter-samples/ED_visit_90_day.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Train an IntegratedML model on ED Readmit likelihood Dataset\n",
    "## Use JDBC to connect to InterSystems IRIS database\n",
    "**NOTE: This Notebook will not run as-is!**\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 ED Readmits in the next 90 days\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": "markdown",
   "metadata": {},
   "source": [
    "### 1. Get jdbc connection and cursor"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
=======
   "execution_count": 1,
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
=======
   "execution_count": 2,
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i '../Initializations/Conns.py'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Create and specify the source data table(s)"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
=======
   "execution_count": 3,
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "metadata": {},
   "outputs": [],
   "source": [
    "#Use this block to create a starting data set that you can/will build upon.\n",
    "#NOTE: It is always useful to have a unique identifier in the data\n",
    "TargetTable = 'Data.EDEncsPredB90View'\n",
    "TrainTable = 'Data.EDEncsTraining'\n",
    "TestTable = 'Data.EDEncsTesting'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Review the data to ensure the Target variable and Independent variables are in good standing."
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
=======
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ID  DRID  HPS   Age Gend Mar InsRel PlanCode  T30  T60  ...  S180  S365  \\\n",
      "0   1     1  4.0  52.0    F   M      S      L05  0.0  0.0  ...  53.0  87.0   \n",
      "1   2     2  2.0  48.0    F   M      1    13947  0.0  0.0  ...   0.0   1.0   \n",
      "2   3     3  1.0  66.0    F   M      S      W01  NaN  NaN  ...   NaN   NaN   \n",
      "3   4     4  NaN  62.0    M   D      S      S20  NaN  NaN  ...   NaN   NaN   \n",
      "4   5     5  NaN  51.0    M   S      S      W01  0.0  1.0  ...   NaN   NaN   \n",
      "\n",
      "   Gen  dGen  Med  dMed  Appt  dAppt   Rx  dRx  \n",
      "0  9.0   7.0  9.0   3.0   0.0    0.0  2.0  2.0  \n",
      "1  NaN   NaN  NaN   NaN   NaN    NaN  NaN  NaN  \n",
      "2  NaN   NaN  NaN   NaN   NaN    NaN  NaN  NaN  \n",
      "3  NaN   NaN  NaN   NaN   NaN    NaN  NaN  NaN  \n",
      "4  NaN   NaN  NaN   NaN   NaN    NaN  NaN  NaN  \n",
      "\n",
      "[5 rows x 75 columns]\n",
      "Index(['ID', 'DRID', 'HPS', 'Age', 'Gend', 'Mar', 'InsRel', 'PlanCode', 'T30',\n",
      "       'T60', 'T90', 'E30', 'E90', 'E180', 'E365', 'I180', 'I365', 'O30',\n",
      "       'O90', 'O180', 'O365', 'ObsHt', 'dObsHt', 'ObsWt', 'dObsWt', 'ObsBMI',\n",
      "       'dObsBMI', 'ObsBSA', 'dObsBSA', 'ObsTemp', 'dObsTemp', 'ObsPulse',\n",
      "       'dObsPulse', 'ObsBPS', 'dObsBPS', 'ObsBPD', 'dObsBPD', 'ObsO2',\n",
      "       'dObsO2', 'Labs', 'dLabs', 'Meds', 'dMeds', 'Rads', 'dRads', 'Vax',\n",
      "       'dVax', 'HypChol_E78', 'dHypChol_E78', 'TII_E11', 'dTII_E11',\n",
      "       'GenEnc_Z00', 'dGenEnc_Z00', 'Scrn_Z13', 'dScrn_Z13', 'Couns_Z71',\n",
      "       'dCouns_Z71', 'OWt_E66', 'dOWt_E66', 'HypThy_E03', 'dHypThy_E03',\n",
      "       'Scrn_Z12', 'dScrn_Z12', 'S30', 'S90', 'S180', 'S365', 'Gen', 'dGen',\n",
      "       'Med', 'dMed', 'Appt', 'dAppt', 'Rx', 'dRx'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "source": [
    "tKeep()\n",
    "import pandas as pd\n",
    "from IPython.display import display\n",
    "\n",
<<<<<<< HEAD
    "df = pd.read_sql(\"select top 3 * from Data.PatientCostData\", iconn)\n",
=======
    "df = pd.read_sql(\"select top 5 * from Data.PatientCostData\", iconn)\n",
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
    "print(df)\n",
    "print(df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop and unwanted fields"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
   "metadata": {},
   "outputs": [],
=======
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'ID,DRID,HPS,Age,Gend,Mar,InsRel,PlanCode,E30,E90,E180,E365,I180,I365,O30,O90,O180,O365,ObsHt,dObsHt,ObsWt,dObsWt,ObsBMI,dObsBMI,ObsBSA,dObsBSA,ObsTemp,dObsTemp,ObsPulse,dObsPulse,ObsBPS,dObsBPS,ObsBPD,dObsBPD,ObsO2,dObsO2,Labs,dLabs,Meds,dMeds,Rads,dRads,Vax,dVax,HypChol_E78,dHypChol_E78,TII_E11,dTII_E11,GenEnc_Z00,dGenEnc_Z00,Scrn_Z13,dScrn_Z13,Couns_Z71,dCouns_Z71,OWt_E66,dOWt_E66,HypThy_E03,dHypThy_E03,Scrn_Z12,dScrn_Z12,S30,S90,S180,S365,Gen,dGen,Med,dMed,Appt,dAppt,Rx,dRx'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "source": [
    "df = df.drop(['T30', 'T60', 'T90'], axis = 1)\n",
    "Usable = str(list(df.columns)).replace(\"', '\", \",\")[2:-2]\n",
    "Usable"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
   "metadata": {},
   "outputs": [],
=======
   "execution_count": 6,
   "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>Recs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>126633</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Recs\n",
       "0  126633"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "source": [
    "icurs.execute(' \\\n",
    "    create or replace view %s as \\\n",
    "        select case when t90 > 0 then 1 else 0 end as B90, %s \\\n",
    "        from Data.PatientCostData' % (TargetTable, Usable))\n",
    "df1 = pd.read_sql('SELECT COUNT(*) as Recs FROM %s' % TargetTable, iconn)\n",
    "TargetVar = 'B90'\n",
    "display(df1)"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
   "metadata": {},
   "outputs": [],
=======
   "execution_count": 7,
   "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>B90</th>\n",
       "      <th>Recs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>120181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>6452</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   B90    Recs\n",
       "0    0  120181\n",
       "1    1    6452"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "source": [
    "Distro = pd.read_sql('select %s, count(*) as Recs from %s group by %s' % (TargetVar, TargetTable, TargetVar), iconn)\n",
    "Distro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Assess the probability of your target and sample accordingly into split training and testing datasets"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
=======
   "execution_count": 8,
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "metadata": {},
   "outputs": [],
   "source": [
    "#we want to split the data into Training (80%) and Test (20%), ...\n",
    "# but also reduce the ratio of Negative (ED Enc = 0) to Positive\n",
    "Train = 0.8\n",
    "TVRatio = 2\n",
    "PT_List = pd.read_sql('select DRID, %s from %s order by %s, DRID' % (TargetVar, TargetTable, TargetVar), iconn)\n",
    "PT_List.index = PT_List['DRID']"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
=======
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      DRID\n",
      "B90       \n",
      "0    10323\n",
      "1     5161\n",
      "      DRID\n",
      "B90       \n",
      "0    27787\n",
      "1     1291\n"
     ]
    }
   ],
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "source": [
    "#Create the lists, check the ratios, and create the \"In lists\":\n",
    "TrainList = PT_List[PT_List[TargetVar] == 0].sample(int(Distro['Recs'].loc[1]*TVRatio*Train)) \\\n",
    "    .append(PT_List[PT_List[TargetVar] == 1].sample(int(Distro['Recs'].loc[1]*Train)))\n",
    "TrainList['Flag'] = 1\n",
    "TrainList.index = TrainList['DRID']\n",
    "print(TrainList.pivot_table(index = TargetVar, values = 'DRID', aggfunc = 'count'))\n",
    "#NOTE: It is IMPERATIVE that Test does NOT contain any Train data\n",
    "TestList = PT_List.join(TrainList['Flag'], how = 'left')\n",
    "TestList = TestList[(TestList['Flag'] != 1)]\n",
    "TestList = TestList[(TestList[TargetVar] == 1)].append(TestList[TestList[TargetVar] == 0].sample(int(len(TestList)*0.25)))\n",
    "print(TestList.pivot_table(index = TargetVar, values = 'DRID', aggfunc = 'count'))\n",
    "TrainIns = str(list(TrainList['DRID']))[1:-1]\n",
    "TestIns = str(list(TestList['DRID']))[1:-1]"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD
   "execution_count": null,
=======
   "execution_count": 10,
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "metadata": {},
   "outputs": [],
   "source": [
    "# Training set view\n",
    "icurs.execute(''' \\\n",
    "    CREATE or replace VIEW %s AS \\\n",
    "        SELECT * FROM %s \n",
    "        WHERE DRID in (%s)''' \\\n",
    "    % (TrainTable, TargetTable, TrainIns))\n",
    "# Prediction set\n",
    "icurs.execute(''' \\\n",
    "    CREATE or replace VIEW %s AS \\\n",
    "        SELECT * FROM %s \n",
    "        WHERE DRID in (%s)''' \\\n",
    "    % (TestTable, TargetTable, TestIns))"
   ]
  },
  {
   "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",
<<<<<<< HEAD
   "execution_count": null,
=======
   "execution_count": 11,
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    icurs.execute(\"CREATE MODEL NewEncModel PREDICTING (%s) FROM %s\" % (TargetVar, TrainTable))\n",
    "except:\n",
    "    icurs.execute(\"DROP MODEL NewEncModel\")\n",
    "    icurs.execute(\"CREATE MODEL NewEncModel PREDICTING (%s) FROM %s\" % (TargetVar, TrainTable))"
   ]
  },
  {
   "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",
<<<<<<< HEAD
   "execution_count": null,
   "metadata": {},
   "outputs": [],
=======
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "java.sql.SQLException: [SQLCODE: <-400>:<Fatal error occurred>]\r\n[Location: <ServerLoop>]\r\n[%msg: <ERROR #2800: %ML General Error 'Training failed:': \u0007\u0001error)\u0001{\"message\": \"Deployment limit reached\"}.>]",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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: <-400>:<Fatal error occurred>]\r\n[Location: <ServerLoop>]\r\n[%msg: <ERROR #2800: %ML General Error 'Training failed:': \u0007\u0001error)\u0001{\"message\": \"Deployment limit reached\"}.>]",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m~/Initializations/Conns.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;31m#icurs.execute(\"TRAIN MODEL NewEncModel as NewEncModel_Auto\")\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0micurs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"set ml configuration DRCfg\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m \u001b[0micurs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"TRAIN MODEL NewEncModel as NewEncModel_DR\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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: <-400>:<Fatal error occurred>]\r\n[Location: <ServerLoop>]\r\n[%msg: <ERROR #2800: %ML General Error 'Training failed:': \u0007\u0001error)\u0001{\"message\": \"Deployment limit reached\"}.>]"
     ]
    }
   ],
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "source": [
    "icurs.execute(\"set ml configuration %AutoML\")\n",
    "icurs.execute(\"TRAIN MODEL NewEncModel as NewEncModel_Auto\")\n",
    "icurs.execute(\"set ml configuration DRCfg\")\n",
    "icurs.execute(\"TRAIN MODEL NewEncModel as NewEncModel_DR\")"
   ]
  },
  {
   "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",
<<<<<<< HEAD
   "execution_count": null,
   "metadata": {},
   "outputs": [],
=======
   "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>NoShowModel</td>\n",
       "      <td>NoShowModel_t2</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2020-10-26 15:57:18.731000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:Random Forest, Package:sklearn, Prob...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NewEncModelTD</td>\n",
       "      <td>NewEncModelAutoML</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2020-10-27 01:46:39.459000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:Logistic Regression, Package:sklearn...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      MODEL_NAME TRAINED_MODEL_NAME PROVIDER           TRAINED_TIMESTAMP  \\\n",
       "0    NoShowModel     NoShowModel_t2   AutoML  2020-10-26 15:57:18.731000   \n",
       "1  NewEncModelTD  NewEncModelAutoML   AutoML  2020-10-27 01:46:39.459000   \n",
       "\n",
       "       MODEL_TYPE                                         MODEL_INFO  \n",
       "0  classification  ModelType:Random Forest, Package:sklearn, Prob...  \n",
       "1  classification  ModelType:Logistic Regression, Package:sklearn...  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   "source": [
    "pd.read_sql(\"SELECT * FROM INFORMATION_SCHEMA.ML_TRAINED_MODELS\", iconn)"
   ]
  },
  {
   "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",
<<<<<<< HEAD
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "TestSet = pd.read_sql('''\n",
    "    SELECT PREDICT(NewEncModel use NewEncModel_Auto) AS PredictedEncs,\n",
    "        case when B90 = 1 then 1 end AS ActualPos,\n",
    "        case when B90 != 1 then 0 end AS ActualNeg\n",
    "    FROM %s''' % (TestTable), iconn)"
=======
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql 'SELECT PREDICT(NewEncModel use NewEncModel_Auto) AS PredictedEncs,                   case when B90 = 1 then 1 end AS ActualPos,                   case when B90 != 1 then 0 end AS ActualNeg                   FROM Data.EDEncsTesting': java.sql.SQLException: [SQLCODE: <-181>:<Model or Trained Model not found>]\r\n[Location: <Prepare>]\r\n[%msg: < Model 'NewEncModel_Auto' not found>]",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.prepareStatement\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.prepareStatement\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.getOrCreatePossiblyShardedIRISPreparedStatement\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.<init>\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.prepare\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.prepareInternal\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-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/lib64/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1680\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-> 1681\u001b[0;31m             \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\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   1682\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    530\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_close_last\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--> 531\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mprepareStatement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0moperation\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    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[0;31mjava.sql.SQLException\u001b[0m: java.sql.SQLException: [SQLCODE: <-181>:<Model or Trained Model not found>]\r\n[Location: <Prepare>]\r\n[%msg: < Model 'NewEncModel_Auto' not found>]",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m~/Initializations/Conns.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      2\u001b[0m                   \u001b[0mcase\u001b[0m \u001b[0mwhen\u001b[0m \u001b[0mB90\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m1\u001b[0m \u001b[0mthen\u001b[0m \u001b[0;36m1\u001b[0m \u001b[0mend\u001b[0m \u001b[0mAS\u001b[0m \u001b[0mActualPos\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m\\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m                   \u001b[0mcase\u001b[0m \u001b[0mwhen\u001b[0m \u001b[0mB90\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0;36m1\u001b[0m \u001b[0mthen\u001b[0m \u001b[0;36m0\u001b[0m \u001b[0mend\u001b[0m \u001b[0mAS\u001b[0m \u001b[0mActualNeg\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m\\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m                   FROM %s\" % (TestTable), iconn)\n\u001b[0m",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[1;32m    487\u001b[0m             \u001b[0mcoerce_float\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcoerce_float\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    488\u001b[0m             \u001b[0mparse_dates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparse_dates\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 489\u001b[0;31m             \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\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    490\u001b[0m         )\n\u001b[1;32m    491\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_query\u001b[0;34m(self, sql, index_col, coerce_float, params, parse_dates, chunksize)\u001b[0m\n\u001b[1;32m   1725\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1726\u001b[0m         \u001b[0margs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1727\u001b[0;31m         \u001b[0mcursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\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   1728\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mcol_desc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mcol_desc\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdescription\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1729\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1691\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1692\u001b[0m             \u001b[0mex\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mDatabaseError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"Execution failed on sql '{args[0]}': {exc}\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1693\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mex\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1694\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1695\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mstaticmethod\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDatabaseError\u001b[0m: Execution failed on sql 'SELECT PREDICT(NewEncModel use NewEncModel_Auto) AS PredictedEncs,                   case when B90 = 1 then 1 end AS ActualPos,                   case when B90 != 1 then 0 end AS ActualNeg                   FROM Data.EDEncsTesting': java.sql.SQLException: [SQLCODE: <-181>:<Model or Trained Model not found>]\r\n[Location: <Prepare>]\r\n[%msg: < Model 'NewEncModel_Auto' not found>]"
     ]
    }
   ],
   "source": [
    "TestSet = pd.read_sql(\"SELECT PREDICT(NewEncModel use NewEncModel_Auto) AS PredictedEncs, \\\n",
    "                  case when B90 = 1 then 1 end AS ActualPos, \\\n",
    "                  case when B90 != 1 then 0 end AS ActualNeg \\\n",
    "                  FROM %s\" % (TestTable), iconn)"
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pd.pivot_table(TestSet, index = 'PredictedEncs', values = ['ActualPos', 'ActualNeg'], aggfunc = 'count'))\n",
    "print('Accuracy: '+str(round((len(TestSet[(TestSet['PredictedEncs'] == TestSet['ActualPos']) \\\n",
    "            | (TestSet['PredictedEncs'] == TestSet['ActualNeg'])])/len(TestSet))*100))+'%')\n",
    "print('Misclassification Rate: '+str(round((len(TestSet[(TestSet['PredictedEncs'] != TestSet['ActualPos']) \\\n",
    "            & (TestSet['PredictedEncs'] != TestSet['ActualNeg'])])/len(TestSet))*100))+'%')\n",
    "print('%FP: '+str(round((len(TestSet[(TestSet['PredictedEncs'] == 1) & (TestSet['ActualNeg'] == 0)])/ \\\n",
    "            len(TestSet[TestSet['ActualNeg'] == 0]))*100))+'%')\n",
    "print('%FN: '+str(round((len(TestSet[(TestSet['PredictedEncs'] == 0) & (TestSet['ActualPos'] == 1)])/ \\\n",
    "            len(TestSet[TestSet['ActualPos'] == 1]))*100))+'%')"
   ]
  },
  {
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
<<<<<<< HEAD
    "icurs.execute(\"VALIDATE model NewEncModel use NewEncModel_Auto FROM Data.EDEncsTesting\")\n",
=======
    "#icurs.execute(\"VALIDATE model NewEncModel FROM Data.EDEncsTesting\")\n",
>>>>>>> 730f3d3b116a644bb0466c140bb03f80ba73e6c9
    "#df5 = pd.read_sql(\"SELECT * FROM INFORMATION_SCHEMA.ML_VALIDATION_METRICS\", iconn)\n",
    "#df5\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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}