db2_for_machine_learning_samples/notebooks/Db2 Sample For Tensorflow.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Copyright IBM All Rights Reserved.\n",
    "#### SPDX-License-Identifier: Apache-2.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Db2 Sample For Tensorflow\n",
    "\n",
    "In this code sample, we will show how to use the Db2 Python driver to import data from our Db2 database. Then, we will use that data to create a machine learning model with tensorflow.\n",
    "\n",
    "Many wine connoisseurs love to taste different wines from all over the world. Mostly importantly, they want to be able to guess the type of wine it is based on the taste and ingredients of the wine. In this notebook, we will be using a dataset that has collected certain attributes of many wine bottles that determines the class of the wine. Using this dataset, we will help our wine connoisseurs predict the `class` of wine.\n",
    "\n",
    "This notebook will demonstrate how to use Db2 as a data source for creating machine learning models.\n",
    "\n",
    "Prerequisites:\n",
    "1. Python 3.6 and above\n",
    "2. Db2 on Cloud instance (using free-tier option)\n",
    "3. Data already loaded in your Db2 instance\n",
    "4. Have Db2 connection credentials on hand\n",
    "\n",
    "We will be importing two libraries- `ibm_db` and `ibm_dbi`. `ibm_db` is a library with low-level functions that will directly connect to our db2 database. To make things easier for you, we will be using `ibm-dbi`, which communicates with `ibm-db` and gives us an easy interface to interact with our data and import our data as a pandas dataframe. \n",
    "\n",
    "For this example, we will be using the [wine dataset](../data/wine.csv), which we have loaded into our Db2 instance.\n",
    "\n",
    "NOTE: Running this notebook within a docker container. If `!easy_install ibm_db` doesn't work on your normally on jupter notebook, you may need to also run this notebook within a docker container as well."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Import Data\n",
    "Let's first install and import all the libraries needed for this notebook. Most important we will be installing and importing the db2 python driver `ibm_db`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install tensorflow\n",
    "!easy_install ibm_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline\n",
    "\n",
    "# The two python ibm db2 drivers we need\n",
    "import ibm_db\n",
    "import ibm_db_dbi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# replace only <> credentials\n",
    "dsn = \"DRIVER={{IBM DB2 ODBC DRIVER}};\" + \\\n",
    "      \"DATABASE=<DATABASE NAME>;\" + \\\n",
    "      \"HOSTNAME=<HOSTNMAE>;\" + \\\n",
    "      \"PORT=50000;\" + \\\n",
    "      \"PROTOCOL=TCPIP;\" + \\\n",
    "      \"UID=<USERNAME>;\" + \\\n",
    "      \"PWD=<PWD>;\"\n",
    "hdbc  = ibm_db.connect(dsn, \"\", \"\")\n",
    "hdbi = ibm_db_dbi.Connection(hdbc)\n",
    "\n",
    "sql = 'SELECT * FROM <SCHEMA NAME>.<TABLE NAME>'\n",
    "\n",
    "wine = pd.read_sql(sql,hdbi)\n",
    "\n",
    "#colnames = ['Class','Alcohol','Malic acid','Ash','Alcalinity of ash','Magnesium','Total phenols','Flavanoids','Nonflavanoid phenols','Proanthocyanins','Color intensity','Hue','dilute','Proline']\n",
    "#wine = pd.read_csv('../data/winequality-red.csv', sep=';') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's see what our data looks like\n",
    "wine.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Data Exploration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this step, we are going to try and explore our data inorder to gain insight. We hope to be able to make some assumptions of our data before we start modeling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wine.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Minimum price of the data\n",
    "minimum_price = np.amin(wine['Wine'])\n",
    "\n",
    "# Maximum price of the data\n",
    "maximum_price = np.amax(wine['Wine'])\n",
    "\n",
    "# Mean price of the data\n",
    "mean_price = np.mean(wine['Wine'])\n",
    "\n",
    "# Median price of the data\n",
    "median_price = np.median(wine['Wine'])\n",
    "\n",
    "# Standard deviation of prices of the data\n",
    "std_price = np.std(wine['Wine'])\n",
    "\n",
    "# Show the calculated statistics\n",
    "print(\"Statistics for housing dataset:\\n\")\n",
    "print(\"Minimum: {}\".format(minimum_price)) \n",
    "print(\"Maximum: {}\".format(maximum_price))\n",
    "print(\"Mean: {}\".format(mean_price))\n",
    "print(\"Median {}\".format(median_price))\n",
    "print(\"Standard deviation: {}\".format(std_price))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wine.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "corr_matrix = wine.corr()\n",
    "corr_matrix[\"Wine\"].sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Data Visualization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wine.hist(bins=50, figsize=(30,25))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "boxplot = wine.boxplot(column=['Wine'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Pre-Process Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we start creating our model, we need to first pre-process our data for tensorflow."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tensorflow as tf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First we convert the Wine labels to Onehot format.\n",
    "df = pd.get_dummies(wine, columns=['Wine'])\n",
    "\n",
    "# Convert labels to numpy array for tensorflow\n",
    "labels = df.loc[:,['Class_1','Class_2','Class_3']]\n",
    "labels = labels.values\n",
    "\n",
    "# Convert features to numpy array for tensorflow\n",
    "features = df.drop(['Class_1','Class_2','Class_3','Ash'],axis = 1)\n",
    "features = features.values\n",
    "\n",
    "# Make sure the type is numpy arrays\n",
    "print(type(labels))\n",
    "print(type(features))\n",
    "\n",
    "# Make sure the shape of the array is correct\n",
    "print(labels.shape)\n",
    "print(features.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "# Split the data into test and train data\n",
    "train_x,test_x,train_y,test_y = train_test_split(features,labels)\n",
    "\n",
    "# Verify the shape of the test and train data\n",
    "print(train_x.shape,train_y.shape,test_x.shape,test_y.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import MinMaxScaler\n",
    "\n",
    "# NN in tensorflow works better when the data is scaled between (0,1). So let's scale our data\n",
    "scale = MinMaxScaler(feature_range = (0,1))\n",
    "\n",
    "train_x = scale.fit_transform(train_x)\n",
    "test_x = scale.fit_transform(test_x)\n",
    "\n",
    "# Take a seak peak at our data \n",
    "print(train_x[0])\n",
    "print(train_y[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Creating Machine Learning Model\n",
    "\n",
    "Now that we have cleaned and explored our data. We are ready to build our model that will predict the attribute `Class`. We will be creating a basic neural network with tensorflow to help us predict. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's first create placeholders for our feature and labels\n",
    "X = tf.placeholder(tf.float32,[None,12]) # Since we have 12 features as input\n",
    "y = tf.placeholder(tf.float32,[None,3])  # Since we have 3 outut labels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to create a simple NN model with 2 hidden layters (3 layers in total). They are going to be 80 and 50 respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Weights and biases for our first hidden layer\n",
    "weights1 = tf.get_variable(\"weights1\",shape=[12,80],initializer = tf.contrib.layers.xavier_initializer())\n",
    "biases1 = tf.get_variable(\"biases1\",shape = [80],initializer = tf.zeros_initializer)\n",
    "layer1out = tf.nn.relu(tf.matmul(X,weights1)+biases1)\n",
    "\n",
    "# Weights and biases for our second hidden layer\n",
    "weights2 = tf.get_variable(\"weights2\",shape=[80,50],initializer = tf.contrib.layers.xavier_initializer())\n",
    "biases2 = tf.get_variable(\"biases2\",shape = [50],initializer = tf.zeros_initializer)\n",
    "layer2out = tf.nn.relu(tf.matmul(layer1out,weights2)+biases2)\n",
    "\n",
    "# Weights and biases for our output node\n",
    "weights3 = tf.get_variable(\"weights3\",shape=[50,3],initializer = tf.contrib.layers.xavier_initializer())\n",
    "biases3 = tf.get_variable(\"biases3\",shape = [3],initializer = tf.zeros_initializer)\n",
    "prediction =tf.matmul(layer2out,weights3)+biases3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We also need to degine the loss funtion. We will be using the softmax_cross_entropy_with_logits_v2 function. \n",
    "cost = tf.reduce_mean(tf.nn.softmax_cross_entropy_with_logits_v2(logits=prediction, labels=y))\n",
    "\n",
    "# I am keeping our learning rate as 0.001, but you can always change that.\n",
    "optimizer = tf.train.AdamOptimizer(0.001).minimize(cost)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "acc = []\n",
    "\n",
    "# This is where we will run our model\n",
    "with tf.Session() as sess:\n",
    "    \n",
    "    # Initilize our variables\n",
    "    sess.run(tf.global_variables_initializer())\n",
    "    \n",
    "    # Train our data over 200 iterations\n",
    "    for epoch in range(201):\n",
    "        \n",
    "        # Train using our NN model we created\n",
    "        opt,costval = sess.run([optimizer,cost],feed_dict = {X:train_x,y:train_y})\n",
    "        \n",
    "        # Calculate how many matches we made\n",
    "        matches = tf.equal(tf.argmax(prediction, 1), tf.argmax(y, 1))\n",
    "        \n",
    "        # Compute cost and update parameters and also ouput accuracy with current parameters\n",
    "        accuracy = tf.reduce_mean(tf.cast(matches, 'float'))\n",
    "        \n",
    "        # Calculate the accuracy and store it\n",
    "        acc.append(accuracy.eval({X:test_x,y:test_y}))\n",
    "        if(epoch % 100 == 0):\n",
    "            print(\"Epoch\", epoch, \"--\" , \"Cost\",costval)\n",
    "            print(\"Accuracy on the test set ->\",accuracy.eval({X:test_x,y:test_y}))\n",
    "    print(\"FINISHED !!!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets plot our accuracy over the number of iterations and see how our model did \n",
    "plt.plot(acc)\n",
    "plt.ylabel(\"Accuracy\")\n",
    "plt.xlabel(\"Epochs\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like our model did really well with our data. It seems however that we may not have needed to run it over 200 iterations. But that's up to you guys to decide! "
   ]
  }
 ],
 "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
}