http://git-wip-us.apache.org/repos/asf/madlib-site/blob/3f849b9e/community-artifacts/Summary-v1.ipynb ---------------------------------------------------------------------- diff --git a/community-artifacts/Summary-v1.ipynb b/community-artifacts/Summary-v1.ipynb deleted file mode 100644 index 57c3611..0000000 --- a/community-artifacts/Summary-v1.ipynb +++ /dev/null @@ -1,1026 +0,0 @@ -{ - "cells": [ - { - "cell_type": "code", - "execution_count": 13, - "metadata": {}, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "The sql extension is already loaded. To reload it, use:\n", - " %reload_ext sql\n" - ] - } - ], - "source": [ - "%load_ext sql" - ] - }, - { - "cell_type": "code", - "execution_count": 14, - "metadata": {}, - "outputs": [ - { - "data": { - "text/plain": [ - "u'Connected: fmcquillan@madlib'" - ] - }, - "execution_count": 14, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "# Greenplum 4.3.10.0\n", - "# %sql postgresql://gpdbchina@10.194.10.68:61000/madlib\n", - " \n", - "# PostgreSQL local\n", - "%sql postgresql://fmcquillan@localhost:5432/madlib" - ] - }, - { - "cell_type": "code", - "execution_count": 15, - "metadata": {}, - "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.12, git revision: unknown, cmake configuration time: Wed Aug 23 23:07:18 UTC 2017, build type: Release, build system: Darwin-16.7.0, C compiler: Clang, C++ compiler: Clang</td>\n", - " </tr>\n", - "</table>" - ], - "text/plain": [ - "[(u'MADlib version: 1.12, git revision: unknown, cmake configuration time: Wed Aug 23 23:07:18 UTC 2017, build type: Release, build system: Darwin-16.7.0, C compiler: Clang, C++ compiler: Clang',)]" - ] - }, - "execution_count": 15, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "%sql select madlib.version();\n", - "#%sql select version();" - ] - }, - { - "cell_type": "markdown", - "metadata": {}, - "source": [ - "# 1. On-line help" - ] - }, - { - "cell_type": "code", - "execution_count": 16, - "metadata": {}, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "1 rows affected.\n" - ] - }, - { - "data": { - "text/html": [ - "<table>\n", - " <tr>\n", - " <th>summary</th>\n", - " </tr>\n", - " <tr>\n", - " <td><br> 'summary' is a generic function used to produce summary statistics<br> of any data table. The function invokes particular 'methods' from<br> the MADlib library to provide an overview of the data.<br> -------<br> For an overview on usage, run:<br> SELECT madlib.summary('usage');<br> -------<br> For an example, run:<br> SELECT madlib.summary('example')<br> </td>\n", - " </tr>\n", - "</table>" - ], - "text/plain": [ - "[(u\"\\n 'summary' is a generic function used to produce summary statistics\\n of any data table. The function invokes particular 'methods' from\\n the MADlib library to provide an overview of the data.\\n -------\\n For an overview on usage, run:\\n SELECT madlib.summary('usage');\\n -------\\n For an example, run:\\n SELECT madlib.summary('example')\\n \",)]" - ] - }, - "execution_count": 16, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "%sql SELECT * FROM madlib.summary();" - ] - }, - { - "cell_type": "markdown", - "metadata": {}, - "source": [ - "# 2. Load data\n", - "Using part of the popular iris data set from https://archive.ics.uci.edu/ml/datasets/iris" - ] - }, - { - "cell_type": "code", - "execution_count": 51, - "metadata": {}, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "Done.\n", - "Done.\n", - "30 rows affected.\n", - "30 rows affected.\n" - ] - }, - { - "data": { - "text/html": [ - "<table>\n", - " <tr>\n", - " <th>id</th>\n", - " <th>sepal_length</th>\n", - " <th>sepal_width</th>\n", - " <th>petal_length</th>\n", - " <th>petal_width</th>\n", - " <th>class_name</th>\n", - " </tr>\n", - " <tr>\n", - " <td>1</td>\n", - " <td>5.1</td>\n", - " <td>3.5</td>\n", - " <td>1.4</td>\n", - " <td>0.2</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>2</td>\n", - " <td>4.9</td>\n", - " <td>3.0</td>\n", - " <td>1.4</td>\n", - " <td>0.2</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>3</td>\n", - " <td>4.7</td>\n", - " <td>3.2</td>\n", - " <td>1.3</td>\n", - " <td>0.2</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>4</td>\n", - " <td>4.6</td>\n", - " <td>3.1</td>\n", - " <td>1.5</td>\n", - " <td>0.2</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>5</td>\n", - " <td>5.0</td>\n", - " <td>3.6</td>\n", - " <td>1.4</td>\n", - " <td>0.2</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>6</td>\n", - " <td>5.4</td>\n", - " <td>3.9</td>\n", - " <td>1.7</td>\n", - " <td>0.4</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>7</td>\n", - " <td>4.6</td>\n", - " <td>3.4</td>\n", - " <td>1.4</td>\n", - " <td>0.3</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>8</td>\n", - " <td>5.0</td>\n", - " <td>3.4</td>\n", - " <td>1.5</td>\n", - " <td>0.2</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>9</td>\n", - " <td>4.4</td>\n", - " <td>2.9</td>\n", - " <td>1.4</td>\n", - " <td>0.2</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>10</td>\n", - " <td>4.9</td>\n", - " <td>3.1</td>\n", - " <td>1.5</td>\n", - " <td>0.1</td>\n", - " <td>Iris-setosa</td>\n", - " </tr>\n", - " <tr>\n", - " <td>11</td>\n", - " <td>7.0</td>\n", - " <td>3.2</td>\n", - " <td>4.7</td>\n", - " <td>1.4</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>12</td>\n", - " <td>6.4</td>\n", - " <td>3.2</td>\n", - " <td>4.5</td>\n", - " <td>1.5</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>13</td>\n", - " <td>6.9</td>\n", - " <td>3.1</td>\n", - " <td>4.9</td>\n", - " <td>1.5</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>14</td>\n", - " <td>5.5</td>\n", - " <td>2.3</td>\n", - " <td>4.0</td>\n", - " <td>1.3</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>15</td>\n", - " <td>6.5</td>\n", - " <td>2.8</td>\n", - " <td>4.6</td>\n", - " <td>1.5</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>16</td>\n", - " <td>5.7</td>\n", - " <td>2.8</td>\n", - " <td>4.5</td>\n", - " <td>1.3</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>17</td>\n", - " <td>6.3</td>\n", - " <td>3.3</td>\n", - " <td>4.7</td>\n", - " <td>1.6</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>18</td>\n", - " <td>4.9</td>\n", - " <td>2.4</td>\n", - " <td>3.3</td>\n", - " <td>1.0</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>19</td>\n", - " <td>6.6</td>\n", - " <td>2.9</td>\n", - " <td>4.6</td>\n", - " <td>1.3</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>20</td>\n", - " <td>5.2</td>\n", - " <td>2.7</td>\n", - " <td>3.9</td>\n", - " <td>1.4</td>\n", - " <td>Iris-versicolor</td>\n", - " </tr>\n", - " <tr>\n", - " <td>21</td>\n", - " <td>6.3</td>\n", - " <td>3.3</td>\n", - " <td>6.0</td>\n", - " <td>2.5</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>22</td>\n", - " <td>5.8</td>\n", - " <td>2.7</td>\n", - " <td>5.1</td>\n", - " <td>1.9</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>23</td>\n", - " <td>7.1</td>\n", - " <td>3.0</td>\n", - " <td>5.9</td>\n", - " <td>2.1</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>24</td>\n", - " <td>6.3</td>\n", - " <td>2.9</td>\n", - " <td>5.6</td>\n", - " <td>1.8</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>25</td>\n", - " <td>6.5</td>\n", - " <td>3.0</td>\n", - " <td>5.8</td>\n", - " <td>2.2</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>26</td>\n", - " <td>7.6</td>\n", - " <td>3.0</td>\n", - " <td>6.6</td>\n", - " <td>2.1</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>27</td>\n", - " <td>4.9</td>\n", - " <td>2.5</td>\n", - " <td>4.5</td>\n", - " <td>1.7</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>28</td>\n", - " <td>7.3</td>\n", - " <td>2.9</td>\n", - " <td>6.3</td>\n", - " <td>1.8</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>29</td>\n", - " <td>6.7</td>\n", - " <td>2.5</td>\n", - " <td>5.8</td>\n", - " <td>1.8</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - " <tr>\n", - " <td>30</td>\n", - " <td>7.2</td>\n", - " <td>3.6</td>\n", - " <td>6.1</td>\n", - " <td>2.5</td>\n", - " <td>Iris-virginica</td>\n", - " </tr>\n", - "</table>" - ], - "text/plain": [ - "[(1, 5.1, 3.5, 1.4, 0.2, u'Iris-setosa'),\n", - " (2, 4.9, 3.0, 1.4, 0.2, u'Iris-setosa'),\n", - " (3, 4.7, 3.2, 1.3, 0.2, u'Iris-setosa'),\n", - " (4, 4.6, 3.1, 1.5, 0.2, u'Iris-setosa'),\n", - " (5, 5.0, 3.6, 1.4, 0.2, u'Iris-setosa'),\n", - " (6, 5.4, 3.9, 1.7, 0.4, u'Iris-setosa'),\n", - " (7, 4.6, 3.4, 1.4, 0.3, u'Iris-setosa'),\n", - " (8, 5.0, 3.4, 1.5, 0.2, u'Iris-setosa'),\n", - " (9, 4.4, 2.9, 1.4, 0.2, u'Iris-setosa'),\n", - " (10, 4.9, 3.1, 1.5, 0.1, u'Iris-setosa'),\n", - " (11, 7.0, 3.2, 4.7, 1.4, u'Iris-versicolor'),\n", - " (12, 6.4, 3.2, 4.5, 1.5, u'Iris-versicolor'),\n", - " (13, 6.9, 3.1, 4.9, 1.5, u'Iris-versicolor'),\n", - " (14, 5.5, 2.3, 4.0, 1.3, u'Iris-versicolor'),\n", - " (15, 6.5, 2.8, 4.6, 1.5, u'Iris-versicolor'),\n", - " (16, 5.7, 2.8, 4.5, 1.3, u'Iris-versicolor'),\n", - " (17, 6.3, 3.3, 4.7, 1.6, u'Iris-versicolor'),\n", - " (18, 4.9, 2.4, 3.3, 1.0, u'Iris-versicolor'),\n", - " (19, 6.6, 2.9, 4.6, 1.3, u'Iris-versicolor'),\n", - " (20, 5.2, 2.7, 3.9, 1.4, u'Iris-versicolor'),\n", - " (21, 6.3, 3.3, 6.0, 2.5, u'Iris-virginica'),\n", - " (22, 5.8, 2.7, 5.1, 1.9, u'Iris-virginica'),\n", - " (23, 7.1, 3.0, 5.9, 2.1, u'Iris-virginica'),\n", - " (24, 6.3, 2.9, 5.6, 1.8, u'Iris-virginica'),\n", - " (25, 6.5, 3.0, 5.8, 2.2, u'Iris-virginica'),\n", - " (26, 7.6, 3.0, 6.6, 2.1, u'Iris-virginica'),\n", - " (27, 4.9, 2.5, 4.5, 1.7, u'Iris-virginica'),\n", - " (28, 7.3, 2.9, 6.3, 1.8, u'Iris-virginica'),\n", - " (29, 6.7, 2.5, 5.8, 1.8, u'Iris-virginica'),\n", - " (30, 7.2, 3.6, 6.1, 2.5, u'Iris-virginica')]" - ] - }, - "execution_count": 51, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "%%sql\n", - "DROP TABLE IF EXISTS iris;\n", - "\n", - "CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,\n", - " petal_length FLOAT, petal_width FLOAT, \n", - " class_name text);\n", - " \n", - "INSERT INTO iris VALUES \n", - "(1,5.1,3.5,1.4,0.2,'Iris-setosa'),\n", - "(2,4.9,3.0,1.4,0.2,'Iris-setosa'),\n", - "(3,4.7,3.2,1.3,0.2,'Iris-setosa'),\n", - "(4,4.6,3.1,1.5,0.2,'Iris-setosa'),\n", - "(5,5.0,3.6,1.4,0.2,'Iris-setosa'),\n", - "(6,5.4,3.9,1.7,0.4,'Iris-setosa'),\n", - "(7,4.6,3.4,1.4,0.3,'Iris-setosa'),\n", - "(8,5.0,3.4,1.5,0.2,'Iris-setosa'),\n", - "(9,4.4,2.9,1.4,0.2,'Iris-setosa'),\n", - "(10,4.9,3.1,1.5,0.1,'Iris-setosa'),\n", - "(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),\n", - "(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),\n", - "(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),\n", - "(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),\n", - "(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),\n", - "(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),\n", - "(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),\n", - "(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),\n", - "(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),\n", - "(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),\n", - "(21,6.3,3.3,6.0,2.5,'Iris-virginica'),\n", - "(22,5.8,2.7,5.1,1.9,'Iris-virginica'),\n", - "(23,7.1,3.0,5.9,2.1,'Iris-virginica'),\n", - "(24,6.3,2.9,5.6,1.8,'Iris-virginica'),\n", - "(25,6.5,3.0,5.8,2.2,'Iris-virginica'),\n", - "(26,7.6,3.0,6.6,2.1,'Iris-virginica'),\n", - "(27,4.9,2.5,4.5,1.7,'Iris-virginica'),\n", - "(28,7.3,2.9,6.3,1.8,'Iris-virginica'),\n", - "(29,6.7,2.5,5.8,1.8,'Iris-virginica'),\n", - "(30,7.2,3.6,6.1,2.5,'Iris-virginica');\n", - "\n", - "SELECT * FROM iris ORDER BY id;" - ] - }, - { - "cell_type": "markdown", - "metadata": {}, - "source": [ - "# 3. Get summary data \n", - "Basic summary data using all defaults." - ] - }, - { - "cell_type": "code", - "execution_count": 52, - "metadata": {}, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "Done.\n", - "1 rows affected.\n" - ] - }, - { - "data": { - "text/html": [ - "<table>\n", - " <tr>\n", - " <th>output_table</th>\n", - " <th>row_count</th>\n", - " <th>duration</th>\n", - " </tr>\n", - " <tr>\n", - " <td>iris_summary</td>\n", - " <td>6</td>\n", - " <td>0.0161118507385</td>\n", - " </tr>\n", - "</table>" - ], - "text/plain": [ - "[(u'iris_summary', 6, 0.0161118507385254)]" - ] - }, - "execution_count": 52, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "%%sql\n", - "DROP TABLE IF EXISTS iris_summary;\n", - "\n", - "SELECT * FROM madlib.summary( 'iris', -- Source table\n", - " 'iris_summary' -- Output table\n", - " );" - ] - }, - { - "cell_type": "code", - "execution_count": 53, - "metadata": { - "scrolled": true - }, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "2 rows affected.\n" - ] - }, - { - "data": { - "text/html": [ - "<table>\n", - " <tr>\n", - " <th>group_by</th>\n", - " <th>group_by_value</th>\n", - " <th>target_column</th>\n", - " <th>column_number</th>\n", - " <th>data_type</th>\n", - " <th>row_count</th>\n", - " <th>distinct_values</th>\n", - " <th>missing_values</th>\n", - " <th>blank_values</th>\n", - " <th>fraction_missing</th>\n", - " <th>fraction_blank</th>\n", - " <th>mean</th>\n", - " <th>variance</th>\n", - " <th>min</th>\n", - " <th>max</th>\n", - " <th>quantile_array</th>\n", - " <th>most_frequent_values</th>\n", - " <th>mfv_frequencies</th>\n", - " </tr>\n", - " <tr>\n", - " <td>None</td>\n", - " <td>None</td>\n", - " <td>sepal_length</td>\n", - " <td>2</td>\n", - " <td>float8</td>\n", - " <td>30</td>\n", - " <td>22</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>5.84333333333</td>\n", - " <td>0.929436781609</td>\n", - " <td>4.4</td>\n", - " <td>7.6</td>\n", - " <td>[5.057, 6.414]</td>\n", - " <td>[u'4.9', u'6.3', u'5']</td>\n", - " <td>[4L, 3L, 2L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>None</td>\n", - " <td>None</td>\n", - " <td>sepal_width</td>\n", - " <td>3</td>\n", - " <td>float8</td>\n", - " <td>30</td>\n", - " <td>14</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>3.04</td>\n", - " <td>0.139034482759</td>\n", - " <td>2.3</td>\n", - " <td>3.9</td>\n", - " <td>[2.9, 3.2]</td>\n", - " <td>[u'3', u'2.9', u'3.2']</td>\n", - " <td>[4L, 4L, 3L]</td>\n", - " </tr>\n", - "</table>" - ], - "text/plain": [ - "[(None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, [5.057, 6.414], [u'4.9', u'6.3', u'5'], [4L, 3L, 2L]),\n", - " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, [2.9, 3.2], [u'3', u'2.9', u'3.2'], [4L, 4L, 3L])]" - ] - }, - "execution_count": 53, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "%%sql\n", - "SELECT * FROM iris_summary;" - ] - }, - { - "cell_type": "markdown", - "metadata": {}, - "source": [ - "# 4. Grouping" - ] - }, - { - "cell_type": "code", - "execution_count": 45, - "metadata": {}, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "Done.\n", - "1 rows affected.\n", - "8 rows affected.\n" - ] - }, - { - "data": { - "text/html": [ - "<table>\n", - " <tr>\n", - " <th>group_by</th>\n", - " <th>group_by_value</th>\n", - " <th>target_column</th>\n", - " <th>column_number</th>\n", - " <th>data_type</th>\n", - " <th>row_count</th>\n", - " <th>distinct_values</th>\n", - " <th>missing_values</th>\n", - " <th>blank_values</th>\n", - " <th>fraction_missing</th>\n", - " <th>fraction_blank</th>\n", - " <th>mean</th>\n", - " <th>variance</th>\n", - " <th>min</th>\n", - " <th>max</th>\n", - " <th>first_quartile</th>\n", - " <th>median</th>\n", - " <th>third_quartile</th>\n", - " <th>most_frequent_values</th>\n", - " <th>mfv_frequencies</th>\n", - " </tr>\n", - " <tr>\n", - " <td>class_name</td>\n", - " <td>Iris-setosa</td>\n", - " <td>sepal_length</td>\n", - " <td>2</td>\n", - " <td>float8</td>\n", - " <td>10</td>\n", - " <td>7</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>4.86</td>\n", - " <td>0.0848888888889</td>\n", - " <td>4.4</td>\n", - " <td>5.4</td>\n", - " <td>4.625</td>\n", - " <td>4.9</td>\n", - " <td>5.0</td>\n", - " <td>[u'4.6', u'4.9', u'5', u'5.1', u'4.4', u'5.4', u'4.7']</td>\n", - " <td>[2L, 2L, 2L, 1L, 1L, 1L, 1L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>class_name</td>\n", - " <td>Iris-setosa</td>\n", - " <td>sepal_width</td>\n", - " <td>3</td>\n", - " <td>float8</td>\n", - " <td>10</td>\n", - " <td>8</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>3.31</td>\n", - " <td>0.0943333333333</td>\n", - " <td>2.9</td>\n", - " <td>3.9</td>\n", - " <td>3.1</td>\n", - " <td>3.3</td>\n", - " <td>3.475</td>\n", - " <td>[u'3.4', u'3.1', u'3.2', u'3.6', u'3.9', u'3.5', u'2.9', u'3']</td>\n", - " <td>[2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>class_name</td>\n", - " <td>Iris-versicolor</td>\n", - " <td>sepal_length</td>\n", - " <td>2</td>\n", - " <td>float8</td>\n", - " <td>10</td>\n", - " <td>10</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>6.1</td>\n", - " <td>0.528888888889</td>\n", - " <td>4.9</td>\n", - " <td>7.0</td>\n", - " <td>5.55</td>\n", - " <td>6.35</td>\n", - " <td>6.575</td>\n", - " <td>[u'7', u'6.4', u'6.9', u'5.5', u'6.5', u'5.7', u'6.3', u'4.9', u'6.6', u'5.2']</td>\n", - " <td>[1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>class_name</td>\n", - " <td>Iris-versicolor</td>\n", - " <td>sepal_width</td>\n", - " <td>3</td>\n", - " <td>float8</td>\n", - " <td>10</td>\n", - " <td>8</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>2.87</td>\n", - " <td>0.115666666667</td>\n", - " <td>2.3</td>\n", - " <td>3.3</td>\n", - " <td>2.725</td>\n", - " <td>2.85</td>\n", - " <td>3.175</td>\n", - " <td>[u'2.8', u'3.2', u'2.3', u'3.3', u'2.4', u'2.9', u'2.7', u'3.1']</td>\n", - " <td>[2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>class_name</td>\n", - " <td>Iris-virginica</td>\n", - " <td>sepal_length</td>\n", - " <td>2</td>\n", - " <td>float8</td>\n", - " <td>10</td>\n", - " <td>9</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>6.57</td>\n", - " <td>0.646777777778</td>\n", - " <td>4.9</td>\n", - " <td>7.6</td>\n", - " <td>6.3</td>\n", - " <td>6.6</td>\n", - " <td>7.175</td>\n", - " <td>[u'6.3', u'5.8', u'7.1', u'6.5', u'7.6', u'4.9', u'7.3', u'6.7', u'7.2']</td>\n", - " <td>[2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>class_name</td>\n", - " <td>Iris-virginica</td>\n", - " <td>sepal_width</td>\n", - " <td>3</td>\n", - " <td>float8</td>\n", - " <td>10</td>\n", - " <td>6</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>2.94</td>\n", - " <td>0.113777777778</td>\n", - " <td>2.5</td>\n", - " <td>3.6</td>\n", - " <td>2.75</td>\n", - " <td>2.95</td>\n", - " <td>3.0</td>\n", - " <td>[u'3', u'2.9', u'2.5', u'3.3', u'2.7', u'3.6']</td>\n", - " <td>[3L, 2L, 2L, 1L, 1L, 1L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>None</td>\n", - " <td>None</td>\n", - " <td>sepal_length</td>\n", - " <td>2</td>\n", - " <td>float8</td>\n", - " <td>30</td>\n", - " <td>22</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>5.84333333333</td>\n", - " <td>0.929436781609</td>\n", - " <td>4.4</td>\n", - " <td>7.6</td>\n", - " <td>4.925</td>\n", - " <td>5.75</td>\n", - " <td>6.575</td>\n", - " <td>[u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4']</td>\n", - " <td>[4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>None</td>\n", - " <td>None</td>\n", - " <td>sepal_width</td>\n", - " <td>3</td>\n", - " <td>float8</td>\n", - " <td>30</td>\n", - " <td>14</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>3.04</td>\n", - " <td>0.139034482759</td>\n", - " <td>2.3</td>\n", - " <td>3.9</td>\n", - " <td>2.825</td>\n", - " <td>3.0</td>\n", - " <td>3.275</td>\n", - " <td>[u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4']</td>\n", - " <td>[4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L]</td>\n", - " </tr>\n", - "</table>" - ], - "text/plain": [ - "[(u'class_name', u'Iris-setosa', u'sepal_length', 2, u'float8', 10L, 7L, 0L, None, 0.0, None, 4.86, 0.0848888888888976, 4.4, 5.4, 4.625, 4.9, 5.0, [u'4.6', u'4.9', u'5', u'5.1', u'4.4', u'5.4', u'4.7'], [2L, 2L, 2L, 1L, 1L, 1L, 1L]),\n", - " (u'class_name', u'Iris-setosa', u'sepal_width', 3, u'float8', 10L, 8L, 0L, None, 0.0, None, 3.31, 0.094333333333336, 2.9, 3.9, 3.1, 3.3, 3.475, [u'3.4', u'3.1', u'3.2', u'3.6', u'3.9', u'3.5', u'2.9', u'3'], [2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", - " (u'class_name', u'Iris-versicolor', u'sepal_length', 2, u'float8', 10L, 10L, 0L, None, 0.0, None, 6.1, 0.528888888888893, 4.9, 7.0, 5.55, 6.35, 6.575, [u'7', u'6.4', u'6.9', u'5.5', u'6.5', u'5.7', u'6.3', u'4.9', u'6.6', u'5.2'], [1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", - " (u'class_name', u'Iris-versicolor', u'sepal_width', 3, u'float8', 10L, 8L, 0L, None, 0.0, None, 2.87, 0.115666666666666, 2.3, 3.3, 2.725, 2.85, 3.175, [u'2.8', u'3.2', u'2.3', u'3.3', u'2.4', u'2.9', u'2.7', u'3.1'], [2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", - " (u'class_name', u'Iris-virginica', u'sepal_length', 2, u'float8', 10L, 9L, 0L, None, 0.0, None, 6.57, 0.646777777777778, 4.9, 7.6, 6.3, 6.6, 7.175, [u'6.3', u'5.8', u'7.1', u'6.5', u'7.6', u'4.9', u'7.3', u'6.7', u'7.2'], [2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", - " (u'class_name', u'Iris-virginica', u'sepal_width', 3, u'float8', 10L, 6L, 0L, None, 0.0, None, 2.94, 0.11377777777778, 2.5, 3.6, 2.75, 2.95, 3.0, [u'3', u'2.9', u'2.5', u'3.3', u'2.7', u'3.6'], [3L, 2L, 2L, 1L, 1L, 1L]),\n", - " (None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, 4.925, 5.75, 6.575, [u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4'], [4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]),\n", - " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, 2.825, 3.0, 3.275, [u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4'], [4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L])]" - ] - }, - "execution_count": 45, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "%%sql\n", - "DROP TABLE IF EXISTS iris_summary;\n", - "\n", - "SELECT * FROM madlib.summary( 'iris', -- Source table\n", - " 'iris_summary', -- Output table\n", - " 'sepal_length, sepal_width', -- Columns to summarize\n", - " 'class_name' -- Grouping column\n", - " );\n", - "\n", - "SELECT * FROM iris_summary;" - ] - }, - { - "cell_type": "markdown", - "metadata": {}, - "source": [ - "# 5. Other parameters" - ] - }, - { - "cell_type": "code", - "execution_count": 50, - "metadata": {}, - "outputs": [ - { - "name": "stdout", - "output_type": "stream", - "text": [ - "Done.\n", - "1 rows affected.\n", - "2 rows affected.\n" - ] - }, - { - "data": { - "text/html": [ - "<table>\n", - " <tr>\n", - " <th>group_by</th>\n", - " <th>group_by_value</th>\n", - " <th>target_column</th>\n", - " <th>column_number</th>\n", - " <th>data_type</th>\n", - " <th>row_count</th>\n", - " <th>distinct_values</th>\n", - " <th>missing_values</th>\n", - " <th>blank_values</th>\n", - " <th>fraction_missing</th>\n", - " <th>fraction_blank</th>\n", - " <th>mean</th>\n", - " <th>variance</th>\n", - " <th>min</th>\n", - " <th>max</th>\n", - " <th>quantile_array</th>\n", - " <th>most_frequent_values</th>\n", - " <th>mfv_frequencies</th>\n", - " </tr>\n", - " <tr>\n", - " <td>None</td>\n", - " <td>None</td>\n", - " <td>sepal_length</td>\n", - " <td>2</td>\n", - " <td>float8</td>\n", - " <td>30</td>\n", - " <td>22</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>5.84333333333</td>\n", - " <td>0.929436781609</td>\n", - " <td>4.4</td>\n", - " <td>7.6</td>\n", - " <td>[5.057, 6.414]</td>\n", - " <td>[u'4.9', u'6.3', u'5']</td>\n", - " <td>[4L, 3L, 2L]</td>\n", - " </tr>\n", - " <tr>\n", - " <td>None</td>\n", - " <td>None</td>\n", - " <td>sepal_width</td>\n", - " <td>3</td>\n", - " <td>float8</td>\n", - " <td>30</td>\n", - " <td>14</td>\n", - " <td>0</td>\n", - " <td>None</td>\n", - " <td>0.0</td>\n", - " <td>None</td>\n", - " <td>3.04</td>\n", - " <td>0.139034482759</td>\n", - " <td>2.3</td>\n", - " <td>3.9</td>\n", - " <td>[2.9, 3.2]</td>\n", - " <td>[u'3', u'2.9', u'3.2']</td>\n", - " <td>[4L, 4L, 3L]</td>\n", - " </tr>\n", - "</table>" - ], - "text/plain": [ - "[(None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, [5.057, 6.414], [u'4.9', u'6.3', u'5'], [4L, 3L, 2L]),\n", - " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, [2.9, 3.2], [u'3', u'2.9', u'3.2'], [4L, 4L, 3L])]" - ] - }, - "execution_count": 50, - "metadata": {}, - "output_type": "execute_result" - } - ], - "source": [ - "%%sql\n", - "DROP TABLE IF EXISTS iris_summary;\n", - "\n", - "SELECT * FROM madlib.summary( 'iris', -- Source table\n", - " 'iris_summary', -- Output table\n", - " 'sepal_length, sepal_width', -- Columns to summarize\n", - " NULL, -- No grouping\n", - " TRUE, -- Get distinct values\n", - " FALSE, -- Dont get quartiles\n", - " ARRAY[0.33, 0.66], -- Get ntiles\n", - " 3, -- Number of MFV to compute\n", - " FALSE -- Get exact values\n", - " );\n", - "\n", - "SELECT * FROM iris_summary;" - ] - } - ], - "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": 1 -}
http://git-wip-us.apache.org/repos/asf/madlib-site/blob/3f849b9e/community-artifacts/Summary-v2.ipynb ---------------------------------------------------------------------- diff --git a/community-artifacts/Summary-v2.ipynb b/community-artifacts/Summary-v2.ipynb new file mode 100644 index 0000000..f8c7a39 --- /dev/null +++ b/community-artifacts/Summary-v2.ipynb @@ -0,0 +1,1017 @@ +{ + "cells": [ + { + "cell_type": "code", + "execution_count": 1, + "metadata": {}, + "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": {}, + "outputs": [ + { + "data": { + "text/plain": [ + "u'Connected: fmcquillan@madlib'" + ] + }, + "execution_count": 2, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "# Greenplum 4.3.10.0\n", + "#%sql postgresql://gpdbchina@10.194.10.68:61000/madlib\n", + " \n", + "# PostgreSQL local\n", + "%sql postgresql://fmcquillan@localhost:5432/madlib" + ] + }, + { + "cell_type": "code", + "execution_count": 3, + "metadata": {}, + "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.13, git revision: unknown, cmake configuration time: Wed Dec 20 08:02:21 UTC 2017, build type: Release, build system: Darwin-17.3.0, C compiler: Clang, C++ compiler: Clang</td>\n", + " </tr>\n", + "</table>" + ], + "text/plain": [ + "[(u'MADlib version: 1.13, git revision: unknown, cmake configuration time: Wed Dec 20 08:02:21 UTC 2017, build type: Release, build system: Darwin-17.3.0, C compiler: Clang, C++ compiler: Clang',)]" + ] + }, + "execution_count": 3, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "%sql select madlib.version();\n", + "#%sql select version();" + ] + }, + { + "cell_type": "markdown", + "metadata": {}, + "source": [ + "# 1. On-line help" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": { + "collapsed": true + }, + "outputs": [], + "source": [ + "%sql SELECT * FROM madlib.summary();" + ] + }, + { + "cell_type": "markdown", + "metadata": {}, + "source": [ + "# 2. Load data\n", + "Using part of the popular iris data set from https://archive.ics.uci.edu/ml/datasets/iris" + ] + }, + { + "cell_type": "code", + "execution_count": 4, + "metadata": {}, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "Done.\n", + "Done.\n", + "30 rows affected.\n", + "30 rows affected.\n" + ] + }, + { + "data": { + "text/html": [ + "<table>\n", + " <tr>\n", + " <th>id</th>\n", + " <th>sepal_length</th>\n", + " <th>sepal_width</th>\n", + " <th>petal_length</th>\n", + " <th>petal_width</th>\n", + " <th>class_name</th>\n", + " </tr>\n", + " <tr>\n", + " <td>1</td>\n", + " <td>5.1</td>\n", + " <td>3.5</td>\n", + " <td>1.4</td>\n", + " <td>0.2</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>2</td>\n", + " <td>4.9</td>\n", + " <td>3.0</td>\n", + " <td>1.4</td>\n", + " <td>0.2</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>3</td>\n", + " <td>4.7</td>\n", + " <td>3.2</td>\n", + " <td>1.3</td>\n", + " <td>0.2</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>4</td>\n", + " <td>4.6</td>\n", + " <td>3.1</td>\n", + " <td>1.5</td>\n", + " <td>0.2</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>5</td>\n", + " <td>5.0</td>\n", + " <td>3.6</td>\n", + " <td>1.4</td>\n", + " <td>0.2</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>6</td>\n", + " <td>5.4</td>\n", + " <td>3.9</td>\n", + " <td>1.7</td>\n", + " <td>0.4</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>7</td>\n", + " <td>4.6</td>\n", + " <td>3.4</td>\n", + " <td>1.4</td>\n", + " <td>0.3</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>8</td>\n", + " <td>5.0</td>\n", + " <td>3.4</td>\n", + " <td>1.5</td>\n", + " <td>0.2</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>9</td>\n", + " <td>4.4</td>\n", + " <td>2.9</td>\n", + " <td>1.4</td>\n", + " <td>0.2</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>10</td>\n", + " <td>4.9</td>\n", + " <td>3.1</td>\n", + " <td>1.5</td>\n", + " <td>0.1</td>\n", + " <td>Iris-setosa</td>\n", + " </tr>\n", + " <tr>\n", + " <td>11</td>\n", + " <td>7.0</td>\n", + " <td>3.2</td>\n", + " <td>4.7</td>\n", + " <td>1.4</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>12</td>\n", + " <td>6.4</td>\n", + " <td>3.2</td>\n", + " <td>4.5</td>\n", + " <td>1.5</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>13</td>\n", + " <td>6.9</td>\n", + " <td>3.1</td>\n", + " <td>4.9</td>\n", + " <td>1.5</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>14</td>\n", + " <td>5.5</td>\n", + " <td>2.3</td>\n", + " <td>4.0</td>\n", + " <td>1.3</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>15</td>\n", + " <td>6.5</td>\n", + " <td>2.8</td>\n", + " <td>4.6</td>\n", + " <td>1.5</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>16</td>\n", + " <td>5.7</td>\n", + " <td>2.8</td>\n", + " <td>4.5</td>\n", + " <td>1.3</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>17</td>\n", + " <td>6.3</td>\n", + " <td>3.3</td>\n", + " <td>4.7</td>\n", + " <td>1.6</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>18</td>\n", + " <td>4.9</td>\n", + " <td>2.4</td>\n", + " <td>3.3</td>\n", + " <td>1.0</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>19</td>\n", + " <td>6.6</td>\n", + " <td>2.9</td>\n", + " <td>4.6</td>\n", + " <td>1.3</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>20</td>\n", + " <td>5.2</td>\n", + " <td>2.7</td>\n", + " <td>3.9</td>\n", + " <td>1.4</td>\n", + " <td>Iris-versicolor</td>\n", + " </tr>\n", + " <tr>\n", + " <td>21</td>\n", + " <td>6.3</td>\n", + " <td>3.3</td>\n", + " <td>6.0</td>\n", + " <td>2.5</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>22</td>\n", + " <td>5.8</td>\n", + " <td>2.7</td>\n", + " <td>5.1</td>\n", + " <td>1.9</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>23</td>\n", + " <td>7.1</td>\n", + " <td>3.0</td>\n", + " <td>5.9</td>\n", + " <td>2.1</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>24</td>\n", + " <td>6.3</td>\n", + " <td>2.9</td>\n", + " <td>5.6</td>\n", + " <td>1.8</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>25</td>\n", + " <td>6.5</td>\n", + " <td>3.0</td>\n", + " <td>5.8</td>\n", + " <td>2.2</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>26</td>\n", + " <td>7.6</td>\n", + " <td>3.0</td>\n", + " <td>6.6</td>\n", + " <td>2.1</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>27</td>\n", + " <td>4.9</td>\n", + " <td>2.5</td>\n", + " <td>4.5</td>\n", + " <td>1.7</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>28</td>\n", + " <td>7.3</td>\n", + " <td>2.9</td>\n", + " <td>6.3</td>\n", + " <td>1.8</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>29</td>\n", + " <td>6.7</td>\n", + " <td>2.5</td>\n", + " <td>5.8</td>\n", + " <td>1.8</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + " <tr>\n", + " <td>30</td>\n", + " <td>7.2</td>\n", + " <td>3.6</td>\n", + " <td>6.1</td>\n", + " <td>2.5</td>\n", + " <td>Iris-virginica</td>\n", + " </tr>\n", + "</table>" + ], + "text/plain": [ + "[(1, 5.1, 3.5, 1.4, 0.2, u'Iris-setosa'),\n", + " (2, 4.9, 3.0, 1.4, 0.2, u'Iris-setosa'),\n", + " (3, 4.7, 3.2, 1.3, 0.2, u'Iris-setosa'),\n", + " (4, 4.6, 3.1, 1.5, 0.2, u'Iris-setosa'),\n", + " (5, 5.0, 3.6, 1.4, 0.2, u'Iris-setosa'),\n", + " (6, 5.4, 3.9, 1.7, 0.4, u'Iris-setosa'),\n", + " (7, 4.6, 3.4, 1.4, 0.3, u'Iris-setosa'),\n", + " (8, 5.0, 3.4, 1.5, 0.2, u'Iris-setosa'),\n", + " (9, 4.4, 2.9, 1.4, 0.2, u'Iris-setosa'),\n", + " (10, 4.9, 3.1, 1.5, 0.1, u'Iris-setosa'),\n", + " (11, 7.0, 3.2, 4.7, 1.4, u'Iris-versicolor'),\n", + " (12, 6.4, 3.2, 4.5, 1.5, u'Iris-versicolor'),\n", + " (13, 6.9, 3.1, 4.9, 1.5, u'Iris-versicolor'),\n", + " (14, 5.5, 2.3, 4.0, 1.3, u'Iris-versicolor'),\n", + " (15, 6.5, 2.8, 4.6, 1.5, u'Iris-versicolor'),\n", + " (16, 5.7, 2.8, 4.5, 1.3, u'Iris-versicolor'),\n", + " (17, 6.3, 3.3, 4.7, 1.6, u'Iris-versicolor'),\n", + " (18, 4.9, 2.4, 3.3, 1.0, u'Iris-versicolor'),\n", + " (19, 6.6, 2.9, 4.6, 1.3, u'Iris-versicolor'),\n", + " (20, 5.2, 2.7, 3.9, 1.4, u'Iris-versicolor'),\n", + " (21, 6.3, 3.3, 6.0, 2.5, u'Iris-virginica'),\n", + " (22, 5.8, 2.7, 5.1, 1.9, u'Iris-virginica'),\n", + " (23, 7.1, 3.0, 5.9, 2.1, u'Iris-virginica'),\n", + " (24, 6.3, 2.9, 5.6, 1.8, u'Iris-virginica'),\n", + " (25, 6.5, 3.0, 5.8, 2.2, u'Iris-virginica'),\n", + " (26, 7.6, 3.0, 6.6, 2.1, u'Iris-virginica'),\n", + " (27, 4.9, 2.5, 4.5, 1.7, u'Iris-virginica'),\n", + " (28, 7.3, 2.9, 6.3, 1.8, u'Iris-virginica'),\n", + " (29, 6.7, 2.5, 5.8, 1.8, u'Iris-virginica'),\n", + " (30, 7.2, 3.6, 6.1, 2.5, u'Iris-virginica')]" + ] + }, + "execution_count": 4, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "%%sql\n", + "DROP TABLE IF EXISTS iris;\n", + "\n", + "CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,\n", + " petal_length FLOAT, petal_width FLOAT, \n", + " class_name text);\n", + " \n", + "INSERT INTO iris VALUES \n", + "(1,5.1,3.5,1.4,0.2,'Iris-setosa'),\n", + "(2,4.9,3.0,1.4,0.2,'Iris-setosa'),\n", + "(3,4.7,3.2,1.3,0.2,'Iris-setosa'),\n", + "(4,4.6,3.1,1.5,0.2,'Iris-setosa'),\n", + "(5,5.0,3.6,1.4,0.2,'Iris-setosa'),\n", + "(6,5.4,3.9,1.7,0.4,'Iris-setosa'),\n", + "(7,4.6,3.4,1.4,0.3,'Iris-setosa'),\n", + "(8,5.0,3.4,1.5,0.2,'Iris-setosa'),\n", + "(9,4.4,2.9,1.4,0.2,'Iris-setosa'),\n", + "(10,4.9,3.1,1.5,0.1,'Iris-setosa'),\n", + "(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),\n", + "(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),\n", + "(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),\n", + "(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),\n", + "(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),\n", + "(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),\n", + "(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),\n", + "(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),\n", + "(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),\n", + "(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),\n", + "(21,6.3,3.3,6.0,2.5,'Iris-virginica'),\n", + "(22,5.8,2.7,5.1,1.9,'Iris-virginica'),\n", + "(23,7.1,3.0,5.9,2.1,'Iris-virginica'),\n", + "(24,6.3,2.9,5.6,1.8,'Iris-virginica'),\n", + "(25,6.5,3.0,5.8,2.2,'Iris-virginica'),\n", + "(26,7.6,3.0,6.6,2.1,'Iris-virginica'),\n", + "(27,4.9,2.5,4.5,1.7,'Iris-virginica'),\n", + "(28,7.3,2.9,6.3,1.8,'Iris-virginica'),\n", + "(29,6.7,2.5,5.8,1.8,'Iris-virginica'),\n", + "(30,7.2,3.6,6.1,2.5,'Iris-virginica');\n", + "\n", + "SELECT * FROM iris ORDER BY id;" + ] + }, + { + "cell_type": "markdown", + "metadata": {}, + "source": [ + "# 3. Get summary data \n", + "Basic summary data using all defaults." + ] + }, + { + "cell_type": "code", + "execution_count": 6, + "metadata": {}, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "Done.\n", + "1 rows affected.\n" + ] + }, + { + "data": { + "text/html": [ + "<table>\n", + " <tr>\n", + " <th>output_table</th>\n", + " <th>row_count</th>\n", + " <th>duration</th>\n", + " </tr>\n", + " <tr>\n", + " <td>iris_summary</td>\n", + " <td>6</td>\n", + " <td>0.0278480052948</td>\n", + " </tr>\n", + "</table>" + ], + "text/plain": [ + "[(u'iris_summary', 6, 0.0278480052947998)]" + ] + }, + "execution_count": 6, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "%%sql\n", + "DROP TABLE IF EXISTS iris_summary;\n", + "\n", + "SELECT * FROM madlib.summary( 'iris', -- Source table\n", + " 'iris_summary' -- Output table\n", + " );" + ] + }, + { + "cell_type": "code", + "execution_count": 7, + "metadata": { + "scrolled": true + }, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "6 rows affected.\n" + ] + }, + { + "data": { + "text/html": [ + "<table>\n", + " <tr>\n", + " <th>group_by</th>\n", + " <th>group_by_value</th>\n", + " <th>target_column</th>\n", + " <th>column_number</th>\n", + " <th>data_type</th>\n", + " <th>row_count</th>\n", + " <th>distinct_values</th>\n", + " <th>missing_values</th>\n", + " <th>blank_values</th>\n", + " <th>fraction_missing</th>\n", + " <th>fraction_blank</th>\n", + " <th>mean</th>\n", + " <th>variance</th>\n", + " <th>min</th>\n", + " <th>max</th>\n", + " <th>first_quartile</th>\n", + " <th>median</th>\n", + " <th>third_quartile</th>\n", + " <th>most_frequent_values</th>\n", + " <th>mfv_frequencies</th>\n", + " </tr>\n", + " <tr>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>id</td>\n", + " <td>1</td>\n", + " <td>int4</td>\n", + " <td>30</td>\n", + " <td>30</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>15.5</td>\n", + " <td>77.5</td>\n", + " <td>1.0</td>\n", + " <td>30.0</td>\n", + " <td>8.25</td>\n", + " <td>15.5</td>\n", + " <td>22.75</td>\n", + " <td>[u'1', u'2', u'3', u'4', u'5', u'6', u'7', u'8', u'9', u'10']</td>\n", + " <td>[1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>sepal_length</td>\n", + " <td>2</td>\n", + " <td>float8</td>\n", + " <td>30</td>\n", + " <td>22</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>5.84333333333</td>\n", + " <td>0.929436781609</td>\n", + " <td>4.4</td>\n", + " <td>7.6</td>\n", + " <td>4.925</td>\n", + " <td>5.75</td>\n", + " <td>6.575</td>\n", + " <td>[u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4']</td>\n", + " <td>[4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>sepal_width</td>\n", + " <td>3</td>\n", + " <td>float8</td>\n", + " <td>30</td>\n", + " <td>14</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>3.04</td>\n", + " <td>0.139034482759</td>\n", + " <td>2.3</td>\n", + " <td>3.9</td>\n", + " <td>2.825</td>\n", + " <td>3.0</td>\n", + " <td>3.275</td>\n", + " <td>[u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4']</td>\n", + " <td>[4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>petal_length</td>\n", + " <td>4</td>\n", + " <td>float8</td>\n", + " <td>30</td>\n", + " <td>19</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>3.86333333333</td>\n", + " <td>3.53964367816</td>\n", + " <td>1.3</td>\n", + " <td>6.6</td>\n", + " <td>1.5</td>\n", + " <td>4.5</td>\n", + " <td>5.475</td>\n", + " <td>[u'1.4', u'1.5', u'4.5', u'5.8', u'4.6', u'4.7', u'3.3', u'4.9', u'1.7', u'4']</td>\n", + " <td>[5L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>petal_width</td>\n", + " <td>5</td>\n", + " <td>float8</td>\n", + " <td>30</td>\n", + " <td>15</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>1.21333333333</td>\n", + " <td>0.622574712644</td>\n", + " <td>0.1</td>\n", + " <td>2.5</td>\n", + " <td>0.225</td>\n", + " <td>1.4</td>\n", + " <td>1.8</td>\n", + " <td>[u'0.2', u'1.8', u'1.5', u'1.3', u'1.4', u'2.1', u'2.5', u'1.6', u'1', u'0.1']</td>\n", + " <td>[7L, 3L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>class_name</td>\n", + " <td>6</td>\n", + " <td>text</td>\n", + " <td>30</td>\n", + " <td>3</td>\n", + " <td>0</td>\n", + " <td>0</td>\n", + " <td>0.0</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>11.0</td>\n", + " <td>15.0</td>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>[u'Iris-setosa', u'Iris-versicolor', u'Iris-virginica']</td>\n", + " <td>[10L, 10L, 10L]</td>\n", + " </tr>\n", + "</table>" + ], + "text/plain": [ + "[(None, None, u'id', 1, u'int4', 30L, 30L, 0L, None, 0.0, None, 15.5, 77.5, 1.0, 30.0, 8.25, 15.5, 22.75, [u'1', u'2', u'3', u'4', u'5', u'6', u'7', u'8', u'9', u'10'], [1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", + " (None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, 4.925, 5.75, 6.575, [u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4'], [4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]),\n", + " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, 2.825, 3.0, 3.275, [u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4'], [4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L]),\n", + " (None, None, u'petal_length', 4, u'float8', 30L, 19L, 0L, None, 0.0, None, 3.86333333333333, 3.53964367816093, 1.3, 6.6, 1.5, 4.5, 5.475, [u'1.4', u'1.5', u'4.5', u'5.8', u'4.6', u'4.7', u'3.3', u'4.9', u'1.7', u'4'], [5L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L]),\n", + " (None, None, u'petal_width', 5, u'float8', 30L, 15L, 0L, None, 0.0, None, 1.21333333333333, 0.622574712643679, 0.1, 2.5, 0.225, 1.4, 1.8, [u'0.2', u'1.8', u'1.5', u'1.3', u'1.4', u'2.1', u'2.5', u'1.6', u'1', u'0.1'], [7L, 3L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L]),\n", + " (None, None, u'class_name', 6, u'text', 30L, 3L, 0L, 0L, 0.0, 0.0, None, None, 11.0, 15.0, None, None, None, [u'Iris-setosa', u'Iris-versicolor', u'Iris-virginica'], [10L, 10L, 10L])]" + ] + }, + "execution_count": 7, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "%%sql\n", + "SELECT * FROM iris_summary;" + ] + }, + { + "cell_type": "markdown", + "metadata": {}, + "source": [ + "# 4. Grouping" + ] + }, + { + "cell_type": "code", + "execution_count": 8, + "metadata": {}, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "Done.\n", + "1 rows affected.\n", + "8 rows affected.\n" + ] + }, + { + "data": { + "text/html": [ + "<table>\n", + " <tr>\n", + " <th>group_by</th>\n", + " <th>group_by_value</th>\n", + " <th>target_column</th>\n", + " <th>column_number</th>\n", + " <th>data_type</th>\n", + " <th>row_count</th>\n", + " <th>distinct_values</th>\n", + " <th>missing_values</th>\n", + " <th>blank_values</th>\n", + " <th>fraction_missing</th>\n", + " <th>fraction_blank</th>\n", + " <th>mean</th>\n", + " <th>variance</th>\n", + " <th>min</th>\n", + " <th>max</th>\n", + " <th>first_quartile</th>\n", + " <th>median</th>\n", + " <th>third_quartile</th>\n", + " <th>most_frequent_values</th>\n", + " <th>mfv_frequencies</th>\n", + " </tr>\n", + " <tr>\n", + " <td>class_name</td>\n", + " <td>Iris-setosa</td>\n", + " <td>sepal_length</td>\n", + " <td>2</td>\n", + " <td>float8</td>\n", + " <td>10</td>\n", + " <td>7</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>4.86</td>\n", + " <td>0.0848888888889</td>\n", + " <td>4.4</td>\n", + " <td>5.4</td>\n", + " <td>4.625</td>\n", + " <td>4.9</td>\n", + " <td>5.0</td>\n", + " <td>[u'4.6', u'4.9', u'5', u'5.1', u'4.4', u'5.4', u'4.7']</td>\n", + " <td>[2L, 2L, 2L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>class_name</td>\n", + " <td>Iris-setosa</td>\n", + " <td>sepal_width</td>\n", + " <td>3</td>\n", + " <td>float8</td>\n", + " <td>10</td>\n", + " <td>8</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>3.31</td>\n", + " <td>0.0943333333333</td>\n", + " <td>2.9</td>\n", + " <td>3.9</td>\n", + " <td>3.1</td>\n", + " <td>3.3</td>\n", + " <td>3.475</td>\n", + " <td>[u'3.4', u'3.1', u'3.2', u'3.6', u'3.9', u'3.5', u'2.9', u'3']</td>\n", + " <td>[2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>class_name</td>\n", + " <td>Iris-versicolor</td>\n", + " <td>sepal_length</td>\n", + " <td>2</td>\n", + " <td>float8</td>\n", + " <td>10</td>\n", + " <td>10</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>6.1</td>\n", + " <td>0.528888888889</td>\n", + " <td>4.9</td>\n", + " <td>7.0</td>\n", + " <td>5.55</td>\n", + " <td>6.35</td>\n", + " <td>6.575</td>\n", + " <td>[u'7', u'6.4', u'6.9', u'5.5', u'6.5', u'5.7', u'6.3', u'4.9', u'6.6', u'5.2']</td>\n", + " <td>[1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>class_name</td>\n", + " <td>Iris-versicolor</td>\n", + " <td>sepal_width</td>\n", + " <td>3</td>\n", + " <td>float8</td>\n", + " <td>10</td>\n", + " <td>8</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>2.87</td>\n", + " <td>0.115666666667</td>\n", + " <td>2.3</td>\n", + " <td>3.3</td>\n", + " <td>2.725</td>\n", + " <td>2.85</td>\n", + " <td>3.175</td>\n", + " <td>[u'2.8', u'3.2', u'2.3', u'3.3', u'2.4', u'2.9', u'2.7', u'3.1']</td>\n", + " <td>[2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>class_name</td>\n", + " <td>Iris-virginica</td>\n", + " <td>sepal_length</td>\n", + " <td>2</td>\n", + " <td>float8</td>\n", + " <td>10</td>\n", + " <td>9</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>6.57</td>\n", + " <td>0.646777777778</td>\n", + " <td>4.9</td>\n", + " <td>7.6</td>\n", + " <td>6.3</td>\n", + " <td>6.6</td>\n", + " <td>7.175</td>\n", + " <td>[u'6.3', u'5.8', u'7.1', u'6.5', u'7.6', u'4.9', u'7.3', u'6.7', u'7.2']</td>\n", + " <td>[2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>class_name</td>\n", + " <td>Iris-virginica</td>\n", + " <td>sepal_width</td>\n", + " <td>3</td>\n", + " <td>float8</td>\n", + " <td>10</td>\n", + " <td>6</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>2.94</td>\n", + " <td>0.113777777778</td>\n", + " <td>2.5</td>\n", + " <td>3.6</td>\n", + " <td>2.75</td>\n", + " <td>2.95</td>\n", + " <td>3.0</td>\n", + " <td>[u'3', u'2.9', u'2.5', u'3.3', u'2.7', u'3.6']</td>\n", + " <td>[3L, 2L, 2L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>sepal_length</td>\n", + " <td>2</td>\n", + " <td>float8</td>\n", + " <td>30</td>\n", + " <td>22</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>5.84333333333</td>\n", + " <td>0.929436781609</td>\n", + " <td>4.4</td>\n", + " <td>7.6</td>\n", + " <td>4.925</td>\n", + " <td>5.75</td>\n", + " <td>6.575</td>\n", + " <td>[u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4']</td>\n", + " <td>[4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]</td>\n", + " </tr>\n", + " <tr>\n", + " <td>None</td>\n", + " <td>None</td>\n", + " <td>sepal_width</td>\n", + " <td>3</td>\n", + " <td>float8</td>\n", + " <td>30</td>\n", + " <td>14</td>\n", + " <td>0</td>\n", + " <td>None</td>\n", + " <td>0.0</td>\n", + " <td>None</td>\n", + " <td>3.04</td>\n", + " <td>0.139034482759</td>\n", + " <td>2.3</td>\n", + " <td>3.9</td>\n", + " <td>2.825</td>\n", + " <td>3.0</td>\n", + " <td>3.275</td>\n", + " <td>[u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4']</td>\n", + " <td>[4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L]</td>\n", + " </tr>\n", + "</table>" + ], + "text/plain": [ + "[(u'class_name', u'Iris-setosa', u'sepal_length', 2, u'float8', 10L, 7L, 0L, None, 0.0, None, 4.86, 0.0848888888888976, 4.4, 5.4, 4.625, 4.9, 5.0, [u'4.6', u'4.9', u'5', u'5.1', u'4.4', u'5.4', u'4.7'], [2L, 2L, 2L, 1L, 1L, 1L, 1L]),\n", + " (u'class_name', u'Iris-setosa', u'sepal_width', 3, u'float8', 10L, 8L, 0L, None, 0.0, None, 3.31, 0.094333333333336, 2.9, 3.9, 3.1, 3.3, 3.475, [u'3.4', u'3.1', u'3.2', u'3.6', u'3.9', u'3.5', u'2.9', u'3'], [2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", + " (u'class_name', u'Iris-versicolor', u'sepal_length', 2, u'float8', 10L, 10L, 0L, None, 0.0, None, 6.1, 0.528888888888893, 4.9, 7.0, 5.55, 6.35, 6.575, [u'7', u'6.4', u'6.9', u'5.5', u'6.5', u'5.7', u'6.3', u'4.9', u'6.6', u'5.2'], [1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", + " (u'class_name', u'Iris-versicolor', u'sepal_width', 3, u'float8', 10L, 8L, 0L, None, 0.0, None, 2.87, 0.115666666666666, 2.3, 3.3, 2.725, 2.85, 3.175, [u'2.8', u'3.2', u'2.3', u'3.3', u'2.4', u'2.9', u'2.7', u'3.1'], [2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", + " (u'class_name', u'Iris-virginica', u'sepal_length', 2, u'float8', 10L, 9L, 0L, None, 0.0, None, 6.57, 0.646777777777778, 4.9, 7.6, 6.3, 6.6, 7.175, [u'6.3', u'5.8', u'7.1', u'6.5', u'7.6', u'4.9', u'7.3', u'6.7', u'7.2'], [2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n", + " (u'class_name', u'Iris-virginica', u'sepal_width', 3, u'float8', 10L, 6L, 0L, None, 0.0, None, 2.94, 0.11377777777778, 2.5, 3.6, 2.75, 2.95, 3.0, [u'3', u'2.9', u'2.5', u'3.3', u'2.7', u'3.6'], [3L, 2L, 2L, 1L, 1L, 1L]),\n", + " (None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, 4.925, 5.75, 6.575, [u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4'], [4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]),\n", + " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, 2.825, 3.0, 3.275, [u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4'], [4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L])]" + ] + }, + "execution_count": 8, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "%%sql\n", + "DROP TABLE IF EXISTS iris_summary;\n", + "\n", + "SELECT * FROM madlib.summary( 'iris', -- Source table\n", + " 'iris_summary', -- Output table\n", + " 'sepal_length, sepal_width', -- Columns to summarize\n", + " 'class_name' -- Grouping column\n", + " );\n", + "\n", + "SELECT * FROM iris_summary;" + ] + }, + { + "cell_type": "markdown", + "metadata": {}, + "source": [ + "# 5. Other parameters" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": { + "collapsed": true + }, + "outputs": [], + "source": [ + "%%sql\n", + "DROP TABLE IF EXISTS iris_summary;\n", + "\n", + "SELECT * FROM madlib.summary( 'iris', -- Source table\n", + " 'iris_summary', -- Output table\n", + " 'sepal_length, sepal_width', -- Columns to summarize\n", + " NULL, -- No grouping\n", + " TRUE, -- Get distinct values\n", + " FALSE, -- Dont get quartiles\n", + " ARRAY[0.33, 0.66], -- Get ntiles\n", + " 3, -- Number of MFV to compute\n", + " FALSE -- Get exact values\n", + " );\n", + "\n", + "SELECT * FROM iris_summary;" + ] + } + ], + "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": 1 +}