You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@madlib.apache.org by fm...@apache.org on 2020/03/20 22:46:41 UTC
[madlib-site] branch asf-site updated: add new workbook for E2E ds
workflow
This is an automated email from the ASF dual-hosted git repository.
fmcquillan pushed a commit to branch asf-site
in repository https://gitbox.apache.org/repos/asf/madlib-site.git
The following commit(s) were added to refs/heads/asf-site by this push:
new 826205f add new workbook for E2E ds workflow
826205f is described below
commit 826205ff7787bb6a955766cb3195b1f29404b41d
Author: Frank McQuillan <fm...@pivotal.io>
AuthorDate: Fri Mar 20 15:46:10 2020 -0700
add new workbook for E2E ds workflow
---
.../E2E-workflows/MADlib-e2e-ds-workflow-eg1.ipynb | 4489 ++++++++++++++++++++
community-artifacts/README.md | 4 +-
2 files changed, 4491 insertions(+), 2 deletions(-)
diff --git a/community-artifacts/E2E-workflows/MADlib-e2e-ds-workflow-eg1.ipynb b/community-artifacts/E2E-workflows/MADlib-e2e-ds-workflow-eg1.ipynb
new file mode 100644
index 0000000..f2d7a34
--- /dev/null
+++ b/community-artifacts/E2E-workflows/MADlib-e2e-ds-workflow-eg1.ipynb
@@ -0,0 +1,4489 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<img src=\"https://s3.amazonaws.com/greenplum.org/wp-content/uploads/2018/11/14180216/logo-gpdb-light.svg\" alt=\"drawing\" width=\"200\"/>"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# Data Science Workshop"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "This notebook serves as an hands-on introduction to the data science pipeline. Using a single dataset throughout, it begins with loading the data into Greenplum Database (GPDB), then proceeds to data exploration, feature engineering, model development, and model evaluation.\n",
+ "\n",
+ "We’ll be using the publicly available [Abalone dataset from the University of California, Irvine Machine Learning Repository](https://archive.ics.uci.edu/ml/datasets/abalone). The dataset contains nine attributes (including our target prediction column).\n",
+ "\n",
+ "| Column Name | Data Type | Description|\n",
+ "| ---|:---:| ---:|\n",
+ "|Sex | text | M,F,I[infant]|\n",
+ "| Length | float | Longest shell measurement|\n",
+ "|Diameter | float | Perpendicular to length|\n",
+ "| Height | float | With meat in shell |\n",
+ "| Whole weight | float | Whole abalone |\n",
+ "| Shucked weight | float | Weight of meat only |\n",
+ "| Viscera weight | float | Gut weight (after bleeding) |\n",
+ "| Shell weight | float | Post-drying |\n",
+ "| Rings | integer | +1.5 gives the age in years|\n"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# Table of contents\n",
+ "\n",
+ "<a href=\"#setup\">1. Setup</a>\n",
+ "\n",
+ "<a href=\"#load_data\">2. Load data</a>\n",
+ "\n",
+ "<a href=\"#data_review\">3. Review raw data</a>\n",
+ "\n",
+ "<a href=\"#explore\">4. Explore data</a>\n",
+ "\n",
+ "<a href=\"#classification\">5. Classification models</a>\n",
+ "\n",
+ "* <a href=\"#logistic\">5a. Logistic regression</a>\n",
+ "\n",
+ "* <a href=\"#forest\">5b. Random forest</a>\n",
+ "\n",
+ "<a href=\"#regression\">6. Regression models</a>\n",
+ "\n",
+ "* <a href=\"#logistic\">6a. Linear regression</a>\n",
+ "\n",
+ "* <a href=\"#elastic\">6b. Elastic net</a>"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"setup\"></a>\n",
+ "# 1. Set Up Your Notebook Environment and Connect to the Database"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 1,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "# this command allows for visualizations to appear in the notebook\n",
+ "%matplotlib inline\n",
+ "%load_ext sql\n",
+ "import math\n",
+ "import six\n",
+ "import pandas as pd\n",
+ "from sqlalchemy import create_engine\n",
+ "import numpy as np \n",
+ "import matplotlib.pyplot as plt\n",
+ "import seaborn as sns\n",
+ "pd.set_option('display.max_columns', 200)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "Connect to Database"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 2,
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "# Greenplum Database 5.x on GCP (PM demo machine) - via tunnel\n",
+ "%sql postgresql://gpadmin@localhost:8000/madlib\n",
+ " \n",
+ "# PostgreSQL local\n",
+ "#%sql postgresql://fmcquillan@localhost:5432/madlib"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"load_data\"></a>\n",
+ "# 2. Load Abalone Data"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "An [abalone](https://simple.wikipedia.org/wiki/Abalone) is a salt water univalve mollusc.\n",
+ "We'll load the data from ```web external table``` then start looking at the data."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 3,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "10 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>sex</th>\n",
+ " <th>length</th>\n",
+ " <th>diameter</th>\n",
+ " <th>height</th>\n",
+ " <th>whole_weight</th>\n",
+ " <th>shucked_weight</th>\n",
+ " <th>viscera_weight</th>\n",
+ " <th>shell_weight</th>\n",
+ " <th>rings</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>M</td>\n",
+ " <td>0.455</td>\n",
+ " <td>0.365</td>\n",
+ " <td>0.095</td>\n",
+ " <td>0.514</td>\n",
+ " <td>0.2245</td>\n",
+ " <td>0.101</td>\n",
+ " <td>0.15</td>\n",
+ " <td>15</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>M</td>\n",
+ " <td>0.35</td>\n",
+ " <td>0.265</td>\n",
+ " <td>0.09</td>\n",
+ " <td>0.2255</td>\n",
+ " <td>0.0995</td>\n",
+ " <td>0.0485</td>\n",
+ " <td>0.07</td>\n",
+ " <td>7</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>F</td>\n",
+ " <td>0.53</td>\n",
+ " <td>0.42</td>\n",
+ " <td>0.135</td>\n",
+ " <td>0.677</td>\n",
+ " <td>0.2565</td>\n",
+ " <td>0.1415</td>\n",
+ " <td>0.21</td>\n",
+ " <td>9</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>M</td>\n",
+ " <td>0.44</td>\n",
+ " <td>0.365</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.516</td>\n",
+ " <td>0.2155</td>\n",
+ " <td>0.114</td>\n",
+ " <td>0.155</td>\n",
+ " <td>10</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>I</td>\n",
+ " <td>0.33</td>\n",
+ " <td>0.255</td>\n",
+ " <td>0.08</td>\n",
+ " <td>0.205</td>\n",
+ " <td>0.0895</td>\n",
+ " <td>0.0395</td>\n",
+ " <td>0.055</td>\n",
+ " <td>7</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>I</td>\n",
+ " <td>0.425</td>\n",
+ " <td>0.3</td>\n",
+ " <td>0.095</td>\n",
+ " <td>0.3515</td>\n",
+ " <td>0.141</td>\n",
+ " <td>0.0775</td>\n",
+ " <td>0.12</td>\n",
+ " <td>8</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>F</td>\n",
+ " <td>0.53</td>\n",
+ " <td>0.415</td>\n",
+ " <td>0.15</td>\n",
+ " <td>0.7775</td>\n",
+ " <td>0.237</td>\n",
+ " <td>0.1415</td>\n",
+ " <td>0.33</td>\n",
+ " <td>20</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>F</td>\n",
+ " <td>0.545</td>\n",
+ " <td>0.425</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.768</td>\n",
+ " <td>0.294</td>\n",
+ " <td>0.1495</td>\n",
+ " <td>0.26</td>\n",
+ " <td>16</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>M</td>\n",
+ " <td>0.475</td>\n",
+ " <td>0.37</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.5095</td>\n",
+ " <td>0.2165</td>\n",
+ " <td>0.1125</td>\n",
+ " <td>0.165</td>\n",
+ " <td>9</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>F</td>\n",
+ " <td>0.55</td>\n",
+ " <td>0.44</td>\n",
+ " <td>0.15</td>\n",
+ " <td>0.8945</td>\n",
+ " <td>0.3145</td>\n",
+ " <td>0.151</td>\n",
+ " <td>0.32</td>\n",
+ " <td>19</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'M', 0.455, 0.365, 0.095, 0.514, 0.2245, 0.101, 0.15, 15),\n",
+ " (u'M', 0.35, 0.265, 0.09, 0.2255, 0.0995, 0.0485, 0.07, 7),\n",
+ " (u'F', 0.53, 0.42, 0.135, 0.677, 0.2565, 0.1415, 0.21, 9),\n",
+ " (u'M', 0.44, 0.365, 0.125, 0.516, 0.2155, 0.114, 0.155, 10),\n",
+ " (u'I', 0.33, 0.255, 0.08, 0.205, 0.0895, 0.0395, 0.055, 7),\n",
+ " (u'I', 0.425, 0.3, 0.095, 0.3515, 0.141, 0.0775, 0.12, 8),\n",
+ " (u'F', 0.53, 0.415, 0.15, 0.7775, 0.237, 0.1415, 0.33, 20),\n",
+ " (u'F', 0.545, 0.425, 0.125, 0.768, 0.294, 0.1495, 0.26, 16),\n",
+ " (u'M', 0.475, 0.37, 0.125, 0.5095, 0.2165, 0.1125, 0.165, 9),\n",
+ " (u'F', 0.55, 0.44, 0.15, 0.8945, 0.3145, 0.151, 0.32, 19)]"
+ ]
+ },
+ "execution_count": 3,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "---\n",
+ "--- The below code will create an external table that points at the data available at IMS/UCI.\n",
+ "---\n",
+ "drop external table if exists abalone_web;\n",
+ "create external web table\n",
+ " abalone_web (\n",
+ " sex char(1),\n",
+ " length float,\n",
+ " diameter float,\n",
+ " height float,\n",
+ " whole_weight float,\n",
+ " shucked_weight float,\n",
+ " viscera_weight float,\n",
+ " shell_weight float,\n",
+ " rings integer\n",
+ " )\n",
+ " location ( 'http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data' )\n",
+ " format 'TEXT' ( delimiter ',' null '' )\n",
+ " log errors\n",
+ " segment reject limit 100\n",
+ ";\n",
+ "select * from abalone_web limit 10\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "Create heap table"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 4,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "Done.\n",
+ "Done.\n",
+ "4177 rows affected.\n",
+ "10 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>sex</th>\n",
+ " <th>length</th>\n",
+ " <th>diameter</th>\n",
+ " <th>height</th>\n",
+ " <th>whole_weight</th>\n",
+ " <th>shucked_weight</th>\n",
+ " <th>viscera_weight</th>\n",
+ " <th>shell_weight</th>\n",
+ " <th>rings</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0</td>\n",
+ " <td>M</td>\n",
+ " <td>0.455</td>\n",
+ " <td>0.365</td>\n",
+ " <td>0.095</td>\n",
+ " <td>0.514</td>\n",
+ " <td>0.2245</td>\n",
+ " <td>0.101</td>\n",
+ " <td>0.15</td>\n",
+ " <td>15</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>M</td>\n",
+ " <td>0.35</td>\n",
+ " <td>0.265</td>\n",
+ " <td>0.09</td>\n",
+ " <td>0.2255</td>\n",
+ " <td>0.0995</td>\n",
+ " <td>0.0485</td>\n",
+ " <td>0.07</td>\n",
+ " <td>7</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2</td>\n",
+ " <td>F</td>\n",
+ " <td>0.53</td>\n",
+ " <td>0.42</td>\n",
+ " <td>0.135</td>\n",
+ " <td>0.677</td>\n",
+ " <td>0.2565</td>\n",
+ " <td>0.1415</td>\n",
+ " <td>0.21</td>\n",
+ " <td>9</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3</td>\n",
+ " <td>M</td>\n",
+ " <td>0.44</td>\n",
+ " <td>0.365</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.516</td>\n",
+ " <td>0.2155</td>\n",
+ " <td>0.114</td>\n",
+ " <td>0.155</td>\n",
+ " <td>10</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>4</td>\n",
+ " <td>I</td>\n",
+ " <td>0.33</td>\n",
+ " <td>0.255</td>\n",
+ " <td>0.08</td>\n",
+ " <td>0.205</td>\n",
+ " <td>0.0895</td>\n",
+ " <td>0.0395</td>\n",
+ " <td>0.055</td>\n",
+ " <td>7</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>5</td>\n",
+ " <td>I</td>\n",
+ " <td>0.425</td>\n",
+ " <td>0.3</td>\n",
+ " <td>0.095</td>\n",
+ " <td>0.3515</td>\n",
+ " <td>0.141</td>\n",
+ " <td>0.0775</td>\n",
+ " <td>0.12</td>\n",
+ " <td>8</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>6</td>\n",
+ " <td>F</td>\n",
+ " <td>0.53</td>\n",
+ " <td>0.415</td>\n",
+ " <td>0.15</td>\n",
+ " <td>0.7775</td>\n",
+ " <td>0.237</td>\n",
+ " <td>0.1415</td>\n",
+ " <td>0.33</td>\n",
+ " <td>20</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>7</td>\n",
+ " <td>F</td>\n",
+ " <td>0.545</td>\n",
+ " <td>0.425</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.768</td>\n",
+ " <td>0.294</td>\n",
+ " <td>0.1495</td>\n",
+ " <td>0.26</td>\n",
+ " <td>16</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>8</td>\n",
+ " <td>M</td>\n",
+ " <td>0.475</td>\n",
+ " <td>0.37</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.5095</td>\n",
+ " <td>0.2165</td>\n",
+ " <td>0.1125</td>\n",
+ " <td>0.165</td>\n",
+ " <td>9</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>9</td>\n",
+ " <td>F</td>\n",
+ " <td>0.55</td>\n",
+ " <td>0.44</td>\n",
+ " <td>0.15</td>\n",
+ " <td>0.8945</td>\n",
+ " <td>0.3145</td>\n",
+ " <td>0.151</td>\n",
+ " <td>0.32</td>\n",
+ " <td>19</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(0, u'M', 0.455, 0.365, 0.095, 0.514, 0.2245, 0.101, 0.15, 15),\n",
+ " (1, u'M', 0.35, 0.265, 0.09, 0.2255, 0.0995, 0.0485, 0.07, 7),\n",
+ " (2, u'F', 0.53, 0.42, 0.135, 0.677, 0.2565, 0.1415, 0.21, 9),\n",
+ " (3, u'M', 0.44, 0.365, 0.125, 0.516, 0.2155, 0.114, 0.155, 10),\n",
+ " (4, u'I', 0.33, 0.255, 0.08, 0.205, 0.0895, 0.0395, 0.055, 7),\n",
+ " (5, u'I', 0.425, 0.3, 0.095, 0.3515, 0.141, 0.0775, 0.12, 8),\n",
+ " (6, u'F', 0.53, 0.415, 0.15, 0.7775, 0.237, 0.1415, 0.33, 20),\n",
+ " (7, u'F', 0.545, 0.425, 0.125, 0.768, 0.294, 0.1495, 0.26, 16),\n",
+ " (8, u'M', 0.475, 0.37, 0.125, 0.5095, 0.2165, 0.1125, 0.165, 9),\n",
+ " (9, u'F', 0.55, 0.44, 0.15, 0.8945, 0.3145, 0.151, 0.32, 19)]"
+ ]
+ },
+ "execution_count": 4,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone;\n",
+ "CREATE TABLE abalone (\n",
+ " id integer,\n",
+ " sex char(1),\n",
+ " length float,\n",
+ " diameter float,\n",
+ " height float,\n",
+ " whole_weight float,\n",
+ " shucked_weight float,\n",
+ " viscera_weight float,\n",
+ " shell_weight float,\n",
+ " rings integer\n",
+ " )\n",
+ ";\n",
+ " \n",
+ "DROP SEQUENCE IF EXISTS abalone_id ;\n",
+ "CREATE TEMPORARY SEQUENCE abalone_id MINVALUE 0 START 0;\n",
+ "\n",
+ "insert into \n",
+ " abalone \n",
+ "select \n",
+ " nextval('abalone_id') as id, *\n",
+ "FROM \n",
+ " abalone_web\n",
+ ";\n",
+ "\n",
+ "\n",
+ "SELECT * FROM abalone ORDER BY id LIMIT 10;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"data_review\"></a>\n",
+ "# 3. Review raw data\n",
+ "\n",
+ "Load some data into a local variable ```abalone```"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 5,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "4177 rows affected.\n"
+ ]
+ }
+ ],
+ "source": [
+ "abalone = %sql select * from abalone where random() < 1.0 order by id limit 5000;\n",
+ "abalone = abalone.DataFrame()"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 6,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "<class 'pandas.core.frame.DataFrame'>\n",
+ "RangeIndex: 4177 entries, 0 to 4176\n",
+ "Data columns (total 10 columns):\n",
+ "id 4177 non-null int64\n",
+ "sex 4177 non-null object\n",
+ "length 4177 non-null float64\n",
+ "diameter 4177 non-null float64\n",
+ "height 4177 non-null float64\n",
+ "whole_weight 4177 non-null float64\n",
+ "shucked_weight 4177 non-null float64\n",
+ "viscera_weight 4177 non-null float64\n",
+ "shell_weight 4177 non-null float64\n",
+ "rings 4177 non-null int64\n",
+ "dtypes: float64(7), int64(2), object(1)\n",
+ "memory usage: 326.4+ KB\n"
+ ]
+ }
+ ],
+ "source": [
+ "abalone.info();"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "We're interested in estimating the age of the abalone in the data. To get age, add 1.5 to the number of rings. A good place to begin is to create a histogram of the target variable."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 7,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYAAAAD8CAYAAAB+UHOxAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi41LCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvSM8oowAAEwdJREFUeJzt3X+MZeV93/H3p2BsAi0Lxhqh3W2XNqtErjdNnBEmchQNpnUAR1kiORSLxotLta2EU6esFK/TP3BTWSJtiGtLKdXG0Kwl12uCnbIKpA7CjFz/ATHYhOVHUjZkCbta2Dj8cMZOYk387R/3wRrvzrDLvXfm7p3n/ZJGc85znnPv9+Fc9nPPc8+5k6pCktSfvzfpAiRJk2EASFKnDABJ6pQBIEmdMgAkqVMGgCR1ygCQpE4ZAJLUKQNAkjp15qQLeC0XXnhhbdmyZdJlnJJv [...]
+ "text/plain": [
+ "<Figure size 432x288 with 1 Axes>"
+ ]
+ },
+ "metadata": {
+ "needs_background": "light"
+ },
+ "output_type": "display_data"
+ }
+ ],
+ "source": [
+ "(abalone.rings + 1.5 ).hist(bins=20);"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 8,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "True 2770\n",
+ "False 1407\n",
+ "Name: rings, dtype: int64"
+ ]
+ },
+ "execution_count": 8,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "((abalone.rings + 1.5) >= 10).value_counts()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Look at the cumulative distribution"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 9,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYUAAAEKCAYAAAD9xUlFAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi41LCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvSM8oowAAIABJREFUeJzt3Xl8VeW59//PlYEESAiEMUACKCgzAilqtS1ocRbqXOpcK9qjPdUWz9E+PR6xP59TK7W0PT61aNWqVUodEC0KrcWxVQJhRgVkTMI8JQESspPr98feiQEhWUB2drLzfb9e67XXvK/lwn1l3fe679vcHREREYCEWAcgIiJNh5KCiIjUUFIQEZEaSgoiIlJDSUFERGooKYiISA0lBRERqaGkICIiNZQURESkRlKsAzhWnTp18t69e8c6DBGRZmXhwoU73L1zffs1u6TQu3dv [...]
+ "text/plain": [
+ "<Figure size 432x288 with 1 Axes>"
+ ]
+ },
+ "metadata": {
+ "needs_background": "light"
+ },
+ "output_type": "display_data"
+ }
+ ],
+ "source": [
+ "n_cumsum_df = pd.DataFrame({'Cumulative Sum':((abalone.rings + 1.5).value_counts().sort_index().cumsum())/abalone.shape[0]})\n",
+ "ax = n_cumsum_df.plot();\n",
+ "ax.set_xlabel('Age');\n",
+ "ax.set_ylabel('Normalized Cumulative Sum');"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"explore\"></a>\n",
+ "# 4. Process and explore data\n",
+ "\n",
+ "Create a column for maturity"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 10,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "4177 rows affected.\n",
+ "10 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>sex</th>\n",
+ " <th>length</th>\n",
+ " <th>diameter</th>\n",
+ " <th>height</th>\n",
+ " <th>whole_weight</th>\n",
+ " <th>shucked_weight</th>\n",
+ " <th>viscera_weight</th>\n",
+ " <th>shell_weight</th>\n",
+ " <th>rings</th>\n",
+ " <th>age</th>\n",
+ " <th>mature</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>m</td>\n",
+ " <td>0.35</td>\n",
+ " <td>0.265</td>\n",
+ " <td>0.09</td>\n",
+ " <td>0.2255</td>\n",
+ " <td>0.0995</td>\n",
+ " <td>0.0485</td>\n",
+ " <td>0.07</td>\n",
+ " <td>7</td>\n",
+ " <td>8.5</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>5</td>\n",
+ " <td>i</td>\n",
+ " <td>0.425</td>\n",
+ " <td>0.3</td>\n",
+ " <td>0.095</td>\n",
+ " <td>0.3515</td>\n",
+ " <td>0.141</td>\n",
+ " <td>0.0775</td>\n",
+ " <td>0.12</td>\n",
+ " <td>8</td>\n",
+ " <td>9.5</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>9</td>\n",
+ " <td>f</td>\n",
+ " <td>0.55</td>\n",
+ " <td>0.44</td>\n",
+ " <td>0.15</td>\n",
+ " <td>0.8945</td>\n",
+ " <td>0.3145</td>\n",
+ " <td>0.151</td>\n",
+ " <td>0.32</td>\n",
+ " <td>19</td>\n",
+ " <td>20.5</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>13</td>\n",
+ " <td>f</td>\n",
+ " <td>0.535</td>\n",
+ " <td>0.405</td>\n",
+ " <td>0.145</td>\n",
+ " <td>0.6845</td>\n",
+ " <td>0.2725</td>\n",
+ " <td>0.171</td>\n",
+ " <td>0.205</td>\n",
+ " <td>10</td>\n",
+ " <td>11.5</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>17</td>\n",
+ " <td>f</td>\n",
+ " <td>0.44</td>\n",
+ " <td>0.34</td>\n",
+ " <td>0.1</td>\n",
+ " <td>0.451</td>\n",
+ " <td>0.188</td>\n",
+ " <td>0.087</td>\n",
+ " <td>0.13</td>\n",
+ " <td>10</td>\n",
+ " <td>11.5</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>21</td>\n",
+ " <td>i</td>\n",
+ " <td>0.38</td>\n",
+ " <td>0.275</td>\n",
+ " <td>0.1</td>\n",
+ " <td>0.2255</td>\n",
+ " <td>0.08</td>\n",
+ " <td>0.049</td>\n",
+ " <td>0.085</td>\n",
+ " <td>10</td>\n",
+ " <td>11.5</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>25</td>\n",
+ " <td>f</td>\n",
+ " <td>0.56</td>\n",
+ " <td>0.44</td>\n",
+ " <td>0.14</td>\n",
+ " <td>0.9285</td>\n",
+ " <td>0.3825</td>\n",
+ " <td>0.188</td>\n",
+ " <td>0.3</td>\n",
+ " <td>11</td>\n",
+ " <td>12.5</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>29</td>\n",
+ " <td>m</td>\n",
+ " <td>0.575</td>\n",
+ " <td>0.425</td>\n",
+ " <td>0.14</td>\n",
+ " <td>0.8635</td>\n",
+ " <td>0.393</td>\n",
+ " <td>0.227</td>\n",
+ " <td>0.2</td>\n",
+ " <td>11</td>\n",
+ " <td>12.5</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>33</td>\n",
+ " <td>f</td>\n",
+ " <td>0.68</td>\n",
+ " <td>0.55</td>\n",
+ " <td>0.175</td>\n",
+ " <td>1.798</td>\n",
+ " <td>0.815</td>\n",
+ " <td>0.3925</td>\n",
+ " <td>0.455</td>\n",
+ " <td>19</td>\n",
+ " <td>20.5</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>37</td>\n",
+ " <td>f</td>\n",
+ " <td>0.45</td>\n",
+ " <td>0.355</td>\n",
+ " <td>0.105</td>\n",
+ " <td>0.5225</td>\n",
+ " <td>0.237</td>\n",
+ " <td>0.1165</td>\n",
+ " <td>0.145</td>\n",
+ " <td>8</td>\n",
+ " <td>9.5</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1, u'm', 0.35, 0.265, 0.09, 0.2255, 0.0995, 0.0485, 0.07, 7, Decimal('8.5'), 0),\n",
+ " (5, u'i', 0.425, 0.3, 0.095, 0.3515, 0.141, 0.0775, 0.12, 8, Decimal('9.5'), 0),\n",
+ " (9, u'f', 0.55, 0.44, 0.15, 0.8945, 0.3145, 0.151, 0.32, 19, Decimal('20.5'), 1),\n",
+ " (13, u'f', 0.535, 0.405, 0.145, 0.6845, 0.2725, 0.171, 0.205, 10, Decimal('11.5'), 1),\n",
+ " (17, u'f', 0.44, 0.34, 0.1, 0.451, 0.188, 0.087, 0.13, 10, Decimal('11.5'), 1),\n",
+ " (21, u'i', 0.38, 0.275, 0.1, 0.2255, 0.08, 0.049, 0.085, 10, Decimal('11.5'), 1),\n",
+ " (25, u'f', 0.56, 0.44, 0.14, 0.9285, 0.3825, 0.188, 0.3, 11, Decimal('12.5'), 1),\n",
+ " (29, u'm', 0.575, 0.425, 0.14, 0.8635, 0.393, 0.227, 0.2, 11, Decimal('12.5'), 1),\n",
+ " (33, u'f', 0.68, 0.55, 0.175, 1.798, 0.815, 0.3925, 0.455, 19, Decimal('20.5'), 1),\n",
+ " (37, u'f', 0.45, 0.355, 0.105, 0.5225, 0.237, 0.1165, 0.145, 8, Decimal('9.5'), 0)]"
+ ]
+ },
+ "execution_count": 10,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_target;\n",
+ "CREATE TABLE abalone_target\n",
+ "AS\n",
+ "SELECT \n",
+ " id,\n",
+ " lower(sex) as sex, --- ensure that sex is indicated in lower case (m, f, i)\n",
+ " \"length\",\n",
+ " diameter,\n",
+ " height,\n",
+ " whole_weight,\n",
+ " shucked_weight,\n",
+ " viscera_weight,\n",
+ " shell_weight,\n",
+ " rings,\n",
+ " rings + 1.5 as age, --- Define the age\n",
+ " CASE WHEN --- Identifies whether the abalone is mature or not (1/0)\n",
+ " (rings + 1.5) >= 10.0\n",
+ " THEN 1\n",
+ " ELSE 0\n",
+ " END as mature\n",
+ "FROM abalone\n",
+ ";\n",
+ "SELECT * FROM abalone_target LIMIT 10; --- show a sample"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Encode categorical variables\n",
+ "\n",
+ "Next use [MADlib to one-hot encode](http://madlib.apache.org/docs/latest/group__grp__encode__categorical.html) the “sex” column which is a categorical variable. In order to create a predictive model, we need all our columns to be numerical values. Making sure all our model inputs conform to this standard is an important part of the data science modeling pipeline and is considered part of the preprocessing/data cleaning step of the process."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 11,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "10 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>length</th>\n",
+ " <th>diameter</th>\n",
+ " <th>height</th>\n",
+ " <th>whole_weight</th>\n",
+ " <th>shucked_weight</th>\n",
+ " <th>viscera_weight</th>\n",
+ " <th>shell_weight</th>\n",
+ " <th>rings</th>\n",
+ " <th>age</th>\n",
+ " <th>mature</th>\n",
+ " <th>sex_f</th>\n",
+ " <th>sex_i</th>\n",
+ " <th>sex_m</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3</td>\n",
+ " <td>0.44</td>\n",
+ " <td>0.365</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.516</td>\n",
+ " <td>0.2155</td>\n",
+ " <td>0.114</td>\n",
+ " <td>0.155</td>\n",
+ " <td>10</td>\n",
+ " <td>11.5</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>11</td>\n",
+ " <td>0.43</td>\n",
+ " <td>0.35</td>\n",
+ " <td>0.11</td>\n",
+ " <td>0.406</td>\n",
+ " <td>0.1675</td>\n",
+ " <td>0.081</td>\n",
+ " <td>0.135</td>\n",
+ " <td>10</td>\n",
+ " <td>11.5</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>19</td>\n",
+ " <td>0.45</td>\n",
+ " <td>0.32</td>\n",
+ " <td>0.1</td>\n",
+ " <td>0.381</td>\n",
+ " <td>0.1705</td>\n",
+ " <td>0.075</td>\n",
+ " <td>0.115</td>\n",
+ " <td>9</td>\n",
+ " <td>10.5</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>27</td>\n",
+ " <td>0.59</td>\n",
+ " <td>0.445</td>\n",
+ " <td>0.14</td>\n",
+ " <td>0.931</td>\n",
+ " <td>0.356</td>\n",
+ " <td>0.234</td>\n",
+ " <td>0.28</td>\n",
+ " <td>12</td>\n",
+ " <td>13.5</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>35</td>\n",
+ " <td>0.465</td>\n",
+ " <td>0.355</td>\n",
+ " <td>0.105</td>\n",
+ " <td>0.4795</td>\n",
+ " <td>0.227</td>\n",
+ " <td>0.124</td>\n",
+ " <td>0.125</td>\n",
+ " <td>8</td>\n",
+ " <td>9.5</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>43</td>\n",
+ " <td>0.205</td>\n",
+ " <td>0.15</td>\n",
+ " <td>0.055</td>\n",
+ " <td>0.042</td>\n",
+ " <td>0.0255</td>\n",
+ " <td>0.015</td>\n",
+ " <td>0.012</td>\n",
+ " <td>5</td>\n",
+ " <td>6.5</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>51</td>\n",
+ " <td>0.4</td>\n",
+ " <td>0.32</td>\n",
+ " <td>0.095</td>\n",
+ " <td>0.303</td>\n",
+ " <td>0.1335</td>\n",
+ " <td>0.06</td>\n",
+ " <td>0.1</td>\n",
+ " <td>7</td>\n",
+ " <td>8.5</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>59</td>\n",
+ " <td>0.505</td>\n",
+ " <td>0.4</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.583</td>\n",
+ " <td>0.246</td>\n",
+ " <td>0.13</td>\n",
+ " <td>0.175</td>\n",
+ " <td>7</td>\n",
+ " <td>8.5</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>67</td>\n",
+ " <td>0.595</td>\n",
+ " <td>0.495</td>\n",
+ " <td>0.185</td>\n",
+ " <td>1.285</td>\n",
+ " <td>0.416</td>\n",
+ " <td>0.224</td>\n",
+ " <td>0.485</td>\n",
+ " <td>13</td>\n",
+ " <td>14.5</td>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>75</td>\n",
+ " <td>0.6</td>\n",
+ " <td>0.475</td>\n",
+ " <td>0.15</td>\n",
+ " <td>1.0075</td>\n",
+ " <td>0.4425</td>\n",
+ " <td>0.221</td>\n",
+ " <td>0.28</td>\n",
+ " <td>15</td>\n",
+ " <td>16.5</td>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(3, 0.44, 0.365, 0.125, 0.516, 0.2155, 0.114, 0.155, 10, Decimal('11.5'), 1, 0, 0, 1),\n",
+ " (11, 0.43, 0.35, 0.11, 0.406, 0.1675, 0.081, 0.135, 10, Decimal('11.5'), 1, 0, 0, 1),\n",
+ " (19, 0.45, 0.32, 0.1, 0.381, 0.1705, 0.075, 0.115, 9, Decimal('10.5'), 1, 0, 0, 1),\n",
+ " (27, 0.59, 0.445, 0.14, 0.931, 0.356, 0.234, 0.28, 12, Decimal('13.5'), 1, 0, 0, 1),\n",
+ " (35, 0.465, 0.355, 0.105, 0.4795, 0.227, 0.124, 0.125, 8, Decimal('9.5'), 0, 0, 0, 1),\n",
+ " (43, 0.205, 0.15, 0.055, 0.042, 0.0255, 0.015, 0.012, 5, Decimal('6.5'), 0, 0, 1, 0),\n",
+ " (51, 0.4, 0.32, 0.095, 0.303, 0.1335, 0.06, 0.1, 7, Decimal('8.5'), 0, 0, 0, 1),\n",
+ " (59, 0.505, 0.4, 0.125, 0.583, 0.246, 0.13, 0.175, 7, Decimal('8.5'), 0, 1, 0, 0),\n",
+ " (67, 0.595, 0.495, 0.185, 1.285, 0.416, 0.224, 0.485, 13, Decimal('14.5'), 1, 1, 0, 0),\n",
+ " (75, 0.6, 0.475, 0.15, 1.0075, 0.4425, 0.221, 0.28, 15, Decimal('16.5'), 1, 1, 0, 0)]"
+ ]
+ },
+ "execution_count": 11,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_encoded;\n",
+ "SELECT\n",
+ "madlib.encode_categorical_variables (\n",
+ " 'abalone_target', -- input table\n",
+ " 'abalone_encoded', -- output table\n",
+ " 'sex', -- categorical_cols\n",
+ " NULL, --categorical_cols_to_exclude -- Optional\n",
+ " NULL, --row_id, -- Optional\n",
+ " NULL, --top, -- Optional\n",
+ " NULL, --value_to_drop, -- Optional\n",
+ " NULL, --encode_null, -- Optional\n",
+ " NULL, --output_type, -- Optional\n",
+ " NULL, --output_dictionary, -- Optional\n",
+ " NULL --distributed_by -- Optional\n",
+ " )\n",
+ ";\n",
+ "SELECT *\n",
+ "FROM abalone_encoded\n",
+ "LIMIT 10"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Statistics\n",
+ "\n",
+ "The next step through the modeling process is to explore our data. We’ll again use some of MADLib’s built in functionality to generate [descriptive statistics](http://madlib.apache.org/docs/latest/group__grp__summary.html) of our data. This will generate important information about the data including count, number of missing values, the mean, median, maximum, minimum, interquartile range, mode, and variance.\n",
+ "\n",
+ "Note that you only want to do this after converting categorical data to numeric data because otherwise the statistics will not be compute correctly."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 12,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "14 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>group_by</th>\n",
+ " <th>group_by_value</th>\n",
+ " <th>target_column</th>\n",
+ " <th>column_number</th>\n",
+ " <th>data_type</th>\n",
+ " <th>row_count</th>\n",
+ " <th>distinct_values</th>\n",
+ " <th>missing_values</th>\n",
+ " <th>blank_values</th>\n",
+ " <th>fraction_missing</th>\n",
+ " <th>fraction_blank</th>\n",
+ " <th>positive_values</th>\n",
+ " <th>negative_values</th>\n",
+ " <th>zero_values</th>\n",
+ " <th>mean</th>\n",
+ " <th>variance</th>\n",
+ " <th>confidence_interval</th>\n",
+ " <th>min</th>\n",
+ " <th>max</th>\n",
+ " <th>first_quartile</th>\n",
+ " <th>median</th>\n",
+ " <th>third_quartile</th>\n",
+ " <th>most_frequent_values</th>\n",
+ " <th>mfv_frequencies</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>id</td>\n",
+ " <td>1</td>\n",
+ " <td>int4</td>\n",
+ " <td>4177</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4176</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>2088.0</td>\n",
+ " <td>1454292.16667</td>\n",
+ " <td>[2051.42791957426, 2124.57208042574]</td>\n",
+ " <td>0.0</td>\n",
+ " <td>4176.0</td>\n",
+ " <td>1044.0</td>\n",
+ " <td>2088.0</td>\n",
+ " <td>3132.0</td>\n",
+ " <td>[u'3453', u'4114', u'2806', u'2823', u'2700', u'2914', u'2970', u'3559', u'3663', u'3809']</td>\n",
+ " <td>[5L, 5L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>length</td>\n",
+ " <td>2</td>\n",
+ " <td>float8</td>\n",
+ " <td>4177</td>\n",
+ " <td>134</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0.523992099593</td>\n",
+ " <td>0.0144223076483</td>\n",
+ " <td>[0.520350088942874, 0.527634110243145]</td>\n",
+ " <td>0.075</td>\n",
+ " <td>0.815</td>\n",
+ " <td>0.45</td>\n",
+ " <td>0.545</td>\n",
+ " <td>0.615</td>\n",
+ " <td>[u'0.55', u'0.625', u'0.575', u'0.58', u'0.62', u'0.6', u'0.5', u'0.57', u'0.63', u'0.61']</td>\n",
+ " <td>[94L, 94L, 93L, 92L, 87L, 87L, 81L, 79L, 78L, 75L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>diameter</td>\n",
+ " <td>3</td>\n",
+ " <td>float8</td>\n",
+ " <td>4177</td>\n",
+ " <td>111</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0.407881254489</td>\n",
+ " <td>0.00984855103022</td>\n",
+ " <td>[0.404871645997762, 0.410890862979976]</td>\n",
+ " <td>0.055</td>\n",
+ " <td>0.65</td>\n",
+ " <td>0.35</td>\n",
+ " <td>0.425</td>\n",
+ " <td>0.48</td>\n",
+ " <td>[u'0.45', u'0.475', u'0.4', u'0.5', u'0.47', u'0.48', u'0.455', u'0.46', u'0.44', u'0.485']</td>\n",
+ " <td>[139L, 120L, 111L, 110L, 100L, 91L, 90L, 89L, 87L, 83L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>height</td>\n",
+ " <td>4</td>\n",
+ " <td>float8</td>\n",
+ " <td>4177</td>\n",
+ " <td>51</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4175</td>\n",
+ " <td>0</td>\n",
+ " <td>2</td>\n",
+ " <td>0.13951639933</td>\n",
+ " <td>0.00174950266443</td>\n",
+ " <td>[0.138247926591561, 0.140784872067761]</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.13</td>\n",
+ " <td>0.115</td>\n",
+ " <td>0.14</td>\n",
+ " <td>0.165</td>\n",
+ " <td>[u'0.15', u'0.14', u'0.155', u'0.175', u'0.16', u'0.125', u'0.165', u'0.135', u'0.145', u'0.12']</td>\n",
+ " <td>[267L, 220L, 217L, 211L, 205L, 202L, 193L, 189L, 182L, 169L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>whole_weight</td>\n",
+ " <td>5</td>\n",
+ " <td>float8</td>\n",
+ " <td>4177</td>\n",
+ " <td>2429</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0.828742159445</td>\n",
+ " <td>0.240481389202</td>\n",
+ " <td>[0.813870324055101, 0.843613994834057]</td>\n",
+ " <td>0.002</td>\n",
+ " <td>2.8255</td>\n",
+ " <td>0.4415</td>\n",
+ " <td>0.7995</td>\n",
+ " <td>1.153</td>\n",
+ " <td>[u'1.1345', u'0.2225', u'0.196', u'0.4425', u'0.4775', u'1.0835', u'0.97', u'0.874', u'1.1155', u'0.872']</td>\n",
+ " <td>[10L, 8L, 8L, 7L, 7L, 7L, 7L, 7L, 7L, 7L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>shucked_weight</td>\n",
+ " <td>6</td>\n",
+ " <td>float8</td>\n",
+ " <td>4177</td>\n",
+ " <td>1515</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0.359367488628</td>\n",
+ " <td>0.0492675507435</td>\n",
+ " <td>[0.352636105342962, 0.366098871913441]</td>\n",
+ " <td>0.001</td>\n",
+ " <td>1.488</td>\n",
+ " <td>0.186</td>\n",
+ " <td>0.336</td>\n",
+ " <td>0.502</td>\n",
+ " <td>[u'0.175', u'0.2505', u'0.21', u'0.419', u'0.2', u'0.097', u'0.165', u'0.096', u'0.0745', u'0.2025']</td>\n",
+ " <td>[11L, 10L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>viscera_weight</td>\n",
+ " <td>7</td>\n",
+ " <td>float8</td>\n",
+ " <td>4177</td>\n",
+ " <td>880</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0.180593607853</td>\n",
+ " <td>0.01201528386</td>\n",
+ " <td>[0.17726937948362, 0.183917836221431]</td>\n",
+ " <td>0.0005</td>\n",
+ " <td>0.76</td>\n",
+ " <td>0.0935</td>\n",
+ " <td>0.171</td>\n",
+ " <td>0.253</td>\n",
+ " <td>[u'0.1715', u'0.196', u'0.0575', u'0.2195', u'0.061', u'0.037', u'0.096', u'0.1405', u'0.099', u'0.0265']</td>\n",
+ " <td>[15L, 14L, 13L, 13L, 13L, 13L, 12L, 12L, 12L, 12L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>shell_weight</td>\n",
+ " <td>8</td>\n",
+ " <td>float8</td>\n",
+ " <td>4177</td>\n",
+ " <td>926</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0.238830859469</td>\n",
+ " <td>0.0193773832022</td>\n",
+ " <td>[0.234609314715373, 0.243052404221663]</td>\n",
+ " <td>0.0015</td>\n",
+ " <td>1.005</td>\n",
+ " <td>0.13</td>\n",
+ " <td>0.234</td>\n",
+ " <td>0.329</td>\n",
+ " <td>[u'0.275', u'0.25', u'0.185', u'0.265', u'0.315', u'0.3', u'0.17', u'0.285', u'0.175', u'0.22']</td>\n",
+ " <td>[43L, 42L, 40L, 40L, 40L, 37L, 37L, 37L, 36L, 36L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>rings</td>\n",
+ " <td>9</td>\n",
+ " <td>int4</td>\n",
+ " <td>4177</td>\n",
+ " <td>28</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>9.93368446253</td>\n",
+ " <td>10.3952659473</td>\n",
+ " <td>[9.83590635305212, 10.0314625720137]</td>\n",
+ " <td>1.0</td>\n",
+ " <td>29.0</td>\n",
+ " <td>8.0</td>\n",
+ " <td>9.0</td>\n",
+ " <td>11.0</td>\n",
+ " <td>[u'9', u'10', u'8', u'11', u'7', u'12', u'6', u'13', u'14', u'5']</td>\n",
+ " <td>[689L, 634L, 568L, 487L, 391L, 267L, 259L, 203L, 126L, 115L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>age</td>\n",
+ " <td>10</td>\n",
+ " <td>numeric</td>\n",
+ " <td>4177</td>\n",
+ " <td>28</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4177</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>11.4336844625</td>\n",
+ " <td>10.3952659473</td>\n",
+ " <td>[11.3359063530521, 11.5314625720137]</td>\n",
+ " <td>2.5</td>\n",
+ " <td>30.5</td>\n",
+ " <td>9.5</td>\n",
+ " <td>10.5</td>\n",
+ " <td>12.5</td>\n",
+ " <td>[u'10.5', u'11.5', u'9.5', u'12.5', u'8.5', u'13.5', u'7.5', u'14.5', u'15.5', u'6.5']</td>\n",
+ " <td>[689L, 634L, 568L, 487L, 391L, 267L, 259L, 203L, 126L, 115L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>mature</td>\n",
+ " <td>11</td>\n",
+ " <td>int4</td>\n",
+ " <td>4177</td>\n",
+ " <td>2</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>2770</td>\n",
+ " <td>0</td>\n",
+ " <td>1407</td>\n",
+ " <td>0.663155374671</td>\n",
+ " <td>0.223433815173</td>\n",
+ " <td>[0.648820355293342, 0.67749039404829]</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>[u'1', u'0']</td>\n",
+ " <td>[2770L, 1407L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>sex_f</td>\n",
+ " <td>12</td>\n",
+ " <td>int4</td>\n",
+ " <td>4177</td>\n",
+ " <td>2</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>1307</td>\n",
+ " <td>0</td>\n",
+ " <td>2870</td>\n",
+ " <td>0.312903998085</td>\n",
+ " <td>0.215046569565</td>\n",
+ " <td>[0.298840605732167, 0.326967390437333]</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>[u'0', u'1']</td>\n",
+ " <td>[2870L, 1307L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>sex_i</td>\n",
+ " <td>13</td>\n",
+ " <td>int4</td>\n",
+ " <td>4177</td>\n",
+ " <td>2</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>1342</td>\n",
+ " <td>0</td>\n",
+ " <td>2835</td>\n",
+ " <td>0.32128321762</td>\n",
+ " <td>0.218112529203</td>\n",
+ " <td>[0.30711992784858, 0.335446507392023]</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>[u'0', u'1']</td>\n",
+ " <td>[2835L, 1342L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>sex_m</td>\n",
+ " <td>14</td>\n",
+ " <td>int4</td>\n",
+ " <td>4177</td>\n",
+ " <td>2</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>1528</td>\n",
+ " <td>0</td>\n",
+ " <td>2649</td>\n",
+ " <td>0.365812784295</td>\n",
+ " <td>0.23204934521</td>\n",
+ " <td>[0.351204002328337, 0.380421566261561]</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>[u'0', u'1']</td>\n",
+ " <td>[2649L, 1528L]</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(None, None, u'id', 1, u'int4', 4177L, 4177L, 0L, None, 0.0, None, 4176L, 0L, 1L, 2088.0, 1454292.16666667, [2051.42791957426, 2124.57208042574], 0.0, 4176.0, 1044.0, 2088.0, 3132.0, [u'3453', u'4114', u'2806', u'2823', u'2700', u'2914', u'2970', u'3559', u'3663', u'3809'], [5L, 5L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L]),\n",
+ " (None, None, u'length', 2, u'float8', 4177L, 134L, 0L, None, 0.0, None, 4177L, 0L, 0L, 0.523992099593009, 0.0144223076482969, [0.520350088942874, 0.527634110243145], 0.075, 0.815, 0.45, 0.545, 0.615, [u'0.55', u'0.625', u'0.575', u'0.58', u'0.62', u'0.6', u'0.5', u'0.57', u'0.63', u'0.61'], [94L, 94L, 93L, 92L, 87L, 87L, 81L, 79L, 78L, 75L]),\n",
+ " (None, None, u'diameter', 3, u'float8', 4177L, 111L, 0L, None, 0.0, None, 4177L, 0L, 0L, 0.407881254488869, 0.00984855103022442, [0.404871645997762, 0.410890862979976], 0.055, 0.65, 0.35, 0.425, 0.48, [u'0.45', u'0.475', u'0.4', u'0.5', u'0.47', u'0.48', u'0.455', u'0.46', u'0.44', u'0.485'], [139L, 120L, 111L, 110L, 100L, 91L, 90L, 89L, 87L, 83L]),\n",
+ " (None, None, u'height', 4, u'float8', 4177L, 51L, 0L, None, 0.0, None, 4175L, 0L, 2L, 0.139516399329661, 0.00174950266442686, [0.138247926591561, 0.140784872067761], 0.0, 1.13, 0.115, 0.14, 0.165, [u'0.15', u'0.14', u'0.155', u'0.175', u'0.16', u'0.125', u'0.165', u'0.135', u'0.145', u'0.12'], [267L, 220L, 217L, 211L, 205L, 202L, 193L, 189L, 182L, 169L]),\n",
+ " (None, None, u'whole_weight', 5, u'float8', 4177L, 2429L, 0L, None, 0.0, None, 4177L, 0L, 0L, 0.828742159444579, 0.240481389201558, [0.813870324055101, 0.843613994834057], 0.002, 2.8255, 0.4415, 0.7995, 1.153, [u'1.1345', u'0.2225', u'0.196', u'0.4425', u'0.4775', u'1.0835', u'0.97', u'0.874', u'1.1155', u'0.872'], [10L, 8L, 8L, 7L, 7L, 7L, 7L, 7L, 7L, 7L]),\n",
+ " (None, None, u'shucked_weight', 6, u'float8', 4177L, 1515L, 0L, None, 0.0, None, 4177L, 0L, 0L, 0.359367488628202, 0.0492675507435241, [0.352636105342962, 0.366098871913441], 0.001, 1.488, 0.186, 0.336, 0.502, [u'0.175', u'0.2505', u'0.21', u'0.419', u'0.2', u'0.097', u'0.165', u'0.096', u'0.0745', u'0.2025'], [11L, 10L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L]),\n",
+ " (None, None, u'viscera_weight', 7, u'float8', 4177L, 880L, 0L, None, 0.0, None, 4177L, 0L, 0L, 0.180593607852526, 0.0120152838599929, [0.17726937948362, 0.183917836221431], 0.0005, 0.76, 0.0935, 0.171, 0.253, [u'0.1715', u'0.196', u'0.0575', u'0.2195', u'0.061', u'0.037', u'0.096', u'0.1405', u'0.099', u'0.0265'], [15L, 14L, 13L, 13L, 13L, 13L, 12L, 12L, 12L, 12L]),\n",
+ " (None, None, u'shell_weight', 8, u'float8', 4177L, 926L, 0L, None, 0.0, None, 4177L, 0L, 0L, 0.238830859468518, 0.0193773832021588, [0.234609314715373, 0.243052404221663], 0.0015, 1.005, 0.13, 0.234, 0.329, [u'0.275', u'0.25', u'0.185', u'0.265', u'0.315', u'0.3', u'0.17', u'0.285', u'0.175', u'0.22'], [43L, 42L, 40L, 40L, 40L, 37L, 37L, 37L, 36L, 36L]),\n",
+ " (None, None, u'rings', 9, u'int4', 4177L, 28L, 0L, None, 0.0, None, 4177L, 0L, 0L, 9.93368446253292, 10.3952659473471, [9.83590635305212, 10.0314625720137], 1.0, 29.0, 8.0, 9.0, 11.0, [u'9', u'10', u'8', u'11', u'7', u'12', u'6', u'13', u'14', u'5'], [689L, 634L, 568L, 487L, 391L, 267L, 259L, 203L, 126L, 115L]),\n",
+ " (None, None, u'age', 10, u'numeric', 4177L, 28L, 0L, None, 0.0, None, 4177L, 0L, 0L, 11.4336844625329, 10.3952659473471, [11.3359063530521, 11.5314625720137], 2.5, 30.5, 9.5, 10.5, 12.5, [u'10.5', u'11.5', u'9.5', u'12.5', u'8.5', u'13.5', u'7.5', u'14.5', u'15.5', u'6.5'], [689L, 634L, 568L, 487L, 391L, 267L, 259L, 203L, 126L, 115L]),\n",
+ " (None, None, u'mature', 11, u'int4', 4177L, 2L, 0L, None, 0.0, None, 2770L, 0L, 1407L, 0.663155374670816, 0.223433815172854, [0.648820355293342, 0.67749039404829], 0.0, 1.0, 0.0, 1.0, 1.0, [u'1', u'0'], [2770L, 1407L]),\n",
+ " (None, None, u'sex_f', 12, u'int4', 4177L, 2L, 0L, None, 0.0, None, 1307L, 0L, 2870L, 0.31290399808475, 0.21504656956495, [0.298840605732167, 0.326967390437333], 0.0, 1.0, 0.0, 0.0, 1.0, [u'0', u'1'], [2870L, 1307L]),\n",
+ " (None, None, u'sex_i', 13, u'int4', 4177L, 2L, 0L, None, 0.0, None, 1342L, 0L, 2835L, 0.321283217620302, 0.218112529203438, [0.30711992784858, 0.335446507392023], 0.0, 1.0, 0.0, 0.0, 1.0, [u'0', u'1'], [2835L, 1342L]),\n",
+ " (None, None, u'sex_m', 14, u'int4', 4177L, 2L, 0L, None, 0.0, None, 1528L, 0L, 2649L, 0.365812784294949, 0.232049345210086, [0.351204002328337, 0.380421566261561], 0.0, 1.0, 0.0, 0.0, 1.0, [u'0', u'1'], [2649L, 1528L])]"
+ ]
+ },
+ "execution_count": 12,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_summary ;\n",
+ "SELECT madlib.summary (\n",
+ " 'abalone_encoded', -- source_table\n",
+ " 'abalone_summary', -- output_table\n",
+ " NULL, -- target_cols\n",
+ " NULL, -- grouping_cols\n",
+ " TRUE, -- get_distinct\n",
+ " TRUE, -- get_quartiles\n",
+ " NULL, -- quantile_array\n",
+ " 10, -- how_many_mfv\n",
+ " FALSE -- get_estimate\n",
+ ")\n",
+ ";\n",
+ "\n",
+ "SELECT * FROM abalone_summary LIMIT 15\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Correlation\n",
+ "Another aspect of the data that we might want to know about is the correlation between different columns. We turn again to MADlib to provide a ready made function: [correlation()](http://madlib.apache.org/docs/latest/group__grp__correlation.html)."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 13,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>correlation</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>Summary for 'Correlation' function<br>Output table = abalone_correlations<br>Grouping columns: sex_f,sex_i,sex_m<br>Producing correlation for columns: length,diameter,height,whole_weight,shucked_weight,viscera_weight,shell_weight,rings<br>Total run time = ('abalone_correlations', 8, 0.21109700202941895)</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u\"Summary for 'Correlation' function\\nOutput table = abalone_correlations\\nGrouping columns: sex_f,sex_i,sex_m\\nProducing correlation for columns: length,diameter,height,whole_weight,shucked_weight,viscera_weight,shell_weight,rings\\nTotal run time = ('abalone_correlations', 8, 0.21109700202941895)\",)]"
+ ]
+ },
+ "execution_count": 13,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_correlations;\n",
+ "DROP TABLE IF EXISTS abalone_correlations_summary;\n",
+ "\n",
+ "SELECT\n",
+ "madlib.correlation(\n",
+ " 'abalone_encoded', -- source_table,\n",
+ " 'abalone_correlations', -- output_table,\n",
+ " 'length,diameter,height,whole_weight,shucked_weight,viscera_weight,shell_weight,rings', -- target_cols,\n",
+ " TRUE, -- verbose,\n",
+ " 'sex_f,sex_i,sex_m' -- grouping_columns\n",
+ ");"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 14,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "8 rows affected.\n"
+ ]
+ }
+ ],
+ "source": [
+ "Index = %sql SELECT variable FROM abalone_correlations WHERE sex_m = '1' ORDER BY column_position;\n",
+ "Index = Index.DataFrame();"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 15,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "length,diameter,height,whole_weight,shucked_weight,viscera_weight,shell_weight,rings\n"
+ ]
+ }
+ ],
+ "source": [
+ "columns = ','.join(','.join('%s' %x for x in y) for y in Index.values)\n",
+ "print(columns)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 16,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "8 rows affected.\n"
+ ]
+ }
+ ],
+ "source": [
+ "correlations_male = %sql select variable,{columns} from abalone_correlations where sex_m = '1' ORDER BY column_position;\n",
+ "correlations_male = correlations_male.DataFrame()"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 17,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "image/png": "iVBORw0KGgoAAAANSUhEUgAAAbIAAAFPCAYAAAAof0vbAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi41LCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvSM8oowAAIABJREFUeJzt3Xu8pXPd//HX2xg5nyJhMGhU4zQxSA5JEXKoyCEq5abuQif9bjogh453J4UahYgc6zYyoZuYCTkMYw6KNLgdkzLOxszen98f3+821yx77bX2nrWua63Z76fH9dhrXde1ruuz1xrrs79nRQRmZmbdaomqAzAzM1sUTmRmZtbVnMjMzKyrOZGZmVlXcyIzM7Ou5kRmZmZdzYnMhg1JD0p6zxBfu4Oke1sdU4N7riFpsqTnJH2vzHsPRNK5kk5p07VvkPQfQ3xt2+KyzuZE [...]
+ "text/plain": [
+ "<Figure size 432x288 with 2 Axes>"
+ ]
+ },
+ "metadata": {
+ "needs_background": "light"
+ },
+ "output_type": "display_data"
+ }
+ ],
+ "source": [
+ "corm = correlations_male.set_index('variable')\n",
+ "ax = sns.heatmap(corm)\n",
+ "ax.set_title('Correlations for Male Abalone')\n",
+ "plt.show();"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Sampling for Training and Testing\n",
+ "Ensuring predictive power in large part is the result of creating a hold-out data set that we don’t train our model with. By creating this subset of the data, we can test any model we develop against “unseen” data to prevent overfitting by our model. This has the benefit of generating a predictive model that will generalize better.\n",
+ "\n",
+ "There’s no right answer as to how much data to set aside in the test table; a 70&-30% split, weighted towards the training data, is a good rule of thumb. This process is referred to as the [train-test split](http://madlib.apache.org/docs/latest/group__grp__train__test__split.html).\n"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 18,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "Done.\n",
+ "1 rows affected.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>Number of abalone in the Training Set</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2924</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(2924L,)]"
+ ]
+ },
+ "execution_count": 18,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_classif CASCADE;\n",
+ "DROP TABLE IF EXISTS abalone_classif_train CASCADE;\n",
+ "DROP TABLE IF EXISTS abalone_classif_test CASCADE;\n",
+ "SELECT madlib.train_test_split(\n",
+ " 'abalone_encoded', -- source_table,\n",
+ " 'abalone_classif', -- output_table,\n",
+ " 0.7, -- train_proportion,\n",
+ " NULL, -- test_proportion,\n",
+ " NULL, -- grouping_cols,\n",
+ " 'id,length,diameter,height,whole_weight,shucked_weight,viscera_weight,shell_weight,sex_f,sex_i,sex_m,rings,age,mature', -- target_cols,\n",
+ " FALSE, -- with_replacement,\n",
+ " TRUE -- separate_output_tables\n",
+ ")\n",
+ ";\n",
+ "SELECT count(*) as \"Number of abalone in the Training Set\" FROM abalone_classif_train\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"classification\"></a>\n",
+ "# 5. Classification models\n",
+ "\n",
+ "<a id=\"logistic\"></a>\n",
+ "## 5a. Logistic Regression\n",
+ "\n",
+ "We’re now ready to create our first predictive model. We’ll start with a classic [logistic regression](http://madlib.apache.org/docs/latest/group__grp__logreg.html).\n",
+ "\n",
+ "Note: we drop one of the 1-hot-encoded variables to remove perfect colinearity"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 19,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "1 rows affected.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>method</th>\n",
+ " <th>source_table</th>\n",
+ " <th>out_table</th>\n",
+ " <th>dependent_varname</th>\n",
+ " <th>independent_varname</th>\n",
+ " <th>optimizer_params</th>\n",
+ " <th>num_all_groups</th>\n",
+ " <th>num_failed_groups</th>\n",
+ " <th>num_rows_processed</th>\n",
+ " <th>num_missing_rows_skipped</th>\n",
+ " <th>grouping_col</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>logregr</td>\n",
+ " <td>abalone_classif_train</td>\n",
+ " <td>abalone_logreg_model</td>\n",
+ " <td>mature</td>\n",
+ " <td>ARRAY[<br> 1,<br> length,<br> diameter,<br> height,<br> whole_weight,<br> shucked_weight,<br> viscera_weight,<br> shell_weight,<br> sex_f,<br> sex_m<br> ]</td>\n",
+ " <td>optimizer=irls, max_iter=20, tolerance=0.0001</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>2924</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'logregr', u'abalone_classif_train', u'abalone_logreg_model', u'mature', u'ARRAY[\\n 1,\\n length,\\n diameter,\\n height,\\n whole_weight,\\n shucked_weight,\\n viscera_weight,\\n shell_weight,\\n sex_f,\\n sex_m\\n ]', u'optimizer=irls, max_iter=20, tolerance=0.0001', 1, 0, 2924, 0, None)]"
+ ]
+ },
+ "execution_count": 19,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_logreg_model;\n",
+ "DROP TABLE IF EXISTS abalone_logreg_model_summary;\n",
+ "\n",
+ "SELECT\n",
+ " madlib.logregr_train( --- Train the Logistic Regression Model\n",
+ " 'abalone_classif_train', -- source_table,\n",
+ " 'abalone_logreg_model', -- out_table,\n",
+ " 'mature', -- dependent_varname,\n",
+ " 'ARRAY[\n",
+ " 1,\n",
+ " length,\n",
+ " diameter,\n",
+ " height,\n",
+ " whole_weight,\n",
+ " shucked_weight,\n",
+ " viscera_weight,\n",
+ " shell_weight,\n",
+ " sex_f,\n",
+ " sex_m\n",
+ " ]' -- independent_varname,\n",
+ " --, -- grouping_cols,\n",
+ " --, -- max_iter,\n",
+ " --, -- optimizer,\n",
+ " --, -- tolerance,\n",
+ " -- verbose\n",
+ " )\n",
+ ";\n",
+ "SELECT * FROM abalone_logreg_model_summary ; --- Get the summary table"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Review model weights"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 20,
+ "metadata": {
+ "scrolled": true
+ },
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>coef</th>\n",
+ " <th>log_likelihood</th>\n",
+ " <th>std_err</th>\n",
+ " <th>z_stats</th>\n",
+ " <th>p_values</th>\n",
+ " <th>odds_ratios</th>\n",
+ " <th>condition_no</th>\n",
+ " <th>num_rows_processed</th>\n",
+ " <th>num_missing_rows_skipped</th>\n",
+ " <th>num_iterations</th>\n",
+ " <th>variance_covariance</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>[-4.67737274255474, -0.392212133228859, 6.15510638669522, 2.08463990575267, 4.87504773295541, -13.7623409061431, -1.36671380800461, 17.1547402958807, 1.01917554661788, 0.942108041681632]</td>\n",
+ " <td>-1006.98331724</td>\n",
+ " <td>[0.590840441026775, 2.97184057268197, 3.73897571658262, 2.03371620613073, 1.83990223675481, 2.03448594937021, 3.0108856938889, 2.83197606788329, 0.152140116528269, 0.139164221999888]</td>\n",
+ " <td>[-7.91647358198146, -0.131976168854476, 1.64620122013548, 1.02503972750398, 2.64962324387079, -6.76452983634679, -0.453924176124849, 6.05751598342522, 6.69892708034377, 6.76975754359042]</td>\n",
+ " <td>[2.44341625367833e-15, 0.895003141346682, 0.0997223381574991, 0.305344442807183, 0.00805815758922618, 1.33742797426474e-11, 0.649883402076758, 1.38239609928844e-09, 2.0995532130572e-11, 1.28998390096206e-11]</td>\n",
+ " <td>[0.00930342429755957, 0.675560789867617, 471.116960371319, 8.04169519208295, 130.980405143444, 1.05460854620395e-06, 0.254943377371314, 28197398.9511836, 2.77090933640556, 2.56538365793589]</td>\n",
+ " <td>121.997664048</td>\n",
+ " <td>2924</td>\n",
+ " <td>0</td>\n",
+ " <td>7</td>\n",
+ " <td>[[0.349092426752714, -0.675574747128063, -0.397935077415633, -0.180020641088026, 0.147499936799071, -0.0153052244481757, 0.122866412950121, 0.34417106132921, -0.00868455748700556, -0.0138313591702408], [-0.675574747128063, 8.83183638943869, -9.04613626962293, -0.0601147873774726, -0.411410454286909, 0.158792353760485, -0.555224429178143, 0.279153789377445, 0.0398313893638008, 0.0352908716573887], [-0.397935077415633, -9.04613626962293, 13.9799394091945, -0.51728651433 [...]
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[([-4.67737274255474, -0.392212133228859, 6.15510638669522, 2.08463990575267, 4.87504773295541, -13.7623409061431, -1.36671380800461, 17.1547402958807, 1.01917554661788, 0.942108041681632], -1006.98331724464, [0.590840441026775, 2.97184057268197, 3.73897571658262, 2.03371620613073, 1.83990223675481, 2.03448594937021, 3.0108856938889, 2.83197606788329, 0.152140116528269, 0.139164221999888], [-7.91647358198146, -0.131976168854476, 1.64620122013548, 1.02503972750398, 2.6496232438707 [...]
+ ]
+ },
+ "execution_count": 20,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT * FROM abalone_logreg_model ;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Show coefficients from model"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 21,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "1 rows affected.\n"
+ ]
+ }
+ ],
+ "source": [
+ "logreg_coefs = %sql SELECT coef FROM abalone_logreg_model ;\n",
+ "logreg_coefs = logreg_coefs.DataFrame();"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 22,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "(('intercept', -4.67737274255474),\n",
+ " ('length', -0.392212133228859),\n",
+ " ('diameter', 6.15510638669522),\n",
+ " ('height', 2.08463990575267),\n",
+ " ('whole_weight', 4.87504773295541),\n",
+ " ('shucked_weight', -13.7623409061431),\n",
+ " ('viscera_weight', -1.36671380800461),\n",
+ " ('shell_weight', 17.1547402958807),\n",
+ " ('sex_f', 1.01917554661788),\n",
+ " ('sex_m', 0.942108041681632))"
+ ]
+ },
+ "execution_count": 22,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "logreg_coef_names = (\n",
+ " 'intercept',\n",
+ " 'length',\n",
+ " 'diameter',\n",
+ " 'height',\n",
+ " 'whole_weight',\n",
+ " 'shucked_weight',\n",
+ " 'viscera_weight',\n",
+ " 'shell_weight',\n",
+ " 'sex_f',\n",
+ " 'sex_m'\n",
+ ")\n",
+ "tuple(zip(logreg_coef_names, logreg_coefs.iloc[0, 0]))"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Score the model against test data and join with the test data\n",
+ "\n",
+ "Now that we have a model with coefficients, we can make predictions on records previously unseen by the model. In the current version of MADlib (1.16), the way to predict probability using a logistic regression model is to `CROSS JOIN` the test set records with the single-row model table. A `CROSS JOIN` produces the cartesian product between all records in both tables, meaning it pairs every record from one table with every record in the other table. In Postgres/Greenplum this can b [...]
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 23,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1253 rows affected.\n",
+ "10 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>proba</th>\n",
+ " <th>mature</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.991068065265</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.826655523716</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.608566963248</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.916620487399</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.309092601567</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.516539096279</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.5456252611</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.318211176601</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.992267215094</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.831075550203</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(0.99106806526485, 1),\n",
+ " (0.826655523716257, 1),\n",
+ " (0.608566963247747, 1),\n",
+ " (0.91662048739902, 1),\n",
+ " (0.309092601566622, 1),\n",
+ " (0.516539096278762, 1),\n",
+ " (0.545625261099759, 0),\n",
+ " (0.318211176600757, 0),\n",
+ " (0.992267215093604, 1),\n",
+ " (0.831075550203186, 1)]"
+ ]
+ },
+ "execution_count": 23,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_logreg_test_proba;\n",
+ "CREATE TABLE abalone_logreg_test_proba\n",
+ "AS\n",
+ "SELECT madlib.logregr_predict_prob( --- Use the logistic regression model to estimate probability of mature\n",
+ " coef, \n",
+ " ARRAY[\n",
+ " 1,\n",
+ " length,\n",
+ " diameter,\n",
+ " height,\n",
+ " whole_weight,\n",
+ " shucked_weight,\n",
+ " viscera_weight,\n",
+ " shell_weight,\n",
+ " sex_f,\n",
+ " sex_m\n",
+ " ] \n",
+ " ) as proba,\n",
+ " test.mature\n",
+ "FROM abalone_classif_test test, abalone_logreg_model model\n",
+ ";\n",
+ "\n",
+ "SELECT * --- take a look at a few of the values\n",
+ "FROM abalone_logreg_test_proba\n",
+ "LIMIT 10\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Look at the top model calculated probability vs the actual maturity"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 24,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "10 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>probability</th>\n",
+ " <th>mature</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.999999986635</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.999999582192</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.99999808571</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.999993019587</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.99999142131</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.999989619946</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.999989488185</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.999979937474</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.999976591803</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.999971356358</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(0.999999986634653, 1),\n",
+ " (0.999999582191841, 1),\n",
+ " (0.999998085709951, 1),\n",
+ " (0.99999301958695, 1),\n",
+ " (0.999991421309669, 1),\n",
+ " (0.999989619945967, 1),\n",
+ " (0.999989488185467, 1),\n",
+ " (0.999979937474046, 1),\n",
+ " (0.999976591802924, 1),\n",
+ " (0.999971356358266, 1)]"
+ ]
+ },
+ "execution_count": 24,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT proba as probability, mature\n",
+ " FROM abalone_logreg_test_proba\n",
+ " ORDER BY probability DESC\n",
+ " LIMIT 10;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Calculate the area under the ROC"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 25,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>area_under_roc</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.89729162423337180618758011406069658496850</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(Decimal('0.89729162423337180618758011406069658496850'),)]"
+ ]
+ },
+ "execution_count": 25,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "drop table if exists abalone_logreg_test_auc;\n",
+ "SELECT\n",
+ "madlib.area_under_roc(\n",
+ " 'abalone_logreg_test_proba', -- table_in, \n",
+ " 'abalone_logreg_test_auc', --table_out,\n",
+ " 'proba', -- prediction_col, \n",
+ " 'mature' --observed_col, \n",
+ ") as result\n",
+ ";\n",
+ "SELECT *\n",
+ "FROM abalone_logreg_test_auc\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Confusion matrix"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 26,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1253 rows affected.\n",
+ "10 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>predicted</th>\n",
+ " <th>mature</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1, 1),\n",
+ " (1, 1),\n",
+ " (0, 1),\n",
+ " (1, 0),\n",
+ " (1, 1),\n",
+ " (1, 1),\n",
+ " (1, 1),\n",
+ " (1, 1),\n",
+ " (1, 1),\n",
+ " (0, 0)]"
+ ]
+ },
+ "execution_count": 26,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_logreg_test_predict ;\n",
+ "CREATE TABLE abalone_logreg_test_predict\n",
+ "AS\n",
+ "SELECT\n",
+ " (proba >= 0.5)::integer as predicted,\n",
+ " mature\n",
+ "FROM abalone_logreg_test_proba\n",
+ ";\n",
+ "SELECT * \n",
+ " FROM abalone_logreg_test_predict\n",
+ " LIMIT 10\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 27,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "2 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>row_id</th>\n",
+ " <th>Mature</th>\n",
+ " <th>Predicted: Not Mature</th>\n",
+ " <th>Mature_1</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>306</td>\n",
+ " <td>123</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2</td>\n",
+ " <td>1</td>\n",
+ " <td>96</td>\n",
+ " <td>728</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1L, 0, Decimal('306'), Decimal('123')),\n",
+ " (2L, 1, Decimal('96'), Decimal('728'))]"
+ ]
+ },
+ "execution_count": 27,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_logreg_test_conf_matrix;\n",
+ "SELECT\n",
+ "madlib.confusion_matrix(\n",
+ " 'abalone_logreg_test_predict', -- table_in\n",
+ " 'abalone_logreg_test_conf_matrix', -- table_out\n",
+ " 'predicted', --prediction_col\n",
+ " 'mature' --observation_col\n",
+ ")\n",
+ ";\n",
+ "SELECT --- display the confusion matrix\n",
+ " row_id,\n",
+ " class as \"Mature\",\n",
+ " confusion_arr[1] as \"Predicted: Not Mature\",\n",
+ " confusion_arr[2] as \"Mature\"\n",
+ "FROM abalone_logreg_test_conf_matrix\n",
+ "ORDER BY row_id\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Get ROC values (thresholds, true-positives, false-positives)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 28,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>count</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1253</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1253L,)]"
+ ]
+ },
+ "execution_count": 28,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_logreg_test_binary_metrics ;\n",
+ "SELECT\n",
+ "madlib.binary_classifier(\n",
+ " 'abalone_logreg_test_proba', -- table_in\n",
+ " 'abalone_logreg_test_binary_metrics', -- table_out\n",
+ " 'proba', --prediction_col\n",
+ " 'mature' --observation_col\n",
+ ")\n",
+ ";\n",
+ "select count(*) from abalone_logreg_test_binary_metrics\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 29,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "19 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>threshold</th>\n",
+ " <th>tp</th>\n",
+ " <th>fp</th>\n",
+ " <th>fn</th>\n",
+ " <th>tn</th>\n",
+ " <th>tpr</th>\n",
+ " <th>tnr</th>\n",
+ " <th>ppv</th>\n",
+ " <th>npv</th>\n",
+ " <th>fpr</th>\n",
+ " <th>fdr</th>\n",
+ " <th>fnr</th>\n",
+ " <th>acc</th>\n",
+ " <th>f1</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.480496919575</td>\n",
+ " <td>735</td>\n",
+ " <td>130</td>\n",
+ " <td>89</td>\n",
+ " <td>299</td>\n",
+ " <td>0.891990291262</td>\n",
+ " <td>0.69696969697</td>\n",
+ " <td>0.849710982659</td>\n",
+ " <td>0.770618556701</td>\n",
+ " <td>0.30303030303</td>\n",
+ " <td>0.150289017341</td>\n",
+ " <td>0.108009708738</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.87033747779751332149</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.481956373088</td>\n",
+ " <td>734</td>\n",
+ " <td>130</td>\n",
+ " <td>90</td>\n",
+ " <td>299</td>\n",
+ " <td>0.890776699029</td>\n",
+ " <td>0.69696969697</td>\n",
+ " <td>0.849537037037</td>\n",
+ " <td>0.768637532134</td>\n",
+ " <td>0.30303030303</td>\n",
+ " <td>0.150462962963</td>\n",
+ " <td>0.109223300971</td>\n",
+ " <td>0.824421388667</td>\n",
+ " <td>0.86966824644549763033</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.484870083561</td>\n",
+ " <td>734</td>\n",
+ " <td>129</td>\n",
+ " <td>90</td>\n",
+ " <td>300</td>\n",
+ " <td>0.890776699029</td>\n",
+ " <td>0.699300699301</td>\n",
+ " <td>0.850521436848</td>\n",
+ " <td>0.769230769231</td>\n",
+ " <td>0.300699300699</td>\n",
+ " <td>0.149478563152</td>\n",
+ " <td>0.109223300971</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.87018375815056312982</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.485451087833</td>\n",
+ " <td>734</td>\n",
+ " <td>128</td>\n",
+ " <td>90</td>\n",
+ " <td>301</td>\n",
+ " <td>0.890776699029</td>\n",
+ " <td>0.701631701632</td>\n",
+ " <td>0.85150812065</td>\n",
+ " <td>0.769820971867</td>\n",
+ " <td>0.298368298368</td>\n",
+ " <td>0.14849187935</td>\n",
+ " <td>0.109223300971</td>\n",
+ " <td>0.826017557861</td>\n",
+ " <td>0.87069988137603795967</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.485865382954</td>\n",
+ " <td>733</td>\n",
+ " <td>128</td>\n",
+ " <td>91</td>\n",
+ " <td>301</td>\n",
+ " <td>0.889563106796</td>\n",
+ " <td>0.701631701632</td>\n",
+ " <td>0.851335656214</td>\n",
+ " <td>0.767857142857</td>\n",
+ " <td>0.298368298368</td>\n",
+ " <td>0.148664343786</td>\n",
+ " <td>0.110436893204</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.87002967359050445104</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.487008367123</td>\n",
+ " <td>732</td>\n",
+ " <td>128</td>\n",
+ " <td>92</td>\n",
+ " <td>301</td>\n",
+ " <td>0.888349514563</td>\n",
+ " <td>0.701631701632</td>\n",
+ " <td>0.851162790698</td>\n",
+ " <td>0.765903307888</td>\n",
+ " <td>0.298368298368</td>\n",
+ " <td>0.148837209302</td>\n",
+ " <td>0.111650485437</td>\n",
+ " <td>0.824421388667</td>\n",
+ " <td>0.86935866983372921615</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.490888785527</td>\n",
+ " <td>732</td>\n",
+ " <td>127</td>\n",
+ " <td>92</td>\n",
+ " <td>302</td>\n",
+ " <td>0.888349514563</td>\n",
+ " <td>0.703962703963</td>\n",
+ " <td>0.852153667055</td>\n",
+ " <td>0.766497461929</td>\n",
+ " <td>0.296037296037</td>\n",
+ " <td>0.147846332945</td>\n",
+ " <td>0.111650485437</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.86987522281639928699</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.491507015169</td>\n",
+ " <td>731</td>\n",
+ " <td>127</td>\n",
+ " <td>93</td>\n",
+ " <td>302</td>\n",
+ " <td>0.88713592233</td>\n",
+ " <td>0.703962703963</td>\n",
+ " <td>0.851981351981</td>\n",
+ " <td>0.764556962025</td>\n",
+ " <td>0.296037296037</td>\n",
+ " <td>0.148018648019</td>\n",
+ " <td>0.11286407767</td>\n",
+ " <td>0.824421388667</td>\n",
+ " <td>0.86920332936979785969</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.49466862411</td>\n",
+ " <td>731</td>\n",
+ " <td>126</td>\n",
+ " <td>93</td>\n",
+ " <td>303</td>\n",
+ " <td>0.88713592233</td>\n",
+ " <td>0.706293706294</td>\n",
+ " <td>0.852975495916</td>\n",
+ " <td>0.765151515152</td>\n",
+ " <td>0.293706293706</td>\n",
+ " <td>0.147024504084</td>\n",
+ " <td>0.11286407767</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.86972040452111838192</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.496203693043</td>\n",
+ " <td>731</td>\n",
+ " <td>125</td>\n",
+ " <td>93</td>\n",
+ " <td>304</td>\n",
+ " <td>0.88713592233</td>\n",
+ " <td>0.708624708625</td>\n",
+ " <td>0.853971962617</td>\n",
+ " <td>0.765743073048</td>\n",
+ " <td>0.291375291375</td>\n",
+ " <td>0.146028037383</td>\n",
+ " <td>0.11286407767</td>\n",
+ " <td>0.826017557861</td>\n",
+ " <td>0.87023809523809523810</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.497656616358</td>\n",
+ " <td>730</td>\n",
+ " <td>125</td>\n",
+ " <td>94</td>\n",
+ " <td>304</td>\n",
+ " <td>0.885922330097</td>\n",
+ " <td>0.708624708625</td>\n",
+ " <td>0.853801169591</td>\n",
+ " <td>0.763819095477</td>\n",
+ " <td>0.291375291375</td>\n",
+ " <td>0.146198830409</td>\n",
+ " <td>0.114077669903</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.86956521739130434783</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.499008921623</td>\n",
+ " <td>729</td>\n",
+ " <td>125</td>\n",
+ " <td>95</td>\n",
+ " <td>304</td>\n",
+ " <td>0.884708737864</td>\n",
+ " <td>0.708624708625</td>\n",
+ " <td>0.853629976581</td>\n",
+ " <td>0.761904761905</td>\n",
+ " <td>0.291375291375</td>\n",
+ " <td>0.146370023419</td>\n",
+ " <td>0.115291262136</td>\n",
+ " <td>0.824421388667</td>\n",
+ " <td>0.86889153754469606675</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.499074380654</td>\n",
+ " <td>728</td>\n",
+ " <td>125</td>\n",
+ " <td>96</td>\n",
+ " <td>304</td>\n",
+ " <td>0.883495145631</td>\n",
+ " <td>0.708624708625</td>\n",
+ " <td>0.853458382181</td>\n",
+ " <td>0.76</td>\n",
+ " <td>0.291375291375</td>\n",
+ " <td>0.146541617819</td>\n",
+ " <td>0.116504854369</td>\n",
+ " <td>0.82362330407</td>\n",
+ " <td>0.86821705426356589147</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.499863656654</td>\n",
+ " <td>728</td>\n",
+ " <td>124</td>\n",
+ " <td>96</td>\n",
+ " <td>305</td>\n",
+ " <td>0.883495145631</td>\n",
+ " <td>0.710955710956</td>\n",
+ " <td>0.854460093897</td>\n",
+ " <td>0.760598503741</td>\n",
+ " <td>0.289044289044</td>\n",
+ " <td>0.145539906103</td>\n",
+ " <td>0.116504854369</td>\n",
+ " <td>0.824421388667</td>\n",
+ " <td>0.86873508353221957041</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.500382820041</td>\n",
+ " <td>728</td>\n",
+ " <td>123</td>\n",
+ " <td>96</td>\n",
+ " <td>306</td>\n",
+ " <td>0.883495145631</td>\n",
+ " <td>0.713286713287</td>\n",
+ " <td>0.855464159812</td>\n",
+ " <td>0.761194029851</td>\n",
+ " <td>0.286713286713</td>\n",
+ " <td>0.144535840188</td>\n",
+ " <td>0.116504854369</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.86925373134328358209</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.504665625386</td>\n",
+ " <td>727</td>\n",
+ " <td>123</td>\n",
+ " <td>97</td>\n",
+ " <td>306</td>\n",
+ " <td>0.882281553398</td>\n",
+ " <td>0.713286713287</td>\n",
+ " <td>0.855294117647</td>\n",
+ " <td>0.759305210918</td>\n",
+ " <td>0.286713286713</td>\n",
+ " <td>0.144705882353</td>\n",
+ " <td>0.117718446602</td>\n",
+ " <td>0.824421388667</td>\n",
+ " <td>0.86857825567502986858</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.509291045366</td>\n",
+ " <td>727</td>\n",
+ " <td>122</td>\n",
+ " <td>97</td>\n",
+ " <td>307</td>\n",
+ " <td>0.882281553398</td>\n",
+ " <td>0.715617715618</td>\n",
+ " <td>0.856301531213</td>\n",
+ " <td>0.759900990099</td>\n",
+ " <td>0.284382284382</td>\n",
+ " <td>0.143698468787</td>\n",
+ " <td>0.117718446602</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.86909742976688583383</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.516539096279</td>\n",
+ " <td>727</td>\n",
+ " <td>121</td>\n",
+ " <td>97</td>\n",
+ " <td>308</td>\n",
+ " <td>0.882281553398</td>\n",
+ " <td>0.717948717949</td>\n",
+ " <td>0.857311320755</td>\n",
+ " <td>0.76049382716</td>\n",
+ " <td>0.282051282051</td>\n",
+ " <td>0.142688679245</td>\n",
+ " <td>0.117718446602</td>\n",
+ " <td>0.826017557861</td>\n",
+ " <td>0.86961722488038277512</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.517108140708</td>\n",
+ " <td>726</td>\n",
+ " <td>121</td>\n",
+ " <td>98</td>\n",
+ " <td>308</td>\n",
+ " <td>0.881067961165</td>\n",
+ " <td>0.717948717949</td>\n",
+ " <td>0.857142857143</td>\n",
+ " <td>0.758620689655</td>\n",
+ " <td>0.282051282051</td>\n",
+ " <td>0.142857142857</td>\n",
+ " <td>0.118932038835</td>\n",
+ " <td>0.825219473264</td>\n",
+ " <td>0.86894075403949730700</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(0.480496919574974, Decimal('735'), Decimal('130'), Decimal('89'), Decimal('299'), 0.891990291262136, 0.696969696969697, 0.84971098265896, 0.770618556701031, 0.303030303030303, 0.15028901734104, 0.108009708737864, 0.825219473264166, Decimal('0.87033747779751332149')),\n",
+ " (0.481956373087644, Decimal('734'), Decimal('130'), Decimal('90'), Decimal('299'), 0.890776699029126, 0.696969696969697, 0.849537037037037, 0.768637532133676, 0.303030303030303, 0.150462962962963, 0.109223300970874, 0.824421388667199, Decimal('0.86966824644549763033')),\n",
+ " (0.484870083560699, Decimal('734'), Decimal('129'), Decimal('90'), Decimal('300'), 0.890776699029126, 0.699300699300699, 0.850521436848204, 0.769230769230769, 0.300699300699301, 0.149478563151796, 0.109223300970874, 0.825219473264166, Decimal('0.87018375815056312982')),\n",
+ " (0.485451087833237, Decimal('734'), Decimal('128'), Decimal('90'), Decimal('301'), 0.890776699029126, 0.701631701631702, 0.851508120649652, 0.769820971867008, 0.298368298368298, 0.148491879350348, 0.109223300970874, 0.826017557861133, Decimal('0.87069988137603795967')),\n",
+ " (0.485865382953973, Decimal('733'), Decimal('128'), Decimal('91'), Decimal('301'), 0.889563106796116, 0.701631701631702, 0.851335656213705, 0.767857142857143, 0.298368298368298, 0.148664343786295, 0.110436893203884, 0.825219473264166, Decimal('0.87002967359050445104')),\n",
+ " (0.487008367123115, Decimal('732'), Decimal('128'), Decimal('92'), Decimal('301'), 0.888349514563107, 0.701631701631702, 0.851162790697674, 0.765903307888041, 0.298368298368298, 0.148837209302326, 0.111650485436893, 0.824421388667199, Decimal('0.86935866983372921615')),\n",
+ " (0.490888785527338, Decimal('732'), Decimal('127'), Decimal('92'), Decimal('302'), 0.888349514563107, 0.703962703962704, 0.852153667054715, 0.766497461928934, 0.296037296037296, 0.147846332945285, 0.111650485436893, 0.825219473264166, Decimal('0.86987522281639928699')),\n",
+ " (0.491507015168975, Decimal('731'), Decimal('127'), Decimal('93'), Decimal('302'), 0.887135922330097, 0.703962703962704, 0.851981351981352, 0.764556962025316, 0.296037296037296, 0.148018648018648, 0.112864077669903, 0.824421388667199, Decimal('0.86920332936979785969')),\n",
+ " (0.494668624110227, Decimal('731'), Decimal('126'), Decimal('93'), Decimal('303'), 0.887135922330097, 0.706293706293706, 0.852975495915986, 0.765151515151515, 0.293706293706294, 0.147024504084014, 0.112864077669903, 0.825219473264166, Decimal('0.86972040452111838192')),\n",
+ " (0.49620369304347, Decimal('731'), Decimal('125'), Decimal('93'), Decimal('304'), 0.887135922330097, 0.708624708624709, 0.853971962616822, 0.765743073047859, 0.291375291375291, 0.146028037383178, 0.112864077669903, 0.826017557861133, Decimal('0.87023809523809523810')),\n",
+ " (0.497656616357738, Decimal('730'), Decimal('125'), Decimal('94'), Decimal('304'), 0.885922330097087, 0.708624708624709, 0.853801169590643, 0.763819095477387, 0.291375291375291, 0.146198830409357, 0.114077669902913, 0.825219473264166, Decimal('0.86956521739130434783')),\n",
+ " (0.499008921622982, Decimal('729'), Decimal('125'), Decimal('95'), Decimal('304'), 0.884708737864078, 0.708624708624709, 0.853629976580796, 0.761904761904762, 0.291375291375291, 0.146370023419204, 0.115291262135922, 0.824421388667199, Decimal('0.86889153754469606675')),\n",
+ " (0.499074380653872, Decimal('728'), Decimal('125'), Decimal('96'), Decimal('304'), 0.883495145631068, 0.708624708624709, 0.853458382180539, 0.76, 0.291375291375291, 0.146541617819461, 0.116504854368932, 0.823623304070231, Decimal('0.86821705426356589147')),\n",
+ " (0.499863656653757, Decimal('728'), Decimal('124'), Decimal('96'), Decimal('305'), 0.883495145631068, 0.710955710955711, 0.854460093896714, 0.760598503740648, 0.289044289044289, 0.145539906103286, 0.116504854368932, 0.824421388667199, Decimal('0.86873508353221957041')),\n",
+ " (0.500382820040981, Decimal('728'), Decimal('123'), Decimal('96'), Decimal('306'), 0.883495145631068, 0.713286713286713, 0.855464159811986, 0.761194029850746, 0.286713286713287, 0.144535840188014, 0.116504854368932, 0.825219473264166, Decimal('0.86925373134328358209')),\n",
+ " (0.504665625386371, Decimal('727'), Decimal('123'), Decimal('97'), Decimal('306'), 0.882281553398058, 0.713286713286713, 0.855294117647059, 0.759305210918114, 0.286713286713287, 0.144705882352941, 0.117718446601942, 0.824421388667199, Decimal('0.86857825567502986858')),\n",
+ " (0.509291045365549, Decimal('727'), Decimal('122'), Decimal('97'), Decimal('307'), 0.882281553398058, 0.715617715617716, 0.856301531213192, 0.75990099009901, 0.284382284382284, 0.143698468786808, 0.117718446601942, 0.825219473264166, Decimal('0.86909742976688583383')),\n",
+ " (0.516539096278762, Decimal('727'), Decimal('121'), Decimal('97'), Decimal('308'), 0.882281553398058, 0.717948717948718, 0.857311320754717, 0.760493827160494, 0.282051282051282, 0.142688679245283, 0.117718446601942, 0.826017557861133, Decimal('0.86961722488038277512')),\n",
+ " (0.517108140708033, Decimal('726'), Decimal('121'), Decimal('98'), Decimal('308'), 0.881067961165049, 0.717948717948718, 0.857142857142857, 0.758620689655172, 0.282051282051282, 0.142857142857143, 0.118932038834951, 0.825219473264166, Decimal('0.86894075403949730700'))]"
+ ]
+ },
+ "execution_count": 29,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT *\n",
+ "FROM abalone_logreg_test_binary_metrics\n",
+ "WHERE \n",
+ " --round(threshold::numeric, 1) = 0.5\n",
+ " threshold >= 0.48 AND\n",
+ " threshold <= 0.52\n",
+ "ORDER BY threshold;"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 30,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "1253 rows affected.\n"
+ ]
+ }
+ ],
+ "source": [
+ "logreg_metrics = %sql SELECT * FROM abalone_logreg_test_binary_metrics ORDER BY threshold;\n",
+ "logreg_metrics = logreg_metrics.DataFrame();"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 31,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXwAAAEKCAYAAAARnO4WAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi41LCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvSM8oowAAG+xJREFUeJzt3Xt0VfWd9/H3NzdCIARJuAiJEAoqCIo0Qlu8oHYUtcpjtY5Yx6njiKsO7czUOst52mk79nmeXrR1phVqaevqZcRbp+OkFcpUK4uicklVkItguAjhToBAgFzP9/njHJIQE3IIJ2fnnP15rcXy7L1/Z5+vv5V88jv78tvm7oiISPrLCLoAERFJDgW+iEhIKPBFREJCgS8iEhIKfBGRkFDgi4iERJeBb2ZPm9k+M1vbyXYzsx+YWaWZrTGzyYkvU0REzlY8I/yfAzNOs/0G [...]
+ "text/plain": [
+ "<Figure size 432x288 with 1 Axes>"
+ ]
+ },
+ "metadata": {
+ "needs_background": "light"
+ },
+ "output_type": "display_data"
+ }
+ ],
+ "source": [
+ "logreg_metrics.plot('fpr', 'tpr',xlim=(0.,1.),ylim=(0.,1.));"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"forest\"></a>\n",
+ "## 5b. Random Forest "
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 32,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "Done.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>forest_train</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td></td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[('',)]"
+ ]
+ },
+ "execution_count": 32,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_rf_model;\n",
+ "DROP TABLE IF EXISTS abalone_rf_model_group;\n",
+ "DROP TABLE IF EXISTS abalone_rf_model_summary;\n",
+ "SELECT\n",
+ "madlib.forest_train(\n",
+ " 'abalone_classif_train', -- training_table_name\n",
+ " 'abalone_rf_model', -- output_table_name\n",
+ " 'id', -- id_col_name\n",
+ " 'mature', -- dependent_variable\n",
+ " 'length,diameter,height,whole_weight,shucked_weight,viscera_weight,shell_weight,sex_f,sex_m', -- list_of_features\n",
+ " NULL, -- list_of_features_to_exclude\n",
+ " NULL, -- grouping_columns\n",
+ " 10 -- number of trees\n",
+ ");"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "View one of the trees in the forest. You could also view the tree in dot format, though for random forest tree visualization is less relevant."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 33,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>get_tree</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>digraph \"Classification tree for abalone_classif_train\" {<br>\"0\" [label=\"length <= 0.495\\n impurity = 0.453016\\n samples = 3011\\n value = [1044 1967]\\n class = 1\", shape=ellipse];<br>\"0\" -> \"1\"[label=\"yes\"];<br>\"0\" -> \"2\"[label=\"no\"];<br>\"1\" [label=\"diameter <= 0.345\\n impurity = 0.430643\\n samples = 1125\\n value = [772 353]\\n class = 0\", shape=ellipse];<br>\"1\" -> \"3\"[label=\"yes\"];<br>\"1\" -> \"4\"[label=\"no\"];< [...]
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'digraph \"Classification tree for abalone_classif_train\" {\\n\"0\" [label=\"length <= 0.495\\\\n impurity = 0.453016\\\\n samples = 3011\\\\n value = [1044 196 ... (21658 characters truncated) ... \" -> \"1620\"[label=\"no\"];\\n\"1620\" [label=\"1\\\\n impurity = 0.0713306\\\\n samples = 27\\\\n value = [ 1 26]\",shape=box];\\n\\n} //---end of digraph--------- ',)]"
+ ]
+ },
+ "execution_count": 33,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql \n",
+ "SELECT madlib.get_tree('abalone_rf_model', 1, 1, TRUE, TRUE); "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "Get variable importance:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 34,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "9 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>feature</th>\n",
+ " <th>oob_var_importance</th>\n",
+ " <th>impurity_var_importance</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>shucked_weight</td>\n",
+ " <td>12.0025318256</td>\n",
+ " <td>20.4821989904</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>shell_weight</td>\n",
+ " <td>31.2254609526</td>\n",
+ " <td>19.8024587933</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>length</td>\n",
+ " <td>23.0067732101</td>\n",
+ " <td>11.2012793065</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>viscera_weight</td>\n",
+ " <td>3.60975085939</td>\n",
+ " <td>10.8568589594</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>whole_weight</td>\n",
+ " <td>9.63781565072</td>\n",
+ " <td>10.4829872168</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>diameter</td>\n",
+ " <td>8.83966394397</td>\n",
+ " <td>9.84865534216</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>height</td>\n",
+ " <td>0.0</td>\n",
+ " <td>9.54744115005</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>sex_m</td>\n",
+ " <td>5.96519495962</td>\n",
+ " <td>4.53823943951</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>sex_f</td>\n",
+ " <td>5.712808598</td>\n",
+ " <td>3.23988080193</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'shucked_weight', 12.0025318256043, 20.4821989904084),\n",
+ " (u'shell_weight', 31.2254609525938, 19.8024587932943),\n",
+ " (u'length', 23.0067732101024, 11.2012793064942),\n",
+ " (u'viscera_weight', 3.60975085939451, 10.8568589593852),\n",
+ " (u'whole_weight', 9.63781565071796, 10.4829872167693),\n",
+ " (u'diameter', 8.83966394396736, 9.84865534216095),\n",
+ " (u'height', 0.0, 9.54744115004596),\n",
+ " (u'sex_m', 5.96519495962168, 4.53823943950833),\n",
+ " (u'sex_f', 5.71280859799804, 3.23988080193335)]"
+ ]
+ },
+ "execution_count": 34,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_rf_importances;\n",
+ "SELECT madlib.get_var_importance(\n",
+ " 'abalone_rf_model', -- model_table\n",
+ " 'abalone_rf_importances' -- output_table\n",
+ ")\n",
+ ";\n",
+ "SELECT *\n",
+ "FROM abalone_rf_importances\n",
+ "ORDER BY impurity_var_importance DESC\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## Look at some of the predictions"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 35,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>forest_predict</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td></td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[('',)]"
+ ]
+ },
+ "execution_count": 35,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_rf_test_proba;\n",
+ "SELECT\n",
+ "madlib.forest_predict(\n",
+ " 'abalone_rf_model', -- random_forest_model\n",
+ " 'abalone_classif_test', -- new_data_table\n",
+ " 'abalone_rf_test_proba', -- output_table\n",
+ " 'prob' -- type\n",
+ ")\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 36,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "5 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>estimated_prob_0</th>\n",
+ " <th>estimated_prob_1</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>10</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>16</td>\n",
+ " <td>1.0</td>\n",
+ " <td>0.0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>20</td>\n",
+ " <td>0.8</td>\n",
+ " <td>0.2</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>24</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>26</td>\n",
+ " <td>0.0</td>\n",
+ " <td>1.0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(10, 0.0, 1.0),\n",
+ " (16, 1.0, 0.0),\n",
+ " (20, 0.8, 0.2),\n",
+ " (24, 0.0, 1.0),\n",
+ " (26, 0.0, 1.0)]"
+ ]
+ },
+ "execution_count": 36,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT * \n",
+ "FROM abalone_rf_test_proba\n",
+ "LIMIT 5"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 37,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1253 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/plain": [
+ "[]"
+ ]
+ },
+ "execution_count": 37,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_rf_test_predict_actual;\n",
+ "CREATE TABLE abalone_rf_test_predict_actual\n",
+ "AS\n",
+ "SELECT \n",
+ " test.id,\n",
+ " prob.estimated_prob_1,\n",
+ " prob.estimated_prob_1 >= 0.5 as predicted_class,\n",
+ " test.mature as actual_class\n",
+ "FROM \n",
+ " abalone_rf_test_proba prob\n",
+ "INNER JOIN\n",
+ " abalone_classif_test test\n",
+ "ON\n",
+ " prob.id = test.id"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 38,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "11 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>threshold</th>\n",
+ " <th>tp</th>\n",
+ " <th>fp</th>\n",
+ " <th>fn</th>\n",
+ " <th>tn</th>\n",
+ " <th>tpr</th>\n",
+ " <th>tnr</th>\n",
+ " <th>ppv</th>\n",
+ " <th>npv</th>\n",
+ " <th>fpr</th>\n",
+ " <th>fdr</th>\n",
+ " <th>fnr</th>\n",
+ " <th>acc</th>\n",
+ " <th>f1</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.0</td>\n",
+ " <td>824</td>\n",
+ " <td>429</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>0.657621707901</td>\n",
+ " <td>None</td>\n",
+ " <td>1.0</td>\n",
+ " <td>0.342378292099</td>\n",
+ " <td>0.0</td>\n",
+ " <td>0.657621707901</td>\n",
+ " <td>0.79345209436687530091</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.1</td>\n",
+ " <td>807</td>\n",
+ " <td>263</td>\n",
+ " <td>17</td>\n",
+ " <td>166</td>\n",
+ " <td>0.979368932039</td>\n",
+ " <td>0.386946386946</td>\n",
+ " <td>0.754205607477</td>\n",
+ " <td>0.907103825137</td>\n",
+ " <td>0.613053613054</td>\n",
+ " <td>0.245794392523</td>\n",
+ " <td>0.0206310679612</td>\n",
+ " <td>0.776536312849</td>\n",
+ " <td>0.85216473072861668427</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.2</td>\n",
+ " <td>800</td>\n",
+ " <td>226</td>\n",
+ " <td>24</td>\n",
+ " <td>203</td>\n",
+ " <td>0.970873786408</td>\n",
+ " <td>0.473193473193</td>\n",
+ " <td>0.779727095517</td>\n",
+ " <td>0.894273127753</td>\n",
+ " <td>0.526806526807</td>\n",
+ " <td>0.220272904483</td>\n",
+ " <td>0.0291262135922</td>\n",
+ " <td>0.800478850758</td>\n",
+ " <td>0.86486486486486486486</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.3</td>\n",
+ " <td>787</td>\n",
+ " <td>198</td>\n",
+ " <td>37</td>\n",
+ " <td>231</td>\n",
+ " <td>0.955097087379</td>\n",
+ " <td>0.538461538462</td>\n",
+ " <td>0.798984771574</td>\n",
+ " <td>0.861940298507</td>\n",
+ " <td>0.461538461538</td>\n",
+ " <td>0.201015228426</td>\n",
+ " <td>0.0449029126214</td>\n",
+ " <td>0.812450119713</td>\n",
+ " <td>0.87009397457158651189</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.4</td>\n",
+ " <td>769</td>\n",
+ " <td>167</td>\n",
+ " <td>55</td>\n",
+ " <td>262</td>\n",
+ " <td>0.933252427184</td>\n",
+ " <td>0.610722610723</td>\n",
+ " <td>0.821581196581</td>\n",
+ " <td>0.826498422713</td>\n",
+ " <td>0.389277389277</td>\n",
+ " <td>0.178418803419</td>\n",
+ " <td>0.0667475728155</td>\n",
+ " <td>0.822825219473</td>\n",
+ " <td>0.87386363636363636364</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.5</td>\n",
+ " <td>743</td>\n",
+ " <td>148</td>\n",
+ " <td>81</td>\n",
+ " <td>281</td>\n",
+ " <td>0.901699029126</td>\n",
+ " <td>0.655011655012</td>\n",
+ " <td>0.833894500561</td>\n",
+ " <td>0.776243093923</td>\n",
+ " <td>0.344988344988</td>\n",
+ " <td>0.166105499439</td>\n",
+ " <td>0.0983009708738</td>\n",
+ " <td>0.817238627294</td>\n",
+ " <td>0.86647230320699708455</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.6</td>\n",
+ " <td>719</td>\n",
+ " <td>119</td>\n",
+ " <td>105</td>\n",
+ " <td>310</td>\n",
+ " <td>0.872572815534</td>\n",
+ " <td>0.722610722611</td>\n",
+ " <td>0.85799522673</td>\n",
+ " <td>0.746987951807</td>\n",
+ " <td>0.277389277389</td>\n",
+ " <td>0.14200477327</td>\n",
+ " <td>0.127427184466</td>\n",
+ " <td>0.821229050279</td>\n",
+ " <td>0.86522262334536702768</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.7</td>\n",
+ " <td>690</td>\n",
+ " <td>97</td>\n",
+ " <td>134</td>\n",
+ " <td>332</td>\n",
+ " <td>0.837378640777</td>\n",
+ " <td>0.773892773893</td>\n",
+ " <td>0.876747141042</td>\n",
+ " <td>0.712446351931</td>\n",
+ " <td>0.226107226107</td>\n",
+ " <td>0.123252858958</td>\n",
+ " <td>0.162621359223</td>\n",
+ " <td>0.815642458101</td>\n",
+ " <td>0.85661080074487895717</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.8</td>\n",
+ " <td>658</td>\n",
+ " <td>79</td>\n",
+ " <td>166</td>\n",
+ " <td>350</td>\n",
+ " <td>0.79854368932</td>\n",
+ " <td>0.815850815851</td>\n",
+ " <td>0.892808683853</td>\n",
+ " <td>0.678294573643</td>\n",
+ " <td>0.184149184149</td>\n",
+ " <td>0.107191316147</td>\n",
+ " <td>0.20145631068</td>\n",
+ " <td>0.804469273743</td>\n",
+ " <td>0.84304932735426008969</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.9</td>\n",
+ " <td>619</td>\n",
+ " <td>60</td>\n",
+ " <td>205</td>\n",
+ " <td>369</td>\n",
+ " <td>0.751213592233</td>\n",
+ " <td>0.86013986014</td>\n",
+ " <td>0.911634756996</td>\n",
+ " <td>0.642857142857</td>\n",
+ " <td>0.13986013986</td>\n",
+ " <td>0.0883652430044</td>\n",
+ " <td>0.248786407767</td>\n",
+ " <td>0.788507581804</td>\n",
+ " <td>0.82368596141051230872</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1.0</td>\n",
+ " <td>538</td>\n",
+ " <td>38</td>\n",
+ " <td>286</td>\n",
+ " <td>391</td>\n",
+ " <td>0.652912621359</td>\n",
+ " <td>0.911421911422</td>\n",
+ " <td>0.934027777778</td>\n",
+ " <td>0.577548005908</td>\n",
+ " <td>0.0885780885781</td>\n",
+ " <td>0.0659722222222</td>\n",
+ " <td>0.347087378641</td>\n",
+ " <td>0.741420590583</td>\n",
+ " <td>0.76857142857142857143</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(0.0, Decimal('824'), Decimal('429'), Decimal('0'), Decimal('0'), 1.0, 0.0, 0.657621707901037, None, 1.0, 0.342378292098962, 0.0, 0.657621707901037, Decimal('0.79345209436687530091')),\n",
+ " (0.1, Decimal('807'), Decimal('263'), Decimal('17'), Decimal('166'), 0.979368932038835, 0.386946386946387, 0.754205607476636, 0.907103825136612, 0.613053613053613, 0.245794392523364, 0.020631067961165, 0.776536312849162, Decimal('0.85216473072861668427')),\n",
+ " (0.2, Decimal('800'), Decimal('226'), Decimal('24'), Decimal('203'), 0.970873786407767, 0.473193473193473, 0.779727095516569, 0.894273127753304, 0.526806526806527, 0.220272904483431, 0.029126213592233, 0.80047885075818, Decimal('0.86486486486486486486')),\n",
+ " (0.3, Decimal('787'), Decimal('198'), Decimal('37'), Decimal('231'), 0.955097087378641, 0.538461538461538, 0.798984771573604, 0.861940298507463, 0.461538461538462, 0.201015228426396, 0.0449029126213592, 0.81245011971269, Decimal('0.87009397457158651189')),\n",
+ " (0.4, Decimal('769'), Decimal('167'), Decimal('55'), Decimal('262'), 0.933252427184466, 0.610722610722611, 0.821581196581197, 0.826498422712934, 0.389277389277389, 0.178418803418803, 0.066747572815534, 0.822825219473264, Decimal('0.87386363636363636364')),\n",
+ " (0.5, Decimal('743'), Decimal('148'), Decimal('81'), Decimal('281'), 0.901699029126214, 0.655011655011655, 0.833894500561167, 0.776243093922652, 0.344988344988345, 0.166105499438833, 0.0983009708737864, 0.817238627294493, Decimal('0.86647230320699708455')),\n",
+ " (0.6, Decimal('719'), Decimal('119'), Decimal('105'), Decimal('310'), 0.872572815533981, 0.722610722610723, 0.85799522673031, 0.746987951807229, 0.277389277389277, 0.14200477326969, 0.127427184466019, 0.82122905027933, Decimal('0.86522262334536702768')),\n",
+ " (0.7, Decimal('690'), Decimal('97'), Decimal('134'), Decimal('332'), 0.837378640776699, 0.773892773892774, 0.876747141041931, 0.71244635193133, 0.226107226107226, 0.123252858958069, 0.162621359223301, 0.815642458100559, Decimal('0.85661080074487895717')),\n",
+ " (0.8, Decimal('658'), Decimal('79'), Decimal('166'), Decimal('350'), 0.798543689320388, 0.815850815850816, 0.89280868385346, 0.678294573643411, 0.184149184149184, 0.10719131614654, 0.201456310679612, 0.804469273743017, Decimal('0.84304932735426008969')),\n",
+ " (0.9, Decimal('619'), Decimal('60'), Decimal('205'), Decimal('369'), 0.75121359223301, 0.86013986013986, 0.911634756995582, 0.642857142857143, 0.13986013986014, 0.0883652430044183, 0.24878640776699, 0.788507581803671, Decimal('0.82368596141051230872')),\n",
+ " (1.0, Decimal('538'), Decimal('38'), Decimal('286'), Decimal('391'), 0.652912621359223, 0.911421911421911, 0.934027777777778, 0.577548005908419, 0.0885780885780886, 0.0659722222222222, 0.347087378640777, 0.741420590582602, Decimal('0.76857142857142857143'))]"
+ ]
+ },
+ "execution_count": 38,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_rf_test_binary_metrics;\n",
+ "SELECT\n",
+ "madlib.binary_classifier(\n",
+ " 'abalone_rf_test_predict_actual', -- table_in\n",
+ " 'abalone_rf_test_binary_metrics', -- table_out\n",
+ " 'estimated_prob_1', --prediction_col\n",
+ " 'actual_class' --observation_col\n",
+ ")\n",
+ ";\n",
+ "SELECT * \n",
+ "FROM abalone_rf_test_binary_metrics\n",
+ "ORDER BY threshold\n",
+ "LIMIT 15\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 39,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "11 rows affected.\n"
+ ]
+ }
+ ],
+ "source": [
+ "#collect the false positive and true positive rates\n",
+ "rf_metrics = %sql SELECT fpr, tpr FROM abalone_rf_test_binary_metrics ORDER BY threshold;\n",
+ "rf_metrics = rf_metrics.DataFrame();"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 40,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "<matplotlib.axes._subplots.AxesSubplot at 0x133b46650>"
+ ]
+ },
+ "execution_count": 40,
+ "metadata": {},
+ "output_type": "execute_result"
+ },
+ {
+ "data": {
+ "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXwAAAEKCAYAAAARnO4WAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi41LCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvSM8oowAAGjZJREFUeJzt3Xt0ldWd//H3N/eQC5cQhktCAhRULvVCAG9T6E/rD20L409qYcZWu2xhxqGdaTtddVZHbe1Ml7ba9te1sJa2jq1Ti9pxKtPiZdqqqBUlKqUBBUNASVCJQBJuuX/nj3MghxDIITnJSbI/r7Wycp7n2XnON3sln+yzn+fsmLsjIiJDX0qyCxARkf6hwBcRCYQCX0QkEAp8EZFAKPBFRAKhwBcRCUS3gW9m95nZXjOrOMVxM7MfmFmlmW02swsSX6aIiPRWPCP8+4GFpzl+ [...]
+ "text/plain": [
+ "<Figure size 432x288 with 1 Axes>"
+ ]
+ },
+ "metadata": {
+ "needs_background": "light"
+ },
+ "output_type": "display_data"
+ }
+ ],
+ "source": [
+ "rf_metrics.plot('fpr', 'tpr',xlim=(0.,1.), ylim=(0.,1.))"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 41,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>area_under_roc</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>0.87614287007490890986052886029827777400595</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(Decimal('0.87614287007490890986052886029827777400595'),)]"
+ ]
+ },
+ "execution_count": 41,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_rf_test_auc CASCADE;\n",
+ "SELECT\n",
+ "madlib.area_under_roc(\n",
+ " 'abalone_rf_test_predict_actual', -- table_in\n",
+ " 'abalone_rf_test_auc', -- table_out\n",
+ " 'estimated_prob_1', --prediction_col\n",
+ " 'actual_class' --observation_col\n",
+ ") as result\n",
+ ";\n",
+ "\n",
+ "SELECT * FROM abalone_rf_test_auc ; -- look at the AUC for the random forest model"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"regression\"></a>\n",
+ "# 6. Regression\n",
+ "\n",
+ "Before our target variable was a binary one that we constructed to represent maturity. An abalone was either mature or not mature. Now let's predict its age instead of the binary target. "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"linear\"></a>\n",
+ "## 6a. Linear Regression"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 42,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "2 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>length</th>\n",
+ " <th>diameter</th>\n",
+ " <th>height</th>\n",
+ " <th>whole_weight</th>\n",
+ " <th>shucked_weight</th>\n",
+ " <th>viscera_weight</th>\n",
+ " <th>shell_weight</th>\n",
+ " <th>sex_f</th>\n",
+ " <th>sex_i</th>\n",
+ " <th>sex_m</th>\n",
+ " <th>rings</th>\n",
+ " <th>age</th>\n",
+ " <th>mature</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>0.35</td>\n",
+ " <td>0.265</td>\n",
+ " <td>0.09</td>\n",
+ " <td>0.2255</td>\n",
+ " <td>0.0995</td>\n",
+ " <td>0.0485</td>\n",
+ " <td>0.07</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>7</td>\n",
+ " <td>8.5</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>8</td>\n",
+ " <td>0.475</td>\n",
+ " <td>0.37</td>\n",
+ " <td>0.125</td>\n",
+ " <td>0.5095</td>\n",
+ " <td>0.2165</td>\n",
+ " <td>0.1125</td>\n",
+ " <td>0.165</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>9</td>\n",
+ " <td>10.5</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1, 0.35, 0.265, 0.09, 0.2255, 0.0995, 0.0485, 0.07, 0, 0, 1, 7, Decimal('8.5'), 0),\n",
+ " (8, 0.475, 0.37, 0.125, 0.5095, 0.2165, 0.1125, 0.165, 0, 0, 1, 9, Decimal('10.5'), 1)]"
+ ]
+ },
+ "execution_count": 42,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT * \n",
+ "FROM abalone_classif_train\n",
+ "LIMIT 2"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 43,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "1 rows affected.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>coef</th>\n",
+ " <th>r2</th>\n",
+ " <th>std_err</th>\n",
+ " <th>t_stats</th>\n",
+ " <th>p_values</th>\n",
+ " <th>condition_no</th>\n",
+ " <th>bp_stats</th>\n",
+ " <th>bp_p_value</th>\n",
+ " <th>num_rows_processed</th>\n",
+ " <th>num_missing_rows_skipped</th>\n",
+ " <th>variance_covariance</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>[4.410357699688, 1.27175722855722, 9.64141451253988, 9.33882415610471, 9.5183803866192, -20.1419380713264, -12.1045936650436, 8.74328089164249, 0.817371808454084, 0.876398154242832]</td>\n",
+ " <td>0.545016598</td>\n",
+ " <td>[0.317420812747666, 2.14523563920031, 2.63744840317991, 1.71067733937981, 0.892068852840643, 1.00753029831926, 1.58728493291856, 1.39432077797183, 0.123340170109636, 0.11520852987613]</td>\n",
+ " <td>[13.894355765493, 0.592828687589443, 3.65558412476067, 5.45913828465771, 10.6700064196945, -19.991396888934, -7.62597402268961, 6.27063802660992, 6.6269716324173, 7.60705960908552]</td>\n",
+ " <td>[1.51375990657579e-42, 0.55334180633369, 0.000261129452464093, 5.18665415881891e-08, 4.23697868795871e-26, 2.00173103596502e-83, 3.25682639467242e-14, 4.12820640447016e-10, 4.06638451488027e-11, 3.7597744950155e-14]</td>\n",
+ " <td>136.28036679</td>\n",
+ " <td>345.394268412</td>\n",
+ " <td>5.91944435666e-69</td>\n",
+ " <td>2924</td>\n",
+ " <td>0</td>\n",
+ " <td>[[0.100755972365389, -0.263855054760177, -0.00609972532228305, -0.0726639103174352, 0.00706375431327825, 0.0402300840383149, 0.0648270560387451, 0.0778928701063708, 0.00271915197088884, 0.000835792028445214], [-0.263855054760176, 4.60203594769516, -5.0528770327474, -0.117336658885753, -0.0025167905987354, -0.13242792154729, -0.287715350508991, 0.103398664665856, 0.0127891124036783, 0.00974470709032043], [-0.00609972532228338, -5.0528770327474, 6.95613407943628, -0.481 [...]
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[([4.410357699688, 1.27175722855722, 9.64141451253988, 9.33882415610471, 9.5183803866192, -20.1419380713264, -12.1045936650436, 8.74328089164249, 0.817371808454084, 0.876398154242832], 0.545016598000415, [0.317420812747666, 2.14523563920031, 2.63744840317991, 1.71067733937981, 0.892068852840643, 1.00753029831926, 1.58728493291856, 1.39432077797183, 0.123340170109636, 0.11520852987613], [13.894355765493, 0.592828687589443, 3.65558412476067, 5.45913828465771, 10.6700064196945, -19. [...]
+ ]
+ },
+ "execution_count": 43,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_linreg_model;\n",
+ "DROP TABLE IF EXISTS abalone_linreg_model_summary;\n",
+ "SELECT madlib.linregr_train(\n",
+ " 'abalone_classif_train', -- source_table\n",
+ " 'abalone_linreg_model', -- out_table\n",
+ " 'age', -- dependent_varname\n",
+ " 'ARRAY[\n",
+ " 1,\n",
+ " length,\n",
+ " diameter,\n",
+ " height,\n",
+ " whole_weight,\n",
+ " shucked_weight,\n",
+ " viscera_weight,\n",
+ " shell_weight,\n",
+ " sex_f,\n",
+ " sex_m\n",
+ " ]', -- independent_varname\n",
+ " NULL, -- grouping_cols\n",
+ " TRUE -- heteroskedasticity_option\n",
+ ")\n",
+ ";\n",
+ "SELECT * FROM abalone_linreg_model\n",
+ "LIMIT 10\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "### Look at the predictions from the Linear Regression Model"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 44,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1253 rows affected.\n",
+ "5 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>predicted_age</th>\n",
+ " <th>actual_age</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3918</td>\n",
+ " <td>14.0356025263</td>\n",
+ " <td>19.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>684</td>\n",
+ " <td>11.9578243725</td>\n",
+ " <td>11.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1664</td>\n",
+ " <td>10.2519022088</td>\n",
+ " <td>10.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3936</td>\n",
+ " <td>12.7607562191</td>\n",
+ " <td>14.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>654</td>\n",
+ " <td>9.80860239621</td>\n",
+ " <td>11.5</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(3918, 14.0356025263472, Decimal('19.5')),\n",
+ " (684, 11.9578243724615, Decimal('11.5')),\n",
+ " (1664, 10.2519022087522, Decimal('10.5')),\n",
+ " (3936, 12.760756219147, Decimal('14.5')),\n",
+ " (654, 9.80860239621498, Decimal('11.5'))]"
+ ]
+ },
+ "execution_count": 44,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_linreg_test_predict;\n",
+ "CREATE TABLE abalone_linreg_test_predict\n",
+ "AS\n",
+ "SELECT \n",
+ " test.id,\n",
+ " madlib.linregr_predict(\n",
+ " coef, \n",
+ " ARRAY[\n",
+ " 1,\n",
+ " length,\n",
+ " diameter,\n",
+ " height,\n",
+ " whole_weight,\n",
+ " shucked_weight,\n",
+ " viscera_weight,\n",
+ " shell_weight,\n",
+ " sex_f,\n",
+ " sex_m\n",
+ " ] \n",
+ " ) as predicted_age,\n",
+ " test.age as actual_age\n",
+ "FROM abalone_classif_test test, abalone_linreg_model model\n",
+ ";\n",
+ "SELECT * FROM abalone_linreg_test_predict\n",
+ "LIMIT 5\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 45,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>mean_squared_error</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>4.70025372012</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(4.70025372011683,)]"
+ ]
+ },
+ "execution_count": 45,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "drop table if exists abalone_linreg_test_predict_mse;\n",
+ "SELECT madlib.mean_squared_error(\n",
+ " 'abalone_linreg_test_predict', -- table_in\n",
+ " 'abalone_linreg_test_predict_mse', -- table_out\n",
+ " 'predicted_age', -- prediction_col\n",
+ " 'actual_age' -- observed_col\n",
+ ") as result\n",
+ ";\n",
+ "SELECT * FROM abalone_linreg_test_predict_mse;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "<a id=\"elastic\"></a>\n",
+ "## 6b. Elastic Net Regression\n",
+ "\n",
+ "Elastic Net Regression is linear regression with penalties assigned to the size of the coefficients. Note that \n",
+ "\n",
+ "MADlib's elastic net automatically fits an intercept, so you shouldn't include an explicit intercept column of 1's in your independent variable array."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 46,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>elastic_net_train</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td></td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[('',)]"
+ ]
+ },
+ "execution_count": 46,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_elasticnet_model CASCADE;\n",
+ "DROP TABLE IF EXISTS abalone_elasticnet_model_summary CASCADE;\n",
+ "SELECT madlib.elastic_net_train( \n",
+ " 'abalone_classif_train', -- tbl_source\n",
+ " 'abalone_elasticnet_model', -- tbl_result\n",
+ " 'age', -- col_dep_var\n",
+ " 'ARRAY[\n",
+ " length,\n",
+ " diameter,\n",
+ " height,\n",
+ " whole_weight,\n",
+ " shucked_weight,\n",
+ " viscera_weight,\n",
+ " shell_weight,\n",
+ " sex_f,\n",
+ " sex_m\n",
+ " ]', -- col_ind_var\n",
+ " 'gaussian', -- regress_family\n",
+ " 0.5, -- alpha\n",
+ " 0.5, -- lambda_value\n",
+ " TRUE -- standardize\n",
+ " --, -- grouping_col\n",
+ " --, -- optimizer\n",
+ " --, -- optimizer_params\n",
+ " --, -- excluded\n",
+ " --, -- max_iter\n",
+ " -- tolerance\n",
+ ")"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 47,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>family</th>\n",
+ " <th>features</th>\n",
+ " <th>features_selected</th>\n",
+ " <th>coef_nonzero</th>\n",
+ " <th>coef_all</th>\n",
+ " <th>intercept</th>\n",
+ " <th>log_likelihood</th>\n",
+ " <th>standardize</th>\n",
+ " <th>iteration_run</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>gaussian</td>\n",
+ " <td>[u'[1]', u'[2]', u'[3]', u'[4]', u'[5]', u'[6]', u'[7]', u'[8]', u'[9]']</td>\n",
+ " <td>[u'[1]', u'[2]', u'[3]', u'[7]', u'[8]']</td>\n",
+ " <td>[1.3220343857, 3.14984818966, 8.52839367593, 6.31126111497, 0.120956346376]</td>\n",
+ " <td>[1.3220343857, 3.14984818966, 8.52839367593, 0.0, 0.0, 0.0, 6.31126111497, 0.120956346376, 0.0]</td>\n",
+ " <td>6.74458430257</td>\n",
+ " <td>-3.83846837185</td>\n",
+ " <td>True</td>\n",
+ " <td>193</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'gaussian', [u'[1]', u'[2]', u'[3]', u'[4]', u'[5]', u'[6]', u'[7]', u'[8]', u'[9]'], [u'[1]', u'[2]', u'[3]', u'[7]', u'[8]'], [1.3220343857, 3.14984818966, 8.52839367593, 6.31126111497, 0.120956346376], [1.3220343857, 3.14984818966, 8.52839367593, 0.0, 0.0, 0.0, 6.31126111497, 0.120956346376, 0.0], 6.74458430257, -3.83846837185, True, 193)]"
+ ]
+ },
+ "execution_count": 47,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT * FROM abalone_elasticnet_model"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 48,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>method</th>\n",
+ " <th>source_table</th>\n",
+ " <th>out_table</th>\n",
+ " <th>dependent_varname</th>\n",
+ " <th>independent_varname</th>\n",
+ " <th>family</th>\n",
+ " <th>alpha</th>\n",
+ " <th>lambda_value</th>\n",
+ " <th>grouping_col</th>\n",
+ " <th>num_all_groups</th>\n",
+ " <th>num_failed_groups</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>elastic_net</td>\n",
+ " <td>abalone_classif_train</td>\n",
+ " <td>abalone_elasticnet_model</td>\n",
+ " <td>age</td>\n",
+ " <td>ARRAY[<br> length,<br> diameter,<br> height,<br> whole_weight,<br> shucked_weight,<br> viscera_weight,<br> shell_weight,<br> sex_f,<br> sex_m<br> ]</td>\n",
+ " <td>gaussian</td>\n",
+ " <td>0.5</td>\n",
+ " <td>0.5</td>\n",
+ " <td>NULL</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'elastic_net', u'abalone_classif_train', u'abalone_elasticnet_model', u'age', u'ARRAY[\\n length,\\n diameter,\\n height,\\n whole_weight,\\n shucked_weight,\\n viscera_weight,\\n shell_weight,\\n sex_f,\\n sex_m\\n ]', u'gaussian', 0.5, 0.5, u'NULL', 1, 0)]"
+ ]
+ },
+ "execution_count": 48,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT * FROM abalone_elasticnet_model_summary"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 49,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1253 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/plain": [
+ "[]"
+ ]
+ },
+ "execution_count": 49,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_elasticnet_test_predict;\n",
+ "CREATE TABLE abalone_elasticnet_test_predict\n",
+ "AS\n",
+ "SELECT \n",
+ " test.id,\n",
+ " madlib.elastic_net_gaussian_predict(\n",
+ " model.coef_all, \n",
+ " model.intercept,\n",
+ " ARRAY[\n",
+ " length,\n",
+ " diameter,\n",
+ " height,\n",
+ " whole_weight,\n",
+ " shucked_weight,\n",
+ " viscera_weight,\n",
+ " shell_weight,\n",
+ " sex_f,\n",
+ " sex_m\n",
+ " ] \n",
+ " ) as predicted_age,\n",
+ " test.age as actual_age\n",
+ "FROM abalone_classif_test test, abalone_elasticnet_model model\n",
+ ";"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 50,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "10 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>predicted_age</th>\n",
+ " <th>actual_age</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3941</td>\n",
+ " <td>12.5704131046</td>\n",
+ " <td>16.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3441</td>\n",
+ " <td>10.3744669188</td>\n",
+ " <td>8.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>92</td>\n",
+ " <td>12.9497348984</td>\n",
+ " <td>14.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>849</td>\n",
+ " <td>11.982881522</td>\n",
+ " <td>11.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>224</td>\n",
+ " <td>10.029998676</td>\n",
+ " <td>11.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>651</td>\n",
+ " <td>9.10540972598</td>\n",
+ " <td>7.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1907</td>\n",
+ " <td>11.6905236073</td>\n",
+ " <td>10.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2603</td>\n",
+ " <td>12.8194751477</td>\n",
+ " <td>10.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>592</td>\n",
+ " <td>11.5163675347</td>\n",
+ " <td>19.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>261</td>\n",
+ " <td>11.7751231628</td>\n",
+ " <td>13.5</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(3941, 12.5704131046376, Decimal('16.5')),\n",
+ " (3441, 10.374466918844, Decimal('8.5')),\n",
+ " (92, 12.9497348983795, Decimal('14.5')),\n",
+ " (849, 11.9828815219954, Decimal('11.5')),\n",
+ " (224, 10.0299986760329, Decimal('11.5')),\n",
+ " (651, 9.1054097259781, Decimal('7.5')),\n",
+ " (1907, 11.6905236073399, Decimal('10.5')),\n",
+ " (2603, 12.8194751476722, Decimal('10.5')),\n",
+ " (592, 11.5163675346797, Decimal('19.5')),\n",
+ " (261, 11.7751231627786, Decimal('13.5'))]"
+ ]
+ },
+ "execution_count": 50,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT * FROM abalone_elasticnet_test_predict LIMIT 10"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 51,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>mean_squared_error</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>6.28606344646</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(6.28606344645616,)]"
+ ]
+ },
+ "execution_count": 51,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_elasticnet_test_predict_mse;\n",
+ "SELECT madlib.mean_squared_error(\n",
+ " 'abalone_elasticnet_test_predict', -- table_in\n",
+ " 'abalone_elasticnet_test_predict_mse', -- table_out\n",
+ " 'predicted_age', -- prediction_col\n",
+ " 'actual_age' -- observed_col\n",
+ ") \n",
+ ";\n",
+ "SELECT * FROM abalone_elasticnet_test_predict_mse\n",
+ ";"
+ ]
+ }
+ ],
+ "metadata": {
+ "kernelspec": {
+ "display_name": "Python 2",
+ "language": "python",
+ "name": "python2"
+ },
+ "language_info": {
+ "codemirror_mode": {
+ "name": "ipython",
+ "version": 2
+ },
+ "file_extension": ".py",
+ "mimetype": "text/x-python",
+ "name": "python",
+ "nbconvert_exporter": "python",
+ "pygments_lexer": "ipython2",
+ "version": "2.7.16"
+ },
+ "toc": {
+ "base_numbering": 1,
+ "nav_menu": {
+ "height": "235.994px",
+ "width": "273.991px"
+ },
+ "number_sections": true,
+ "sideBar": true,
+ "skip_h1_title": false,
+ "title_cell": "Table of Contents",
+ "title_sidebar": "Contents",
+ "toc_cell": false,
+ "toc_position": {
+ "height": "calc(100% - 180px)",
+ "left": "10px",
+ "top": "150px",
+ "width": "208px"
+ },
+ "toc_section_display": true,
+ "toc_window_display": true
+ },
+ "varInspector": {
+ "cols": {
+ "lenName": 16,
+ "lenType": 16,
+ "lenVar": 40
+ },
+ "kernels_config": {
+ "python": {
+ "delete_cmd_postfix": "",
+ "delete_cmd_prefix": "del ",
+ "library": "var_list.py",
+ "varRefreshCmd": "print(var_dic_list())"
+ },
+ "r": {
+ "delete_cmd_postfix": ") ",
+ "delete_cmd_prefix": "rm(",
+ "library": "var_list.r",
+ "varRefreshCmd": "cat(var_dic_list()) "
+ }
+ },
+ "types_to_exclude": [
+ "module",
+ "function",
+ "builtin_function_or_method",
+ "instance",
+ "_Feature"
+ ],
+ "window_display": false
+ }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 2
+}
diff --git a/community-artifacts/README.md b/community-artifacts/README.md
index 3afd819..f6697bb 100644
--- a/community-artifacts/README.md
+++ b/community-artifacts/README.md
@@ -2,10 +2,10 @@
The artifacts in this repo are intended to help you get started using Apache MADlib.
-The Jupyter notebooks have been updated to work with Apache MADlib 1.17. They may also work with previous releases.
+The Jupyter notebooks have been updated to work with Apache MADlib 1.17. They may also work with previous releases. Many of the workbooks are based on examples in the [MADlib user documentation](http://madlib.apache.org/docs/latest/index.html/).
If you would like to share any of your work with the Apache MADlib community, or correct/augment any of the artifacts that are here, you are most welcome to open a pull request.
-See the project website [MADlib Home](http://madlib.apache.org/) for documentation and links to the latest binary and source packages.
+See the project website [MADlib Home](http://madlib.apache.org/) for more information.