{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "QJb3sp4ZCF_O"
   },
   "source": [
    "# Data Preparation\n",
    "\n",
    "Notebook supporting the [**Do we know our data, as good as we know our tools** talk](https://devoxxuk19.confinabox.com/talk/VEM-8021/Do_we_know_our_data_as_good_as_we_know_our_tools%3F) at [Devoxx UK 2019](http://twitter.com/@DevoxxUK).\n",
    "\n",
    "The contents of the notebook is inspired by many sources.\n",
    "\n",
    "\n",
    "### High-level steps covered:\n",
    "\n",
    "- Data Cleaning\n",
    "  - Deal with errors \n",
    "  - Deal with duplicates\n",
    "  - Deal with outliers [DEMO - WALKTHRU]\n",
    "  - Deal with missing data [DEMO - WALKTHRU]\n",
    "- Deal with too much data\n",
    "\n",
    "\n",
    "## Resources\n",
    "\n",
    "### Data cleaning\n",
    "- [Data cleaning](https://elitedatascience.com/data-cleaning)\n",
    "- [Spend Less Time Cleaning Data with Machine Learning](https://www.dataversity.net/spend-less-time-cleaning-data-with-machine-learning/#)\n",
    "- [Helpful Python Code Snippets for Data Exploration in Pandas - lots of python snippets to select / clean / prepare](https://medium.com/@msalmon00/helpful-python-code-snippets-for-data-exploration-in-pandas-b7c5aed5ecb9)\n",
    "- [Working with missing data](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html)\n",
    "- [Journal of Statistical Software - TidyData](https://www.jstatsoft.org/article/view/v059i10/)\n",
    "\n",
    "### Data preprocessing / Data Wrangling\n",
    "- [Data Preprocessing vs. Data Wrangling in Machine Learning Projects](https://www.infoq.com/articles/ml-data-processing)\n",
    "- [Improve Model Accuracy with Data Pre-Processing](https://machinelearningmastery.com/improve-model-accuracy-with-data-pre-processing/)\n",
    "- **[Useful cheatsheets](https://github.com/neomatrix369/awesome-ai-ml-dl/blob/master/README-details.md#cheatsheets)**\n",
    "\n",
    "\n",
    "Please refer to the [Slides](http://bit.ly/do-we-know-our-data) for the step here after."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Why?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ask all the questions you should ask with regards to the domain and related domains or sub-domains.\n",
    "\n",
    "It is a good idea to know the **why** part of the action, why are we doing what we are doing with the data, see the [five whys](https://en.wikipedia.org/wiki/5_Whys).\n",
    "\n",
    "Some ideas (of course, please come up with your own as well):\n",
    "\n",
    "- Garbage in, garbage out. If you work with dirty data, even the most\n",
    "sophisticated models won’t be able to get satisfying results. Better\n",
    "data beats fancier algorithms\n",
    "- To create a clean dataset (so that it has good enough accuracy\n",
    "and correctness)\n",
    "- So that we can create models that are closer to nature’s model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "4UtaBjaqq-v7"
   },
   "source": [
    "#### Load Your Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Archive:  boston_housing_dataset.zip\n",
      "  inflating: column.header           \n",
      "  inflating: housing-unclean.csv     \n",
      "  inflating: housing.csv             \n",
      "  inflating: housing.names           \n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "if [[ ! -s boston_housing_dataset.zip ]]; then\n",
    "    curl -O -L https://github.com/neomatrix369/awesome-ai-ml-dl/releases/download/v0.1/boston_housing_dataset.zip\n",
    "fi\n",
    "\n",
    "unzip -o boston_housing_dataset.zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 967
    },
    "colab_type": "code",
    "id": "p0JJ2OzsrECo",
    "outputId": "70b89c5c-ec3a-4af0-c1ff-f1f1c8610e8a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Names and descriptions of the fields of the Boston Housing dataset can be found at\n",
      "https://github.com/jbrownlee/Datasets/blob/master/housing.names\n",
      "\n",
      "1. Title: Boston Housing Data\n",
      "\n",
      "2. Sources:\n",
      "   (a) Origin:  This dataset was taken from the StatLib library which is\n",
      "                maintained at Carnegie Mellon University.\n",
      "   (b) Creator:  Harrison, D. and Rubinfeld, D.L. 'Hedonic prices and the \n",
      "                 demand for clean air', J. Environ. Economics & Management,\n",
      "                 vol.5, 81-102, 1978.\n",
      "   (c) Date: July 7, 1993\n",
      "\n",
      "3. Past Usage:\n",
      "   -   Used in Belsley, Kuh & Welsch, 'Regression diagnostics ...', Wiley, \n",
      "       1980.   N.B. Various transformations are used in the table on\n",
      "       pages 244-261.\n",
      "    -  Quinlan,R. (1993). Combining Instance-Based and Model-Based Learning.\n",
      "       In Proceedings on the Tenth International Conference of Machine \n",
      "       Learning, 236-243, University of Massachusetts, Amherst. Morgan\n",
      "       Kaufmann.\n",
      "\n",
      "4. Relevant Information:\n",
      "\n",
      "   Concerns housing values in suburbs of Boston.\n",
      "\n",
      "5. Number of Instances: 506\n",
      "\n",
      "6. Number of Attributes: 13 continuous attributes (including \"class\"\n",
      "                         attribute \"MEDV\"), 1 binary-valued attribute.\n",
      "\n",
      "7. Attribute Information:\n",
      "\n",
      "    1. CRIM      per capita crime rate by town\n",
      "    2. ZN        proportion of residential land zoned for lots over \n",
      "                 25,000 sq.ft.\n",
      "    3. INDUS     proportion of non-retail business acres per town\n",
      "    4. CHAS      Charles River dummy variable (= 1 if tract bounds \n",
      "                 river; 0 otherwise)\n",
      "    5. NOX       nitric oxides concentration (parts per 10 million)\n",
      "    6. RM        average number of rooms per dwelling\n",
      "    7. AGE       proportion of owner-occupied units built prior to 1940\n",
      "    8. DIS       weighted distances to five Boston employment centres\n",
      "    9. RAD       index of accessibility to radial highways\n",
      "    10. TAX      full-value property-tax rate per $10,000\n",
      "    11. PTRATIO  pupil-teacher ratio by town\n",
      "    12. B        1000(Bk - 0.63)^2 where Bk is the proportion of blacks \n",
      "                 by town\n",
      "    13. LSTAT    % lower status of the population\n",
      "    14. MEDV     Median value of owner-occupied homes in $1000's\n",
      "\n",
      "8. Missing Attribute Values:  None.\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import random\n",
    "\n",
    "sns.set()\n",
    "\n",
    "names = [line.strip() for line in open(\"column.header\", 'r')]\n",
    "data = pd.read_csv(\"housing-unclean.csv\", names=names)\n",
    "\n",
    "print(\"Names and descriptions of the fields of the Boston Housing dataset can be found at\")\n",
    "print(\"https://github.com/jbrownlee/Datasets/blob/master/housing.names\")\n",
    "print(\"\")\n",
    "!cat housing.names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "qIhcY8dYE6QZ"
   },
   "source": [
    "### Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "pMktiylmHncM"
   },
   "source": [
    "- deal with errors\n",
    "- deal with duplicates\n",
    "- deal with outliers\n",
    "- deal with missing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "OH3OVpoBJRtl"
   },
   "source": [
    "#### Deal with errors\n",
    "\n",
    "Also known as structural errors."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Tyd1ZzUre9lj"
   },
   "source": [
    "|Type of problems |Technique to use|\n",
    "|-----------------------------|---------------------------|\n",
    "| mislabelled | relabel data automatically or manually |\n",
    "|----------------------------------------------------------|-------------------------------------------------------------|\n",
    "| dataset standardisation issue | uniformly replace them |\n",
    "|----------------------------------------------------------|-------------------------------------------------------------|\n",
    "| sync issues between sources of data | standardise the data |\n",
    "|----------------------------------------------------------|-------------------------------------------------------------|"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Mm5EsDitJXgQ"
   },
   "source": [
    "####  Deal with duplicates\n",
    "\n",
    "Get stats on the number of non-unqiue or duplicate rows in a dataset and decide if you would like to delete them. In most case you would delete them.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 2201
    },
    "colab_type": "code",
    "id": "WjnQpQCFJq4G",
    "outputId": "e38c4176-5464-4a7e-c46c-ac1dc19ebc76"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset rows count BEFORE dropping duplicates: 606\n",
      "Duplicated rows count: 50\n",
      "% of duplicated rows to total rows in the dataset: 8.25082508250825\n",
      "\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 9800 characters]\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "50 rows deleted\n",
      "Dataset rows count AFTER dropping duplicates: 556\n"
     ]
    }
   ],
   "source": [
    "total_rows_count = data.shape[0]\n",
    "print(\"Dataset rows count BEFORE dropping duplicates:\", total_rows_count)\n",
    "\n",
    "duplicated_rows_count = data[data.duplicated()].shape[0]\n",
    "print(\"Duplicated rows count:\", duplicated_rows_count)\n",
    "print(\"% of duplicated rows to total rows in the dataset:\", duplicated_rows_count / total_rows_count * 100)\n",
    "print()\n",
    "\n",
    "\n",
    "labels = (\"Total rows:\", \"Duplicate rows\", \"Unique rows\")\n",
    "values = (total_rows_count, duplicated_rows_count, total_rows_count - duplicated_rows_count)\n",
    "plt.title(\"Dataset with duplicated rows\")\n",
    "plt.bar(labels, values)\n",
    "plt.show()\n",
    "\n",
    "# Delete duplicates\n",
    "if duplicated_rows_count > 0:\n",
    "    data = data.drop_duplicates()\n",
    "    print(duplicated_rows_count, \"rows deleted\")\n",
    "\n",
    "# Check the dataset after deletion\n",
    "print(\"Dataset rows count AFTER dropping duplicates:\", data.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "SEPARsYEJrnn"
   },
   "source": [
    "#### Deal with outliers [DEMO - WALKTHRU]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Normal distribution](https://www.mathsisfun.com/data/images/normal-distrubution-large.svg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 225
    },
    "colab_type": "code",
    "id": "icXl0KwkK7tD",
    "outputId": "d132dd24-4cc2-4c05-c59a-cf872759141d"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "count    556.000000\n",
      "mean       6.273680\n",
      "std        0.723658\n",
      "min        3.561000\n",
      "25%        5.876750\n",
      "50%        6.189000\n",
      "75%        6.618250\n",
      "max        8.780000\n",
      "Name: rm, dtype: float64\n",
      "Number of outliers in the Room column (< mean - 2 x std dev OR > mean + 2 x std dev): 36\n"
     ]
    }
   ],
   "source": [
    "print(\"\")\n",
    "print(data.describe()[\"rm\"])\n",
    "rm_column = data[\"rm\"]\n",
    "\n",
    "mean=data.describe()[\"rm\"][\"mean\"]\n",
    "stddev=data.describe()[\"rm\"][\"std\"]\n",
    "two_times_stddev=2 * stddev\n",
    "\n",
    "minimum = mean - two_times_stddev\n",
    "maximum = mean + two_times_stddev\n",
    "\n",
    "num_of_outliers_rm_col = rm_column[(rm_column < minimum) | (rm_column > maximum)].count()\n",
    "print(\"Number of outliers in the Room column (< mean - 2 x std dev OR > mean + 2 x std dev):\", num_of_outliers_rm_col)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Distribution with all values\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 6268 characters]\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Distribution without outliers\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 6548 characters]\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "print(\"Distribution with all values\")\n",
    "plt.hist(data[\"rm\"],bins=40)\n",
    "plt.show()\n",
    "print();\n",
    "print(\"Distribution without outliers\")\n",
    "data_without_outliers=data[(data[\"rm\"] > minimum) & (data[\"rm\"] < maximum)]\n",
    "plt.hist(data_without_outliers[\"rm\"],bins=20)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "dSdqmARsDwbM"
   },
   "source": [
    "#### Deal with missing data [DEMO - WALKTHRU]\n",
    "\n",
    "Make an informed decision about which rows to eliminate based on which column or columns have missing data.\n",
    "\n",
    "The decisions can be many:\n",
    "- remove rows with one or more missing values\n",
    "- remove rows with the number of missing values above a certain threshold (i.e. more than 75% of the columns with missing values)\n",
    "- fill the missing values with computed values\n",
    "    - mean\n",
    "    - imputed\n",
    "    - predicted\n",
    "    - other transformations\n",
    "    \n",
    "These decisions and transformations are purely dependent on the goal and objective of behind the analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 2184
    },
    "colab_type": "code",
    "id": "C4HRvWodDxjh",
    "outputId": "6c66fc2e-d8b5-4a62-888c-a6f41ac5bd60"
   },
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 11916 characters]\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset rows count BEFORE dropping rows with missing values: 556\n",
      "\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 11572 characters]\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset rows count AFTER dropping rows with missing values: 506\n"
     ]
    }
   ],
   "source": [
    "total_rows = data.shape[0]\n",
    "total_rows_without_missing_values=data.count()\n",
    "missing_values_count=(total_rows - total_rows_without_missing_values)\n",
    "plt.barh(data.columns, missing_values_count)\n",
    "plt.title(\"Dataset values count before removing missing values\")\n",
    "plt.show()\n",
    "\n",
    "print(\"Dataset rows count BEFORE dropping rows with missing values:\", data.shape[0])\n",
    "# Remove rows with columns missing data \n",
    "if sum(missing_values_count) > 0:\n",
    "    data = data.dropna()\n",
    "\n",
    "print(\"\")\n",
    "\n",
    "# Check the dataset after dropping rows\n",
    "total_rows = data.shape[0]\n",
    "total_rows_without_missing_values=data.count()\n",
    "missing_values_count=(total_rows - total_rows_without_missing_values)\n",
    "plt.barh(data.columns, missing_values_count)\n",
    "plt.title(\"Dataset values count after removing missing values\")\n",
    "plt.show()\n",
    "print(\"Dataset rows count AFTER dropping rows with missing values:\", data.shape[0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "kP4yEElUGNhi"
   },
   "source": [
    "### Deal with too much data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "4j-0oInYclob"
   },
   "source": [
    "|Type of problems |Technique to use|\n",
    "|-----------------------------|---------------------------|\n",
    "|needle in a haystack problems |Step1: group data + histogram - to identify the disproportion|\n",
    "|(huge dataset with disproportionate|Step 2: Undersampling the classes to remove data|\n",
    "|class distribution: e.g. we try to detect |Step 3: Oversampling by adding more data|\n",
    "|data (horse rolling which is a rare | |\n",
    "|event vs simply standing or lying) | |\n",
    "|----------------------------------------------------------------|--------------------------------------------------------------------------------------------------|\n",
    "|.| Step 1: Manage at the training stage (adjust hyperparameter) |\n",
    "|.| (check ML Mastery for more techniques in the google docs) |\n",
    "|----------------------------------------------------------------|--------------------------------------------------------------------------------------------------|\n",
    "| dataset with class overload problems | Group together sparse categories |\n",
    "|(column with astronomical number      | Remove sparse categories |\n",
    "|of categories. e.g. city in house prices)| Summarising categories into higher levels of abstractions |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preparatory questions to ask"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do we have those problems to fix ?\n",
    "\n",
    "- outliers\n",
    "- missing data\n",
    "- class overload\n",
    "- too many features\n",
    "- unbalanced dataset\n",
    "- have we removed or balanced any existing bias in the dataset?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "5ZVFw4kaHVtO"
   },
   "source": [
    "### Please refer to the [Slides](http://bit.ly/do-you-know-your-data) for the step here after."
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Data Preparation (Do we know our data).ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
