t81_558_class_02_4_pandas_functional.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/jeffheaton/t81_558_deep_learning/blob/master/t81_558_class_02_4_pandas_functional.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# T81-558: Applications of Deep Neural Networks\n",
    "**Module 2: Python for Machine Learning**\n",
    "* Instructor: [Jeff Heaton](https://sites.wustl.edu/jeffheaton/), McKelvey School of Engineering, [Washington University in St. Louis](https://engineering.wustl.edu/Programs/Pages/default.aspx)\n",
    "* For more information visit the [class website](https://sites.wustl.edu/jeffheaton/t81-558/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module 2 Material\n",
    "\n",
    "Main video lecture:\n",
    "\n",
    "* Part 2.1: Introduction to Pandas [[Video]](https://www.youtube.com/watch?v=bN4UuCBdpZc&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_1_python_pandas.ipynb)\n",
    "* Part 2.2: Categorical Values [[Video]](https://www.youtube.com/watch?v=4a1odDpG0Ho&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_2_pandas_cat.ipynb)\n",
    "* Part 2.3: Grouping, Sorting, and Shuffling in Python Pandas [[Video]](https://www.youtube.com/watch?v=YS4wm5gD8DM&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_3_pandas_grouping.ipynb)\n",
    "* **Part 2.4: Using Apply and Map in Pandas for Keras** [[Video]](https://www.youtube.com/watch?v=XNCEZ4WaPBY&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_4_pandas_functional.ipynb)\n",
    "* Part 2.5: Feature Engineering in Pandas for Deep Learning in Keras [[Video]](https://www.youtube.com/watch?v=BWPTj4_Mi9E&list=PLjy4p-07OYzulelvJ5KVaT2pDlxivl_BN) [[Notebook]](t81_558_class_02_5_pandas_features.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Google CoLab Instructions\n",
    "\n",
    "The following code ensures that Google CoLab is running the correct version of TensorFlow."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Note: not using Google CoLab\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    %tensorflow_version 2.x\n",
    "    COLAB = True\n",
    "    print(\"Note: using Google CoLab\")\n",
    "except:\n",
    "    print(\"Note: not using Google CoLab\")\n",
    "    COLAB = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2.4: Apply and Map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you've ever worked with Big Data or functional programming languages before, you've likely heard of map/reduce. Map and reduce are two functions that apply a task that you create to a data frame.  Pandas supports functional programming techniques that allow you to use functions across en entire data frame.  In addition to functions that you write, Pandas also provides several standard functions for use with data frames."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Map with Dataframes\n",
    "\n",
    "The map function allows you to transform a column by mapping certain values in that column to other values.  Consider the Auto MPG data set that contains a field **origin_name** that holds a value between one and three that indicates the geographic origin of each car.  We can see how to use the map function to transform this numeric origin into the textual name of each origin.\n",
    "\n",
    "We will begin by loading the Auto MPG data set. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>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": "markdown",
   "metadata": {},
   "source": [
    "The **map** method in Pandas operates on a single column.  You provide **map** with a dictionary of values to transform the target column.  The map keys specify what values in the target column should be turned into values specified by those keys.  The following code shows how the map function can transform the numeric values of 1, 2, and 3 into the string values of North America, Europe and Asia."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mpg</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>displacement</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>weight</th>\n",
       "      <th>acceleration</th>\n",
       "      <th>year</th>\n",
       "      <th>origin</th>\n",
       "      <th>name</th>\n",
       "      <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",
    "The **apply** function of the data frame can run a function over the entire data frame.  You can use either be a traditional named function or a lambda function. Python will execute the provided function against each of the rows or columns in the data frame.  The **axis** parameter specifies of the function is run across rows or columns.  For axis = 1, rows are used. The following code calculates a series called **efficiency** that is the **displacement** divided by **horsepower**. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": [
    "efficiency = df.apply(lambda x: x['displacement']/x['horsepower'], axis=1)\n",
    "display(efficiency[0:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now insert this series into the data frame, either as a new column or to replace an existing column.  The following code inserts this new series into the data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['efficiency'] = efficiency"
   ]
  },
  {
   "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 URL contains US Government public data for \"SOI Tax Stats - Individual Income Tax Statistics.\"  The entry point to the 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 zip codes.  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 six rows for each zip code, for each of the agi_stub brackets. You can skip zip codes 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 six rows are present for 63017:\n",
    "\n",
    "\n",
    "|zipcode |agi_stub | N1 |\n",
    "|--|--|-- |\n",
    "|63017     |1 | 4710 |\n",
    "|63017     |2 | 2780 |\n",
    "|63017     |3 | 2130 |\n",
    "|63017     |4 | 2010 |\n",
    "|63017     |5 | 5240 |\n",
    "|63017     |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",
    "```\n",
    "\n",
    "We begin by reading in the government data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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 zip codes that are either 0 or 99999.  We also select the three fields that we need."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.loc[(df['zipcode']!=0) & (df['zipcode']!=99999),['STATE','zipcode','agi_stub','N1']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</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",
       "...      ...      ...       ...   ...\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": 7,
     "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": 8,
   "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": 9,
   "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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</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",
       "...      ...      ...       ...   ...\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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we group the data frame by zip code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "groups = df.groupby(by='zipcode')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The program applies a lambda is applied across the groups, and then calculates the AGI estimate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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": 12,
   "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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</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",
       "...        ...           ...\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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now rename the new agi_estimate column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = ['zipcode','agi_estimate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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": [
    "Finally, we check to see that our zip code of 63017 got the correct value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[ df['zipcode']==63017 ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3.6 (tensorflow)",
   "language": "python",
   "name": "rga"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}