doc/keras_iris-tensorflow.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Using TensorFlow backend.\n"
     ]
    }
   ],
   "source": [
    "import os, numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "os.environ[\"KERAS_BACKEND\"] = \"tensorflow\"\n",
    "\n",
    "import keras \n",
    "from sklearn import datasets\n",
    "\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "from keras.utils import np_utils\n",
    "\n",
    "\n",
    "keras.backend.set_floatx('float64')\n",
    "\n",
    "from keras.utils import np_utils\n",
    "\n",
    "\n",
    "def set_reproducible():\n",
    "    import tensorflow as tf\n",
    "    import random as rn\n",
    "    import os\n",
    "    os.environ['PYTHONHASHSEED'] = '1960'\n",
    "    rn.seed(1960)\n",
    "    np.random.seed(1960)\n",
    "    session_conf = tf.ConfigProto(intra_op_parallelism_threads=1, inter_op_parallelism_threads=1)\n",
    "\n",
    "    from keras import backend as K\n",
    "    tf.set_random_seed(1960)\n",
    "\n",
    "    sess = tf.Session(graph=tf.get_default_graph(), config=session_conf)\n",
    "    # K.set_session(sess)\n",
    "\n",
    "set_reproducible()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Build a Keras Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def create_model():\n",
    "    from keras.models import Sequential\n",
    "    from keras.layers.core import Dense, Activation, Dropout\n",
    "    from keras.utils import np_utils\n",
    "\n",
    "    model = Sequential()\n",
    "    model.add(Dense(5, input_shape=(4,) , activation='relu'))\n",
    "    model.add(Dropout(0.3))\n",
    "    model.add(Dense(3))\n",
    "    model.add(Activation('softmax'))\n",
    "    model.compile(loss='categorical_crossentropy', optimizer='adam')\n",
    "    return model\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "iris  = datasets.load_iris()\n",
    "train_X, test_X, train_y, test_y = train_test_split(iris.data, iris.target, train_size=0.8, test_size=0.2, random_state=1960)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: Logging before flag parsing goes to stderr.\n",
      "W0704 22:15:07.022100 140187357849408 deprecation.py:506] From /home/antoine/.local/lib/python3.7/site-packages/keras/backend/tensorflow_backend.py:3445: calling dropout (from tensorflow.python.ops.nn_ops) with keep_prob is deprecated and will be removed in a future version.\n",
      "Instructions for updating:\n",
      "Please use `rate` instead of `keep_prob`. Rate should be set to `rate = 1 - keep_prob`.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(120, 4) (120,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<keras.callbacks.History at 0x7f7f732ebbe0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "from keras.wrappers.scikit_learn import KerasClassifier\n",
    "\n",
    "clf = KerasClassifier(build_fn=create_model, epochs=12, verbose=0)\n",
    "\n",
    "print(train_X.shape , train_y.shape)\n",
    "clf.fit(train_X, train_y, verbose=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'sequential_1', 'trainable': True, '_is_compiled': True, '_expects_training_arg': False, '_initial_weights': None, 'supports_masking': False, 'optimizer': <keras.optimizers.Adam object at 0x7f7f73314dd8>, '_updates': [], '_losses': [], '_per_input_losses': {}, '_per_input_updates': {}, '_layers': [<keras.engine.input_layer.InputLayer object at 0x7f7fd0f71cc0>, <keras.layers.core.Dense object at 0x7f7fd0f716a0>, <keras.layers.core.Dropout object at 0x7f7fd0f49fd0>, <keras.layers.core.Dense object at 0x7f7fd0f49828>, <keras.layers.core.Activation object at 0x7f7fd1015780>], '_outbound_nodes': [], '_inbound_nodes': [<keras.engine.base_layer.Node object at 0x7f7fd0f49748>], '_is_graph_network': True, '_uses_inputs_arg': True, 'outputs': [<tf.Tensor 'activation_1/Softmax:0' shape=(?, 3) dtype=float64>], 'inputs': [<tf.Tensor 'dense_1_input:0' shape=(?, 4) dtype=float64>], '_built': True, '_build_input_shape': None, '_compute_previous_mask': True, '_input_layers': [<keras.engine.input_layer.InputLayer object at 0x7f7fd0f71cc0>], '_output_layers': [<keras.layers.core.Activation object at 0x7f7fd1015780>], '_input_coordinates': [(<keras.engine.input_layer.InputLayer object at 0x7f7fd0f71cc0>, 0, 0)], '_output_coordinates': [(<keras.layers.core.Activation object at 0x7f7fd1015780>, 0, 0)], '_output_mask_cache': {'140186943264976_8537744': None}, '_output_tensor_cache': {}, '_output_shape_cache': {}, '_network_nodes': {'activation_1_ib-0', 'dense_2_ib-0', 'dense_1_input_ib-0', 'dropout_1_ib-0', 'dense_1_ib-0'}, '_nodes_by_depth': {0: [<keras.engine.base_layer.Node object at 0x7f7f732933c8>], 1: [<keras.engine.base_layer.Node object at 0x7f7f732ebe48>], 2: [<keras.engine.base_layer.Node object at 0x7f7f73314d30>], 3: [<keras.engine.base_layer.Node object at 0x7f7f779dc908>], 4: [<keras.engine.base_layer.Node object at 0x7f7fd0f71978>]}, '_layers_by_depth': {0: [<keras.layers.core.Activation object at 0x7f7fd1015780>], 1: [<keras.layers.core.Dense object at 0x7f7fd0f49828>], 2: [<keras.layers.core.Dropout object at 0x7f7fd0f49fd0>], 3: [<keras.layers.core.Dense object at 0x7f7fd0f716a0>], 4: [<keras.engine.input_layer.InputLayer object at 0x7f7fd0f71cc0>]}, 'input_names': ['dense_1_input'], 'output_names': ['activation_1'], '_feed_input_names': ['dense_1_input'], '_feed_inputs': [<tf.Tensor 'dense_1_input:0' shape=(?, 4) dtype=float64>], '_feed_input_shapes': [(None, 4)], 'loss': 'categorical_crossentropy', 'metrics': [], 'loss_weights': None, 'sample_weight_mode': None, 'weighted_metrics': None, 'loss_functions': [<function categorical_crossentropy at 0x7f7f7cc5ee18>], '_feed_outputs': [<tf.Tensor 'activation_1/Softmax:0' shape=(?, 3) dtype=float64>], '_feed_output_names': ['activation_1'], '_feed_output_shapes': [(None, 3)], '_feed_loss_fns': [<function categorical_crossentropy at 0x7f7f7cc5ee18>], 'targets': [<tf.Tensor 'activation_1_target:0' shape=(?, ?) dtype=float64>], '_feed_targets': [<tf.Tensor 'activation_1_target:0' shape=(?, ?) dtype=float64>], 'sample_weight_modes': [None], '_feed_sample_weight_modes': [None], 'metrics_names': ['loss'], 'metrics_tensors': [], 'metrics_updates': [], 'stateful_metric_names': [], 'stateful_metric_functions': [], 'total_loss': <tf.Tensor 'loss/mul:0' shape=() dtype=float64>, 'sample_weights': [<tf.Tensor 'activation_1_sample_weights:0' shape=(?,) dtype=float64>], '_feed_sample_weights': [<tf.Tensor 'activation_1_sample_weights:0' shape=(?,) dtype=float64>], '_function_kwargs': {}, 'train_function': <keras.backend.tensorflow_backend.Function object at 0x7f7f732939e8>, 'test_function': None, 'predict_function': None, '_collected_trainable_weights': [<tf.Variable 'dense_1/kernel:0' shape=(4, 5) dtype=float64_ref>, <tf.Variable 'dense_1/bias:0' shape=(5,) dtype=float64_ref>, <tf.Variable 'dense_2/kernel:0' shape=(5, 3) dtype=float64_ref>, <tf.Variable 'dense_2/bias:0' shape=(3,) dtype=float64_ref>], 'history': <keras.callbacks.History object at 0x7f7f732ebbe0>, 'stop_training': False}\n"
     ]
    }
   ],
   "source": [
    "print(clf.model.__dict__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(30, 4)\n",
      "[2]\n"
     ]
    }
   ],
   "source": [
    "print(test_X.shape)\n",
    "preds = clf.predict(test_X[0,:].reshape(1,4))\n",
    "print(preds)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate SQL Code from the Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json, requests, base64, dill as pickle, sys\n",
    "\n",
    "\n",
    "\n",
    "sys.setrecursionlimit(200000)\n",
    "pickle.settings['recurse'] = False\n",
    "\n",
    "def test_ws_sql_gen(pickle_data):\n",
    "    WS_URL=\"https://sklearn2sql.herokuapp.com/model\"\n",
    "    b64_data = base64.b64encode(pickle_data).decode('utf-8')\n",
    "    data={\"Name\":\"model1\", \"PickleData\":b64_data , \"SQLDialect\":\"postgresql\"}\n",
    "    r = requests.post(WS_URL, json=data)\n",
    "    # print(r.__dict__)\n",
    "    content = r.json()\n",
    "    # print(content)\n",
    "    lSQL = content[\"model\"][\"SQLGenrationResult\"][0][\"SQL\"]\n",
    "    return lSQL;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "pickle_data = pickle.dumps(clf)\n",
    "lSQL = test_ws_sql_gen(pickle_data)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WITH keras_input AS \n",
      "(SELECT \"ADS\".\"KEY\" AS \"KEY\", \"ADS\".\"Feature_0\" AS \"Feature_0\", \"ADS\".\"Feature_1\" AS \"Feature_1\", \"ADS\".\"Feature_2\" AS \"Feature_2\", \"ADS\".\"Feature_3\" AS \"Feature_3\" \n",
      "FROM \"INPUT_DATA\" AS \"ADS\"), \n",
      "keras_input_1 AS \n",
      "(SELECT keras_input.\"KEY\" AS \"KEY\", keras_input.\"Feature_0\" AS \"Feature_0\", keras_input.\"Feature_1\" AS \"Feature_1\", keras_input.\"Feature_2\" AS \"Feature_2\", keras_input.\"Feature_3\" AS \"Feature_3\" \n",
      "FROM keras_input), \n",
      "layer_dense_1 AS \n",
      "(SELECT keras_input_1.\"KEY\" AS \"KEY\", 0.03819891008326152 + -0.10551810947064454 * keras_input_1.\"Feature_0\" + 0.6869137954445582 * keras_input_1.\"Feature_1\" + 0.07362650595219875 * keras_input_1.\"Feature_2\" + -0.12291325345623008 * keras_input_1.\"Feature_3\" AS output_1, -0.004487723888804159 + 0.4496606551243778 * keras_input_1.\"Feature_0\" + -0.0020958056354704584 * keras_input_1.\"Feature_1\" + -0.1450205413490414 * keras_input_1.\"Feature_2\" + 0.8403572775801047 * keras_input_1.\"Feature_3\" AS output_2, -0.04482508398542805 + 0.17791137734562554 * keras_input_1.\"Feature_0\" + -0.3350349568524566 * keras_input_1.\"Feature_1\" + 0.031023602127139147 * keras_input_1.\"Feature_2\" + -0.0075448812943813455 * keras_input_1.\"Feature_3\" AS output_3, -0.043295752210237286 + 0.7013113232354665 * keras_input_1.\"Feature_0\" + 0.5786289501536911 * keras_input_1.\"Feature_1\" + -0.41829834050713494 * keras_input_1.\"Feature_2\" + 0.33755178759913435 * keras_input_1.\"Feature_3\" AS output_4, 0.0 + -0.2435283836134725 * keras_input_1.\"Feature_0\" + 0.4004721524270922 * keras_input_1.\"Feature_1\" + -0.7896110374245525 * keras_input_1.\"Feature_2\" + -0.510125423105696 * keras_input_1.\"Feature_3\" AS output_5 \n",
      "FROM keras_input_1), \n",
      "activation_relu AS \n",
      "(SELECT layer_dense_1.\"KEY\" AS \"KEY\", greatest(layer_dense_1.output_1, 0) AS output_1, greatest(layer_dense_1.output_2, 0) AS output_2, greatest(layer_dense_1.output_3, 0) AS output_3, greatest(layer_dense_1.output_4, 0) AS output_4, greatest(layer_dense_1.output_5, 0) AS output_5 \n",
      "FROM layer_dense_1), \n",
      "activation_relu_1 AS \n",
      "(SELECT activation_relu.\"KEY\" AS \"KEY\", activation_relu.output_1 AS output_1, activation_relu.output_2 AS output_2, activation_relu.output_3 AS output_3, activation_relu.output_4 AS output_4, activation_relu.output_5 AS output_5 \n",
      "FROM activation_relu), \n",
      "activation_relu_1_1 AS \n",
      "(SELECT activation_relu_1.\"KEY\" AS \"KEY\", activation_relu_1.output_1 AS output_1, activation_relu_1.output_2 AS output_2, activation_relu_1.output_3 AS output_3, activation_relu_1.output_4 AS output_4, activation_relu_1.output_5 AS output_5 \n",
      "FROM activation_relu_1), \n",
      "layer_dense_2 AS \n",
      "(SELECT activation_relu_1_1.\"KEY\" AS \"KEY\", -0.041216616451408616 + -0.5980049480389407 * activation_relu_1_1.output_1 + -0.59100760371538 * activation_relu_1_1.output_2 + 0.826456023290706 * activation_relu_1_1.output_3 + -0.03591904590537698 * activation_relu_1_1.output_4 + 0.015375282954432579 * activation_relu_1_1.output_5 AS output_1, 0.04615598935740392 + -0.1671102870961577 * activation_relu_1_1.output_1 + -0.4511794395202959 * activation_relu_1_1.output_2 + -0.2568160644683703 * activation_relu_1_1.output_3 + -0.7990628521186195 * activation_relu_1_1.output_4 + 0.39905935927156855 * activation_relu_1_1.output_5 AS output_2, 0.011542176192992937 + -0.15418871317983052 * activation_relu_1_1.output_1 + 0.047406792241336565 * activation_relu_1_1.output_2 + -0.3284395248803203 * activation_relu_1_1.output_3 + -0.5709729308219924 * activation_relu_1_1.output_4 + -0.10397533411660875 * activation_relu_1_1.output_5 AS output_3 \n",
      "FROM activation_relu_1_1), \n",
      "layer_dense_2_1 AS \n",
      "(SELECT layer_dense_2.\"KEY\" AS \"KEY\", layer_dense_2.output_1 AS output_1, layer_dense_2.output_2 AS output_2, layer_dense_2.output_3 AS output_3 \n",
      "FROM layer_dense_2), \n",
      "score_soft_max_step1 AS \n",
      "(SELECT layer_dense_2_1.\"KEY\" AS \"KEY\", layer_dense_2_1.output_1 AS \"Score_0\", exp(least(100.0, greatest(-100.0, layer_dense_2_1.output_1))) AS \"exp_Score_0\", layer_dense_2_1.output_2 AS \"Score_1\", exp(least(100.0, greatest(-100.0, layer_dense_2_1.output_2))) AS \"exp_Score_1\", layer_dense_2_1.output_3 AS \"Score_2\", exp(least(100.0, greatest(-100.0, layer_dense_2_1.output_3))) AS \"exp_Score_2\" \n",
      "FROM layer_dense_2_1), \n",
      "score_class_union_soft AS \n",
      "(SELECT soft_scu.\"KEY\" AS \"KEY\", soft_scu.class AS class, soft_scu.\"exp_Score\" AS \"exp_Score\" \n",
      "FROM (SELECT score_soft_max_step1.\"KEY\" AS \"KEY\", 0 AS class, score_soft_max_step1.\"exp_Score_0\" AS \"exp_Score\" \n",
      "FROM score_soft_max_step1 UNION ALL SELECT score_soft_max_step1.\"KEY\" AS \"KEY\", 1 AS class, score_soft_max_step1.\"exp_Score_1\" AS \"exp_Score\" \n",
      "FROM score_soft_max_step1 UNION ALL SELECT score_soft_max_step1.\"KEY\" AS \"KEY\", 2 AS class, score_soft_max_step1.\"exp_Score_2\" AS \"exp_Score\" \n",
      "FROM score_soft_max_step1) AS soft_scu), \n",
      "score_soft_max AS \n",
      "(SELECT score_soft_max_step1.\"KEY\" AS \"KEY\", score_soft_max_step1.\"Score_0\" AS \"Score_0\", score_soft_max_step1.\"exp_Score_0\" AS \"exp_Score_0\", score_soft_max_step1.\"Score_1\" AS \"Score_1\", score_soft_max_step1.\"exp_Score_1\" AS \"exp_Score_1\", score_soft_max_step1.\"Score_2\" AS \"Score_2\", score_soft_max_step1.\"exp_Score_2\" AS \"exp_Score_2\", sum_exp_t.\"KEY_sum\" AS \"KEY_sum\", sum_exp_t.\"sum_ExpScore\" AS \"sum_ExpScore\" \n",
      "FROM score_soft_max_step1 LEFT OUTER JOIN (SELECT score_class_union_soft.\"KEY\" AS \"KEY_sum\", sum(score_class_union_soft.\"exp_Score\") AS \"sum_ExpScore\" \n",
      "FROM score_class_union_soft GROUP BY score_class_union_soft.\"KEY\") AS sum_exp_t ON score_soft_max_step1.\"KEY\" = sum_exp_t.\"KEY_sum\"), \n",
      "layer_softmax AS \n",
      "(SELECT score_soft_max.\"KEY\" AS \"KEY\", score_soft_max.\"exp_Score_0\" / score_soft_max.\"sum_ExpScore\" AS output_1, score_soft_max.\"exp_Score_1\" / score_soft_max.\"sum_ExpScore\" AS output_2, score_soft_max.\"exp_Score_2\" / score_soft_max.\"sum_ExpScore\" AS output_3 \n",
      "FROM score_soft_max), \n",
      "orig_cte AS \n",
      "(SELECT layer_softmax.\"KEY\" AS \"KEY\", CAST(NULL AS FLOAT) AS \"Score_0\", CAST(NULL AS FLOAT) AS \"Score_1\", CAST(NULL AS FLOAT) AS \"Score_2\", layer_softmax.output_1 AS \"Proba_0\", layer_softmax.output_2 AS \"Proba_1\", layer_softmax.output_3 AS \"Proba_2\", CAST(NULL AS FLOAT) AS \"LogProba_0\", CAST(NULL AS FLOAT) AS \"LogProba_1\", CAST(NULL AS FLOAT) AS \"LogProba_2\", CAST(NULL AS BIGINT) AS \"Decision\", CAST(NULL AS FLOAT) AS \"DecisionProba\" \n",
      "FROM layer_softmax), \n",
      "score_class_union AS \n",
      "(SELECT scu.\"KEY_u\" AS \"KEY_u\", scu.class AS class, scu.\"LogProba\" AS \"LogProba\", scu.\"Proba\" AS \"Proba\", scu.\"Score\" AS \"Score\" \n",
      "FROM (SELECT orig_cte.\"KEY\" AS \"KEY_u\", 0 AS class, orig_cte.\"LogProba_0\" AS \"LogProba\", orig_cte.\"Proba_0\" AS \"Proba\", orig_cte.\"Score_0\" AS \"Score\" \n",
      "FROM orig_cte UNION ALL SELECT orig_cte.\"KEY\" AS \"KEY_u\", 1 AS class, orig_cte.\"LogProba_1\" AS \"LogProba\", orig_cte.\"Proba_1\" AS \"Proba\", orig_cte.\"Score_1\" AS \"Score\" \n",
      "FROM orig_cte UNION ALL SELECT orig_cte.\"KEY\" AS \"KEY_u\", 2 AS class, orig_cte.\"LogProba_2\" AS \"LogProba\", orig_cte.\"Proba_2\" AS \"Proba\", orig_cte.\"Score_2\" AS \"Score\" \n",
      "FROM orig_cte) AS scu), \n",
      "score_max AS \n",
      "(SELECT orig_cte.\"KEY\" AS \"KEY\", orig_cte.\"Score_0\" AS \"Score_0\", orig_cte.\"Score_1\" AS \"Score_1\", orig_cte.\"Score_2\" AS \"Score_2\", orig_cte.\"Proba_0\" AS \"Proba_0\", orig_cte.\"Proba_1\" AS \"Proba_1\", orig_cte.\"Proba_2\" AS \"Proba_2\", orig_cte.\"LogProba_0\" AS \"LogProba_0\", orig_cte.\"LogProba_1\" AS \"LogProba_1\", orig_cte.\"LogProba_2\" AS \"LogProba_2\", orig_cte.\"Decision\" AS \"Decision\", orig_cte.\"DecisionProba\" AS \"DecisionProba\", max_select.\"KEY_m\" AS \"KEY_m\", max_select.\"max_Proba\" AS \"max_Proba\" \n",
      "FROM orig_cte LEFT OUTER JOIN (SELECT score_class_union.\"KEY_u\" AS \"KEY_m\", max(score_class_union.\"Proba\") AS \"max_Proba\" \n",
      "FROM score_class_union GROUP BY score_class_union.\"KEY_u\") AS max_select ON orig_cte.\"KEY\" = max_select.\"KEY_m\"), \n",
      "union_with_max AS \n",
      "(SELECT score_class_union.\"KEY_u\" AS \"KEY_u\", score_class_union.class AS class, score_class_union.\"LogProba\" AS \"LogProba\", score_class_union.\"Proba\" AS \"Proba\", score_class_union.\"Score\" AS \"Score\", score_max.\"KEY\" AS \"KEY\", score_max.\"Score_0\" AS \"Score_0\", score_max.\"Score_1\" AS \"Score_1\", score_max.\"Score_2\" AS \"Score_2\", score_max.\"Proba_0\" AS \"Proba_0\", score_max.\"Proba_1\" AS \"Proba_1\", score_max.\"Proba_2\" AS \"Proba_2\", score_max.\"LogProba_0\" AS \"LogProba_0\", score_max.\"LogProba_1\" AS \"LogProba_1\", score_max.\"LogProba_2\" AS \"LogProba_2\", score_max.\"Decision\" AS \"Decision\", score_max.\"DecisionProba\" AS \"DecisionProba\", score_max.\"KEY_m\" AS \"KEY_m\", score_max.\"max_Proba\" AS \"max_Proba\" \n",
      "FROM score_class_union LEFT OUTER JOIN score_max ON score_class_union.\"KEY_u\" = score_max.\"KEY\"), \n",
      "arg_max_cte AS \n",
      "(SELECT score_max.\"KEY\" AS \"KEY\", score_max.\"Score_0\" AS \"Score_0\", score_max.\"Score_1\" AS \"Score_1\", score_max.\"Score_2\" AS \"Score_2\", score_max.\"Proba_0\" AS \"Proba_0\", score_max.\"Proba_1\" AS \"Proba_1\", score_max.\"Proba_2\" AS \"Proba_2\", score_max.\"LogProba_0\" AS \"LogProba_0\", score_max.\"LogProba_1\" AS \"LogProba_1\", score_max.\"LogProba_2\" AS \"LogProba_2\", score_max.\"Decision\" AS \"Decision\", score_max.\"DecisionProba\" AS \"DecisionProba\", score_max.\"KEY_m\" AS \"KEY_m\", score_max.\"max_Proba\" AS \"max_Proba\", \"arg_max_t_Proba\".\"KEY_Proba\" AS \"KEY_Proba\", \"arg_max_t_Proba\".\"arg_max_Proba\" AS \"arg_max_Proba\" \n",
      "FROM score_max LEFT OUTER JOIN (SELECT union_with_max.\"KEY\" AS \"KEY_Proba\", max(union_with_max.class) AS \"arg_max_Proba\" \n",
      "FROM union_with_max \n",
      "WHERE union_with_max.\"max_Proba\" <= union_with_max.\"Proba\" GROUP BY union_with_max.\"KEY\") AS \"arg_max_t_Proba\" ON score_max.\"KEY\" = \"arg_max_t_Proba\".\"KEY_Proba\")\n",
      " SELECT arg_max_cte.\"KEY\" AS \"KEY\", arg_max_cte.\"Score_0\" AS \"Score_0\", arg_max_cte.\"Score_1\" AS \"Score_1\", arg_max_cte.\"Score_2\" AS \"Score_2\", arg_max_cte.\"Proba_0\" AS \"Proba_0\", arg_max_cte.\"Proba_1\" AS \"Proba_1\", arg_max_cte.\"Proba_2\" AS \"Proba_2\", CASE WHEN (arg_max_cte.\"Proba_0\" IS NULL OR arg_max_cte.\"Proba_0\" > 0.0) THEN ln(arg_max_cte.\"Proba_0\") ELSE -1.79769313486231e+308 END AS \"LogProba_0\", CASE WHEN (arg_max_cte.\"Proba_1\" IS NULL OR arg_max_cte.\"Proba_1\" > 0.0) THEN ln(arg_max_cte.\"Proba_1\") ELSE -1.79769313486231e+308 END AS \"LogProba_1\", CASE WHEN (arg_max_cte.\"Proba_2\" IS NULL OR arg_max_cte.\"Proba_2\" > 0.0) THEN ln(arg_max_cte.\"Proba_2\") ELSE -1.79769313486231e+308 END AS \"LogProba_2\", arg_max_cte.\"arg_max_Proba\" AS \"Decision\", arg_max_cte.\"max_Proba\" AS \"DecisionProba\" \n",
      "FROM arg_max_cte\n"
     ]
    }
   ],
   "source": [
    "print(lSQL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Execute the SQL Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/antoine/.local/lib/python3.7/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.\n",
      "  \"\"\")\n"
     ]
    }
   ],
   "source": [
    "# save the dataset in a database table\n",
    "\n",
    "\n",
    "import sqlalchemy as sa\n",
    "\n",
    "#engine = sa.create_engine('sqlite://' , echo=False)\n",
    "engine = sa.create_engine(\"postgresql://db:db@localhost/db?port=5432\", echo=False)\n",
    "conn = engine.connect()\n",
    "\n",
    "lTable = pd.DataFrame(iris.data);\n",
    "lTable.columns = ['Feature_0', 'Feature_1', 'Feature_2', 'Feature_3']\n",
    "lTable['TGT'] = iris.target\n",
    "lTable['KEY'] = range(iris.data.shape[0])\n",
    "lTable.to_sql(\"INPUT_DATA\" , conn,   if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_output = pd.read_sql(lSQL , conn);\n",
    "sql_output = sql_output.sort_values(by='KEY').reset_index(drop=True)\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>KEY</th>\n",
       "      <th>Score_0</th>\n",
       "      <th>Score_1</th>\n",
       "      <th>Score_2</th>\n",
       "      <th>Proba_0</th>\n",
       "      <th>Proba_1</th>\n",
       "      <th>Proba_2</th>\n",
       "      <th>LogProba_0</th>\n",
       "      <th>LogProba_1</th>\n",
       "      <th>LogProba_2</th>\n",
       "      <th>Decision</th>\n",
       "      <th>DecisionProba</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>114</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.335153</td>\n",
       "      <td>0.035072</td>\n",
       "      <td>0.629774</td>\n",
       "      <td>-1.093168</td>\n",
       "      <td>-3.350341</td>\n",
       "      <td>-0.462394</td>\n",
       "      <td>2</td>\n",
       "      <td>0.629774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>74</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.478754</td>\n",
       "      <td>0.032302</td>\n",
       "      <td>0.488944</td>\n",
       "      <td>-0.736569</td>\n",
       "      <td>-3.432626</td>\n",
       "      <td>-0.715507</td>\n",
       "      <td>2</td>\n",
       "      <td>0.488944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.548498</td>\n",
       "      <td>0.050943</td>\n",
       "      <td>0.400560</td>\n",
       "      <td>-0.600573</td>\n",
       "      <td>-2.977053</td>\n",
       "      <td>-0.914892</td>\n",
       "      <td>0</td>\n",
       "      <td>0.548498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>88</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.404438</td>\n",
       "      <td>0.046393</td>\n",
       "      <td>0.549169</td>\n",
       "      <td>-0.905257</td>\n",
       "      <td>-3.070605</td>\n",
       "      <td>-0.599349</td>\n",
       "      <td>2</td>\n",
       "      <td>0.549169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.541715</td>\n",
       "      <td>0.049148</td>\n",
       "      <td>0.409138</td>\n",
       "      <td>-0.613016</td>\n",
       "      <td>-3.012926</td>\n",
       "      <td>-0.893704</td>\n",
       "      <td>0</td>\n",
       "      <td>0.541715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.545648</td>\n",
       "      <td>0.035097</td>\n",
       "      <td>0.419255</td>\n",
       "      <td>-0.605781</td>\n",
       "      <td>-3.349647</td>\n",
       "      <td>-0.869276</td>\n",
       "      <td>0</td>\n",
       "      <td>0.545648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>48</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.562328</td>\n",
       "      <td>0.038155</td>\n",
       "      <td>0.399517</td>\n",
       "      <td>-0.575670</td>\n",
       "      <td>-3.266102</td>\n",
       "      <td>-0.917499</td>\n",
       "      <td>0</td>\n",
       "      <td>0.562328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>117</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.367966</td>\n",
       "      <td>0.020700</td>\n",
       "      <td>0.611334</td>\n",
       "      <td>-0.999765</td>\n",
       "      <td>-3.877599</td>\n",
       "      <td>-0.492112</td>\n",
       "      <td>2</td>\n",
       "      <td>0.611334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>83</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.412955</td>\n",
       "      <td>0.042532</td>\n",
       "      <td>0.544513</td>\n",
       "      <td>-0.884417</td>\n",
       "      <td>-3.157492</td>\n",
       "      <td>-0.607863</td>\n",
       "      <td>2</td>\n",
       "      <td>0.544513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>105</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.441827</td>\n",
       "      <td>0.022182</td>\n",
       "      <td>0.535991</td>\n",
       "      <td>-0.816838</td>\n",
       "      <td>-3.808478</td>\n",
       "      <td>-0.623637</td>\n",
       "      <td>2</td>\n",
       "      <td>0.535991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.557235</td>\n",
       "      <td>0.040997</td>\n",
       "      <td>0.401767</td>\n",
       "      <td>-0.584768</td>\n",
       "      <td>-3.194245</td>\n",
       "      <td>-0.911883</td>\n",
       "      <td>0</td>\n",
       "      <td>0.557235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>64</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.430517</td>\n",
       "      <td>0.041654</td>\n",
       "      <td>0.527828</td>\n",
       "      <td>-0.842767</td>\n",
       "      <td>-3.178352</td>\n",
       "      <td>-0.638984</td>\n",
       "      <td>2</td>\n",
       "      <td>0.527828</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     KEY Score_0 Score_1 Score_2   Proba_0   Proba_1   Proba_2  LogProba_0  \\\n",
       "114  114    None    None    None  0.335153  0.035072  0.629774   -1.093168   \n",
       "74    74    None    None    None  0.478754  0.032302  0.488944   -0.736569   \n",
       "9      9    None    None    None  0.548498  0.050943  0.400560   -0.600573   \n",
       "88    88    None    None    None  0.404438  0.046393  0.549169   -0.905257   \n",
       "25    25    None    None    None  0.541715  0.049148  0.409138   -0.613016   \n",
       "5      5    None    None    None  0.545648  0.035097  0.419255   -0.605781   \n",
       "48    48    None    None    None  0.562328  0.038155  0.399517   -0.575670   \n",
       "117  117    None    None    None  0.367966  0.020700  0.611334   -0.999765   \n",
       "83    83    None    None    None  0.412955  0.042532  0.544513   -0.884417   \n",
       "105  105    None    None    None  0.441827  0.022182  0.535991   -0.816838   \n",
       "27    27    None    None    None  0.557235  0.040997  0.401767   -0.584768   \n",
       "64    64    None    None    None  0.430517  0.041654  0.527828   -0.842767   \n",
       "\n",
       "     LogProba_1  LogProba_2  Decision  DecisionProba  \n",
       "114   -3.350341   -0.462394         2       0.629774  \n",
       "74    -3.432626   -0.715507         2       0.488944  \n",
       "9     -2.977053   -0.914892         0       0.548498  \n",
       "88    -3.070605   -0.599349         2       0.549169  \n",
       "25    -3.012926   -0.893704         0       0.541715  \n",
       "5     -3.349647   -0.869276         0       0.545648  \n",
       "48    -3.266102   -0.917499         0       0.562328  \n",
       "117   -3.877599   -0.492112         2       0.611334  \n",
       "83    -3.157492   -0.607863         2       0.544513  \n",
       "105   -3.808478   -0.623637         2       0.535991  \n",
       "27    -3.194245   -0.911883         0       0.557235  \n",
       "64    -3.178352   -0.638984         2       0.527828  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_output.sample(12, random_state=1960)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Keras Prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>KEY</th>\n",
       "      <th>Score_0</th>\n",
       "      <th>Score_1</th>\n",
       "      <th>Score_2</th>\n",
       "      <th>Proba_0</th>\n",
       "      <th>Proba_1</th>\n",
       "      <th>Proba_2</th>\n",
       "      <th>LogProba_0</th>\n",
       "      <th>LogProba_1</th>\n",
       "      <th>LogProba_2</th>\n",
       "      <th>Decision</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>114</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.335153</td>\n",
       "      <td>0.035072</td>\n",
       "      <td>0.629774</td>\n",
       "      <td>-1.093168</td>\n",
       "      <td>-3.350341</td>\n",
       "      <td>-0.462394</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.478754</td>\n",
       "      <td>0.032302</td>\n",
       "      <td>0.488944</td>\n",
       "      <td>-0.736569</td>\n",
       "      <td>-3.432626</td>\n",
       "      <td>-0.715507</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.548498</td>\n",
       "      <td>0.050943</td>\n",
       "      <td>0.400560</td>\n",
       "      <td>-0.600573</td>\n",
       "      <td>-2.977053</td>\n",
       "      <td>-0.914892</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.404438</td>\n",
       "      <td>0.046393</td>\n",
       "      <td>0.549169</td>\n",
       "      <td>-0.905257</td>\n",
       "      <td>-3.070605</td>\n",
       "      <td>-0.599349</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.541715</td>\n",
       "      <td>0.049148</td>\n",
       "      <td>0.409138</td>\n",
       "      <td>-0.613016</td>\n",
       "      <td>-3.012926</td>\n",
       "      <td>-0.893704</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.545648</td>\n",
       "      <td>0.035097</td>\n",
       "      <td>0.419255</td>\n",
       "      <td>-0.605781</td>\n",
       "      <td>-3.349647</td>\n",
       "      <td>-0.869276</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.562328</td>\n",
       "      <td>0.038155</td>\n",
       "      <td>0.399517</td>\n",
       "      <td>-0.575670</td>\n",
       "      <td>-3.266102</td>\n",
       "      <td>-0.917499</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.367966</td>\n",
       "      <td>0.020700</td>\n",
       "      <td>0.611334</td>\n",
       "      <td>-0.999765</td>\n",
       "      <td>-3.877599</td>\n",
       "      <td>-0.492112</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>83</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.412955</td>\n",
       "      <td>0.042532</td>\n",
       "      <td>0.544513</td>\n",
       "      <td>-0.884417</td>\n",
       "      <td>-3.157492</td>\n",
       "      <td>-0.607863</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.441827</td>\n",
       "      <td>0.022182</td>\n",
       "      <td>0.535991</td>\n",
       "      <td>-0.816838</td>\n",
       "      <td>-3.808478</td>\n",
       "      <td>-0.623637</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.557235</td>\n",
       "      <td>0.040997</td>\n",
       "      <td>0.401767</td>\n",
       "      <td>-0.584768</td>\n",
       "      <td>-3.194245</td>\n",
       "      <td>-0.911883</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.430517</td>\n",
       "      <td>0.041654</td>\n",
       "      <td>0.527828</td>\n",
       "      <td>-0.842767</td>\n",
       "      <td>-3.178352</td>\n",
       "      <td>-0.638984</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     KEY Score_0 Score_1 Score_2   Proba_0   Proba_1   Proba_2  LogProba_0  \\\n",
       "114  114     NaN     NaN     NaN  0.335153  0.035072  0.629774   -1.093168   \n",
       "74    74     NaN     NaN     NaN  0.478754  0.032302  0.488944   -0.736569   \n",
       "9      9     NaN     NaN     NaN  0.548498  0.050943  0.400560   -0.600573   \n",
       "88    88     NaN     NaN     NaN  0.404438  0.046393  0.549169   -0.905257   \n",
       "25    25     NaN     NaN     NaN  0.541715  0.049148  0.409138   -0.613016   \n",
       "5      5     NaN     NaN     NaN  0.545648  0.035097  0.419255   -0.605781   \n",
       "48    48     NaN     NaN     NaN  0.562328  0.038155  0.399517   -0.575670   \n",
       "117  117     NaN     NaN     NaN  0.367966  0.020700  0.611334   -0.999765   \n",
       "83    83     NaN     NaN     NaN  0.412955  0.042532  0.544513   -0.884417   \n",
       "105  105     NaN     NaN     NaN  0.441827  0.022182  0.535991   -0.816838   \n",
       "27    27     NaN     NaN     NaN  0.557235  0.040997  0.401767   -0.584768   \n",
       "64    64     NaN     NaN     NaN  0.430517  0.041654  0.527828   -0.842767   \n",
       "\n",
       "     LogProba_1  LogProba_2  Decision  \n",
       "114   -3.350341   -0.462394         2  \n",
       "74    -3.432626   -0.715507         2  \n",
       "9     -2.977053   -0.914892         0  \n",
       "88    -3.070605   -0.599349         2  \n",
       "25    -3.012926   -0.893704         0  \n",
       "5     -3.349647   -0.869276         0  \n",
       "48    -3.266102   -0.917499         0  \n",
       "117   -3.877599   -0.492112         2  \n",
       "83    -3.157492   -0.607863         2  \n",
       "105   -3.808478   -0.623637         2  \n",
       "27    -3.194245   -0.911883         0  \n",
       "64    -3.178352   -0.638984         2  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "keras_output = pd.DataFrame()\n",
    "keras_output_key = pd.DataFrame(list(range(iris.data.shape[0])), columns=['KEY']);\n",
    "keras_output_score = pd.DataFrame(columns=['Score_0', 'Score_1', 'Score_2']);\n",
    "keras_output_proba = pd.DataFrame(clf.predict_proba(iris.data), columns=['Proba_0', 'Proba_1', 'Proba_2'])\n",
    "keras_output = pd.concat([keras_output_key, keras_output_score, keras_output_proba] , axis=1)\n",
    "for class_label in [0, 1, 2]:\n",
    "    keras_output['LogProba_' + str(class_label)] = np.log(keras_output_proba['Proba_' + str(class_label)])\n",
    "keras_output['Decision'] = clf.predict(iris.data)\n",
    "keras_output.sample(12, random_state=1960)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "format": "column"
   },
   "source": [
    "# Comparing the SQL and Keras Predictions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_keras_join = keras_output.join(sql_output , how='left', on='KEY', lsuffix='_keras', rsuffix='_sql')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>KEY_keras</th>\n",
       "      <th>Score_0_keras</th>\n",
       "      <th>Score_1_keras</th>\n",
       "      <th>Score_2_keras</th>\n",
       "      <th>Proba_0_keras</th>\n",
       "      <th>Proba_1_keras</th>\n",
       "      <th>Proba_2_keras</th>\n",
       "      <th>LogProba_0_keras</th>\n",
       "      <th>LogProba_1_keras</th>\n",
       "      <th>LogProba_2_keras</th>\n",
       "      <th>...</th>\n",
       "      <th>Score_1_sql</th>\n",
       "      <th>Score_2_sql</th>\n",
       "      <th>Proba_0_sql</th>\n",
       "      <th>Proba_1_sql</th>\n",
       "      <th>Proba_2_sql</th>\n",
       "      <th>LogProba_0_sql</th>\n",
       "      <th>LogProba_1_sql</th>\n",
       "      <th>LogProba_2_sql</th>\n",
       "      <th>Decision_sql</th>\n",
       "      <th>DecisionProba</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.557450</td>\n",
       "      <td>0.041788</td>\n",
       "      <td>0.400762</td>\n",
       "      <td>-0.584382</td>\n",
       "      <td>-3.175147</td>\n",
       "      <td>-0.914388</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.557450</td>\n",
       "      <td>0.041788</td>\n",
       "      <td>0.400762</td>\n",
       "      <td>-0.584382</td>\n",
       "      <td>-3.175147</td>\n",
       "      <td>-0.914388</td>\n",
       "      <td>0</td>\n",
       "      <td>0.557450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.546366</td>\n",
       "      <td>0.048863</td>\n",
       "      <td>0.404770</td>\n",
       "      <td>-0.604466</td>\n",
       "      <td>-3.018730</td>\n",
       "      <td>-0.904435</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.546366</td>\n",
       "      <td>0.048863</td>\n",
       "      <td>0.404770</td>\n",
       "      <td>-0.604466</td>\n",
       "      <td>-3.018730</td>\n",
       "      <td>-0.904435</td>\n",
       "      <td>0</td>\n",
       "      <td>0.546366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.543171</td>\n",
       "      <td>0.050627</td>\n",
       "      <td>0.406202</td>\n",
       "      <td>-0.610330</td>\n",
       "      <td>-2.983280</td>\n",
       "      <td>-0.900905</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.543171</td>\n",
       "      <td>0.050627</td>\n",
       "      <td>0.406202</td>\n",
       "      <td>-0.610330</td>\n",
       "      <td>-2.983280</td>\n",
       "      <td>-0.900905</td>\n",
       "      <td>0</td>\n",
       "      <td>0.543171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.529060</td>\n",
       "      <td>0.056298</td>\n",
       "      <td>0.414642</td>\n",
       "      <td>-0.636653</td>\n",
       "      <td>-2.877101</td>\n",
       "      <td>-0.880339</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.529060</td>\n",
       "      <td>0.056298</td>\n",
       "      <td>0.414642</td>\n",
       "      <td>-0.636653</td>\n",
       "      <td>-2.877101</td>\n",
       "      <td>-0.880339</td>\n",
       "      <td>0</td>\n",
       "      <td>0.529060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.553694</td>\n",
       "      <td>0.043079</td>\n",
       "      <td>0.403227</td>\n",
       "      <td>-0.591143</td>\n",
       "      <td>-3.144717</td>\n",
       "      <td>-0.908256</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.553694</td>\n",
       "      <td>0.043079</td>\n",
       "      <td>0.403227</td>\n",
       "      <td>-0.591143</td>\n",
       "      <td>-3.144717</td>\n",
       "      <td>-0.908256</td>\n",
       "      <td>0</td>\n",
       "      <td>0.553694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.545648</td>\n",
       "      <td>0.035097</td>\n",
       "      <td>0.419255</td>\n",
       "      <td>-0.605781</td>\n",
       "      <td>-3.349647</td>\n",
       "      <td>-0.869276</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.545648</td>\n",
       "      <td>0.035097</td>\n",
       "      <td>0.419255</td>\n",
       "      <td>-0.605781</td>\n",
       "      <td>-3.349647</td>\n",
       "      <td>-0.869276</td>\n",
       "      <td>0</td>\n",
       "      <td>0.545648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.529159</td>\n",
       "      <td>0.051147</td>\n",
       "      <td>0.419694</td>\n",
       "      <td>-0.636466</td>\n",
       "      <td>-2.973051</td>\n",
       "      <td>-0.868230</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.529159</td>\n",
       "      <td>0.051147</td>\n",
       "      <td>0.419694</td>\n",
       "      <td>-0.636466</td>\n",
       "      <td>-2.973051</td>\n",
       "      <td>-0.868230</td>\n",
       "      <td>0</td>\n",
       "      <td>0.529159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.548251</td>\n",
       "      <td>0.045395</td>\n",
       "      <td>0.406354</td>\n",
       "      <td>-0.601022</td>\n",
       "      <td>-3.092352</td>\n",
       "      <td>-0.900530</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.548251</td>\n",
       "      <td>0.045395</td>\n",
       "      <td>0.406354</td>\n",
       "      <td>-0.601022</td>\n",
       "      <td>-3.092352</td>\n",
       "      <td>-0.900530</td>\n",
       "      <td>0</td>\n",
       "      <td>0.548251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.523391</td>\n",
       "      <td>0.061531</td>\n",
       "      <td>0.415078</td>\n",
       "      <td>-0.647427</td>\n",
       "      <td>-2.788207</td>\n",
       "      <td>-0.879289</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.523391</td>\n",
       "      <td>0.061531</td>\n",
       "      <td>0.415078</td>\n",
       "      <td>-0.647427</td>\n",
       "      <td>-2.788207</td>\n",
       "      <td>-0.879289</td>\n",
       "      <td>0</td>\n",
       "      <td>0.523391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.548498</td>\n",
       "      <td>0.050943</td>\n",
       "      <td>0.400560</td>\n",
       "      <td>-0.600573</td>\n",
       "      <td>-2.977053</td>\n",
       "      <td>-0.914892</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.548498</td>\n",
       "      <td>0.050943</td>\n",
       "      <td>0.400560</td>\n",
       "      <td>-0.600573</td>\n",
       "      <td>-2.977053</td>\n",
       "      <td>-0.914892</td>\n",
       "      <td>0</td>\n",
       "      <td>0.548498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.566453</td>\n",
       "      <td>0.036495</td>\n",
       "      <td>0.397051</td>\n",
       "      <td>-0.568360</td>\n",
       "      <td>-3.310573</td>\n",
       "      <td>-0.923690</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.566453</td>\n",
       "      <td>0.036495</td>\n",
       "      <td>0.397051</td>\n",
       "      <td>-0.568360</td>\n",
       "      <td>-3.310573</td>\n",
       "      <td>-0.923690</td>\n",
       "      <td>0</td>\n",
       "      <td>0.566453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.535053</td>\n",
       "      <td>0.050788</td>\n",
       "      <td>0.414159</td>\n",
       "      <td>-0.625389</td>\n",
       "      <td>-2.980093</td>\n",
       "      <td>-0.881506</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.535053</td>\n",
       "      <td>0.050788</td>\n",
       "      <td>0.414159</td>\n",
       "      <td>-0.625389</td>\n",
       "      <td>-2.980093</td>\n",
       "      <td>-0.881506</td>\n",
       "      <td>0</td>\n",
       "      <td>0.535053</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    KEY_keras Score_0_keras Score_1_keras Score_2_keras  Proba_0_keras  \\\n",
       "0           0           NaN           NaN           NaN       0.557450   \n",
       "1           1           NaN           NaN           NaN       0.546366   \n",
       "2           2           NaN           NaN           NaN       0.543171   \n",
       "3           3           NaN           NaN           NaN       0.529060   \n",
       "4           4           NaN           NaN           NaN       0.553694   \n",
       "5           5           NaN           NaN           NaN       0.545648   \n",
       "6           6           NaN           NaN           NaN       0.529159   \n",
       "7           7           NaN           NaN           NaN       0.548251   \n",
       "8           8           NaN           NaN           NaN       0.523391   \n",
       "9           9           NaN           NaN           NaN       0.548498   \n",
       "10         10           NaN           NaN           NaN       0.566453   \n",
       "11         11           NaN           NaN           NaN       0.535053   \n",
       "\n",
       "    Proba_1_keras  Proba_2_keras  LogProba_0_keras  LogProba_1_keras  \\\n",
       "0        0.041788       0.400762         -0.584382         -3.175147   \n",
       "1        0.048863       0.404770         -0.604466         -3.018730   \n",
       "2        0.050627       0.406202         -0.610330         -2.983280   \n",
       "3        0.056298       0.414642         -0.636653         -2.877101   \n",
       "4        0.043079       0.403227         -0.591143         -3.144717   \n",
       "5        0.035097       0.419255         -0.605781         -3.349647   \n",
       "6        0.051147       0.419694         -0.636466         -2.973051   \n",
       "7        0.045395       0.406354         -0.601022         -3.092352   \n",
       "8        0.061531       0.415078         -0.647427         -2.788207   \n",
       "9        0.050943       0.400560         -0.600573         -2.977053   \n",
       "10       0.036495       0.397051         -0.568360         -3.310573   \n",
       "11       0.050788       0.414159         -0.625389         -2.980093   \n",
       "\n",
       "    LogProba_2_keras      ...        Score_1_sql  Score_2_sql Proba_0_sql  \\\n",
       "0          -0.914388      ...               None         None    0.557450   \n",
       "1          -0.904435      ...               None         None    0.546366   \n",
       "2          -0.900905      ...               None         None    0.543171   \n",
       "3          -0.880339      ...               None         None    0.529060   \n",
       "4          -0.908256      ...               None         None    0.553694   \n",
       "5          -0.869276      ...               None         None    0.545648   \n",
       "6          -0.868230      ...               None         None    0.529159   \n",
       "7          -0.900530      ...               None         None    0.548251   \n",
       "8          -0.879289      ...               None         None    0.523391   \n",
       "9          -0.914892      ...               None         None    0.548498   \n",
       "10         -0.923690      ...               None         None    0.566453   \n",
       "11         -0.881506      ...               None         None    0.535053   \n",
       "\n",
       "   Proba_1_sql Proba_2_sql  LogProba_0_sql  LogProba_1_sql  LogProba_2_sql  \\\n",
       "0     0.041788    0.400762       -0.584382       -3.175147       -0.914388   \n",
       "1     0.048863    0.404770       -0.604466       -3.018730       -0.904435   \n",
       "2     0.050627    0.406202       -0.610330       -2.983280       -0.900905   \n",
       "3     0.056298    0.414642       -0.636653       -2.877101       -0.880339   \n",
       "4     0.043079    0.403227       -0.591143       -3.144717       -0.908256   \n",
       "5     0.035097    0.419255       -0.605781       -3.349647       -0.869276   \n",
       "6     0.051147    0.419694       -0.636466       -2.973051       -0.868230   \n",
       "7     0.045395    0.406354       -0.601022       -3.092352       -0.900530   \n",
       "8     0.061531    0.415078       -0.647427       -2.788207       -0.879289   \n",
       "9     0.050943    0.400560       -0.600573       -2.977053       -0.914892   \n",
       "10    0.036495    0.397051       -0.568360       -3.310573       -0.923690   \n",
       "11    0.050788    0.414159       -0.625389       -2.980093       -0.881506   \n",
       "\n",
       "    Decision_sql  DecisionProba  \n",
       "0              0       0.557450  \n",
       "1              0       0.546366  \n",
       "2              0       0.543171  \n",
       "3              0       0.529060  \n",
       "4              0       0.553694  \n",
       "5              0       0.545648  \n",
       "6              0       0.529159  \n",
       "7              0       0.548251  \n",
       "8              0       0.523391  \n",
       "9              0       0.548498  \n",
       "10             0       0.566453  \n",
       "11             0       0.535053  \n",
       "\n",
       "[12 rows x 23 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_keras_join.head(12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>KEY_keras</th>\n",
       "      <th>Score_0_keras</th>\n",
       "      <th>Score_1_keras</th>\n",
       "      <th>Score_2_keras</th>\n",
       "      <th>Proba_0_keras</th>\n",
       "      <th>Proba_1_keras</th>\n",
       "      <th>Proba_2_keras</th>\n",
       "      <th>LogProba_0_keras</th>\n",
       "      <th>LogProba_1_keras</th>\n",
       "      <th>LogProba_2_keras</th>\n",
       "      <th>...</th>\n",
       "      <th>Score_1_sql</th>\n",
       "      <th>Score_2_sql</th>\n",
       "      <th>Proba_0_sql</th>\n",
       "      <th>Proba_1_sql</th>\n",
       "      <th>Proba_2_sql</th>\n",
       "      <th>LogProba_0_sql</th>\n",
       "      <th>LogProba_1_sql</th>\n",
       "      <th>LogProba_2_sql</th>\n",
       "      <th>Decision_sql</th>\n",
       "      <th>DecisionProba</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [KEY_keras, Score_0_keras, Score_1_keras, Score_2_keras, Proba_0_keras, Proba_1_keras, Proba_2_keras, LogProba_0_keras, LogProba_1_keras, LogProba_2_keras, Decision_keras, KEY_sql, Score_0_sql, Score_1_sql, Score_2_sql, Proba_0_sql, Proba_1_sql, Proba_2_sql, LogProba_0_sql, LogProba_1_sql, LogProba_2_sql, Decision_sql, DecisionProba]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 23 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition = (sql_keras_join.Decision_sql != sql_keras_join.Decision_keras)\n",
    "sql_keras_join[condition]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}