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: [email protected] With regards, Apache Git Services
