t81_558_class_02_1_python_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/jeffheaton/t81_558_deep_learning/blob/master/t81_558_class_02_1_python_pandas.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# T81-558: Applications of Deep Neural Networks\n",
    "**Module 2: Python for Machine Learning**\n",
    "* Instructor: [Jeff Heaton](https://sites.wustl.edu/jeffheaton/), McKelvey School of Engineering, [Washington University in St. Louis](https://engineering.wustl.edu/Programs/Pages/default.aspx)\n",
    "* For more information visit the [class website](https://sites.wustl.edu/jeffheaton/t81-558/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 2 Material\n",
    "\n",
    "Main video lecture:\n",
    "\n",
    "* **Part 2.1: Introduction to Pandas** [[Video]](https://www.youtube.com/watch?v=bN4UuCBdpZc&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](https://github.com/jeffheaton/t81_558_deep_learning/blob/master/t81_558_class_02_1_python_pandas.ipynb)\n",
    "* Part 2.2: Categorical Values [[Video]](https://www.youtube.com/watch?v=4a1odDpG0Ho&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](https://github.com/jeffheaton/t81_558_deep_learning/blob/master/t81_558_class_02_2_pandas_cat.ipynb)\n",
    "* Part 2.3: Grouping, Sorting, and Shuffling in Python Pandas [[Video]](https://www.youtube.com/watch?v=YS4wm5gD8DM&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](https://github.com/jeffheaton/t81_558_deep_learning/blob/master/t81_558_class_02_3_pandas_grouping.ipynb)\n",
    "* Part 2.4: Using Apply and Map in Pandas for Keras [[Video]](https://www.youtube.com/watch?v=XNCEZ4WaPBY&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](https://github.com/jeffheaton/t81_558_deep_learning/blob/master/t81_558_class_02_4_pandas_functional.ipynb)\n",
    "* Part 2.5: Feature Engineering in Pandas for Deep Learning in Keras [[Video]](https://www.youtube.com/watch?v=BWPTj4_Mi9E&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](https://github.com/jeffheaton/t81_558_deep_learning/blob/master/t81_558_class_02_5_pandas_features.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Google CoLab Instructions\n",
    "\n",
    "The following code ensures that Google CoLab is running the correct version of TensorFlow."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Note: not using Google CoLab\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    from google.colab import drive\n",
    "    %tensorflow_version 2.x\n",
    "    COLAB = True\n",
    "    print(\"Note: using Google CoLab\")\n",
    "except:\n",
    "    print(\"Note: not using Google CoLab\")\n",
    "    COLAB = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2.1: Introduction to Pandas\n",
    "\n",
    "[Pandas](http://pandas.pydata.org/) is an open-source library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.  It is based on the [dataframe](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html) concept found in the [R programming language](https://www.r-project.org/about.html).  For this class, Pandas will be the primary means by which we manipulate data to be processed by neural networks.\n",
    "\n",
    "The data frame is a crucial component of Pandas.  We will use it to access the [auto-mpg dataset](https://archive.ics.uci.edu/ml/datasets/Auto+MPG).  You can find this dataset on the UCI machine learning repository.  For this class, we will use a version of the Auto MPG dataset, where I added column headers.  You can find my version [here](https://raw.githubusercontent.com/jeffheaton/t81_558_deep_learning/master/data/auto-mpg.csv).\n",
    "\n",
    "UCI took this dataset from the StatLib library, which Carnegie Mellon University maintains. The dataset was used in the 1983 American Statistical Association Exposition.  It contains data for 398 cars, including [mpg](https://en.wikipedia.org/wiki/Fuel_economy_in_automobiles), [cylinders](https://en.wikipedia.org/wiki/Cylinder_(engine)), [displacement](https://en.wikipedia.org/wiki/Engine_displacement), [horsepower](https://en.wikipedia.org/wiki/Horsepower) , weight, acceleration, model year, origin and the car's name.\n",
    "\n",
    "The following code loads the MPG dataset into a data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>...</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18.0</td>\n",
       "      <td>8</td>\n",
       "      <td>307.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet chevelle malibu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>buick skylark 320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18.0</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth satellite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16.0</td>\n",
       "      <td>8</td>\n",
       "      <td>304.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc rebel sst</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.0</td>\n",
       "      <td>8</td>\n",
       "      <td>302.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford torino</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    mpg  cylinders  displacement  ... year  origin                       name\n",
       "0  18.0          8         307.0  ...   70       1  chevrolet chevelle malibu\n",
       "1  15.0          8         350.0  ...   70       1          buick skylark 320\n",
       "2  18.0          8         318.0  ...   70       1         plymouth satellite\n",
       "3  16.0          8         304.0  ...   70       1              amc rebel sst\n",
       "4  17.0          8         302.0  ...   70       1                ford torino\n",
       "\n",
       "[5 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Simple dataframe\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option('display.max_columns', 7)\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\")\n",
    "display(df[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **display** function provides a cleaner display than merely printing the data frame.  Specifying the maximum rows and columns allows you to achieve greater control over the display."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>...</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18.0</td>\n",
       "      <td>8</td>\n",
       "      <td>307.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet chevelle malibu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>buick skylark 320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>28.0</td>\n",
       "      <td>4</td>\n",
       "      <td>120.0</td>\n",
       "      <td>...</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>ford ranger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>31.0</td>\n",
       "      <td>4</td>\n",
       "      <td>119.0</td>\n",
       "      <td>...</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>chevy s-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>398 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  cylinders  displacement  ... year  origin  \\\n",
       "0    18.0          8         307.0  ...   70       1   \n",
       "1    15.0          8         350.0  ...   70       1   \n",
       "..    ...        ...           ...  ...  ...     ...   \n",
       "396  28.0          4         120.0  ...   82       1   \n",
       "397  31.0          4         119.0  ...   82       1   \n",
       "\n",
       "                          name  \n",
       "0    chevrolet chevelle malibu  \n",
       "1            buick skylark 320  \n",
       "..                         ...  \n",
       "396                ford ranger  \n",
       "397                 chevy s-10  \n",
       "\n",
       "[398 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', 7)\n",
    "pd.set_option('display.max_rows', 5)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to generate a second data frame to display statistical information about the first data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'mpg', 'mean': 23.514572864321607, 'var': 61.089610774274405, 'sdev': 7.815984312565782}\n",
      "{'name': 'cylinders', 'mean': 5.454773869346734, 'var': 2.893415439920003, 'sdev': 1.7010042445332119}\n",
      "{'name': 'displacement', 'mean': 193.42587939698493, 'var': 10872.199152247384, 'sdev': 104.26983817119591}\n",
      "{'name': 'weight', 'mean': 2970.424623115578, 'var': 717140.9905256763, 'sdev': 846.8417741973268}\n",
      "{'name': 'acceleration', 'mean': 15.568090452261307, 'var': 7.604848233611383, 'sdev': 2.757688929812676}\n",
      "{'name': 'year', 'mean': 76.01005025125629, 'var': 13.672442818627143, 'sdev': 3.697626646732623}\n",
      "{'name': 'origin', 'mean': 1.5728643216080402, 'var': 0.6432920268850549, 'sdev': 0.8020548777266148}\n"
     ]
    }
   ],
   "source": [
    "# Strip non-numerics\n",
    "df = df.select_dtypes(include=['int', 'float'])\n",
    "\n",
    "headers = list(df.columns.values)\n",
    "fields = []\n",
    "\n",
    "for field in headers:\n",
    "    fields.append({\n",
    "        'name' : field,\n",
    "        'mean': df[field].mean(),\n",
    "        'var': df[field].var(),\n",
    "        'sdev': df[field].std()\n",
    "    })\n",
    "\n",
    "for field in fields:\n",
    "    print(field)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This code outputs a list of dictionaries that hold this statistical information.  This information looks similar to the JSON code seen in Module 1.  To as proper JSON, the program should add these records to a list and call the Python JSON library's **dumps** command called.\n",
    "\n",
    "The Python program can convert this JSON-like information to a data frame for better display."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>name</th>\n",
       "      <th>mean</th>\n",
       "      <th>var</th>\n",
       "      <th>sdev</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>mpg</td>\n",
       "      <td>23.514573</td>\n",
       "      <td>61.089611</td>\n",
       "      <td>7.815984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cylinders</td>\n",
       "      <td>5.454774</td>\n",
       "      <td>2.893415</td>\n",
       "      <td>1.701004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>displacement</td>\n",
       "      <td>193.425879</td>\n",
       "      <td>10872.199152</td>\n",
       "      <td>104.269838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>weight</td>\n",
       "      <td>2970.424623</td>\n",
       "      <td>717140.990526</td>\n",
       "      <td>846.841774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>acceleration</td>\n",
       "      <td>15.568090</td>\n",
       "      <td>7.604848</td>\n",
       "      <td>2.757689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>year</td>\n",
       "      <td>76.010050</td>\n",
       "      <td>13.672443</td>\n",
       "      <td>3.697627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>origin</td>\n",
       "      <td>1.572864</td>\n",
       "      <td>0.643292</td>\n",
       "      <td>0.802055</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name         mean            var        sdev\n",
       "0           mpg    23.514573      61.089611    7.815984\n",
       "1     cylinders     5.454774       2.893415    1.701004\n",
       "2  displacement   193.425879   10872.199152  104.269838\n",
       "3        weight  2970.424623  717140.990526  846.841774\n",
       "4  acceleration    15.568090       7.604848    2.757689\n",
       "5          year    76.010050      13.672443    3.697627\n",
       "6        origin     1.572864       0.643292    0.802055"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', 0)\n",
    "pd.set_option('display.max_rows', 0)\n",
    "df2 = pd.DataFrame(fields)\n",
    "display(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Missing Values\n",
    "\n",
    "Missing values are a reality of machine learning.  Ideally, every row of data will have values for all columns.  However, this is rarely the case.  Most of the values are present in the MPG database.  However, there are missing values in the horsepower column.  A common practice is to replace missing values with the median value for that column.  The program calculates the median as described [here](https://www.mathsisfun.com/median.html).  The following code replaces any NA values in horsepower with the median:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "horsepower has na? True\n",
      "Filling missing values...\n",
      "horsepower has na? False\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\", \n",
    "    na_values=['NA', '?'])\n",
    "print(f\"horsepower has na? {pd.isnull(df['horsepower']).values.any()}\")\n",
    "    \n",
    "print(\"Filling missing values...\")\n",
    "med = df['horsepower'].median()\n",
    "df['horsepower'] = df['horsepower'].fillna(med)\n",
    "# df = df.dropna() # you can also simply drop NA values\n",
    "                 \n",
    "print(f\"horsepower has na? {pd.isnull(df['horsepower']).values.any()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with Outliers\n",
    "\n",
    "Outliers are values that are unusually high or low.  Sometimes outliers are simply errors; this is a result of [observation error](https://en.wikipedia.org/wiki/Observational_error).  Outliers can also be truly large or small values that may be difficult to address. We typically consider outliers to be a value that is several standard deviations from the mean.  The following function can remove such values.    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove all rows where the specified column is +/- sd standard deviations\n",
    "def remove_outliers(df, name, sd):\n",
    "    drop_rows = df.index[(np.abs(df[name] - df[name].mean())\n",
    "                          >= (sd * df[name].std()))]\n",
    "    df.drop(drop_rows, axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code below will drop every row from the Auto MPG dataset where the horsepower is more than two standard deviations above or below the mean."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Length before MPG outliers dropped: 398\n",
      "Length after MPG outliers dropped: 388\n"
     ]
    },
    {
     "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18.0</td>\n",
       "      <td>8</td>\n",
       "      <td>307.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>3504</td>\n",
       "      <td>12.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>3693</td>\n",
       "      <td>11.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>28.0</td>\n",
       "      <td>4</td>\n",
       "      <td>120.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>2625</td>\n",
       "      <td>18.6</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>31.0</td>\n",
       "      <td>4</td>\n",
       "      <td>119.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>2720</td>\n",
       "      <td>19.4</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>388 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  cylinders  displacement  ...  acceleration  year  origin\n",
       "0    18.0          8         307.0  ...          12.0    70       1\n",
       "1    15.0          8         350.0  ...          11.5    70       1\n",
       "..    ...        ...           ...  ...           ...   ...     ...\n",
       "396  28.0          4         120.0  ...          18.6    82       1\n",
       "397  31.0          4         119.0  ...          19.4    82       1\n",
       "\n",
       "[388 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import numpy as np\n",
    "from sklearn import metrics\n",
    "from scipy.stats import zscore\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\",\n",
    "    na_values=['NA','?'])\n",
    "\n",
    "# create feature vector\n",
    "med = df['horsepower'].median()\n",
    "df['horsepower'] = df['horsepower'].fillna(med)\n",
    "\n",
    "# Drop the name column\n",
    "df.drop('name',1,inplace=True)\n",
    "\n",
    "# Drop outliers in horsepower\n",
    "print(\"Length before MPG outliers dropped: {}\".format(len(df)))\n",
    "remove_outliers(df,'mpg',2)\n",
    "print(\"Length after MPG outliers dropped: {}\".format(len(df)))\n",
    "\n",
    "pd.set_option('display.max_columns', 0)\n",
    "pd.set_option('display.max_rows', 5)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dropping Fields\n",
    "\n",
    "Some fields are of no value to the neural network should be dropped.  The following code removes the name column from the MPG dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before drop: ['mpg', 'cylinders', 'displacement', 'horsepower', 'weight', 'acceleration', 'year', 'origin', 'name']\n",
      "After drop: ['mpg', 'cylinders', 'displacement', 'horsepower', 'weight', 'acceleration', 'year', 'origin']\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\",\n",
    "    na_values=['NA','?'])\n",
    "\n",
    "print(f\"Before drop: {list(df.columns)}\")\n",
    "df.drop('name', 1, inplace=True)\n",
    "print(f\"After drop: {list(df.columns)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concatenating Rows and Columns\n",
    "Python can concatenate rows and columns together to form new data frames.  The code below creates a new data frame from the **name** and **horsepower** columns from the Auto MPG dataset.  The program does this by concatenating two columns together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>name</th>\n",
       "      <th>horsepower</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>chevrolet chevelle malibu</td>\n",
       "      <td>130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>buick skylark 320</td>\n",
       "      <td>165.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>ford ranger</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>chevy s-10</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>398 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          name  horsepower\n",
       "0    chevrolet chevelle malibu       130.0\n",
       "1            buick skylark 320       165.0\n",
       "..                         ...         ...\n",
       "396                ford ranger        79.0\n",
       "397                 chevy s-10        82.0\n",
       "\n",
       "[398 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create a new dataframe from name and horsepower\n",
    "\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\",\n",
    "    na_values=['NA','?'])\n",
    "\n",
    "col_horsepower = df['horsepower']\n",
    "col_name = df['name']\n",
    "result = pd.concat([col_name, col_horsepower], axis=1)\n",
    "\n",
    "pd.set_option('display.max_columns', 0)\n",
    "pd.set_option('display.max_rows', 5)\n",
    "display(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **concat** function can also concatenate two rows together.  This code concatenates the first two rows and the last two rows of the Auto MPG dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>...</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18.0</td>\n",
       "      <td>8</td>\n",
       "      <td>307.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet chevelle malibu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>buick skylark 320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>28.0</td>\n",
       "      <td>4</td>\n",
       "      <td>120.0</td>\n",
       "      <td>...</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>ford ranger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>31.0</td>\n",
       "      <td>4</td>\n",
       "      <td>119.0</td>\n",
       "      <td>...</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>chevy s-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  cylinders  displacement  ...  year  origin  \\\n",
       "0    18.0          8         307.0  ...    70       1   \n",
       "1    15.0          8         350.0  ...    70       1   \n",
       "396  28.0          4         120.0  ...    82       1   \n",
       "397  31.0          4         119.0  ...    82       1   \n",
       "\n",
       "                          name  \n",
       "0    chevrolet chevelle malibu  \n",
       "1            buick skylark 320  \n",
       "396                ford ranger  \n",
       "397                 chevy s-10  \n",
       "\n",
       "[4 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create a new dataframe from first 2 rows and last 2 rows\n",
    "\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\",\n",
    "    na_values=['NA','?'])\n",
    "\n",
    "result = pd.concat([df[0:2],df[-2:]], axis=0)\n",
    "\n",
    "pd.set_option('display.max_columns', 7)\n",
    "pd.set_option('display.max_rows', 0)\n",
    "display(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Training and Validation\n",
    "\n",
    "We must evaluate a machine learning model based on its ability to predict data that it has never seen before.  Because of this, we often divide the training data into a validation and training set.  The machine learning model will learn from the training data, but ultimately be evaluated based on the validation data.\n",
    "\n",
    "* **Training Data** - **In Sample Data** - The data that the neural network used to train. \n",
    "* **Validation Data** - **Out of Sample Data** - The data that the machine learning model is evaluated upon after it is fit to the training data.\n",
    "\n",
    "There are two effective means of dealing with training and validation data:\n",
    "\n",
    "* **Training/Validation Split** - The program splits the data according to some ratio between a training and validation (hold-out) set. Typical rates are 80% training and 20% validation.\n",
    "* **K-Fold Cross Validation** - The program splits the data into several folds and models.  Because the program creates the same number of models as folds, the program can generate out-of-sample predictions for the entire dataset.\n",
    "\n",
    "The code below performs a split of the MPG data into a training and validation set.  The training set uses 80% of the data, and the validation set uses 20%. Figure 2.TRN-VAL shows how a model is trained on 80% of the data and then validated against the remaining 20%.\n",
    "\n",
    "**Figure 2.TRN-VAL: Training and Validation**\n",
    "![Training and Validation](https://raw.githubusercontent.com/jeffheaton/t81_558_deep_learning/master/images/class_1_train_val.png \"Training and Validation\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Training DF: 333\n",
      "Validation DF: 65\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\",\n",
    "    na_values=['NA','?'])\n",
    "\n",
    "# Usually a good idea to shuffle\n",
    "df = df.reindex(np.random.permutation(df.index)) \n",
    "\n",
    "mask = np.random.rand(len(df)) < 0.8\n",
    "trainDF = pd.DataFrame(df[mask])\n",
    "validationDF = pd.DataFrame(df[~mask])\n",
    "\n",
    "print(f\"Training DF: {len(trainDF)}\")\n",
    "print(f\"Validation DF: {len(validationDF)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting a Dataframe to a Matrix\n",
    "\n",
    "Neural networks do not directly operate on Python data frames.  A neural network requires a numeric matrix.  The program uses the **values** property of a data frame to convert the data to a matrix."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[20.2, 6, 232.0, ..., 79, 1, 'amc concord dl 6'],\n",
       "       [14.0, 8, 304.0, ..., 74, 1, 'amc matador (sw)'],\n",
       "       [14.0, 8, 351.0, ..., 71, 1, 'ford galaxie 500'],\n",
       "       ...,\n",
       "       [20.2, 6, 200.0, ..., 78, 1, 'ford fairmont (auto)'],\n",
       "       [26.0, 4, 97.0, ..., 70, 2, 'volkswagen 1131 deluxe sedan'],\n",
       "       [19.4, 6, 232.0, ..., 78, 1, 'amc concord']], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might wish only to convert some of the columns, to leave out the name column, use the following code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 20.2,   6. , 232. , ...,  18.2,  79. ,   1. ],\n",
       "       [ 14. ,   8. , 304. , ...,  15.5,  74. ,   1. ],\n",
       "       [ 14. ,   8. , 351. , ...,  13.5,  71. ,   1. ],\n",
       "       ...,\n",
       "       [ 20.2,   6. , 200. , ...,  15.8,  78. ,   1. ],\n",
       "       [ 26. ,   4. ,  97. , ...,  20.5,  70. ,   2. ],\n",
       "       [ 19.4,   6. , 232. , ...,  17.2,  78. ,   1. ]])"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['mpg', 'cylinders', 'displacement', 'horsepower', 'weight',\n",
    "       'acceleration', 'year', 'origin']].values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving a Dataframe to CSV\n",
    "\n",
    "Many of the assignments in this course will require that you save a data frame to submit to the instructor.  The following code performs a shuffle and then saves a new copy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "path = \".\"\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\",\n",
    "    na_values=['NA','?'])\n",
    "\n",
    "filename_write = os.path.join(path, \"auto-mpg-shuffle.csv\")\n",
    "df = df.reindex(np.random.permutation(df.index))\n",
    "# Specify index = false to not write row numbers\n",
    "df.to_csv(filename_write, index=False) \n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving a Dataframe to Pickle\n",
    "\n",
    "A variety of software programs can make use of text files stored as CSV.  However, they do take longer to generate and can sometimes lose small amounts of precision in the conversion.  Another format is [Pickle](https://docs.python.org/3/library/pickle.html).  Generally, you will output to CSV because it is very compatible, even outside of Python.  The code below stores the Dataframe to Pickle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "\n",
    "path = \".\"\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\",\n",
    "    na_values=['NA','?'])\n",
    "\n",
    "filename_write = os.path.join(path, \"auto-mpg-shuffle.pkl\")\n",
    "df = df.reindex(np.random.permutation(df.index))\n",
    "\n",
    "with open(filename_write,\"wb\") as fp:\n",
    "    pickle.dump(df, fp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Loading the pickle file back into memory is accomplished by the following lines of code.  Notice that the index numbers are still jumbled from the previous shuffle?  Loading the CSV rebuilt (in the last step) did not preserve these values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>...</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>38.0</td>\n",
       "      <td>6</td>\n",
       "      <td>262.0</td>\n",
       "      <td>...</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>oldsmobile cutlass ciera (diesel)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>25.4</td>\n",
       "      <td>6</td>\n",
       "      <td>168.0</td>\n",
       "      <td>...</td>\n",
       "      <td>81</td>\n",
       "      <td>3</td>\n",
       "      <td>toyota cressida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>31.6</td>\n",
       "      <td>4</td>\n",
       "      <td>120.0</td>\n",
       "      <td>...</td>\n",
       "      <td>81</td>\n",
       "      <td>3</td>\n",
       "      <td>mazda 626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>30.5</td>\n",
       "      <td>4</td>\n",
       "      <td>98.0</td>\n",
       "      <td>...</td>\n",
       "      <td>77</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet chevette</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>398 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  cylinders  displacement  ...  year  origin  \\\n",
       "387  38.0          6         262.0  ...    82       1   \n",
       "361  25.4          6         168.0  ...    81       3   \n",
       "..    ...        ...           ...  ...   ...     ...   \n",
       "358  31.6          4         120.0  ...    81       3   \n",
       "237  30.5          4          98.0  ...    77       1   \n",
       "\n",
       "                                  name  \n",
       "387  oldsmobile cutlass ciera (diesel)  \n",
       "361                    toyota cressida  \n",
       "..                                 ...  \n",
       "358                          mazda 626  \n",
       "237                 chevrolet chevette  \n",
       "\n",
       "[398 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "\n",
    "path = \".\"\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\",\n",
    "    na_values=['NA','?'])\n",
    "\n",
    "filename_read = os.path.join(path, \"auto-mpg-shuffle.pkl\")\n",
    "\n",
    "with open(filename_write,\"rb\") as fp:\n",
    "    df = pickle.load(fp)\n",
    "\n",
    "pd.set_option('display.max_columns', 7)\n",
    "pd.set_option('display.max_rows', 5)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 2 Assignment\n",
    "\n",
    "You can find the first assignment here: [assignment 2](https://github.com/jeffheaton/t81_558_deep_learning/blob/master/assignments/assignment_yourname_class2.ipynb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3.9 (tensorflow)",
   "language": "python",
   "name": "tensorflow"
  },
  "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}