t81_558_class_02_3_pandas_grouping.ipynb

{
 "cells": [
  {
   "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": [
    "# Part 2.3: Grouping, Sorting, and Shuffling  \n",
    "\n",
    "### Shuffling a Dataset\n",
    "The following code is used to shuffle and reindex a data set.  A random seed can be used to produce a consistent shuffling of the data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "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>25.0</td>\n",
       "      <td>4</td>\n",
       "      <td>98.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2046</td>\n",
       "      <td>19.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>ford pinto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19.0</td>\n",
       "      <td>6</td>\n",
       "      <td>250.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>3302</td>\n",
       "      <td>15.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>ford torino 500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16.0</td>\n",
       "      <td>8</td>\n",
       "      <td>302.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>4141</td>\n",
       "      <td>14.0</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>ford gran torino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18.2</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>3830</td>\n",
       "      <td>15.2</td>\n",
       "      <td>79</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge st. regis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32.2</td>\n",
       "      <td>4</td>\n",
       "      <td>108.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>2265</td>\n",
       "      <td>15.2</td>\n",
       "      <td>80</td>\n",
       "      <td>3</td>\n",
       "      <td>toyota corolla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>36.1</td>\n",
       "      <td>4</td>\n",
       "      <td>98.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>1800</td>\n",
       "      <td>14.4</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>ford fiesta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>44.0</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>2130</td>\n",
       "      <td>24.6</td>\n",
       "      <td>82</td>\n",
       "      <td>2</td>\n",
       "      <td>vw pickup</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10.0</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>4615</td>\n",
       "      <td>14.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford f250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>30.7</td>\n",
       "      <td>6</td>\n",
       "      <td>145.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3160</td>\n",
       "      <td>19.6</td>\n",
       "      <td>81</td>\n",
       "      <td>2</td>\n",
       "      <td>volvo diesel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>19.1</td>\n",
       "      <td>6</td>\n",
       "      <td>225.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3381</td>\n",
       "      <td>18.7</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge aspen</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    mpg  cylinders  displacement  horsepower  weight  acceleration  year  \\\n",
       "0  25.0          4          98.0         NaN    2046          19.0    71   \n",
       "1  19.0          6         250.0        88.0    3302          15.5    71   \n",
       "2  16.0          8         302.0       140.0    4141          14.0    74   \n",
       "3  18.2          8         318.0       135.0    3830          15.2    79   \n",
       "4  32.2          4         108.0        75.0    2265          15.2    80   \n",
       "5  36.1          4          98.0        66.0    1800          14.4    78   \n",
       "6  44.0          4          97.0        52.0    2130          24.6    82   \n",
       "7  10.0          8         360.0       215.0    4615          14.0    70   \n",
       "8  30.7          6         145.0        76.0    3160          19.6    81   \n",
       "9  19.1          6         225.0        90.0    3381          18.7    80   \n",
       "\n",
       "   origin              name  \n",
       "0       1        ford pinto  \n",
       "1       1   ford torino 500  \n",
       "2       1  ford gran torino  \n",
       "3       1   dodge st. regis  \n",
       "4       3    toyota corolla  \n",
       "5       1       ford fiesta  \n",
       "6       2         vw pickup  \n",
       "7       1         ford f250  \n",
       "8       2      volvo diesel  \n",
       "9       1       dodge aspen  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "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",
    "#np.random.seed(42) # Uncomment this line to get the same shuffle each time\n",
    "df = df.reindex(np.random.permutation(df.index))\n",
    "df.reset_index(inplace=True, drop=True)\n",
    "display(df[0:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting a Data Set\n",
    "\n",
    "Data sets can also be sorted.  This code sorts the MPG dataset by name and displays the first car."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The first car is: amc ambassador brougham\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",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>13.0</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>3821</td>\n",
       "      <td>11.0</td>\n",
       "      <td>73</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador brougham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>390.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>3850</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador dpl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>17.0</td>\n",
       "      <td>8</td>\n",
       "      <td>304.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>3672</td>\n",
       "      <td>11.5</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador sst</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>24.3</td>\n",
       "      <td>4</td>\n",
       "      <td>151.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3003</td>\n",
       "      <td>20.1</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>amc concord</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>19.4</td>\n",
       "      <td>6</td>\n",
       "      <td>232.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3210</td>\n",
       "      <td>17.2</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>amc concord</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  cylinders  displacement  horsepower  weight  acceleration  year  \\\n",
       "96   13.0          8         360.0       175.0    3821          11.0    73   \n",
       "9    15.0          8         390.0       190.0    3850           8.5    70   \n",
       "66   17.0          8         304.0       150.0    3672          11.5    72   \n",
       "315  24.3          4         151.0        90.0    3003          20.1    80   \n",
       "257  19.4          6         232.0        90.0    3210          17.2    78   \n",
       "\n",
       "     origin                     name  \n",
       "96        1  amc ambassador brougham  \n",
       "9         1       amc ambassador dpl  \n",
       "66        1       amc ambassador sst  \n",
       "315       1              amc concord  \n",
       "257       1              amc concord  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "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",
    "df = df.sort_values(by='name', ascending=True)\n",
    "print(f\"The first car is: {df['name'].iloc[0]}\")\n",
    "display(df[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping a Data Set\n",
    "\n",
    "Grouping is a common operation on data sets.  In SQL, this operation is referred to as \"GROUP BY\".  Grouping is used to summarize data.  Because of this summarization the row could will either stay the same or more likely shrink after a grouping is applied.\n",
    "\n",
    "The Auto MPG dataset is used to demonstrate grouping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>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",
       "      <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.0</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.0</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.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                       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": [
    "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",
    "display(df[0:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above data set can be used with group to perform summaries.  For example, the following code will group cylinders by the average (mean).  This code will provide the grouping.  In addition to mean, other aggregating functions, such as **sum** or **count** can be used. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cylinders\n",
       "3    20.550000\n",
       "4    29.286765\n",
       "5    27.366667\n",
       "6    19.985714\n",
       "8    14.963107\n",
       "Name: mpg, dtype: float64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g = df.groupby('cylinders')['mpg'].mean()\n",
    "g"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It might be useful to have these **mean** values as a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{3: 20.55,\n",
       " 4: 29.28676470588236,\n",
       " 5: 27.366666666666664,\n",
       " 6: 19.985714285714284,\n",
       " 8: 14.963106796116508}"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = g.to_dict()\n",
    "d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This allows you to quickly access an individual element, such as to lookup the mean for 6 cylinders.  This is used in target encoding, which is presented in this module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19.985714285714284"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d[6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code below shows how to count the number of rows that match each cylinder count."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{3: 4, 4: 204, 5: 3, 6: 84, 8: 103}"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('cylinders')['mpg'].count().to_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2.4: Apply and Map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **apply** and **map** functions can also be applied to Pandas **dataframes**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Map with Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>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",
       "      <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.0</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.0</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",
       "    <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>6</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>454.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>4354</td>\n",
       "      <td>9.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet impala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>440.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>4312</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth fury iii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>455.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>4425</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>pontiac catalina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>390.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>3850</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador dpl</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",
       "5  15.0          8         429.0       198.0    4341          10.0    70   \n",
       "6  14.0          8         454.0       220.0    4354           9.0    70   \n",
       "7  14.0          8         440.0       215.0    4312           8.5    70   \n",
       "8  14.0          8         455.0       225.0    4425          10.0    70   \n",
       "9  15.0          8         390.0       190.0    3850           8.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",
       "5       1           ford galaxie 500  \n",
       "6       1           chevrolet impala  \n",
       "7       1          plymouth fury iii  \n",
       "8       1           pontiac catalina  \n",
       "9       1         amc ambassador dpl  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "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",
    "display(df[0:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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",
       "      <th>origin_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",
       "      <td>North America</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",
       "      <td>North America</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",
       "      <td>plymouth satellite</td>\n",
       "      <td>North America</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",
       "      <td>amc rebel sst</td>\n",
       "      <td>North America</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",
       "      <td>ford torino</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\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",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>454.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>4354</td>\n",
       "      <td>9.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet impala</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>440.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>4312</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth fury iii</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>455.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>4425</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>pontiac catalina</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>390.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>3850</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador dpl</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>383.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>3563</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge challenger se</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>340.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>3609</td>\n",
       "      <td>8.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth 'cuda 340</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>15.0</td>\n",
       "      <td>8</td>\n",
       "      <td>400.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>3761</td>\n",
       "      <td>9.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet monte carlo</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>455.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>3086</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>buick estate wagon (sw)</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>24.0</td>\n",
       "      <td>4</td>\n",
       "      <td>113.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2372</td>\n",
       "      <td>15.0</td>\n",
       "      <td>70</td>\n",
       "      <td>3</td>\n",
       "      <td>toyota corona mark ii</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>22.0</td>\n",
       "      <td>6</td>\n",
       "      <td>198.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2833</td>\n",
       "      <td>15.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth duster</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>18.0</td>\n",
       "      <td>6</td>\n",
       "      <td>199.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>2774</td>\n",
       "      <td>15.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc hornet</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>21.0</td>\n",
       "      <td>6</td>\n",
       "      <td>200.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>2587</td>\n",
       "      <td>16.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford maverick</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>27.0</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2130</td>\n",
       "      <td>14.5</td>\n",
       "      <td>70</td>\n",
       "      <td>3</td>\n",
       "      <td>datsun pl510</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>26.0</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>1835</td>\n",
       "      <td>20.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>volkswagen 1131 deluxe sedan</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>25.0</td>\n",
       "      <td>4</td>\n",
       "      <td>110.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>2672</td>\n",
       "      <td>17.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>peugeot 504</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>24.0</td>\n",
       "      <td>4</td>\n",
       "      <td>107.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2430</td>\n",
       "      <td>14.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>audi 100 ls</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>25.0</td>\n",
       "      <td>4</td>\n",
       "      <td>104.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2375</td>\n",
       "      <td>17.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>saab 99e</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>26.0</td>\n",
       "      <td>4</td>\n",
       "      <td>121.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>2234</td>\n",
       "      <td>12.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>bmw 2002</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>21.0</td>\n",
       "      <td>6</td>\n",
       "      <td>199.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2648</td>\n",
       "      <td>15.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc gremlin</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10.0</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>4615</td>\n",
       "      <td>14.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford f250</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10.0</td>\n",
       "      <td>8</td>\n",
       "      <td>307.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>4376</td>\n",
       "      <td>15.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevy c20</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11.0</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>4382</td>\n",
       "      <td>13.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge d200</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>9.0</td>\n",
       "      <td>8</td>\n",
       "      <td>304.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>4732</td>\n",
       "      <td>18.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>hi 1200d</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>27.0</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2130</td>\n",
       "      <td>14.5</td>\n",
       "      <td>71</td>\n",
       "      <td>3</td>\n",
       "      <td>datsun pl510</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>28.0</td>\n",
       "      <td>4</td>\n",
       "      <td>140.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2264</td>\n",
       "      <td>15.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet vega 2300</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>25.0</td>\n",
       "      <td>4</td>\n",
       "      <td>113.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2228</td>\n",
       "      <td>14.0</td>\n",
       "      <td>71</td>\n",
       "      <td>3</td>\n",
       "      <td>toyota corona</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>25.0</td>\n",
       "      <td>4</td>\n",
       "      <td>98.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2046</td>\n",
       "      <td>19.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>ford pinto</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>19.0</td>\n",
       "      <td>6</td>\n",
       "      <td>232.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2634</td>\n",
       "      <td>13.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>amc gremlin</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>16.0</td>\n",
       "      <td>6</td>\n",
       "      <td>225.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3439</td>\n",
       "      <td>15.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth satellite custom</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>17.0</td>\n",
       "      <td>6</td>\n",
       "      <td>250.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>3329</td>\n",
       "      <td>15.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet chevelle malibu</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>19.0</td>\n",
       "      <td>6</td>\n",
       "      <td>250.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>3302</td>\n",
       "      <td>15.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>ford torino 500</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>18.0</td>\n",
       "      <td>6</td>\n",
       "      <td>232.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>3288</td>\n",
       "      <td>15.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>amc matador</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>350.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>4209</td>\n",
       "      <td>12.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet impala</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>400.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>4464</td>\n",
       "      <td>11.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>pontiac catalina brougham</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>351.0</td>\n",
       "      <td>153.0</td>\n",
       "      <td>4154</td>\n",
       "      <td>13.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>ford galaxie 500</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>14.0</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>4096</td>\n",
       "      <td>13.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth fury iii</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>12.0</td>\n",
       "      <td>8</td>\n",
       "      <td>383.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>4955</td>\n",
       "      <td>11.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge monaco (sw)</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>13.0</td>\n",
       "      <td>8</td>\n",
       "      <td>400.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>4746</td>\n",
       "      <td>12.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>ford country squire (sw)</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>13.0</td>\n",
       "      <td>8</td>\n",
       "      <td>400.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>5140</td>\n",
       "      <td>12.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>pontiac safari (sw)</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>18.0</td>\n",
       "      <td>6</td>\n",
       "      <td>258.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>2962</td>\n",
       "      <td>13.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>amc hornet sportabout (sw)</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>22.0</td>\n",
       "      <td>4</td>\n",
       "      <td>140.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>2408</td>\n",
       "      <td>19.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet vega (sw)</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>19.0</td>\n",
       "      <td>6</td>\n",
       "      <td>250.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>3282</td>\n",
       "      <td>15.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>pontiac firebird</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>18.0</td>\n",
       "      <td>6</td>\n",
       "      <td>250.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>3139</td>\n",
       "      <td>14.5</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>ford mustang</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>23.0</td>\n",
       "      <td>4</td>\n",
       "      <td>122.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2220</td>\n",
       "      <td>14.0</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>mercury capri 2000</td>\n",
       "      <td>North America</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",
       "5   15.0          8         429.0       198.0    4341          10.0    70   \n",
       "6   14.0          8         454.0       220.0    4354           9.0    70   \n",
       "7   14.0          8         440.0       215.0    4312           8.5    70   \n",
       "8   14.0          8         455.0       225.0    4425          10.0    70   \n",
       "9   15.0          8         390.0       190.0    3850           8.5    70   \n",
       "10  15.0          8         383.0       170.0    3563          10.0    70   \n",
       "11  14.0          8         340.0       160.0    3609           8.0    70   \n",
       "12  15.0          8         400.0       150.0    3761           9.5    70   \n",
       "13  14.0          8         455.0       225.0    3086          10.0    70   \n",
       "14  24.0          4         113.0        95.0    2372          15.0    70   \n",
       "15  22.0          6         198.0        95.0    2833          15.5    70   \n",
       "16  18.0          6         199.0        97.0    2774          15.5    70   \n",
       "17  21.0          6         200.0        85.0    2587          16.0    70   \n",
       "18  27.0          4          97.0        88.0    2130          14.5    70   \n",
       "19  26.0          4          97.0        46.0    1835          20.5    70   \n",
       "20  25.0          4         110.0        87.0    2672          17.5    70   \n",
       "21  24.0          4         107.0        90.0    2430          14.5    70   \n",
       "22  25.0          4         104.0        95.0    2375          17.5    70   \n",
       "23  26.0          4         121.0       113.0    2234          12.5    70   \n",
       "24  21.0          6         199.0        90.0    2648          15.0    70   \n",
       "25  10.0          8         360.0       215.0    4615          14.0    70   \n",
       "26  10.0          8         307.0       200.0    4376          15.0    70   \n",
       "27  11.0          8         318.0       210.0    4382          13.5    70   \n",
       "28   9.0          8         304.0       193.0    4732          18.5    70   \n",
       "29  27.0          4          97.0        88.0    2130          14.5    71   \n",
       "30  28.0          4         140.0        90.0    2264          15.5    71   \n",
       "31  25.0          4         113.0        95.0    2228          14.0    71   \n",
       "32  25.0          4          98.0         NaN    2046          19.0    71   \n",
       "33  19.0          6         232.0       100.0    2634          13.0    71   \n",
       "34  16.0          6         225.0       105.0    3439          15.5    71   \n",
       "35  17.0          6         250.0       100.0    3329          15.5    71   \n",
       "36  19.0          6         250.0        88.0    3302          15.5    71   \n",
       "37  18.0          6         232.0       100.0    3288          15.5    71   \n",
       "38  14.0          8         350.0       165.0    4209          12.0    71   \n",
       "39  14.0          8         400.0       175.0    4464          11.5    71   \n",
       "40  14.0          8         351.0       153.0    4154          13.5    71   \n",
       "41  14.0          8         318.0       150.0    4096          13.0    71   \n",
       "42  12.0          8         383.0       180.0    4955          11.5    71   \n",
       "43  13.0          8         400.0       170.0    4746          12.0    71   \n",
       "44  13.0          8         400.0       175.0    5140          12.0    71   \n",
       "45  18.0          6         258.0       110.0    2962          13.5    71   \n",
       "46  22.0          4         140.0        72.0    2408          19.0    71   \n",
       "47  19.0          6         250.0       100.0    3282          15.0    71   \n",
       "48  18.0          6         250.0        88.0    3139          14.5    71   \n",
       "49  23.0          4         122.0        86.0    2220          14.0    71   \n",
       "\n",
       "    origin                          name    origin_name  \n",
       "0        1     chevrolet chevelle malibu  North America  \n",
       "1        1             buick skylark 320  North America  \n",
       "2        1            plymouth satellite  North America  \n",
       "3        1                 amc rebel sst  North America  \n",
       "4        1                   ford torino  North America  \n",
       "5        1              ford galaxie 500  North America  \n",
       "6        1              chevrolet impala  North America  \n",
       "7        1             plymouth fury iii  North America  \n",
       "8        1              pontiac catalina  North America  \n",
       "9        1            amc ambassador dpl  North America  \n",
       "10       1           dodge challenger se  North America  \n",
       "11       1            plymouth 'cuda 340  North America  \n",
       "12       1         chevrolet monte carlo  North America  \n",
       "13       1       buick estate wagon (sw)  North America  \n",
       "14       3         toyota corona mark ii           Asia  \n",
       "15       1               plymouth duster  North America  \n",
       "16       1                    amc hornet  North America  \n",
       "17       1                 ford maverick  North America  \n",
       "18       3                  datsun pl510           Asia  \n",
       "19       2  volkswagen 1131 deluxe sedan         Europe  \n",
       "20       2                   peugeot 504         Europe  \n",
       "21       2                   audi 100 ls         Europe  \n",
       "22       2                      saab 99e         Europe  \n",
       "23       2                      bmw 2002         Europe  \n",
       "24       1                   amc gremlin  North America  \n",
       "25       1                     ford f250  North America  \n",
       "26       1                     chevy c20  North America  \n",
       "27       1                    dodge d200  North America  \n",
       "28       1                      hi 1200d  North America  \n",
       "29       3                  datsun pl510           Asia  \n",
       "30       1           chevrolet vega 2300  North America  \n",
       "31       3                 toyota corona           Asia  \n",
       "32       1                    ford pinto  North America  \n",
       "33       1                   amc gremlin  North America  \n",
       "34       1     plymouth satellite custom  North America  \n",
       "35       1     chevrolet chevelle malibu  North America  \n",
       "36       1               ford torino 500  North America  \n",
       "37       1                   amc matador  North America  \n",
       "38       1              chevrolet impala  North America  \n",
       "39       1     pontiac catalina brougham  North America  \n",
       "40       1              ford galaxie 500  North America  \n",
       "41       1             plymouth fury iii  North America  \n",
       "42       1             dodge monaco (sw)  North America  \n",
       "43       1      ford country squire (sw)  North America  \n",
       "44       1           pontiac safari (sw)  North America  \n",
       "45       1    amc hornet sportabout (sw)  North America  \n",
       "46       1           chevrolet vega (sw)  North America  \n",
       "47       1              pontiac firebird  North America  \n",
       "48       1                  ford mustang  North America  \n",
       "49       1            mercury capri 2000  North America  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['origin_name'] = df['origin'].map({1: 'North America', 2: 'Europe', 3: 'Asia'})\n",
    "display(df[0:50])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Apply with Dataframes\n",
    "\n",
    "If the **apply** function is directly executed on the data frame, the lambda function is called once per column or row, depending on the value of axis.  For axis = 1, rows are used. \n",
    "\n",
    "The following code calculates a series called **efficiency** that is the **displacement** divided by **horsepower**. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2.361538\n",
       "1    2.121212\n",
       "2    2.120000\n",
       "3    2.026667\n",
       "4    2.157143\n",
       "5    2.166667\n",
       "6    2.063636\n",
       "7    2.046512\n",
       "8    2.022222\n",
       "9    2.052632\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "effi = df.apply(lambda x: x['displacement']/x['horsepower'], axis=1)\n",
    "display(effi[0:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Feature Engineering with Apply and Map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section we will see how to calculate a complex feature using map, apply, and grouping.  The data set is the following CSV:\n",
    "\n",
    "* https://www.irs.gov/pub/irs-soi/16zpallagi.csv \n",
    "\n",
    "This is US Government public data for \"SOI Tax Stats - Individual Income Tax Statistics\".  The primary website is here:\n",
    "\n",
    "* https://www.irs.gov/statistics/soi-tax-stats-individual-income-tax-statistics-2016-zip-code-data-soi \n",
    "\n",
    "Documentation describing this data is at the above link.\n",
    "\n",
    "For this feature, we will attempt to estimate the adjusted gross income (AGI) for each of the zipcodes.  The data file contains many columns; however, you will only use the following:\n",
    "\n",
    "* STATE - The state (e.g. MO)\n",
    "* zipcode - The zipcode (e.g. 63017)\n",
    "* agi_stub - Six different brackets of annual income (1 through 6) \n",
    "* N1 - The number of tax returns for each of the agi_stubs\n",
    "\n",
    "Note, the file will have 6 rows for each zipcode, for each of the agi_stub brackets. You can skip zipcodes with 0 or 99999.\n",
    "\n",
    "We will create an output CSV with these columns; however, only one row per zip code. Calculate a weighted average of the income brackets. For example, the following 6 rows are present for 63017:\n",
    "\n",
    "\n",
    "|zipcode |agi_stub | N1 |\n",
    "|--|--|-- |\n",
    "|63017\t |1 | 4710 |\n",
    "|63017\t |2 | 2780 |\n",
    "|63017\t |3 | 2130 |\n",
    "|63017\t |4 | 2010 |\n",
    "|63017\t |5 | 5240 |\n",
    "|63017\t |6 | 3510 |\n",
    "\n",
    "\n",
    "We must combine these six rows into one.  For privacy reasons, AGI's are broken out into 6 buckets.  We need to combine the buckets and estimate the actual AGI of a zipcode. To do this, consider the values for N1:\n",
    "\n",
    "* 1 = \\$1 to \\$25,000\n",
    "* 2 = \\$25,000 to \\$50,000\n",
    "* 3 = \\$50,000 to \\$75,000\n",
    "* 4 = \\$75,000 to \\$100,000\n",
    "* 5 = \\$100,000 to \\$200,000\n",
    "* 6 = \\$200,000 or more\n",
    "\n",
    "The median of each of these ranges is approximately:\n",
    "\n",
    "* 1 = \\$12,500\n",
    "* 2 = \\$37,500\n",
    "* 3 = \\$62,500 \n",
    "* 4 = \\$87,500\n",
    "* 5 = \\$112,500\n",
    "* 6 = \\$212,500\n",
    "\n",
    "Using this you can estimate 63017's average AGI as:\n",
    "\n",
    "```\n",
    ">>> totalCount = 4710 + 2780 + 2130 + 2010 + 5240 + 3510\n",
    ">>> totalAGI = 4710 * 12500 + 2780 * 37500 + 2130 * 62500 + 2010 * 87500 + 5240 * 112500 + 3510 * 212500\n",
    ">>> print(totalAGI / totalCount)\n",
    "\n",
    "88689.89205103042\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('https://www.irs.gov/pub/irs-soi/16zpallagi.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we trim all zipcodes that are either 0 or 99999.  We also select the three fields that we need."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.loc[(df['zipcode']!=0) & (df['zipcode']!=99999),['STATE','zipcode','agi_stub','N1']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>STATE</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>agi_stub</th>\n",
       "      <th>N1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>1</td>\n",
       "      <td>1510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>2</td>\n",
       "      <td>1410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>3</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>4</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>5</td>\n",
       "      <td>630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>1</td>\n",
       "      <td>1310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>2</td>\n",
       "      <td>960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>3</td>\n",
       "      <td>450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>4</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>5</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>1</td>\n",
       "      <td>430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>2</td>\n",
       "      <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>3</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>4</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>5</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>1</td>\n",
       "      <td>4070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>2</td>\n",
       "      <td>2650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>3</td>\n",
       "      <td>1820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>4</td>\n",
       "      <td>1340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>5</td>\n",
       "      <td>1820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>6</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>1</td>\n",
       "      <td>3620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>2</td>\n",
       "      <td>2070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>3</td>\n",
       "      <td>890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>4</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>5</td>\n",
       "      <td>620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>6</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179760</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179761</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179762</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179763</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179764</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179765</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179766</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179767</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179768</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179769</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179770</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>5</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179771</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179772</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>1</td>\n",
       "      <td>390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179773</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>2</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179774</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>3</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179775</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>4</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179776</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>5</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179777</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179778</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>1</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179779</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>2</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179780</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>3</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179781</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>4</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179782</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>5</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179783</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179784</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179785</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179786</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>3</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179787</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179788</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179789</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>6</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>179184 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATE  zipcode  agi_stub    N1\n",
       "6         AL    35004         1  1510\n",
       "7         AL    35004         2  1410\n",
       "8         AL    35004         3   950\n",
       "9         AL    35004         4   650\n",
       "10        AL    35004         5   630\n",
       "11        AL    35004         6    60\n",
       "12        AL    35005         1  1310\n",
       "13        AL    35005         2   960\n",
       "14        AL    35005         3   450\n",
       "15        AL    35005         4   200\n",
       "16        AL    35005         5   180\n",
       "17        AL    35005         6     0\n",
       "18        AL    35006         1   430\n",
       "19        AL    35006         2   330\n",
       "20        AL    35006         3   190\n",
       "21        AL    35006         4   120\n",
       "22        AL    35006         5   130\n",
       "23        AL    35006         6     0\n",
       "24        AL    35007         1  4070\n",
       "25        AL    35007         2  2650\n",
       "26        AL    35007         3  1820\n",
       "27        AL    35007         4  1340\n",
       "28        AL    35007         5  1820\n",
       "29        AL    35007         6   240\n",
       "30        AL    35010         1  3620\n",
       "31        AL    35010         2  2070\n",
       "32        AL    35010         3   890\n",
       "33        AL    35010         4   530\n",
       "34        AL    35010         5   620\n",
       "35        AL    35010         6   180\n",
       "...      ...      ...       ...   ...\n",
       "179760    WY    83123         1    90\n",
       "179761    WY    83123         2    60\n",
       "179762    WY    83123         3    30\n",
       "179763    WY    83123         4    40\n",
       "179764    WY    83123         5    60\n",
       "179765    WY    83123         6     0\n",
       "179766    WY    83126         1    70\n",
       "179767    WY    83126         2    20\n",
       "179768    WY    83126         3    30\n",
       "179769    WY    83126         4    20\n",
       "179770    WY    83126         5    30\n",
       "179771    WY    83126         6     0\n",
       "179772    WY    83127         1   390\n",
       "179773    WY    83127         2   300\n",
       "179774    WY    83127         3   280\n",
       "179775    WY    83127         4   190\n",
       "179776    WY    83127         5   250\n",
       "179777    WY    83127         6    60\n",
       "179778    WY    83128         1   250\n",
       "179779    WY    83128         2   240\n",
       "179780    WY    83128         3   170\n",
       "179781    WY    83128         4    90\n",
       "179782    WY    83128         5   110\n",
       "179783    WY    83128         6    60\n",
       "179784    WY    83414         1    50\n",
       "179785    WY    83414         2    40\n",
       "179786    WY    83414         3    40\n",
       "179787    WY    83414         4     0\n",
       "179788    WY    83414         5    40\n",
       "179789    WY    83414         6    30\n",
       "\n",
       "[179184 rows x 4 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We replace all of the **agi_stub** values with the correct median values with the **map** function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "medians = {1:12500,2:37500,3:62500,4:87500,5:112500,6:212500}\n",
    "df['agi_stub']=df.agi_stub.map(medians)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>STATE</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>agi_stub</th>\n",
       "      <th>N1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>12500</td>\n",
       "      <td>1510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>37500</td>\n",
       "      <td>1410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>62500</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>87500</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>112500</td>\n",
       "      <td>630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>212500</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>12500</td>\n",
       "      <td>1310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>37500</td>\n",
       "      <td>960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>62500</td>\n",
       "      <td>450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>87500</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>112500</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>AL</td>\n",
       "      <td>35005</td>\n",
       "      <td>212500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>12500</td>\n",
       "      <td>430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>37500</td>\n",
       "      <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>62500</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>87500</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>112500</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AL</td>\n",
       "      <td>35006</td>\n",
       "      <td>212500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>12500</td>\n",
       "      <td>4070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>37500</td>\n",
       "      <td>2650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>62500</td>\n",
       "      <td>1820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>87500</td>\n",
       "      <td>1340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>112500</td>\n",
       "      <td>1820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>212500</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>12500</td>\n",
       "      <td>3620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>37500</td>\n",
       "      <td>2070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>62500</td>\n",
       "      <td>890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>87500</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>112500</td>\n",
       "      <td>620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>212500</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179760</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>12500</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179761</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>37500</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179762</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>62500</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179763</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>87500</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179764</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>112500</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179765</th>\n",
       "      <td>WY</td>\n",
       "      <td>83123</td>\n",
       "      <td>212500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179766</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>12500</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179767</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>37500</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179768</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>62500</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179769</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>87500</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179770</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>112500</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179771</th>\n",
       "      <td>WY</td>\n",
       "      <td>83126</td>\n",
       "      <td>212500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179772</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>12500</td>\n",
       "      <td>390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179773</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>37500</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179774</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>62500</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179775</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>87500</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179776</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>112500</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179777</th>\n",
       "      <td>WY</td>\n",
       "      <td>83127</td>\n",
       "      <td>212500</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179778</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>12500</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179779</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>37500</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179780</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>62500</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179781</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>87500</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179782</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>112500</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179783</th>\n",
       "      <td>WY</td>\n",
       "      <td>83128</td>\n",
       "      <td>212500</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179784</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>12500</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179785</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>37500</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179786</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>62500</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179787</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>87500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179788</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>112500</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179789</th>\n",
       "      <td>WY</td>\n",
       "      <td>83414</td>\n",
       "      <td>212500</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>179184 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATE  zipcode  agi_stub    N1\n",
       "6         AL    35004     12500  1510\n",
       "7         AL    35004     37500  1410\n",
       "8         AL    35004     62500   950\n",
       "9         AL    35004     87500   650\n",
       "10        AL    35004    112500   630\n",
       "11        AL    35004    212500    60\n",
       "12        AL    35005     12500  1310\n",
       "13        AL    35005     37500   960\n",
       "14        AL    35005     62500   450\n",
       "15        AL    35005     87500   200\n",
       "16        AL    35005    112500   180\n",
       "17        AL    35005    212500     0\n",
       "18        AL    35006     12500   430\n",
       "19        AL    35006     37500   330\n",
       "20        AL    35006     62500   190\n",
       "21        AL    35006     87500   120\n",
       "22        AL    35006    112500   130\n",
       "23        AL    35006    212500     0\n",
       "24        AL    35007     12500  4070\n",
       "25        AL    35007     37500  2650\n",
       "26        AL    35007     62500  1820\n",
       "27        AL    35007     87500  1340\n",
       "28        AL    35007    112500  1820\n",
       "29        AL    35007    212500   240\n",
       "30        AL    35010     12500  3620\n",
       "31        AL    35010     37500  2070\n",
       "32        AL    35010     62500   890\n",
       "33        AL    35010     87500   530\n",
       "34        AL    35010    112500   620\n",
       "35        AL    35010    212500   180\n",
       "...      ...      ...       ...   ...\n",
       "179760    WY    83123     12500    90\n",
       "179761    WY    83123     37500    60\n",
       "179762    WY    83123     62500    30\n",
       "179763    WY    83123     87500    40\n",
       "179764    WY    83123    112500    60\n",
       "179765    WY    83123    212500     0\n",
       "179766    WY    83126     12500    70\n",
       "179767    WY    83126     37500    20\n",
       "179768    WY    83126     62500    30\n",
       "179769    WY    83126     87500    20\n",
       "179770    WY    83126    112500    30\n",
       "179771    WY    83126    212500     0\n",
       "179772    WY    83127     12500   390\n",
       "179773    WY    83127     37500   300\n",
       "179774    WY    83127     62500   280\n",
       "179775    WY    83127     87500   190\n",
       "179776    WY    83127    112500   250\n",
       "179777    WY    83127    212500    60\n",
       "179778    WY    83128     12500   250\n",
       "179779    WY    83128     37500   240\n",
       "179780    WY    83128     62500   170\n",
       "179781    WY    83128     87500    90\n",
       "179782    WY    83128    112500   110\n",
       "179783    WY    83128    212500    60\n",
       "179784    WY    83414     12500    50\n",
       "179785    WY    83414     37500    40\n",
       "179786    WY    83414     62500    40\n",
       "179787    WY    83414     87500     0\n",
       "179788    WY    83414    112500    40\n",
       "179789    WY    83414    212500    30\n",
       "\n",
       "[179184 rows x 4 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next the dataframe is grouped by zip code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "groups = df.groupby(by='zipcode')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A lambda is applied across the groups and the AGI estimate is calculated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(groups.apply(lambda x:sum(x['N1']*x['agi_stub'])/sum(x['N1']))).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>zipcode</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>52895.322940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>64528.451001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>15441.176471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1005</td>\n",
       "      <td>54694.092827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1007</td>\n",
       "      <td>63654.353562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1008</td>\n",
       "      <td>57575.757576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1009</td>\n",
       "      <td>45576.923077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1010</td>\n",
       "      <td>61303.191489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1011</td>\n",
       "      <td>49807.692308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1012</td>\n",
       "      <td>53214.285714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1013</td>\n",
       "      <td>40390.995261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1020</td>\n",
       "      <td>45904.746210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1022</td>\n",
       "      <td>42934.782609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1026</td>\n",
       "      <td>49744.897959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1027</td>\n",
       "      <td>53633.130081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1028</td>\n",
       "      <td>68378.787879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1029</td>\n",
       "      <td>53906.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1030</td>\n",
       "      <td>58416.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1031</td>\n",
       "      <td>44500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1032</td>\n",
       "      <td>55769.230769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1033</td>\n",
       "      <td>59094.427245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1034</td>\n",
       "      <td>62019.230769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1035</td>\n",
       "      <td>59172.661871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1036</td>\n",
       "      <td>64738.805970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1037</td>\n",
       "      <td>59294.871795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1038</td>\n",
       "      <td>62692.307692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1039</td>\n",
       "      <td>59615.384615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1040</td>\n",
       "      <td>40402.055623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1050</td>\n",
       "      <td>52616.279070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1053</td>\n",
       "      <td>59375.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29842</th>\n",
       "      <td>99763</td>\n",
       "      <td>32812.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29843</th>\n",
       "      <td>99765</td>\n",
       "      <td>27500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29844</th>\n",
       "      <td>99766</td>\n",
       "      <td>29924.242424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29845</th>\n",
       "      <td>99769</td>\n",
       "      <td>25595.238095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29846</th>\n",
       "      <td>99770</td>\n",
       "      <td>30921.052632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29847</th>\n",
       "      <td>99772</td>\n",
       "      <td>28750.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29848</th>\n",
       "      <td>99777</td>\n",
       "      <td>29807.692308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29849</th>\n",
       "      <td>99780</td>\n",
       "      <td>44778.481013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29850</th>\n",
       "      <td>99782</td>\n",
       "      <td>33189.655172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29851</th>\n",
       "      <td>99785</td>\n",
       "      <td>22500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29852</th>\n",
       "      <td>99786</td>\n",
       "      <td>31250.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29853</th>\n",
       "      <td>99789</td>\n",
       "      <td>35326.086957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29854</th>\n",
       "      <td>99791</td>\n",
       "      <td>27205.882353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29855</th>\n",
       "      <td>99801</td>\n",
       "      <td>62332.073887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29856</th>\n",
       "      <td>99820</td>\n",
       "      <td>28676.470588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29857</th>\n",
       "      <td>99824</td>\n",
       "      <td>66101.694915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29858</th>\n",
       "      <td>99826</td>\n",
       "      <td>49107.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29859</th>\n",
       "      <td>99827</td>\n",
       "      <td>47539.370079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29860</th>\n",
       "      <td>99829</td>\n",
       "      <td>40064.102564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29861</th>\n",
       "      <td>99830</td>\n",
       "      <td>37500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29862</th>\n",
       "      <td>99833</td>\n",
       "      <td>54367.469880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29863</th>\n",
       "      <td>99835</td>\n",
       "      <td>54222.972973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29864</th>\n",
       "      <td>99840</td>\n",
       "      <td>48863.636364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29865</th>\n",
       "      <td>99901</td>\n",
       "      <td>54365.079365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29866</th>\n",
       "      <td>99919</td>\n",
       "      <td>48076.923077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29867</th>\n",
       "      <td>99921</td>\n",
       "      <td>48042.168675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29868</th>\n",
       "      <td>99922</td>\n",
       "      <td>32954.545455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29869</th>\n",
       "      <td>99925</td>\n",
       "      <td>45639.534884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29870</th>\n",
       "      <td>99926</td>\n",
       "      <td>41136.363636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29871</th>\n",
       "      <td>99929</td>\n",
       "      <td>45911.214953</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>29872 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       zipcode             0\n",
       "0         1001  52895.322940\n",
       "1         1002  64528.451001\n",
       "2         1003  15441.176471\n",
       "3         1005  54694.092827\n",
       "4         1007  63654.353562\n",
       "5         1008  57575.757576\n",
       "6         1009  45576.923077\n",
       "7         1010  61303.191489\n",
       "8         1011  49807.692308\n",
       "9         1012  53214.285714\n",
       "10        1013  40390.995261\n",
       "11        1020  45904.746210\n",
       "12        1022  42934.782609\n",
       "13        1026  49744.897959\n",
       "14        1027  53633.130081\n",
       "15        1028  68378.787879\n",
       "16        1029  53906.250000\n",
       "17        1030  58416.666667\n",
       "18        1031  44500.000000\n",
       "19        1032  55769.230769\n",
       "20        1033  59094.427245\n",
       "21        1034  62019.230769\n",
       "22        1035  59172.661871\n",
       "23        1036  64738.805970\n",
       "24        1037  59294.871795\n",
       "25        1038  62692.307692\n",
       "26        1039  59615.384615\n",
       "27        1040  40402.055623\n",
       "28        1050  52616.279070\n",
       "29        1053  59375.000000\n",
       "...        ...           ...\n",
       "29842    99763  32812.500000\n",
       "29843    99765  27500.000000\n",
       "29844    99766  29924.242424\n",
       "29845    99769  25595.238095\n",
       "29846    99770  30921.052632\n",
       "29847    99772  28750.000000\n",
       "29848    99777  29807.692308\n",
       "29849    99780  44778.481013\n",
       "29850    99782  33189.655172\n",
       "29851    99785  22500.000000\n",
       "29852    99786  31250.000000\n",
       "29853    99789  35326.086957\n",
       "29854    99791  27205.882353\n",
       "29855    99801  62332.073887\n",
       "29856    99820  28676.470588\n",
       "29857    99824  66101.694915\n",
       "29858    99826  49107.142857\n",
       "29859    99827  47539.370079\n",
       "29860    99829  40064.102564\n",
       "29861    99830  37500.000000\n",
       "29862    99833  54367.469880\n",
       "29863    99835  54222.972973\n",
       "29864    99840  48863.636364\n",
       "29865    99901  54365.079365\n",
       "29866    99919  48076.923077\n",
       "29867    99921  48042.168675\n",
       "29868    99922  32954.545455\n",
       "29869    99925  45639.534884\n",
       "29870    99926  41136.363636\n",
       "29871    99929  45911.214953\n",
       "\n",
       "[29872 rows x 2 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The new agi_estimate column is renamed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = ['zipcode','agi_estimate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>zipcode</th>\n",
       "      <th>agi_estimate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>52895.322940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>64528.451001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>15441.176471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1005</td>\n",
       "      <td>54694.092827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1007</td>\n",
       "      <td>63654.353562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1008</td>\n",
       "      <td>57575.757576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1009</td>\n",
       "      <td>45576.923077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1010</td>\n",
       "      <td>61303.191489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1011</td>\n",
       "      <td>49807.692308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1012</td>\n",
       "      <td>53214.285714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   zipcode  agi_estimate\n",
       "0     1001  52895.322940\n",
       "1     1002  64528.451001\n",
       "2     1003  15441.176471\n",
       "3     1005  54694.092827\n",
       "4     1007  63654.353562\n",
       "5     1008  57575.757576\n",
       "6     1009  45576.923077\n",
       "7     1010  61303.191489\n",
       "8     1011  49807.692308\n",
       "9     1012  53214.285714"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df[0:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also see that our zipcode of 63017 gets the correct value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>zipcode</th>\n",
       "      <th>agi_estimate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19909</th>\n",
       "      <td>63017</td>\n",
       "      <td>88689.892051</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       zipcode  agi_estimate\n",
       "19909    63017  88689.892051"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[ df['zipcode']==63017 ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2.5: Feature Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Feature engineering is a very important part of machine learning.  Later in this course we will see some techniques for automatic feature engineering.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculated Fields\n",
    "\n",
    "It is possible to add new fields to the dataframe that are calculated from the other fields.  We can create a new column that gives the weight in kilograms.  The equation to calculate a metric weight, given a weight in pounds is:\n",
    "\n",
    "$ m_{(kg)} = m_{(lb)} \\times 0.45359237 $\n",
    "\n",
    "This can be used with the following Python code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>weight_kg</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>1589</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>1675</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>2</th>\n",
       "      <td>18.0</td>\n",
       "      <td>1558</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",
       "      <td>plymouth satellite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16.0</td>\n",
       "      <td>1557</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",
       "      <td>amc rebel sst</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.0</td>\n",
       "      <td>1564</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",
       "      <td>ford torino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15.0</td>\n",
       "      <td>1969</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>6</th>\n",
       "      <td>14.0</td>\n",
       "      <td>1974</td>\n",
       "      <td>8</td>\n",
       "      <td>454.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>4354</td>\n",
       "      <td>9.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet impala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>14.0</td>\n",
       "      <td>1955</td>\n",
       "      <td>8</td>\n",
       "      <td>440.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>4312</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth fury iii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14.0</td>\n",
       "      <td>2007</td>\n",
       "      <td>8</td>\n",
       "      <td>455.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>4425</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>pontiac catalina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>15.0</td>\n",
       "      <td>1746</td>\n",
       "      <td>8</td>\n",
       "      <td>390.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>3850</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc ambassador dpl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>15.0</td>\n",
       "      <td>1616</td>\n",
       "      <td>8</td>\n",
       "      <td>383.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>3563</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge challenger se</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>14.0</td>\n",
       "      <td>1637</td>\n",
       "      <td>8</td>\n",
       "      <td>340.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>3609</td>\n",
       "      <td>8.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth 'cuda 340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>15.0</td>\n",
       "      <td>1705</td>\n",
       "      <td>8</td>\n",
       "      <td>400.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>3761</td>\n",
       "      <td>9.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet monte carlo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14.0</td>\n",
       "      <td>1399</td>\n",
       "      <td>8</td>\n",
       "      <td>455.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>3086</td>\n",
       "      <td>10.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>buick estate wagon (sw)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>24.0</td>\n",
       "      <td>1075</td>\n",
       "      <td>4</td>\n",
       "      <td>113.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2372</td>\n",
       "      <td>15.0</td>\n",
       "      <td>70</td>\n",
       "      <td>3</td>\n",
       "      <td>toyota corona mark ii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>22.0</td>\n",
       "      <td>1285</td>\n",
       "      <td>6</td>\n",
       "      <td>198.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2833</td>\n",
       "      <td>15.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth duster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>18.0</td>\n",
       "      <td>1258</td>\n",
       "      <td>6</td>\n",
       "      <td>199.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>2774</td>\n",
       "      <td>15.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc hornet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>21.0</td>\n",
       "      <td>1173</td>\n",
       "      <td>6</td>\n",
       "      <td>200.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>2587</td>\n",
       "      <td>16.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford maverick</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>27.0</td>\n",
       "      <td>966</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2130</td>\n",
       "      <td>14.5</td>\n",
       "      <td>70</td>\n",
       "      <td>3</td>\n",
       "      <td>datsun pl510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>26.0</td>\n",
       "      <td>832</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>1835</td>\n",
       "      <td>20.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>volkswagen 1131 deluxe sedan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>25.0</td>\n",
       "      <td>1211</td>\n",
       "      <td>4</td>\n",
       "      <td>110.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>2672</td>\n",
       "      <td>17.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>peugeot 504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>24.0</td>\n",
       "      <td>1102</td>\n",
       "      <td>4</td>\n",
       "      <td>107.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2430</td>\n",
       "      <td>14.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>audi 100 ls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>25.0</td>\n",
       "      <td>1077</td>\n",
       "      <td>4</td>\n",
       "      <td>104.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2375</td>\n",
       "      <td>17.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>saab 99e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>26.0</td>\n",
       "      <td>1013</td>\n",
       "      <td>4</td>\n",
       "      <td>121.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>2234</td>\n",
       "      <td>12.5</td>\n",
       "      <td>70</td>\n",
       "      <td>2</td>\n",
       "      <td>bmw 2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>21.0</td>\n",
       "      <td>1201</td>\n",
       "      <td>6</td>\n",
       "      <td>199.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2648</td>\n",
       "      <td>15.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>amc gremlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10.0</td>\n",
       "      <td>2093</td>\n",
       "      <td>8</td>\n",
       "      <td>360.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>4615</td>\n",
       "      <td>14.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>ford f250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10.0</td>\n",
       "      <td>1984</td>\n",
       "      <td>8</td>\n",
       "      <td>307.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>4376</td>\n",
       "      <td>15.0</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>chevy c20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11.0</td>\n",
       "      <td>1987</td>\n",
       "      <td>8</td>\n",
       "      <td>318.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>4382</td>\n",
       "      <td>13.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge d200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>9.0</td>\n",
       "      <td>2146</td>\n",
       "      <td>8</td>\n",
       "      <td>304.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>4732</td>\n",
       "      <td>18.5</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>hi 1200d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>27.0</td>\n",
       "      <td>966</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2130</td>\n",
       "      <td>14.5</td>\n",
       "      <td>71</td>\n",
       "      <td>3</td>\n",
       "      <td>datsun pl510</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>27.0</td>\n",
       "      <td>1197</td>\n",
       "      <td>4</td>\n",
       "      <td>112.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2640</td>\n",
       "      <td>18.6</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet cavalier wagon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>34.0</td>\n",
       "      <td>1086</td>\n",
       "      <td>4</td>\n",
       "      <td>112.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2395</td>\n",
       "      <td>18.0</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet cavalier 2-door</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>370</th>\n",
       "      <td>31.0</td>\n",
       "      <td>1168</td>\n",
       "      <td>4</td>\n",
       "      <td>112.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>2575</td>\n",
       "      <td>16.2</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>pontiac j2000 se hatchback</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>371</th>\n",
       "      <td>29.0</td>\n",
       "      <td>1145</td>\n",
       "      <td>4</td>\n",
       "      <td>135.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2525</td>\n",
       "      <td>16.0</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge aries se</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>372</th>\n",
       "      <td>27.0</td>\n",
       "      <td>1240</td>\n",
       "      <td>4</td>\n",
       "      <td>151.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2735</td>\n",
       "      <td>18.0</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>pontiac phoenix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>373</th>\n",
       "      <td>24.0</td>\n",
       "      <td>1299</td>\n",
       "      <td>4</td>\n",
       "      <td>140.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>2865</td>\n",
       "      <td>16.4</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>ford fairmont futura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>374</th>\n",
       "      <td>23.0</td>\n",
       "      <td>1376</td>\n",
       "      <td>4</td>\n",
       "      <td>151.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3035</td>\n",
       "      <td>20.5</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>amc concord dl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>36.0</td>\n",
       "      <td>898</td>\n",
       "      <td>4</td>\n",
       "      <td>105.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1980</td>\n",
       "      <td>15.3</td>\n",
       "      <td>82</td>\n",
       "      <td>2</td>\n",
       "      <td>volkswagen rabbit l</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376</th>\n",
       "      <td>37.0</td>\n",
       "      <td>918</td>\n",
       "      <td>4</td>\n",
       "      <td>91.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>2025</td>\n",
       "      <td>18.2</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>mazda glc custom l</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td>31.0</td>\n",
       "      <td>893</td>\n",
       "      <td>4</td>\n",
       "      <td>91.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>1970</td>\n",
       "      <td>17.6</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>mazda glc custom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>38.0</td>\n",
       "      <td>963</td>\n",
       "      <td>4</td>\n",
       "      <td>105.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>2125</td>\n",
       "      <td>14.7</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>plymouth horizon miser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td>36.0</td>\n",
       "      <td>963</td>\n",
       "      <td>4</td>\n",
       "      <td>98.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>2125</td>\n",
       "      <td>17.3</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>mercury lynx l</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380</th>\n",
       "      <td>36.0</td>\n",
       "      <td>979</td>\n",
       "      <td>4</td>\n",
       "      <td>120.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2160</td>\n",
       "      <td>14.5</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>nissan stanza xe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td>36.0</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>107.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>2205</td>\n",
       "      <td>14.5</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>honda accord</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382</th>\n",
       "      <td>34.0</td>\n",
       "      <td>1018</td>\n",
       "      <td>4</td>\n",
       "      <td>108.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>2245</td>\n",
       "      <td>16.9</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>toyota corolla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>38.0</td>\n",
       "      <td>891</td>\n",
       "      <td>4</td>\n",
       "      <td>91.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>1965</td>\n",
       "      <td>15.0</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>honda civic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>384</th>\n",
       "      <td>32.0</td>\n",
       "      <td>891</td>\n",
       "      <td>4</td>\n",
       "      <td>91.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>1965</td>\n",
       "      <td>15.7</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>honda civic (auto)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>38.0</td>\n",
       "      <td>904</td>\n",
       "      <td>4</td>\n",
       "      <td>91.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>1995</td>\n",
       "      <td>16.2</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>datsun 310 gx</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>25.0</td>\n",
       "      <td>1335</td>\n",
       "      <td>6</td>\n",
       "      <td>181.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>2945</td>\n",
       "      <td>16.4</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>buick century limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>38.0</td>\n",
       "      <td>1367</td>\n",
       "      <td>6</td>\n",
       "      <td>262.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3015</td>\n",
       "      <td>17.0</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>oldsmobile cutlass ciera (diesel)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>26.0</td>\n",
       "      <td>1172</td>\n",
       "      <td>4</td>\n",
       "      <td>156.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>2585</td>\n",
       "      <td>14.5</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>chrysler lebaron medallion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>22.0</td>\n",
       "      <td>1285</td>\n",
       "      <td>6</td>\n",
       "      <td>232.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>2835</td>\n",
       "      <td>14.7</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>ford granada l</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>32.0</td>\n",
       "      <td>1208</td>\n",
       "      <td>4</td>\n",
       "      <td>144.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>2665</td>\n",
       "      <td>13.9</td>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>toyota celica gt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391</th>\n",
       "      <td>36.0</td>\n",
       "      <td>1075</td>\n",
       "      <td>4</td>\n",
       "      <td>135.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2370</td>\n",
       "      <td>13.0</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge charger 2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>27.0</td>\n",
       "      <td>1338</td>\n",
       "      <td>4</td>\n",
       "      <td>151.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2950</td>\n",
       "      <td>17.3</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>chevrolet camaro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>27.0</td>\n",
       "      <td>1265</td>\n",
       "      <td>4</td>\n",
       "      <td>140.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2790</td>\n",
       "      <td>15.6</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>ford mustang gl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>44.0</td>\n",
       "      <td>966</td>\n",
       "      <td>4</td>\n",
       "      <td>97.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>2130</td>\n",
       "      <td>24.6</td>\n",
       "      <td>82</td>\n",
       "      <td>2</td>\n",
       "      <td>vw pickup</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>32.0</td>\n",
       "      <td>1040</td>\n",
       "      <td>4</td>\n",
       "      <td>135.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2295</td>\n",
       "      <td>11.6</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>dodge rampage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>28.0</td>\n",
       "      <td>1190</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>1233</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",
       "<p>398 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      mpg  weight_kg  cylinders  displacement  horsepower  weight  \\\n",
       "0    18.0       1589          8         307.0       130.0    3504   \n",
       "1    15.0       1675          8         350.0       165.0    3693   \n",
       "2    18.0       1558          8         318.0       150.0    3436   \n",
       "3    16.0       1557          8         304.0       150.0    3433   \n",
       "4    17.0       1564          8         302.0       140.0    3449   \n",
       "5    15.0       1969          8         429.0       198.0    4341   \n",
       "6    14.0       1974          8         454.0       220.0    4354   \n",
       "7    14.0       1955          8         440.0       215.0    4312   \n",
       "8    14.0       2007          8         455.0       225.0    4425   \n",
       "9    15.0       1746          8         390.0       190.0    3850   \n",
       "10   15.0       1616          8         383.0       170.0    3563   \n",
       "11   14.0       1637          8         340.0       160.0    3609   \n",
       "12   15.0       1705          8         400.0       150.0    3761   \n",
       "13   14.0       1399          8         455.0       225.0    3086   \n",
       "14   24.0       1075          4         113.0        95.0    2372   \n",
       "15   22.0       1285          6         198.0        95.0    2833   \n",
       "16   18.0       1258          6         199.0        97.0    2774   \n",
       "17   21.0       1173          6         200.0        85.0    2587   \n",
       "18   27.0        966          4          97.0        88.0    2130   \n",
       "19   26.0        832          4          97.0        46.0    1835   \n",
       "20   25.0       1211          4         110.0        87.0    2672   \n",
       "21   24.0       1102          4         107.0        90.0    2430   \n",
       "22   25.0       1077          4         104.0        95.0    2375   \n",
       "23   26.0       1013          4         121.0       113.0    2234   \n",
       "24   21.0       1201          6         199.0        90.0    2648   \n",
       "25   10.0       2093          8         360.0       215.0    4615   \n",
       "26   10.0       1984          8         307.0       200.0    4376   \n",
       "27   11.0       1987          8         318.0       210.0    4382   \n",
       "28    9.0       2146          8         304.0       193.0    4732   \n",
       "29   27.0        966          4          97.0        88.0    2130   \n",
       "..    ...        ...        ...           ...         ...     ...   \n",
       "368  27.0       1197          4         112.0        88.0    2640   \n",
       "369  34.0       1086          4         112.0        88.0    2395   \n",
       "370  31.0       1168          4         112.0        85.0    2575   \n",
       "371  29.0       1145          4         135.0        84.0    2525   \n",
       "372  27.0       1240          4         151.0        90.0    2735   \n",
       "373  24.0       1299          4         140.0        92.0    2865   \n",
       "374  23.0       1376          4         151.0         NaN    3035   \n",
       "375  36.0        898          4         105.0        74.0    1980   \n",
       "376  37.0        918          4          91.0        68.0    2025   \n",
       "377  31.0        893          4          91.0        68.0    1970   \n",
       "378  38.0        963          4         105.0        63.0    2125   \n",
       "379  36.0        963          4          98.0        70.0    2125   \n",
       "380  36.0        979          4         120.0        88.0    2160   \n",
       "381  36.0       1000          4         107.0        75.0    2205   \n",
       "382  34.0       1018          4         108.0        70.0    2245   \n",
       "383  38.0        891          4          91.0        67.0    1965   \n",
       "384  32.0        891          4          91.0        67.0    1965   \n",
       "385  38.0        904          4          91.0        67.0    1995   \n",
       "386  25.0       1335          6         181.0       110.0    2945   \n",
       "387  38.0       1367          6         262.0        85.0    3015   \n",
       "388  26.0       1172          4         156.0        92.0    2585   \n",
       "389  22.0       1285          6         232.0       112.0    2835   \n",
       "390  32.0       1208          4         144.0        96.0    2665   \n",
       "391  36.0       1075          4         135.0        84.0    2370   \n",
       "392  27.0       1338          4         151.0        90.0    2950   \n",
       "393  27.0       1265          4         140.0        86.0    2790   \n",
       "394  44.0        966          4          97.0        52.0    2130   \n",
       "395  32.0       1040          4         135.0        84.0    2295   \n",
       "396  28.0       1190          4         120.0        79.0    2625   \n",
       "397  31.0       1233          4         119.0        82.0    2720   \n",
       "\n",
       "     acceleration  year  origin                               name  \n",
       "0            12.0    70       1          chevrolet chevelle malibu  \n",
       "1            11.5    70       1                  buick skylark 320  \n",
       "2            11.0    70       1                 plymouth satellite  \n",
       "3            12.0    70       1                      amc rebel sst  \n",
       "4            10.5    70       1                        ford torino  \n",
       "5            10.0    70       1                   ford galaxie 500  \n",
       "6             9.0    70       1                   chevrolet impala  \n",
       "7             8.5    70       1                  plymouth fury iii  \n",
       "8            10.0    70       1                   pontiac catalina  \n",
       "9             8.5    70       1                 amc ambassador dpl  \n",
       "10           10.0    70       1                dodge challenger se  \n",
       "11            8.0    70       1                 plymouth 'cuda 340  \n",
       "12            9.5    70       1              chevrolet monte carlo  \n",
       "13           10.0    70       1            buick estate wagon (sw)  \n",
       "14           15.0    70       3              toyota corona mark ii  \n",
       "15           15.5    70       1                    plymouth duster  \n",
       "16           15.5    70       1                         amc hornet  \n",
       "17           16.0    70       1                      ford maverick  \n",
       "18           14.5    70       3                       datsun pl510  \n",
       "19           20.5    70       2       volkswagen 1131 deluxe sedan  \n",
       "20           17.5    70       2                        peugeot 504  \n",
       "21           14.5    70       2                        audi 100 ls  \n",
       "22           17.5    70       2                           saab 99e  \n",
       "23           12.5    70       2                           bmw 2002  \n",
       "24           15.0    70       1                        amc gremlin  \n",
       "25           14.0    70       1                          ford f250  \n",
       "26           15.0    70       1                          chevy c20  \n",
       "27           13.5    70       1                         dodge d200  \n",
       "28           18.5    70       1                           hi 1200d  \n",
       "29           14.5    71       3                       datsun pl510  \n",
       "..            ...   ...     ...                                ...  \n",
       "368          18.6    82       1           chevrolet cavalier wagon  \n",
       "369          18.0    82       1          chevrolet cavalier 2-door  \n",
       "370          16.2    82       1         pontiac j2000 se hatchback  \n",
       "371          16.0    82       1                     dodge aries se  \n",
       "372          18.0    82       1                    pontiac phoenix  \n",
       "373          16.4    82       1               ford fairmont futura  \n",
       "374          20.5    82       1                     amc concord dl  \n",
       "375          15.3    82       2                volkswagen rabbit l  \n",
       "376          18.2    82       3                 mazda glc custom l  \n",
       "377          17.6    82       3                   mazda glc custom  \n",
       "378          14.7    82       1             plymouth horizon miser  \n",
       "379          17.3    82       1                     mercury lynx l  \n",
       "380          14.5    82       3                   nissan stanza xe  \n",
       "381          14.5    82       3                       honda accord  \n",
       "382          16.9    82       3                     toyota corolla  \n",
       "383          15.0    82       3                        honda civic  \n",
       "384          15.7    82       3                 honda civic (auto)  \n",
       "385          16.2    82       3                      datsun 310 gx  \n",
       "386          16.4    82       1              buick century limited  \n",
       "387          17.0    82       1  oldsmobile cutlass ciera (diesel)  \n",
       "388          14.5    82       1         chrysler lebaron medallion  \n",
       "389          14.7    82       1                     ford granada l  \n",
       "390          13.9    82       3                   toyota celica gt  \n",
       "391          13.0    82       1                  dodge charger 2.2  \n",
       "392          17.3    82       1                   chevrolet camaro  \n",
       "393          15.6    82       1                    ford mustang gl  \n",
       "394          24.6    82       2                          vw pickup  \n",
       "395          11.6    82       1                      dodge rampage  \n",
       "396          18.6    82       1                        ford ranger  \n",
       "397          19.4    82       1                         chevy s-10  \n",
       "\n",
       "[398 rows x 10 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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",
    "df.insert(1, 'weight_kg', (df['weight'] * 0.45359237).astype(int))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Google API Keys\n",
    "\n",
    "Sometimes you will use external API's to obtain data.  The following examples show how to use the Google API keys to encode addresses for use with neural networks.  To use these, you will need your own Google API key.  The key I have below is not a real key, you need to put your own in there.  Google will ask for a credit card, but unless you use a very large number of lookups, there will be no actual cost.  YOU ARE NOT required to get an Google API key for this class, this only shows you how.  If you would like to get a Google API key, visit this site and obtain one for **geocode**.\n",
    "\n",
    "[Google API Keys](https://developers.google.com/maps/documentation/embed/get-api-key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "GOOGLE_KEY = 'INSERT_YOUR_KEY'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Other Examples: Dealing with Addresses\n",
    "\n",
    "Addresses can be difficult to encode into a neural network.  There are many different approaches, and you must consider how you can transform the address into something more meaningful.  Map coordinates can be a good approach.  [Latitude and longitude](https://en.wikipedia.org/wiki/Geographic_coordinate_system) can be a useful encoding.  Thanks to the power of the Internet, it is relatively easy to transform an address into its latitude and longitude values.  The following code determines the coordinates of [Washington University](https://wustl.edu/):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'lat': 38.648238, 'lng': -90.30487459999999}\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "address = \"1 Brookings Dr, St. Louis, MO 63130\"\n",
    "\n",
    "response = requests.get('https://maps.googleapis.com/maps/api/geocode/json?key={}&address={}'.format(GOOGLE_KEY,address))\n",
    "\n",
    "resp_json_payload = response.json()\n",
    "\n",
    "if 'error_message' in resp_json_payload:\n",
    "    print(resp_json_payload['error_message'])\n",
    "else:\n",
    "    print(resp_json_payload['results'][0]['geometry']['location'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If latitude and longitude are simply fed into the neural network as two features, they might not be overly helpful.  These two values would allow your neural network to cluster locations on a map.  Sometimes cluster locations on a map can be useful.  Consider the percentage of the population that smokes in the USA by state:\n",
    "\n",
    "![Smokers by State](https://raw.githubusercontent.com/jeffheaton/t81_558_deep_learning/master/images/class_6_smokers.png \"Smokers by State\")\n",
    "\n",
    "The above map shows that certain behaviors, like smoking, can be clustered by global region. \n",
    "\n",
    "However, often you will want to transform the coordinates into distances.  It is reasonably easy to estimate the distance between any two points on Earth by using the [great circle distance](https://en.wikipedia.org/wiki/Great-circle_distance) between any two points on a sphere:\n",
    "\n",
    "The following code implements this formula:\n",
    "\n",
    "$\\Delta\\sigma=\\arccos\\bigl(\\sin\\phi_1\\cdot\\sin\\phi_2+\\cos\\phi_1\\cdot\\cos\\phi_2\\cdot\\cos(\\Delta\\lambda)\\bigr)$\n",
    "\n",
    "$d = r \\, \\Delta\\sigma$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Distance, St. Louis, MO to Ft. Lauderdale, FL: 1684.9161446533758 km\n"
     ]
    }
   ],
   "source": [
    "from math import sin, cos, sqrt, atan2, radians\n",
    "\n",
    "# Distance function\n",
    "def distance_lat_lng(lat1,lng1,lat2,lng2):\n",
    "    # approximate radius of earth in km\n",
    "    R = 6373.0\n",
    "\n",
    "    # degrees to radians (lat/lon are in degrees)\n",
    "    lat1 = radians(lat1)\n",
    "    lng1 = radians(lng1)\n",
    "    lat2 = radians(lat2)\n",
    "    lng2 = radians(lng2)\n",
    "\n",
    "    dlng = lng2 - lng1\n",
    "    dlat = lat2 - lat1\n",
    "\n",
    "    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlng / 2)**2\n",
    "    c = 2 * atan2(sqrt(a), sqrt(1 - a))\n",
    "\n",
    "    return R * c\n",
    "\n",
    "# Find lat lon for address\n",
    "def lookup_lat_lng(address):\n",
    "    response = requests.get('https://maps.googleapis.com/maps/api/geocode/json?key={}&address={}'.format(GOOGLE_KEY,address))\n",
    "    json = response.json()\n",
    "    if len(json['results']) == 0:\n",
    "        print(\"Can't find: {}\".format(address))\n",
    "        return 0,0\n",
    "    map = json['results'][0]['geometry']['location']\n",
    "    return map['lat'],map['lng']\n",
    "\n",
    "\n",
    "# Distance between two locations\n",
    "\n",
    "import requests\n",
    "\n",
    "address1 = \"1 Brookings Dr, St. Louis, MO 63130\" \n",
    "address2 = \"3301 College Ave, Fort Lauderdale, FL 33314\"\n",
    "\n",
    "lat1, lng1 = lookup_lat_lng(address1)\n",
    "lat2, lng2 = lookup_lat_lng(address2)\n",
    "\n",
    "print(\"Distance, St. Louis, MO to Ft. Lauderdale, FL: {} km\".format(\n",
    "        distance_lat_lng(lat1,lng1,lat2,lng2)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Distances can be useful to encode addresses as.  You must consider what distance might be useful for your dataset.  Consider:\n",
    "\n",
    "* Distance to major metropolitan area\n",
    "* Distance to competitor\n",
    "* Distance to distribution center\n",
    "* Distance to retail outlet\n",
    "\n",
    "The following code calculates the distance between 10 universities and washu:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "School 'Princeton', distance to wustl is: 1354.4748428037537\n",
      "School 'Harvard', distance to wustl is: 1670.6348910867227\n",
      "School 'University of Chicago', distance to wustl is: 418.07123096093096\n",
      "School 'Yale', distance to wustl is: 1508.209168740192\n",
      "School 'Columbia University', distance to wustl is: 1418.2846378506144\n",
      "School 'Stanford', distance to wustl is: 2780.6884662205066\n",
      "School 'MIT', distance to wustl is: 1672.4354422735219\n",
      "School 'Duke University', distance to wustl is: 1046.7924543575177\n",
      "School 'University of Pennsylvania', distance to wustl is: 1307.1873732319766\n",
      "School 'Johns Hopkins', distance to wustl is: 1184.3754484499111\n"
     ]
    }
   ],
   "source": [
    "# Encoding other universities by their distance to Washington University\n",
    "\n",
    "schools = [\n",
    "    [\"Princeton University, Princeton, NJ 08544\", 'Princeton'],\n",
    "    [\"Massachusetts Hall, Cambridge, MA 02138\", 'Harvard'],\n",
    "    [\"5801 S Ellis Ave, Chicago, IL 60637\", 'University of Chicago'],\n",
    "    [\"Yale, New Haven, CT 06520\", 'Yale'],\n",
    "    [\"116th St & Broadway, New York, NY 10027\", 'Columbia University'],\n",
    "    [\"450 Serra Mall, Stanford, CA 94305\", 'Stanford'],\n",
    "    [\"77 Massachusetts Ave, Cambridge, MA 02139\", 'MIT'],\n",
    "    [\"Duke University, Durham, NC 27708\", 'Duke University'],\n",
    "    [\"University of Pennsylvania, Philadelphia, PA 19104\", 'University of Pennsylvania'],\n",
    "    [\"Johns Hopkins University, Baltimore, MD 21218\", 'Johns Hopkins']\n",
    "]\n",
    "\n",
    "lat1, lng1 = lookup_lat_lng(\"1 Brookings Dr, St. Louis, MO 63130\")\n",
    "\n",
    "for address, name in schools:\n",
    "    lat2,lng2 = lookup_lat_lng(address)\n",
    "    dist = distance_lat_lng(lat1,lng1,lat2,lng2)\n",
    "    print(\"School '{}', distance to wustl is: {}\".format(name,dist))"
   ]
  },
  {
   "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",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}