ml_classifier_lending_club.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inplace Machine Learning in Snowflake \n",
    "## Demonstrated using Lending Club Data\n",
    "\n",
    "Author: Avadhoot Agasti\n",
    "\n",
    "Snowflake has phenomenal support for executing Python, Spark, Java etc code base. The machine learning models can be trained and executed using these languages. However, day to day execution of the models require separate environment to run the models. \n",
    "\n",
    "This notebook showcases a mechanism to push the model inside Snowflake UDF so that we can directly run the model as part of SQL. This is very beneficial since we don't need to maintain any other environment. \n",
    "\n",
    "I have used Lending Club data to demonstrate this. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataset and Use Case\n",
    "Lending Club Data\n",
    "\n",
    "The Raw data has 1.95M records and 145 columns. \n",
    "\n",
    "The use case is as below:\n",
    "Every loan has a purpose, like major purchase, small business, vacation etc. In this dataset, there are about 121K records where purpose is not defined (other). The idea is the train a classifier to predict the purpose for these 121K records.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Snowflake Connection and Utility Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from configparser import ConfigParser\n",
    "config = ConfigParser()\n",
    "config.read('config.properties')\n",
    "# get config data\n",
    "account = config.get('DatabaseSection', 'account')\n",
    "user = config.get('DatabaseSection', 'user')\n",
    "password = config.get('DatabaseSection', 'password')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import snowflake.connector\n",
    "from pandas import DataFrame\n",
    "\n",
    "ctx = snowflake.connector.connect(\n",
    "user=user,\n",
    "password=password,\n",
    "account=account\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def execSQL(sql):\n",
    "    ctx.cursor().execute(sql)\n",
    "    \n",
    "def execSQLAndGetDF(sql, max_rows = 1000000):\n",
    "    try:\n",
    "        cur = ctx.cursor()\n",
    "        cs = cur.execute(sql)\n",
    "        rows = cs.fetchmany(max_rows)\n",
    "    finally:\n",
    "        cs.close()\n",
    "    df = DataFrame(rows)\n",
    "    df.columns = [col[0] for col in cur.description]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "execSQL(\"USE DATABASE LENDINGCLUBDB\")\n",
    "execSQL(\"USE SCHEMA RAWSCHEMA\")\n",
    "execSQL(\"USE WAREHOUSE LoadWH\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "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>COUNT(*)</th>\n",
       "      <th>PURPOSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20087</td>\n",
       "      <td>small_business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>43620</td>\n",
       "      <td>major_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1097090</td>\n",
       "      <td>debt_consolidation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35</td>\n",
       "      <td>educational</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>129269</td>\n",
       "      <td>home_improvement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>454328</td>\n",
       "      <td>credit_card</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12356</td>\n",
       "      <td>house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>13902</td>\n",
       "      <td>vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23526</td>\n",
       "      <td>medical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>13255</td>\n",
       "      <td>moving</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1205</td>\n",
       "      <td>renewable_energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20438</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1013</td>\n",
       "      <td>wedding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>121678</td>\n",
       "      <td>other</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    COUNT(*)             PURPOSE\n",
       "0      20087      small_business\n",
       "1      43620      major_purchase\n",
       "2    1097090  debt_consolidation\n",
       "3         35         educational\n",
       "4     129269    home_improvement\n",
       "5     454328         credit_card\n",
       "6      12356               house\n",
       "7      13902            vacation\n",
       "8      23526             medical\n",
       "9      13255              moving\n",
       "10      1205    renewable_energy\n",
       "11     20438                 car\n",
       "12      1013             wedding\n",
       "13    121678               other"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purposeDF = execSQLAndGetDF(\"select count(*), purpose from loan_data group by 2;\")\n",
    "purposeDF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Preperation\n",
    "\n",
    "We use Snowflake SQL to perform data preperation operations. This includes min_max_normalization, one-hot-encoding etc. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"with aggregates as (\\\n",
    "  select \\\n",
    "  min(loan_amnt) as min_loan_amnt, max(loan_amnt) as max_loan_amnt,\\\n",
    "  min(funded_amnt) as min_funded_amnt, max(funded_amnt) as max_funded_amnt,\\\n",
    "  min(annual_inc) as min_annual_inc, max(annual_inc) as max_annual_inc \\\n",
    "  from loan_data) \\\n",
    "\\\n",
    "select \\\n",
    "(loan_amnt - min_loan_amnt)/ (max_loan_amnt - min_loan_amnt) as normalized_loan_amnt, \\\n",
    "(funded_amnt - min_funded_amnt)/ (max_funded_amnt - min_funded_amnt) as normalized_funded_amnt, \\\n",
    "(annual_inc - min_annual_inc)/ (max_annual_inc - min_annual_inc) as normalized_annual_inc, \\\n",
    "int_rate, installment, \\\n",
    "\\\n",
    "case when purpose='small_business' then 1 \\\n",
    "when purpose='major_purchase' then 2 \\\n",
    "when purpose='debt_consolidation' then 3 \\\n",
    "when purpose='educational' then 4 \\\n",
    "when purpose='credit_card' then 5 \\\n",
    "when purpose='house' then 6 \\\n",
    "when purpose='home_improvement' then 7 \\\n",
    "when purpose='moving' then 8 \\\n",
    "when purpose='medical' then 9 \\\n",
    "when purpose='vacation' then 10 \\\n",
    "when purpose='car' then 11 \\\n",
    "when purpose='renewable_energy' then 12 \\\n",
    "end as purpose, \\\n",
    "\\\n",
    "iff(loan_status='Does not meet the credit policy. Status:Fully Paid',1,0) as status_dcp_fp,\\\n",
    "iff(loan_status='Does not meet the credit policy. Status:Charged Off',1,0) as status_dcp_coff,\\\n",
    "iff(loan_status='Late (16-30 days)',1,0) as status_late_30,\\\n",
    "iff(loan_status='Charged Off',1,0) as status_coff,\\\n",
    "iff(loan_status='In Grace Period',1,0) as status_igp,\\\n",
    "iff(loan_status='Current',1,0) as status_current,\\\n",
    "iff(loan_status='Late (31-120 days)',1,0) as status_late_120,\\\n",
    "iff(loan_status='Fully Paid',1,0) as status_fp,\\\n",
    "iff(loan_status='Default',1,0) as status_def,\\\n",
    "\\\n",
    "iff(term='36 months',1,0) as term_med,\\\n",
    "iff(term='60 months',1,0) as term_long,\\\n",
    "\\\n",
    "iff(grade='A',1,0) as grade_A, \\\n",
    "iff(grade='B',1,0) as grade_B, \\\n",
    "iff(grade='C',1,0) as grade_C, \\\n",
    "iff(grade='D',1,0) as grade_D, \\\n",
    "iff(grade='E',1,0) as grade_E, \\\n",
    "iff(grade='F',1,0) as grade_F, \\\n",
    "iff(grade='G',1,0) as grade_G,\\\n",
    "\\\n",
    "iff(home_ownership='OWN',1,0) as ho_own,\\\n",
    "iff(home_ownership='MORTGAGE',1,0) as ho_mtg,\\\n",
    "iff(home_ownership='NONE',1,0) as ho_none,\\\n",
    "iff(home_ownership='RENT',1,0) as ho_rent,\\\n",
    "iff(home_ownership='ANY',1,0) as ho_any,\\\n",
    "iff(home_ownership='OTHER',1,0) as ho_other \\\n",
    "\\\n",
    "from aggregates, loan_data \\\n",
    "where purpose != 'other' \\\n",
    "\"\n",
    "\n",
    "sql = sql + \" limit 100;\"\n",
    "\n",
    "df = execSQLAndGetDF(sql)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Separate label\n",
    "train_labels = df[[\"PURPOSE\"]].copy()\n",
    "train_data = df.drop([\"PURPOSE\"], axis=1) # drop labels for training set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "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>NORMALIZED_LOAN_AMNT</th>\n",
       "      <th>NORMALIZED_FUNDED_AMNT</th>\n",
       "      <th>NORMALIZED_ANNUAL_INC</th>\n",
       "      <th>INT_RATE</th>\n",
       "      <th>INSTALLMENT</th>\n",
       "      <th>STATUS_DCP_FP</th>\n",
       "      <th>STATUS_DCP_COFF</th>\n",
       "      <th>STATUS_LATE_30</th>\n",
       "      <th>STATUS_COFF</th>\n",
       "      <th>STATUS_IGP</th>\n",
       "      <th>...</th>\n",
       "      <th>GRADE_D</th>\n",
       "      <th>GRADE_E</th>\n",
       "      <th>GRADE_F</th>\n",
       "      <th>GRADE_G</th>\n",
       "      <th>HO_OWN</th>\n",
       "      <th>HO_MTG</th>\n",
       "      <th>HO_NONE</th>\n",
       "      <th>HO_RENT</th>\n",
       "      <th>HO_ANY</th>\n",
       "      <th>HO_OTHER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.358974</td>\n",
       "      <td>0.358974</td>\n",
       "      <td>0.000710</td>\n",
       "      <td>11.49</td>\n",
       "      <td>494.57</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.128205</td>\n",
       "      <td>0.128205</td>\n",
       "      <td>0.000646</td>\n",
       "      <td>9.49</td>\n",
       "      <td>192.17</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.179487</td>\n",
       "      <td>0.179487</td>\n",
       "      <td>0.000601</td>\n",
       "      <td>9.49</td>\n",
       "      <td>256.23</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.589744</td>\n",
       "      <td>0.589744</td>\n",
       "      <td>0.001055</td>\n",
       "      <td>8.59</td>\n",
       "      <td>493.44</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.064103</td>\n",
       "      <td>0.064103</td>\n",
       "      <td>0.000483</td>\n",
       "      <td>13.49</td>\n",
       "      <td>118.76</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>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  NORMALIZED_LOAN_AMNT NORMALIZED_FUNDED_AMNT NORMALIZED_ANNUAL_INC INT_RATE  \\\n",
       "0             0.358974               0.358974              0.000710    11.49   \n",
       "1             0.128205               0.128205              0.000646     9.49   \n",
       "2             0.179487               0.179487              0.000601     9.49   \n",
       "3             0.589744               0.589744              0.001055     8.59   \n",
       "4             0.064103               0.064103              0.000483    13.49   \n",
       "\n",
       "  INSTALLMENT  STATUS_DCP_FP  STATUS_DCP_COFF  STATUS_LATE_30  STATUS_COFF  \\\n",
       "0      494.57              0                0               0            1   \n",
       "1      192.17              0                0               0            0   \n",
       "2      256.23              0                0               0            1   \n",
       "3      493.44              0                0               0            0   \n",
       "4      118.76              0                0               0            0   \n",
       "\n",
       "   STATUS_IGP    ...     GRADE_D  GRADE_E  GRADE_F  GRADE_G  HO_OWN  HO_MTG  \\\n",
       "0           0    ...           0        0        0        0       0       0   \n",
       "1           0    ...           0        0        0        0       0       1   \n",
       "2           0    ...           0        0        0        0       0       1   \n",
       "3           0    ...           0        0        0        0       0       1   \n",
       "4           0    ...           0        0        0        0       0       1   \n",
       "\n",
       "   HO_NONE  HO_RENT  HO_ANY  HO_OTHER  \n",
       "0        0        1       0         0  \n",
       "1        0        0       0         0  \n",
       "2        0        0       0         0  \n",
       "3        0        0       0         0  \n",
       "4        0        0       0         0  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_data.iloc[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "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>PURPOSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PURPOSE\n",
       "0        3\n",
       "1        5\n",
       "2        3\n",
       "3        3\n",
       "4        3"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_labels.iloc[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Model Training in Scikit Learn\n",
    "\n",
    "The data scientists like to use SKLearn to train the models. Feature engineering, model creation, scoring etc are complicated activities and we want to continue leverage the best tool to do this. So we won't disturb that process. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import cross_val_score\n",
    "from sklearn.model_selection import KFold\n",
    "\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "from sklearn.discriminant_analysis import LinearDiscriminantAnalysis\n",
    "from sklearn.naive_bayes import GaussianNB\n",
    "from sklearn.svm import SVC\n",
    "from sklearn.ensemble import RandomForestClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/avadhoot_agasti/anaconda3/lib/python3.7/site-packages/sklearn/model_selection/_split.py:605: Warning: The least populated class in y has only 1 members, which is too few. The minimum number of members in any class cannot be less than n_splits=10.\n",
      "  % (min_groups, self.n_splits)), Warning)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([0.4       , 0.5       , 0.41666667, 0.6       , 0.5       ,\n",
       "       0.77777778, 0.44444444, 0.25      , 0.25      , 0.42857143])"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clf_dtree = DecisionTreeClassifier()\n",
    "clf_dtree.fit(train_data, train_labels)\n",
    "cross_val_score(clf_dtree, train_data, train_labels, cv=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The accuracy is not that great but thats not the purpose of this notebook. We can spend more time improving the accuracy. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "predict_df = clf_dtree.predict(train_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 3,  5,  3,  3,  3,  3,  3,  3,  3,  3,  5,  3,  3,  3,  3,  3,  3,\n",
       "        3,  2,  3,  6,  5,  3,  3,  5,  3,  3,  3,  7,  3,  7,  5,  5,  3,\n",
       "        3,  3,  3,  3,  7,  3,  3,  2, 10,  3,  3,  5,  3,  5,  3,  3,  3,\n",
       "        3,  7,  7,  3,  3,  3, 11,  7,  3,  7, 11,  3,  3,  5,  3,  3,  3,\n",
       "        2,  3,  3,  3,  3,  3,  3, 11,  3,  3,  5,  7,  3,  3,  3,  3,  3,\n",
       "        3,  5,  3,  7,  5,  3,  5,  3,  3,  5,  3,  3,  5,  3,  9])"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predict_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Porting the model to Snowflake\n",
    "We use multiple steps to port the model to Snowflake\n",
    "\n",
    "\n",
    "Step 1: Create equivalent model in JavaScript. We use porter project to achieve this. \n",
    "https://github.com/nok/sklearn-porter/blob/release/1.0.0/examples/basics/index.pct.ipynb\n",
    "\n",
    "\n",
    "Step 2: Decorate the generated Javascript to create Snowflake UDF from it\n",
    "\n",
    "\n",
    "Step 3: Start using UDF to predict the class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn_porter import Porter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "var DecisionTreeClassifier = function() {\n",
      "\n",
      "    var findMax = function(nums) {\n",
      "        var index = 0;\n",
      "        for (var i = 0; i < nums.length; i++) {\n",
      "            index = nums[i] > nums[index] ? i : index;\n",
      "        }\n",
      "        return index;\n",
      "    };\n",
      "\n",
      "    this.predict = function(features) {\n",
      "        var classes = new Array(8);\n",
      "            \n",
      "        if (features[0] <= 0.053846001625061035) {\n",
      "            if (features[8] <= 0.5) {\n",
      "                classes[0] = 0; \n",
      "                classes[1] = 0; \n",
      "                classes[2] = 0; \n",
      "                classes[3] = 0; \n",
      "                classes[4] = 0; \n",
      "                classes[5] = 0; \n",
      "                classes[6] = 0; \n",
      "                classes[7] = 3; \n",
      "            } else {\n",
      "                classes[0] = 1; \n",
      "                classes[1] = 0; \n",
      "                classes[2] = 0; \n",
      "                classes[3] = 0; \n",
      "                classes[4] = 0; \n",
      "                classes[5] = 0; \n",
      "                classes[6] = 0; \n",
      "                classes[7] = 0; \n",
      "            }\n",
      "        } else {\n",
      "            if (features[24] <= 0.5) {\n",
      "                if (features[3] <= 6.654999732971191) {\n",
      "                    classes[0] = 0; \n",
      "                    classes[1] = 0; \n",
      "                    classes[2] = 1; \n",
      "                    classes[3] = 0; \n",
      "                    classes[4] = 0; \n",
      "                    classes[5] = 0; \n",
      "                    classes[6] = 0; \n",
      "                    classes[7] = 0; \n",
      "                } else {\n",
      "                    if (features[3] <= 24.639999389648438) {\n",
      "                        if (features[3] <= 13.139999389648438) {\n",
      "                            if (features[3] <= 10.739999771118164) {\n",
      "                                classes[0] = 0; \n",
      "                                classes[1] = 7; \n",
      "                                classes[2] = 0; \n",
      "                                classes[3] = 0; \n",
      "                                classes[4] = 0; \n",
      "                                classes[5] = 0; \n",
      "                                classes[6] = 0; \n",
      "                                classes[7] = 0; \n",
      "                            } else {\n",
      "                                if (features[0] <= 0.3205125033855438) {\n",
      "                                    if (features[12] <= 0.5) {\n",
      "                                        classes[0] = 0; \n",
      "                                        classes[1] = 2; \n",
      "                                        classes[2] = 0; \n",
      "                                        classes[3] = 0; \n",
      "                                        classes[4] = 0; \n",
      "                                        classes[5] = 0; \n",
      "                                        classes[6] = 0; \n",
      "                                        classes[7] = 0; \n",
      "                                    } else {\n",
      "                                        if (features[0] <= 0.21794849634170532) {\n",
      "                                            if (features[2] <= 0.0008009999874047935) {\n",
      "                                                classes[0] = 0; \n",
      "                                                classes[1] = 2; \n",
      "                                                classes[2] = 0; \n",
      "                                                classes[3] = 0; \n",
      "                                                classes[4] = 0; \n",
      "                                                classes[5] = 0; \n",
      "                                                classes[6] = 0; \n",
      "                                                classes[7] = 0; \n",
      "                                            } else {\n",
      "                                                classes[0] = 0; \n",
      "                                                classes[1] = 0; \n",
      "                                                classes[2] = 1; \n",
      "                                                classes[3] = 0; \n",
      "                                                classes[4] = 0; \n",
      "                                                classes[5] = 0; \n",
      "                                                classes[6] = 0; \n",
      "                                                classes[7] = 0; \n",
      "                                            }\n",
      "                                        } else {\n",
      "                                            classes[0] = 0; \n",
      "                                            classes[1] = 0; \n",
      "                                            classes[2] = 2; \n",
      "                                            classes[3] = 0; \n",
      "                                            classes[4] = 0; \n",
      "                                            classes[5] = 0; \n",
      "                                            classes[6] = 0; \n",
      "                                            classes[7] = 0; \n",
      "                                        }\n",
      "                                    }\n",
      "                                } else {\n",
      "                                    if (features[2] <= 0.00035099999513477087) {\n",
      "                                        classes[0] = 1; \n",
      "                                        classes[1] = 0; \n",
      "                                        classes[2] = 0; \n",
      "                                        classes[3] = 0; \n",
      "                                        classes[4] = 0; \n",
      "                                        classes[5] = 0; \n",
      "                                        classes[6] = 0; \n",
      "                                        classes[7] = 0; \n",
      "                                    } else {\n",
      "                                        classes[0] = 0; \n",
      "                                        classes[1] = 5; \n",
      "                                        classes[2] = 0; \n",
      "                                        classes[3] = 0; \n",
      "                                        classes[4] = 0; \n",
      "                                        classes[5] = 0; \n",
      "                                        classes[6] = 0; \n",
      "                                        classes[7] = 0; \n",
      "                                    }\n",
      "                                }\n",
      "                            }\n",
      "                        } else {\n",
      "                            if (features[3] <= 18.489999771118164) {\n",
      "                                classes[0] = 0; \n",
      "                                classes[1] = 18; \n",
      "                                classes[2] = 0; \n",
      "                                classes[3] = 0; \n",
      "                                classes[4] = 0; \n",
      "                                classes[5] = 0; \n",
      "                                classes[6] = 0; \n",
      "                                classes[7] = 0; \n",
      "                            } else {\n",
      "                                if (features[26] <= 0.5) {\n",
      "                                    classes[0] = 1; \n",
      "                                    classes[1] = 0; \n",
      "                                    classes[2] = 0; \n",
      "                                    classes[3] = 0; \n",
      "                                    classes[4] = 0; \n",
      "                                    classes[5] = 0; \n",
      "                                    classes[6] = 0; \n",
      "                                    classes[7] = 0; \n",
      "                                } else {\n",
      "                                    classes[0] = 0; \n",
      "                                    classes[1] = 5; \n",
      "                                    classes[2] = 0; \n",
      "                                    classes[3] = 0; \n",
      "                                    classes[4] = 0; \n",
      "                                    classes[5] = 0; \n",
      "                                    classes[6] = 0; \n",
      "                                    classes[7] = 0; \n",
      "                                }\n",
      "                            }\n",
      "                        }\n",
      "                    } else {\n",
      "                        classes[0] = 0; \n",
      "                        classes[1] = 0; \n",
      "                        classes[2] = 1; \n",
      "                        classes[3] = 0; \n",
      "                        classes[4] = 0; \n",
      "                        classes[5] = 0; \n",
      "                        classes[6] = 0; \n",
      "                        classes[7] = 0; \n",
      "                    }\n",
      "                }\n",
      "            } else {\n",
      "                if (features[3] <= 10.739999771118164) {\n",
      "                    if (features[4] <= 531.0349731445312) {\n",
      "                        if (features[2] <= 0.0013144999975338578) {\n",
      "                            if (features[2] <= 0.0003029999788850546) {\n",
      "                                classes[0] = 0; \n",
      "                                classes[1] = 0; \n",
      "                                classes[2] = 0; \n",
      "                                classes[3] = 0; \n",
      "                                classes[4] = 1; \n",
      "                                classes[5] = 0; \n",
      "                                classes[6] = 0; \n",
      "                                classes[7] = 0; \n",
      "                            } else {\n",
      "                                if (features[10] <= 0.5) {\n",
      "                                    if (features[2] <= 0.0010875000152736902) {\n",
      "                                        classes[0] = 0; \n",
      "                                        classes[1] = 3; \n",
      "                                        classes[2] = 0; \n",
      "                                        classes[3] = 0; \n",
      "                                        classes[4] = 0; \n",
      "                                        classes[5] = 0; \n",
      "                                        classes[6] = 0; \n",
      "                                        classes[7] = 0; \n",
      "                                    } else {\n",
      "                                        if (features[1] <= 0.19871799647808075) {\n",
      "                                            classes[0] = 0; \n",
      "                                            classes[1] = 1; \n",
      "                                            classes[2] = 0; \n",
      "                                            classes[3] = 0; \n",
      "                                            classes[4] = 0; \n",
      "                                            classes[5] = 0; \n",
      "                                            classes[6] = 0; \n",
      "                                            classes[7] = 0; \n",
      "                                        } else {\n",
      "                                            classes[0] = 0; \n",
      "                                            classes[1] = 0; \n",
      "                                            classes[2] = 1; \n",
      "                                            classes[3] = 0; \n",
      "                                            classes[4] = 0; \n",
      "                                            classes[5] = 0; \n",
      "                                            classes[6] = 0; \n",
      "                                            classes[7] = 0; \n",
      "                                        }\n",
      "                                    }\n",
      "                                } else {\n",
      "                                    if (features[0] <= 0.3846150040626526) {\n",
      "                                        if (features[2] <= 0.0006004999740980566) {\n",
      "                                            if (features[1] <= 0.08878199756145477) {\n",
      "                                                classes[0] = 0; \n",
      "                                                classes[1] = 0; \n",
      "                                                classes[2] = 1; \n",
      "                                                classes[3] = 0; \n",
      "                                                classes[4] = 0; \n",
      "                                                classes[5] = 0; \n",
      "                                                classes[6] = 0; \n",
      "                                                classes[7] = 0; \n",
      "                                            } else {\n",
      "                                                classes[0] = 0; \n",
      "                                                classes[1] = 2; \n",
      "                                                classes[2] = 0; \n",
      "                                                classes[3] = 0; \n",
      "                                                classes[4] = 0; \n",
      "                                                classes[5] = 0; \n",
      "                                                classes[6] = 0; \n",
      "                                                classes[7] = 0; \n",
      "                                            }\n",
      "                                        } else {\n",
      "                                            classes[0] = 0; \n",
      "                                            classes[1] = 0; \n",
      "                                            classes[2] = 2; \n",
      "                                            classes[3] = 0; \n",
      "                                            classes[4] = 0; \n",
      "                                            classes[5] = 0; \n",
      "                                            classes[6] = 0; \n",
      "                                            classes[7] = 0; \n",
      "                                        }\n",
      "                                    } else {\n",
      "                                        classes[0] = 0; \n",
      "                                        classes[1] = 0; \n",
      "                                        classes[2] = 0; \n",
      "                                        classes[3] = 0; \n",
      "                                        classes[4] = 1; \n",
      "                                        classes[5] = 0; \n",
      "                                        classes[6] = 0; \n",
      "                                        classes[7] = 0; \n",
      "                                    }\n",
      "                                }\n",
      "                            }\n",
      "                        } else {\n",
      "                            if (features[1] <= 0.4102564752101898) {\n",
      "                                classes[0] = 0; \n",
      "                                classes[1] = 0; \n",
      "                                classes[2] = 0; \n",
      "                                classes[3] = 0; \n",
      "                                classes[4] = 0; \n",
      "                                classes[5] = 0; \n",
      "                                classes[6] = 1; \n",
      "                                classes[7] = 0; \n",
      "                            } else {\n",
      "                                classes[0] = 0; \n",
      "                                classes[1] = 0; \n",
      "                                classes[2] = 0; \n",
      "                                classes[3] = 0; \n",
      "                                classes[4] = 1; \n",
      "                                classes[5] = 0; \n",
      "                                classes[6] = 0; \n",
      "                                classes[7] = 0; \n",
      "                            }\n",
      "                        }\n",
      "                    } else {\n",
      "                        classes[0] = 0; \n",
      "                        classes[1] = 0; \n",
      "                        classes[2] = 3; \n",
      "                        classes[3] = 0; \n",
      "                        classes[4] = 0; \n",
      "                        classes[5] = 0; \n",
      "                        classes[6] = 0; \n",
      "                        classes[7] = 0; \n",
      "                    }\n",
      "                } else {\n",
      "                    if (features[0] <= 0.25) {\n",
      "                        classes[0] = 0; \n",
      "                        classes[1] = 8; \n",
      "                        classes[2] = 0; \n",
      "                        classes[3] = 0; \n",
      "                        classes[4] = 0; \n",
      "                        classes[5] = 0; \n",
      "                        classes[6] = 0; \n",
      "                        classes[7] = 0; \n",
      "                    } else {\n",
      "                        if (features[2] <= 0.0007089999853633344) {\n",
      "                            if (features[4] <= 593.9199829101562) {\n",
      "                                if (features[12] <= 0.5) {\n",
      "                                    classes[0] = 0; \n",
      "                                    classes[1] = 0; \n",
      "                                    classes[2] = 0; \n",
      "                                    classes[3] = 0; \n",
      "                                    classes[4] = 5; \n",
      "                                    classes[5] = 0; \n",
      "                                    classes[6] = 0; \n",
      "                                    classes[7] = 0; \n",
      "                                } else {\n",
      "                                    classes[0] = 0; \n",
      "                                    classes[1] = 0; \n",
      "                                    classes[2] = 1; \n",
      "                                    classes[3] = 0; \n",
      "                                    classes[4] = 0; \n",
      "                                    classes[5] = 0; \n",
      "                                    classes[6] = 0; \n",
      "                                    classes[7] = 0; \n",
      "                                }\n",
      "                            } else {\n",
      "                                if (features[18] <= 0.5) {\n",
      "                                    classes[0] = 0; \n",
      "                                    classes[1] = 3; \n",
      "                                    classes[2] = 0; \n",
      "                                    classes[3] = 0; \n",
      "                                    classes[4] = 0; \n",
      "                                    classes[5] = 0; \n",
      "                                    classes[6] = 0; \n",
      "                                    classes[7] = 0; \n",
      "                                } else {\n",
      "                                    classes[0] = 0; \n",
      "                                    classes[1] = 0; \n",
      "                                    classes[2] = 1; \n",
      "                                    classes[3] = 0; \n",
      "                                    classes[4] = 0; \n",
      "                                    classes[5] = 0; \n",
      "                                    classes[6] = 0; \n",
      "                                    classes[7] = 0; \n",
      "                                }\n",
      "                            }\n",
      "                        } else {\n",
      "                            if (features[3] <= 16.239999771118164) {\n",
      "                                if (features[2] <= 0.0007645000005140901) {\n",
      "                                    if (features[2] <= 0.0007190000033006072) {\n",
      "                                        classes[0] = 0; \n",
      "                                        classes[1] = 1; \n",
      "                                        classes[2] = 0; \n",
      "                                        classes[3] = 0; \n",
      "                                        classes[4] = 0; \n",
      "                                        classes[5] = 0; \n",
      "                                        classes[6] = 0; \n",
      "                                        classes[7] = 0; \n",
      "                                    } else {\n",
      "                                        classes[0] = 0; \n",
      "                                        classes[1] = 0; \n",
      "                                        classes[2] = 0; \n",
      "                                        classes[3] = 1; \n",
      "                                        classes[4] = 0; \n",
      "                                        classes[5] = 0; \n",
      "                                        classes[6] = 0; \n",
      "                                        classes[7] = 0; \n",
      "                                    }\n",
      "                                } else {\n",
      "                                    classes[0] = 0; \n",
      "                                    classes[1] = 8; \n",
      "                                    classes[2] = 0; \n",
      "                                    classes[3] = 0; \n",
      "                                    classes[4] = 0; \n",
      "                                    classes[5] = 0; \n",
      "                                    classes[6] = 0; \n",
      "                                    classes[7] = 0; \n",
      "                                }\n",
      "                            } else {\n",
      "                                if (features[1] <= 0.7897435426712036) {\n",
      "                                    if (features[20] <= 0.5) {\n",
      "                                        classes[0] = 0; \n",
      "                                        classes[1] = 0; \n",
      "                                        classes[2] = 0; \n",
      "                                        classes[3] = 0; \n",
      "                                        classes[4] = 0; \n",
      "                                        classes[5] = 1; \n",
      "                                        classes[6] = 0; \n",
      "                                        classes[7] = 0; \n",
      "                                    } else {\n",
      "                                        if (features[1] <= 0.6692309975624084) {\n",
      "                                            classes[0] = 0; \n",
      "                                            classes[1] = 0; \n",
      "                                            classes[2] = 0; \n",
      "                                            classes[3] = 0; \n",
      "                                            classes[4] = 1; \n",
      "                                            classes[5] = 0; \n",
      "                                            classes[6] = 0; \n",
      "                                            classes[7] = 0; \n",
      "                                        } else {\n",
      "                                            classes[0] = 0; \n",
      "                                            classes[1] = 0; \n",
      "                                            classes[2] = 1; \n",
      "                                            classes[3] = 0; \n",
      "                                            classes[4] = 0; \n",
      "                                            classes[5] = 0; \n",
      "                                            classes[6] = 0; \n",
      "                                            classes[7] = 0; \n",
      "                                        }\n",
      "                                    }\n",
      "                                } else {\n",
      "                                    classes[0] = 0; \n",
      "                                    classes[1] = 2; \n",
      "                                    classes[2] = 0; \n",
      "                                    classes[3] = 0; \n",
      "                                    classes[4] = 0; \n",
      "                                    classes[5] = 0; \n",
      "                                    classes[6] = 0; \n",
      "                                    classes[7] = 0; \n",
      "                                }\n",
      "                            }\n",
      "                        }\n",
      "                    }\n",
      "                }\n",
      "            }\n",
      "        }\n",
      "    \n",
      "        return findMax(classes);\n",
      "    };\n",
      "\n",
      "};\n",
      "\n",
      "if (typeof process !== 'undefined' && typeof process.argv !== 'undefined') {\n",
      "    if (process.argv.length - 2 === 29) {\n",
      "\n",
      "        // Features:\n",
      "        var features = process.argv.slice(2);\n",
      "\n",
      "        // Prediction:\n",
      "        var clf = new DecisionTreeClassifier();\n",
      "        var prediction = clf.predict(features);\n",
      "        console.log(prediction);\n",
      "\n",
      "    }\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "porter = Porter(clf_dtree, language='JS')\n",
    "ml_class = porter.export(embed_data=True, template='attached')\n",
    "print(ml_class)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Decorate the generated code to create UDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "udf_name = \"lending_club_purpose_classifier\"\n",
    "udf_header = \"CREATE OR REPLACE FUNCTION \" + udf_name + \" (INPUT_ARRAY VARCHAR) \\\n",
    "              returns varchar \\\n",
    "              language javascript \\\n",
    "              as $$ \"\n",
    "\n",
    "udf_footer_js = \" \\\n",
    "                else \\\n",
    "                { \\\n",
    "                        var features = INPUT_ARRAY.split(',').map(function(item) { \\\n",
    "                                                return parseFloat(item, 10); \\\n",
    "                                                }); \\\n",
    "                        var clf = new DecisionTreeClassifier(); \\\n",
    "                        var prediction = clf.predict(features); \\\n",
    "                }\"\n",
    "\n",
    "udf_footer =  \"$$;\"\n",
    "#udf = udf_header + ml_class + udf_footer;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = open('udf_code.js', 'w+')\n",
    "#ml_class + udf_footer_js\n",
    "f.write(ml_class+udf_footer_js)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requesting mini-me of udf_code.js. . .\n",
      "Minification complete. See udf_code.min.js\n"
     ]
    }
   ],
   "source": [
    "js_file = 'udf_code.js'\n",
    "with open(js_file, 'r') as c:\n",
    "    js = c.read()\n",
    "\n",
    "# Pack it, ship it    \n",
    "payload = {'input': js}\n",
    "url = 'https://javascript-minifier.com/raw'\n",
    "print(\"Requesting mini-me of {}. . .\".format(c.name))\n",
    "r = requests.post(url, payload)\n",
    "\n",
    "# Write out minified version\n",
    "minified = js_file.rstrip('.js')+'.min.js'\n",
    "with open(minified, 'w') as m:\n",
    "    m.write(r.text)\n",
    "\n",
    "print(\"Minification complete. See {}\".format(m.name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "udf_code.min.js\n"
     ]
    }
   ],
   "source": [
    "print(minified)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Small Change in the Generated Code\n",
    "At this step, I made a small change in the generated code to add following line \n",
    "\n",
    "return (predictions);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(minified, 'r') as c:\n",
    "    udf_code_min = c.read()\n",
    "\n",
    "udf = udf_header + udf_code_min + udf_footer;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "execSQL(udf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Predict using UDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "predict_sql = \"with aggregates as (\\\n",
    "  select \\\n",
    "  min(loan_amnt) as min_loan_amnt, max(loan_amnt) as max_loan_amnt,\\\n",
    "  min(funded_amnt) as min_funded_amnt, max(funded_amnt) as max_funded_amnt,\\\n",
    "  min(annual_inc) as min_annual_inc, max(annual_inc) as max_annual_inc \\\n",
    "  from loan_data) \\\n",
    "\\\n",
    "select \\\n",
    "lending_club_purpose_classifier( \\\n",
    "cast((loan_amnt - min_loan_amnt)/ (max_loan_amnt - min_loan_amnt) as string) || ',' || \\\n",
    "cast((funded_amnt - min_funded_amnt)/ (max_funded_amnt - min_funded_amnt) as string) || ',' || \\\n",
    "cast((annual_inc - min_annual_inc)/ (max_annual_inc - min_annual_inc) as string) || ',' || \\\n",
    "cast(int_rate as string) || ',' || \\\n",
    "cast(installment as string) || ',' || \\\n",
    "iff(loan_status='Does not meet the credit policy. Status:Fully Paid','1','0') || ',' || \\\n",
    "iff(loan_status='Does not meet the credit policy. Status:Charged Off','1','0') || ',' || \\\n",
    "iff(loan_status='Late (16-30 days)','1','0') || ',' || \\\n",
    "iff(loan_status='Charged Off','1','0') || ',' || \\\n",
    "iff(loan_status='In Grace Period','1','0') || ',' || \\\n",
    "iff(loan_status='Current','1','0') || ',' || \\\n",
    "iff(loan_status='Late (31-120 days)','1','0') || ',' || \\\n",
    "iff(loan_status='Fully Paid','1','0') || ',' || \\\n",
    "iff(loan_status='Default','1','0') || ',' || \\\n",
    "\\\n",
    "iff(term='36 months','1','0') || ',' || \\\n",
    "iff(term='60 months','1','0') || ',' || \\\n",
    "\\\n",
    "iff(grade='C','1','0') || ',' || \\\n",
    "iff(grade='D','1','0') || ',' || \\\n",
    "iff(grade='E','1','0') || ',' || \\\n",
    "iff(grade='F','1','0') || ',' || \\\n",
    "iff(grade='G','1','0') || ',' || \\\n",
    "\\\n",
    "iff(home_ownership='OWN','1','0') || ',' || \\\n",
    "iff(home_ownership='MORTGAGE','1','0') || ',' || \\\n",
    "iff(home_ownership='NE','1','0') || ',' || \\\n",
    "iff(home_ownership='RENT','1','0') || ',' || \\\n",
    "iff(home_ownership='ANY','1','0') || ',' || \\\n",
    "iff(home_ownership='OTHER','1','0') \\\n",
    "\\\n",
    ") as predicted_purpose \\\n",
    "from aggregates, loan_data \\\n",
    "where purpose = 'other' \\\n",
    "\"\n",
    "\n",
    "predict_sql = predict_sql + \" limit 100;\"\n",
    "df = execSQLAndGetDF(predict_sql)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "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>PREDICTED_PURPOSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   PREDICTED_PURPOSE\n",
       "50                 7\n",
       "51                 1\n",
       "52                 7\n",
       "53                 0\n",
       "54                 1"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[50:55]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0', '7', '1', '4', '5', '2', '6'], dtype=object)"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df.groupby('PREDICTED_PURPOSE').count()\n",
    "df.PREDICTED_PURPOSE.unique()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "We demonstrated how Scikit Learn models can be converted into Snowflake UDF to execute ML within Snowflake\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "Porter: \n",
    "https://github.com/nok/sklearn-porter/blob/release/1.0.0/examples/basics/index.pct.ipynb\n"
   ]
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}