You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@madlib.apache.org by GitBox <gi...@apache.org> on 2019/05/06 18:48:22 UTC
[GitHub] [madlib] fmcquillan99 commented on issue #384: MLP - Fixed column
names in the Output Table
fmcquillan99 commented on issue #384: MLP - Fixed column names in the Output Table
URL: https://github.com/apache/madlib/pull/384#issuecomment-489731762
LGTM
```
madlib=# DROP TABLE IF EXISTS iris_data;
NOTICE: table "iris_data" does not exist, skipping
DROP TABLE
madlib=# CREATE TABLE iris_data(
madlib(# id serial,
madlib(# attributes numeric[],
madlib(# class_text varchar,
madlib(# class integer,
madlib(# state varchar
madlib(# );
NOTICE: CREATE TABLE will create implicit sequence "iris_data_id_seq" for serial column "iris_data.id"
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
madlib=# INSERT INTO iris_data(id, attributes, class_text, class, state) VALUES
madlib-# (1,ARRAY[5.0,3.2,1.2,0.2],'Iris_setosa',1,'Alaska'),
madlib-# (2,ARRAY[5.5,3.5,1.3,0.2],'Iris_setosa',1,'Alaska'),
madlib-# (3,ARRAY[4.9,3.1,1.5,0.1],'Iris_setosa',1,'Alaska'),
madlib-# (4,ARRAY[4.4,3.0,1.3,0.2],'Iris_setosa',1,'Alaska'),
madlib-# (5,ARRAY[5.1,3.4,1.5,0.2],'Iris_setosa',1,'Alaska'),
madlib-# (6,ARRAY[5.0,3.5,1.3,0.3],'Iris_setosa',1,'Alaska'),
madlib-# (7,ARRAY[4.5,2.3,1.3,0.3],'Iris_setosa',1,'Alaska'),
madlib-# (8,ARRAY[4.4,3.2,1.3,0.2],'Iris_setosa',1,'Alaska'),
madlib-# (9,ARRAY[5.0,3.5,1.6,0.6],'Iris_setosa',1,'Alaska'),
madlib-# (10,ARRAY[5.1,3.8,1.9,0.4],'Iris_setosa',1,'Alaska'),
madlib-# (11,ARRAY[4.8,3.0,1.4,0.3],'Iris_setosa',1,'Alaska'),
madlib-# (12,ARRAY[5.1,3.8,1.6,0.2],'Iris_setosa',1,'Alaska'),
madlib-# (13,ARRAY[5.7,2.8,4.5,1.3],'Iris_versicolor',2,'Alaska'),
madlib-# (14,ARRAY[6.3,3.3,4.7,1.6],'Iris_versicolor',2,'Alaska'),
madlib-# (15,ARRAY[4.9,2.4,3.3,1.0],'Iris_versicolor',2,'Alaska'),
madlib-# (16,ARRAY[6.6,2.9,4.6,1.3],'Iris_versicolor',2,'Alaska'),
madlib-# (17,ARRAY[5.2,2.7,3.9,1.4],'Iris_versicolor',2,'Alaska'),
madlib-# (18,ARRAY[5.0,2.0,3.5,1.0],'Iris_versicolor',2,'Alaska'),
madlib-# (19,ARRAY[5.9,3.0,4.2,1.5],'Iris_versicolor',2,'Alaska'),
madlib-# (20,ARRAY[6.0,2.2,4.0,1.0],'Iris_versicolor',2,'Alaska'),
madlib-# (21,ARRAY[6.1,2.9,4.7,1.4],'Iris_versicolor',2,'Alaska'),
madlib-# (22,ARRAY[5.6,2.9,3.6,1.3],'Iris_versicolor',2,'Alaska'),
madlib-# (23,ARRAY[6.7,3.1,4.4,1.4],'Iris_versicolor',2,'Alaska'),
madlib-# (24,ARRAY[5.6,3.0,4.5,1.5],'Iris_versicolor',2,'Alaska'),
madlib-# (25,ARRAY[5.8,2.7,4.1,1.0],'Iris_versicolor',2,'Alaska'),
madlib-# (26,ARRAY[6.2,2.2,4.5,1.5],'Iris_versicolor',2,'Alaska'),
madlib-# (27,ARRAY[5.6,2.5,3.9,1.1],'Iris_versicolor',2,'Alaska'),
madlib-# (28,ARRAY[5.0,3.4,1.5,0.2],'Iris_setosa',1,'Tennessee'),
madlib-# (29,ARRAY[4.4,2.9,1.4,0.2],'Iris_setosa',1,'Tennessee'),
madlib-# (30,ARRAY[4.9,3.1,1.5,0.1],'Iris_setosa',1,'Tennessee'),
madlib-# (31,ARRAY[5.4,3.7,1.5,0.2],'Iris_setosa',1,'Tennessee'),
madlib-# (32,ARRAY[4.8,3.4,1.6,0.2],'Iris_setosa',1,'Tennessee'),
madlib-# (33,ARRAY[4.8,3.0,1.4,0.1],'Iris_setosa',1,'Tennessee'),
madlib-# (34,ARRAY[4.3,3.0,1.1,0.1],'Iris_setosa',1,'Tennessee'),
madlib-# (35,ARRAY[5.8,4.0,1.2,0.2],'Iris_setosa',1,'Tennessee'),
madlib-# (36,ARRAY[5.7,4.4,1.5,0.4],'Iris_setosa',1,'Tennessee'),
madlib-# (37,ARRAY[5.4,3.9,1.3,0.4],'Iris_setosa',1,'Tennessee'),
madlib-# (38,ARRAY[6.0,2.9,4.5,1.5],'Iris_versicolor',2,'Tennessee'),
madlib-# (39,ARRAY[5.7,2.6,3.5,1.0],'Iris_versicolor',2,'Tennessee'),
madlib-# (40,ARRAY[5.5,2.4,3.8,1.1],'Iris_versicolor',2,'Tennessee'),
madlib-# (41,ARRAY[5.5,2.4,3.7,1.0],'Iris_versicolor',2,'Tennessee'),
madlib-# (42,ARRAY[5.8,2.7,3.9,1.2],'Iris_versicolor',2,'Tennessee'),
madlib-# (43,ARRAY[6.0,2.7,5.1,1.6],'Iris_versicolor',2,'Tennessee'),
madlib-# (44,ARRAY[5.4,3.0,4.5,1.5],'Iris_versicolor',2,'Tennessee'),
madlib-# (45,ARRAY[6.0,3.4,4.5,1.6],'Iris_versicolor',2,'Tennessee'),
madlib-# (46,ARRAY[6.7,3.1,4.7,1.5],'Iris_versicolor',2,'Tennessee'),
madlib-# (47,ARRAY[6.3,2.3,4.4,1.3],'Iris_versicolor',2,'Tennessee'),
madlib-# (48,ARRAY[5.6,3.0,4.1,1.3],'Iris_versicolor',2,'Tennessee'),
madlib-# (49,ARRAY[5.5,2.5,4.0,1.3],'Iris_versicolor',2,'Tennessee'),
madlib-# (50,ARRAY[5.5,2.6,4.4,1.2],'Iris_versicolor',2,'Tennessee'),
madlib-# (51,ARRAY[6.1,3.0,4.6,1.4],'Iris_versicolor',2,'Tennessee'),
madlib-# (52,ARRAY[5.8,2.6,4.0,1.2],'Iris_versicolor',2,'Tennessee');
INSERT 0 52
madlib=#
madlib=#
madlib=#
madlib=#
madlib=# DROP TABLE IF EXISTS mlp_model, mlp_model_summary, mlp_model_standardization;
NOTICE: table "mlp_model" does not exist, skipping
NOTICE: table "mlp_model_summary" does not exist, skipping
NOTICE: table "mlp_model_standardization" does not exist, skipping
DROP TABLE
madlib=# -- Set seed so results are reproducible
madlib=# SELECT setseed(0);
setseed
---------
(1 row)
madlib=# SELECT madlib.mlp_classification(
madlib(# 'iris_data', -- Source table
madlib(# 'mlp_model', -- Destination table
madlib(# 'attributes', -- Input features
madlib(# 'class_text', -- Label
madlib(# ARRAY[5], -- Number of units per layer
madlib(# 'learning_rate_init=0.003,
madlib'# n_iterations=2,
madlib'# tolerance=0', -- Optimizer params
madlib(# 'tanh', -- Activation function
madlib(# NULL, -- Default weight (1)
madlib(# FALSE, -- No warm start
madlib(# FALSE -- Not verbose
madlib(# );
mlp_classification
--------------------
(1 row)
madlib=#
madlib=#
madlib=# DROP TABLE IF EXISTS mlp_prediction;
NOTICE: table "mlp_prediction" does not exist, skipping
DROP TABLE
madlib=# \x off
Expanded display is off.
madlib=# SELECT madlib.mlp_predict(
madlib(# 'mlp_model', -- Model table
madlib(# 'iris_data', -- Test data table
madlib(# 'id', -- Id column in test table
madlib(# 'mlp_prediction', -- Output table for predictions
madlib(# 'response' -- Output classes, not probabilities
madlib(# );
mlp_predict
-------------
(1 row)
madlib=# SELECT * FROM mlp_prediction JOIN iris_data USING (id) ORDER BY id;
id | estimated_class_text | attributes | class_text | class | state
----+----------------------+-------------------+-----------------+-------+-----------
1 | Iris_setosa | {5.0,3.2,1.2,0.2} | Iris_setosa | 1 | Alaska
2 | Iris_setosa | {5.5,3.5,1.3,0.2} | Iris_setosa | 1 | Alaska
3 | Iris_setosa | {4.9,3.1,1.5,0.1} | Iris_setosa | 1 | Alaska
4 | Iris_setosa | {4.4,3.0,1.3,0.2} | Iris_setosa | 1 | Alaska
5 | Iris_setosa | {5.1,3.4,1.5,0.2} | Iris_setosa | 1 | Alaska
6 | Iris_setosa | {5.0,3.5,1.3,0.3} | Iris_setosa | 1 | Alaska
7 | Iris_setosa | {4.5,2.3,1.3,0.3} | Iris_setosa | 1 | Alaska
8 | Iris_setosa | {4.4,3.2,1.3,0.2} | Iris_setosa | 1 | Alaska
9 | Iris_setosa | {5.0,3.5,1.6,0.6} | Iris_setosa | 1 | Alaska
10 | Iris_setosa | {5.1,3.8,1.9,0.4} | Iris_setosa | 1 | Alaska
11 | Iris_setosa | {4.8,3.0,1.4,0.3} | Iris_setosa | 1 | Alaska
12 | Iris_setosa | {5.1,3.8,1.6,0.2} | Iris_setosa | 1 | Alaska
13 | Iris_versicolor | {5.7,2.8,4.5,1.3} | Iris_versicolor | 2 | Alaska
14 | Iris_versicolor | {6.3,3.3,4.7,1.6} | Iris_versicolor | 2 | Alaska
15 | Iris_setosa | {4.9,2.4,3.3,1.0} | Iris_versicolor | 2 | Alaska
16 | Iris_versicolor | {6.6,2.9,4.6,1.3} | Iris_versicolor | 2 | Alaska
17 | Iris_versicolor | {5.2,2.7,3.9,1.4} | Iris_versicolor | 2 | Alaska
18 | Iris_versicolor | {5.0,2.0,3.5,1.0} | Iris_versicolor | 2 | Alaska
19 | Iris_versicolor | {5.9,3.0,4.2,1.5} | Iris_versicolor | 2 | Alaska
20 | Iris_versicolor | {6.0,2.2,4.0,1.0} | Iris_versicolor | 2 | Alaska
21 | Iris_versicolor | {6.1,2.9,4.7,1.4} | Iris_versicolor | 2 | Alaska
22 | Iris_versicolor | {5.6,2.9,3.6,1.3} | Iris_versicolor | 2 | Alaska
23 | Iris_versicolor | {6.7,3.1,4.4,1.4} | Iris_versicolor | 2 | Alaska
24 | Iris_versicolor | {5.6,3.0,4.5,1.5} | Iris_versicolor | 2 | Alaska
25 | Iris_versicolor | {5.8,2.7,4.1,1.0} | Iris_versicolor | 2 | Alaska
26 | Iris_versicolor | {6.2,2.2,4.5,1.5} | Iris_versicolor | 2 | Alaska
27 | Iris_versicolor | {5.6,2.5,3.9,1.1} | Iris_versicolor | 2 | Alaska
28 | Iris_setosa | {5.0,3.4,1.5,0.2} | Iris_setosa | 1 | Tennessee
29 | Iris_setosa | {4.4,2.9,1.4,0.2} | Iris_setosa | 1 | Tennessee
30 | Iris_setosa | {4.9,3.1,1.5,0.1} | Iris_setosa | 1 | Tennessee
31 | Iris_setosa | {5.4,3.7,1.5,0.2} | Iris_setosa | 1 | Tennessee
32 | Iris_setosa | {4.8,3.4,1.6,0.2} | Iris_setosa | 1 | Tennessee
33 | Iris_setosa | {4.8,3.0,1.4,0.1} | Iris_setosa | 1 | Tennessee
34 | Iris_setosa | {4.3,3.0,1.1,0.1} | Iris_setosa | 1 | Tennessee
35 | Iris_setosa | {5.8,4.0,1.2,0.2} | Iris_setosa | 1 | Tennessee
36 | Iris_setosa | {5.7,4.4,1.5,0.4} | Iris_setosa | 1 | Tennessee
37 | Iris_setosa | {5.4,3.9,1.3,0.4} | Iris_setosa | 1 | Tennessee
38 | Iris_versicolor | {6.0,2.9,4.5,1.5} | Iris_versicolor | 2 | Tennessee
39 | Iris_versicolor | {5.7,2.6,3.5,1.0} | Iris_versicolor | 2 | Tennessee
40 | Iris_versicolor | {5.5,2.4,3.8,1.1} | Iris_versicolor | 2 | Tennessee
41 | Iris_versicolor | {5.5,2.4,3.7,1.0} | Iris_versicolor | 2 | Tennessee
42 | Iris_versicolor | {5.8,2.7,3.9,1.2} | Iris_versicolor | 2 | Tennessee
43 | Iris_versicolor | {6.0,2.7,5.1,1.6} | Iris_versicolor | 2 | Tennessee
44 | Iris_versicolor | {5.4,3.0,4.5,1.5} | Iris_versicolor | 2 | Tennessee
45 | Iris_versicolor | {6.0,3.4,4.5,1.6} | Iris_versicolor | 2 | Tennessee
46 | Iris_versicolor | {6.7,3.1,4.7,1.5} | Iris_versicolor | 2 | Tennessee
47 | Iris_versicolor | {6.3,2.3,4.4,1.3} | Iris_versicolor | 2 | Tennessee
48 | Iris_versicolor | {5.6,3.0,4.1,1.3} | Iris_versicolor | 2 | Tennessee
49 | Iris_versicolor | {5.5,2.5,4.0,1.3} | Iris_versicolor | 2 | Tennessee
50 | Iris_versicolor | {5.5,2.6,4.4,1.2} | Iris_versicolor | 2 | Tennessee
51 | Iris_versicolor | {6.1,3.0,4.6,1.4} | Iris_versicolor | 2 | Tennessee
52 | Iris_versicolor | {5.8,2.6,4.0,1.2} | Iris_versicolor | 2 | Tennessee
(52 rows)
madlib=#
madlib=#
madlib=# DROP TABLE IF EXISTS mlp_prediction;
DROP TABLE
madlib=# \x off
Expanded display is off.
madlib=# SELECT madlib.mlp_predict(
madlib(# 'mlp_model', -- Model table
madlib(# 'iris_data', -- Test data table
madlib(# 'id', -- Id column in test table
madlib(# 'mlp_prediction', -- Output table for predictions
madlib(# 'prob' -- Output classes, not probabilities
madlib(# );
mlp_predict
-------------
(1 row)
madlib=# SELECT * FROM mlp_prediction JOIN iris_data USING (id) ORDER BY id;
id | prob_Iris_setosa | prob_Iris_versicolor | attributes | class_text | class | state
----+-------------------+----------------------+-------------------+-----------------+-------+-----------
1 | 0.666174532805849 | 0.333825467194151 | {5.0,3.2,1.2,0.2} | Iris_setosa | 1 | Alaska
2 | 0.623958544634086 | 0.376041455365914 | {5.5,3.5,1.3,0.2} | Iris_setosa | 1 | Alaska
3 | 0.665599637404062 | 0.334400362595938 | {4.9,3.1,1.5,0.1} | Iris_setosa | 1 | Alaska
4 | 0.69967468724987 | 0.30032531275013 | {4.4,3.0,1.3,0.2} | Iris_setosa | 1 | Alaska
5 | 0.650589350186648 | 0.349410649813352 | {5.1,3.4,1.5,0.2} | Iris_setosa | 1 | Alaska
6 | 0.663709611723239 | 0.336290388276761 | {5.0,3.5,1.3,0.3} | Iris_setosa | 1 | Alaska
7 | 0.675248608383111 | 0.324751391616889 | {4.5,2.3,1.3,0.3} | Iris_setosa | 1 | Alaska
8 | 0.703124938604637 | 0.296875061395363 | {4.4,3.2,1.3,0.2} | Iris_setosa | 1 | Alaska
9 | 0.637098431634514 | 0.362901568365486 | {5.0,3.5,1.6,0.6} | Iris_setosa | 1 | Alaska
10 | 0.631752903785035 | 0.368247096214965 | {5.1,3.8,1.9,0.4} | Iris_setosa | 1 | Alaska
11 | 0.665992441859372 | 0.334007558140628 | {4.8,3.0,1.4,0.3} | Iris_setosa | 1 | Alaska
12 | 0.654122896704659 | 0.345877103295341 | {5.1,3.8,1.6,0.2} | Iris_setosa | 1 | Alaska
13 | 0.368818396572769 | 0.631181603427231 | {5.7,2.8,4.5,1.3} | Iris_versicolor | 2 | Alaska
14 | 0.32729448376684 | 0.67270551623316 | {6.3,3.3,4.7,1.6} | Iris_versicolor | 2 | Alaska
15 | 0.511588731030722 | 0.488411268969278 | {4.9,2.4,3.3,1.0} | Iris_versicolor | 2 | Alaska
16 | 0.312293999827632 | 0.687706000172368 | {6.6,2.9,4.6,1.3} | Iris_versicolor | 2 | Alaska
17 | 0.431575754430078 | 0.568424245569922 | {5.2,2.7,3.9,1.4} | Iris_versicolor | 2 | Alaska
18 | 0.477032252308531 | 0.522967747691469 | {5.0,2.0,3.5,1.0} | Iris_versicolor | 2 | Alaska
19 | 0.360964626631117 | 0.639035373368883 | {5.9,3.0,4.2,1.5} | Iris_versicolor | 2 | Alaska
20 | 0.358371054681968 | 0.641628945318031 | {6.0,2.2,4.0,1.0} | Iris_versicolor | 2 | Alaska
21 | 0.333503763115969 | 0.666496236884031 | {6.1,2.9,4.7,1.4} | Iris_versicolor | 2 | Alaska
22 | 0.41609135101373 | 0.58390864898627 | {5.6,2.9,3.6,1.3} | Iris_versicolor | 2 | Alaska
23 | 0.314518780348681 | 0.685481219651319 | {6.7,3.1,4.4,1.4} | Iris_versicolor | 2 | Alaska
24 | 0.374234117146093 | 0.625765882853907 | {5.6,3.0,4.5,1.5} | Iris_versicolor | 2 | Alaska
25 | 0.386125072425895 | 0.613874927574105 | {5.8,2.7,4.1,1.0} | Iris_versicolor | 2 | Alaska
26 | 0.312315390027158 | 0.687684609972842 | {6.2,2.2,4.5,1.5} | Iris_versicolor | 2 | Alaska
27 | 0.401452930747385 | 0.598547069252615 | {5.6,2.5,3.9,1.1} | Iris_versicolor | 2 | Alaska
28 | 0.659022857590838 | 0.340977142409162 | {5.0,3.4,1.5,0.2} | Iris_setosa | 1 | Tennessee
29 | 0.695023192185683 | 0.304976807814317 | {4.4,2.9,1.4,0.2} | Iris_setosa | 1 | Tennessee
30 | 0.665599637404062 | 0.334400362595938 | {4.9,3.1,1.5,0.1} | Iris_setosa | 1 | Tennessee
31 | 0.629232765741101 | 0.370767234258899 | {5.4,3.7,1.5,0.2} | Iris_setosa | 1 | Tennessee
32 | 0.671132849420246 | 0.328867150579754 | {4.8,3.4,1.6,0.2} | Iris_setosa | 1 | Tennessee
33 | 0.674549537991219 | 0.325450462008781 | {4.8,3.0,1.4,0.1} | Iris_setosa | 1 | Tennessee
34 | 0.713075054931339 | 0.286924945068661 | {4.3,3.0,1.1,0.1} | Iris_setosa | 1 | Tennessee
35 | 0.608638139763174 | 0.391361860236826 | {5.8,4.0,1.2,0.2} | Iris_setosa | 1 | Tennessee
36 | 0.601557087287665 | 0.398442912712336 | {5.7,4.4,1.5,0.4} | Iris_setosa | 1 | Tennessee
37 | 0.630725900302699 | 0.369274099697301 | {5.4,3.9,1.3,0.4} | Iris_setosa | 1 | Tennessee
38 | 0.341828733438096 | 0.658171266561904 | {6.0,2.9,4.5,1.5} | Iris_versicolor | 2 | Tennessee
39 | 0.417481151579777 | 0.582518848420223 | {5.7,2.6,3.5,1.0} | Iris_versicolor | 2 | Tennessee
40 | 0.412667968922148 | 0.587332031077852 | {5.5,2.4,3.8,1.1} | Iris_versicolor | 2 | Tennessee
41 | 0.422989371981319 | 0.577010628018681 | {5.5,2.4,3.7,1.0} | Iris_versicolor | 2 | Tennessee
42 | 0.383891542809219 | 0.616108457190781 | {5.8,2.7,3.9,1.2} | Iris_versicolor | 2 | Tennessee
43 | 0.317435758525586 | 0.682564241474414 | {6.0,2.7,5.1,1.6} | Iris_versicolor | 2 | Tennessee
44 | 0.391313394378737 | 0.608686605621263 | {5.4,3.0,4.5,1.5} | Iris_versicolor | 2 | Tennessee
45 | 0.35346507763096 | 0.64653492236904 | {6.0,3.4,4.5,1.6} | Iris_versicolor | 2 | Tennessee
46 | 0.306016706551455 | 0.693983293448545 | {6.7,3.1,4.7,1.5} | Iris_versicolor | 2 | Tennessee
47 | 0.317659270538081 | 0.682340729461919 | {6.3,2.3,4.4,1.3} | Iris_versicolor | 2 | Tennessee
48 | 0.398806291221914 | 0.601193708778086 | {5.6,3.0,4.1,1.3} | Iris_versicolor | 2 | Tennessee
49 | 0.396157329616885 | 0.603842670383115 | {5.5,2.5,4.0,1.3} | Iris_versicolor | 2 | Tennessee
50 | 0.388394756376795 | 0.611605243623205 | {5.5,2.6,4.4,1.2} | Iris_versicolor | 2 | Tennessee
51 | 0.339084264519136 | 0.660915735480864 | {6.1,3.0,4.6,1.4} | Iris_versicolor | 2 | Tennessee
52 | 0.377039255659268 | 0.622960744340732 | {5.8,2.6,4.0,1.2} | Iris_versicolor | 2 | Tennessee
(52 rows)
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services