Practice4 - Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning in Python\n",
    "The examples and code in this notebook are taken from the [Kaggle Data Cleaning Challenge](https://www.kaggle.com/getting-started/52652)\n",
    "\n",
    "Detailed explanations for important code snippets are provided by Mervat Abuelkheir as part of the CSEN1095 Data Engineering Course.\n",
    "\n",
    "The goal of this notebook is to show how to handle missing values and noisy data.\n",
    "\n",
    "Pay attention to the <span style=\"color:red\"> <b> paragraphs in bold red</b></span>; they ask you to do something and provide input!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inspecting the data\n",
    "\n",
    "First thing we need to do is import a dataset.\n",
    "\n",
    "<br>We will work in this exercise with the <a href=\"https://www.kaggle.com/maxhorowitz/nflplaybyplay2009to2016#NFL%20Play%20by%20Play%202009-2017%20(v4).csv\"> NFL dataset</a>. The NFL dataset is available in the repository's data folder, and is made available on Kaggle. It contains all the regular season plays from the 2009-2016 NFL seasons. The dataset has 100 columns. Each play contains information on: game situation, players involved, results, and advanced metrics such as expected point and win probability values.\n",
    "\n",
    "### Important note\n",
    "\n",
    "<b>The NFL dataset is very big (200+ MB) and is not made available in the `data` folder in this repository. Download the dataset from the link provided, store it in your own data or working directory, and proceed with running the code below, taking care to change your directory according to your own data storage location.</b>\n",
    "\n",
    "<b>For large datasets, you will be provided with a link through which you can download the data. Another option is to read from the html link, provided the data is well-organized to help with proper parsing.</b>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ahmed/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3057: DtypeWarning: Columns (25,51) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "# modules we'll use\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# read in all our data\n",
    "nfl_data = pd.read_csv(\"data/NFL Play by Play 2009-2017 (v4).csv\")\n",
    "\n",
    "# set seed for reproducibility\n",
    "np.random.seed(0) \n"
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>GameID</th>\n",
       "      <th>Drive</th>\n",
       "      <th>qtr</th>\n",
       "      <th>down</th>\n",
       "      <th>time</th>\n",
       "      <th>TimeUnder</th>\n",
       "      <th>TimeSecs</th>\n",
       "      <th>PlayTimeDiff</th>\n",
       "      <th>SideofField</th>\n",
       "      <th>...</th>\n",
       "      <th>yacEPA</th>\n",
       "      <th>Home_WP_pre</th>\n",
       "      <th>Away_WP_pre</th>\n",
       "      <th>Home_WP_post</th>\n",
       "      <th>Away_WP_post</th>\n",
       "      <th>Win_Prob</th>\n",
       "      <th>WPA</th>\n",
       "      <th>airWPA</th>\n",
       "      <th>yacWPA</th>\n",
       "      <th>Season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>244485</th>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>2014102607</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>00:39</td>\n",
       "      <td>1</td>\n",
       "      <td>939.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>TB</td>\n",
       "      <td>...</td>\n",
       "      <td>1.240299</td>\n",
       "      <td>0.225647</td>\n",
       "      <td>0.774353</td>\n",
       "      <td>0.245582</td>\n",
       "      <td>0.754418</td>\n",
       "      <td>0.225647</td>\n",
       "      <td>0.019935</td>\n",
       "      <td>-0.018156</td>\n",
       "      <td>0.038091</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115340</th>\n",
       "      <td>2011-11-20</td>\n",
       "      <td>2011112000</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>06:47</td>\n",
       "      <td>7</td>\n",
       "      <td>407.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>OAK</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.056036</td>\n",
       "      <td>0.943964</td>\n",
       "      <td>0.042963</td>\n",
       "      <td>0.957037</td>\n",
       "      <td>0.943964</td>\n",
       "      <td>0.013073</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68357</th>\n",
       "      <td>2010-11-14</td>\n",
       "      <td>2010111401</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00:23</td>\n",
       "      <td>1</td>\n",
       "      <td>1823.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.365307</td>\n",
       "      <td>0.634693</td>\n",
       "      <td>0.384697</td>\n",
       "      <td>0.615303</td>\n",
       "      <td>0.634693</td>\n",
       "      <td>-0.019390</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368377</th>\n",
       "      <td>2017-09-24</td>\n",
       "      <td>2017092405</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>08:48</td>\n",
       "      <td>9</td>\n",
       "      <td>528.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>CLE</td>\n",
       "      <td>...</td>\n",
       "      <td>1.075660</td>\n",
       "      <td>0.935995</td>\n",
       "      <td>0.064005</td>\n",
       "      <td>0.921231</td>\n",
       "      <td>0.078769</td>\n",
       "      <td>0.064005</td>\n",
       "      <td>0.014764</td>\n",
       "      <td>0.003866</td>\n",
       "      <td>0.010899</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 102 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Date      GameID  Drive  qtr  down   time  TimeUnder  TimeSecs  \\\n",
       "244485  2014-10-26  2014102607     18    3   1.0  00:39          1     939.0   \n",
       "115340  2011-11-20  2011112000     22    4   1.0  06:47          7     407.0   \n",
       "68357   2010-11-14  2010111401      8    2   NaN  00:23          1    1823.0   \n",
       "368377  2017-09-24  2017092405     24    4   1.0  08:48          9     528.0   \n",
       "\n",
       "        PlayTimeDiff SideofField  ...    yacEPA  Home_WP_pre  Away_WP_pre  \\\n",
       "244485          12.0          TB  ...  1.240299     0.225647     0.774353   \n",
       "115340          44.0         OAK  ...       NaN     0.056036     0.943964   \n",
       "68357            0.0         CLE  ...       NaN     0.365307     0.634693   \n",
       "368377           8.0         CLE  ...  1.075660     0.935995     0.064005   \n",
       "\n",
       "        Home_WP_post  Away_WP_post  Win_Prob       WPA    airWPA    yacWPA  \\\n",
       "244485      0.245582      0.754418  0.225647  0.019935 -0.018156  0.038091   \n",
       "115340      0.042963      0.957037  0.943964  0.013073       NaN       NaN   \n",
       "68357       0.384697      0.615303  0.634693 -0.019390       NaN       NaN   \n",
       "368377      0.921231      0.078769  0.064005  0.014764  0.003866  0.010899   \n",
       "\n",
       "        Season  \n",
       "244485    2014  \n",
       "115340    2011  \n",
       "68357     2010  \n",
       "368377    2017  \n",
       "\n",
       "[4 rows x 102 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Look at a few rows of the nfl_data file. A handful of missing data!\n",
    "nfl_data.sample(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## See how many missing data points we have\n",
    "\n",
    "Let's see how many we have in each column. Let's start with the NFL dataset. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date                                0\n",
      "GameID                              0\n",
      "Drive                               0\n",
      "qtr                                 0\n",
      "down                            61154\n",
      "time                              224\n",
      "TimeUnder                           0\n",
      "TimeSecs                          224\n",
      "PlayTimeDiff                      444\n",
      "SideofField                       528\n",
      "yrdln                             840\n",
      "yrdline100                        840\n",
      "ydstogo                             0\n",
      "ydsnet                              0\n",
      "GoalToGo                          840\n",
      "FirstDown                       28811\n",
      "posteam                         24992\n",
      "DefensiveTeam                   24992\n",
      "desc                                2\n",
      "PlayAttempted                       0\n",
      "Yards.Gained                        0\n",
      "sp                                  0\n",
      "Touchdown                           0\n",
      "ExPointResult                  397578\n",
      "TwoPointConv                   407083\n",
      "DefTwoPoint                    407664\n",
      "Safety                              0\n",
      "Onsidekick                          0\n",
      "PuntResult                     385317\n",
      "PlayType                            0\n",
      "                                ...  \n",
      "AwayTeam                            0\n",
      "Timeout_Indicator                   0\n",
      "Timeout_Team                        0\n",
      "posteam_timeouts_pre                0\n",
      "HomeTimeouts_Remaining_Pre          0\n",
      "AwayTimeouts_Remaining_Pre          0\n",
      "HomeTimeouts_Remaining_Post         0\n",
      "AwayTimeouts_Remaining_Post         0\n",
      "No_Score_Prob                     176\n",
      "Opp_Field_Goal_Prob               176\n",
      "Opp_Safety_Prob                   176\n",
      "Opp_Touchdown_Prob                176\n",
      "Field_Goal_Prob                   176\n",
      "Safety_Prob                       176\n",
      "Touchdown_Prob                    176\n",
      "ExPoint_Prob                        0\n",
      "TwoPoint_Prob                       0\n",
      "ExpPts                            176\n",
      "EPA                               369\n",
      "airEPA                         248394\n",
      "yacEPA                         248498\n",
      "Home_WP_pre                     24954\n",
      "Away_WP_pre                     24954\n",
      "Home_WP_post                    26587\n",
      "Away_WP_post                    26587\n",
      "Win_Prob                        25009\n",
      "WPA                              5541\n",
      "airWPA                         248501\n",
      "yacWPA                         248762\n",
      "Season                              0\n",
      "Length: 102, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# get the number of missing data points per column\n",
    "missing_values_count_nfl = nfl_data.isnull().sum()\n",
    "\n",
    "# look at the # of missing points in the first ten columns\n",
    "print(missing_values_count_nfl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a lot of missing values it seems! Let's take a look at the percentage of the missing values in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "24.87214126835169\n"
     ]
    }
   ],
   "source": [
    "# How many total missing values do we have?\n",
    "# shape returns the dimentionality of a dataframe (rows and columns), can you guess what product will do?\n",
    "total_cells_nfl = np.product(nfl_data.shape) \n",
    "total_missing_nfl = missing_values_count_nfl.sum()\n",
    "\n",
    "# percent of data that is missing\n",
    "percentage_missign_values_nfl = (total_missing_nfl/total_cells_nfl) * 100\n",
    "print(percentage_missign_values_nfl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Almost a quarter of the cells in this dataset are empty! \n",
    "\n",
    "In the next step, we're going to take a closer look at some of the columns with missing values and try to figure out what might be going on with them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examining the missing values\n",
    "\n",
    "This is the point where you should develop some data intuition and look at the data to try and figure out why it is the way it is and how that will affect your analysis. For dealing with missing values, you'll need to use your intution to figure out why the value is missing. One of the most important question you can ask yourself to help figure this out is this:\n",
    "\n",
    "**Is this value missing becuase it wasn't recorded or becuase it dosen't exist?**\n",
    "\n",
    "If a value is missing becuase it doens't exist (like the height of the oldest child of someone who doesn't have any children) then it doesn't make sense to try and guess what it might be. These values you probably do want to keep as `NaN`. On the other hand, if a value is missing becuase it wasn't recorded, then you can try to guess what it might have been based on the other values in that column and row. (This is called \"imputation\" and we'll learn how to do it next! :)\n",
    "\n",
    "Let's work through an example. Looking at the number of missing values in the nfl_data dataframe, notice that the column TimesSec has a lot of missing values in it: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date                0\n",
       "GameID              0\n",
       "Drive               0\n",
       "qtr                 0\n",
       "down            61154\n",
       "time              224\n",
       "TimeUnder           0\n",
       "TimeSecs          224\n",
       "PlayTimeDiff      444\n",
       "SideofField       528\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# look at the # of missing points in the first ten columns\n",
    "missing_values_count_nfl[0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `TimeSecs` column has information on the number of seconds left in the game when the play was made. This means that these values are probably missing because they were not recorded, rather than because they don't exist. So, it would make sense for us to try and guess what they should be rather than just leaving them as NA's.\n",
    "\n",
    "On the other hand, there are other fields, like `PenalizedTeam` that also have lot of missing fields. In this case, though, the field is missing because if there was no penalty then it doesn't make sense to say which team was penalized. For this column, it would make more sense to either leave it empty or to add a third value like \"neither\" and use that to replace the NA's.\n",
    "\n",
    "**Tip: This is a great place to read over the dataset documentation if you haven't already! If you're working with a dataset that you've gotten from another person, you can also try reaching out to them to get more information.**\n",
    "\n",
    "You should look at each column individually to figure out the best strategy for filling those missing values. For the rest of this notebook, we'll cover some \"quick and dirty\" techniques that can help you with missing values but will probably also end up removing some useful information or adding some noise to your data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dropping the missing values\n",
    "\n",
    "If you don't have a reason to figure out why your values are missing, one option you have is to just remove any rows or columns that contain missing values. (Note: DO NOT do this approch for important projects! It's usually worth it to take the time to go through your data and really look at all the columns with missing values one-by-one to really get to know your dataset.)  \n",
    "\n",
    "If you're sure you want to drop rows with missing values, pandas does have a handy function, `dropna()` to help you do this. Let's try it out on our NFL dataset!"
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>GameID</th>\n",
       "      <th>Drive</th>\n",
       "      <th>qtr</th>\n",
       "      <th>down</th>\n",
       "      <th>time</th>\n",
       "      <th>TimeUnder</th>\n",
       "      <th>TimeSecs</th>\n",
       "      <th>PlayTimeDiff</th>\n",
       "      <th>SideofField</th>\n",
       "      <th>...</th>\n",
       "      <th>yacEPA</th>\n",
       "      <th>Home_WP_pre</th>\n",
       "      <th>Away_WP_pre</th>\n",
       "      <th>Home_WP_post</th>\n",
       "      <th>Away_WP_post</th>\n",
       "      <th>Win_Prob</th>\n",
       "      <th>WPA</th>\n",
       "      <th>airWPA</th>\n",
       "      <th>yacWPA</th>\n",
       "      <th>Season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 102 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Date, GameID, Drive, qtr, down, time, TimeUnder, TimeSecs, PlayTimeDiff, SideofField, yrdln, yrdline100, ydstogo, ydsnet, GoalToGo, FirstDown, posteam, DefensiveTeam, desc, PlayAttempted, Yards.Gained, sp, Touchdown, ExPointResult, TwoPointConv, DefTwoPoint, Safety, Onsidekick, PuntResult, PlayType, Passer, Passer_ID, PassAttempt, PassOutcome, PassLength, AirYards, YardsAfterCatch, QBHit, PassLocation, InterceptionThrown, Interceptor, Rusher, Rusher_ID, RushAttempt, RunLocation, RunGap, Receiver, Receiver_ID, Reception, ReturnResult, Returner, BlockingPlayer, Tackler1, Tackler2, FieldGoalResult, FieldGoalDistance, Fumble, RecFumbTeam, RecFumbPlayer, Sack, Challenge.Replay, ChalReplayResult, Accepted.Penalty, PenalizedTeam, PenaltyType, PenalizedPlayer, Penalty.Yards, PosTeamScore, DefTeamScore, ScoreDiff, AbsScoreDiff, HomeTeam, AwayTeam, Timeout_Indicator, Timeout_Team, posteam_timeouts_pre, HomeTimeouts_Remaining_Pre, AwayTimeouts_Remaining_Pre, HomeTimeouts_Remaining_Post, AwayTimeouts_Remaining_Post, No_Score_Prob, Opp_Field_Goal_Prob, Opp_Safety_Prob, Opp_Touchdown_Prob, Field_Goal_Prob, Safety_Prob, Touchdown_Prob, ExPoint_Prob, TwoPoint_Prob, ExpPts, EPA, airEPA, yacEPA, Home_WP_pre, Away_WP_pre, Home_WP_post, Away_WP_post, Win_Prob, WPA, airWPA, ...]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 102 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove all the rows that contain a missing value\n",
    "nfl_data.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Oops! That's removed all our data! <span style=\"color:red\"><b>Why do you think this happened?</b></span>\n",
    "\n",
    "<b>Answer</b> : Because we did not specify the \"axis\" and \"how\" parameters in the \"dropna()\" function, as the default\n",
    "values are (axis=0, how='any'), which would delete all the rows that contains any null value in any column. Then it seems that all rows in the dataframe contain at least one null value.\n",
    "\n",
    "We might have better luck removing all the columns that have at least one missing value instead."
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>GameID</th>\n",
       "      <th>Drive</th>\n",
       "      <th>qtr</th>\n",
       "      <th>TimeUnder</th>\n",
       "      <th>ydstogo</th>\n",
       "      <th>ydsnet</th>\n",
       "      <th>PlayAttempted</th>\n",
       "      <th>Yards.Gained</th>\n",
       "      <th>sp</th>\n",
       "      <th>...</th>\n",
       "      <th>Timeout_Indicator</th>\n",
       "      <th>Timeout_Team</th>\n",
       "      <th>posteam_timeouts_pre</th>\n",
       "      <th>HomeTimeouts_Remaining_Pre</th>\n",
       "      <th>AwayTimeouts_Remaining_Pre</th>\n",
       "      <th>HomeTimeouts_Remaining_Post</th>\n",
       "      <th>AwayTimeouts_Remaining_Post</th>\n",
       "      <th>ExPoint_Prob</th>\n",
       "      <th>TwoPoint_Prob</th>\n",
       "      <th>Season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-09-10</td>\n",
       "      <td>2009091000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-09-10</td>\n",
       "      <td>2009091000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-09-10</td>\n",
       "      <td>2009091000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>-3</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-09-10</td>\n",
       "      <td>2009091000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-09-10</td>\n",
       "      <td>2009091000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date      GameID  Drive  qtr  TimeUnder  ydstogo  ydsnet  \\\n",
       "0  2009-09-10  2009091000      1    1         15        0       0   \n",
       "1  2009-09-10  2009091000      1    1         15       10       5   \n",
       "2  2009-09-10  2009091000      1    1         15        5       2   \n",
       "3  2009-09-10  2009091000      1    1         14        8       2   \n",
       "4  2009-09-10  2009091000      1    1         14        8       2   \n",
       "\n",
       "   PlayAttempted  Yards.Gained  sp  ...  Timeout_Indicator  Timeout_Team  \\\n",
       "0              1            39   0  ...                  0          None   \n",
       "1              1             5   0  ...                  0          None   \n",
       "2              1            -3   0  ...                  0          None   \n",
       "3              1             0   0  ...                  0          None   \n",
       "4              1             0   0  ...                  0          None   \n",
       "\n",
       "   posteam_timeouts_pre HomeTimeouts_Remaining_Pre AwayTimeouts_Remaining_Pre  \\\n",
       "0                     3                          3                          3   \n",
       "1                     3                          3                          3   \n",
       "2                     3                          3                          3   \n",
       "3                     3                          3                          3   \n",
       "4                     3                          3                          3   \n",
       "\n",
       "   HomeTimeouts_Remaining_Post  AwayTimeouts_Remaining_Post  ExPoint_Prob  \\\n",
       "0                            3                            3           0.0   \n",
       "1                            3                            3           0.0   \n",
       "2                            3                            3           0.0   \n",
       "3                            3                            3           0.0   \n",
       "4                            3                            3           0.0   \n",
       "\n",
       "   TwoPoint_Prob  Season  \n",
       "0            0.0    2009  \n",
       "1            0.0    2009  \n",
       "2            0.0    2009  \n",
       "3            0.0    2009  \n",
       "4            0.0    2009  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove all columns with at least one missing value\n",
    "columns_with_na_dropped = nfl_data.dropna(axis=1) # axis 1 indicates operation along columns\n",
    "columns_with_na_dropped.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check how much data we lost."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns in original dataset: 102 \n",
      "\n",
      "Columns with na's dropped: 41\n"
     ]
    }
   ],
   "source": [
    "print(\"Columns in original dataset: %d \\n\" % nfl_data.shape[1])\n",
    "print(\"Columns with na's dropped: %d\" % columns_with_na_dropped.shape[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've lost quite a bit of data, but at this point we have successfully removed all the `NaN`'s from our data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Filling in missing values automatically\n",
    "\n",
    "Another option is to try and fill in the missing values. Let's get a small sub-section of the NFL data so that it will print well and work with that to avoid having to work with the entire dataset and consume CPU time. You can work on the entire dataset at home!"
   ]
  },
  {
   "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>EPA</th>\n",
       "      <th>airEPA</th>\n",
       "      <th>yacEPA</th>\n",
       "      <th>Home_WP_pre</th>\n",
       "      <th>Away_WP_pre</th>\n",
       "      <th>Home_WP_post</th>\n",
       "      <th>Away_WP_post</th>\n",
       "      <th>Win_Prob</th>\n",
       "      <th>WPA</th>\n",
       "      <th>airWPA</th>\n",
       "      <th>yacWPA</th>\n",
       "      <th>Season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.014474</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.485675</td>\n",
       "      <td>0.514325</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.453567</td>\n",
       "      <td>0.485675</td>\n",
       "      <td>0.060758</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.077907</td>\n",
       "      <td>-1.068169</td>\n",
       "      <td>1.146076</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.453567</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>0.448912</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.004655</td>\n",
       "      <td>-0.032244</td>\n",
       "      <td>0.036899</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1.402760</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>0.448912</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>0.489207</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>-0.040295</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1.712583</td>\n",
       "      <td>3.318841</td>\n",
       "      <td>-5.031425</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>0.489207</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.538783</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>-0.049576</td>\n",
       "      <td>0.106663</td>\n",
       "      <td>-0.156239</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.097796</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.538783</td>\n",
       "      <td>0.558929</td>\n",
       "      <td>0.441071</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.097712</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        EPA    airEPA    yacEPA  Home_WP_pre  Away_WP_pre  Home_WP_post  \\\n",
       "0  2.014474       NaN       NaN     0.485675     0.514325      0.546433   \n",
       "1  0.077907 -1.068169  1.146076     0.546433     0.453567      0.551088   \n",
       "2 -1.402760       NaN       NaN     0.551088     0.448912      0.510793   \n",
       "3 -1.712583  3.318841 -5.031425     0.510793     0.489207      0.461217   \n",
       "4  2.097796       NaN       NaN     0.461217     0.538783      0.558929   \n",
       "\n",
       "   Away_WP_post  Win_Prob       WPA    airWPA    yacWPA  Season  \n",
       "0      0.453567  0.485675  0.060758       NaN       NaN    2009  \n",
       "1      0.448912  0.546433  0.004655 -0.032244  0.036899    2009  \n",
       "2      0.489207  0.551088 -0.040295       NaN       NaN    2009  \n",
       "3      0.538783  0.510793 -0.049576  0.106663 -0.156239    2009  \n",
       "4      0.441071  0.461217  0.097712       NaN       NaN    2009  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get a small subset of the NFL dataset\n",
    "subset_nfl_data = nfl_data.loc[:, 'EPA':'Season'].head()\n",
    "subset_nfl_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the Panda's fillna() function to fill in missing values in a dataframe for us. One option we have is to specify what we want the `NaN` values to be replaced with. Here, I'm saying that I would like to replace all the `NaN` values with 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EPA</th>\n",
       "      <th>airEPA</th>\n",
       "      <th>yacEPA</th>\n",
       "      <th>Home_WP_pre</th>\n",
       "      <th>Away_WP_pre</th>\n",
       "      <th>Home_WP_post</th>\n",
       "      <th>Away_WP_post</th>\n",
       "      <th>Win_Prob</th>\n",
       "      <th>WPA</th>\n",
       "      <th>airWPA</th>\n",
       "      <th>yacWPA</th>\n",
       "      <th>Season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.014474</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.485675</td>\n",
       "      <td>0.514325</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.453567</td>\n",
       "      <td>0.485675</td>\n",
       "      <td>0.060758</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.077907</td>\n",
       "      <td>-1.068169</td>\n",
       "      <td>1.146076</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.453567</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>0.448912</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.004655</td>\n",
       "      <td>-0.032244</td>\n",
       "      <td>0.036899</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1.402760</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>0.448912</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>0.489207</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>-0.040295</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1.712583</td>\n",
       "      <td>3.318841</td>\n",
       "      <td>-5.031425</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>0.489207</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.538783</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>-0.049576</td>\n",
       "      <td>0.106663</td>\n",
       "      <td>-0.156239</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.097796</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.538783</td>\n",
       "      <td>0.558929</td>\n",
       "      <td>0.441071</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.097712</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        EPA    airEPA    yacEPA  Home_WP_pre  Away_WP_pre  Home_WP_post  \\\n",
       "0  2.014474  0.000000  0.000000     0.485675     0.514325      0.546433   \n",
       "1  0.077907 -1.068169  1.146076     0.546433     0.453567      0.551088   \n",
       "2 -1.402760  0.000000  0.000000     0.551088     0.448912      0.510793   \n",
       "3 -1.712583  3.318841 -5.031425     0.510793     0.489207      0.461217   \n",
       "4  2.097796  0.000000  0.000000     0.461217     0.538783      0.558929   \n",
       "\n",
       "   Away_WP_post  Win_Prob       WPA    airWPA    yacWPA  Season  \n",
       "0      0.453567  0.485675  0.060758  0.000000  0.000000    2009  \n",
       "1      0.448912  0.546433  0.004655 -0.032244  0.036899    2009  \n",
       "2      0.489207  0.551088 -0.040295  0.000000  0.000000    2009  \n",
       "3      0.538783  0.510793 -0.049576  0.106663 -0.156239    2009  \n",
       "4      0.441071  0.461217  0.097712  0.000000  0.000000    2009  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replace all NA's with 0\n",
    "subset_nfl_data.fillna(0) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could also be a bit more savvy and replace missing values with whatever value comes directly after it in the same column. (This makes a lot of sense for datasets where the observations have some sort of logical order to them.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EPA</th>\n",
       "      <th>airEPA</th>\n",
       "      <th>yacEPA</th>\n",
       "      <th>Home_WP_pre</th>\n",
       "      <th>Away_WP_pre</th>\n",
       "      <th>Home_WP_post</th>\n",
       "      <th>Away_WP_post</th>\n",
       "      <th>Win_Prob</th>\n",
       "      <th>WPA</th>\n",
       "      <th>airWPA</th>\n",
       "      <th>yacWPA</th>\n",
       "      <th>Season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.014474</td>\n",
       "      <td>-1.068169</td>\n",
       "      <td>1.146076</td>\n",
       "      <td>0.485675</td>\n",
       "      <td>0.514325</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.453567</td>\n",
       "      <td>0.485675</td>\n",
       "      <td>0.060758</td>\n",
       "      <td>-0.032244</td>\n",
       "      <td>0.036899</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.077907</td>\n",
       "      <td>-1.068169</td>\n",
       "      <td>1.146076</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.453567</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>0.448912</td>\n",
       "      <td>0.546433</td>\n",
       "      <td>0.004655</td>\n",
       "      <td>-0.032244</td>\n",
       "      <td>0.036899</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1.402760</td>\n",
       "      <td>3.318841</td>\n",
       "      <td>-5.031425</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>0.448912</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>0.489207</td>\n",
       "      <td>0.551088</td>\n",
       "      <td>-0.040295</td>\n",
       "      <td>0.106663</td>\n",
       "      <td>-0.156239</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1.712583</td>\n",
       "      <td>3.318841</td>\n",
       "      <td>-5.031425</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>0.489207</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.538783</td>\n",
       "      <td>0.510793</td>\n",
       "      <td>-0.049576</td>\n",
       "      <td>0.106663</td>\n",
       "      <td>-0.156239</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.097796</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.538783</td>\n",
       "      <td>0.558929</td>\n",
       "      <td>0.441071</td>\n",
       "      <td>0.461217</td>\n",
       "      <td>0.097712</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        EPA    airEPA    yacEPA  Home_WP_pre  Away_WP_pre  Home_WP_post  \\\n",
       "0  2.014474 -1.068169  1.146076     0.485675     0.514325      0.546433   \n",
       "1  0.077907 -1.068169  1.146076     0.546433     0.453567      0.551088   \n",
       "2 -1.402760  3.318841 -5.031425     0.551088     0.448912      0.510793   \n",
       "3 -1.712583  3.318841 -5.031425     0.510793     0.489207      0.461217   \n",
       "4  2.097796  0.000000  0.000000     0.461217     0.538783      0.558929   \n",
       "\n",
       "   Away_WP_post  Win_Prob       WPA    airWPA    yacWPA  Season  \n",
       "0      0.453567  0.485675  0.060758 -0.032244  0.036899    2009  \n",
       "1      0.448912  0.546433  0.004655 -0.032244  0.036899    2009  \n",
       "2      0.489207  0.551088 -0.040295  0.106663 -0.156239    2009  \n",
       "3      0.538783  0.510793 -0.049576  0.106663 -0.156239    2009  \n",
       "4      0.441071  0.461217  0.097712  0.000000  0.000000    2009  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replace all NA's the value that comes directly after it in the same column, \n",
    "# then replace all the reamining na's with 0\n",
    "subset_nfl_data.fillna(method = 'bfill', axis=0).fillna(0) # here you may want to explore other methods like 'mean' if it makes sense"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Trying multivariate imputation with sklearn\n",
    "\n",
    "sklearn has an `Imputer` and a `SimpleImputer` modules which perform imputation on one column at a time using the mean, median, or mode. \n",
    "\n",
    "sklearn also has a multivariate imputation module, `IterativeImputer`, that can help us impute values across multiple columns, but it is experimental and only available through developer release.\n",
    "\n",
    "Let's see how an sklearn's imputation works for the NFL dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ahmed/anaconda3/lib/python3.7/site-packages/sklearn/utils/deprecation.py:66: DeprecationWarning: Class Imputer is deprecated; Imputer was deprecated in version 0.20 and will be removed in 0.22. Import impute.SimpleImputer from sklearn instead.\n",
      "  warnings.warn(msg, category=DeprecationWarning)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "EPA             0\n",
       "airEPA          3\n",
       "yacEPA          3\n",
       "Home_WP_pre     0\n",
       "Away_WP_pre     0\n",
       "Home_WP_post    0\n",
       "Away_WP_post    0\n",
       "Win_Prob        0\n",
       "WPA             0\n",
       "airWPA          3\n",
       "yacWPA          3\n",
       "Season          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.preprocessing import Imputer\n",
    "#from sklearn.impute import SimpleImputer # pretty much the same thing, you can try yourself\n",
    "mean_imputer = Imputer(missing_values=np.nan, strategy='mean')\n",
    "#mean_simpleimputer = SimpleImputer(missing_values=np.nan, strategy='mean') # Same use for sklearn SimpleImputer\n",
    "\n",
    "# Again, let's get a small subset of the NFL dataset\n",
    "subset_nfl_data = nfl_data.loc[:, 'EPA':'Season'].head()\n",
    "\n",
    "# Check the presence of null values in the subset chosen\n",
    "subset_nfl_data.isnull().sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "EPA             0\n",
       "airEPA          3\n",
       "yacEPA          3\n",
       "Home_WP_pre     0\n",
       "Away_WP_pre     0\n",
       "Home_WP_post    0\n",
       "Away_WP_post    0\n",
       "Win_Prob        0\n",
       "WPA             0\n",
       "airWPA          3\n",
       "yacWPA          0\n",
       "Season          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now let's impute missing values in yacWPA column with the mean\n",
    "mean_imputer.fit(subset_nfl_data[[\"yacWPA\"]])\n",
    "subset_nfl_data[[\"yacWPA\"]]=mean_imputer.transform(subset_nfl_data[[\"yacWPA\"]]).ravel()\n",
    "#imputed_subset_nfl_data = pd.DataFrame(mean_simpleimputer.fit_transform(subset_nfl_data), columns=subset_nfl_data.columns) # fitting and transforming can be done in one step\n",
    "\n",
    "# Let's check the presence null values again to see if imputation worked for the column\n",
    "subset_nfl_data.isnull().sum()\n",
    "# add you own counting of nulls for the dataframe imputed by SimpleImputer\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        EPA    airEPA    yacEPA  Home_WP_pre  Away_WP_pre  Home_WP_post  \\\n",
      "0  2.014474       NaN       NaN     0.485675     0.514325      0.546433   \n",
      "1  0.077907 -1.068169  1.146076     0.546433     0.453567      0.551088   \n",
      "2 -1.402760       NaN       NaN     0.551088     0.448912      0.510793   \n",
      "3 -1.712583  3.318841 -5.031425     0.510793     0.489207      0.461217   \n",
      "4  2.097796       NaN       NaN     0.461217     0.538783      0.558929   \n",
      "\n",
      "   Away_WP_post  Win_Prob       WPA    airWPA    yacWPA  Season  \n",
      "0      0.453567  0.485675  0.060758       NaN -0.059670    2009  \n",
      "1      0.448912  0.546433  0.004655 -0.032244  0.036899    2009  \n",
      "2      0.489207  0.551088 -0.040295       NaN -0.059670    2009  \n",
      "3      0.538783  0.510793 -0.049576  0.106663 -0.156239    2009  \n",
      "4      0.441071  0.461217  0.097712       NaN -0.059670    2009  \n"
     ]
    }
   ],
   "source": [
    "# Print the dataframe \n",
    "print(subset_nfl_data)\n",
    "#print(imputed_subset_nfl_data) # if you want to try SimpleImputer code\n",
    "# You may want to try the same code snippets above with the entire dataset and not just the subset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's try the more advanced `IterativeImputer` in sklearn. It provides means to do multivariate imputation using a variety of ML models."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'mean_simpleimputer' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-15-9a28e0d7e0a4>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0mreg_imputer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mIterativeImputer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mBayesianRidge\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmax_iter\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m5\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrandom_state\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0msubset_nfl_data\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mnfl_data\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'EPA'\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m'Season'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 10\u001b[0;31m \u001b[0mimputed_subset_nfl_data\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmean_simpleimputer\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfit_transform\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msubset_nfl_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msubset_nfl_data\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     11\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mimputed_subset_nfl_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'mean_simpleimputer' is not defined"
     ]
    }
   ],
   "source": [
    "# This code snippet may not work, so do not worry about it, and just examine the similarities with the SimpleImputer\n",
    "# To use this experimental feature, we need to explicitly ask for it:\n",
    "from sklearn.experimental import enable_iterative_imputer  \n",
    "from sklearn.impute import IterativeImputer\n",
    "from sklearn.linear_model import BayesianRidge # Imputation via linear regression\n",
    "from sklearn.neighbors import KNeighborsRegressor # Imputation via KNN\n",
    "# Define imputer\n",
    "reg_imputer = IterativeImputer(BayesianRidge, max_iter=5, random_state=0)\n",
    "subset_nfl_data = nfl_data.loc[:, 'EPA':'Season'].head()\n",
    "imputed_subset_nfl_data = pd.DataFrame(mean_simpleimputer.fit_transform(subset_nfl_data), columns=subset_nfl_data.columns)\n",
    "print(imputed_subset_nfl_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:red\"> Exercise 1: Missing value handling on the San Francisco Building Permits Dataset </span>\n",
    "\n",
    "<span style=\"color:red\"><b> The <a href=\"https://www.kaggle.com/aparnashastry/building-permit-applications-data#Building_Permits.csv\"> Building permits dataset</a> pertains to all types of structural permits from Jan 1, 2013-Feb 25th 2018 in San Francisco. Data includes details on application/permit numbers, job addresses, supervisorial districts, and the current status of the applications. A data dictionary for the dataset is available in the data folder in this repository.</b></span>\n",
    "\n",
    "<span style=\"color:red\"> <b>The dataset is rather big and does not exist in the \"data\" folder in this repository. Download the dataset from the link provided and store it in your own data or working folder. Import the dataset in the next cell, and work your way through the steps in the subsequent cells.</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ahmed/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3057: DtypeWarning: Columns (22,32) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "BP_data = pd.read_csv(\"data/Building_Permits.csv\")\n",
    "\n",
    "np.random.seed(0) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"> <b>Find out what percent of the building permits dataset is missing. </b></span>\n",
    "<span style=\"color:red\"> <b><br>Write down your code in the following cell. </b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26.26002315058403\n"
     ]
    }
   ],
   "source": [
    "missing_values_count = BP_data.isnull().sum()\n",
    "total_cells = np.product(BP_data.shape)\n",
    "\n",
    "total_missing_values_count = missing_values_count.sum()\n",
    "\n",
    "percentage_missign_values = (total_missing_values_count/total_cells_nfl) * 100\n",
    "print(percentage_missign_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"><b>Write a code to check the values of the `Street Number Suffix` and `Zipcode` from the building permits datasets. Both of these contain missing values. Which, if either, of these are missing because they don't exist? Which, if either, are missing because they weren't recorded?</b></span>\n",
    "\n",
    "<b>Answer</b> : Zipcode values are missing because they were not recorded.\n",
    "Street Number Suffix values are missing because most probably they do not exist."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Street Number Suffix  Zipcode\n",
      "0                       NaN  94102.0\n",
      "1                       NaN  94102.0\n",
      "2                       NaN  94109.0\n",
      "3                       NaN  94109.0\n",
      "4                       NaN  94102.0\n",
      "5                       NaN  94107.0\n",
      "6                       NaN  94122.0\n",
      "7                       NaN  94124.0\n",
      "8                       NaN  94117.0\n",
      "9                       NaN  94117.0\n",
      "10                      NaN  94114.0\n",
      "11                      NaN  94102.0\n",
      "12                      NaN  94114.0\n",
      "13                      NaN  94131.0\n",
      "14                      NaN  94115.0\n",
      "15                      NaN  94108.0\n",
      "16                      NaN  94104.0\n",
      "17                      NaN  94114.0\n",
      "18                      NaN  94123.0\n",
      "19                      NaN  94114.0\n",
      "20                      NaN  94117.0\n",
      "21                      NaN  94110.0\n",
      "22                      NaN  94133.0\n",
      "23                      NaN  94123.0\n",
      "24                      NaN  94123.0\n",
      "25                      NaN  94122.0\n",
      "26                      NaN  94121.0\n",
      "27                      NaN  94117.0\n",
      "28                      NaN  94122.0\n",
      "29                      NaN  94102.0\n",
      "...                     ...      ...\n",
      "198870                  NaN  94109.0\n",
      "198871                  NaN  94117.0\n",
      "198872                  NaN  94114.0\n",
      "198873                  NaN  94110.0\n",
      "198874                  NaN  94131.0\n",
      "198875                  NaN  94102.0\n",
      "198876                  NaN  94117.0\n",
      "198877                  NaN  94112.0\n",
      "198878                  NaN  94110.0\n",
      "198879                  NaN  94112.0\n",
      "198880                  NaN  94122.0\n",
      "198881                  NaN  94133.0\n",
      "198882                  NaN  94112.0\n",
      "198883                  NaN  94102.0\n",
      "198884                  NaN  94103.0\n",
      "198885                  NaN  94116.0\n",
      "198886                  NaN      NaN\n",
      "198887                  NaN      NaN\n",
      "198888                  NaN      NaN\n",
      "198889                  NaN      NaN\n",
      "198890                  NaN      NaN\n",
      "198891                  NaN      NaN\n",
      "198892                  NaN      NaN\n",
      "198893                  NaN      NaN\n",
      "198894                  NaN      NaN\n",
      "198895                  NaN      NaN\n",
      "198896                  NaN      NaN\n",
      "198897                  NaN      NaN\n",
      "198898                  NaN      NaN\n",
      "198899                  NaN      NaN\n",
      "\n",
      "[198900 rows x 2 columns]\n",
      "Missing Values in Street Number Suffix Column 196684\n",
      "Missing Values in Zipcode Column 1716\n"
     ]
    }
   ],
   "source": [
    "print(BP_data[['Street Number Suffix','Zipcode']])\n",
    "\n",
    "print('Missing Values in Street Number Suffix Column ' + str(missing_values_count['Street Number Suffix']))\n",
    "print('Missing Values in Zipcode Column ' + str(missing_values_count['Zipcode']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"><b>See if you can drop the missing values from the building permits dataset and still keep some data, or drop the columns with missing values and see if you have columns still.</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Permit Number  Permit Type            Permit Type Definition  \\\n",
      "0       201505065519            4                      sign - erect   \n",
      "1       201604195146            4                      sign - erect   \n",
      "2       201605278609            3  additions alterations or repairs   \n",
      "3       201611072166            8            otc alterations permit   \n",
      "4       201611283529            6                       demolitions   \n",
      "5       201706149344            8            otc alterations permit   \n",
      "6       201706300814            8            otc alterations permit   \n",
      "7            M803667            8            otc alterations permit   \n",
      "8            M804227            8            otc alterations permit   \n",
      "9            M804767            8            otc alterations permit   \n",
      "10           M805287            8            otc alterations permit   \n",
      "11           M805907            8            otc alterations permit   \n",
      "12           M806447            8            otc alterations permit   \n",
      "13      201707252846            8            otc alterations permit   \n",
      "14           M813729            8            otc alterations permit   \n",
      "15           M813907            8            otc alterations permit   \n",
      "16           M813967            8            otc alterations permit   \n",
      "17           M814148            8            otc alterations permit   \n",
      "18      201707283165            8            otc alterations permit   \n",
      "19           M814368            8            otc alterations permit   \n",
      "20           M814967            8            otc alterations permit   \n",
      "21      201708013476            8            otc alterations permit   \n",
      "22      201708013544            3  additions alterations or repairs   \n",
      "23           M816927            8            otc alterations permit   \n",
      "24      201708084249            8            otc alterations permit   \n",
      "25      201708165014            8            otc alterations permit   \n",
      "26           M820728            8            otc alterations permit   \n",
      "27           M821207            8            otc alterations permit   \n",
      "28           M821268            8            otc alterations permit   \n",
      "29           M821847            8            otc alterations permit   \n",
      "...              ...          ...                               ...   \n",
      "198870       M893328            8            otc alterations permit   \n",
      "198871       M893347            8            otc alterations permit   \n",
      "198872  201802232165            8            otc alterations permit   \n",
      "198873  201802232166            8            otc alterations permit   \n",
      "198874  201802232106            8            otc alterations permit   \n",
      "198875  201802232167            8            otc alterations permit   \n",
      "198876       M893367            8            otc alterations permit   \n",
      "198877  201802232169            8            otc alterations permit   \n",
      "198878  201802232170            8            otc alterations permit   \n",
      "198879  201802232171            8            otc alterations permit   \n",
      "198880       M893387            8            otc alterations permit   \n",
      "198881       M893407            8            otc alterations permit   \n",
      "198882  201802232172            8            otc alterations permit   \n",
      "198883       M893427            8            otc alterations permit   \n",
      "198884       M893447            8            otc alterations permit   \n",
      "198885  201802232173            8            otc alterations permit   \n",
      "198886  201709066971            3  additions alterations or repairs   \n",
      "198887  201710040362            8            otc alterations permit   \n",
      "198888  201710110995            8            otc alterations permit   \n",
      "198889  201710242114            8            otc alterations permit   \n",
      "198890  201711274822            3  additions alterations or repairs   \n",
      "198891  201711274844            8            otc alterations permit   \n",
      "198892  201712045380            8            otc alterations permit   \n",
      "198893  201712055472            8            otc alterations permit   \n",
      "198894  201712055493            8            otc alterations permit   \n",
      "198895       M862628            8            otc alterations permit   \n",
      "198896  201712055595            8            otc alterations permit   \n",
      "198897       M863507            8            otc alterations permit   \n",
      "198898       M863747            8            otc alterations permit   \n",
      "198899       M864287            8            otc alterations permit   \n",
      "\n",
      "       Permit Creation Date Block   Lot  Street Number  Street Name  \\\n",
      "0                05/06/2015  0326   023            140        Ellis   \n",
      "1                04/19/2016  0306   007            440        Geary   \n",
      "2                05/27/2016  0595   203           1647      Pacific   \n",
      "3                11/07/2016  0156   011           1230      Pacific   \n",
      "4                11/28/2016  0342   001            950       Market   \n",
      "5                06/14/2017  4105   009            800      Indiana   \n",
      "6                06/30/2017  1739   020           1291         11th   \n",
      "7                06/30/2017  4789   014           1465       Revere   \n",
      "8                07/05/2017  1212   054           2094         Fell   \n",
      "9                07/06/2017  1259   016             89       Alpine   \n",
      "10               07/06/2017  3541   001            106      Sanchez   \n",
      "11               07/07/2017  0829   021            675         Fell   \n",
      "12               07/10/2017  6537   023           4082         25th   \n",
      "13               07/25/2017  6589  002R            681         27th   \n",
      "14               07/26/2017  1049   027           2761         Pine   \n",
      "15               07/27/2017  0243   043            840       Powell   \n",
      "16               07/27/2017  0268   008            220   Montgomery   \n",
      "17               07/27/2017  3621   097           3707         21st   \n",
      "18               07/28/2017  0472   042           1307          Bay   \n",
      "19               07/28/2017  2659   001            263      Corbett   \n",
      "20               07/31/2017  1176   068           1624       Fulton   \n",
      "21               08/01/2017  6529   010           3410         26th   \n",
      "22               08/01/2017  0074   043           2150       Taylor   \n",
      "23               08/07/2017  0941   012           2824      Filbert   \n",
      "24               08/08/2017  0542   039              1     Charlton   \n",
      "25               08/16/2017  1824   006           1435         29th   \n",
      "26               08/18/2017  1449   006            331         17th   \n",
      "27               08/21/2017  1193   024            120      Shrader   \n",
      "28               08/21/2017  1851   012           1512         06th   \n",
      "29               08/22/2017  0326   001            165       Powell   \n",
      "...                     ...   ...   ...            ...          ...   \n",
      "198870           02/23/2018  0025   024            888  North Point   \n",
      "198871           02/23/2018  1253   001            701      Clayton   \n",
      "198872           02/23/2018  3543   012           2123       Market   \n",
      "198873           02/23/2018  5723   041            100     Tompkins   \n",
      "198874           02/23/2018  6592   023            336       Duncan   \n",
      "198875           02/23/2018  0807   010            580        Hayes   \n",
      "198876           02/23/2018  1213   013            500      Stanyan   \n",
      "198877           02/23/2018  7103   020             91        Lobos   \n",
      "198878           02/23/2018  3641   016           3134         24th   \n",
      "198879           02/23/2018  6082   009            350        Paris   \n",
      "198880           02/23/2018  1841  025D           1442      Funston   \n",
      "198881           02/23/2018  0018   004           2300     Stockton   \n",
      "198882           02/23/2018  6021  001B            730    Excelsior   \n",
      "198883           02/23/2018  0315   021            377        Geary   \n",
      "198884           02/23/2018  3504   030           1699       Market   \n",
      "198885           02/23/2018  2150  001A           2007         30th   \n",
      "198886           09/06/2017  0999   025           3191   Washington   \n",
      "198887           10/04/2017  0750   001           1521         Eddy   \n",
      "198888           10/11/2017  0726   019           1410      Steiner   \n",
      "198889           10/24/2017  1654   035            748         12th   \n",
      "198890           11/27/2017  3803   001            185        Berry   \n",
      "198891           11/27/2017  0766   008            590     Van Ness   \n",
      "198892           12/04/2017  1180   021           1543   Mcallister   \n",
      "198893           12/05/2017  1000   014           3018         Clay   \n",
      "198894           12/05/2017  3522   006            340         12th   \n",
      "198895           12/05/2017  0113  017A           1228   Montgomery   \n",
      "198896           12/05/2017  0271   014            580         Bush   \n",
      "198897           12/06/2017  4318   019           1568      Indiana   \n",
      "198898           12/06/2017  0298   029            795       Sutter   \n",
      "198899           12/07/2017  0160   006            838      Pacific   \n",
      "\n",
      "       Current Status Current Status Date  Filed Date      Record ID  \n",
      "0             expired          12/21/2017  05/06/2015  1380611233945  \n",
      "1              issued          08/03/2017  04/19/2016  1420164406718  \n",
      "2           withdrawn          09/26/2017  05/27/2016  1424856504716  \n",
      "3            complete          07/24/2017  11/07/2016  1443574295566  \n",
      "4              issued          12/01/2017  11/28/2016   144548169992  \n",
      "5              issued          07/06/2017  06/14/2017  1466911170855  \n",
      "6            complete          07/12/2017  06/30/2017  1468970403692  \n",
      "7              issued          06/30/2017  06/30/2017  1469035175050  \n",
      "8              issued          07/05/2017  07/05/2017  1469198253772  \n",
      "9              issued          07/06/2017  07/06/2017   146932394171  \n",
      "10             issued          07/06/2017  07/06/2017  1469445246849  \n",
      "11             issued          07/07/2017  07/07/2017   146961582526  \n",
      "12             issued          07/10/2017  07/10/2017  1469781206718  \n",
      "13              filed          07/25/2017  07/25/2017  1471799208305  \n",
      "14             issued          07/26/2017  07/26/2017   147203988249  \n",
      "15             issued          07/27/2017  07/27/2017   147208763456  \n",
      "16             issued          07/27/2017  07/27/2017   147211866168  \n",
      "17             issued          07/27/2017  07/27/2017  1472184412108  \n",
      "18              filed          07/28/2017  07/28/2017  1472277302360  \n",
      "19             issued          07/28/2017  07/28/2017  1472295408612  \n",
      "20             issued          07/31/2017  07/31/2017  1472619503582  \n",
      "21              filed          08/01/2017  08/01/2017  1472727381751  \n",
      "22              filed          08/01/2017  08/01/2017   147281357792  \n",
      "23             issued          08/07/2017  08/07/2017   147359384812  \n",
      "24             issued          08/08/2017  08/08/2017  1473796372030  \n",
      "25             issued          08/16/2017  08/16/2017  1474841115935  \n",
      "26             issued          08/18/2017  08/18/2017  1475223100163  \n",
      "27             issued          08/21/2017  08/21/2017   147537591942  \n",
      "28             issued          08/21/2017  08/21/2017  1475405117215  \n",
      "29             issued          08/22/2017  08/22/2017  1475605447747  \n",
      "...               ...                 ...         ...            ...  \n",
      "198870         issued          02/23/2018  02/23/2018  1498327347381  \n",
      "198871         issued          02/23/2018  02/23/2018  1498328241068  \n",
      "198872          filed          02/23/2018  02/23/2018  1498329423799  \n",
      "198873         issued          02/23/2018  02/23/2018  1498330475722  \n",
      "198874         issued          02/23/2018  02/23/2018  1498331399024  \n",
      "198875          filed          02/23/2018  02/23/2018   149833282001  \n",
      "198876         issued          02/23/2018  02/23/2018   149833392632  \n",
      "198877         issued          02/23/2018  02/23/2018  1498334221586  \n",
      "198878          filed          02/23/2018  02/23/2018  1498335409431  \n",
      "198879         issued          02/23/2018  02/23/2018  1498336195616  \n",
      "198880         issued          02/23/2018  02/23/2018  1498337116842  \n",
      "198881         issued          02/23/2018  02/23/2018  1498338347099  \n",
      "198882         issued          02/23/2018  02/23/2018  1498339194341  \n",
      "198883         issued          02/23/2018  02/23/2018   149834069380  \n",
      "198884          filed          02/23/2018  02/23/2018  1498341156230  \n",
      "198885          filed          02/23/2018  02/23/2018  1498342127659  \n",
      "198886       approved          01/11/2018  09/06/2017  1477559238559  \n",
      "198887       complete          12/06/2017  10/04/2017  1482126434174  \n",
      "198888       complete          12/07/2017  10/11/2017  1483035367814  \n",
      "198889       complete          12/05/2017  10/24/2017  1484587273155  \n",
      "198890          filed          11/27/2017  11/27/2017  1488352388160  \n",
      "198891         issued          12/08/2017  11/27/2017   148838081446  \n",
      "198892         issued          01/16/2018  12/04/2017   148916691540  \n",
      "198893       complete          01/22/2018  12/05/2017  1489281258414  \n",
      "198894         issued          12/05/2017  12/05/2017  1489308256860  \n",
      "198895         issued          12/05/2017  12/05/2017  1489337276729  \n",
      "198896         issued          12/06/2017  12/05/2017  1489462354993  \n",
      "198897         issued          12/06/2017  12/06/2017  1489539379952  \n",
      "198898         issued          12/06/2017  12/06/2017  1489608233656  \n",
      "198899         issued          12/07/2017  12/07/2017  1489796283803  \n",
      "\n",
      "[198900 rows x 12 columns]\n"
     ]
    }
   ],
   "source": [
    "after_dropping_na_from_rows = BP_data.dropna(axis=0, how='any').head()\n",
    "print(after_dropping_na_from_rows) # No Rows\n",
    "\n",
    "after_dropping_na_from_columns = BP_data.dropna(axis=1, how='any')\n",
    "print(after_dropping_na_from_columns) # 12 Columns from 43 with 198900 rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"><b>Try replacing all the `NaN`'s in the building permits data with the one that comes directly after it and then replacing any remaining `NaN`'s with 0.</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Permit Number</th>\n",
       "      <th>Permit Type</th>\n",
       "      <th>Permit Type Definition</th>\n",
       "      <th>Permit Creation Date</th>\n",
       "      <th>Block</th>\n",
       "      <th>Lot</th>\n",
       "      <th>Street Number</th>\n",
       "      <th>Street Number Suffix</th>\n",
       "      <th>Street Name</th>\n",
       "      <th>Street Suffix</th>\n",
       "      <th>...</th>\n",
       "      <th>Existing Construction Type</th>\n",
       "      <th>Existing Construction Type Description</th>\n",
       "      <th>Proposed Construction Type</th>\n",
       "      <th>Proposed Construction Type Description</th>\n",
       "      <th>Site Permit</th>\n",
       "      <th>Supervisor District</th>\n",
       "      <th>Neighborhoods - Analysis Boundaries</th>\n",
       "      <th>Zipcode</th>\n",
       "      <th>Location</th>\n",
       "      <th>Record ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201505065519</td>\n",
       "      <td>4</td>\n",
       "      <td>sign - erect</td>\n",
       "      <td>05/06/2015</td>\n",
       "      <td>0326</td>\n",
       "      <td>023</td>\n",
       "      <td>140</td>\n",
       "      <td>A</td>\n",
       "      <td>Ellis</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>constr type 3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>Y</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>94102.0</td>\n",
       "      <td>(37.785719256680785, -122.40852313194863)</td>\n",
       "      <td>1380611233945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201604195146</td>\n",
       "      <td>4</td>\n",
       "      <td>sign - erect</td>\n",
       "      <td>04/19/2016</td>\n",
       "      <td>0306</td>\n",
       "      <td>007</td>\n",
       "      <td>440</td>\n",
       "      <td>A</td>\n",
       "      <td>Geary</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>constr type 3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>Y</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>94102.0</td>\n",
       "      <td>(37.78733980600732, -122.41063199757738)</td>\n",
       "      <td>1420164406718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201605278609</td>\n",
       "      <td>3</td>\n",
       "      <td>additions alterations or repairs</td>\n",
       "      <td>05/27/2016</td>\n",
       "      <td>0595</td>\n",
       "      <td>203</td>\n",
       "      <td>1647</td>\n",
       "      <td>A</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>Y</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Russian Hill</td>\n",
       "      <td>94109.0</td>\n",
       "      <td>(37.7946573324287, -122.42232562979227)</td>\n",
       "      <td>1424856504716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201611072166</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>11/07/2016</td>\n",
       "      <td>0156</td>\n",
       "      <td>011</td>\n",
       "      <td>1230</td>\n",
       "      <td>A</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Nob Hill</td>\n",
       "      <td>94109.0</td>\n",
       "      <td>(37.79595867909168, -122.41557405519474)</td>\n",
       "      <td>1443574295566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201611283529</td>\n",
       "      <td>6</td>\n",
       "      <td>demolitions</td>\n",
       "      <td>11/28/2016</td>\n",
       "      <td>0342</td>\n",
       "      <td>001</td>\n",
       "      <td>950</td>\n",
       "      <td>A</td>\n",
       "      <td>Market</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>constr type 3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>Y</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>94102.0</td>\n",
       "      <td>(37.78315261897309, -122.40950883997789)</td>\n",
       "      <td>144548169992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>201706149344</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>06/14/2017</td>\n",
       "      <td>4105</td>\n",
       "      <td>009</td>\n",
       "      <td>800</td>\n",
       "      <td>A</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>Y</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Potrero Hill</td>\n",
       "      <td>94107.0</td>\n",
       "      <td>(37.75922331346539, -122.39170402628598)</td>\n",
       "      <td>1466911170855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>201706300814</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>06/30/2017</td>\n",
       "      <td>1739</td>\n",
       "      <td>020</td>\n",
       "      <td>1291</td>\n",
       "      <td>A</td>\n",
       "      <td>11th</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Inner Sunset</td>\n",
       "      <td>94122.0</td>\n",
       "      <td>(37.764145640138565, -122.46875112470363)</td>\n",
       "      <td>1468970403692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>M803667</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>06/30/2017</td>\n",
       "      <td>4789</td>\n",
       "      <td>014</td>\n",
       "      <td>1465</td>\n",
       "      <td>A</td>\n",
       "      <td>Revere</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Bayview Hunters Point</td>\n",
       "      <td>94124.0</td>\n",
       "      <td>(37.73005099023611, -122.38784938916618)</td>\n",
       "      <td>1469035175050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>M804227</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/05/2017</td>\n",
       "      <td>1212</td>\n",
       "      <td>054</td>\n",
       "      <td>2094</td>\n",
       "      <td>A</td>\n",
       "      <td>Fell</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Lone Mountain/USF</td>\n",
       "      <td>94117.0</td>\n",
       "      <td>(37.772393498502595, -122.45231466824669)</td>\n",
       "      <td>1469198253772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>M804767</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/06/2017</td>\n",
       "      <td>1259</td>\n",
       "      <td>016</td>\n",
       "      <td>89</td>\n",
       "      <td>A</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>Tr</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Haight Ashbury</td>\n",
       "      <td>94117.0</td>\n",
       "      <td>(37.7691724293766, -122.43734859051908)</td>\n",
       "      <td>146932394171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>M805287</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/06/2017</td>\n",
       "      <td>3541</td>\n",
       "      <td>001</td>\n",
       "      <td>106</td>\n",
       "      <td>A</td>\n",
       "      <td>Sanchez</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Castro/Upper Market</td>\n",
       "      <td>94114.0</td>\n",
       "      <td>(37.767349757757145, -122.43140682973024)</td>\n",
       "      <td>1469445246849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>M805907</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/07/2017</td>\n",
       "      <td>0829</td>\n",
       "      <td>021</td>\n",
       "      <td>675</td>\n",
       "      <td>A</td>\n",
       "      <td>Fell</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Hayes Valley</td>\n",
       "      <td>94102.0</td>\n",
       "      <td>(37.774914087334544, -122.42886839796792)</td>\n",
       "      <td>146961582526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>M806447</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/10/2017</td>\n",
       "      <td>6537</td>\n",
       "      <td>023</td>\n",
       "      <td>4082</td>\n",
       "      <td>A</td>\n",
       "      <td>25th</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Noe Valley</td>\n",
       "      <td>94114.0</td>\n",
       "      <td>(37.75006779619502, -122.43127350268124)</td>\n",
       "      <td>1469781206718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>201707252846</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/25/2017</td>\n",
       "      <td>6589</td>\n",
       "      <td>002R</td>\n",
       "      <td>681</td>\n",
       "      <td>A</td>\n",
       "      <td>27th</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Noe Valley</td>\n",
       "      <td>94131.0</td>\n",
       "      <td>(37.7462205625642, -122.4353484860549)</td>\n",
       "      <td>1471799208305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>M813729</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/26/2017</td>\n",
       "      <td>1049</td>\n",
       "      <td>027</td>\n",
       "      <td>2761</td>\n",
       "      <td>A</td>\n",
       "      <td>Pine</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Pacific Heights</td>\n",
       "      <td>94115.0</td>\n",
       "      <td>(37.78676200041077, -122.44124093333335)</td>\n",
       "      <td>147203988249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>M813907</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/27/2017</td>\n",
       "      <td>0243</td>\n",
       "      <td>043</td>\n",
       "      <td>840</td>\n",
       "      <td>A</td>\n",
       "      <td>Powell</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Chinatown</td>\n",
       "      <td>94108.0</td>\n",
       "      <td>(37.7926690651263, -122.40883941791454)</td>\n",
       "      <td>147208763456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>M813967</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/27/2017</td>\n",
       "      <td>0268</td>\n",
       "      <td>008</td>\n",
       "      <td>220</td>\n",
       "      <td>A</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Financial District/South Beach</td>\n",
       "      <td>94104.0</td>\n",
       "      <td>(37.79130641988351, -122.40198579459195)</td>\n",
       "      <td>147211866168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>M814148</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/27/2017</td>\n",
       "      <td>3621</td>\n",
       "      <td>097</td>\n",
       "      <td>3707</td>\n",
       "      <td>A</td>\n",
       "      <td>21st</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Noe Valley</td>\n",
       "      <td>94114.0</td>\n",
       "      <td>(37.75607840165479, -122.43041143390109)</td>\n",
       "      <td>1472184412108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>201707283165</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/28/2017</td>\n",
       "      <td>0472</td>\n",
       "      <td>042</td>\n",
       "      <td>1307</td>\n",
       "      <td>A</td>\n",
       "      <td>Bay</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Marina</td>\n",
       "      <td>94123.0</td>\n",
       "      <td>(37.8035183252307, -122.42863180996076)</td>\n",
       "      <td>1472277302360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>M814368</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/28/2017</td>\n",
       "      <td>2659</td>\n",
       "      <td>001</td>\n",
       "      <td>263</td>\n",
       "      <td>A</td>\n",
       "      <td>Corbett</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Castro/Upper Market</td>\n",
       "      <td>94114.0</td>\n",
       "      <td>(37.761092365437214, -122.44402154957145)</td>\n",
       "      <td>1472295408612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>M814967</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>07/31/2017</td>\n",
       "      <td>1176</td>\n",
       "      <td>068</td>\n",
       "      <td>1624</td>\n",
       "      <td>A</td>\n",
       "      <td>Fulton</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Lone Mountain/USF</td>\n",
       "      <td>94117.0</td>\n",
       "      <td>(37.77640228859421, -122.44361679686382)</td>\n",
       "      <td>1472619503582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>201708013476</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>08/01/2017</td>\n",
       "      <td>6529</td>\n",
       "      <td>010</td>\n",
       "      <td>3410</td>\n",
       "      <td>A</td>\n",
       "      <td>26th</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Mission</td>\n",
       "      <td>94110.0</td>\n",
       "      <td>(37.74914175355125, -122.41849211166853)</td>\n",
       "      <td>1472727381751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>201708013544</td>\n",
       "      <td>3</td>\n",
       "      <td>additions alterations or repairs</td>\n",
       "      <td>08/01/2017</td>\n",
       "      <td>0074</td>\n",
       "      <td>043</td>\n",
       "      <td>2150</td>\n",
       "      <td>A</td>\n",
       "      <td>Taylor</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Russian Hill</td>\n",
       "      <td>94133.0</td>\n",
       "      <td>(37.802405951667645, -122.41446286561653)</td>\n",
       "      <td>147281357792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>M816927</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>08/07/2017</td>\n",
       "      <td>0941</td>\n",
       "      <td>012</td>\n",
       "      <td>2824</td>\n",
       "      <td>A</td>\n",
       "      <td>Filbert</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Marina</td>\n",
       "      <td>94123.0</td>\n",
       "      <td>(37.79697456740493, -122.44615769723546)</td>\n",
       "      <td>147359384812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>201708084249</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>08/08/2017</td>\n",
       "      <td>0542</td>\n",
       "      <td>039</td>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>Charlton</td>\n",
       "      <td>Ct</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Marina</td>\n",
       "      <td>94123.0</td>\n",
       "      <td>(37.79739093461306, -122.43127133823367)</td>\n",
       "      <td>1473796372030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>201708165014</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>08/16/2017</td>\n",
       "      <td>1824</td>\n",
       "      <td>006</td>\n",
       "      <td>1435</td>\n",
       "      <td>A</td>\n",
       "      <td>29th</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Sunset/Parkside</td>\n",
       "      <td>94122.0</td>\n",
       "      <td>(37.76052279949518, -122.4879889739165)</td>\n",
       "      <td>1474841115935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>M820728</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>08/18/2017</td>\n",
       "      <td>1449</td>\n",
       "      <td>006</td>\n",
       "      <td>331</td>\n",
       "      <td>A</td>\n",
       "      <td>17th</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Outer Richmond</td>\n",
       "      <td>94121.0</td>\n",
       "      <td>(37.78177022745135, -122.47658979438802)</td>\n",
       "      <td>1475223100163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>M821207</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>08/21/2017</td>\n",
       "      <td>1193</td>\n",
       "      <td>024</td>\n",
       "      <td>120</td>\n",
       "      <td>A</td>\n",
       "      <td>Shrader</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Lone Mountain/USF</td>\n",
       "      <td>94117.0</td>\n",
       "      <td>(37.77371007080848, -122.45243180846707)</td>\n",
       "      <td>147537591942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>M821268</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>08/21/2017</td>\n",
       "      <td>1851</td>\n",
       "      <td>012</td>\n",
       "      <td>1512</td>\n",
       "      <td>A</td>\n",
       "      <td>06th</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Inner Sunset</td>\n",
       "      <td>94122.0</td>\n",
       "      <td>(37.760252335082875, -122.4625393396095)</td>\n",
       "      <td>1475405117215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>M821847</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>08/22/2017</td>\n",
       "      <td>0326</td>\n",
       "      <td>001</td>\n",
       "      <td>165</td>\n",
       "      <td>A</td>\n",
       "      <td>Powell</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>Y</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>94102.0</td>\n",
       "      <td>(37.78625524960107, -122.40834431815433)</td>\n",
       "      <td>1475605447747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198870</th>\n",
       "      <td>M893328</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>0025</td>\n",
       "      <td>024</td>\n",
       "      <td>888</td>\n",
       "      <td>0</td>\n",
       "      <td>North Point</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Russian Hill</td>\n",
       "      <td>94109.0</td>\n",
       "      <td>(37.8057495627139, -122.42181538597318)</td>\n",
       "      <td>1498327347381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198871</th>\n",
       "      <td>M893347</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>1253</td>\n",
       "      <td>001</td>\n",
       "      <td>701</td>\n",
       "      <td>0</td>\n",
       "      <td>Clayton</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Haight Ashbury</td>\n",
       "      <td>94117.0</td>\n",
       "      <td>(37.76869660680249, -122.44864582118123)</td>\n",
       "      <td>1498328241068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198872</th>\n",
       "      <td>201802232165</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>3543</td>\n",
       "      <td>012</td>\n",
       "      <td>2123</td>\n",
       "      <td>0</td>\n",
       "      <td>Market</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Castro/Upper Market</td>\n",
       "      <td>94114.0</td>\n",
       "      <td>(37.76682151637876, -122.42926240167795)</td>\n",
       "      <td>1498329423799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198873</th>\n",
       "      <td>201802232166</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>5723</td>\n",
       "      <td>041</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>Tompkins</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Bernal Heights</td>\n",
       "      <td>94110.0</td>\n",
       "      <td>(37.73708421334594, -122.4157759256698)</td>\n",
       "      <td>1498330475722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198874</th>\n",
       "      <td>201802232106</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>6592</td>\n",
       "      <td>023</td>\n",
       "      <td>336</td>\n",
       "      <td>0</td>\n",
       "      <td>Duncan</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Noe Valley</td>\n",
       "      <td>94131.0</td>\n",
       "      <td>(37.746231746880696, -122.4285179313817)</td>\n",
       "      <td>1498331399024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198875</th>\n",
       "      <td>201802232167</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>0807</td>\n",
       "      <td>010</td>\n",
       "      <td>580</td>\n",
       "      <td>0</td>\n",
       "      <td>Hayes</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Hayes Valley</td>\n",
       "      <td>94102.0</td>\n",
       "      <td>(37.776756688343944, -122.4260292019233)</td>\n",
       "      <td>149833282001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198876</th>\n",
       "      <td>M893367</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>1213</td>\n",
       "      <td>013</td>\n",
       "      <td>500</td>\n",
       "      <td>0</td>\n",
       "      <td>Stanyan</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Lone Mountain/USF</td>\n",
       "      <td>94117.0</td>\n",
       "      <td>(37.77277103274044, -122.45392960440346)</td>\n",
       "      <td>149833392632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198877</th>\n",
       "      <td>201802232169</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>7103</td>\n",
       "      <td>020</td>\n",
       "      <td>91</td>\n",
       "      <td>0</td>\n",
       "      <td>Lobos</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>Oceanview/Merced/Ingleside</td>\n",
       "      <td>94112.0</td>\n",
       "      <td>(37.7146765393289, -122.45557353560899)</td>\n",
       "      <td>1498334221586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198878</th>\n",
       "      <td>201802232170</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>3641</td>\n",
       "      <td>016</td>\n",
       "      <td>3134</td>\n",
       "      <td>0</td>\n",
       "      <td>24th</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Mission</td>\n",
       "      <td>94110.0</td>\n",
       "      <td>(37.752661001157854, -122.41485533400166)</td>\n",
       "      <td>1498335409431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198879</th>\n",
       "      <td>201802232171</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>6082</td>\n",
       "      <td>009</td>\n",
       "      <td>350</td>\n",
       "      <td>0</td>\n",
       "      <td>Paris</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>Excelsior</td>\n",
       "      <td>94112.0</td>\n",
       "      <td>(37.72324934238285, -122.43409219261636)</td>\n",
       "      <td>1498336195616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198880</th>\n",
       "      <td>M893387</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>1841</td>\n",
       "      <td>025D</td>\n",
       "      <td>1442</td>\n",
       "      <td>0</td>\n",
       "      <td>Funston</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Inner Sunset</td>\n",
       "      <td>94122.0</td>\n",
       "      <td>(37.76117190796411, -122.47007880106999)</td>\n",
       "      <td>1498337116842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198881</th>\n",
       "      <td>M893407</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>0018</td>\n",
       "      <td>004</td>\n",
       "      <td>2300</td>\n",
       "      <td>0</td>\n",
       "      <td>Stockton</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>North Beach</td>\n",
       "      <td>94133.0</td>\n",
       "      <td>(37.80747140583783, -122.41024025753224)</td>\n",
       "      <td>1498338347099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198882</th>\n",
       "      <td>201802232172</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>6021</td>\n",
       "      <td>001B</td>\n",
       "      <td>730</td>\n",
       "      <td>0</td>\n",
       "      <td>Excelsior</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>Excelsior</td>\n",
       "      <td>94112.0</td>\n",
       "      <td>(37.723206757170836, -122.42763728105176)</td>\n",
       "      <td>1498339194341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198883</th>\n",
       "      <td>M893427</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>0315</td>\n",
       "      <td>021</td>\n",
       "      <td>377</td>\n",
       "      <td>0</td>\n",
       "      <td>Geary</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>94102.0</td>\n",
       "      <td>(37.78696971667916, -122.40936701144197)</td>\n",
       "      <td>149834069380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198884</th>\n",
       "      <td>M893447</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>3504</td>\n",
       "      <td>030</td>\n",
       "      <td>1699</td>\n",
       "      <td>0</td>\n",
       "      <td>Market</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Mission</td>\n",
       "      <td>94103.0</td>\n",
       "      <td>(37.77217130976155, -122.42193576313322)</td>\n",
       "      <td>1498341156230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198885</th>\n",
       "      <td>201802232173</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>02/23/2018</td>\n",
       "      <td>2150</td>\n",
       "      <td>001A</td>\n",
       "      <td>2007</td>\n",
       "      <td>0</td>\n",
       "      <td>30th</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Sunset/Parkside</td>\n",
       "      <td>94116.0</td>\n",
       "      <td>(37.74976582284573, -122.48824447038214)</td>\n",
       "      <td>1498342127659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198886</th>\n",
       "      <td>201709066971</td>\n",
       "      <td>3</td>\n",
       "      <td>additions alterations or repairs</td>\n",
       "      <td>09/06/2017</td>\n",
       "      <td>0999</td>\n",
       "      <td>025</td>\n",
       "      <td>3191</td>\n",
       "      <td>0</td>\n",
       "      <td>Washington</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1477559238559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198887</th>\n",
       "      <td>201710040362</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>10/04/2017</td>\n",
       "      <td>0750</td>\n",
       "      <td>001</td>\n",
       "      <td>1521</td>\n",
       "      <td>0</td>\n",
       "      <td>Eddy</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1482126434174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198888</th>\n",
       "      <td>201710110995</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>10/11/2017</td>\n",
       "      <td>0726</td>\n",
       "      <td>019</td>\n",
       "      <td>1410</td>\n",
       "      <td>0</td>\n",
       "      <td>Steiner</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1483035367814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198889</th>\n",
       "      <td>201710242114</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>10/24/2017</td>\n",
       "      <td>1654</td>\n",
       "      <td>035</td>\n",
       "      <td>748</td>\n",
       "      <td>0</td>\n",
       "      <td>12th</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1484587273155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198890</th>\n",
       "      <td>201711274822</td>\n",
       "      <td>3</td>\n",
       "      <td>additions alterations or repairs</td>\n",
       "      <td>11/27/2017</td>\n",
       "      <td>3803</td>\n",
       "      <td>001</td>\n",
       "      <td>185</td>\n",
       "      <td>0</td>\n",
       "      <td>Berry</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>constr type 1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1488352388160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198891</th>\n",
       "      <td>201711274844</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>11/27/2017</td>\n",
       "      <td>0766</td>\n",
       "      <td>008</td>\n",
       "      <td>590</td>\n",
       "      <td>0</td>\n",
       "      <td>Van Ness</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>constr type 3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>constr type 3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>148838081446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198892</th>\n",
       "      <td>201712045380</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>12/04/2017</td>\n",
       "      <td>1180</td>\n",
       "      <td>021</td>\n",
       "      <td>1543</td>\n",
       "      <td>0</td>\n",
       "      <td>Mcallister</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>148916691540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198893</th>\n",
       "      <td>201712055472</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>12/05/2017</td>\n",
       "      <td>1000</td>\n",
       "      <td>014</td>\n",
       "      <td>3018</td>\n",
       "      <td>0</td>\n",
       "      <td>Clay</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1489281258414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198894</th>\n",
       "      <td>201712055493</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>12/05/2017</td>\n",
       "      <td>3522</td>\n",
       "      <td>006</td>\n",
       "      <td>340</td>\n",
       "      <td>0</td>\n",
       "      <td>12th</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1489308256860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198895</th>\n",
       "      <td>M862628</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>12/05/2017</td>\n",
       "      <td>0113</td>\n",
       "      <td>017A</td>\n",
       "      <td>1228</td>\n",
       "      <td>0</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1489337276729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198896</th>\n",
       "      <td>201712055595</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>12/05/2017</td>\n",
       "      <td>0271</td>\n",
       "      <td>014</td>\n",
       "      <td>580</td>\n",
       "      <td>0</td>\n",
       "      <td>Bush</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>5.0</td>\n",
       "      <td>wood frame (5)</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1489462354993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198897</th>\n",
       "      <td>M863507</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>4318</td>\n",
       "      <td>019</td>\n",
       "      <td>1568</td>\n",
       "      <td>0</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1489539379952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198898</th>\n",
       "      <td>M863747</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>0298</td>\n",
       "      <td>029</td>\n",
       "      <td>795</td>\n",
       "      <td>0</td>\n",
       "      <td>Sutter</td>\n",
       "      <td>St</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1489608233656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198899</th>\n",
       "      <td>M864287</td>\n",
       "      <td>8</td>\n",
       "      <td>otc alterations permit</td>\n",
       "      <td>12/07/2017</td>\n",
       "      <td>0160</td>\n",
       "      <td>006</td>\n",
       "      <td>838</td>\n",
       "      <td>0</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Av</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1489796283803</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>198900 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Permit Number  Permit Type            Permit Type Definition  \\\n",
       "0       201505065519            4                      sign - erect   \n",
       "1       201604195146            4                      sign - erect   \n",
       "2       201605278609            3  additions alterations or repairs   \n",
       "3       201611072166            8            otc alterations permit   \n",
       "4       201611283529            6                       demolitions   \n",
       "5       201706149344            8            otc alterations permit   \n",
       "6       201706300814            8            otc alterations permit   \n",
       "7            M803667            8            otc alterations permit   \n",
       "8            M804227            8            otc alterations permit   \n",
       "9            M804767            8            otc alterations permit   \n",
       "10           M805287            8            otc alterations permit   \n",
       "11           M805907            8            otc alterations permit   \n",
       "12           M806447            8            otc alterations permit   \n",
       "13      201707252846            8            otc alterations permit   \n",
       "14           M813729            8            otc alterations permit   \n",
       "15           M813907            8            otc alterations permit   \n",
       "16           M813967            8            otc alterations permit   \n",
       "17           M814148            8            otc alterations permit   \n",
       "18      201707283165            8            otc alterations permit   \n",
       "19           M814368            8            otc alterations permit   \n",
       "20           M814967            8            otc alterations permit   \n",
       "21      201708013476            8            otc alterations permit   \n",
       "22      201708013544            3  additions alterations or repairs   \n",
       "23           M816927            8            otc alterations permit   \n",
       "24      201708084249            8            otc alterations permit   \n",
       "25      201708165014            8            otc alterations permit   \n",
       "26           M820728            8            otc alterations permit   \n",
       "27           M821207            8            otc alterations permit   \n",
       "28           M821268            8            otc alterations permit   \n",
       "29           M821847            8            otc alterations permit   \n",
       "...              ...          ...                               ...   \n",
       "198870       M893328            8            otc alterations permit   \n",
       "198871       M893347            8            otc alterations permit   \n",
       "198872  201802232165            8            otc alterations permit   \n",
       "198873  201802232166            8            otc alterations permit   \n",
       "198874  201802232106            8            otc alterations permit   \n",
       "198875  201802232167            8            otc alterations permit   \n",
       "198876       M893367            8            otc alterations permit   \n",
       "198877  201802232169            8            otc alterations permit   \n",
       "198878  201802232170            8            otc alterations permit   \n",
       "198879  201802232171            8            otc alterations permit   \n",
       "198880       M893387            8            otc alterations permit   \n",
       "198881       M893407            8            otc alterations permit   \n",
       "198882  201802232172            8            otc alterations permit   \n",
       "198883       M893427            8            otc alterations permit   \n",
       "198884       M893447            8            otc alterations permit   \n",
       "198885  201802232173            8            otc alterations permit   \n",
       "198886  201709066971            3  additions alterations or repairs   \n",
       "198887  201710040362            8            otc alterations permit   \n",
       "198888  201710110995            8            otc alterations permit   \n",
       "198889  201710242114            8            otc alterations permit   \n",
       "198890  201711274822            3  additions alterations or repairs   \n",
       "198891  201711274844            8            otc alterations permit   \n",
       "198892  201712045380            8            otc alterations permit   \n",
       "198893  201712055472            8            otc alterations permit   \n",
       "198894  201712055493            8            otc alterations permit   \n",
       "198895       M862628            8            otc alterations permit   \n",
       "198896  201712055595            8            otc alterations permit   \n",
       "198897       M863507            8            otc alterations permit   \n",
       "198898       M863747            8            otc alterations permit   \n",
       "198899       M864287            8            otc alterations permit   \n",
       "\n",
       "       Permit Creation Date Block   Lot  Street Number Street Number Suffix  \\\n",
       "0                05/06/2015  0326   023            140                    A   \n",
       "1                04/19/2016  0306   007            440                    A   \n",
       "2                05/27/2016  0595   203           1647                    A   \n",
       "3                11/07/2016  0156   011           1230                    A   \n",
       "4                11/28/2016  0342   001            950                    A   \n",
       "5                06/14/2017  4105   009            800                    A   \n",
       "6                06/30/2017  1739   020           1291                    A   \n",
       "7                06/30/2017  4789   014           1465                    A   \n",
       "8                07/05/2017  1212   054           2094                    A   \n",
       "9                07/06/2017  1259   016             89                    A   \n",
       "10               07/06/2017  3541   001            106                    A   \n",
       "11               07/07/2017  0829   021            675                    A   \n",
       "12               07/10/2017  6537   023           4082                    A   \n",
       "13               07/25/2017  6589  002R            681                    A   \n",
       "14               07/26/2017  1049   027           2761                    A   \n",
       "15               07/27/2017  0243   043            840                    A   \n",
       "16               07/27/2017  0268   008            220                    A   \n",
       "17               07/27/2017  3621   097           3707                    A   \n",
       "18               07/28/2017  0472   042           1307                    A   \n",
       "19               07/28/2017  2659   001            263                    A   \n",
       "20               07/31/2017  1176   068           1624                    A   \n",
       "21               08/01/2017  6529   010           3410                    A   \n",
       "22               08/01/2017  0074   043           2150                    A   \n",
       "23               08/07/2017  0941   012           2824                    A   \n",
       "24               08/08/2017  0542   039              1                    A   \n",
       "25               08/16/2017  1824   006           1435                    A   \n",
       "26               08/18/2017  1449   006            331                    A   \n",
       "27               08/21/2017  1193   024            120                    A   \n",
       "28               08/21/2017  1851   012           1512                    A   \n",
       "29               08/22/2017  0326   001            165                    A   \n",
       "...                     ...   ...   ...            ...                  ...   \n",
       "198870           02/23/2018  0025   024            888                    0   \n",
       "198871           02/23/2018  1253   001            701                    0   \n",
       "198872           02/23/2018  3543   012           2123                    0   \n",
       "198873           02/23/2018  5723   041            100                    0   \n",
       "198874           02/23/2018  6592   023            336                    0   \n",
       "198875           02/23/2018  0807   010            580                    0   \n",
       "198876           02/23/2018  1213   013            500                    0   \n",
       "198877           02/23/2018  7103   020             91                    0   \n",
       "198878           02/23/2018  3641   016           3134                    0   \n",
       "198879           02/23/2018  6082   009            350                    0   \n",
       "198880           02/23/2018  1841  025D           1442                    0   \n",
       "198881           02/23/2018  0018   004           2300                    0   \n",
       "198882           02/23/2018  6021  001B            730                    0   \n",
       "198883           02/23/2018  0315   021            377                    0   \n",
       "198884           02/23/2018  3504   030           1699                    0   \n",
       "198885           02/23/2018  2150  001A           2007                    0   \n",
       "198886           09/06/2017  0999   025           3191                    0   \n",
       "198887           10/04/2017  0750   001           1521                    0   \n",
       "198888           10/11/2017  0726   019           1410                    0   \n",
       "198889           10/24/2017  1654   035            748                    0   \n",
       "198890           11/27/2017  3803   001            185                    0   \n",
       "198891           11/27/2017  0766   008            590                    0   \n",
       "198892           12/04/2017  1180   021           1543                    0   \n",
       "198893           12/05/2017  1000   014           3018                    0   \n",
       "198894           12/05/2017  3522   006            340                    0   \n",
       "198895           12/05/2017  0113  017A           1228                    0   \n",
       "198896           12/05/2017  0271   014            580                    0   \n",
       "198897           12/06/2017  4318   019           1568                    0   \n",
       "198898           12/06/2017  0298   029            795                    0   \n",
       "198899           12/07/2017  0160   006            838                    0   \n",
       "\n",
       "        Street Name Street Suffix  ...  Existing Construction Type  \\\n",
       "0             Ellis            St  ...                         3.0   \n",
       "1             Geary            St  ...                         3.0   \n",
       "2           Pacific            Av  ...                         1.0   \n",
       "3           Pacific            Av  ...                         5.0   \n",
       "4            Market            St  ...                         3.0   \n",
       "5           Indiana            St  ...                         1.0   \n",
       "6              11th            Av  ...                         5.0   \n",
       "7            Revere            Av  ...                         5.0   \n",
       "8              Fell            St  ...                         5.0   \n",
       "9            Alpine            Tr  ...                         5.0   \n",
       "10          Sanchez            St  ...                         5.0   \n",
       "11             Fell            St  ...                         5.0   \n",
       "12             25th            St  ...                         5.0   \n",
       "13             27th            St  ...                         5.0   \n",
       "14             Pine            St  ...                         5.0   \n",
       "15           Powell            St  ...                         5.0   \n",
       "16       Montgomery            St  ...                         5.0   \n",
       "17             21st            St  ...                         5.0   \n",
       "18              Bay            St  ...                         5.0   \n",
       "19          Corbett            Av  ...                         5.0   \n",
       "20           Fulton            St  ...                         5.0   \n",
       "21             26th            St  ...                         5.0   \n",
       "22           Taylor            St  ...                         5.0   \n",
       "23          Filbert            St  ...                         5.0   \n",
       "24         Charlton            Ct  ...                         5.0   \n",
       "25             29th            Av  ...                         5.0   \n",
       "26             17th            Av  ...                         5.0   \n",
       "27          Shrader            St  ...                         5.0   \n",
       "28             06th            Av  ...                         5.0   \n",
       "29           Powell            St  ...                         5.0   \n",
       "...             ...           ...  ...                         ...   \n",
       "198870  North Point            St  ...                         5.0   \n",
       "198871      Clayton            St  ...                         5.0   \n",
       "198872       Market            St  ...                         5.0   \n",
       "198873     Tompkins            Av  ...                         5.0   \n",
       "198874       Duncan            St  ...                         5.0   \n",
       "198875        Hayes            St  ...                         5.0   \n",
       "198876      Stanyan            St  ...                         5.0   \n",
       "198877        Lobos            St  ...                         5.0   \n",
       "198878         24th            St  ...                         5.0   \n",
       "198879        Paris            St  ...                         5.0   \n",
       "198880      Funston            Av  ...                         5.0   \n",
       "198881     Stockton            St  ...                         5.0   \n",
       "198882    Excelsior            Av  ...                         5.0   \n",
       "198883        Geary            St  ...                         5.0   \n",
       "198884       Market            St  ...                         5.0   \n",
       "198885         30th            Av  ...                         5.0   \n",
       "198886   Washington            St  ...                         5.0   \n",
       "198887         Eddy            St  ...                         1.0   \n",
       "198888      Steiner            St  ...                         1.0   \n",
       "198889         12th            Av  ...                         5.0   \n",
       "198890        Berry            St  ...                         1.0   \n",
       "198891     Van Ness            Av  ...                         3.0   \n",
       "198892   Mcallister            St  ...                         5.0   \n",
       "198893         Clay            St  ...                         5.0   \n",
       "198894         12th            St  ...                         5.0   \n",
       "198895   Montgomery            St  ...                         5.0   \n",
       "198896         Bush            St  ...                         5.0   \n",
       "198897      Indiana            St  ...                         0.0   \n",
       "198898       Sutter            St  ...                         0.0   \n",
       "198899      Pacific            Av  ...                         0.0   \n",
       "\n",
       "       Existing Construction Type Description Proposed Construction Type  \\\n",
       "0                               constr type 3                        1.0   \n",
       "1                               constr type 3                        1.0   \n",
       "2                               constr type 1                        1.0   \n",
       "3                              wood frame (5)                        5.0   \n",
       "4                               constr type 3                        1.0   \n",
       "5                               constr type 1                        1.0   \n",
       "6                              wood frame (5)                        5.0   \n",
       "7                              wood frame (5)                        5.0   \n",
       "8                              wood frame (5)                        5.0   \n",
       "9                              wood frame (5)                        5.0   \n",
       "10                             wood frame (5)                        5.0   \n",
       "11                             wood frame (5)                        5.0   \n",
       "12                             wood frame (5)                        5.0   \n",
       "13                             wood frame (5)                        5.0   \n",
       "14                             wood frame (5)                        5.0   \n",
       "15                             wood frame (5)                        5.0   \n",
       "16                             wood frame (5)                        5.0   \n",
       "17                             wood frame (5)                        5.0   \n",
       "18                             wood frame (5)                        5.0   \n",
       "19                             wood frame (5)                        5.0   \n",
       "20                             wood frame (5)                        5.0   \n",
       "21                             wood frame (5)                        5.0   \n",
       "22                             wood frame (5)                        5.0   \n",
       "23                             wood frame (5)                        5.0   \n",
       "24                             wood frame (5)                        5.0   \n",
       "25                             wood frame (5)                        5.0   \n",
       "26                             wood frame (5)                        5.0   \n",
       "27                             wood frame (5)                        5.0   \n",
       "28                             wood frame (5)                        5.0   \n",
       "29                             wood frame (5)                        5.0   \n",
       "...                                       ...                        ...   \n",
       "198870                         wood frame (5)                        5.0   \n",
       "198871                         wood frame (5)                        5.0   \n",
       "198872                         wood frame (5)                        5.0   \n",
       "198873                         wood frame (5)                        5.0   \n",
       "198874                         wood frame (5)                        5.0   \n",
       "198875                         wood frame (5)                        1.0   \n",
       "198876                         wood frame (5)                        5.0   \n",
       "198877                         wood frame (5)                        5.0   \n",
       "198878                         wood frame (5)                        5.0   \n",
       "198879                         wood frame (5)                        5.0   \n",
       "198880                         wood frame (5)                        5.0   \n",
       "198881                         wood frame (5)                        5.0   \n",
       "198882                         wood frame (5)                        5.0   \n",
       "198883                         wood frame (5)                        5.0   \n",
       "198884                         wood frame (5)                        5.0   \n",
       "198885                         wood frame (5)                        5.0   \n",
       "198886                         wood frame (5)                        5.0   \n",
       "198887                          constr type 1                        1.0   \n",
       "198888                          constr type 1                        1.0   \n",
       "198889                         wood frame (5)                        5.0   \n",
       "198890                          constr type 1                        1.0   \n",
       "198891                          constr type 3                        3.0   \n",
       "198892                         wood frame (5)                        5.0   \n",
       "198893                         wood frame (5)                        5.0   \n",
       "198894                         wood frame (5)                        5.0   \n",
       "198895                         wood frame (5)                        5.0   \n",
       "198896                         wood frame (5)                        5.0   \n",
       "198897                                      0                        0.0   \n",
       "198898                                      0                        0.0   \n",
       "198899                                      0                        0.0   \n",
       "\n",
       "       Proposed Construction Type Description Site Permit Supervisor District  \\\n",
       "0                               constr type 1           Y                 3.0   \n",
       "1                               constr type 1           Y                 3.0   \n",
       "2                               constr type 1           Y                 3.0   \n",
       "3                              wood frame (5)           Y                 3.0   \n",
       "4                               constr type 1           Y                 6.0   \n",
       "5                               constr type 1           Y                10.0   \n",
       "6                              wood frame (5)           Y                 5.0   \n",
       "7                              wood frame (5)           Y                10.0   \n",
       "8                              wood frame (5)           Y                 5.0   \n",
       "9                              wood frame (5)           Y                 8.0   \n",
       "10                             wood frame (5)           Y                 8.0   \n",
       "11                             wood frame (5)           Y                 5.0   \n",
       "12                             wood frame (5)           Y                 8.0   \n",
       "13                             wood frame (5)           Y                 8.0   \n",
       "14                             wood frame (5)           Y                 2.0   \n",
       "15                             wood frame (5)           Y                 3.0   \n",
       "16                             wood frame (5)           Y                 3.0   \n",
       "17                             wood frame (5)           Y                 8.0   \n",
       "18                             wood frame (5)           Y                 2.0   \n",
       "19                             wood frame (5)           Y                 8.0   \n",
       "20                             wood frame (5)           Y                 5.0   \n",
       "21                             wood frame (5)           Y                 9.0   \n",
       "22                             wood frame (5)           Y                 3.0   \n",
       "23                             wood frame (5)           Y                 2.0   \n",
       "24                             wood frame (5)           Y                 2.0   \n",
       "25                             wood frame (5)           Y                 4.0   \n",
       "26                             wood frame (5)           Y                 1.0   \n",
       "27                             wood frame (5)           Y                 5.0   \n",
       "28                             wood frame (5)           Y                 7.0   \n",
       "29                             wood frame (5)           Y                 3.0   \n",
       "...                                       ...         ...                 ...   \n",
       "198870                         wood frame (5)           0                 2.0   \n",
       "198871                         wood frame (5)           0                 5.0   \n",
       "198872                         wood frame (5)           0                 8.0   \n",
       "198873                         wood frame (5)           0                 9.0   \n",
       "198874                         wood frame (5)           0                 8.0   \n",
       "198875                          constr type 1           0                 5.0   \n",
       "198876                         wood frame (5)           0                 5.0   \n",
       "198877                         wood frame (5)           0                11.0   \n",
       "198878                         wood frame (5)           0                 9.0   \n",
       "198879                         wood frame (5)           0                11.0   \n",
       "198880                         wood frame (5)           0                 7.0   \n",
       "198881                         wood frame (5)           0                 3.0   \n",
       "198882                         wood frame (5)           0                11.0   \n",
       "198883                         wood frame (5)           0                 3.0   \n",
       "198884                         wood frame (5)           0                 6.0   \n",
       "198885                         wood frame (5)           0                 4.0   \n",
       "198886                         wood frame (5)           0                 0.0   \n",
       "198887                          constr type 1           0                 0.0   \n",
       "198888                          constr type 1           0                 0.0   \n",
       "198889                         wood frame (5)           0                 0.0   \n",
       "198890                          constr type 1           0                 0.0   \n",
       "198891                          constr type 3           0                 0.0   \n",
       "198892                         wood frame (5)           0                 0.0   \n",
       "198893                         wood frame (5)           0                 0.0   \n",
       "198894                         wood frame (5)           0                 0.0   \n",
       "198895                         wood frame (5)           0                 0.0   \n",
       "198896                         wood frame (5)           0                 0.0   \n",
       "198897                                      0           0                 0.0   \n",
       "198898                                      0           0                 0.0   \n",
       "198899                                      0           0                 0.0   \n",
       "\n",
       "       Neighborhoods - Analysis Boundaries  Zipcode  \\\n",
       "0                               Tenderloin  94102.0   \n",
       "1                               Tenderloin  94102.0   \n",
       "2                             Russian Hill  94109.0   \n",
       "3                                 Nob Hill  94109.0   \n",
       "4                               Tenderloin  94102.0   \n",
       "5                             Potrero Hill  94107.0   \n",
       "6                             Inner Sunset  94122.0   \n",
       "7                    Bayview Hunters Point  94124.0   \n",
       "8                        Lone Mountain/USF  94117.0   \n",
       "9                           Haight Ashbury  94117.0   \n",
       "10                     Castro/Upper Market  94114.0   \n",
       "11                            Hayes Valley  94102.0   \n",
       "12                              Noe Valley  94114.0   \n",
       "13                              Noe Valley  94131.0   \n",
       "14                         Pacific Heights  94115.0   \n",
       "15                               Chinatown  94108.0   \n",
       "16          Financial District/South Beach  94104.0   \n",
       "17                              Noe Valley  94114.0   \n",
       "18                                  Marina  94123.0   \n",
       "19                     Castro/Upper Market  94114.0   \n",
       "20                       Lone Mountain/USF  94117.0   \n",
       "21                                 Mission  94110.0   \n",
       "22                            Russian Hill  94133.0   \n",
       "23                                  Marina  94123.0   \n",
       "24                                  Marina  94123.0   \n",
       "25                         Sunset/Parkside  94122.0   \n",
       "26                          Outer Richmond  94121.0   \n",
       "27                       Lone Mountain/USF  94117.0   \n",
       "28                            Inner Sunset  94122.0   \n",
       "29                              Tenderloin  94102.0   \n",
       "...                                    ...      ...   \n",
       "198870                        Russian Hill  94109.0   \n",
       "198871                      Haight Ashbury  94117.0   \n",
       "198872                 Castro/Upper Market  94114.0   \n",
       "198873                      Bernal Heights  94110.0   \n",
       "198874                          Noe Valley  94131.0   \n",
       "198875                        Hayes Valley  94102.0   \n",
       "198876                   Lone Mountain/USF  94117.0   \n",
       "198877          Oceanview/Merced/Ingleside  94112.0   \n",
       "198878                             Mission  94110.0   \n",
       "198879                           Excelsior  94112.0   \n",
       "198880                        Inner Sunset  94122.0   \n",
       "198881                         North Beach  94133.0   \n",
       "198882                           Excelsior  94112.0   \n",
       "198883                          Tenderloin  94102.0   \n",
       "198884                             Mission  94103.0   \n",
       "198885                     Sunset/Parkside  94116.0   \n",
       "198886                                   0      0.0   \n",
       "198887                                   0      0.0   \n",
       "198888                                   0      0.0   \n",
       "198889                                   0      0.0   \n",
       "198890                                   0      0.0   \n",
       "198891                                   0      0.0   \n",
       "198892                                   0      0.0   \n",
       "198893                                   0      0.0   \n",
       "198894                                   0      0.0   \n",
       "198895                                   0      0.0   \n",
       "198896                                   0      0.0   \n",
       "198897                                   0      0.0   \n",
       "198898                                   0      0.0   \n",
       "198899                                   0      0.0   \n",
       "\n",
       "                                         Location      Record ID  \n",
       "0       (37.785719256680785, -122.40852313194863)  1380611233945  \n",
       "1        (37.78733980600732, -122.41063199757738)  1420164406718  \n",
       "2         (37.7946573324287, -122.42232562979227)  1424856504716  \n",
       "3        (37.79595867909168, -122.41557405519474)  1443574295566  \n",
       "4        (37.78315261897309, -122.40950883997789)   144548169992  \n",
       "5        (37.75922331346539, -122.39170402628598)  1466911170855  \n",
       "6       (37.764145640138565, -122.46875112470363)  1468970403692  \n",
       "7        (37.73005099023611, -122.38784938916618)  1469035175050  \n",
       "8       (37.772393498502595, -122.45231466824669)  1469198253772  \n",
       "9         (37.7691724293766, -122.43734859051908)   146932394171  \n",
       "10      (37.767349757757145, -122.43140682973024)  1469445246849  \n",
       "11      (37.774914087334544, -122.42886839796792)   146961582526  \n",
       "12       (37.75006779619502, -122.43127350268124)  1469781206718  \n",
       "13         (37.7462205625642, -122.4353484860549)  1471799208305  \n",
       "14       (37.78676200041077, -122.44124093333335)   147203988249  \n",
       "15        (37.7926690651263, -122.40883941791454)   147208763456  \n",
       "16       (37.79130641988351, -122.40198579459195)   147211866168  \n",
       "17       (37.75607840165479, -122.43041143390109)  1472184412108  \n",
       "18        (37.8035183252307, -122.42863180996076)  1472277302360  \n",
       "19      (37.761092365437214, -122.44402154957145)  1472295408612  \n",
       "20       (37.77640228859421, -122.44361679686382)  1472619503582  \n",
       "21       (37.74914175355125, -122.41849211166853)  1472727381751  \n",
       "22      (37.802405951667645, -122.41446286561653)   147281357792  \n",
       "23       (37.79697456740493, -122.44615769723546)   147359384812  \n",
       "24       (37.79739093461306, -122.43127133823367)  1473796372030  \n",
       "25        (37.76052279949518, -122.4879889739165)  1474841115935  \n",
       "26       (37.78177022745135, -122.47658979438802)  1475223100163  \n",
       "27       (37.77371007080848, -122.45243180846707)   147537591942  \n",
       "28       (37.760252335082875, -122.4625393396095)  1475405117215  \n",
       "29       (37.78625524960107, -122.40834431815433)  1475605447747  \n",
       "...                                           ...            ...  \n",
       "198870    (37.8057495627139, -122.42181538597318)  1498327347381  \n",
       "198871   (37.76869660680249, -122.44864582118123)  1498328241068  \n",
       "198872   (37.76682151637876, -122.42926240167795)  1498329423799  \n",
       "198873    (37.73708421334594, -122.4157759256698)  1498330475722  \n",
       "198874   (37.746231746880696, -122.4285179313817)  1498331399024  \n",
       "198875   (37.776756688343944, -122.4260292019233)   149833282001  \n",
       "198876   (37.77277103274044, -122.45392960440346)   149833392632  \n",
       "198877    (37.7146765393289, -122.45557353560899)  1498334221586  \n",
       "198878  (37.752661001157854, -122.41485533400166)  1498335409431  \n",
       "198879   (37.72324934238285, -122.43409219261636)  1498336195616  \n",
       "198880   (37.76117190796411, -122.47007880106999)  1498337116842  \n",
       "198881   (37.80747140583783, -122.41024025753224)  1498338347099  \n",
       "198882  (37.723206757170836, -122.42763728105176)  1498339194341  \n",
       "198883   (37.78696971667916, -122.40936701144197)   149834069380  \n",
       "198884   (37.77217130976155, -122.42193576313322)  1498341156230  \n",
       "198885   (37.74976582284573, -122.48824447038214)  1498342127659  \n",
       "198886                                          0  1477559238559  \n",
       "198887                                          0  1482126434174  \n",
       "198888                                          0  1483035367814  \n",
       "198889                                          0  1484587273155  \n",
       "198890                                          0  1488352388160  \n",
       "198891                                          0   148838081446  \n",
       "198892                                          0   148916691540  \n",
       "198893                                          0  1489281258414  \n",
       "198894                                          0  1489308256860  \n",
       "198895                                          0  1489337276729  \n",
       "198896                                          0  1489462354993  \n",
       "198897                                          0  1489539379952  \n",
       "198898                                          0  1489608233656  \n",
       "198899                                          0  1489796283803  \n",
       "\n",
       "[198900 rows x 43 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filled_Data = BP_data.fillna(method = 'bfill', axis=0).fillna(0)\n",
    "filled_Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"><b>Create a new dataset at the ending with all missing values handled and store as a CSV file.</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "filled_Data.to_csv('data/Building_Permits_Filled.csv');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:red\"> Exercise 2: Missing value handling on the  Pima Indians Diabetes Dataset</span>\n",
    "\n",
    "<span style=\"color:red\"><b> The <a href= \"https://raw.githubusercontent.com/jbrownlee/Datasets/master/pima-indians-diabetes.data.csv\"> Pima Indians Diabetes Dataset</a> involves predicting the onset of diabetes within 5 years in Pima Indians given medical details.</b></span>\n",
    "\n",
    "<span style=\"color:red\">There are 768 observations with 8 input variables and 1 output variable. The variable names are as follows:</span>\n",
    "\n",
    "<br><span style=\"color:red\">0. Number of times pregnant.</span>\n",
    "<br><span style=\"color:red\">1. Plasma glucose concentration a 2 hours in an oral glucose tolerance test.</span>\n",
    "<br><span style=\"color:red\">2. Diastolic blood pressure (mm Hg).</span>\n",
    "<br><span style=\"color:red\">3. Triceps skinfold thickness (mm).</span>\n",
    "<br><span style=\"color:red\">4. 2-Hour serum insulin (mu U/ml).</span>\n",
    "<br><span style=\"color:red\">5. Body mass index (weight in kg/(height in m)^2).</span>\n",
    "<br><span style=\"color:red\">6. Diabetes pedigree function.</span>\n",
    "<br><span style=\"color:red\">7. Age (years).\n",
    "<br><span style=\"color:red\">8. Class variable (0 or 1).</span>\n",
    "\n",
    "<span style=\"color:red\"> <b>This is a medical dataset, so an important thing we will do with this dataset is that we will see if the statistical summaries can reveal something about the missing values.</b></span>\n",
    "\n",
    "<span style=\"color:red\"> <b>The dataset is available in the `data` folder in this repository. Import the dataset in the next cell, and work your way through the steps in the subsequent cells.</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "columns_names = ['Number of times pregnant', 'Plasma glucose concentration', 'Diastolic blood pressure', 'Triceps skinfold thickness',\n",
    "                 '2H serum insulin', 'BMI', 'Diabetes pedigree function', 'Age', 'Class']\n",
    "\n",
    "PID_data = pd.read_csv(\"data/pima-indians-diabetes.data.csv\", names=columns_names)\n",
    "\n",
    "np.random.seed(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"> <b>Use the describe method used for pandas data frames to obtain a statistucal summary of the dataset.</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>Number of times pregnant</th>\n",
       "      <th>Plasma glucose concentration</th>\n",
       "      <th>Diastolic blood pressure</th>\n",
       "      <th>Triceps skinfold thickness</th>\n",
       "      <th>2H serum insulin</th>\n",
       "      <th>BMI</th>\n",
       "      <th>Diabetes pedigree function</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.845052</td>\n",
       "      <td>120.894531</td>\n",
       "      <td>69.105469</td>\n",
       "      <td>20.536458</td>\n",
       "      <td>79.799479</td>\n",
       "      <td>31.992578</td>\n",
       "      <td>0.471876</td>\n",
       "      <td>33.240885</td>\n",
       "      <td>0.348958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>3.369578</td>\n",
       "      <td>31.972618</td>\n",
       "      <td>19.355807</td>\n",
       "      <td>15.952218</td>\n",
       "      <td>115.244002</td>\n",
       "      <td>7.884160</td>\n",
       "      <td>0.331329</td>\n",
       "      <td>11.760232</td>\n",
       "      <td>0.476951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.078000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>99.000000</td>\n",
       "      <td>62.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>27.300000</td>\n",
       "      <td>0.243750</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>117.000000</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>30.500000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>0.372500</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>140.250000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>127.250000</td>\n",
       "      <td>36.600000</td>\n",
       "      <td>0.626250</td>\n",
       "      <td>41.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>17.000000</td>\n",
       "      <td>199.000000</td>\n",
       "      <td>122.000000</td>\n",
       "      <td>99.000000</td>\n",
       "      <td>846.000000</td>\n",
       "      <td>67.100000</td>\n",
       "      <td>2.420000</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Number of times pregnant  Plasma glucose concentration  \\\n",
       "count                768.000000                    768.000000   \n",
       "mean                   3.845052                    120.894531   \n",
       "std                    3.369578                     31.972618   \n",
       "min                    0.000000                      0.000000   \n",
       "25%                    1.000000                     99.000000   \n",
       "50%                    3.000000                    117.000000   \n",
       "75%                    6.000000                    140.250000   \n",
       "max                   17.000000                    199.000000   \n",
       "\n",
       "       Diastolic blood pressure  Triceps skinfold thickness  2H serum insulin  \\\n",
       "count                768.000000                  768.000000        768.000000   \n",
       "mean                  69.105469                   20.536458         79.799479   \n",
       "std                   19.355807                   15.952218        115.244002   \n",
       "min                    0.000000                    0.000000          0.000000   \n",
       "25%                   62.000000                    0.000000          0.000000   \n",
       "50%                   72.000000                   23.000000         30.500000   \n",
       "75%                   80.000000                   32.000000        127.250000   \n",
       "max                  122.000000                   99.000000        846.000000   \n",
       "\n",
       "              BMI  Diabetes pedigree function         Age       Class  \n",
       "count  768.000000                  768.000000  768.000000  768.000000  \n",
       "mean    31.992578                    0.471876   33.240885    0.348958  \n",
       "std      7.884160                    0.331329   11.760232    0.476951  \n",
       "min      0.000000                    0.078000   21.000000    0.000000  \n",
       "25%     27.300000                    0.243750   24.000000    0.000000  \n",
       "50%     32.000000                    0.372500   29.000000    0.000000  \n",
       "75%     36.600000                    0.626250   41.000000    1.000000  \n",
       "max     67.100000                    2.420000   81.000000    1.000000  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PID_data.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"><b>There are columns that have a minimum value of zero (0). On some columns, a value of zero does not make sense and indicates an invalid or missing value. For example, the `BMI` for a person cannot be 0. See if there are other columns/attributes whose value of 0 does not make sense. An understanding of the medical columns may be needed.</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plasma glucose concentration, Diastolic blood pressure, Triceps skinfold thickness, 2H serum insulin, BMI Cannot\n",
    "# be Zero"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"><b>Now you can proceed to check the missing values and remove/impute as in exercise 1. Create a new dataset at the ending with all missing values handled and store as a CSV file.</b></span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "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>Number of times pregnant</th>\n",
       "      <th>Plasma glucose concentration</th>\n",
       "      <th>Diastolic blood pressure</th>\n",
       "      <th>Triceps skinfold thickness</th>\n",
       "      <th>2H serum insulin</th>\n",
       "      <th>BMI</th>\n",
       "      <th>Diabetes pedigree function</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "      <td>768.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.845052</td>\n",
       "      <td>121.686763</td>\n",
       "      <td>72.405184</td>\n",
       "      <td>29.153420</td>\n",
       "      <td>155.548223</td>\n",
       "      <td>32.457464</td>\n",
       "      <td>0.471876</td>\n",
       "      <td>33.240885</td>\n",
       "      <td>0.348958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>3.369578</td>\n",
       "      <td>30.435949</td>\n",
       "      <td>12.096346</td>\n",
       "      <td>8.790942</td>\n",
       "      <td>85.021108</td>\n",
       "      <td>6.875151</td>\n",
       "      <td>0.331329</td>\n",
       "      <td>11.760232</td>\n",
       "      <td>0.476951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>18.200000</td>\n",
       "      <td>0.078000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>99.750000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>121.500000</td>\n",
       "      <td>27.500000</td>\n",
       "      <td>0.243750</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>117.000000</td>\n",
       "      <td>72.202592</td>\n",
       "      <td>29.153420</td>\n",
       "      <td>155.548223</td>\n",
       "      <td>32.400000</td>\n",
       "      <td>0.372500</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.000000</td>\n",
       "      <td>140.250000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>155.548223</td>\n",
       "      <td>36.600000</td>\n",
       "      <td>0.626250</td>\n",
       "      <td>41.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>17.000000</td>\n",
       "      <td>199.000000</td>\n",
       "      <td>122.000000</td>\n",
       "      <td>99.000000</td>\n",
       "      <td>846.000000</td>\n",
       "      <td>67.100000</td>\n",
       "      <td>2.420000</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Number of times pregnant  Plasma glucose concentration  \\\n",
       "count                768.000000                    768.000000   \n",
       "mean                   3.845052                    121.686763   \n",
       "std                    3.369578                     30.435949   \n",
       "min                    0.000000                     44.000000   \n",
       "25%                    1.000000                     99.750000   \n",
       "50%                    3.000000                    117.000000   \n",
       "75%                    6.000000                    140.250000   \n",
       "max                   17.000000                    199.000000   \n",
       "\n",
       "       Diastolic blood pressure  Triceps skinfold thickness  2H serum insulin  \\\n",
       "count                768.000000                  768.000000        768.000000   \n",
       "mean                  72.405184                   29.153420        155.548223   \n",
       "std                   12.096346                    8.790942         85.021108   \n",
       "min                   24.000000                    7.000000         14.000000   \n",
       "25%                   64.000000                   25.000000        121.500000   \n",
       "50%                   72.202592                   29.153420        155.548223   \n",
       "75%                   80.000000                   32.000000        155.548223   \n",
       "max                  122.000000                   99.000000        846.000000   \n",
       "\n",
       "              BMI  Diabetes pedigree function         Age       Class  \n",
       "count  768.000000                  768.000000  768.000000  768.000000  \n",
       "mean    32.457464                    0.471876   33.240885    0.348958  \n",
       "std      6.875151                    0.331329   11.760232    0.476951  \n",
       "min     18.200000                    0.078000   21.000000    0.000000  \n",
       "25%     27.500000                    0.243750   24.000000    0.000000  \n",
       "50%     32.400000                    0.372500   29.000000    0.000000  \n",
       "75%     36.600000                    0.626250   41.000000    1.000000  \n",
       "max     67.100000                    2.420000   81.000000    1.000000  "
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['Plasma glucose concentration', 'Diastolic blood pressure', 'Triceps skinfold thickness', '2H serum insulin', 'BMI']\n",
    "\n",
    "PID_data[cols] = PID_data[cols].replace({0:np.nan})\n",
    "\n",
    "filled_Data = PID_data.fillna(PID_data.mean())\n",
    "\n",
    "filled_Data.to_csv('data/Pima_Indians_Diabetes_Dataset_Filled.csv');\n",
    "\n",
    "filled_Data.describe()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}