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/02/08 23:03:48 UTC
[2/2] incubator-madlib-site git commit: update/add notebooks
update/add notebooks
Project: http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/commit/9e6cc815
Tree: http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/tree/9e6cc815
Diff: http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/diff/9e6cc815
Branch: refs/heads/asf-site
Commit: 9e6cc815b0f3a8f236831fe00bad203aabb23f66
Parents: 3c445f7
Author: Frank McQuillan <fm...@pivotal.io>
Authored: Wed Feb 8 15:02:42 2017 -0800
Committer: Frank McQuillan <fm...@pivotal.io>
Committed: Wed Feb 8 15:02:42 2017 -0800
----------------------------------------------------------------------
...coding-categorical-variables-1dot10-v1.ipynb | 1813 +++++++++++++++---
community-artifacts/Kmeans-v1.ipynb | 977 ++++++++++
community-artifacts/kNN-v1.ipynb | 497 +++++
3 files changed, 3014 insertions(+), 273 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/9e6cc815/community-artifacts/Encoding-categorical-variables-1dot10-v1.ipynb
----------------------------------------------------------------------
diff --git a/community-artifacts/Encoding-categorical-variables-1dot10-v1.ipynb b/community-artifacts/Encoding-categorical-variables-1dot10-v1.ipynb
index 611b5ff..e404717 100644
--- a/community-artifacts/Encoding-categorical-variables-1dot10-v1.ipynb
+++ b/community-artifacts/Encoding-categorical-variables-1dot10-v1.ipynb
@@ -10,44 +10,22 @@
},
{
"cell_type": "code",
- "execution_count": 1,
+ "execution_count": null,
"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"
- ]
- }
- ],
+ "outputs": [],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "code",
- "execution_count": 2,
+ "execution_count": null,
"metadata": {
"collapsed": false
},
- "outputs": [
- {
- "data": {
- "text/plain": [
- "u'Connected: gpdbchina@madlib'"
- ]
- },
- "execution_count": 2,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
+ "outputs": [],
"source": [
"%sql postgresql://gpdbchina@10.194.10.68:55000/madlib\n",
"#%sql postgresql://fmcquillan@localhost:5432/madlib\n",
@@ -56,7 +34,7 @@
},
{
"cell_type": "code",
- "execution_count": 3,
+ "execution_count": 19,
"metadata": {
"collapsed": false
},
@@ -76,15 +54,15 @@
" <th>version</th>\n",
" </tr>\n",
" <tr>\n",
- " <td>MADlib version: 1.10.0-dev, git revision: v1.7.1-203-g51f7a94, cmake configuration time: Tue Jan 3 23:26:27 UTC 2017, 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",
+ " <td>MADlib version: 1.10.0-dev, git revision: rel/v1.9.1-47-g2d5a5ed, cmake configuration time: Tue Feb 7 19:45:19 UTC 2017, 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: v1.7.1-203-g51f7a94, cmake configuration time: Tue Jan 3 23:26:27 UTC 2017, 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',)]"
+ "[(u'MADlib version: 1.10.0-dev, git revision: rel/v1.9.1-47-g2d5a5ed, cmake configuration time: Tue Feb 7 19:45:19 UTC 2017, 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,
+ "execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
@@ -104,7 +82,7 @@
},
{
"cell_type": "code",
- "execution_count": 4,
+ "execution_count": 20,
"metadata": {
"collapsed": false
},
@@ -316,7 +294,7 @@
" (20, None, 0.45, 0.32, 0.1, 9)]"
]
},
- "execution_count": 4,
+ "execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
@@ -369,7 +347,7 @@
},
{
"cell_type": "code",
- "execution_count": 5,
+ "execution_count": 31,
"metadata": {
"collapsed": false
},
@@ -622,7 +600,7 @@
" (20, 0.45, 0.32, 0.1, 9, 0, 0, 0)]"
]
},
- "execution_count": 5,
+ "execution_count": 31,
"metadata": {},
"output_type": "execute_result"
}
@@ -648,7 +626,7 @@
},
{
"cell_type": "code",
- "execution_count": 6,
+ "execution_count": 32,
"metadata": {
"collapsed": false
},
@@ -922,7 +900,7 @@
" (20, 0.45, 0.32, 0.1, 9, 0, 0, 0, 1)]"
]
},
- "execution_count": 6,
+ "execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
@@ -953,245 +931,1522 @@
},
{
"cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
- "SELECT madlib.encode_categorical_variables (\n",
- " 'abalone', -- Source table\n",
- " 'abalone_out', -- Output table\n",
- " '*', -- Categorical columns\n",
- " NULL, -- Categorical columns to exclude\n",
- " 'id' -- Index columns\n",
- " );\n",
- "SELECT * FROM abalone_out ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 5. Encode top values\n",
- "Now let's encode only the top values and group others into a miscellaneous bucket column. Top values can be global across all columns or specified by column. As an example of the latter, here are the top 2 'sex' values and the top 50% of 'rings' values:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
- "SELECT madlib.encode_categorical_variables (\n",
- " 'abalone', -- Source table\n",
- " 'abalone_out', -- Output table\n",
- " '*', -- Categorical columns\n",
- " NULL, -- Categorical columns to exclude\n",
- " 'id', -- Index columns\n",
- " 'sex=2, rings=0.5' -- Top values\n",
- " );\n",
- "SELECT * FROM abalone_out ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 6. Show raw variables and encoded variables together\n",
- "If you want to see both the raw categorical variable and its encoded form in the output_table, then include the categorical variables 'sex' and 'rings' in the index parameter. (Remember that this will not work if you specify '*' for the parameter 'categorical_cols', because in this case 'row_id' columns will not be encoded at all.)"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
- "SELECT madlib.encode_categorical_variables (\n",
- " 'abalone', -- Source table\n",
- " 'abalone_out', -- Output table\n",
- " 'sex, rings', -- Categorical columns\n",
- " NULL, -- Categorical columns to exclude\n",
- " 'id, sex, rings' -- Index columns\n",
- " );\n",
- "SELECT * FROM abalone_out ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 7. Dummy encoding\n",
- "For dummy encoding, let's make the 'I' value from the 'sex' variable as the reference.\n",
- "Here we use the 'value_to_drop' parameter:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
- "SELECT madlib.encode_categorical_variables (\n",
- " 'abalone', -- Source table\n",
- " 'abalone_out', -- Output table\n",
- " '*', -- Categorical columns\n",
- " 'rings', -- Categorical columns to exclude\n",
- " 'id', -- Index columns\n",
- " NULL, -- Top value\n",
- " 'sex=I' -- Value to drop for dummy encoding \n",
- " );\n",
- "SELECT * FROM abalone_out ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 8. Array output\n",
- "Create an array output for the two categorical variables in the source table:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
- "SELECT madlib.encode_categorical_variables (\n",
- " 'abalone', -- Source table\n",
- " 'abalone_out', -- Output table\n",
- " '*', -- Categorical columns\n",
- " NULL, -- Categorical columns to exclude\n",
- " 'id', -- Index columns\n",
- " NULL, -- Top values\n",
- " NULL, -- Value to drop for dummy encoding\n",
- " NULL, -- Encode nulls\n",
- " TRUE -- Array output\n",
- " );\n",
- "SELECT * FROM abalone_out ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "And here is the dictionary table that specifies the index into the array:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%sql SELECT * FROM abalone_out_dictionary;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 9. Dictionary output\n",
- "Create a dictionary:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
- "SELECT madlib.encode_categorical_variables (\n",
- " 'abalone', -- Source table\n",
- " 'abalone_out', -- Output table\n",
- " '*', -- Categorical columns\n",
- " NULL, -- Categorical columns to exclude\n",
- " 'id', -- Index columns\n",
- " NULL, -- Top values\n",
- " NULL, -- Value to drop for dummy encoding\n",
- " NULL, -- Encode nulls\n",
- " FALSE, -- Array output\n",
- " TRUE -- Dictionary output \n",
- " );\n",
- "SELECT * FROM abalone_out ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "And here is the dictionary table that defines the columns in the output table:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%sql SELECT * FROM abalone_out_dictionary ORDER BY encoded_column_name;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "## 10. Distribution policy\n",
- "We can chose for various distribution policies, for examply RANDOMLY:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
+ "execution_count": 33,
"metadata": {
"collapsed": false
},
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
- "SELECT madlib.encode_categorical_variables (\n",
- " 'abalone', -- Source table\n",
- " 'abalone_out', -- Output table\n",
- " '*', -- Categorical columns\n",
- " NULL, -- Categorical columns to exclude\n",
- " 'id', -- Index columns\n",
- " NULL, -- Top values\n",
- " NULL, -- Value to drop for dummy encoding\n",
- " NULL, -- Encode nulls\n",
- " NULL, -- Array output\n",
- " NULL, -- Dictionary output\n",
- " 'RANDOMLY' -- Distribution policy\n",
- " );"
- ]
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "20 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>sex_F</th>\n",
+ " <th>sex_I</th>\n",
+ " <th>sex_M</th>\n",
+ " <th>rings_7</th>\n",
+ " <th>rings_8</th>\n",
+ " <th>rings_9</th>\n",
+ " <th>rings_10</th>\n",
+ " <th>rings_11</th>\n",
+ " <th>rings_12</th>\n",
+ " <th>rings_14</th>\n",
+ " <th>rings_15</th>\n",
+ " <th>rings_16</th>\n",
+ " <th>rings_19</th>\n",
+ " <th>rings_20</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>4</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>5</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>6</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>7</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>8</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>9</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>10</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>11</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>12</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>13</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>14</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>15</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>16</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>17</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>18</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>19</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>20</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0),\n",
+ " (2, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (3, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (4, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (5, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (6, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (7, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1),\n",
+ " (8, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0),\n",
+ " (9, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0),\n",
+ " (11, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0),\n",
+ " (12, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (13, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0),\n",
+ " (14, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (15, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (16, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0),\n",
+ " (17, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (18, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (19, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (20, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0)]"
+ ]
+ },
+ "execution_count": 33,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
+ "SELECT madlib.encode_categorical_variables (\n",
+ " 'abalone', -- Source table\n",
+ " 'abalone_out', -- Output table\n",
+ " '*', -- Categorical columns\n",
+ " NULL, -- Categorical columns to exclude\n",
+ " 'id' -- Index columns\n",
+ " );\n",
+ "SELECT * FROM abalone_out ORDER BY id;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## 5. Encode top values\n",
+ "Now let's encode only the top values and group others into a miscellaneous bucket column. Top values can be global across all columns or specified by column. As an example of the latter, here are the top 2 'sex' values and the top 50% of 'rings' values:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 34,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "20 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>sex_M</th>\n",
+ " <th>sex_F</th>\n",
+ " <th>sex__MISC__</th>\n",
+ " <th>rings_10</th>\n",
+ " <th>rings_7</th>\n",
+ " <th>rings_9</th>\n",
+ " <th>rings__MISC__</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>4</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>5</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>6</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>7</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>8</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>9</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>10</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>11</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>12</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>13</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>14</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>15</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>16</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>17</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>18</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>19</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>20</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1, 1, 0, 0, 0, 0, 0, 1),\n",
+ " (2, 1, 0, 0, 0, 1, 0, 0),\n",
+ " (3, 0, 1, 0, 0, 0, 1, 0),\n",
+ " (4, 1, 0, 0, 1, 0, 0, 0),\n",
+ " (5, 0, 0, 1, 0, 1, 0, 0),\n",
+ " (6, 0, 0, 1, 0, 0, 0, 1),\n",
+ " (7, 0, 1, 0, 0, 0, 0, 1),\n",
+ " (8, 0, 1, 0, 0, 0, 0, 1),\n",
+ " (9, 1, 0, 0, 0, 0, 1, 0),\n",
+ " (10, 0, 0, 0, 0, 0, 0, 1),\n",
+ " (11, 0, 1, 0, 0, 0, 0, 1),\n",
+ " (12, 1, 0, 0, 1, 0, 0, 0),\n",
+ " (13, 1, 0, 0, 0, 0, 0, 1),\n",
+ " (14, 0, 1, 0, 1, 0, 0, 0),\n",
+ " (15, 0, 1, 0, 1, 0, 0, 0),\n",
+ " (16, 1, 0, 0, 0, 0, 0, 1),\n",
+ " (17, 0, 0, 1, 0, 1, 0, 0),\n",
+ " (18, 0, 1, 0, 1, 0, 0, 0),\n",
+ " (19, 1, 0, 0, 0, 1, 0, 0),\n",
+ " (20, 0, 0, 0, 0, 0, 1, 0)]"
+ ]
+ },
+ "execution_count": 34,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
+ "SELECT madlib.encode_categorical_variables (\n",
+ " 'abalone', -- Source table\n",
+ " 'abalone_out', -- Output table\n",
+ " '*', -- Categorical columns\n",
+ " NULL, -- Categorical columns to exclude\n",
+ " 'id', -- Index columns\n",
+ " 'sex=2, rings=0.5' -- Top values\n",
+ " );\n",
+ "SELECT * FROM abalone_out ORDER BY id;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## 6. Show raw variables and encoded variables together\n",
+ "If you want to see both the raw categorical variable and its encoded form in the output_table, then include the categorical variables 'sex' and 'rings' in the index parameter. (Remember that this will not work if you specify '*' for the parameter 'categorical_cols', because in this case 'row_id' columns will not be encoded at all.)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 35,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "20 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>sex</th>\n",
+ " <th>rings</th>\n",
+ " <th>sex_F</th>\n",
+ " <th>sex_I</th>\n",
+ " <th>sex_M</th>\n",
+ " <th>rings_7</th>\n",
+ " <th>rings_8</th>\n",
+ " <th>rings_9</th>\n",
+ " <th>rings_10</th>\n",
+ " <th>rings_11</th>\n",
+ " <th>rings_12</th>\n",
+ " <th>rings_14</th>\n",
+ " <th>rings_15</th>\n",
+ " <th>rings_16</th>\n",
+ " <th>rings_19</th>\n",
+ " <th>rings_20</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>M</td>\n",
+ " <td>15</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2</td>\n",
+ " <td>M</td>\n",
+ " <td>7</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3</td>\n",
+ " <td>F</td>\n",
+ " <td>9</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>4</td>\n",
+ " <td>M</td>\n",
+ " <td>10</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>5</td>\n",
+ " <td>I</td>\n",
+ " <td>7</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>6</td>\n",
+ " <td>I</td>\n",
+ " <td>8</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>7</td>\n",
+ " <td>F</td>\n",
+ " <td>20</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>8</td>\n",
+ " <td>F</td>\n",
+ " <td>16</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>9</td>\n",
+ " <td>M</td>\n",
+ " <td>9</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>10</td>\n",
+ " <td>None</td>\n",
+ " <td>19</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>11</td>\n",
+ " <td>F</td>\n",
+ " <td>14</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>12</td>\n",
+ " <td>M</td>\n",
+ " <td>10</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>13</td>\n",
+ " <td>M</td>\n",
+ " <td>11</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>14</td>\n",
+ " <td>F</td>\n",
+ " <td>10</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>15</td>\n",
+ " <td>F</td>\n",
+ " <td>10</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>16</td>\n",
+ " <td>M</td>\n",
+ " <td>12</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>17</td>\n",
+ " <td>I</td>\n",
+ " <td>7</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>18</td>\n",
+ " <td>F</td>\n",
+ " <td>10</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>19</td>\n",
+ " <td>M</td>\n",
+ " <td>7</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>20</td>\n",
+ " <td>None</td>\n",
+ " <td>9</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1, u'M', 15, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0),\n",
+ " (2, u'M', 7, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (3, u'F', 9, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (4, u'M', 10, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (5, u'I', 7, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (6, u'I', 8, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (7, u'F', 20, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1),\n",
+ " (8, u'F', 16, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0),\n",
+ " (9, u'M', 9, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (10, None, 19, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0),\n",
+ " (11, u'F', 14, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0),\n",
+ " (12, u'M', 10, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (13, u'M', 11, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0),\n",
+ " (14, u'F', 10, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (15, u'F', 10, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (16, u'M', 12, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0),\n",
+ " (17, u'I', 7, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (18, u'F', 10, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (19, u'M', 7, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
+ " (20, None, 9, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0)]"
+ ]
+ },
+ "execution_count": 35,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
+ "SELECT madlib.encode_categorical_variables (\n",
+ " 'abalone', -- Source table\n",
+ " 'abalone_out', -- Output table\n",
+ " 'sex, rings', -- Categorical columns\n",
+ " NULL, -- Categorical columns to exclude\n",
+ " 'id, sex, rings' -- Index columns\n",
+ " );\n",
+ "SELECT * FROM abalone_out ORDER BY id;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## 7. Dummy encoding\n",
+ "For dummy encoding, let's make the 'I' value from the 'sex' variable as the reference.\n",
+ "Here we use the 'value_to_drop' parameter:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 36,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "20 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>sex_F</th>\n",
+ " <th>sex_M</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>4</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>5</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>6</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>7</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>8</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>9</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>10</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>11</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>12</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>13</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>14</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>15</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>16</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>17</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>18</td>\n",
+ " <td>1</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>19</td>\n",
+ " <td>0</td>\n",
+ " <td>1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>20</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1, 0, 1),\n",
+ " (2, 0, 1),\n",
+ " (3, 1, 0),\n",
+ " (4, 0, 1),\n",
+ " (5, 0, 0),\n",
+ " (6, 0, 0),\n",
+ " (7, 1, 0),\n",
+ " (8, 1, 0),\n",
+ " (9, 0, 1),\n",
+ " (10, 0, 0),\n",
+ " (11, 1, 0),\n",
+ " (12, 0, 1),\n",
+ " (13, 0, 1),\n",
+ " (14, 1, 0),\n",
+ " (15, 1, 0),\n",
+ " (16, 0, 1),\n",
+ " (17, 0, 0),\n",
+ " (18, 1, 0),\n",
+ " (19, 0, 1),\n",
+ " (20, 0, 0)]"
+ ]
+ },
+ "execution_count": 36,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
+ "SELECT madlib.encode_categorical_variables (\n",
+ " 'abalone', -- Source table\n",
+ " 'abalone_out', -- Output table\n",
+ " '*', -- Categorical columns\n",
+ " 'rings', -- Categorical columns to exclude\n",
+ " 'id', -- Index columns\n",
+ " NULL, -- Top value\n",
+ " 'sex=I' -- Value to drop for dummy encoding \n",
+ " );\n",
+ "SELECT * FROM abalone_out ORDER BY id;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## 8. Array output\n",
+ "Create an array output for the two categorical variables in the source table:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 42,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n"
+ ]
+ },
+ {
+ "ename": "InternalError",
+ "evalue": "(psycopg2.InternalError) plpy.Error: Encoding categorical: Output type should be one of ('column', 'array', 'svec') (plpython.c:4648)\nCONTEXT: Traceback (most recent call last):\n PL/Python function \"encode_categorical_variables\", line 23, in <module>\n return encode_categorical.encode_categorical_variables(**globals())\n PL/Python function \"encode_categorical_variables\", line 585, in encode_categorical_variables\n PL/Python function \"encode_categorical_variables\", line 86, in __init__\n PL/Python function \"encode_categorical_variables\", line 232, in _validate_parameters\n PL/Python function \"encode_categorical_variables\", line 48, in _assert\nPL/Python function \"encode_categorical_variables\"\n [SQL: \"SELECT madlib.encode_categorical_variables (\\n 'abalone', -- Source table\\n 'abalone_out', -- Output table\\n '*', -- Categorical columns\\n NULL,
-- Categorical columns to exclude\\n 'id', -- Index columns\\n NULL, -- Top values\\n NULL, -- Value to drop for dummy encoding\\n NULL, -- Encode nulls\\n NULL -- Output type\\n );\"]",
+ "output_type": "error",
+ "traceback": [
+ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
+ "\u001b[0;31mInternalError\u001b[0m Traceback (most recent call last)",
+ "\u001b[0;32m<ipython-input-42-be2cca418e49>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_cell_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mu'sql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34mu''\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34mu\"DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\\nSELECT madlib.encode_categorical_variables (\\n 'abalone', -- Source table\\n 'abalone_out', -- Output table\\n '*', -- Categorical columns\\n NULL, -- Categorical columns to exclude\\n 'id', -- Index columns\\n NULL, -- Top values\\n NULL, -- Value to drop for dummy encoding\\n NULL, -- Encode nulls\\n
NULL -- Output type\\n );\\nSELECT * FROM abalone_out ORDER BY id;\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/IPython/core/interactiveshell.pyc\u001b[0m in \u001b[0;36mrun_cell_magic\u001b[0;34m(self, magic_name, line, cell)\u001b[0m\n\u001b[1;32m 2291\u001b[0m \u001b[0mmagic_arg_s\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvar_expand\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mline\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstack_depth\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2292\u001b[0m \u001b[0;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2293\u001b[0;31m \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmagic_arg_s\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcell\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m
2294\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2295\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sql/magic.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/IPython/core/magic.pyc\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m 191\u001b[0m \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 192\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 193\u001b[0;31m \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u00
1b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 194\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 195\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sql/magic.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/IPython/core/magic.pyc\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m 191\u001b[0m \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 192\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 193\u001b[0;31m \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u00
1b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 194\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 195\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sql/magic.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[1;32m 78\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_persist_dataframe\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mparsed\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 79\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 80\u001b[0;31m \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparsed\u001b[0m\u001b[0;3
4m[\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 81\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 82\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mProgrammingError\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sql/run.pyc\u001b[0m in \u001b[0;36mrun\u001b[0;34m(conn, sql, config, user_namespace)\u001b[0m\n\u001b[1;32m 270\u001b[0m \u001b[0;32mraise\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"ipython_sql does not support transactions\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 271\u001b[0m \u001b[0mtxt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msqlalchemy\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 272\u001b[0;31m \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtxt\u001b[0m\u001b[0;34m,\u001b[0m \u001b[
0muser_namespace\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 273\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 274\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'commit'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object, *multiparams, **params)\u001b[0m\n\u001b[1;32m 912\u001b[0m type(object))\n\u001b[1;32m 913\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 914\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 915\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 916\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mp
arams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m 321\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 322\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 323\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u0
01b[0m\u001b[1;32m 324\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 325\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0munique_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0moptionaldict\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m 1008\u001b[0m \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1009\u001b[0m \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1010\u001b[0;31m \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdistilled_params\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 1011\u001b[0m )\n\u001b[1;32m 1012\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[
0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m 1144\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1145\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1146\u001b[0;31m context)\n\u001b[0m\u001b[1;32m 1147\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1148\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m 1339\u001b[0m util.raise_from_cause(\n\u001b[1;32m 1340\u001b[0m \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1341\u001b[0;31m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 1342\u001b[0m )\n\u001b[1;32m 1343\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m 197\u001b[0m \u001b[0mexc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 198\u001b[0m \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 199\u001b[0;31m \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m)\u
001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 200\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 201\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mpy3k\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m 1137\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1138\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1139\u001b[0;31m context)\n\u001b[0m\u001b[1;32m 1140\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1141\u001b[0m self._handle_dbapi_exception(\n",
+ "\u001b[0;32m/Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m 448\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 449\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 450\u001b[0;31m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;
32m 451\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 452\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
+ "\u001b[0;31mInternalError\u001b[0m: (psycopg2.InternalError) plpy.Error: Encoding categorical: Output type should be one of ('column', 'array', 'svec') (plpython.c:4648)\nCONTEXT: Traceback (most recent call last):\n PL/Python function \"encode_categorical_variables\", line 23, in <module>\n return encode_categorical.encode_categorical_variables(**globals())\n PL/Python function \"encode_categorical_variables\", line 585, in encode_categorical_variables\n PL/Python function \"encode_categorical_variables\", line 86, in __init__\n PL/Python function \"encode_categorical_variables\", line 232, in _validate_parameters\n PL/Python function \"encode_categorical_variables\", line 48, in _assert\nPL/Python function \"encode_categorical_variables\"\n [SQL: \"SELECT madlib.encode_categorical_variables (\\n 'abalone', -- Source table\\n 'abalone_out', -- Output table\\n '*', -- Categorical columns\\n
NULL, -- Categorical columns to exclude\\n 'id', -- Index columns\\n NULL, -- Top values\\n NULL, -- Value to drop for dummy encoding\\n NULL, -- Encode nulls\\n NULL -- Output type\\n );\"]"
+ ]
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
+ "SELECT madlib.encode_categorical_variables (\n",
+ " 'abalone', -- Source table\n",
+ " 'abalone_out', -- Output table\n",
+ " '*', -- Categorical columns\n",
+ " NULL, -- Categorical columns to exclude\n",
+ " 'id', -- Index columns\n",
+ " NULL, -- Top values\n",
+ " NULL, -- Value to drop for dummy encoding\n",
+ " NULL, -- Encode nulls\n",
+ " NULL -- Output type\n",
+ " );\n",
+ "SELECT * FROM abalone_out ORDER BY id;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "And here is the dictionary table that specifies the index into the array:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "%sql SELECT * FROM abalone_out_dictionary;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## 9. Dictionary output\n",
+ "Create a dictionary:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
+ "SELECT madlib.encode_categorical_variables (\n",
+ " 'abalone', -- Source table\n",
+ " 'abalone_out', -- Output table\n",
+ " '*', -- Categorical columns\n",
+ " NULL, -- Categorical columns to exclude\n",
+ " 'id', -- Index columns\n",
+ " NULL, -- Top values\n",
+ " NULL, -- Value to drop for dummy encoding\n",
+ " NULL, -- Encode nulls\n",
+ " FALSE, -- Array output\n",
+ " TRUE -- Dictionary output \n",
+ " );\n",
+ "SELECT * FROM abalone_out ORDER BY id;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "And here is the dictionary table that defines the columns in the output table:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "%sql SELECT * FROM abalone_out_dictionary ORDER BY encoded_column_name;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "## 10. Distribution policy\n",
+ "We can chose for various distribution policies, for examply RANDOMLY:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS abalone_out, abalone_out_dictionary;\n",
+ "SELECT madlib.encode_categorical_variables (\n",
+ " 'abalone', -- Source table\n",
+ " 'abalone_out', -- Output table\n",
+ " '*', -- Categorical columns\n",
+ " NULL, -- Categorical columns to exclude\n",
+ " 'id', -- Index columns\n",
+ " NULL, -- Top values\n",
+ " NULL, -- Value to drop for dummy encoding\n",
+ " NULL, -- Encode nulls\n",
+ " NULL, -- Array output\n",
+ " NULL, -- Dictionary output\n",
+ " 'RANDOMLY' -- Distribution policy\n",
+ " );"
+ ]
},
{
"cell_type": "markdown",
@@ -1214,9 +2469,21 @@
"SELECT madlib.encode_categorical_variables (\n",
" 'abalone', -- Source table\n",
" 'abalone_out', -- Output table\n",
- " 'height::TEXT' -- Categorical columns\n",
+ " 'height>.05' -- Categorical columns\n",
" );"
]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": false
+ },
+ "outputs": [],
+ "source": [
+ "%%sql\n",
+ "SELECT * FROM abalone_out ORDER BY id;"
+ ]
}
],
"metadata": {