Data_Cleanup_and_Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Austin B-cycle Data Cleanup and Exploration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Introduction \n",
    "\n",
    "This notebook contains the clean up and exploration for the publicly available data of Austin B-Cycle, a bike-sharing program. The notebook looks at the inconsistencies in the columns caused by missing data, duplicates, typos, and other anomalies. The result is a re-organized CSV file that has data ready for analysis.\n",
    "\n",
    "### Data Extraction \n",
    "\n",
    "* The Austin B-Cycle data comes from the city of Austin Open Data Portal (AODP). The portal has API functionality through which we obtained the most current dataset. The API uses The Socrata Open Data API(SODA API) which hosts the AODP datasets. \n",
    "\n",
    "* As of early September 2018, there are a total of 991271 rows of rides. The dataset starts from the Austin B-Cycle inception in December 2013 and continues through July 2018.\n",
    "\n",
    "* Data Provided \n",
    "    * Trip ID \n",
    "    * Membership Type \n",
    "    * Bicycle ID \n",
    "    * Checkout Time \n",
    "    * Checkout Kiosk ID\n",
    "    * Checkout Kiosk\n",
    "    * Return Kiosk ID\n",
    "    * Trip Duration Minutes\n",
    "    * Month\n",
    "    * Year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Dependencies and API\n",
    "\n",
    "\n",
    "* AODP Dataset Access: https://data.austintexas.gov/Transportation-and-Mobility/Austin-B-Cycle-Trips/tyfh-5r8s\n",
    "* API Endpoint:  https://data.austintexas.gov/resource/cwi3-ckqi.json\n",
    "* API Documentation: https://dev.socrata.com/foundry/data.austintexas.gov/cwi3-ckqi\n",
    "\n",
    "To access the dataset host, install SODA API first: \n",
    "\n",
    "> pip install sodapy\n",
    "\n",
    "The script works without a token and password, as Austin B-Cycle data is a public dataset. An individual API token and password may be created to avoid throttling limits. To create a token and password visit https://data.austintexas.gov/profile/app_tokens and specify the client information below on the corresponding commented section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Uncomment the command below if sodapy is not currently installed \n",
    "#!pip install sodapy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import Dependencies\n",
    "import numpy as np \n",
    "import pandas as pd \n",
    "import matplotlib.pyplot as plt\n",
    "import csv\n",
    "import requests\n",
    "from pprint import pprint\n",
    "from sodapy import Socrata\n",
    "\n",
    "# Ignore Warnings as we are rewrititng values \n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
     ]
    }
   ],
   "source": [
    "# Unauthenticated client only works with public data sets. Note 'None'\n",
    "# in place of application token, and no username or password:\n",
    "client = Socrata(\"data.austintexas.gov\", None)\n",
    "\n",
    "# First 991271 results, returned as JSON from API / converted to Python list of\n",
    "# dictionaries by sodapy.\n",
    "results = client.get(\"cwi3-ckqi\", limit= 991271)\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "df_bike= pd.DataFrame.from_records(results)"
   ]
  },
  {
   "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>bicycle_id</th>\n",
       "      <th>checkout_date</th>\n",
       "      <th>checkout_kiosk</th>\n",
       "      <th>checkout_kiosk_id</th>\n",
       "      <th>checkout_time</th>\n",
       "      <th>membership_type</th>\n",
       "      <th>month</th>\n",
       "      <th>return_kiosk</th>\n",
       "      <th>return_kiosk_id</th>\n",
       "      <th>trip_duration_minutes</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>207</td>\n",
       "      <td>2014-10-26T00:00:00.000</td>\n",
       "      <td>West &amp; 6th St.</td>\n",
       "      <td>2537</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>Annual (San Antonio B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Rainey St @ Cummings</td>\n",
       "      <td>2707</td>\n",
       "      <td>76</td>\n",
       "      <td>9900285854</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>969</td>\n",
       "      <td>2014-10-26T00:00:00.000</td>\n",
       "      <td>Convention Center / 4th St. @ MetroRail</td>\n",
       "      <td>2498</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Pfluger Bridge @ W 2nd Street</td>\n",
       "      <td>2566</td>\n",
       "      <td>58</td>\n",
       "      <td>9900285855</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>214</td>\n",
       "      <td>2014-10-26T00:00:00.000</td>\n",
       "      <td>West &amp; 6th St.</td>\n",
       "      <td>2537</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>Annual Membership (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>8th &amp; Congress</td>\n",
       "      <td>2496</td>\n",
       "      <td>8</td>\n",
       "      <td>9900285856</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>745</td>\n",
       "      <td>2014-10-26T00:00:00.000</td>\n",
       "      <td>Zilker Park at Barton Springs &amp; William Barton...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Zilker Park at Barton Springs &amp; William Barton...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28</td>\n",
       "      <td>9900285857</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>164</td>\n",
       "      <td>2014-10-26T00:00:00.000</td>\n",
       "      <td>Bullock Museum @ Congress &amp; MLK</td>\n",
       "      <td>2538</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Convention Center/ 3rd &amp; Trinity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>9900285858</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  bicycle_id            checkout_date  \\\n",
       "0        207  2014-10-26T00:00:00.000   \n",
       "1        969  2014-10-26T00:00:00.000   \n",
       "2        214  2014-10-26T00:00:00.000   \n",
       "3        745  2014-10-26T00:00:00.000   \n",
       "4        164  2014-10-26T00:00:00.000   \n",
       "\n",
       "                                      checkout_kiosk checkout_kiosk_id  \\\n",
       "0                                     West & 6th St.              2537   \n",
       "1            Convention Center / 4th St. @ MetroRail              2498   \n",
       "2                                     West & 6th St.              2537   \n",
       "3  Zilker Park at Barton Springs & William Barton...               NaN   \n",
       "4                    Bullock Museum @ Congress & MLK              2538   \n",
       "\n",
       "  checkout_time                     membership_type month  \\\n",
       "0      13:12:00        Annual (San Antonio B-cycle)    10   \n",
       "1      13:12:00      24-Hour Kiosk (Austin B-cycle)    10   \n",
       "2      13:12:00  Annual Membership (Austin B-cycle)    10   \n",
       "3      13:12:00      24-Hour Kiosk (Austin B-cycle)    10   \n",
       "4      13:12:00      24-Hour Kiosk (Austin B-cycle)    10   \n",
       "\n",
       "                                        return_kiosk return_kiosk_id  \\\n",
       "0                               Rainey St @ Cummings            2707   \n",
       "1                      Pfluger Bridge @ W 2nd Street            2566   \n",
       "2                                     8th & Congress            2496   \n",
       "3  Zilker Park at Barton Springs & William Barton...             NaN   \n",
       "4                   Convention Center/ 3rd & Trinity             NaN   \n",
       "\n",
       "  trip_duration_minutes     trip_id  year  \n",
       "0                    76  9900285854  2014  \n",
       "1                    58  9900285855  2014  \n",
       "2                     8  9900285856  2014  \n",
       "3                    28  9900285857  2014  \n",
       "4                    15  9900285858  2014  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display the top rows of the dataframe \n",
    "df_bike.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initial Data Exploration \n",
    "\n",
    "On an initial exploration of the data, we see that the columns month, year, membership type, bicycle id, return kiosk id, and check out kiosk id are missing values. It does not necessarily mean that all the data is not present, it just needs to be extracted and reformatted. The API returns all data types as strings or Pandas objects. Currently, there are 991271 rows of information and 12 columns. During the initial exploration we discovered we were missing values in the following columns:\n",
    "\n",
    "* month \n",
    "* year \n",
    "* membership_type \n",
    "* bicycle id\n",
    "* checkout_kiosk_id \n",
    "* return_kiosk_id  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(991271, 12)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count rows and columns \n",
    "df_bike.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bicycle_id               990548\n",
       "checkout_date            991271\n",
       "checkout_kiosk           991271\n",
       "checkout_kiosk_id        972240\n",
       "checkout_time            991271\n",
       "membership_type          984960\n",
       "month                    618479\n",
       "return_kiosk             991271\n",
       "return_kiosk_id          971487\n",
       "trip_duration_minutes    991271\n",
       "trip_id                  991271\n",
       "year                     618479\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for missing values \n",
    "df_bike.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bicycle_id</th>\n",
       "      <th>checkout_date</th>\n",
       "      <th>checkout_kiosk</th>\n",
       "      <th>checkout_kiosk_id</th>\n",
       "      <th>checkout_time</th>\n",
       "      <th>membership_type</th>\n",
       "      <th>month</th>\n",
       "      <th>return_kiosk</th>\n",
       "      <th>return_kiosk_id</th>\n",
       "      <th>trip_duration_minutes</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>991266</th>\n",
       "      <td>1524</td>\n",
       "      <td>2018-07-31T00:00:00.000</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>2497</td>\n",
       "      <td>23:24:32</td>\n",
       "      <td>U.T. Student Membership</td>\n",
       "      <td>NaN</td>\n",
       "      <td>City Hall / Lavaca &amp; 2nd</td>\n",
       "      <td>2499</td>\n",
       "      <td>29</td>\n",
       "      <td>18122309</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991267</th>\n",
       "      <td>95</td>\n",
       "      <td>2018-07-31T00:00:00.000</td>\n",
       "      <td>Long Center @ South 1st &amp; Riverside</td>\n",
       "      <td>2549</td>\n",
       "      <td>23:29:21</td>\n",
       "      <td>Local365+Guest Pass</td>\n",
       "      <td>NaN</td>\n",
       "      <td>East 6th &amp; Pedernales St.</td>\n",
       "      <td>2544</td>\n",
       "      <td>18</td>\n",
       "      <td>18122312</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991268</th>\n",
       "      <td>252</td>\n",
       "      <td>2018-07-31T00:00:00.000</td>\n",
       "      <td>Rio Grande &amp; 28th</td>\n",
       "      <td>3793</td>\n",
       "      <td>23:39:16</td>\n",
       "      <td>U.T. Student Membership</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22nd &amp; Pearl</td>\n",
       "      <td>3792</td>\n",
       "      <td>4</td>\n",
       "      <td>18122330</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991269</th>\n",
       "      <td>2228</td>\n",
       "      <td>2018-07-31T00:00:00.000</td>\n",
       "      <td>Dean Keeton &amp; Whitis</td>\n",
       "      <td>3795</td>\n",
       "      <td>23:42:50</td>\n",
       "      <td>U.T. Student Membership</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nueces &amp; 26th</td>\n",
       "      <td>3838</td>\n",
       "      <td>2</td>\n",
       "      <td>18122340</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991270</th>\n",
       "      <td>576</td>\n",
       "      <td>2018-07-31T00:00:00.000</td>\n",
       "      <td>Rio Grande &amp; 28th</td>\n",
       "      <td>3793</td>\n",
       "      <td>23:44:32</td>\n",
       "      <td>U.T. Student Membership</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rio Grande &amp; 28th</td>\n",
       "      <td>3793</td>\n",
       "      <td>4</td>\n",
       "      <td>18122349</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       bicycle_id            checkout_date  \\\n",
       "991266       1524  2018-07-31T00:00:00.000   \n",
       "991267         95  2018-07-31T00:00:00.000   \n",
       "991268        252  2018-07-31T00:00:00.000   \n",
       "991269       2228  2018-07-31T00:00:00.000   \n",
       "991270        576  2018-07-31T00:00:00.000   \n",
       "\n",
       "                             checkout_kiosk checkout_kiosk_id checkout_time  \\\n",
       "991266    Capitol Station / Congress & 11th              2497      23:24:32   \n",
       "991267  Long Center @ South 1st & Riverside              2549      23:29:21   \n",
       "991268                    Rio Grande & 28th              3793      23:39:16   \n",
       "991269                 Dean Keeton & Whitis              3795      23:42:50   \n",
       "991270                    Rio Grande & 28th              3793      23:44:32   \n",
       "\n",
       "                membership_type month               return_kiosk  \\\n",
       "991266  U.T. Student Membership   NaN   City Hall / Lavaca & 2nd   \n",
       "991267      Local365+Guest Pass   NaN  East 6th & Pedernales St.   \n",
       "991268  U.T. Student Membership   NaN               22nd & Pearl   \n",
       "991269  U.T. Student Membership   NaN              Nueces & 26th   \n",
       "991270  U.T. Student Membership   NaN          Rio Grande & 28th   \n",
       "\n",
       "       return_kiosk_id trip_duration_minutes   trip_id year  \n",
       "991266            2499                    29  18122309  NaN  \n",
       "991267            2544                    18  18122312  NaN  \n",
       "991268            3792                     4  18122330  NaN  \n",
       "991269            3838                     2  18122340  NaN  \n",
       "991270            3793                     4  18122349  NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We see that the last values of our dataframe are missing the month and year\n",
    "df_bike.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 372792 missing year values.\n",
      "There are 372792 missing month values.\n",
      "There are 6311 missing membership type values.\n",
      "There are 723 missing bike id values.\n"
     ]
    }
   ],
   "source": [
    "# Count how many rows are missing year, month, membership_type, bicycle_id \n",
    "missing_year = df_bike[\"year\"].isnull().sum()\n",
    "missing_month = df_bike[\"month\"].isnull().sum()\n",
    "missing_membership_type = df_bike[\"membership_type\"].isnull().sum()\n",
    "missing_bike_id = df_bike[\"bicycle_id\"].isnull().sum()\n",
    "\n",
    "# Create summary table of the missing values \n",
    "print(f\"There are {missing_year} missing year values.\")\n",
    "print(f\"There are {missing_month} missing month values.\")\n",
    "print(f\"There are {missing_membership_type} missing membership type values.\")\n",
    "print(f\"There are {missing_bike_id} missing bike id values.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bicycle_id               object\n",
       "checkout_date            object\n",
       "checkout_kiosk           object\n",
       "checkout_kiosk_id        object\n",
       "checkout_time            object\n",
       "membership_type          object\n",
       "month                    object\n",
       "return_kiosk             object\n",
       "return_kiosk_id          object\n",
       "trip_duration_minutes    object\n",
       "trip_id                  object\n",
       "year                     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bike.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initial Data Clean Up\n",
    "\n",
    "In this section, we address the missing values found in the initial exploration.\n",
    "\n",
    "* First, we use the check_out date to extract the year, month, trip date, and trip day of the week. This is accomplished through the use of the datetime function.\n",
    "Next, we split the hour from the checkout time. This is accomplished with an anonymous function.\n",
    "Also, we change the data types of trip duration from object to integer type. After this change all the data types are ready for iteration in the analysis notebook.\n",
    "Finally, we rename the columns to further explore and understand the remaining columns of missing and conflicting values: \n",
    "\n",
    "    * membership_type\n",
    "    * checkout_kiosk_id\n",
    "    * return_kiosk_id\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming the data frame\n",
    "df_bike_clean = df_bike"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bicycle_id               990548\n",
       "checkout_date            991271\n",
       "checkout_kiosk           991271\n",
       "checkout_kiosk_id        972240\n",
       "checkout_time            991271\n",
       "membership_type          984960\n",
       "month                    618479\n",
       "return_kiosk             991271\n",
       "return_kiosk_id          971487\n",
       "trip_duration_minutes    991271\n",
       "trip_id                  991271\n",
       "year                     618479\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking whether the checkout date matches with the number of trips (Trip ID) taken  \n",
    "df_bike_clean.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We see take the Checkout Date and extract the Month, Year, and Day of the Week \n",
    "df_bike_clean['checkout_date'] = pd.to_datetime(df_bike_clean['checkout_date']) \n",
    "df_bike_clean['year'] = df_bike_clean['checkout_date'].dt.year\n",
    "df_bike_clean['month'] = df_bike_clean['checkout_date'].dt.month\n",
    "df_bike_clean['Trip Date'] = df_bike_clean['checkout_date'].dt.day\n",
    "df_bike_clean['Trip Day of Week'] = df_bike_clean['checkout_date'].dt.weekday_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bicycle_id</th>\n",
       "      <th>checkout_date</th>\n",
       "      <th>checkout_kiosk</th>\n",
       "      <th>checkout_kiosk_id</th>\n",
       "      <th>checkout_time</th>\n",
       "      <th>membership_type</th>\n",
       "      <th>month</th>\n",
       "      <th>return_kiosk</th>\n",
       "      <th>return_kiosk_id</th>\n",
       "      <th>trip_duration_minutes</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>year</th>\n",
       "      <th>Trip Date</th>\n",
       "      <th>Trip Day of Week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>991266</th>\n",
       "      <td>1524</td>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>2497</td>\n",
       "      <td>23:24:32</td>\n",
       "      <td>U.T. Student Membership</td>\n",
       "      <td>7</td>\n",
       "      <td>City Hall / Lavaca &amp; 2nd</td>\n",
       "      <td>2499</td>\n",
       "      <td>29</td>\n",
       "      <td>18122309</td>\n",
       "      <td>2018</td>\n",
       "      <td>31</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991267</th>\n",
       "      <td>95</td>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>Long Center @ South 1st &amp; Riverside</td>\n",
       "      <td>2549</td>\n",
       "      <td>23:29:21</td>\n",
       "      <td>Local365+Guest Pass</td>\n",
       "      <td>7</td>\n",
       "      <td>East 6th &amp; Pedernales St.</td>\n",
       "      <td>2544</td>\n",
       "      <td>18</td>\n",
       "      <td>18122312</td>\n",
       "      <td>2018</td>\n",
       "      <td>31</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991268</th>\n",
       "      <td>252</td>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>Rio Grande &amp; 28th</td>\n",
       "      <td>3793</td>\n",
       "      <td>23:39:16</td>\n",
       "      <td>U.T. Student Membership</td>\n",
       "      <td>7</td>\n",
       "      <td>22nd &amp; Pearl</td>\n",
       "      <td>3792</td>\n",
       "      <td>4</td>\n",
       "      <td>18122330</td>\n",
       "      <td>2018</td>\n",
       "      <td>31</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991269</th>\n",
       "      <td>2228</td>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>Dean Keeton &amp; Whitis</td>\n",
       "      <td>3795</td>\n",
       "      <td>23:42:50</td>\n",
       "      <td>U.T. Student Membership</td>\n",
       "      <td>7</td>\n",
       "      <td>Nueces &amp; 26th</td>\n",
       "      <td>3838</td>\n",
       "      <td>2</td>\n",
       "      <td>18122340</td>\n",
       "      <td>2018</td>\n",
       "      <td>31</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>991270</th>\n",
       "      <td>576</td>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>Rio Grande &amp; 28th</td>\n",
       "      <td>3793</td>\n",
       "      <td>23:44:32</td>\n",
       "      <td>U.T. Student Membership</td>\n",
       "      <td>7</td>\n",
       "      <td>Rio Grande &amp; 28th</td>\n",
       "      <td>3793</td>\n",
       "      <td>4</td>\n",
       "      <td>18122349</td>\n",
       "      <td>2018</td>\n",
       "      <td>31</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       bicycle_id checkout_date                       checkout_kiosk  \\\n",
       "991266       1524    2018-07-31    Capitol Station / Congress & 11th   \n",
       "991267         95    2018-07-31  Long Center @ South 1st & Riverside   \n",
       "991268        252    2018-07-31                    Rio Grande & 28th   \n",
       "991269       2228    2018-07-31                 Dean Keeton & Whitis   \n",
       "991270        576    2018-07-31                    Rio Grande & 28th   \n",
       "\n",
       "       checkout_kiosk_id checkout_time          membership_type  month  \\\n",
       "991266              2497      23:24:32  U.T. Student Membership      7   \n",
       "991267              2549      23:29:21      Local365+Guest Pass      7   \n",
       "991268              3793      23:39:16  U.T. Student Membership      7   \n",
       "991269              3795      23:42:50  U.T. Student Membership      7   \n",
       "991270              3793      23:44:32  U.T. Student Membership      7   \n",
       "\n",
       "                     return_kiosk return_kiosk_id trip_duration_minutes  \\\n",
       "991266   City Hall / Lavaca & 2nd            2499                    29   \n",
       "991267  East 6th & Pedernales St.            2544                    18   \n",
       "991268               22nd & Pearl            3792                     4   \n",
       "991269              Nueces & 26th            3838                     2   \n",
       "991270          Rio Grande & 28th            3793                     4   \n",
       "\n",
       "         trip_id  year  Trip Date Trip Day of Week  \n",
       "991266  18122309  2018         31          Tuesday  \n",
       "991267  18122312  2018         31          Tuesday  \n",
       "991268  18122330  2018         31          Tuesday  \n",
       "991269  18122340  2018         31          Tuesday  \n",
       "991270  18122349  2018         31          Tuesday  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Inspect the filled in values \n",
    "df_bike_clean.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split the hour from the checkout time\n",
    "df_bike_clean['Trip Hour'] = df_bike_clean['checkout_time'].apply(lambda x: x.split(\":\")[0])\n"
   ]
  },
  {
   "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>bicycle_id</th>\n",
       "      <th>checkout_date</th>\n",
       "      <th>checkout_kiosk</th>\n",
       "      <th>checkout_kiosk_id</th>\n",
       "      <th>checkout_time</th>\n",
       "      <th>membership_type</th>\n",
       "      <th>month</th>\n",
       "      <th>return_kiosk</th>\n",
       "      <th>return_kiosk_id</th>\n",
       "      <th>trip_duration_minutes</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>year</th>\n",
       "      <th>Trip Date</th>\n",
       "      <th>Trip Day of Week</th>\n",
       "      <th>Trip Hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>207</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>West &amp; 6th St.</td>\n",
       "      <td>2537</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>Annual (San Antonio B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Rainey St @ Cummings</td>\n",
       "      <td>2707</td>\n",
       "      <td>76</td>\n",
       "      <td>9900285854</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>969</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>Convention Center / 4th St. @ MetroRail</td>\n",
       "      <td>2498</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Pfluger Bridge @ W 2nd Street</td>\n",
       "      <td>2566</td>\n",
       "      <td>58</td>\n",
       "      <td>9900285855</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>214</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>West &amp; 6th St.</td>\n",
       "      <td>2537</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>Annual Membership (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>8th &amp; Congress</td>\n",
       "      <td>2496</td>\n",
       "      <td>8</td>\n",
       "      <td>9900285856</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>745</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>Zilker Park at Barton Springs &amp; William Barton...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Zilker Park at Barton Springs &amp; William Barton...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28</td>\n",
       "      <td>9900285857</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>164</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>Bullock Museum @ Congress &amp; MLK</td>\n",
       "      <td>2538</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Convention Center/ 3rd &amp; Trinity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>9900285858</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  bicycle_id checkout_date                                     checkout_kiosk  \\\n",
       "0        207    2014-10-26                                     West & 6th St.   \n",
       "1        969    2014-10-26            Convention Center / 4th St. @ MetroRail   \n",
       "2        214    2014-10-26                                     West & 6th St.   \n",
       "3        745    2014-10-26  Zilker Park at Barton Springs & William Barton...   \n",
       "4        164    2014-10-26                    Bullock Museum @ Congress & MLK   \n",
       "\n",
       "  checkout_kiosk_id checkout_time                     membership_type  month  \\\n",
       "0              2537      13:12:00        Annual (San Antonio B-cycle)     10   \n",
       "1              2498      13:12:00      24-Hour Kiosk (Austin B-cycle)     10   \n",
       "2              2537      13:12:00  Annual Membership (Austin B-cycle)     10   \n",
       "3               NaN      13:12:00      24-Hour Kiosk (Austin B-cycle)     10   \n",
       "4              2538      13:12:00      24-Hour Kiosk (Austin B-cycle)     10   \n",
       "\n",
       "                                        return_kiosk return_kiosk_id  \\\n",
       "0                               Rainey St @ Cummings            2707   \n",
       "1                      Pfluger Bridge @ W 2nd Street            2566   \n",
       "2                                     8th & Congress            2496   \n",
       "3  Zilker Park at Barton Springs & William Barton...             NaN   \n",
       "4                   Convention Center/ 3rd & Trinity             NaN   \n",
       "\n",
       "  trip_duration_minutes     trip_id  year  Trip Date Trip Day of Week  \\\n",
       "0                    76  9900285854  2014         26           Sunday   \n",
       "1                    58  9900285855  2014         26           Sunday   \n",
       "2                     8  9900285856  2014         26           Sunday   \n",
       "3                    28  9900285857  2014         26           Sunday   \n",
       "4                    15  9900285858  2014         26           Sunday   \n",
       "\n",
       "  Trip Hour  \n",
       "0        13  \n",
       "1        13  \n",
       "2        13  \n",
       "3        13  \n",
       "4        13  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Inspect the filled in values \n",
    "df_bike_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bicycle_id               990548\n",
       "checkout_date            991271\n",
       "checkout_kiosk           991271\n",
       "checkout_kiosk_id        972240\n",
       "checkout_time            991271\n",
       "membership_type          984960\n",
       "month                    991271\n",
       "return_kiosk             991271\n",
       "return_kiosk_id          971487\n",
       "trip_duration_minutes    991271\n",
       "trip_id                  991271\n",
       "year                     991271\n",
       "Trip Date                991271\n",
       "Trip Day of Week         991271\n",
       "Trip Hour                991271\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for missing values \n",
    "df_bike_clean.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bicycle_id                       object\n",
       "checkout_date            datetime64[ns]\n",
       "checkout_kiosk                   object\n",
       "checkout_kiosk_id                object\n",
       "checkout_time                    object\n",
       "membership_type                  object\n",
       "month                             int64\n",
       "return_kiosk                     object\n",
       "return_kiosk_id                  object\n",
       "trip_duration_minutes            object\n",
       "trip_id                          object\n",
       "year                              int64\n",
       "Trip Date                         int64\n",
       "Trip Day of Week                 object\n",
       "Trip Hour                        object\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verify Data types\n",
    "df_bike_clean.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert trip duration minutes from object to integer\n",
    "df_bike_change1 = df_bike_clean[\"trip_duration_minutes\"].astype(int)\n",
    "df_bike_clean[\"trip_duration_minutes\"] = df_bike_change1\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert bicycle ID from object to integer\n",
    "df_bike_change2 = df_bike_clean[\"bicycle_id\"].astype(float)\n",
    "df_bike_clean[\"bicycle_id\"] = df_bike_change2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bicycle_id                      float64\n",
       "checkout_date            datetime64[ns]\n",
       "checkout_kiosk                   object\n",
       "checkout_kiosk_id                object\n",
       "checkout_time                    object\n",
       "membership_type                  object\n",
       "month                             int64\n",
       "return_kiosk                     object\n",
       "return_kiosk_id                  object\n",
       "trip_duration_minutes             int32\n",
       "trip_id                          object\n",
       "year                              int64\n",
       "Trip Date                         int64\n",
       "Trip Day of Week                 object\n",
       "Trip Hour                        object\n",
       "dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bike_clean.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Bicycle ID</th>\n",
       "      <th>Checkout Date</th>\n",
       "      <th>Checkout Station</th>\n",
       "      <th>Checkout Station ID</th>\n",
       "      <th>Checkout Time</th>\n",
       "      <th>Membership Type</th>\n",
       "      <th>Trip Month</th>\n",
       "      <th>Return Station</th>\n",
       "      <th>Return Station ID</th>\n",
       "      <th>Trip Duration Minutes</th>\n",
       "      <th>Trip ID</th>\n",
       "      <th>Trip Year</th>\n",
       "      <th>Trip Date</th>\n",
       "      <th>Trip Day of Week</th>\n",
       "      <th>Trip Hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>207.0</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>West &amp; 6th St.</td>\n",
       "      <td>2537</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>Annual (San Antonio B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Rainey St @ Cummings</td>\n",
       "      <td>2707</td>\n",
       "      <td>76</td>\n",
       "      <td>9900285854</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Bicycle ID Checkout Date Checkout Station Checkout Station ID  \\\n",
       "0       207.0    2014-10-26   West & 6th St.                2537   \n",
       "\n",
       "  Checkout Time               Membership Type  Trip Month  \\\n",
       "0      13:12:00  Annual (San Antonio B-cycle)          10   \n",
       "\n",
       "         Return Station Return Station ID  Trip Duration Minutes     Trip ID  \\\n",
       "0  Rainey St @ Cummings              2707                     76  9900285854   \n",
       "\n",
       "   Trip Year  Trip Date Trip Day of Week Trip Hour  \n",
       "0       2014         26           Sunday        13  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename the Columns' Name\n",
    "\n",
    "df_bike_clean = df_bike_clean.rename(columns = {\n",
    "    \"bicycle_id\": \"Bicycle ID\",\n",
    "    \"checkout_date\": \"Checkout Date\",\n",
    "    \"checkout_kiosk\": \"Checkout Station\",\n",
    "    \"checkout_kiosk_id\": \"Checkout Station ID\",\n",
    "    \"checkout_time\": \"Checkout Time\",\n",
    "    \"membership_type\": \"Membership Type\",\n",
    "    \"month\": \"Trip Month\",\n",
    "    \"return_kiosk\":\"Return Station\",\n",
    "    \"return_kiosk_id\": \"Return Station ID\",\n",
    "    \"trip_duration_minutes\": \"Trip Duration Minutes\",\n",
    "    \"trip_id\": \"Trip ID\",\n",
    "    \"year\":\"Trip Year\",\n",
    "    \"Checkout Kiosk ID\":\"Checkout Station ID\"\n",
    "                                         })\n",
    "\n",
    "df_bike_clean.head(1) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Station ID and Station Name Exploration and Cleanup\n",
    "\n",
    "There are a significant number of values missing in the Checkout and Return Station ID columns. However, all the station names are available. In this section, we seek to understand and quantify the remaining missing values. \n",
    "\n",
    "* The missing values are summarized below. The only column with missing data that will be used in the analysis notebook is the membership_type. Thus, we need to fill-in the remaining missing values with zeros and explain the missing data. To accomplish this we fill missing values with zero.\n",
    "* Besides missing values, we find that there are cases where there are more than one value assigned to a key in the Station ID columns.\n",
    "* Ultimately, we make a new data frame that reorganizes the column order and focuses on the columns we will iterate through in the analysis section. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 19031 Checkout Station IDs that are blank.\n"
     ]
    }
   ],
   "source": [
    "# To check how many Checkout Station ID are blank\n",
    "number_df_bike_checkout_id_blank  = df_bike_clean[\"Checkout Station ID\"].isnull().sum()\n",
    "print(f\"There are {number_df_bike_checkout_id_blank} Checkout Station IDs that are blank.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill the Na values with zero for exploration\n",
    "df_bike_na = df_bike_clean.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Zilker Park at Barton Springs & William Barton Drive    11534\n",
       "Dean Keeton & Speedway                                   3825\n",
       "ACC - West & 12th                                        2462\n",
       "Convention Center/ 3rd & Trinity                         1292\n",
       "Mobile Station                                           1183\n",
       "East 11th Street at Victory Grill                        1030\n",
       "Red River @ LBJ Library                                   584\n",
       "Mobile Station @ Bike Fest                                516\n",
       "Main Office                                               300\n",
       "Bullock Museum @ Congress & MLK                           172\n",
       "State Capitol @ 14th & Colorado                           111\n",
       "MapJam at Pan Am Park                                      32\n",
       "MapJam at French Legation                                  27\n",
       "MapJam at Hops & Grain Brewery                             19\n",
       "Repair Shop                                                15\n",
       "MapJam at Scoot Inn                                        11\n",
       "Shop                                                       10\n",
       "Mobile Station @ Boardwalk Opening Ceremony                 9\n",
       "Re-branding                                                 7\n",
       "Marketing Event                                             4\n",
       "Mobile Station @ Unplugged                                  4\n",
       "Customer Service                                            4\n",
       "Eeyore's 2018                                               2\n",
       "Eeyore's 2017                                               1\n",
       "Name: Checkout Station, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# To find which check out station have blank checkout IDs that is zero or #N/A\n",
    "df_bike_checkout_id_blank = df_bike_na.loc[(df_bike_na[\"Checkout Station ID\"] == 0) | (df_bike_na[\"Checkout Station ID\"] == \"#N/A\")]\n",
    "df_bike_checkout_id_blank[\"Checkout Station\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['3798', '2575', '2499', '2494', '2501', '2707', '2495', '2498', '2563',\n",
       "       '2497', '2566', '2552', '2548', '2549', '2567', '2574', '2711', '2502',\n",
       "            0, '2503', '2547', '2570', '2539', '2572', '2496', '2504', '3841',\n",
       "       '2537', '3792', '2542', '3377', '2565', '3390', '2571', '2538', '3793',\n",
       "       '3838', '2550', '2569', '3794', '2562', '3795', '3513', '2540', '3797',\n",
       "       '2822', '2564', '3619', '3621', '2561', '3799', '2536', '3455', '2544',\n",
       "       '3292', '2568', '2541', '3687', '1007', '1008', '3291', '3684', '3293',\n",
       "       '#N/A', '3686', '3660', '2712', '2823', '2576', '3294', '3685', '2546',\n",
       "       '2545', '3635', '1006', '1002', '3464', '3790', '1003', '3381', '2500',\n",
       "       '3791', '3456', '1005', '1001'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List of checkout station id\n",
    "checkout_station_id_list =  df_bike_na[\"Checkout Station ID\"].value_counts().index\n",
    "checkout_station_id_list\n",
    "\n",
    "# Currently we have 83 unique check out station ID excluding zero and #N/A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 24 stations without checkout ID.\n",
      "There are 104 Unique Checkout Station.\n",
      "There are 80 stations with unique checkout station ids other than zero and #N/A.\n",
      "There are 83 Unique Checkout Station ID.\n"
     ]
    }
   ],
   "source": [
    "# Number of stations which have no checkout ID\n",
    "blank_stations = len(df_bike_checkout_id_blank[\"Checkout Station\"].value_counts())\n",
    "\n",
    "# Number of unique checkout stations\n",
    "unique_checkout_stations = df_bike_clean[\"Checkout Station\"].unique().size\n",
    "\n",
    "# Number of stations with unique checkout station ids other than zero\n",
    "unique_stations_nonnull = unique_checkout_stations - blank_stations \n",
    "\n",
    "# Number of unique checkout station ID\n",
    "unique_checkout_id = df_bike_clean[\"Checkout Station ID\"].unique().size\n",
    "unique_checkout_id_not_zero = unique_checkout_id - 2\n",
    "# -2 for zero and #N/A checkout id\n",
    "\n",
    "# Summary checkout station ID findings \n",
    "print(f\"There are {blank_stations} stations without checkout ID.\")\n",
    "print(f\"There are {unique_checkout_stations} Unique Checkout Station.\")\n",
    "print(f\"There are {unique_stations_nonnull} stations with unique checkout station ids other than zero and #N/A.\")\n",
    "print(f\"There are {unique_checkout_id_not_zero} Unique Checkout Station ID.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We have a larger number of unique checkout station ids than number of unique checkout stations. \n",
    "# This implies we have a few checkout stations with more than one checkout station IDs.\n",
    "\n",
    "# Create a dictionary using keyword arguments: checkout station and checkout station ids \n",
    "Checkout_station_id = dict()\n",
    "for index, row in df_bike_na.iterrows():\n",
    "    if row['Checkout Station'] not in Checkout_station_id:\n",
    "        Checkout_station_id[row['Checkout Station']] = set()\n",
    "    else:\n",
    "         Checkout_station_id[row['Checkout Station']].add(row['Checkout Station ID'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bullock Museum @ Congress & MLK has both ids: {'2538', '#N/A'}\n",
      "State Capitol @ 14th & Colorado has both ids: {'2541', '#N/A'}\n",
      "Main Office has both ids: {0, '#N/A', '1001'}\n",
      "Lavaca & 6th has both ids: {'3294', '1007'}\n",
      "Re-branding has both ids: {0, '#N/A'}\n",
      "Repair Shop has both ids: {0, '#N/A'}\n",
      "Republic Square @ 5th & Guadalupe has both ids: {'3456', '3455'}\n",
      "Dean Keeton & Speedway  has both ids: {'3794', '#N/A'}\n"
     ]
    }
   ],
   "source": [
    "# To check which station has more than one checkout id\n",
    "Checkout_id_check = Checkout_station_id\n",
    "for key in Checkout_id_check:\n",
    "    if len(Checkout_id_check[key]) > 1:\n",
    "        print(\"{} has both ids: {}\".format(key, Checkout_id_check[key]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Lavaca & 6th and Guadalupe & 6th share id: {'3294'}\n",
      "Republic Square @ Federal Courthouse Plaza and Republic Square @ 5th & Guadalupe share id: {'3455'}\n",
      "Guadalupe & 6th and Lavaca & 6th share id: {'3294'}\n",
      "Republic Square @ 5th & Guadalupe and Republic Square @ Federal Courthouse Plaza share id: {'3455'}\n"
     ]
    }
   ],
   "source": [
    "# To check if there is an overlap of Checkout Station ID :\n",
    "\n",
    "# Remove station with zero checkout station ID \n",
    "for key in Checkout_id_check:\n",
    "    if 0 in Checkout_id_check[key]:\n",
    "        Checkout_id_check[key].remove(0)\n",
    "\n",
    "# Remove station with \"#N/A\" checkout station ID \n",
    "for key in Checkout_id_check:\n",
    "    if \"#N/A\" in Checkout_id_check[key]:\n",
    "        Checkout_id_check[key].remove(\"#N/A\")\n",
    "        \n",
    "# Check for an overlap of station IDs\n",
    "for key1 in Checkout_id_check:\n",
    "    for key2 in Checkout_id_check:\n",
    "        if key1 == key2:\n",
    "            continue\n",
    "        intersect = Checkout_id_check[key1].intersection(Checkout_id_check[key2])\n",
    "        if len(intersect) > 0:\n",
    "            print(\"{} and {} share id: {}\".format(key1, key2, intersect))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning the Trip Duration and Bike ID Columns \n",
    "\n",
    "1.\t Looking closely into the data we found that some trip duration values are unusually large. During investigation, we found that under the Return Station column, those bikes were labeled as either stolen or missing. We are dropping the rows for our final clean CSV file for those missing and stolen bikes.\n",
    "\n",
    "2.\t We also found that some bikes have a trip duration of zero minutes. This data is correct. Some of this can be explained by the bikes being transported by the company to different stations and marked as zero minutes. (https://austinstartups.com/analyzing-austin-bcycle-rentals-stations-d9a1863d17e9) \n",
    "\n",
    "3. We are also dropping the missing bicycle IDs. They do not provide us with relevant data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 23 bikes reported as stolen.\n",
      "There are 25 bikes reported as missing.\n",
      "There are 19033 bikes that had a trip duration of zero.\n"
     ]
    }
   ],
   "source": [
    "# To check how many bikes were stolen\n",
    "# These bikes have unusally large trip durations\n",
    "df_bike_stolen = df_bike_clean.loc[df_bike_clean[\"Return Station\"] == \"Stolen\"]\n",
    "number_bike_stolen = df_bike_stolen[\"Return Station\"].count()\n",
    "\n",
    "\n",
    "# To check how many bikes were missing\n",
    "# These bikes have unusally large trip durations\n",
    "df_bike_missing = df_bike_clean.loc[df_bike_clean[\"Return Station\"] == \"Missing\"]\n",
    "number_bike_missing = df_bike_missing[\"Return Station\"].count()\n",
    "\n",
    "\n",
    "# To check how many bikes have trip duration with zero minutes\n",
    "df_bike_trip_minutes_zero = df_bike_clean.loc[df_bike_clean[\"Trip Duration Minutes\"] == 0]\n",
    "number_bike_trip_minutes_zero = df_bike_trip_minutes_zero[\"Trip ID\"].count()\n",
    "\n",
    "# Summary of bike trips with distinct data \n",
    "print(f\"There are {number_bike_stolen} bikes reported as stolen.\")\n",
    "print(f\"There are {number_bike_missing} bikes reported as missing.\")\n",
    "print(f\"There are {number_bike_trip_minutes_zero} bikes that had a trip duration of zero.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the Rows for Stolen and Missing Bikes\n",
    "# Create a clean data frame that does not include missing and stolen bikes\n",
    "df_bike_clean = df_bike_clean.loc[(df_bike_clean[\"Return Station\"] != \"Stolen\") & (df_bike_clean[\"Return Station\"] != \"Missing\"), :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(991223, 15)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the data frame size after dropping the rows\n",
    "df_bike_clean.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Bicycle ID</th>\n",
       "      <th>Checkout Date</th>\n",
       "      <th>Checkout Station</th>\n",
       "      <th>Checkout Station ID</th>\n",
       "      <th>Checkout Time</th>\n",
       "      <th>Membership Type</th>\n",
       "      <th>Trip Month</th>\n",
       "      <th>Return Station</th>\n",
       "      <th>Return Station ID</th>\n",
       "      <th>Trip Duration Minutes</th>\n",
       "      <th>Trip ID</th>\n",
       "      <th>Trip Year</th>\n",
       "      <th>Trip Date</th>\n",
       "      <th>Trip Day of Week</th>\n",
       "      <th>Trip Hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>207.0</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>West &amp; 6th St.</td>\n",
       "      <td>2537</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>Annual (San Antonio B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Rainey St @ Cummings</td>\n",
       "      <td>2707</td>\n",
       "      <td>76</td>\n",
       "      <td>9900285854</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>969.0</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>Convention Center / 4th St. @ MetroRail</td>\n",
       "      <td>2498</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Pfluger Bridge @ W 2nd Street</td>\n",
       "      <td>2566</td>\n",
       "      <td>58</td>\n",
       "      <td>9900285855</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>214.0</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>West &amp; 6th St.</td>\n",
       "      <td>2537</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>Annual Membership (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>8th &amp; Congress</td>\n",
       "      <td>2496</td>\n",
       "      <td>8</td>\n",
       "      <td>9900285856</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>745.0</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>Zilker Park at Barton Springs &amp; William Barton...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Zilker Park at Barton Springs &amp; William Barton...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28</td>\n",
       "      <td>9900285857</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>164.0</td>\n",
       "      <td>2014-10-26</td>\n",
       "      <td>Bullock Museum @ Congress &amp; MLK</td>\n",
       "      <td>2538</td>\n",
       "      <td>13:12:00</td>\n",
       "      <td>24-Hour Kiosk (Austin B-cycle)</td>\n",
       "      <td>10</td>\n",
       "      <td>Convention Center/ 3rd &amp; Trinity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15</td>\n",
       "      <td>9900285858</td>\n",
       "      <td>2014</td>\n",
       "      <td>26</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Bicycle ID Checkout Date  \\\n",
       "0       207.0    2014-10-26   \n",
       "1       969.0    2014-10-26   \n",
       "2       214.0    2014-10-26   \n",
       "3       745.0    2014-10-26   \n",
       "4       164.0    2014-10-26   \n",
       "\n",
       "                                    Checkout Station Checkout Station ID  \\\n",
       "0                                     West & 6th St.                2537   \n",
       "1            Convention Center / 4th St. @ MetroRail                2498   \n",
       "2                                     West & 6th St.                2537   \n",
       "3  Zilker Park at Barton Springs & William Barton...                 NaN   \n",
       "4                    Bullock Museum @ Congress & MLK                2538   \n",
       "\n",
       "  Checkout Time                     Membership Type  Trip Month  \\\n",
       "0      13:12:00        Annual (San Antonio B-cycle)          10   \n",
       "1      13:12:00      24-Hour Kiosk (Austin B-cycle)          10   \n",
       "2      13:12:00  Annual Membership (Austin B-cycle)          10   \n",
       "3      13:12:00      24-Hour Kiosk (Austin B-cycle)          10   \n",
       "4      13:12:00      24-Hour Kiosk (Austin B-cycle)          10   \n",
       "\n",
       "                                      Return Station Return Station ID  \\\n",
       "0                               Rainey St @ Cummings              2707   \n",
       "1                      Pfluger Bridge @ W 2nd Street              2566   \n",
       "2                                     8th & Congress              2496   \n",
       "3  Zilker Park at Barton Springs & William Barton...               NaN   \n",
       "4                   Convention Center/ 3rd & Trinity               NaN   \n",
       "\n",
       "   Trip Duration Minutes     Trip ID  Trip Year  Trip Date Trip Day of Week  \\\n",
       "0                     76  9900285854       2014         26           Sunday   \n",
       "1                     58  9900285855       2014         26           Sunday   \n",
       "2                      8  9900285856       2014         26           Sunday   \n",
       "3                     28  9900285857       2014         26           Sunday   \n",
       "4                     15  9900285858       2014         26           Sunday   \n",
       "\n",
       "  Trip Hour  \n",
       "0        13  \n",
       "1        13  \n",
       "2        13  \n",
       "3        13  \n",
       "4        13  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display the clean data frame\n",
    "df_bike_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean the Missing Bicycle ID’s\n",
    "# We are dropping the rows for our final clean CSV file for the unlabeled bikes.\n",
    "# Filling the Na values with zero for exploration\n",
    "df_bike_na = df_bike_clean.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a clean data frame that does not include unlabeled bikes\n",
    "df_bike_clean = df_bike_na.loc[( df_bike_na[\"Bicycle ID\"] != 0), :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(990500, 15)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the data frame size after dropping the rows\n",
    "df_bike_clean.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Reorganized and Updated Data Frame\n",
    "\n",
    "After the initial exploration of the data and clean up, we have a better grasp of the data. The columns that were missing significant data and had abnormalities are accounted for.\n",
    "\n",
    "In the end, we won't need the following columns: bike id, checkout station id, return station ids. For our analysis, we now take only a subset of the most complete data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Organize column and take columns with relevant data which we will use for analysis\n",
    "df_bike_clean = df_bike_clean[[\"Trip ID\", \"Membership Type\", \"Checkout Date\",\"Checkout Time\", \"Checkout Station\", \"Return Station\", \"Trip Duration Minutes\", \"Trip Month\", \"Trip Year\",\"Trip Date\", \"Trip Day of Week\", \"Trip Hour\"]] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning and Grouping Membership Data \n",
    "\n",
    "Even though the data count reflects a complete dataset now, there is further cleaning that needs to happen before we can successfully analyze the data.\n",
    "\n",
    "In this last part, we want to take a closer look to understand the types of memberships. Upon closer inspection, there are memberships with similar names that should be categorized together. For example: U.T. Student Membership and UT Student Membership.\n",
    "\n",
    "We also find that there are two memberships RESTRICTED and PROHIBITED that yield trip duration average of zero minutes. We will drop this small data subset (18 rows) to categorize and analyze correctly. \n",
    "\n",
    "Furthermore, it will be more helpful to categorize the data by day, weekend, week, month, year, 3 year, and U.T. student memberships.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Walk Up                                          368029\n",
       "Local365                                         167270\n",
       "U.T. Student Membership                          158477\n",
       "24-Hour Kiosk (Austin B-cycle)                   108466\n",
       "Local30                                           54729\n",
       "Weekender                                         43860\n",
       "Annual Membership (Austin B-cycle)                30243\n",
       "Explorer                                          14853\n",
       "Local365+Guest Pass                               10331\n",
       "0                                                  6311\n",
       "Local365 ($80 plus tax)                            4004\n",
       "Founding Member                                    3546\n",
       "7-Day                                              3124\n",
       "Founding Member (Austin B-cycle)                   2763\n",
       "7-Day Membership (Austin B-cycle)                  2752\n",
       "Semester Membership (Austin B-cycle)               2418\n",
       "Annual                                             1085\n",
       "Semester Membership                                 900\n",
       "Local30 ($11 plus tax)                              840\n",
       "24-Hour-Online (Austin B-cycle)                     737\n",
       "Weekender ($15 plus tax)                            565\n",
       "Try Before You Buy Special                          521\n",
       "Annual Member                                       480\n",
       "Annual Membership                                   441\n",
       "Annual (San Antonio B-cycle)                        402\n",
       "RideScout Single Ride                               398\n",
       "ACL Weekend Pass Special (Austin B-cycle)           328\n",
       "Annual Member (Houston B-cycle)                     296\n",
       "Annual Membership (Fort Worth Bike Sharing)         265\n",
       "Annual (Denver B-cycle)                             232\n",
       "Republic Rider (Annual)                             216\n",
       "Explorer ($8 plus tax)                              186\n",
       "Annual Plus                                         181\n",
       "Annual (Madison B-cycle)                            165\n",
       "Annual (Broward B-cycle)                            142\n",
       "Annual (Denver Bike Sharing)                        131\n",
       "Annual (Boulder B-cycle)                            129\n",
       "Local365 Youth with helmet (age 13-17 riders)        97\n",
       "Local365 Youth (age 13-17 riders)                    92\n",
       "Annual Membership (GREENbike)                        71\n",
       "Annual Pass                                          54\n",
       "Annual (Cincy Red Bike)                              51\n",
       "Annual (Kansas City B-cycle)                         51\n",
       "Madtown Monthly                                      36\n",
       "Aluminum Access                                      30\n",
       "Annual (Nashville B-cycle)                           27\n",
       "FunFunFun Fest 3 Day Pass                            27\n",
       "Annual Plus Membership                               25\n",
       "Annual Membership (Charlotte B-cycle)                22\n",
       "Annual                                               18\n",
       "Republic Rider                                       17\n",
       "Membership: pay once  one-year commitment            15\n",
       "Denver B-cycle Founder                               10\n",
       "Annual Membership (Indy - Pacers Bikeshare )         10\n",
       "RESTRICTED                                           10\n",
       "UT Student Membership                                 9\n",
       "PROHIBITED                                            8\n",
       "Annual (Omaha B-cycle)                                3\n",
       "24-Hour Membership (Austin B-cycle)                   1\n",
       "Name: Membership Type, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bike_clean[\"Membership Type\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>Trip ID</th>\n",
       "      <th>Membership Type</th>\n",
       "      <th>Checkout Date</th>\n",
       "      <th>Checkout Time</th>\n",
       "      <th>Checkout Station</th>\n",
       "      <th>Return Station</th>\n",
       "      <th>Trip Duration Minutes</th>\n",
       "      <th>Trip Month</th>\n",
       "      <th>Trip Year</th>\n",
       "      <th>Trip Date</th>\n",
       "      <th>Trip Day of Week</th>\n",
       "      <th>Trip Hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>326220</th>\n",
       "      <td>8482437</td>\n",
       "      <td>PROHIBITED</td>\n",
       "      <td>2016-01-20</td>\n",
       "      <td>15:34:07</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>20</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329487</th>\n",
       "      <td>8370751</td>\n",
       "      <td>PROHIBITED</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>14:14:10</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>11</td>\n",
       "      <td>Monday</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329488</th>\n",
       "      <td>8370756</td>\n",
       "      <td>PROHIBITED</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>14:14:46</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>11</td>\n",
       "      <td>Monday</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329489</th>\n",
       "      <td>8370766</td>\n",
       "      <td>PROHIBITED</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>14:15:09</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>11</td>\n",
       "      <td>Monday</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347380</th>\n",
       "      <td>9900014475</td>\n",
       "      <td>PROHIBITED</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>14:12:10</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>11</td>\n",
       "      <td>Monday</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Trip ID Membership Type Checkout Date Checkout Time  \\\n",
       "326220     8482437      PROHIBITED    2016-01-20      15:34:07   \n",
       "329487     8370751      PROHIBITED    2016-01-11      14:14:10   \n",
       "329488     8370756      PROHIBITED    2016-01-11      14:14:46   \n",
       "329489     8370766      PROHIBITED    2016-01-11      14:15:09   \n",
       "347380  9900014475      PROHIBITED    2016-01-11      14:12:10   \n",
       "\n",
       "                         Checkout Station                     Return Station  \\\n",
       "326220  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "329487  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "329488  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "329489  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "347380  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "\n",
       "        Trip Duration Minutes  Trip Month  Trip Year  Trip Date  \\\n",
       "326220                      0           1       2016         20   \n",
       "329487                      0           1       2016         11   \n",
       "329488                      1           1       2016         11   \n",
       "329489                      0           1       2016         11   \n",
       "347380                      0           1       2016         11   \n",
       "\n",
       "       Trip Day of Week Trip Hour  \n",
       "326220        Wednesday        15  \n",
       "329487           Monday        14  \n",
       "329488           Monday        14  \n",
       "329489           Monday        14  \n",
       "347380           Monday        14  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Examine Prohibited Memberships\n",
    "test = df_bike_clean.loc[df_bike_clean[\"Membership Type\"] == \"PROHIBITED\", :]\n",
    "test.head()"
   ]
  },
  {
   "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>Trip ID</th>\n",
       "      <th>Membership Type</th>\n",
       "      <th>Checkout Date</th>\n",
       "      <th>Checkout Time</th>\n",
       "      <th>Checkout Station</th>\n",
       "      <th>Return Station</th>\n",
       "      <th>Trip Duration Minutes</th>\n",
       "      <th>Trip Month</th>\n",
       "      <th>Trip Year</th>\n",
       "      <th>Trip Date</th>\n",
       "      <th>Trip Day of Week</th>\n",
       "      <th>Trip Hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>326221</th>\n",
       "      <td>8482474</td>\n",
       "      <td>RESTRICTED</td>\n",
       "      <td>2016-01-20</td>\n",
       "      <td>15:35:09</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>20</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329490</th>\n",
       "      <td>8368765</td>\n",
       "      <td>RESTRICTED</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>11:30:08</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>11</td>\n",
       "      <td>Monday</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329491</th>\n",
       "      <td>8370726</td>\n",
       "      <td>RESTRICTED</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>14:12:09</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>11</td>\n",
       "      <td>Monday</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329492</th>\n",
       "      <td>8370745</td>\n",
       "      <td>RESTRICTED</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>14:13:32</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>11</td>\n",
       "      <td>Monday</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329493</th>\n",
       "      <td>8370747</td>\n",
       "      <td>RESTRICTED</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>14:13:50</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>Capitol Station / Congress &amp; 11th</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>11</td>\n",
       "      <td>Monday</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Trip ID Membership Type Checkout Date Checkout Time  \\\n",
       "326221  8482474      RESTRICTED    2016-01-20      15:35:09   \n",
       "329490  8368765      RESTRICTED    2016-01-11      11:30:08   \n",
       "329491  8370726      RESTRICTED    2016-01-11      14:12:09   \n",
       "329492  8370745      RESTRICTED    2016-01-11      14:13:32   \n",
       "329493  8370747      RESTRICTED    2016-01-11      14:13:50   \n",
       "\n",
       "                         Checkout Station                     Return Station  \\\n",
       "326221  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "329490  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "329491  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "329492  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "329493  Capitol Station / Congress & 11th  Capitol Station / Congress & 11th   \n",
       "\n",
       "        Trip Duration Minutes  Trip Month  Trip Year  Trip Date  \\\n",
       "326221                      0           1       2016         20   \n",
       "329490                      8           1       2016         11   \n",
       "329491                      1           1       2016         11   \n",
       "329492                      0           1       2016         11   \n",
       "329493                      1           1       2016         11   \n",
       "\n",
       "       Trip Day of Week Trip Hour  \n",
       "326221        Wednesday        15  \n",
       "329490           Monday        11  \n",
       "329491           Monday        14  \n",
       "329492           Monday        14  \n",
       "329493           Monday        14  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Examine Prohibited Memberships\n",
    "test = df_bike_clean.loc[df_bike_clean[\"Membership Type\"] == \"RESTRICTED\", :]\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace all 24-hour with same name == day \n",
    "df_bike_clean[\"Membership Type\"] = df_bike_clean[\"Membership Type\"].replace(\n",
    "    {\"24-Hour Kiosk (Austin B-cycle)\": \"day\",\n",
    "     \"24-Hour-Online (Austin B-cycle)\": \"day\",\n",
    "     \"24-Hour Membership (Austin B-cycle)\": \"day\",\n",
    "    \"Explorer\": \"day\", \n",
    "    \"Explorer ($8 plus tax)\":\"day\",\n",
    "    \"Try Before You Buy Special\": \"day\",\n",
    "    \"RideScout Single Ride\": \"day\", \n",
    "    \"Aluminum Access\":\"day\"})\n",
    "\n",
    "# Replace all weekend membership == walk-up \n",
    "df_bike_clean[\"Membership Type\"] = df_bike_clean[\"Membership Type\"].replace(\n",
    "    {\"Walk Up\": \"walk up\", 0 :\"walk up\"})\n",
    "\n",
    "\n",
    "# Replace all weekend membership == weekend \n",
    "df_bike_clean[\"Membership Type\"] = df_bike_clean[\"Membership Type\"].replace(\n",
    "    {\n",
    "        \"Weekender\": \"weekend\", \n",
    "        \"Weekender ($15 plus tax)\": \"weekend\", \n",
    "        \"ACL Weekend Pass Special (Austin B-cycle)\": \"weekend\", \n",
    "        \"FunFunFun Fest 3 Day Pass\": \"weekend\"\n",
    "    })\n",
    "\n",
    "# Replace all weekend membership == week\n",
    "df_bike_clean[\"Membership Type\"] = df_bike_clean[\"Membership Type\"].replace(\n",
    "    {\n",
    "        \"7-Day\": \"week\", \n",
    "        \"7-Day Membership (Austin B-cycle)\": \"week\", \n",
    "    })\n",
    "\n",
    "\n",
    "# Replace all weekend membership == month\n",
    "df_bike_clean[\"Membership Type\"] = df_bike_clean[\"Membership Type\"].replace(\n",
    "    {\n",
    "        \"Local30\": \"month\", \n",
    "        \"Local30 ($11 plus tax)\": \"month\",\n",
    "        \"Madtown Monthly\":\"month\", \n",
    "    })\n",
    "\n",
    "\n",
    "# Combine all student memberships\n",
    "df_bike_clean[\"Membership Type\"] = df_bike_clean[\"Membership Type\"].replace(\n",
    "    {\n",
    "        \"U.T. Student Membership\": \"student\",\n",
    "        \"UT Student Membership\": \"student\", \n",
    "        \"Semester Membership (Austin B-cycle)\":\"student\", \n",
    "        \"Semester Membership\": \"student\"\n",
    "    })\n",
    "\n",
    "# Replace all annual membership == year\n",
    "df_bike_clean[\"Membership Type\"] = df_bike_clean[\"Membership Type\"].replace(\n",
    "    {\n",
    "        \"Annual Membership (Austin B-cycle)\": \"year\",\n",
    "         \"Annual Member\": \"year\",\n",
    "         \"Annual Membership\":\"year\",\n",
    "         \"Annual (San Antonio B-cycle)\": \"year\",\n",
    "         \"Annual Member (Houston B-cycle)\":\"year\",\n",
    "         \"Annual Membership (Fort Worth Bike Sharing)\":\"year\",\n",
    "         \"Annual (Denver B-cycle)\":\"year\",\n",
    "         \"Republic Rider (Annual)\":\"year\",\n",
    "         \"Republic Rider\": \"year\",\n",
    "         \"Annual Plus\":\"year\",\n",
    "         \"Annual (Madison B-cycle)\":\"year\",\n",
    "         \"Annual (Broward B-cycle)\":\"year\",\n",
    "         \"Annual (Denver Bike Sharing)\":\"year\",\n",
    "         \"Annual (Boulder B-cycle)\":\"year\",\n",
    "         \"Annual Membership (GREENbike)\":\"year\",\n",
    "         \"Annual Pass\":\"year\",\n",
    "         \"Annual (Kansas City B-cycle)\":\"year\",\n",
    "         \"Annual (Cincy Red Bike)\":\"year\",\n",
    "         \"Annual (Nashville B-cycle)\":\"year\",\n",
    "         \"Annual Plus Membership\":\"year\",\n",
    "         \"Annual Membership (Charlotte B-cycle)\":\"year\",\n",
    "         \"Annual Membership (Indy - Pacers Bikeshare )\":\"year\",\n",
    "         \"Annual (Omaha B-cycle)\":\"year\",\n",
    "         \"Annual\":\"year\",\n",
    "         \"Annual \": \"year\",\n",
    "         \"Local365\": \"year\", \n",
    "         \"Local365+Guest Pass\":\"year\",\n",
    "         \"Local365 ($80 plus tax)\": \"year\",\n",
    "         \"Local365 Youth with helmet (age 13-17 riders)\": \"year\", \n",
    "         \"Local365 Youth (age 13-17 riders)\":\"year\",\n",
    "         \"Membership: pay once  one-year commitment\":\"year\"\n",
    "        \n",
    "    })\n",
    "\n",
    "# Replace all founding membership == 3 year\n",
    "df_bike_clean[\"Membership Type\"] = df_bike_clean[\"Membership Type\"].replace(\n",
    "    {\n",
    "        \"Founding Member\": \"3 year\",\n",
    "        \"Founding Member (Austin B-cycle)\": \"3 year\",\n",
    "        \"Denver B-cycle Founder\": \"3 year\"\n",
    "    })\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new data frame that does not include restricted and prohibited\n",
    "df_bike_clean = df_bike_clean.loc[(df_bike_clean[\"Membership Type\"] != \"RESTRICTED\") & (df_bike_clean[\"Membership Type\"] != \"PROHIBITED\"), :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(990482, 12)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bike_clean.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "walk up    374340\n",
       "year       216566\n",
       "student    161804\n",
       "day        125192\n",
       "month       55605\n",
       "weekend     44780\n",
       "3 year       6319\n",
       "week         5876\n",
       "Name: Membership Type, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verify clean up \n",
    "df_bike_clean[\"Membership Type\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Trip ID                  990482\n",
       "Membership Type          990482\n",
       "Checkout Date            990482\n",
       "Checkout Time            990482\n",
       "Checkout Station         990482\n",
       "Return Station           990482\n",
       "Trip Duration Minutes    990482\n",
       "Trip Month               990482\n",
       "Trip Year                990482\n",
       "Trip Date                990482\n",
       "Trip Day of Week         990482\n",
       "Trip Hour                990482\n",
       "dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fill the na values  \n",
    "df_bike_clean = df_bike_clean.fillna(0)\n",
    "df_bike_clean.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export to csv\n",
    "df_bike_clean.to_csv(\"Clean_Data\\out.csv\", index = None)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (PythonData)",
   "language": "python",
   "name": "pythondata"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}