4_wrangling_part1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas Essentials:  Data Wrangling\n",
    "\n",
    "This Pandas Notebook illustrates the essentials of \"wrangling\" your data.  We focus on three months of data from the [Bay Area Bike Share](http://www.bayareabikeshare.com/open-data) program.\n",
    "\n",
    "Topics include:\n",
    "\n",
    "* Transforming Datatypes\n",
    "* Dropping Columns\n",
    "* Renaming Columns\n",
    "* Dealing with NA Values\n",
    "* Dealing with Duplicate Records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading the Bike Share Data Sets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get started, let's load the three data files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read in the Station Data\n",
    "stations_df = pd.read_csv(\"data/babs_station_data.csv\")\n",
    "\n",
    "# Read in the Bike Share Weather Data\n",
    "weather_df = pd.read_csv(\"data/babs_weather_april_thru_june_2016.csv\")\n",
    "\n",
    "# Read in the trips data\n",
    "trips_df = pd.read_csv(\"data/babs_trips_april_thru_june_2016.csv\")\n",
    "\n",
    "# Set max display columns and rows\n",
    "pd.options.display.max_columns = 10\n",
    "pd.options.display.max_rows = 8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transforming Data Types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, let's transform the data types associated with specific columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 83537 entries, 0 to 83536\n",
      "Data columns (total 11 columns):\n",
      "Trip ID            83537 non-null int64\n",
      "Duration           83537 non-null int64\n",
      "Start Date         83537 non-null object\n",
      "Start Station      83537 non-null object\n",
      "Start Terminal     83537 non-null int64\n",
      "End Date           83537 non-null object\n",
      "End Station        83537 non-null object\n",
      "End Terminal       83537 non-null int64\n",
      "Bike #             83537 non-null int64\n",
      "Subscriber Type    83537 non-null object\n",
      "Zip Code           83509 non-null object\n",
      "dtypes: int64(5), object(6)\n",
      "memory usage: 7.0+ MB\n"
     ]
    }
   ],
   "source": [
    "# Before any transformation, these are our trips data types\n",
    "trips_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Convert Trip ID to a String\n",
    "trips_df[\"Trip ID\"] = trips_df[\"Trip ID\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Convert Start Date and End Date to Date Time objects.\n",
    "trips_df[\"Start Date\"] = pd.to_datetime(trips_df[\"Start Date\"])\n",
    "trips_df[\"End Date\"] = pd.to_datetime(trips_df[\"End Date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.tseries.common.DatetimeProperties"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can also use the Series dt attribute to extract specific data/time elements.\n",
    "# Confirm that we are dealing with DatetimeProperties object.\n",
    "type(trips_df[\"Start Date\"].dt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# For example, we can extract just the date, just the month or just the day of week\n",
    "# Very useful for different types of analyses.\n",
    "# For example, now we can compare weekday trips v. weekend trips.\n",
    "trips_df[\"Start Date Only\"] = trips_df[\"Start Date\"].dt.date\n",
    "trips_df[\"Start Date Month\"] = trips_df[\"Start Date\"].dt.month\n",
    "trips_df[\"Start Date Day of Week\"] = trips_df[\"Start Date\"].dt.dayofweek"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify that all changes have taken place."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 83537 entries, 0 to 83536\n",
      "Data columns (total 14 columns):\n",
      "Trip ID                   83537 non-null object\n",
      "Duration                  83537 non-null int64\n",
      "Start Date                83537 non-null datetime64[ns]\n",
      "Start Station             83537 non-null object\n",
      "Start Terminal            83537 non-null int64\n",
      "End Date                  83537 non-null datetime64[ns]\n",
      "End Station               83537 non-null object\n",
      "End Terminal              83537 non-null int64\n",
      "Bike #                    83537 non-null int64\n",
      "Subscriber Type           83537 non-null object\n",
      "Zip Code                  83509 non-null object\n",
      "Start Date Only           83537 non-null object\n",
      "Start Date Month          83537 non-null int64\n",
      "Start Date Day of Week    83537 non-null int64\n",
      "dtypes: datetime64[ns](2), int64(6), object(6)\n",
      "memory usage: 8.9+ MB\n"
     ]
    }
   ],
   "source": [
    "trips_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dropping Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To prepare your data for analysis, it is frequently helpful to drop non-essential columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'station_id', u'name', u'lat', u'long', u'dockcount', u'landmark',\n",
       "       u'installation'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For example, here is the original set of bike station columns\n",
    "stations_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'station_id', u'name', u'dockcount', u'landmark'], dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop a few columns which are not essential to our analysis\n",
    "stations_df.drop(labels=[\"installation\"], axis=\"columns\", inplace=True)\n",
    "stations_df.drop(labels=[\"lat\", \"long\"], axis=\"columns\", inplace=True)\n",
    "\n",
    "# Verify that the columns have indeed been dropped\n",
    "stations_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_id</th>\n",
       "      <th>name</th>\n",
       "      <th>dockcount</th>\n",
       "      <th>landmark</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>San Jose Diridon Caltrain Station</td>\n",
       "      <td>27</td>\n",
       "      <td>San Jose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Santa Clara at Almaden</td>\n",
       "      <td>11</td>\n",
       "      <td>San Jose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>San Pedro Square</td>\n",
       "      <td>15</td>\n",
       "      <td>San Jose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>Paseo de San Antonio</td>\n",
       "      <td>15</td>\n",
       "      <td>San Jose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>30</td>\n",
       "      <td>Middlefield Light Rail Station</td>\n",
       "      <td>15</td>\n",
       "      <td>Mountain View</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>31</td>\n",
       "      <td>San Antonio Shopping Center</td>\n",
       "      <td>15</td>\n",
       "      <td>Mountain View</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>32</td>\n",
       "      <td>Castro Street and El Camino Real</td>\n",
       "      <td>11</td>\n",
       "      <td>Mountain View</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>33</td>\n",
       "      <td>Charleston Park/ North Bayshore Area</td>\n",
       "      <td>15</td>\n",
       "      <td>Mountain View</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    station_id                                  name  dockcount       landmark\n",
       "0            2     San Jose Diridon Caltrain Station         27       San Jose\n",
       "2            4                Santa Clara at Almaden         11       San Jose\n",
       "4            6                      San Pedro Square         15       San Jose\n",
       "5            7                  Paseo de San Antonio         15       San Jose\n",
       "..         ...                                   ...        ...            ...\n",
       "63          30        Middlefield Light Rail Station         15  Mountain View\n",
       "64          31           San Antonio Shopping Center         15  Mountain View\n",
       "65          32      Castro Street and El Camino Real         11  Mountain View\n",
       "66          33  Charleston Park/ North Bayshore Area         15  Mountain View\n",
       "\n",
       "[65 rows x 4 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You can also drop rows.  For example:\n",
    "stations_df.drop(axis=\"index\", labels=[1,3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Renaming columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To prepare your data for analysis, it is also frequently helpful to rename columns, to make them easier to understand or more consistent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'Trip ID', u'Duration', u'Start Date', u'Start Station',\n",
       "       u'Start Terminal', u'End Date', u'End Station', u'End Terminal',\n",
       "       u'Bike #', u'Subscriber Type', u'Zip Code', u'Start Date Only',\n",
       "       u'Start Date Month', u'Start Date Day of Week'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For example, here are the original columns in the trips_df data frame.\n",
    "trips_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 83537 entries, 0 to 83536\n",
      "Data columns (total 14 columns):\n",
      "Trip ID                   83537 non-null object\n",
      "Duration Seconds          83537 non-null int64\n",
      "Start Datetime            83537 non-null datetime64[ns]\n",
      "Start Station             83537 non-null object\n",
      "Start Terminal            83537 non-null int64\n",
      "End Datetime              83537 non-null datetime64[ns]\n",
      "End Station               83537 non-null object\n",
      "End Terminal              83537 non-null int64\n",
      "Bike #                    83537 non-null int64\n",
      "Subscriber Type           83537 non-null object\n",
      "Zip Code                  83509 non-null object\n",
      "Start Date Only           83537 non-null object\n",
      "Start Date Month          83537 non-null int64\n",
      "Start Date Day of Week    83537 non-null int64\n",
      "dtypes: datetime64[ns](2), int64(6), object(6)\n",
      "memory usage: 8.9+ MB\n"
     ]
    }
   ],
   "source": [
    "# We can rename a few columns to make them more explicit\n",
    "trips_df.rename(columns = {'Duration':'Duration Seconds'}, inplace=True)\n",
    "trips_df.rename(columns = {'Start Date':'Start Datetime'}, inplace=True)\n",
    "trips_df.rename(columns = {'End Date':'End Datetime'}, inplace=True)\n",
    "\n",
    "# Verify that changes have taken effect\n",
    "trips_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'TRIP_ID', u'DURATION_SECONDS', u'START_DATETIME', u'START_STATION',\n",
       "       u'START_TERMINAL', u'END_DATETIME', u'END_STATION', u'END_TERMINAL',\n",
       "       u'BIKE_#', u'SUBSCRIBER_TYPE', u'ZIP_CODE', u'START_DATE_ONLY',\n",
       "       u'START_DATE_MONTH', u'START_DATE_DAY_OF_WEEK'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You can also normalize all columns\n",
    "# In this case, replace all spaces with underscores and make upper case\n",
    "# This is very useful, as we can now reference all columns via DOT notation\n",
    "trips_df.columns = trips_df.columns.str.replace(\" \", \"_\")\n",
    "trips_df.columns = trips_df.columns.str.upper()\n",
    "\n",
    "# Verify changes have taken effect\n",
    "trips_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        60\n",
       "1        55\n",
       "2        67\n",
       "3        74\n",
       "         ..\n",
       "83533    63\n",
       "83534    41\n",
       "83535    58\n",
       "83536    58\n",
       "Name: START_TERMINAL, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can now use DOT notation for all columns\n",
    "trips_df.START_TERMINAL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with NA Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before doing an analysis, it's extremely important to understand \"holes\" in your data frame.  These \"holes\" are denoted as NaNs within Pandas data frames.  To illustrate, let's load up a corrupted version of the bike station file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Read in the Corroupted Station Data File\n",
    "# I have manually corrupted the file by deleting fields, etc.\n",
    "stations_corrupted_df = pd.read_csv(\"data/babs_station_data_corrupted.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_id</th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>dockcount</th>\n",
       "      <th>landmark</th>\n",
       "      <th>installation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.0</td>\n",
       "      <td>San Jose Diridon Caltrain Station</td>\n",
       "      <td>37.329732</td>\n",
       "      <td>-121.901782</td>\n",
       "      <td>27.0</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>San Jose Civic Center</td>\n",
       "      <td>37.330698</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/5/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Santa Clara at Almaden</td>\n",
       "      <td>3.333988</td>\n",
       "      <td>-121.894902</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Santa Clara at Almaden</td>\n",
       "      <td>39.333988</td>\n",
       "      <td>-124.894902</td>\n",
       "      <td>11.0</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/16/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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",
       "</div>"
      ],
      "text/plain": [
       "   station_id                               name        lat        long  \\\n",
       "0         2.0  San Jose Diridon Caltrain Station  37.329732 -121.901782   \n",
       "1         3.0              San Jose Civic Center  37.330698         NaN   \n",
       "2         4.0             Santa Clara at Almaden   3.333988 -121.894902   \n",
       "3         4.0             Santa Clara at Almaden  39.333988 -124.894902   \n",
       "4         NaN                                NaN        NaN         NaN   \n",
       "\n",
       "   dockcount  landmark installation  \n",
       "0       27.0  San Jose       8/6/13  \n",
       "1       15.0  San Jose       8/5/13  \n",
       "2        NaN  San Jose       8/6/13  \n",
       "3       11.0  San Jose      8/16/13  \n",
       "4        NaN       NaN          NaN  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Peak at the data set\n",
    "stations_corrupted_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_id</th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>dockcount</th>\n",
       "      <th>landmark</th>\n",
       "      <th>installation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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",
       "      <th>1</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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",
       "      <th>4</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  station_id   name    lat   long dockcount landmark installation\n",
       "0      False  False  False  False     False    False        False\n",
       "1      False  False  False   True     False    False        False\n",
       "2      False  False  False  False      True    False        False\n",
       "3      False  False  False  False     False    False        False\n",
       "4       True   True   True   True      True     True         True"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The isnull() method will return a new data frame with True/False Values\n",
    "# True indicates that the value is null.\n",
    "# For example, check out row id=4.\n",
    "pd.options.display.max_rows = 6\n",
    "stations_corrupted_df.isnull().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "station_id      3\n",
       "name            4\n",
       "lat             7\n",
       "long            8\n",
       "dockcount       6\n",
       "landmark        6\n",
       "installation    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.options.display.max_rows = 10\n",
    "# A very handy trick to determine number of NAs by columns\n",
    "stations_corrupted_df.isnull().sum(axis=\"index\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    51\n",
       "1    14\n",
       "2     5\n",
       "7     2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stations_corrupted_df.isnull().sum(axis=\"columns\").value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    70\n",
       "True      2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many rows consist of all NAs?\n",
    "stations_corrupted_df.isnull().all(axis=\"columns\").value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    51\n",
       "True     21\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many rows have 1 or more NAs?\n",
    "stations_corrupted_df.isnull().any(axis=\"columns\").value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_id</th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>dockcount</th>\n",
       "      <th>landmark</th>\n",
       "      <th>installation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\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",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>San Pedro Square</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-121.894074</td>\n",
       "      <td>15.0</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/7/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\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",
       "</div>"
      ],
      "text/plain": [
       "    station_id              name  lat        long  dockcount  landmark  \\\n",
       "4          NaN               NaN  NaN         NaN        NaN       NaN   \n",
       "6          NaN  San Pedro Square  NaN -121.894074       15.0  San Jose   \n",
       "56         NaN               NaN  NaN         NaN        NaN       NaN   \n",
       "\n",
       "   installation  \n",
       "4           NaN  \n",
       "6        8/7/13  \n",
       "56          NaN  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Which rows are missing a station_id?\n",
    "stations_corrupted_df[stations_corrupted_df.station_id.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_id</th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>dockcount</th>\n",
       "      <th>landmark</th>\n",
       "      <th>installation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Santa Clara at Almaden</td>\n",
       "      <td>3.333988</td>\n",
       "      <td>-121.894902</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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",
       "      <th>13</th>\n",
       "      <td>13.0</td>\n",
       "      <td>St James Park</td>\n",
       "      <td>37.339301</td>\n",
       "      <td>-121.889937</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14.0</td>\n",
       "      <td>Arena Green / SAP Center</td>\n",
       "      <td>37.332692</td>\n",
       "      <td>-121.900084</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/5/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16.0</td>\n",
       "      <td>SJSU - San Salvador at 9th</td>\n",
       "      <td>37.333955</td>\n",
       "      <td>-121.877349</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/7/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\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",
       "</div>"
      ],
      "text/plain": [
       "    station_id                        name        lat        long  dockcount  \\\n",
       "2          4.0      Santa Clara at Almaden   3.333988 -121.894902        NaN   \n",
       "4          NaN                         NaN        NaN         NaN        NaN   \n",
       "13        13.0               St James Park  37.339301 -121.889937        NaN   \n",
       "14        14.0    Arena Green / SAP Center  37.332692 -121.900084        NaN   \n",
       "15        16.0  SJSU - San Salvador at 9th  37.333955 -121.877349        NaN   \n",
       "56         NaN                         NaN        NaN         NaN        NaN   \n",
       "\n",
       "    landmark installation  \n",
       "2   San Jose       8/6/13  \n",
       "4        NaN          NaN  \n",
       "13  San Jose       8/6/13  \n",
       "14  San Jose       8/5/13  \n",
       "15  San Jose       8/7/13  \n",
       "56       NaN          NaN  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Which rows are missing dockcount data?\n",
    "stations_corrupted_df[stations_corrupted_df.dockcount.isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is no right answer as to how to handle NA values.  Generally, you can 1)  leave as is;  2) drop rows with NAs;  or 3) replace NAs with specific values.  Various options are illustrated below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop Example #1:  Drop all rows that contain only NAs\n",
    "stations_subset = stations_corrupted_df.dropna(axis='index', how='all')\n",
    "len(stations_corrupted_df) - len(stations_subset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop Example #2:  Drop all rows that contain >=1 NAs\n",
    "stations_subset = stations_corrupted_df.dropna(axis='index', how='any')\n",
    "len(stations_corrupted_df) - len(stations_subset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop Example #3:  Drop all rows where station_id or dockcount is NA\n",
    "stations_subset = stations_corrupted_df.dropna(axis='index', how='any',\n",
    "    subset = [\"station_id\", \"dockcount\"])\n",
    "len(stations_corrupted_df) - len(stations_subset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_id</th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>dockcount</th>\n",
       "      <th>landmark</th>\n",
       "      <th>installation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>San Jose Diridon Caltrain Station</td>\n",
       "      <td>37.3297</td>\n",
       "      <td>-121.902</td>\n",
       "      <td>27</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>San Jose Civic Center</td>\n",
       "      <td>37.3307</td>\n",
       "      <td>Not Specified</td>\n",
       "      <td>15</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/5/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Santa Clara at Almaden</td>\n",
       "      <td>3.33399</td>\n",
       "      <td>-121.895</td>\n",
       "      <td>Not Specified</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Santa Clara at Almaden</td>\n",
       "      <td>39.334</td>\n",
       "      <td>-124.895</td>\n",
       "      <td>11</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/16/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Not Specified</td>\n",
       "      <td>Not Specified</td>\n",
       "      <td>Not Specified</td>\n",
       "      <td>Not Specified</td>\n",
       "      <td>Not Specified</td>\n",
       "      <td>Not Specified</td>\n",
       "      <td>Not Specified</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      station_id                               name            lat  \\\n",
       "0              2  San Jose Diridon Caltrain Station        37.3297   \n",
       "1              3              San Jose Civic Center        37.3307   \n",
       "2              4             Santa Clara at Almaden        3.33399   \n",
       "3              4             Santa Clara at Almaden         39.334   \n",
       "4  Not Specified                      Not Specified  Not Specified   \n",
       "\n",
       "            long      dockcount       landmark   installation  \n",
       "0       -121.902             27       San Jose         8/6/13  \n",
       "1  Not Specified             15       San Jose         8/5/13  \n",
       "2       -121.895  Not Specified       San Jose         8/6/13  \n",
       "3       -124.895             11       San Jose        8/16/13  \n",
       "4  Not Specified  Not Specified  Not Specified  Not Specified  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fill NA Example #1:  Change all NA values to specified String\n",
    "stations_corrupted_df.fillna(\"Not Specified\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_id</th>\n",
       "      <th>name</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>dockcount</th>\n",
       "      <th>landmark</th>\n",
       "      <th>installation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.0</td>\n",
       "      <td>San Jose Diridon Caltrain Station</td>\n",
       "      <td>37.329732</td>\n",
       "      <td>-121.901782</td>\n",
       "      <td>27.0</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>San Jose Civic Center</td>\n",
       "      <td>37.330698</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/5/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Santa Clara at Almaden</td>\n",
       "      <td>3.333988</td>\n",
       "      <td>-121.894902</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/6/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Santa Clara at Almaden</td>\n",
       "      <td>39.333988</td>\n",
       "      <td>-124.894902</td>\n",
       "      <td>11.0</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>8/16/13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-1.0</td>\n",
       "      <td>Not Specified</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",
       "</div>"
      ],
      "text/plain": [
       "   station_id                               name        lat        long  \\\n",
       "0         2.0  San Jose Diridon Caltrain Station  37.329732 -121.901782   \n",
       "1         3.0              San Jose Civic Center  37.330698         NaN   \n",
       "2         4.0             Santa Clara at Almaden   3.333988 -121.894902   \n",
       "3         4.0             Santa Clara at Almaden  39.333988 -124.894902   \n",
       "4        -1.0                      Not Specified        NaN         NaN   \n",
       "\n",
       "   dockcount  landmark installation  \n",
       "0       27.0  San Jose       8/6/13  \n",
       "1       15.0  San Jose       8/5/13  \n",
       "2        NaN  San Jose       8/6/13  \n",
       "3       11.0  San Jose      8/16/13  \n",
       "4        NaN       NaN          NaN  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fill NA Example #2:  \n",
    "# Change NA Station_Ids to -1 and\n",
    "# Change NA name to \"Not Specified\"\n",
    "stations_corrupted_df.fillna({\"station_id\":-1, \"name\":\"Not Specified\"}).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with Duplicate Records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before doing an analysis, it's also extremely important to understand if you data set contains duplicate records.  Multiple options for identifying and dropping duplicate records are shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    69\n",
       "True      3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example #1:  Identify Completely Duplicate Records\n",
    "stations_corrupted_df.duplicated().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    68\n",
       "True      4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example #2:  Identify Duplicate Records that have the same station ID and name\n",
    "stations_corrupted_df.duplicated(\n",
    "    subset=[\"station_id\", \"name\"]).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "69"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example #3:  Drop Completely Duplicate Records\n",
    "stations_sub = stations_corrupted_df.drop_duplicates()\n",
    "\n",
    "# Verify that the new data frame contains fewer records\n",
    "stations_sub.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "68"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example #4:  Drop Duplicate Records that have the same Station ID and Name\n",
    "stations_sub = stations_corrupted_df.drop_duplicates(subset=[\"station_id\", \"name\"])\n",
    "\n",
    "# Verify that the new data frame contains fewer records\n",
    "stations_sub.shape[0]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}