t81_558_class_02_4_pandas_functional.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.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": 1,
   "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": 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",
       "      <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": 3,
   "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": 4,
   "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": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.loc[(df['zipcode']!=0) & (df['zipcode']!=99999),['STATE','zipcode','agi_stub','N1']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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",
       "...      ...      ...       ...   ...\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": 6,
     "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": 7,
   "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": 8,
   "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",
       "...      ...      ...       ...   ...\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": 8,
     "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": 9,
   "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": 10,
   "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": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "...        ...           ...\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": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The new agi_estimate column is renamed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = ['zipcode','agi_estimate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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": 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>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": 14,
     "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}