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]](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]](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]](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]](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]](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 data is manipulated in conjunction with neural networks.\n",
    "\n",
    "The dataframe is a key component of Pandas.  We will use it to access the [auto-mpg dataset](https://archive.ics.uci.edu/ml/datasets/Auto+MPG).  This dataset can be found 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",
    "This dataset was taken from the StatLib library which is maintained at Carnegie Mellon University. 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 dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    mpg  cylinders  displacement horsepower  weight  acceleration  year  \\\n",
      "0  18.0          8         307.0        130    3504          12.0    70   \n",
      "1  15.0          8         350.0        165    3693          11.5    70   \n",
      "2  18.0          8         318.0        150    3436          11.0    70   \n",
      "3  16.0          8         304.0        150    3433          12.0    70   \n",
      "4  17.0          8         302.0        140    3449          10.5    70   \n",
      "\n",
      "   origin                       name  \n",
      "0       1  chevrolet chevelle malibu  \n",
      "1       1          buick skylark 320  \n",
      "2       1         plymouth satellite  \n",
      "3       1              amc rebel sst  \n",
      "4       1                ford torino  \n"
     ]
    }
   ],
   "source": [
    "# Simple dataframe\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"https://data.heatonresearch.com/data/t81-558/auto-mpg.csv\")\n",
    "print(df[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **display** function provides a cleaner display than simply printing the dataframe."
   ]
  },
  {
   "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>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</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>130</td>\n",
       "      <td>3504</td>\n",
       "      <td>12.0</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>165</td>\n",
       "      <td>3693</td>\n",
       "      <td>11.5</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>150</td>\n",
       "      <td>3436</td>\n",
       "      <td>11.0</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>150</td>\n",
       "      <td>3433</td>\n",
       "      <td>12.0</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>140</td>\n",
       "      <td>3449</td>\n",
       "      <td>10.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford torino</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    mpg  cylinders  displacement horsepower  weight  acceleration  year  \\\n",
       "0  18.0          8         307.0        130    3504          12.0    70   \n",
       "1  15.0          8         350.0        165    3693          11.5    70   \n",
       "2  18.0          8         318.0        150    3436          11.0    70   \n",
       "3  16.0          8         304.0        150    3433          12.0    70   \n",
       "4  17.0          8         302.0        140    3449          10.5    70   \n",
       "\n",
       "   origin                       name  \n",
       "0       1  chevrolet chevelle malibu  \n",
       "1       1          buick skylark 320  \n",
       "2       1         plymouth satellite  \n",
       "3       1              amc rebel sst  \n",
       "4       1                ford torino  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to generate a second dataframe to display statistical information about the first dataframe."
   ]
  },
  {
   "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": [
    "Converting this to a dataframe allows for a nicer 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": [
    "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 median is calculated 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 account for. Outliers are usually defined as a value that is several standard deviations from the mean.  The following function can be used to 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 2 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>2</th>\n",
       "      <td>18.0</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>3436</td>\n",
       "      <td>11.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</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>150.0</td>\n",
       "      <td>3433</td>\n",
       "      <td>12.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</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>140.0</td>\n",
       "      <td>3449</td>\n",
       "      <td>10.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    mpg  cylinders  displacement  horsepower  weight  acceleration  year  \\\n",
       "0  18.0          8         307.0       130.0    3504          12.0    70   \n",
       "1  15.0          8         350.0       165.0    3693          11.5    70   \n",
       "2  18.0          8         318.0       150.0    3436          11.0    70   \n",
       "3  16.0          8         304.0       150.0    3433          12.0    70   \n",
       "4  17.0          8         302.0       140.0    3449          10.5    70   \n",
       "\n",
       "   origin  \n",
       "0       1  \n",
       "1       1  \n",
       "2       1  \n",
       "3       1  \n",
       "4       1  "
      ]
     },
     "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",
    "display(df[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dropping Fields\n",
    "\n",
    "Some fields are of no value to the neural network and can 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",
    "Rows and columns can be concatenated together to form new data frames.  The code below creates a new dataframe from name and horsepower from the Auto MPG dataset.  This is done 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>2</th>\n",
       "      <td>plymouth satellite</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>amc rebel sst</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ford torino</td>\n",
       "      <td>140.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        name  horsepower\n",
       "0  chevrolet chevelle malibu       130.0\n",
       "1          buick skylark 320       165.0\n",
       "2         plymouth satellite       150.0\n",
       "3              amc rebel sst       150.0\n",
       "4                ford torino       140.0"
      ]
     },
     "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",
    "display(result[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **concat** function can also concatenate two rows together.  This code concatenates the first 2 rows and the last 2 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>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</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>130.0</td>\n",
       "      <td>3504</td>\n",
       "      <td>12.0</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>165.0</td>\n",
       "      <td>3693</td>\n",
       "      <td>11.5</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>79.0</td>\n",
       "      <td>2625</td>\n",
       "      <td>18.6</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>82.0</td>\n",
       "      <td>2720</td>\n",
       "      <td>19.4</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>chevy s-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  cylinders  displacement  horsepower  weight  acceleration  year  \\\n",
       "0    18.0          8         307.0       130.0    3504          12.0    70   \n",
       "1    15.0          8         350.0       165.0    3693          11.5    70   \n",
       "396  28.0          4         120.0        79.0    2625          18.6    82   \n",
       "397  31.0          4         119.0        82.0    2720          19.4    82   \n",
       "\n",
       "     origin                       name  \n",
       "0         1  chevrolet chevelle malibu  \n",
       "1         1          buick skylark 320  \n",
       "396       1                ford ranger  \n",
       "397       1                 chevy s-10  "
      ]
     },
     "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",
    "display(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Training and Validation\n",
    "\n",
    "It is very important that we 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 machine learning model was fit to/created from. \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 predominant means of dealing with training and validation data:\n",
    "\n",
    "* **Training/Validation Split** - The data are split according to some ratio between a training and validation (hold-out) set.  Common ratios are 80% training and 20% validation.\n",
    "* **K-Fold Cross Validation** - The data are split into a number of folds and models.  Because a number of models equal to the folds is created out-of-sample predictions can be generated 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%.\n",
    "\n",
    "The following image shows how a model is trained on 80% of the data and then validated against the remaining 20%.\n",
    "\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: 311\n",
      "Validation DF: 87\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",
    "df = df.reindex(np.random.permutation(df.index)) # Usually a good idea to shuffle\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 dataframes.  A neural network requires a numeric matrix.  The **values** property of a dataframe is used to convert to a matrix."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[32.0, 4, 85.0, ..., 76, 3, 'datsun b-210'],\n",
       "       [18.0, 6, 171.0, ..., 75, 1, 'ford pinto'],\n",
       "       [31.0, 4, 79.0, ..., 74, 2, 'fiat x1.9'],\n",
       "       ...,\n",
       "       [19.0, 6, 250.0, ..., 71, 1, 'ford torino 500'],\n",
       "       [27.0, 4, 97.0, ..., 71, 2, 'volkswagen model 111'],\n",
       "       [18.5, 6, 250.0, ..., 77, 1, 'ford granada']], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might wish to only 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([[ 32. ,   4. ,  85. , ...,  17. ,  76. ,   3. ],\n",
       "       [ 18. ,   6. , 171. , ...,  14.5,  75. ,   1. ],\n",
       "       [ 31. ,   4. ,  79. , ...,  16. ,  74. ,   2. ],\n",
       "       ...,\n",
       "       [ 19. ,   6. , 250. , ...,  15.5,  71. ,   1. ],\n",
       "       [ 27. ,   4. ,  97. , ...,  19. ,  71. ,   2. ],\n",
       "       [ 18.5,   6. , 250. , ...,  19. ,  77. ,   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 dataframe 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",
    "df.to_csv(filename_write, index=False) # Specify index = false to not write row numbers\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving a Dataframe to Pickle\n",
    "\n",
    "CSV files are text and can be used by a variety of software programs.  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": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\n"
     ]
    }
   ],
   "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)\n",
    "\n",
    "print(\"Done\")"
   ]
  },
  {
   "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 previous 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>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>429.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>4341</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford galaxie 500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>21.0</td>\n",
       "      <td>4</td>\n",
       "      <td>122.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2226</td>\n",
       "      <td>16.5</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>ford pinto runabout</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>16.2</td>\n",
       "      <td>6</td>\n",
       "      <td>163.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>3410</td>\n",
       "      <td>15.8</td>\n",
       "      <td>78</td>\n",
       "      <td>2</td>\n",
       "      <td>peugeot 604sl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>33.0</td>\n",
       "      <td>4</td>\n",
       "      <td>91.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>1795</td>\n",
       "      <td>17.5</td>\n",
       "      <td>75</td>\n",
       "      <td>3</td>\n",
       "      <td>honda civic cvcc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>24.5</td>\n",
       "      <td>4</td>\n",
       "      <td>98.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>2164</td>\n",
       "      <td>22.1</td>\n",
       "      <td>76</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet woody</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  cylinders  displacement  horsepower  weight  acceleration  year  \\\n",
       "5    15.0          8         429.0       198.0    4341          10.0    70   \n",
       "61   21.0          4         122.0        86.0    2226          16.5    72   \n",
       "277  16.2          6         163.0       133.0    3410          15.8    78   \n",
       "181  33.0          4          91.0        53.0    1795          17.5    75   \n",
       "196  24.5          4          98.0        60.0    2164          22.1    76   \n",
       "\n",
       "     origin                 name  \n",
       "5         1     ford galaxie 500  \n",
       "61        1  ford pinto runabout  \n",
       "277       2        peugeot 604sl  \n",
       "181       3     honda civic cvcc  \n",
       "196       1      chevrolet woody  "
      ]
     },
     "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",
    "display(df[0:5])"
   ]
  },
  {
   "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.6 (tensorflow)",
   "language": "python",
   "name": "rga"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}