1.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": 3,
   "metadata": {},
   "outputs": [],
   "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": 4,
   "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": 4,
     "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": 5,
   "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": 6,
   "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": 7,
   "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": 7,
     "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": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 8,
     "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",
    "We might have better luck removing all the columns that have at least one missing value instead."
   ]
  },
  {
   "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>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": 9,
     "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": 10,
   "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": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 11,
     "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": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 12,
     "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": 13,
   "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": 13,
     "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": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "H:\\DataScienceWork\\Anaconda3\\lib\\site-packages\\sklearn\\utils\\deprecation.py:58: 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": 14,
     "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": 15,
   "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": 15,
     "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": 16,
   "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": 17,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'sklearn.experimental'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-17-9a28e0d7e0a4>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# This code snippet may not work, so do not worry about it, and just examine the similarities with the SimpleImputer\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;31m# To use this experimental feature, we need to explicitly ask for it:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[1;32mfrom\u001b[0m \u001b[0msklearn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexperimental\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0menable_iterative_imputer\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0msklearn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mimpute\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mIterativeImputer\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0msklearn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlinear_model\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mBayesianRidge\u001b[0m \u001b[1;31m# Imputation via linear regression\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'sklearn.experimental'"
     ]
    }
   ],
   "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": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\hp\\appdata\\local\\programs\\python\\python37-32\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3058: 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",
    "# read in all our data\n",
    "nfl_data = pd.read_csv(\"data\\Building_Permits.csv\")\n",
    "\n",
    "# set seed for reproducibility\n",
    "np.random.seed(0) \n"
   ]
  },
  {
   "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>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",
       "      <td>0</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>1</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>2</td>\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>NaN</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>NaN</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",
       "      <td>3</td>\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>NaN</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>NaN</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",
       "      <td>4</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 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",
       "\n",
       "  Permit Creation Date Block  Lot  Street Number Street Number Suffix  \\\n",
       "0           05/06/2015  0326  023            140                  NaN   \n",
       "1           04/19/2016  0306  007            440                  NaN   \n",
       "2           05/27/2016  0595  203           1647                  NaN   \n",
       "3           11/07/2016  0156  011           1230                  NaN   \n",
       "4           11/28/2016  0342  001            950                  NaN   \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",
       "\n",
       "  Existing Construction Type Description Proposed Construction Type  \\\n",
       "0                          constr type 3                        NaN   \n",
       "1                          constr type 3                        NaN   \n",
       "2                          constr type 1                        1.0   \n",
       "3                         wood frame (5)                        5.0   \n",
       "4                          constr type 3                        NaN   \n",
       "\n",
       "  Proposed Construction Type Description Site Permit Supervisor District  \\\n",
       "0                                    NaN         NaN                 3.0   \n",
       "1                                    NaN         NaN                 3.0   \n",
       "2                          constr type 1         NaN                 3.0   \n",
       "3                         wood frame (5)         NaN                 3.0   \n",
       "4                                    NaN         NaN                 6.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",
       "\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",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data.head()"
   ]
  },
  {
   "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": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Permit Number                                  0\n",
      "Permit Type                                    0\n",
      "Permit Type Definition                         0\n",
      "Permit Creation Date                           0\n",
      "Block                                          0\n",
      "Lot                                            0\n",
      "Street Number                                  0\n",
      "Street Number Suffix                      196684\n",
      "Street Name                                    0\n",
      "Street Suffix                               2768\n",
      "Unit                                      169421\n",
      "Unit Suffix                               196939\n",
      "Description                                  290\n",
      "Current Status                                 0\n",
      "Current Status Date                            0\n",
      "Filed Date                                     0\n",
      "Issued Date                                14940\n",
      "Completed Date                            101709\n",
      "First Construction Document Date           14946\n",
      "Structural Notification                   191978\n",
      "Number of Existing Stories                 42784\n",
      "Number of Proposed Stories                 42868\n",
      "Voluntary Soft-Story Retrofit             198865\n",
      "Fire Only Permit                          180073\n",
      "Permit Expiration Date                     51880\n",
      "Estimated Cost                             38066\n",
      "Revised Cost                                6066\n",
      "Existing Use                               41114\n",
      "Existing Units                             51538\n",
      "Proposed Use                               42439\n",
      "Proposed Units                             50911\n",
      "Plansets                                   37309\n",
      "TIDF Compliance                           198898\n",
      "Existing Construction Type                 43366\n",
      "Existing Construction Type Description     43366\n",
      "Proposed Construction Type                 43162\n",
      "Proposed Construction Type Description     43162\n",
      "Site Permit                               193541\n",
      "Supervisor District                         1717\n",
      "Neighborhoods - Analysis Boundaries         1725\n",
      "Zipcode                                     1716\n",
      "Location                                    1700\n",
      "Record ID                                      0\n",
      "dtype: int64\n",
      "26.26002315058403\n"
     ]
    }
   ],
   "source": [
    "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)\n",
    "\n",
    "# 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": [
    "<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>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Street Number Suffix</th>\n",
       "      <th>Street Name</th>\n",
       "      <th>Street Suffix</th>\n",
       "      <th>Unit</th>\n",
       "      <th>Unit Suffix</th>\n",
       "      <th>Description</th>\n",
       "      <th>Current Status</th>\n",
       "      <th>Current Status Date</th>\n",
       "      <th>Filed Date</th>\n",
       "      <th>Issued Date</th>\n",
       "      <th>...</th>\n",
       "      <th>Plansets</th>\n",
       "      <th>TIDF Compliance</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ellis</td>\n",
       "      <td>St</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ground fl facade: to erect illuminated, electr...</td>\n",
       "      <td>expired</td>\n",
       "      <td>12/21/2017</td>\n",
       "      <td>05/06/2015</td>\n",
       "      <td>11/09/2015</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>constr type 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>94102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Geary</td>\n",
       "      <td>St</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>remove (e) awning and associated signs.</td>\n",
       "      <td>issued</td>\n",
       "      <td>08/03/2017</td>\n",
       "      <td>04/19/2016</td>\n",
       "      <td>08/03/2017</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>constr type 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>94102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Av</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>installation of separating wall</td>\n",
       "      <td>withdrawn</td>\n",
       "      <td>09/26/2017</td>\n",
       "      <td>05/27/2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Russian Hill</td>\n",
       "      <td>94109.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Av</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>repair dryrot &amp; stucco at front of bldg.</td>\n",
       "      <td>complete</td>\n",
       "      <td>07/24/2017</td>\n",
       "      <td>11/07/2016</td>\n",
       "      <td>07/18/2017</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Nob Hill</td>\n",
       "      <td>94109.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Market</td>\n",
       "      <td>St</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>demolish retail/office/commercial 3-story buil...</td>\n",
       "      <td>issued</td>\n",
       "      <td>12/01/2017</td>\n",
       "      <td>11/28/2016</td>\n",
       "      <td>12/01/2017</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>constr type 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Tenderloin</td>\n",
       "      <td>94102.0</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>198895</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>St</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>street space</td>\n",
       "      <td>issued</td>\n",
       "      <td>12/05/2017</td>\n",
       "      <td>12/05/2017</td>\n",
       "      <td>12/05/2017</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>198896</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bush</td>\n",
       "      <td>St</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fire alarm upgrade ref 201704123852</td>\n",
       "      <td>issued</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>12/05/2017</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>198897</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>St</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>street space</td>\n",
       "      <td>issued</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>198898</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sutter</td>\n",
       "      <td>St</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>street space permit</td>\n",
       "      <td>issued</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>12/06/2017</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>198899</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Av</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>street space permit</td>\n",
       "      <td>issued</td>\n",
       "      <td>12/07/2017</td>\n",
       "      <td>12/07/2017</td>\n",
       "      <td>12/07/2017</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>198900 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Street Number Suffix Street Name Street Suffix  Unit Unit Suffix  \\\n",
       "0                       NaN       Ellis            St   NaN         NaN   \n",
       "1                       NaN       Geary            St   0.0         NaN   \n",
       "2                       NaN     Pacific            Av   NaN         NaN   \n",
       "3                       NaN     Pacific            Av   0.0         NaN   \n",
       "4                       NaN      Market            St   NaN         NaN   \n",
       "...                     ...         ...           ...   ...         ...   \n",
       "198895                  NaN  Montgomery            St   NaN         NaN   \n",
       "198896                  NaN        Bush            St   NaN         NaN   \n",
       "198897                  NaN     Indiana            St   NaN         NaN   \n",
       "198898                  NaN      Sutter            St   NaN         NaN   \n",
       "198899                  NaN     Pacific            Av   NaN         NaN   \n",
       "\n",
       "                                              Description Current Status  \\\n",
       "0       ground fl facade: to erect illuminated, electr...        expired   \n",
       "1                 remove (e) awning and associated signs.         issued   \n",
       "2                         installation of separating wall      withdrawn   \n",
       "3                repair dryrot & stucco at front of bldg.       complete   \n",
       "4       demolish retail/office/commercial 3-story buil...         issued   \n",
       "...                                                   ...            ...   \n",
       "198895                                       street space         issued   \n",
       "198896                fire alarm upgrade ref 201704123852         issued   \n",
       "198897                                       street space         issued   \n",
       "198898                                street space permit         issued   \n",
       "198899                                street space permit         issued   \n",
       "\n",
       "       Current Status Date  Filed Date Issued Date  ... Plansets  \\\n",
       "0               12/21/2017  05/06/2015  11/09/2015  ...      2.0   \n",
       "1               08/03/2017  04/19/2016  08/03/2017  ...      2.0   \n",
       "2               09/26/2017  05/27/2016         NaN  ...      2.0   \n",
       "3               07/24/2017  11/07/2016  07/18/2017  ...      2.0   \n",
       "4               12/01/2017  11/28/2016  12/01/2017  ...      2.0   \n",
       "...                    ...         ...         ...  ...      ...   \n",
       "198895          12/05/2017  12/05/2017  12/05/2017  ...      NaN   \n",
       "198896          12/06/2017  12/05/2017  12/06/2017  ...      2.0   \n",
       "198897          12/06/2017  12/06/2017  12/06/2017  ...      NaN   \n",
       "198898          12/06/2017  12/06/2017  12/06/2017  ...      NaN   \n",
       "198899          12/07/2017  12/07/2017  12/07/2017  ...      NaN   \n",
       "\n",
       "       TIDF Compliance Existing Construction Type  \\\n",
       "0                  NaN                        3.0   \n",
       "1                  NaN                        3.0   \n",
       "2                  NaN                        1.0   \n",
       "3                  NaN                        5.0   \n",
       "4                  NaN                        3.0   \n",
       "...                ...                        ...   \n",
       "198895             NaN                        NaN   \n",
       "198896             NaN                        5.0   \n",
       "198897             NaN                        NaN   \n",
       "198898             NaN                        NaN   \n",
       "198899             NaN                        NaN   \n",
       "\n",
       "        Existing Construction Type Description  Proposed Construction Type  \\\n",
       "0                                constr type 3                         NaN   \n",
       "1                                constr type 3                         NaN   \n",
       "2                                constr type 1                         1.0   \n",
       "3                               wood frame (5)                         5.0   \n",
       "4                                constr type 3                         NaN   \n",
       "...                                        ...                         ...   \n",
       "198895                                     NaN                         NaN   \n",
       "198896                          wood frame (5)                         5.0   \n",
       "198897                                     NaN                         NaN   \n",
       "198898                                     NaN                         NaN   \n",
       "198899                                     NaN                         NaN   \n",
       "\n",
       "       Proposed Construction Type Description Site Permit Supervisor District  \\\n",
       "0                                         NaN         NaN                 3.0   \n",
       "1                                         NaN         NaN                 3.0   \n",
       "2                               constr type 1         NaN                 3.0   \n",
       "3                              wood frame (5)         NaN                 3.0   \n",
       "4                                         NaN         NaN                 6.0   \n",
       "...                                       ...         ...                 ...   \n",
       "198895                                    NaN         NaN                 NaN   \n",
       "198896                         wood frame (5)         NaN                 NaN   \n",
       "198897                                    NaN         NaN                 NaN   \n",
       "198898                                    NaN         NaN                 NaN   \n",
       "198899                                    NaN         NaN                 NaN   \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",
       "...                                     ...      ...  \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 34 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subset_nfl_data = nfl_data.loc[:, 'Street Number Suffix':'Zipcode']\n",
    "subset_nfl_data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Permit Number                                  0\n",
      "Permit Type                                    0\n",
      "Permit Type Definition                         0\n",
      "Permit Creation Date                           0\n",
      "Block                                          0\n",
      "Lot                                            0\n",
      "Street Number                                  0\n",
      "Street Number Suffix                      196684\n",
      "Street Name                                    0\n",
      "Street Suffix                               2768\n",
      "Unit                                      169421\n",
      "Unit Suffix                               196939\n",
      "Description                                  290\n",
      "Current Status                                 0\n",
      "Current Status Date                            0\n",
      "Filed Date                                     0\n",
      "Issued Date                                14940\n",
      "Completed Date                            101709\n",
      "First Construction Document Date           14946\n",
      "Structural Notification                   191978\n",
      "Number of Existing Stories                 42784\n",
      "Number of Proposed Stories                 42868\n",
      "Voluntary Soft-Story Retrofit             198865\n",
      "Fire Only Permit                          180073\n",
      "Permit Expiration Date                     51880\n",
      "Estimated Cost                             38066\n",
      "Revised Cost                                6066\n",
      "Existing Use                               41114\n",
      "Existing Units                             51538\n",
      "Proposed Use                               42439\n",
      "Proposed Units                             50911\n",
      "Plansets                                   37309\n",
      "TIDF Compliance                           198898\n",
      "Existing Construction Type                 43366\n",
      "Existing Construction Type Description     43366\n",
      "Proposed Construction Type                 43162\n",
      "Proposed Construction Type Description     43162\n",
      "Site Permit                               193541\n",
      "Supervisor District                         1717\n",
      "Neighborhoods - Analysis Boundaries         1725\n",
      "Zipcode                                     1716\n",
      "Location                                    1700\n",
      "Record ID                                      0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "missing_values_subset_nfl_data = nfl_data.isnull().sum()\n",
    "\n",
    "# look at the # of missing points in the first ten columns\n",
    "print(missing_values_subset_nfl_data)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "it is obvious from the results we got that the Street Number Suffix is missing in a huge number of rows which is 196684,while the zipcode is missed in only 1716 rows. this gives an intuation that the street number suffix is missed because it does not exist for these adresses, while the zipcode can not be missed for a specific address so it implies that it is missed because it was not recorded.\n"
     ]
    }
   ],
   "source": [
    "print(\"it is obvious from the results we got that the Street Number Suffix is missing in a huge number of rows which is 196684,while the zipcode is missed in only 1716 rows. \\\n",
    "this gives an intuation that the street number suffix is missed because it does not exist for these adresses, while the zipcode can not be missed for a specific address so it implies that it is missed because it was not recorded.\")"
   ]
  },
  {
   "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": 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>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",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Permit Number, Permit Type, Permit Type Definition, Permit Creation Date, Block, Lot, Street Number, Street Number Suffix, Street Name, Street Suffix, Unit, Unit Suffix, Description, Current Status, Current Status Date, Filed Date, Issued Date, Completed Date, First Construction Document Date, Structural Notification, Number of Existing Stories, Number of Proposed Stories, Voluntary Soft-Story Retrofit, Fire Only Permit, Permit Expiration Date, Estimated Cost, Revised Cost, Existing Use, Existing Units, Proposed Use, Proposed Units, Plansets, TIDF Compliance, Existing Construction Type, Existing Construction Type Description, Proposed Construction Type, Proposed Construction Type Description, Site Permit, Supervisor District, Neighborhoods - Analysis Boundaries, Zipcode, Location, Record ID]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 43 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns in original dataset: 43 \n",
      "\n",
      "Columns with na's dropped: 12\n"
     ]
    }
   ],
   "source": [
    "\n",
    "columns_with_na_dropped = nfl_data.dropna(axis=1) # axis 1 indicates operation along columns\n",
    "columns_with_na_dropped.head()\n",
    "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": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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 Name</th>\n",
       "      <th>Current Status</th>\n",
       "      <th>Current Status Date</th>\n",
       "      <th>Filed Date</th>\n",
       "      <th>Record ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\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>Ellis</td>\n",
       "      <td>expired</td>\n",
       "      <td>12/21/2017</td>\n",
       "      <td>05/06/2015</td>\n",
       "      <td>1380611233945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\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>Geary</td>\n",
       "      <td>issued</td>\n",
       "      <td>08/03/2017</td>\n",
       "      <td>04/19/2016</td>\n",
       "      <td>1420164406718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\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>Pacific</td>\n",
       "      <td>withdrawn</td>\n",
       "      <td>09/26/2017</td>\n",
       "      <td>05/27/2016</td>\n",
       "      <td>1424856504716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\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>Pacific</td>\n",
       "      <td>complete</td>\n",
       "      <td>07/24/2017</td>\n",
       "      <td>11/07/2016</td>\n",
       "      <td>1443574295566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\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>Market</td>\n",
       "      <td>issued</td>\n",
       "      <td>12/01/2017</td>\n",
       "      <td>11/28/2016</td>\n",
       "      <td>144548169992</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\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",
       "\n",
       "  Permit Creation Date Block  Lot  Street Number Street Name Current Status  \\\n",
       "0           05/06/2015  0326  023            140       Ellis        expired   \n",
       "1           04/19/2016  0306  007            440       Geary         issued   \n",
       "2           05/27/2016  0595  203           1647     Pacific      withdrawn   \n",
       "3           11/07/2016  0156  011           1230     Pacific       complete   \n",
       "4           11/28/2016  0342  001            950      Market         issued   \n",
       "\n",
       "  Current Status Date  Filed Date      Record ID  \n",
       "0          12/21/2017  05/06/2015  1380611233945  \n",
       "1          08/03/2017  04/19/2016  1420164406718  \n",
       "2          09/26/2017  05/27/2016  1424856504716  \n",
       "3          07/24/2017  11/07/2016  1443574295566  \n",
       "4          12/01/2017  11/28/2016   144548169992  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_with_na_dropped.head()\n"
   ]
  },
  {
   "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": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "      <td>0</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>1</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>2</td>\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>NaN</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>NaN</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",
       "      <td>3</td>\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>NaN</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>NaN</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",
       "      <td>4</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 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",
       "\n",
       "  Permit Creation Date Block  Lot  Street Number Street Number Suffix  \\\n",
       "0           05/06/2015  0326  023            140                  NaN   \n",
       "1           04/19/2016  0306  007            440                  NaN   \n",
       "2           05/27/2016  0595  203           1647                  NaN   \n",
       "3           11/07/2016  0156  011           1230                  NaN   \n",
       "4           11/28/2016  0342  001            950                  NaN   \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",
       "\n",
       "  Existing Construction Type Description Proposed Construction Type  \\\n",
       "0                          constr type 3                        NaN   \n",
       "1                          constr type 3                        NaN   \n",
       "2                          constr type 1                        1.0   \n",
       "3                         wood frame (5)                        5.0   \n",
       "4                          constr type 3                        NaN   \n",
       "\n",
       "  Proposed Construction Type Description Site Permit Supervisor District  \\\n",
       "0                                    NaN         NaN                 3.0   \n",
       "1                                    NaN         NaN                 3.0   \n",
       "2                          constr type 1         NaN                 3.0   \n",
       "3                         wood frame (5)         NaN                 3.0   \n",
       "4                                    NaN         NaN                 6.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",
       "\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",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>198895</td>\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",
       "      <td>198896</td>\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",
       "      <td>198897</td>\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",
       "      <td>198898</td>\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",
       "      <td>198899</td>\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",
       "...              ...          ...                               ...   \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",
       "...                     ...   ...   ...            ...                  ...   \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",
       "...            ...           ...  ...                         ...   \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",
       "...                                       ...                        ...   \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",
       "...                                       ...         ...                 ...   \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",
       "...                                    ...      ...   \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",
       "...                                           ...            ...  \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": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data_with_no_missing_values=nfl_data.fillna(method = 'bfill', axis=0).fillna(0)\n",
    "nfl_data_with_no_missing_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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",
       "      <td>0</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>1</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>2</td>\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>NaN</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>NaN</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",
       "      <td>3</td>\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>NaN</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>NaN</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",
       "      <td>4</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 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",
       "\n",
       "  Permit Creation Date Block  Lot  Street Number Street Number Suffix  \\\n",
       "0           05/06/2015  0326  023            140                  NaN   \n",
       "1           04/19/2016  0306  007            440                  NaN   \n",
       "2           05/27/2016  0595  203           1647                  NaN   \n",
       "3           11/07/2016  0156  011           1230                  NaN   \n",
       "4           11/28/2016  0342  001            950                  NaN   \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",
       "\n",
       "  Existing Construction Type Description Proposed Construction Type  \\\n",
       "0                          constr type 3                        NaN   \n",
       "1                          constr type 3                        NaN   \n",
       "2                          constr type 1                        1.0   \n",
       "3                         wood frame (5)                        5.0   \n",
       "4                          constr type 3                        NaN   \n",
       "\n",
       "  Proposed Construction Type Description Site Permit Supervisor District  \\\n",
       "0                                    NaN         NaN                 3.0   \n",
       "1                                    NaN         NaN                 3.0   \n",
       "2                          constr type 1         NaN                 3.0   \n",
       "3                         wood frame (5)         NaN                 3.0   \n",
       "4                                    NaN         NaN                 6.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",
       "\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",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " nfl_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "      <td>0</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>1</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>2</td>\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>NaN</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>NaN</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",
       "      <td>3</td>\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>NaN</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>NaN</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",
       "      <td>4</td>\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>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 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",
       "\n",
       "  Permit Creation Date Block  Lot  Street Number Street Number Suffix  \\\n",
       "0           05/06/2015  0326  023            140                  NaN   \n",
       "1           04/19/2016  0306  007            440                  NaN   \n",
       "2           05/27/2016  0595  203           1647                  NaN   \n",
       "3           11/07/2016  0156  011           1230                  NaN   \n",
       "4           11/28/2016  0342  001            950                  NaN   \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",
       "\n",
       "  Existing Construction Type Description Proposed Construction Type  \\\n",
       "0                          constr type 3                        NaN   \n",
       "1                          constr type 3                        NaN   \n",
       "2                          constr type 1                        1.0   \n",
       "3                         wood frame (5)                        5.0   \n",
       "4                          constr type 3                        NaN   \n",
       "\n",
       "  Proposed Construction Type Description Site Permit Supervisor District  \\\n",
       "0                                    NaN         NaN                 3.0   \n",
       "1                                    NaN         NaN                 3.0   \n",
       "2                          constr type 1         NaN                 3.0   \n",
       "3                         wood frame (5)         NaN                 3.0   \n",
       "4                                    NaN         NaN                 6.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",
       "\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",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data.head()"
   ]
  },
  {
   "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": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "nfl_data_with_no_missing_values.to_csv(r'C:\\Users\\hp\\Desktop\\export_data.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": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# read in all our data\n",
    "nfl_data = pd.read_csv(\"data\\pima-indians-diabetes.data.csv\",header=None)\n",
    "\n",
    "# set seed for reproducibility\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": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.describe of       0    1   2   3    4     5      6   7  8\n",
       "0     6  148  72  35    0  33.6  0.627  50  1\n",
       "1     1   85  66  29    0  26.6  0.351  31  0\n",
       "2     8  183  64   0    0  23.3  0.672  32  1\n",
       "3     1   89  66  23   94  28.1  0.167  21  0\n",
       "4     0  137  40  35  168  43.1  2.288  33  1\n",
       "..   ..  ...  ..  ..  ...   ...    ...  .. ..\n",
       "763  10  101  76  48  180  32.9  0.171  63  0\n",
       "764   2  122  70  27    0  36.8  0.340  27  0\n",
       "765   5  121  72  23  112  26.2  0.245  30  0\n",
       "766   1  126  60   0    0  30.1  0.349  47  1\n",
       "767   1   93  70  31    0  30.4  0.315  23  0\n",
       "\n",
       "[768 rows x 9 columns]>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_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": 36,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>763</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>764</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>765</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>766</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>767</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>768 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         0      1      2      3      4      5      6      7      8\n",
       "0    False  False  False  False   True  False  False  False  False\n",
       "1    False  False  False  False   True  False  False  False   True\n",
       "2    False  False  False   True   True  False  False  False  False\n",
       "3    False  False  False  False  False  False  False  False   True\n",
       "4     True  False  False  False  False  False  False  False  False\n",
       "..     ...    ...    ...    ...    ...    ...    ...    ...    ...\n",
       "763  False  False  False  False  False  False  False  False   True\n",
       "764  False  False  False  False   True  False  False  False   True\n",
       "765  False  False  False  False  False  False  False  False   True\n",
       "766  False  False  False   True   True  False  False  False  False\n",
       "767  False  False  False  False   True  False  False  False   True\n",
       "\n",
       "[768 rows x 9 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data==0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "nfl_data.columns=['Number of times pregnant','Plasma glucose concentration a 2 hours in an oral glucose tolerance test','Diastolic blood pressure','Triceps skinfold thickness','2-Hour serum insulin','Body mass index','Diabetes pedigree function','Age','Class variable']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Number of times pregnant</th>\n",
       "      <th>Plasma glucose concentration a 2 hours in an oral glucose tolerance test</th>\n",
       "      <th>Diastolic blood pressure</th>\n",
       "      <th>Triceps skinfold thickness</th>\n",
       "      <th>2-Hour serum insulin</th>\n",
       "      <th>Body mass index</th>\n",
       "      <th>Diabetes pedigree function</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>148</td>\n",
       "      <td>72</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>33.6</td>\n",
       "      <td>0.627</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>85</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>26.6</td>\n",
       "      <td>0.351</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>183</td>\n",
       "      <td>64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23.3</td>\n",
       "      <td>0.672</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "      <td>66</td>\n",
       "      <td>23</td>\n",
       "      <td>94</td>\n",
       "      <td>28.1</td>\n",
       "      <td>0.167</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>40</td>\n",
       "      <td>35</td>\n",
       "      <td>168</td>\n",
       "      <td>43.1</td>\n",
       "      <td>2.288</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Number of times pregnant  \\\n",
       "0                         6   \n",
       "1                         1   \n",
       "2                         8   \n",
       "3                         1   \n",
       "4                         0   \n",
       "\n",
       "   Plasma glucose concentration a 2 hours in an oral glucose tolerance test  \\\n",
       "0                                                148                          \n",
       "1                                                 85                          \n",
       "2                                                183                          \n",
       "3                                                 89                          \n",
       "4                                                137                          \n",
       "\n",
       "   Diastolic blood pressure  Triceps skinfold thickness  2-Hour serum insulin  \\\n",
       "0                        72                          35                     0   \n",
       "1                        66                          29                     0   \n",
       "2                        64                           0                     0   \n",
       "3                        66                          23                    94   \n",
       "4                        40                          35                   168   \n",
       "\n",
       "   Body mass index  Diabetes pedigree function  Age  Class variable  \n",
       "0             33.6                       0.627   50               1  \n",
       "1             26.6                       0.351   31               0  \n",
       "2             23.3                       0.672   32               1  \n",
       "3             28.1                       0.167   21               0  \n",
       "4             43.1                       2.288   33               1  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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 a 2 hours in an oral glucose tolerance test</th>\n",
       "      <th>Diastolic blood pressure</th>\n",
       "      <th>Triceps skinfold thickness</th>\n",
       "      <th>2-Hour serum insulin</th>\n",
       "      <th>Body mass index</th>\n",
       "      <th>Diabetes pedigree function</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>763</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>764</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>765</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>766</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>767</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>768 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Number of times pregnant  \\\n",
       "0                       False   \n",
       "1                       False   \n",
       "2                       False   \n",
       "3                       False   \n",
       "4                        True   \n",
       "..                        ...   \n",
       "763                     False   \n",
       "764                     False   \n",
       "765                     False   \n",
       "766                     False   \n",
       "767                     False   \n",
       "\n",
       "     Plasma glucose concentration a 2 hours in an oral glucose tolerance test  \\\n",
       "0                                                False                          \n",
       "1                                                False                          \n",
       "2                                                False                          \n",
       "3                                                False                          \n",
       "4                                                False                          \n",
       "..                                                 ...                          \n",
       "763                                              False                          \n",
       "764                                              False                          \n",
       "765                                              False                          \n",
       "766                                              False                          \n",
       "767                                              False                          \n",
       "\n",
       "     Diastolic blood pressure  Triceps skinfold thickness  \\\n",
       "0                       False                       False   \n",
       "1                       False                       False   \n",
       "2                       False                        True   \n",
       "3                       False                       False   \n",
       "4                       False                       False   \n",
       "..                        ...                         ...   \n",
       "763                     False                       False   \n",
       "764                     False                       False   \n",
       "765                     False                       False   \n",
       "766                     False                        True   \n",
       "767                     False                       False   \n",
       "\n",
       "     2-Hour serum insulin  Body mass index  Diabetes pedigree function    Age  \\\n",
       "0                    True            False                       False  False   \n",
       "1                    True            False                       False  False   \n",
       "2                    True            False                       False  False   \n",
       "3                   False            False                       False  False   \n",
       "4                   False            False                       False  False   \n",
       "..                    ...              ...                         ...    ...   \n",
       "763                 False            False                       False  False   \n",
       "764                  True            False                       False  False   \n",
       "765                 False            False                       False  False   \n",
       "766                  True            False                       False  False   \n",
       "767                  True            False                       False  False   \n",
       "\n",
       "     Class variable  \n",
       "0             False  \n",
       "1              True  \n",
       "2             False  \n",
       "3              True  \n",
       "4             False  \n",
       "..              ...  \n",
       "763            True  \n",
       "764            True  \n",
       "765            True  \n",
       "766           False  \n",
       "767            True  \n",
       "\n",
       "[768 rows x 9 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data==0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "From the previous table we can see some values that are zeros for coloumns that does not make sense to be zero,for example the triceps skinfold thickness is unlogical to be zero , also the 2-hour serum insulin\n"
     ]
    }
   ],
   "source": [
    "print(\"From the previous table we can see some values that are zeros for coloumns that does not make sense to be zero,\\\n",
    "for example the triceps skinfold thickness is unlogical to be zero , also the 2-hour serum insulin\")"
   ]
  },
  {
   "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": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns in original dataset: 9 \n",
      "\n",
      "Columns with na's dropped: 9\n"
     ]
    }
   ],
   "source": [
    "missing_values_count_nfl = nfl_data.isnull().sum()\n",
    "nfl_data.dropna()\n",
    "columns_with_na_dropped = nfl_data.dropna(axis=1) # axis 1 indicates operation along columns\n",
    "columns_with_na_dropped.head()\n",
    "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": "code",
   "execution_count": 44,
   "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 a 2 hours in an oral glucose tolerance test</th>\n",
       "      <th>Diastolic blood pressure</th>\n",
       "      <th>Triceps skinfold thickness</th>\n",
       "      <th>2-Hour serum insulin</th>\n",
       "      <th>Body mass index</th>\n",
       "      <th>Diabetes pedigree function</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>148</td>\n",
       "      <td>72</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>33.6</td>\n",
       "      <td>0.627</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>85</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>26.6</td>\n",
       "      <td>0.351</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>183</td>\n",
       "      <td>64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23.3</td>\n",
       "      <td>0.672</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "      <td>66</td>\n",
       "      <td>23</td>\n",
       "      <td>94</td>\n",
       "      <td>28.1</td>\n",
       "      <td>0.167</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>40</td>\n",
       "      <td>35</td>\n",
       "      <td>168</td>\n",
       "      <td>43.1</td>\n",
       "      <td>2.288</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Number of times pregnant  \\\n",
       "0                         6   \n",
       "1                         1   \n",
       "2                         8   \n",
       "3                         1   \n",
       "4                         0   \n",
       "\n",
       "   Plasma glucose concentration a 2 hours in an oral glucose tolerance test  \\\n",
       "0                                                148                          \n",
       "1                                                 85                          \n",
       "2                                                183                          \n",
       "3                                                 89                          \n",
       "4                                                137                          \n",
       "\n",
       "   Diastolic blood pressure  Triceps skinfold thickness  2-Hour serum insulin  \\\n",
       "0                        72                          35                     0   \n",
       "1                        66                          29                     0   \n",
       "2                        64                           0                     0   \n",
       "3                        66                          23                    94   \n",
       "4                        40                          35                   168   \n",
       "\n",
       "   Body mass index  Diabetes pedigree function  Age  Class variable  \n",
       "0             33.6                       0.627   50               1  \n",
       "1             26.6                       0.351   31               0  \n",
       "2             23.3                       0.672   32               1  \n",
       "3             28.1                       0.167   21               0  \n",
       "4             43.1                       2.288   33               1  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_with_na_dropped.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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 a 2 hours in an oral glucose tolerance test</th>\n",
       "      <th>Diastolic blood pressure</th>\n",
       "      <th>Triceps skinfold thickness</th>\n",
       "      <th>2-Hour serum insulin</th>\n",
       "      <th>Body mass index</th>\n",
       "      <th>Diabetes pedigree function</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>148</td>\n",
       "      <td>72</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>33.6</td>\n",
       "      <td>0.627</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>85</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>26.6</td>\n",
       "      <td>0.351</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>183</td>\n",
       "      <td>64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23.3</td>\n",
       "      <td>0.672</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "      <td>66</td>\n",
       "      <td>23</td>\n",
       "      <td>94</td>\n",
       "      <td>28.1</td>\n",
       "      <td>0.167</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>40</td>\n",
       "      <td>35</td>\n",
       "      <td>168</td>\n",
       "      <td>43.1</td>\n",
       "      <td>2.288</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>763</td>\n",
       "      <td>10</td>\n",
       "      <td>101</td>\n",
       "      <td>76</td>\n",
       "      <td>48</td>\n",
       "      <td>180</td>\n",
       "      <td>32.9</td>\n",
       "      <td>0.171</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>764</td>\n",
       "      <td>2</td>\n",
       "      <td>122</td>\n",
       "      <td>70</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>36.8</td>\n",
       "      <td>0.340</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>765</td>\n",
       "      <td>5</td>\n",
       "      <td>121</td>\n",
       "      <td>72</td>\n",
       "      <td>23</td>\n",
       "      <td>112</td>\n",
       "      <td>26.2</td>\n",
       "      <td>0.245</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>766</td>\n",
       "      <td>1</td>\n",
       "      <td>126</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.1</td>\n",
       "      <td>0.349</td>\n",
       "      <td>47</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>767</td>\n",
       "      <td>1</td>\n",
       "      <td>93</td>\n",
       "      <td>70</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>30.4</td>\n",
       "      <td>0.315</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>768 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Number of times pregnant  \\\n",
       "0                           6   \n",
       "1                           1   \n",
       "2                           8   \n",
       "3                           1   \n",
       "4                           0   \n",
       "..                        ...   \n",
       "763                        10   \n",
       "764                         2   \n",
       "765                         5   \n",
       "766                         1   \n",
       "767                         1   \n",
       "\n",
       "     Plasma glucose concentration a 2 hours in an oral glucose tolerance test  \\\n",
       "0                                                  148                          \n",
       "1                                                   85                          \n",
       "2                                                  183                          \n",
       "3                                                   89                          \n",
       "4                                                  137                          \n",
       "..                                                 ...                          \n",
       "763                                                101                          \n",
       "764                                                122                          \n",
       "765                                                121                          \n",
       "766                                                126                          \n",
       "767                                                 93                          \n",
       "\n",
       "     Diastolic blood pressure  Triceps skinfold thickness  \\\n",
       "0                          72                          35   \n",
       "1                          66                          29   \n",
       "2                          64                           0   \n",
       "3                          66                          23   \n",
       "4                          40                          35   \n",
       "..                        ...                         ...   \n",
       "763                        76                          48   \n",
       "764                        70                          27   \n",
       "765                        72                          23   \n",
       "766                        60                           0   \n",
       "767                        70                          31   \n",
       "\n",
       "     2-Hour serum insulin  Body mass index  Diabetes pedigree function  Age  \\\n",
       "0                       0             33.6                       0.627   50   \n",
       "1                       0             26.6                       0.351   31   \n",
       "2                       0             23.3                       0.672   32   \n",
       "3                      94             28.1                       0.167   21   \n",
       "4                     168             43.1                       2.288   33   \n",
       "..                    ...              ...                         ...  ...   \n",
       "763                   180             32.9                       0.171   63   \n",
       "764                     0             36.8                       0.340   27   \n",
       "765                   112             26.2                       0.245   30   \n",
       "766                     0             30.1                       0.349   47   \n",
       "767                     0             30.4                       0.315   23   \n",
       "\n",
       "     Class variable  \n",
       "0                 1  \n",
       "1                 0  \n",
       "2                 1  \n",
       "3                 0  \n",
       "4                 1  \n",
       "..              ...  \n",
       "763               0  \n",
       "764               0  \n",
       "765               0  \n",
       "766               1  \n",
       "767               0  \n",
       "\n",
       "[768 rows x 9 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nfl_data_with_no_missing_values=nfl_data.fillna(method = 'bfill', axis=0).fillna(0)\n",
    "nfl_data_with_no_missing_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "nfl_data_with_no_missing_values.to_csv(r'C:\\Users\\hp\\Desktop\\export_data2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}