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 2017/12/08 20:55:17 UTC
[7/7] madlib-site git commit: add new workbooks for 1dot13
add new workbooks for 1dot13
Project: http://git-wip-us.apache.org/repos/asf/madlib-site/repo
Commit: http://git-wip-us.apache.org/repos/asf/madlib-site/commit/95826612
Tree: http://git-wip-us.apache.org/repos/asf/madlib-site/tree/95826612
Diff: http://git-wip-us.apache.org/repos/asf/madlib-site/diff/95826612
Branch: refs/heads/asf-site
Commit: 95826612dd1cc6c52d8977c0582028c636013cb5
Parents: 4ddf175
Author: Frank McQuillan <fm...@pivotal.io>
Authored: Fri Dec 8 12:51:41 2017 -0800
Committer: Frank McQuillan <fm...@pivotal.io>
Committed: Fri Dec 8 12:51:41 2017 -0800
----------------------------------------------------------------------
community-artifacts/Elastic-net-v1.ipynb | 1960 ---------
community-artifacts/Elastic-net-v2.ipynb | 2078 ++++++++++
community-artifacts/HITS-v1.ipynb | 1240 ++++++
community-artifacts/Linear-regression-v1.ipynb | 1188 ++++++
.../Logistic-regression-v1.ipynb | 892 +++++
community-artifacts/Summary-v1.ipynb | 1026 +++++
community-artifacts/kNN-v1.ipynb | 497 ---
community-artifacts/kNN-v2.ipynb | 751 ++++
community-artifacts/mlp-v1.ipynb | 1525 -------
community-artifacts/mlp-v2.ipynb | 3755 ++++++++++++++++++
10 files changed, 10930 insertions(+), 3982 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/madlib-site/blob/95826612/community-artifacts/Elastic-net-v1.ipynb
----------------------------------------------------------------------
diff --git a/community-artifacts/Elastic-net-v1.ipynb b/community-artifacts/Elastic-net-v1.ipynb
deleted file mode 100644
index d7e5202..0000000
--- a/community-artifacts/Elastic-net-v1.ipynb
+++ /dev/null
@@ -1,1960 +0,0 @@
-{
- "cells": [
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# Elastic net (MADlib v1.10+)\n",
- "Demonstrates elastic net, including grouping and cross validation, which were introduced in MADlib v1.10"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 1,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stderr",
- "output_type": "stream",
- "text": [
- "/Users/fmcquillan/anaconda/lib/python2.7/site-packages/IPython/config.py:13: ShimWarning: The `IPython.config` package has been deprecated. You should import from traitlets.config instead.\n",
- " \"You should import from traitlets.config instead.\", ShimWarning)\n",
- "/Users/fmcquillan/anaconda/lib/python2.7/site-packages/IPython/utils/traitlets.py:5: UserWarning: IPython.utils.traitlets has moved to a top-level traitlets package.\n",
- " warn(\"IPython.utils.traitlets has moved to a top-level traitlets package.\")\n"
- ]
- }
- ],
- "source": [
- "%load_ext sql"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 2,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "data": {
- "text/plain": [
- "u'Connected: gpdbchina@madlib'"
- ]
- },
- "execution_count": 2,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%sql postgresql://gpdbchina@10.194.10.68:55000/madlib\n",
- "#%sql postgresql://fmcquillan@localhost:5432/madlib\n",
- "#%sql postgresql://gpadmin@54.197.30.46:10432/gpadmin"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 3,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "1 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>version</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>MADlib version: 1.10.0-dev, git revision: rel/v1.9.1-12-g6939fd6, cmake configuration time: Thu Dec 22 00:35:17 UTC 2016, build type: Release, build system: Linux-2.6.18-238.27.1.el5.hotfix.bz516490, C compiler: gcc 4.4.0, C++ compiler: g++ 4.4.0</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u'MADlib version: 1.10.0-dev, git revision: rel/v1.9.1-12-g6939fd6, cmake configuration time: Thu Dec 22 00:35:17 UTC 2016, build type: Release, build system: Linux-2.6.18-238.27.1.el5.hotfix.bz516490, C compiler: gcc 4.4.0, C++ compiler: g++ 4.4.0',)]"
- ]
- },
- "execution_count": 3,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%sql select madlib.version();\n",
- "#%sql select version();"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 1. Create data set\n",
- "House prices and characteristics."
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 4,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "Done.\n",
- "27 rows affected.\n",
- "27 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>tax</th>\n",
- " <th>bedroom</th>\n",
- " <th>bath</th>\n",
- " <th>price</th>\n",
- " <th>size</th>\n",
- " <th>lot</th>\n",
- " <th>zipcode</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>590</td>\n",
- " <td>2</td>\n",
- " <td>1.0</td>\n",
- " <td>50000</td>\n",
- " <td>770</td>\n",
- " <td>22100</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2</td>\n",
- " <td>1050</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>85000</td>\n",
- " <td>1410</td>\n",
- " <td>12000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>3</td>\n",
- " <td>20</td>\n",
- " <td>3</td>\n",
- " <td>1.0</td>\n",
- " <td>22500</td>\n",
- " <td>1060</td>\n",
- " <td>3500</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>4</td>\n",
- " <td>870</td>\n",
- " <td>2</td>\n",
- " <td>2.0</td>\n",
- " <td>90000</td>\n",
- " <td>1300</td>\n",
- " <td>17500</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>5</td>\n",
- " <td>1320</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>133000</td>\n",
- " <td>1500</td>\n",
- " <td>30000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>6</td>\n",
- " <td>1350</td>\n",
- " <td>2</td>\n",
- " <td>1.0</td>\n",
- " <td>90500</td>\n",
- " <td>820</td>\n",
- " <td>25700</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>7</td>\n",
- " <td>2790</td>\n",
- " <td>3</td>\n",
- " <td>2.5</td>\n",
- " <td>260000</td>\n",
- " <td>2130</td>\n",
- " <td>25000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>8</td>\n",
- " <td>680</td>\n",
- " <td>2</td>\n",
- " <td>1.0</td>\n",
- " <td>142500</td>\n",
- " <td>1170</td>\n",
- " <td>22000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>9</td>\n",
- " <td>1840</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>160000</td>\n",
- " <td>1500</td>\n",
- " <td>19000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>10</td>\n",
- " <td>3680</td>\n",
- " <td>4</td>\n",
- " <td>2.0</td>\n",
- " <td>240000</td>\n",
- " <td>2790</td>\n",
- " <td>20000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>11</td>\n",
- " <td>1660</td>\n",
- " <td>3</td>\n",
- " <td>1.0</td>\n",
- " <td>87000</td>\n",
- " <td>1030</td>\n",
- " <td>17500</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>12</td>\n",
- " <td>1620</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>118600</td>\n",
- " <td>1250</td>\n",
- " <td>20000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>13</td>\n",
- " <td>3100</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>140000</td>\n",
- " <td>1760</td>\n",
- " <td>38000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>14</td>\n",
- " <td>2070</td>\n",
- " <td>2</td>\n",
- " <td>3.0</td>\n",
- " <td>148000</td>\n",
- " <td>1550</td>\n",
- " <td>14000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>15</td>\n",
- " <td>650</td>\n",
- " <td>3</td>\n",
- " <td>1.5</td>\n",
- " <td>65000</td>\n",
- " <td>1450</td>\n",
- " <td>12000</td>\n",
- " <td>94301</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>16</td>\n",
- " <td>770</td>\n",
- " <td>2</td>\n",
- " <td>2.0</td>\n",
- " <td>91000</td>\n",
- " <td>1300</td>\n",
- " <td>17500</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>17</td>\n",
- " <td>1220</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>132300</td>\n",
- " <td>1500</td>\n",
- " <td>30000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>18</td>\n",
- " <td>1150</td>\n",
- " <td>2</td>\n",
- " <td>1.0</td>\n",
- " <td>91100</td>\n",
- " <td>820</td>\n",
- " <td>25700</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>19</td>\n",
- " <td>2690</td>\n",
- " <td>3</td>\n",
- " <td>2.5</td>\n",
- " <td>260011</td>\n",
- " <td>2130</td>\n",
- " <td>25000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>20</td>\n",
- " <td>780</td>\n",
- " <td>2</td>\n",
- " <td>1.0</td>\n",
- " <td>141800</td>\n",
- " <td>1170</td>\n",
- " <td>22000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>21</td>\n",
- " <td>1910</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>160900</td>\n",
- " <td>1500</td>\n",
- " <td>19000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>22</td>\n",
- " <td>3600</td>\n",
- " <td>4</td>\n",
- " <td>2.0</td>\n",
- " <td>239000</td>\n",
- " <td>2790</td>\n",
- " <td>20000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>23</td>\n",
- " <td>1600</td>\n",
- " <td>3</td>\n",
- " <td>1.0</td>\n",
- " <td>81010</td>\n",
- " <td>1030</td>\n",
- " <td>17500</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>24</td>\n",
- " <td>1590</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>117910</td>\n",
- " <td>1250</td>\n",
- " <td>20000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>25</td>\n",
- " <td>3200</td>\n",
- " <td>3</td>\n",
- " <td>2.0</td>\n",
- " <td>141100</td>\n",
- " <td>1760</td>\n",
- " <td>38000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>26</td>\n",
- " <td>2270</td>\n",
- " <td>2</td>\n",
- " <td>3.0</td>\n",
- " <td>148011</td>\n",
- " <td>1550</td>\n",
- " <td>14000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>27</td>\n",
- " <td>750</td>\n",
- " <td>3</td>\n",
- " <td>1.5</td>\n",
- " <td>66000</td>\n",
- " <td>1450</td>\n",
- " <td>12000</td>\n",
- " <td>76010</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(1, 590, 2, 1.0, 50000, 770, 22100, 94301),\n",
- " (2, 1050, 3, 2.0, 85000, 1410, 12000, 94301),\n",
- " (3, 20, 3, 1.0, 22500, 1060, 3500, 94301),\n",
- " (4, 870, 2, 2.0, 90000, 1300, 17500, 94301),\n",
- " (5, 1320, 3, 2.0, 133000, 1500, 30000, 94301),\n",
- " (6, 1350, 2, 1.0, 90500, 820, 25700, 94301),\n",
- " (7, 2790, 3, 2.5, 260000, 2130, 25000, 94301),\n",
- " (8, 680, 2, 1.0, 142500, 1170, 22000, 94301),\n",
- " (9, 1840, 3, 2.0, 160000, 1500, 19000, 94301),\n",
- " (10, 3680, 4, 2.0, 240000, 2790, 20000, 94301),\n",
- " (11, 1660, 3, 1.0, 87000, 1030, 17500, 94301),\n",
- " (12, 1620, 3, 2.0, 118600, 1250, 20000, 94301),\n",
- " (13, 3100, 3, 2.0, 140000, 1760, 38000, 94301),\n",
- " (14, 2070, 2, 3.0, 148000, 1550, 14000, 94301),\n",
- " (15, 650, 3, 1.5, 65000, 1450, 12000, 94301),\n",
- " (16, 770, 2, 2.0, 91000, 1300, 17500, 76010),\n",
- " (17, 1220, 3, 2.0, 132300, 1500, 30000, 76010),\n",
- " (18, 1150, 2, 1.0, 91100, 820, 25700, 76010),\n",
- " (19, 2690, 3, 2.5, 260011, 2130, 25000, 76010),\n",
- " (20, 780, 2, 1.0, 141800, 1170, 22000, 76010),\n",
- " (21, 1910, 3, 2.0, 160900, 1500, 19000, 76010),\n",
- " (22, 3600, 4, 2.0, 239000, 2790, 20000, 76010),\n",
- " (23, 1600, 3, 1.0, 81010, 1030, 17500, 76010),\n",
- " (24, 1590, 3, 2.0, 117910, 1250, 20000, 76010),\n",
- " (25, 3200, 3, 2.0, 141100, 1760, 38000, 76010),\n",
- " (26, 2270, 2, 3.0, 148011, 1550, 14000, 76010),\n",
- " (27, 750, 3, 1.5, 66000, 1450, 12000, 76010)]"
- ]
- },
- "execution_count": 4,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql \n",
- "DROP TABLE IF EXISTS houses;\n",
- "\n",
- "CREATE TABLE houses ( id INT,\n",
- " tax INT,\n",
- " bedroom INT,\n",
- " bath FLOAT,\n",
- " price INT,\n",
- " size INT,\n",
- " lot INT,\n",
- " zipcode INT);\n",
- "\n",
- "INSERT INTO houses (id, tax, bedroom, bath, price, size, lot, zipcode) VALUES\n",
- "(1 , 590 , 2 , 1 , 50000 , 770 , 22100 , 94301),\n",
- "(2 , 1050 , 3 , 2 , 85000 , 1410 , 12000 , 94301),\n",
- "(3 , 20 , 3 , 1 , 22500 , 1060 , 3500 , 94301),\n",
- "(4 , 870 , 2 , 2 , 90000 , 1300 , 17500 , 94301),\n",
- "(5 , 1320 , 3 , 2 , 133000 , 1500 , 30000 , 94301),\n",
- "(6 , 1350 , 2 , 1 , 90500 , 820 , 25700 , 94301),\n",
- "(7 , 2790 , 3 , 2.5 , 260000 , 2130 , 25000 , 94301),\n",
- "(8 , 680 , 2 , 1 , 142500 , 1170 , 22000 , 94301),\n",
- "(9 , 1840 , 3 , 2 , 160000 , 1500 , 19000 , 94301),\n",
- "(10 , 3680 , 4 , 2 , 240000 , 2790 , 20000 , 94301),\n",
- "(11 , 1660 , 3 , 1 , 87000 , 1030 , 17500 , 94301),\n",
- "(12 , 1620 , 3 , 2 , 118600 , 1250 , 20000 , 94301),\n",
- "(13 , 3100 , 3 , 2 , 140000 , 1760 , 38000 , 94301),\n",
- "(14 , 2070 , 2 , 3 , 148000 , 1550 , 14000 , 94301),\n",
- "(15 , 650 , 3 , 1.5 , 65000 , 1450 , 12000 , 94301),\n",
- "(16 , 770 , 2 , 2 , 91000 , 1300 , 17500 , 76010),\n",
- "(17 , 1220 , 3 , 2 , 132300 , 1500 , 30000 , 76010),\n",
- "(18 , 1150 , 2 , 1 , 91100 , 820 , 25700 , 76010),\n",
- "(19 , 2690 , 3 , 2.5 , 260011 , 2130 , 25000 , 76010),\n",
- "(20 , 780 , 2 , 1 , 141800 , 1170 , 22000 , 76010),\n",
- "(21 , 1910 , 3 , 2 , 160900 , 1500 , 19000 , 76010),\n",
- "(22 , 3600 , 4 , 2 , 239000 , 2790 , 20000 , 76010),\n",
- "(23 , 1600 , 3 , 1 , 81010 , 1030 , 17500 , 76010),\n",
- "(24 , 1590 , 3 , 2 , 117910 , 1250 , 20000 , 76010),\n",
- "(25 , 3200 , 3 , 2 , 141100 , 1760 , 38000 , 76010),\n",
- "(26 , 2270 , 2 , 3 , 148011 , 1550 , 14000 , 76010),\n",
- "(27 , 750 , 3 , 1.5 , 66000 , 1450 , 12000 , 76010);\n",
- "\n",
- "SELECT * FROM houses ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 2. Train the model"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 16,
- "metadata": {
- "collapsed": false
- },
- "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>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'tax', u'bath', u'size']</td>\n",
- " <td>[u'tax', u'bath', u'size']</td>\n",
- " <td>[22.785125212, 10707.9542786, 54.796130396]</td>\n",
- " <td>[22.785125212, 10707.9542786, 54.796130396]</td>\n",
- " <td>-7798.79034492</td>\n",
- " <td>-512248641.969</td>\n",
- " <td>True</td>\n",
- " <td>375</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u'gaussian', [u'tax', u'bath', u'size'], [u'tax', u'bath', u'size'], [22.785125212, 10707.9542786, 54.796130396], [22.785125212, 10707.9542786, 54.796130396], -7798.79034492, -512248641.969, True, 375)]"
- ]
- },
- "execution_count": 16,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS houses_en, houses_en_summary;\n",
- "SELECT madlib.elastic_net_train( 'houses', -- Source table\n",
- " 'houses_en', -- Result table\n",
- " 'price', -- Dependent variable\n",
- " 'array[tax, bath, size]', -- Independent variable\n",
- " 'gaussian', -- Regression family\n",
- " 0.5, -- Alpha value\n",
- " 0.1, -- Lambda value\n",
- " TRUE, -- Standardize\n",
- " NULL, -- Grouping column(s)\n",
- " 'fista', -- Optimizer\n",
- " '', -- Optimizer parameters\n",
- " NULL, -- Excluded columns\n",
- " 10000, -- Maximum iterations\n",
- " 1e-6 -- Tolerance value\n",
- " );\n",
- "SELECT * FROM houses_en;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# 3. Prediction\n",
- "Evaluate residuals."
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 17,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "27 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>price</th>\n",
- " <th>predict</th>\n",
- " <th>residual</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>50000</td>\n",
- " <td>58545.4082137</td>\n",
- " <td>-8545.40821368</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2</td>\n",
- " <td>85000</td>\n",
- " <td>114804.043543</td>\n",
- " <td>-29804.0435432</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>3</td>\n",
- " <td>22500</td>\n",
- " <td>61448.7646577</td>\n",
- " <td>-38948.7646577</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>4</td>\n",
- " <td>90000</td>\n",
- " <td>104675.146662</td>\n",
- " <td>-14675.1466615</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>5</td>\n",
- " <td>133000</td>\n",
- " <td>125887.679086</td>\n",
- " <td>7112.32091388</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>6</td>\n",
- " <td>90500</td>\n",
- " <td>78601.9098946</td>\n",
- " <td>11898.0901054</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>7</td>\n",
- " <td>260000</td>\n",
- " <td>199257.352437</td>\n",
- " <td>60742.6475635</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>8</td>\n",
- " <td>142500</td>\n",
- " <td>82514.5216412</td>\n",
- " <td>59985.4783588</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>9</td>\n",
- " <td>160000</td>\n",
- " <td>137735.944196</td>\n",
- " <td>22264.0558036</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>10</td>\n",
- " <td>240000</td>\n",
- " <td>250347.582797</td>\n",
- " <td>-10347.5827973</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>11</td>\n",
- " <td>87000</td>\n",
- " <td>97172.4860935</td>\n",
- " <td>-10172.4860935</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>12</td>\n",
- " <td>118600</td>\n",
- " <td>119024.184051</td>\n",
- " <td>-424.18405072</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>13</td>\n",
- " <td>140000</td>\n",
- " <td>180692.195866</td>\n",
- " <td>-40692.1958664</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>14</td>\n",
- " <td>148000</td>\n",
- " <td>156424.283794</td>\n",
- " <td>-8424.28379352</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>15</td>\n",
- " <td>65000</td>\n",
- " <td>102527.861535</td>\n",
- " <td>-37527.861535</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>16</td>\n",
- " <td>91000</td>\n",
- " <td>102396.63414</td>\n",
- " <td>-11396.6341403</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>17</td>\n",
- " <td>132300</td>\n",
- " <td>123609.166565</td>\n",
- " <td>8690.83343508</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>18</td>\n",
- " <td>91100</td>\n",
- " <td>74044.8848522</td>\n",
- " <td>17055.1151478</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>19</td>\n",
- " <td>260011</td>\n",
- " <td>196978.839915</td>\n",
- " <td>63032.1600847</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>20</td>\n",
- " <td>141800</td>\n",
- " <td>84793.0341624</td>\n",
- " <td>57006.9658376</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>21</td>\n",
- " <td>160900</td>\n",
- " <td>139330.902961</td>\n",
- " <td>21569.0970388</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>22</td>\n",
- " <td>239000</td>\n",
- " <td>248524.77278</td>\n",
- " <td>-9524.77278032</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>23</td>\n",
- " <td>81010</td>\n",
- " <td>95805.3785808</td>\n",
- " <td>-14795.3785808</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>24</td>\n",
- " <td>117910</td>\n",
- " <td>118340.630294</td>\n",
- " <td>-430.63029436</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>25</td>\n",
- " <td>141100</td>\n",
- " <td>182970.708388</td>\n",
- " <td>-41870.7083876</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>26</td>\n",
- " <td>148011</td>\n",
- " <td>160981.308836</td>\n",
- " <td>-12970.3088359</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>27</td>\n",
- " <td>66000</td>\n",
- " <td>104806.374056</td>\n",
- " <td>-38806.3740562</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(1, 50000, 58545.40821368, -8545.40821368),\n",
- " (2, 85000, 114804.04354324, -29804.04354324),\n",
- " (3, 22500, 61448.76465768, -38948.76465768),\n",
- " (4, 90000, 104675.14666152, -14675.14666152),\n",
- " (5, 133000, 125887.67908612, 7112.32091388),\n",
- " (6, 90500, 78601.9098946, 11898.0901054),\n",
- " (7, 260000, 199257.35243654, 60742.64756346),\n",
- " (8, 142500, 82514.52164116, 59985.47835884),\n",
- " (9, 160000, 137735.94419636, 22264.05580364),\n",
- " (10, 240000, 250347.58279728, -10347.58279728),\n",
- " (11, 87000, 97172.48609348, -10172.48609348),\n",
- " (12, 118600, 119024.18405072, -424.18405072001),\n",
- " (13, 140000, 180692.19586644, -40692.19586644),\n",
- " (14, 148000, 156424.28379352, -8424.28379352001),\n",
- " (15, 65000, 102527.86153498, -37527.86153498),\n",
- " (16, 91000, 102396.63414032, -11396.63414032),\n",
- " (17, 132300, 123609.16656492, 8690.83343508),\n",
- " (18, 91100, 74044.8848522, 17055.1151478),\n",
- " (19, 260011, 196978.83991534, 63032.16008466),\n",
- " (20, 141800, 84793.03416236, 57006.96583764),\n",
- " (21, 160900, 139330.9029612, 21569.0970388),\n",
- " (22, 239000, 248524.77278032, -9524.77278032),\n",
- " (23, 81010, 95805.37858076, -14795.37858076),\n",
- " (24, 117910, 118340.63029436, -430.630294360002),\n",
- " (25, 141100, 182970.70838764, -41870.70838764),\n",
- " (26, 148011, 160981.30883592, -12970.30883592),\n",
- " (27, 66000, 104806.37405618, -38806.37405618)]"
- ]
- },
- "execution_count": 17,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "SELECT id, price, predict, price - predict AS residual\n",
- "FROM (\n",
- " SELECT\n",
- " houses.*,\n",
- " madlib.elastic_net_gaussian_predict(\n",
- " m.coef_all, -- Coefficients\n",
- " m.intercept, -- Intercept\n",
- " ARRAY[tax,bath,size] -- Features (corresponding to coefficients)\n",
- " ) AS predict\n",
- " FROM houses, houses_en m) s\n",
- "ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# 4. Grouping \n",
- "Group on zip code."
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 18,
- "metadata": {
- "collapsed": false
- },
- "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>zipcode</th>\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>76010</td>\n",
- " <td>gaussian</td>\n",
- " <td>[u'tax', u'bath', u'size']</td>\n",
- " <td>[u'tax', u'bath', u'size']</td>\n",
- " <td>[15.6325563808, 10166.6562969, 57.8690658309]</td>\n",
- " <td>[15.6325563808, 10166.6562969, 57.8690658309]</td>\n",
- " <td>513.875727396</td>\n",
- " <td>-538806627.977</td>\n",
- " <td>True</td>\n",
- " <td>10000</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>94301</td>\n",
- " <td>gaussian</td>\n",
- " <td>[u'tax', u'bath', u'size']</td>\n",
- " <td>[u'tax', u'bath', u'size']</td>\n",
- " <td>[27.0542060555, 12351.5246493, 47.5833358009]</td>\n",
- " <td>[27.0542060555, 12351.5246493, 47.5833358009]</td>\n",
- " <td>-7191.20234448</td>\n",
- " <td>-519199958.999</td>\n",
- " <td>True</td>\n",
- " <td>10000</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(76010, u'gaussian', [u'tax', u'bath', u'size'], [u'tax', u'bath', u'size'], [15.6325563808, 10166.6562969, 57.8690658309], [15.6325563808, 10166.6562969, 57.8690658309], 513.875727396, -538806627.977, True, 10000),\n",
- " (94301, u'gaussian', [u'tax', u'bath', u'size'], [u'tax', u'bath', u'size'], [27.0542060555, 12351.5246493, 47.5833358009], [27.0542060555, 12351.5246493, 47.5833358009], -7191.20234448, -519199958.999, True, 10000)]"
- ]
- },
- "execution_count": 18,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS houses_en1, houses_en1_summary;\n",
- "SELECT madlib.elastic_net_train( 'houses', -- Source table\n",
- " 'houses_en1', -- Result table\n",
- " 'price', -- Dependent variable\n",
- " 'array[tax, bath, size]', -- Independent variable\n",
- " 'gaussian', -- Regression family\n",
- " 0.5, -- Alpha value\n",
- " 0.1, -- Lambda value\n",
- " TRUE, -- Standardize\n",
- " 'zipcode', -- Grouping column(s)\n",
- " 'fista', -- Optimizer\n",
- " '', -- Optimizer parameters\n",
- " NULL, -- Excluded columns\n",
- " 10000, -- Maximum iterations\n",
- " 1e-6 -- Tolerance value\n",
- " );\n",
- "SELECT * FROM houses_en1;"
- ]
- },
- {
- "cell_type": "raw",
- "metadata": {},
- "source": [
- "Prediction function"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 19,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "1 rows affected.\n",
- "27 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>price</th>\n",
- " <th>prediction</th>\n",
- " <th>residual</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>50000</td>\n",
- " <td>57761.4724443</td>\n",
- " <td>-7761.47244426</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2</td>\n",
- " <td>85000</td>\n",
- " <td>113011.266792</td>\n",
- " <td>-28011.2667917</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>3</td>\n",
- " <td>22500</td>\n",
- " <td>56139.7423749</td>\n",
- " <td>-33639.7423749</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>4</td>\n",
- " <td>90000</td>\n",
- " <td>102907.342764</td>\n",
- " <td>-12907.3427636</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>5</td>\n",
- " <td>133000</td>\n",
- " <td>124598.402649</td>\n",
- " <td>8401.59735127</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>6</td>\n",
- " <td>90500</td>\n",
- " <td>80701.8358365</td>\n",
- " <td>9798.16416352</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>7</td>\n",
- " <td>260000</td>\n",
- " <td>200521.34943</td>\n",
- " <td>59478.6505705</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>8</td>\n",
- " <td>142500</td>\n",
- " <td>79229.6853096</td>\n",
- " <td>63270.3146904</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>9</td>\n",
- " <td>160000</td>\n",
- " <td>138666.589798</td>\n",
- " <td>21333.4102024</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>10</td>\n",
- " <td>240000</td>\n",
- " <td>249828.832123</td>\n",
- " <td>-9828.83212287</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>11</td>\n",
- " <td>87000</td>\n",
- " <td>99081.1402319</td>\n",
- " <td>-12081.1402319</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>12</td>\n",
- " <td>118600</td>\n",
- " <td>120818.830515</td>\n",
- " <td>-2218.83051515</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>13</td>\n",
- " <td>140000</td>\n",
- " <td>185126.556736</td>\n",
- " <td>-45126.5567358</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>14</td>\n",
- " <td>148000</td>\n",
- " <td>159619.74863</td>\n",
- " <td>-11619.7486297</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>15</td>\n",
- " <td>65000</td>\n",
- " <td>97917.1554769</td>\n",
- " <td>-32917.1554768</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>16</td>\n",
- " <td>91000</td>\n",
- " <td>108114.042315</td>\n",
- " <td>-17114.0423146</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>17</td>\n",
- " <td>132300</td>\n",
- " <td>126722.505852</td>\n",
- " <td>5577.49414788</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>18</td>\n",
- " <td>91100</td>\n",
- " <td>76110.6058436</td>\n",
- " <td>14989.3941564</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>19</td>\n",
- " <td>260011</td>\n",
- " <td>191243.203354</td>\n",
- " <td>68767.7966462</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>20</td>\n",
- " <td>141800</td>\n",
- " <td>90580.7330235</td>\n",
- " <td>51219.2669765</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>21</td>\n",
- " <td>160900</td>\n",
- " <td>137508.969755</td>\n",
- " <td>23391.0302451</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>22</td>\n",
- " <td>239000</td>\n",
- " <td>238579.08496</td>\n",
- " <td>420.915039713</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>23</td>\n",
- " <td>81010</td>\n",
- " <td>95297.7600394</td>\n",
- " <td>-14287.7600394</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>24</td>\n",
- " <td>117910</td>\n",
- " <td>118039.285255</td>\n",
- " <td>-129.285255293</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>25</td>\n",
- " <td>141100</td>\n",
- " <td>172720.924602</td>\n",
- " <td>-31620.9246021</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>26</td>\n",
- " <td>148011</td>\n",
- " <td>156196.79964</td>\n",
- " <td>-8185.79964041</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>27</td>\n",
- " <td>66000</td>\n",
- " <td>111398.422913</td>\n",
- " <td>-45398.4229132</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(1, 50000, 57761.472444258, -7761.472444258),\n",
- " (2, 85000, 113011.266791664, -28011.266791664),\n",
- " (3, 22500, 56139.742374884, -33639.742374884),\n",
- " (4, 90000, 102907.342763575, -12907.342763575),\n",
- " (5, 133000, 124598.40264873, 8401.59735127),\n",
- " (6, 90500, 80701.835836483, 9798.16416351699),\n",
- " (7, 260000, 200521.349429532, 59478.650570468),\n",
- " (8, 142500, 79229.685309613, 63270.314690387),\n",
- " (9, 160000, 138666.58979759, 21333.41020241),\n",
- " (10, 240000, 249828.832122871, -9828.83212287101),\n",
- " (11, 87000, 99081.140231877, -12081.140231877),\n",
- " (12, 118600, 120818.830515155, -2218.830515155),\n",
- " (13, 140000, 185126.556735754, -45126.556735754),\n",
- " (14, 148000, 159619.7486297, -11619.7486297),\n",
- " (15, 65000, 97917.15547685, -32917.15547685),\n",
- " (16, 91000, 108114.042314582, -17114.042314582),\n",
- " (17, 132300, 126722.505852122, 5577.49414787799),\n",
- " (18, 91100, 76110.605843554, 14989.394156446),\n",
- " (19, 260011, 191243.203353815, 68767.796646185),\n",
- " (20, 141800, 90580.733023473, 51219.266976527),\n",
- " (21, 160900, 137508.969754874, 23391.030245126),\n",
- " (22, 239000, 238579.084960287, 420.915039713),\n",
- " (23, 81010, 95297.760039403, -14287.760039403),\n",
- " (24, 117910, 118039.285255293, -129.285255292998),\n",
- " (25, 141100, 172720.92460214, -31620.92460214),\n",
- " (26, 148011, 156196.799640407, -8185.799640407),\n",
- " (27, 66000, 111398.422913151, -45398.422913151)]"
- ]
- },
- "execution_count": 19,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "SELECT madlib.elastic_net_predict(\n",
- " 'houses_en1', -- Model table\n",
- " 'houses', -- New source data table\n",
- " 'id', -- Unique ID associated with each row\n",
- " 'houses_en1_prediction' -- Table to store prediction result\n",
- " );\n",
- "\n",
- "SELECT houses.id,\n",
- " houses.price,\n",
- " houses_en1_prediction.prediction,\n",
- " houses.price - houses_en1_prediction.prediction AS residual\n",
- "FROM houses_en1_prediction, houses\n",
- "WHERE houses.id = houses_en1_prediction.id ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 5. When coef_nonzero is different from coef_all\n",
- "Train"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 20,
- "metadata": {
- "collapsed": false
- },
- "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>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'tax', u'bath', u'size']</td>\n",
- " <td>[u'tax', u'size']</td>\n",
- " <td>[6.94744249834, 29.7137297658]</td>\n",
- " <td>[6.94744249834, 0.0, 29.7137297658]</td>\n",
- " <td>74445.7039382</td>\n",
- " <td>-1635348585.07</td>\n",
- " <td>True</td>\n",
- " <td>151</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u'gaussian', [u'tax', u'bath', u'size'], [u'tax', u'size'], [6.94744249834, 29.7137297658], [6.94744249834, 0.0, 29.7137297658], 74445.7039382, -1635348585.07, True, 151)]"
- ]
- },
- "execution_count": 20,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS houses_en2, houses_en2_summary;\n",
- "SELECT madlib.elastic_net_train( 'houses', -- Source table\n",
- " 'houses_en2', -- Result table\n",
- " 'price', -- Dependent variable\n",
- " 'array[tax, bath, size]', -- Independent variable\n",
- " 'gaussian', -- Regression family\n",
- " 1, -- Alpha value\n",
- " 30000, -- Lambda value\n",
- " TRUE, -- Standardize\n",
- " NULL, -- Grouping column(s)\n",
- " 'fista', -- Optimizer\n",
- " '', -- Optimizer parameters\n",
- " NULL, -- Excluded columns\n",
- " 10000, -- Maximum iterations\n",
- " 1e-6 -- Tolerance value\n",
- " );\n",
- "SELECT * FROM houses_en2;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Prediction function with coef_all to evaluate residuals."
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 21,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "27 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>price</th>\n",
- " <th>predict</th>\n",
- " <th>residual</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>50000</td>\n",
- " <td>101424.266932</td>\n",
- " <td>-51424.2669319</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2</td>\n",
- " <td>85000</td>\n",
- " <td>123636.877531</td>\n",
- " <td>-38636.8775312</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>3</td>\n",
- " <td>22500</td>\n",
- " <td>106081.20634</td>\n",
- " <td>-83581.2063399</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>4</td>\n",
- " <td>90000</td>\n",
- " <td>119117.827607</td>\n",
- " <td>-29117.8276073</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>5</td>\n",
- " <td>133000</td>\n",
- " <td>128186.922685</td>\n",
- " <td>4813.07731529</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>6</td>\n",
- " <td>90500</td>\n",
- " <td>108190.009719</td>\n",
- " <td>-17690.0097189</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>7</td>\n",
- " <td>260000</td>\n",
- " <td>157119.31291</td>\n",
- " <td>102880.68709</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>8</td>\n",
- " <td>142500</td>\n",
- " <td>113935.028663</td>\n",
- " <td>28564.9713369</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>9</td>\n",
- " <td>160000</td>\n",
- " <td>131799.592784</td>\n",
- " <td>28200.4072162</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>10</td>\n",
- " <td>240000</td>\n",
- " <td>182913.598379</td>\n",
- " <td>57086.4016213</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>11</td>\n",
- " <td>87000</td>\n",
- " <td>116583.600144</td>\n",
- " <td>-29583.6001442</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>12</td>\n",
- " <td>118600</td>\n",
- " <td>122842.722993</td>\n",
- " <td>-4242.72299276</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>13</td>\n",
- " <td>140000</td>\n",
- " <td>148278.940071</td>\n",
- " <td>-8278.94007086</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>14</td>\n",
- " <td>148000</td>\n",
- " <td>134883.191047</td>\n",
- " <td>13116.8089532</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>15</td>\n",
- " <td>65000</td>\n",
- " <td>122046.449723</td>\n",
- " <td>-57046.4497225</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>16</td>\n",
- " <td>91000</td>\n",
- " <td>118423.083357</td>\n",
- " <td>-27423.0833575</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>17</td>\n",
- " <td>132300</td>\n",
- " <td>127492.178435</td>\n",
- " <td>4807.82156513</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>18</td>\n",
- " <td>91100</td>\n",
- " <td>106800.521219</td>\n",
- " <td>-15700.5212192</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>19</td>\n",
- " <td>260011</td>\n",
- " <td>156424.56866</td>\n",
- " <td>103586.43134</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>20</td>\n",
- " <td>141800</td>\n",
- " <td>114629.772913</td>\n",
- " <td>27170.2270871</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>21</td>\n",
- " <td>160900</td>\n",
- " <td>132285.913759</td>\n",
- " <td>28614.0862413</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>22</td>\n",
- " <td>239000</td>\n",
- " <td>182357.802979</td>\n",
- " <td>56642.1970212</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>23</td>\n",
- " <td>81010</td>\n",
- " <td>116166.753594</td>\n",
- " <td>-35156.7535943</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>24</td>\n",
- " <td>117910</td>\n",
- " <td>122634.299718</td>\n",
- " <td>-4724.29971781</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>25</td>\n",
- " <td>141100</td>\n",
- " <td>148973.684321</td>\n",
- " <td>-7873.6843207</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>26</td>\n",
- " <td>148011</td>\n",
- " <td>136272.679546</td>\n",
- " <td>11738.3204536</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>27</td>\n",
- " <td>66000</td>\n",
- " <td>122741.193972</td>\n",
- " <td>-56741.1939724</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(1, 50000, 101424.266931887, -51424.2669318866),\n",
- " (2, 85000, 123636.877531235, -38636.877531235),\n",
- " (3, 22500, 106081.206339915, -83581.2063399148),\n",
- " (4, 90000, 119117.827607296, -29117.8276072958),\n",
- " (5, 133000, 128186.922684709, 4813.0773152912),\n",
- " (6, 90500, 108190.009718915, -17690.009718915),\n",
- " (7, 260000, 157119.312909723, 102880.687090277),\n",
- " (8, 142500, 113935.028663057, 28564.9713369428),\n",
- " (9, 160000, 131799.592783846, 28200.4072161544),\n",
- " (10, 240000, 182913.598378673, 57086.4016213268),\n",
- " (11, 87000, 116583.600144218, -29583.6001442184),\n",
- " (12, 118600, 122842.722992761, -4242.7229927608),\n",
- " (13, 140000, 148278.940070862, -8278.94007086198),\n",
- " (14, 148000, 134883.191046754, 13116.8089532462),\n",
- " (15, 65000, 122046.449722531, -57046.449722531),\n",
- " (16, 91000, 118423.083357462, -27423.0833574618),\n",
- " (17, 132300, 127492.178434875, 4807.82156512521),\n",
- " (18, 91100, 106800.521219247, -15700.521219247),\n",
- " (19, 260011, 156424.568659889, 103586.431340111),\n",
- " (20, 141800, 114629.772912891, 27170.2270871088),\n",
- " (21, 160900, 132285.913758729, 28614.0862412706),\n",
- " (22, 239000, 182357.802978806, 56642.197021194),\n",
- " (23, 81010, 116166.753594318, -35156.753594318),\n",
- " (24, 117910, 122634.299717811, -4724.29971781059),\n",
- " (25, 141100, 148973.684320696, -7873.68432069599),\n",
- " (26, 148011, 136272.679546422, 11738.3204535782),\n",
- " (27, 66000, 122741.193972365, -56741.193972365)]"
- ]
- },
- "execution_count": 21,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "SELECT id, price, predict, price - predict AS residual\n",
- "FROM (\n",
- " SELECT\n",
- " houses.*,\n",
- " madlib.elastic_net_gaussian_predict(\n",
- " m.coef_all, -- All coefficients\n",
- " m.intercept, -- Intercept\n",
- " ARRAY[tax,bath,size] -- All features\n",
- " ) AS predict\n",
- " FROM houses, houses_en2 m) s\n",
- "ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "We can speed up the prediction function with coef_nonzero to evaluate residuals. This requires the user to examine the feature_selected column in the result table to construct the correct set of independent variables to provide to the prediction function."
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 22,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "27 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>price</th>\n",
- " <th>predict</th>\n",
- " <th>residual</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>50000</td>\n",
- " <td>101424.266932</td>\n",
- " <td>-51424.2669319</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2</td>\n",
- " <td>85000</td>\n",
- " <td>123636.877531</td>\n",
- " <td>-38636.8775312</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>3</td>\n",
- " <td>22500</td>\n",
- " <td>106081.20634</td>\n",
- " <td>-83581.2063399</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>4</td>\n",
- " <td>90000</td>\n",
- " <td>119117.827607</td>\n",
- " <td>-29117.8276073</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>5</td>\n",
- " <td>133000</td>\n",
- " <td>128186.922685</td>\n",
- " <td>4813.07731529</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>6</td>\n",
- " <td>90500</td>\n",
- " <td>108190.009719</td>\n",
- " <td>-17690.0097189</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>7</td>\n",
- " <td>260000</td>\n",
- " <td>157119.31291</td>\n",
- " <td>102880.68709</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>8</td>\n",
- " <td>142500</td>\n",
- " <td>113935.028663</td>\n",
- " <td>28564.9713369</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>9</td>\n",
- " <td>160000</td>\n",
- " <td>131799.592784</td>\n",
- " <td>28200.4072162</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>10</td>\n",
- " <td>240000</td>\n",
- " <td>182913.598379</td>\n",
- " <td>57086.4016213</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>11</td>\n",
- " <td>87000</td>\n",
- " <td>116583.600144</td>\n",
- " <td>-29583.6001442</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>12</td>\n",
- " <td>118600</td>\n",
- " <td>122842.722993</td>\n",
- " <td>-4242.72299276</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>13</td>\n",
- " <td>140000</td>\n",
- " <td>148278.940071</td>\n",
- " <td>-8278.94007086</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>14</td>\n",
- " <td>148000</td>\n",
- " <td>134883.191047</td>\n",
- " <td>13116.8089532</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>15</td>\n",
- " <td>65000</td>\n",
- " <td>122046.449723</td>\n",
- " <td>-57046.4497225</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>16</td>\n",
- " <td>91000</td>\n",
- " <td>118423.083357</td>\n",
- " <td>-27423.0833575</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>17</td>\n",
- " <td>132300</td>\n",
- " <td>127492.178435</td>\n",
- " <td>4807.82156513</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>18</td>\n",
- " <td>91100</td>\n",
- " <td>106800.521219</td>\n",
- " <td>-15700.5212192</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>19</td>\n",
- " <td>260011</td>\n",
- " <td>156424.56866</td>\n",
- " <td>103586.43134</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>20</td>\n",
- " <td>141800</td>\n",
- " <td>114629.772913</td>\n",
- " <td>27170.2270871</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>21</td>\n",
- " <td>160900</td>\n",
- " <td>132285.913759</td>\n",
- " <td>28614.0862413</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>22</td>\n",
- " <td>239000</td>\n",
- " <td>182357.802979</td>\n",
- " <td>56642.1970212</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>23</td>\n",
- " <td>81010</td>\n",
- " <td>116166.753594</td>\n",
- " <td>-35156.7535943</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>24</td>\n",
- " <td>117910</td>\n",
- " <td>122634.299718</td>\n",
- " <td>-4724.29971781</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>25</td>\n",
- " <td>141100</td>\n",
- " <td>148973.684321</td>\n",
- " <td>-7873.6843207</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>26</td>\n",
- " <td>148011</td>\n",
- " <td>136272.679546</td>\n",
- " <td>11738.3204536</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>27</td>\n",
- " <td>66000</td>\n",
- " <td>122741.193972</td>\n",
- " <td>-56741.1939724</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(1, 50000, 101424.266931887, -51424.2669318866),\n",
- " (2, 85000, 123636.877531235, -38636.877531235),\n",
- " (3, 22500, 106081.206339915, -83581.2063399148),\n",
- " (4, 90000, 119117.827607296, -29117.8276072958),\n",
- " (5, 133000, 128186.922684709, 4813.0773152912),\n",
- " (6, 90500, 108190.009718915, -17690.009718915),\n",
- " (7, 260000, 157119.312909723, 102880.687090277),\n",
- " (8, 142500, 113935.028663057, 28564.9713369428),\n",
- " (9, 160000, 131799.592783846, 28200.4072161544),\n",
- " (10, 240000, 182913.598378673, 57086.4016213268),\n",
- " (11, 87000, 116583.600144218, -29583.6001442184),\n",
- " (12, 118600, 122842.722992761, -4242.7229927608),\n",
- " (13, 140000, 148278.940070862, -8278.94007086198),\n",
- " (14, 148000, 134883.191046754, 13116.8089532462),\n",
- " (15, 65000, 122046.449722531, -57046.449722531),\n",
- " (16, 91000, 118423.083357462, -27423.0833574618),\n",
- " (17, 132300, 127492.178434875, 4807.82156512521),\n",
- " (18, 91100, 106800.521219247, -15700.521219247),\n",
- " (19, 260011, 156424.568659889, 103586.431340111),\n",
- " (20, 141800, 114629.772912891, 27170.2270871088),\n",
- " (21, 160900, 132285.913758729, 28614.0862412706),\n",
- " (22, 239000, 182357.802978806, 56642.197021194),\n",
- " (23, 81010, 116166.753594318, -35156.753594318),\n",
- " (24, 117910, 122634.299717811, -4724.29971781059),\n",
- " (25, 141100, 148973.684320696, -7873.68432069599),\n",
- " (26, 148011, 136272.679546422, 11738.3204535782),\n",
- " (27, 66000, 122741.193972365, -56741.193972365)]"
- ]
- },
- "execution_count": 22,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "SELECT id, price, predict, price - predict AS residual\n",
- "FROM (\n",
- " SELECT\n",
- " houses.*,\n",
- " madlib.elastic_net_gaussian_predict(\n",
- " m.coef_nonzero, -- Non-zero coefficients\n",
- " m.intercept, -- Intercept\n",
- " ARRAY[tax,size] -- Features corresponding to non-zero coefficients\n",
- " ) AS predict\n",
- " FROM houses, houses_en2 m) s\n",
- "ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 6. Cross validation\n",
- "Here we use 3-fold cross validation with 3 automatically generated\n",
- "lambda values and 3 specified alpha values. (This can take some time\n",
- "to run since elastic net is effectively being called 27 times.)"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 9,
- "metadata": {
- "collapsed": false
- },
- "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>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'tax', u'bath', u'size']</td>\n",
- " <td>[u'tax', u'bath', u'size']</td>\n",
- " <td>[22.4584783679, 11657.0825871, 52.1622899664]</td>\n",
- " <td>[22.4584783679, 11657.0825871, 52.1622899664]</td>\n",
- " <td>-5067.27288499</td>\n",
- " <td>-543193170.15</td>\n",
- " <td>True</td>\n",
- " <td>392</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u'gaussian', [u'tax', u'bath', u'size'], [u'tax', u'bath', u'size'], [22.4584783679, 11657.0825871, 52.1622899664], [22.4584783679, 11657.0825871, 52.1622899664], -5067.27288499, -543193170.15, True, 392)]"
- ]
- },
- "execution_count": 9,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS houses_en3, houses_en3_summary, houses_en3_cv;\n",
- "SELECT madlib.elastic_net_train( 'houses', -- Source table\n",
- " 'houses_en3', -- Result table\n",
- " 'price', -- Dependent variable\n",
- " 'array[tax, bath, size]', -- Independent variable\n",
- " 'gaussian', -- Regression family\n",
- " 0.5, -- Alpha value\n",
- " 0.1, -- Lambda value\n",
- " TRUE, -- Standardize\n",
- " NULL, -- Grouping column(s)\n",
- " 'fista', -- Optimizer\n",
- " $$ n_folds = 3, -- Optimizer parameters\n",
- " validation_result=houses_en3_cv,\n",
- " n_lambdas = 3, \n",
- " alpha = {0, 0.1, 1}\n",
- " $$, \n",
- " NULL, -- Excluded columns\n",
- " 10000, -- Maximum iterations\n",
- " 1e-6 -- Tolerance value\n",
- " );\n",
- "SELECT * FROM houses_en3;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Details of the cross validation:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 13,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "9 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>std</th>\n",
- " <th>alpha</th>\n",
- " <th>lambda_value</th>\n",
- " <th>mean</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1.80536123195e+110</td>\n",
- " <td>0.0</td>\n",
- " <td>100000.0</td>\n",
- " <td>-1.41777698585e+110</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1.72846143163e+107</td>\n",
- " <td>0.1</td>\n",
- " <td>100000.0</td>\n",
- " <td>-1.19953054719e+107</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2485189261.38</td>\n",
- " <td>1.0</td>\n",
- " <td>100000.0</td>\n",
- " <td>-4175743937.91</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2424765457.66</td>\n",
- " <td>0.0</td>\n",
- " <td>100.0</td>\n",
- " <td>-4054694238.18</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2418294966.72</td>\n",
- " <td>0.1</td>\n",
- " <td>100.0</td>\n",
- " <td>-4041768667.28</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>483327430.802</td>\n",
- " <td>1.0</td>\n",
- " <td>100.0</td>\n",
- " <td>-1458791218.11</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>426795110.876</td>\n",
- " <td>0.0</td>\n",
- " <td>0.1</td>\n",
- " <td>-1442293698.38</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>429680202.16</td>\n",
- " <td>0.1</td>\n",
- " <td>0.1</td>\n",
- " <td>-1442705511.6</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>485107796.02</td>\n",
- " <td>1.0</td>\n",
- " <td>0.1</td>\n",
- " <td>-1459206061.39</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(1.80536123195e+110, 0.0, 100000.0, -1.41777698585e+110),\n",
- " (1.72846143163e+107, 0.1, 100000.0, -1.19953054719e+107),\n",
- " (2485189261.38, 1.0, 100000.0, -4175743937.91),\n",
- " (2424765457.66, 0.0, 100.0, -4054694238.18),\n",
- " (2418294966.72, 0.1, 100.0, -4041768667.28),\n",
- " (483327430.802, 1.0, 100.0, -1458791218.11),\n",
- " (426795110.876, 0.0, 0.1, -1442293698.38),\n",
- " (429680202.16, 0.1, 0.1, -1442705511.6),\n",
- " (485107796.02, 1.0, 0.1, -1459206061.39)]"
- ]
- },
- "execution_count": 13,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "SELECT * FROM houses_en3_cv ORDER BY lambda_value DESC, alpha ASC;"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": true
- },
- "outputs": [],
- "source": []
- }
- ],
- "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.12"
- }
- },
- "nbformat": 4,
- "nbformat_minor": 0
-}