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": [
    "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": 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": "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",
    "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": 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": [
    "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": 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 zipcodes 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 the dataframe is grouped by zip code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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": 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": [
    "The new agi_estimate column is renamed."
   ]
  },
  {
   "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": [
    "We can also see that our zipcode of 63017 gets 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
}