Data Exploration and Cleanup Process.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Exploration and Cleanup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This project analyzes anonymized data from Divvy, a rideshare biking service in Chicago, IL. We chose to examine data in the third quarter of 2019 which includes the summer months and high volume of riders compared to other quarters. \n",
    "\n",
    "We wanted to uncover interesting patterns in the data. **We asked ourselves:**\n",
    "* Are there routes and stations that are more popular than others?\n",
    "* Are there any specific bikes that were rented a surprising amount?\n",
    "* Are there any relationships between the age and/or gender when comparing one-time customers or yearly subscribers?\n",
    "* How long, on average, are trips made by Divvy bike?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Observations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Popular Stations**\n",
    "* The most popular station by far is Streeter Dr. and Grand Ave. which is located on Navy Pier. It is a popular destination for both tourists and locals. Indeed, this station appeared as either the starting or ending station on many of the most popular routes. \n",
    "\n",
    "**Popular Routes**\n",
    "* All of the most frequented routes were in the loop and along the lakefront. Without knowing, we suspect that this is again a combination of tourists and commuters making use of the bikes in downtown Chicago. \n",
    "\n",
    "**Customers vs. Subscribers:** \n",
    "* There are far more subscribers than customers who ride in the Chicago Divvy network, about 84% to 16%\n",
    "* This data may be skewed. We made an early call to drop any data with null values. We suspect that the dropped cells are more likely to be customers, one-time or infrequent users.\n",
    "\n",
    "**Gender:**\n",
    "* There are far more male Divvy riders than female - about 70% to 30%.\n",
    "* It is fairly the same gender breakdown when it comes to customer (about 70/30) and subscriber (about 60/40).\n",
    "* Divvy did not collect gender data beyond male and female, which means portions of Chicago's non-binary population are not accounted for in this dataset. \n",
    "    \n",
    "**Age:**\n",
    "* Riders aged 20-29 made up 47% of overall ridership with people aged 30-39 coming in second with 30% of overall ridership.\n",
    "* The breakdown per usertype, customer versus subscriber, was similar to the overall categories. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing & Cleaning Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notes are in-line with code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import Dependencies\n",
    "from matplotlib import pyplot as plt\n",
    "import scipy.stats as stats\n",
    "from scipy.stats import linregress\n",
    "import numpy as np\n",
    "from sklearn import datasets\n",
    "import pandas as pd\n",
    "import requests\n",
    "import json\n",
    "import os\n",
    "import gmaps\n",
    "\n",
    "# Import API key\n",
    "# from config import g_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import data file\n",
    "divvy_df = pd.read_csv('resources/Divvy_Trips_2019_Q3.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# First, we displayed the dataframe head to quickly identify any obvious items in need of cleaning. 'gender' and 'birthyear' immediately stand out as columns in need of cleaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1640718, 12)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "divvy_df.shape"
   ]
  },
  {
   "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>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>from_station_id</th>\n",
       "      <th>from_station_name</th>\n",
       "      <th>to_station_id</th>\n",
       "      <th>to_station_name</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthyear</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23479388</td>\n",
       "      <td>2019-07-01 00:00:27</td>\n",
       "      <td>2019-07-01 00:20:41</td>\n",
       "      <td>3591</td>\n",
       "      <td>1,214.0</td>\n",
       "      <td>117</td>\n",
       "      <td>Wilton Ave &amp; Belmont Ave</td>\n",
       "      <td>497</td>\n",
       "      <td>Kimball Ave &amp; Belmont Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1992.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23479389</td>\n",
       "      <td>2019-07-01 00:01:16</td>\n",
       "      <td>2019-07-01 00:18:44</td>\n",
       "      <td>5353</td>\n",
       "      <td>1,048.0</td>\n",
       "      <td>381</td>\n",
       "      <td>Western Ave &amp; Monroe St</td>\n",
       "      <td>203</td>\n",
       "      <td>Western Ave &amp; 21st St</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23479390</td>\n",
       "      <td>2019-07-01 00:01:48</td>\n",
       "      <td>2019-07-01 00:27:42</td>\n",
       "      <td>6180</td>\n",
       "      <td>1,554.0</td>\n",
       "      <td>313</td>\n",
       "      <td>Lakeview Ave &amp; Fullerton Pkwy</td>\n",
       "      <td>144</td>\n",
       "      <td>Larrabee St &amp; Webster Ave</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23479391</td>\n",
       "      <td>2019-07-01 00:02:07</td>\n",
       "      <td>2019-07-01 00:27:10</td>\n",
       "      <td>5540</td>\n",
       "      <td>1,503.0</td>\n",
       "      <td>313</td>\n",
       "      <td>Lakeview Ave &amp; Fullerton Pkwy</td>\n",
       "      <td>144</td>\n",
       "      <td>Larrabee St &amp; Webster Ave</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23479392</td>\n",
       "      <td>2019-07-01 00:02:13</td>\n",
       "      <td>2019-07-01 00:22:26</td>\n",
       "      <td>6014</td>\n",
       "      <td>1,213.0</td>\n",
       "      <td>168</td>\n",
       "      <td>Michigan Ave &amp; 14th St</td>\n",
       "      <td>62</td>\n",
       "      <td>McCormick Place</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    trip_id           start_time             end_time  bikeid tripduration  \\\n",
       "0  23479388  2019-07-01 00:00:27  2019-07-01 00:20:41    3591      1,214.0   \n",
       "1  23479389  2019-07-01 00:01:16  2019-07-01 00:18:44    5353      1,048.0   \n",
       "2  23479390  2019-07-01 00:01:48  2019-07-01 00:27:42    6180      1,554.0   \n",
       "3  23479391  2019-07-01 00:02:07  2019-07-01 00:27:10    5540      1,503.0   \n",
       "4  23479392  2019-07-01 00:02:13  2019-07-01 00:22:26    6014      1,213.0   \n",
       "\n",
       "   from_station_id              from_station_name  to_station_id  \\\n",
       "0              117       Wilton Ave & Belmont Ave            497   \n",
       "1              381        Western Ave & Monroe St            203   \n",
       "2              313  Lakeview Ave & Fullerton Pkwy            144   \n",
       "3              313  Lakeview Ave & Fullerton Pkwy            144   \n",
       "4              168         Michigan Ave & 14th St             62   \n",
       "\n",
       "             to_station_name    usertype gender  birthyear  \n",
       "0  Kimball Ave & Belmont Ave  Subscriber   Male     1992.0  \n",
       "1      Western Ave & 21st St    Customer    NaN        NaN  \n",
       "2  Larrabee St & Webster Ave    Customer    NaN        NaN  \n",
       "3  Larrabee St & Webster Ave    Customer    NaN        NaN  \n",
       "4            McCormick Place    Customer    NaN        NaN  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display sample of dataframe\n",
    "divvy_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Null rows dropped. We noticed that since many customers do not have accounts, insights focused on gender and age will be more limited. Nearly 300,000 rows were dropped, mostly of the customer user type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1353368, 12)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove null rows (if needed)\n",
    "divvy_df.dropna(inplace=True)\n",
    "divvy_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['trip_id', 'start_time', 'end_time', 'bikeid', 'tripduration',\n",
       "       'from_station_id', 'from_station_name', 'to_station_id',\n",
       "       'to_station_name', 'usertype', 'gender', 'birthyear'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find column names\n",
    "divvy_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Next, to make it easier to analyze the users by age, we converted 'birthyear' to the user's age in 2019."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>from_station_id</th>\n",
       "      <th>from_station_name</th>\n",
       "      <th>to_station_id</th>\n",
       "      <th>to_station_name</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23479388</td>\n",
       "      <td>2019-07-01 00:00:27</td>\n",
       "      <td>2019-07-01 00:20:41</td>\n",
       "      <td>3591</td>\n",
       "      <td>1,214.0</td>\n",
       "      <td>117</td>\n",
       "      <td>Wilton Ave &amp; Belmont Ave</td>\n",
       "      <td>497</td>\n",
       "      <td>Kimball Ave &amp; Belmont Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23479393</td>\n",
       "      <td>2019-07-01 00:02:21</td>\n",
       "      <td>2019-07-01 00:07:31</td>\n",
       "      <td>4941</td>\n",
       "      <td>310.0</td>\n",
       "      <td>300</td>\n",
       "      <td>Broadway &amp; Barry Ave</td>\n",
       "      <td>232</td>\n",
       "      <td>Pine Grove Ave &amp; Waveland Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>23479406</td>\n",
       "      <td>2019-07-01 00:06:51</td>\n",
       "      <td>2019-07-01 00:26:22</td>\n",
       "      <td>2758</td>\n",
       "      <td>1,171.0</td>\n",
       "      <td>624</td>\n",
       "      <td>Dearborn St &amp; Van Buren St</td>\n",
       "      <td>237</td>\n",
       "      <td>MLK Jr Dr &amp; 29th St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1995.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>23479408</td>\n",
       "      <td>2019-07-01 00:08:24</td>\n",
       "      <td>2019-07-01 00:20:07</td>\n",
       "      <td>2447</td>\n",
       "      <td>703.0</td>\n",
       "      <td>239</td>\n",
       "      <td>Western Ave &amp; Leland Ave</td>\n",
       "      <td>455</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>23479409</td>\n",
       "      <td>2019-07-01 00:08:40</td>\n",
       "      <td>2019-07-01 00:27:50</td>\n",
       "      <td>6336</td>\n",
       "      <td>1,150.0</td>\n",
       "      <td>257</td>\n",
       "      <td>Lincoln Ave &amp; Waveland Ave</td>\n",
       "      <td>295</td>\n",
       "      <td>Broadway &amp; Argyle St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trip_id           start_time             end_time  bikeid tripduration  \\\n",
       "0   23479388  2019-07-01 00:00:27  2019-07-01 00:20:41    3591      1,214.0   \n",
       "5   23479393  2019-07-01 00:02:21  2019-07-01 00:07:31    4941        310.0   \n",
       "18  23479406  2019-07-01 00:06:51  2019-07-01 00:26:22    2758      1,171.0   \n",
       "20  23479408  2019-07-01 00:08:24  2019-07-01 00:20:07    2447        703.0   \n",
       "21  23479409  2019-07-01 00:08:40  2019-07-01 00:27:50    6336      1,150.0   \n",
       "\n",
       "    from_station_id           from_station_name  to_station_id  \\\n",
       "0               117    Wilton Ave & Belmont Ave            497   \n",
       "5               300        Broadway & Barry Ave            232   \n",
       "18              624  Dearborn St & Van Buren St            237   \n",
       "20              239    Western Ave & Leland Ave            455   \n",
       "21              257  Lincoln Ave & Waveland Ave            295   \n",
       "\n",
       "                  to_station_name    usertype gender  birthyear   age  \n",
       "0       Kimball Ave & Belmont Ave  Subscriber   Male     1992.0  27.0  \n",
       "5   Pine Grove Ave & Waveland Ave  Subscriber   Male     1990.0  29.0  \n",
       "18            MLK Jr Dr & 29th St  Subscriber   Male     1995.0  24.0  \n",
       "20   Maplewood Ave & Peterson Ave  Subscriber   Male     1970.0  49.0  \n",
       "21           Broadway & Argyle St  Subscriber   Male     1993.0  26.0  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert birthyear to age\n",
    "divvy_df['age'] = (2019 - divvy_df['birthyear'])\n",
    "divvy_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# We added bins to help with the graphic representation of our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>from_station_id</th>\n",
       "      <th>from_station_name</th>\n",
       "      <th>to_station_id</th>\n",
       "      <th>to_station_name</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>age</th>\n",
       "      <th>Age Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23479388</td>\n",
       "      <td>2019-07-01 00:00:27</td>\n",
       "      <td>2019-07-01 00:20:41</td>\n",
       "      <td>3591</td>\n",
       "      <td>1,214.0</td>\n",
       "      <td>117</td>\n",
       "      <td>Wilton Ave &amp; Belmont Ave</td>\n",
       "      <td>497</td>\n",
       "      <td>Kimball Ave &amp; Belmont Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23479393</td>\n",
       "      <td>2019-07-01 00:02:21</td>\n",
       "      <td>2019-07-01 00:07:31</td>\n",
       "      <td>4941</td>\n",
       "      <td>310.0</td>\n",
       "      <td>300</td>\n",
       "      <td>Broadway &amp; Barry Ave</td>\n",
       "      <td>232</td>\n",
       "      <td>Pine Grove Ave &amp; Waveland Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>23479406</td>\n",
       "      <td>2019-07-01 00:06:51</td>\n",
       "      <td>2019-07-01 00:26:22</td>\n",
       "      <td>2758</td>\n",
       "      <td>1,171.0</td>\n",
       "      <td>624</td>\n",
       "      <td>Dearborn St &amp; Van Buren St</td>\n",
       "      <td>237</td>\n",
       "      <td>MLK Jr Dr &amp; 29th St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1995.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>23479408</td>\n",
       "      <td>2019-07-01 00:08:24</td>\n",
       "      <td>2019-07-01 00:20:07</td>\n",
       "      <td>2447</td>\n",
       "      <td>703.0</td>\n",
       "      <td>239</td>\n",
       "      <td>Western Ave &amp; Leland Ave</td>\n",
       "      <td>455</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>40-49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>23479409</td>\n",
       "      <td>2019-07-01 00:08:40</td>\n",
       "      <td>2019-07-01 00:27:50</td>\n",
       "      <td>6336</td>\n",
       "      <td>1,150.0</td>\n",
       "      <td>257</td>\n",
       "      <td>Lincoln Ave &amp; Waveland Ave</td>\n",
       "      <td>295</td>\n",
       "      <td>Broadway &amp; Argyle St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trip_id           start_time             end_time  bikeid tripduration  \\\n",
       "0   23479388  2019-07-01 00:00:27  2019-07-01 00:20:41    3591      1,214.0   \n",
       "5   23479393  2019-07-01 00:02:21  2019-07-01 00:07:31    4941        310.0   \n",
       "18  23479406  2019-07-01 00:06:51  2019-07-01 00:26:22    2758      1,171.0   \n",
       "20  23479408  2019-07-01 00:08:24  2019-07-01 00:20:07    2447        703.0   \n",
       "21  23479409  2019-07-01 00:08:40  2019-07-01 00:27:50    6336      1,150.0   \n",
       "\n",
       "    from_station_id           from_station_name  to_station_id  \\\n",
       "0               117    Wilton Ave & Belmont Ave            497   \n",
       "5               300        Broadway & Barry Ave            232   \n",
       "18              624  Dearborn St & Van Buren St            237   \n",
       "20              239    Western Ave & Leland Ave            455   \n",
       "21              257  Lincoln Ave & Waveland Ave            295   \n",
       "\n",
       "                  to_station_name    usertype gender  birthyear   age  \\\n",
       "0       Kimball Ave & Belmont Ave  Subscriber   Male     1992.0  27.0   \n",
       "5   Pine Grove Ave & Waveland Ave  Subscriber   Male     1990.0  29.0   \n",
       "18            MLK Jr Dr & 29th St  Subscriber   Male     1995.0  24.0   \n",
       "20   Maplewood Ave & Peterson Ave  Subscriber   Male     1970.0  49.0   \n",
       "21           Broadway & Argyle St  Subscriber   Male     1993.0  26.0   \n",
       "\n",
       "   Age Group  \n",
       "0      20-29  \n",
       "5      20-29  \n",
       "18     20-29  \n",
       "20     40-49  \n",
       "21     20-29  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create bins for age\n",
    "bins = [10, 20, 30, 40, 50, 60, 70, 80, 90]\n",
    "\n",
    "# Create the names for the bins\n",
    "group_names = [\"11-19\", \"20-29\", \"30-39\", \"40-49\", \"50-59\", \"60-69\", \"70-79\", \"80-89\"]\n",
    "divvy_df[\"Age Group\"] = pd.cut(divvy_df[\"age\"], bins, labels=group_names, include_lowest=True)\n",
    "divvy_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The 'tripduration' column is in seconds, but it did not seem to be accurate upon further analysis. Here we manually created the time in minutes as the 'time difference' column to confirm the accuracy of the time. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>from_station_id</th>\n",
       "      <th>from_station_name</th>\n",
       "      <th>to_station_id</th>\n",
       "      <th>to_station_name</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>age</th>\n",
       "      <th>Age Group</th>\n",
       "      <th>time difference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23479388</td>\n",
       "      <td>2019-07-01 00:00:27</td>\n",
       "      <td>2019-07-01 00:20:41</td>\n",
       "      <td>3591</td>\n",
       "      <td>1,214.0</td>\n",
       "      <td>117</td>\n",
       "      <td>Wilton Ave &amp; Belmont Ave</td>\n",
       "      <td>497</td>\n",
       "      <td>Kimball Ave &amp; Belmont Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>20.233333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23479393</td>\n",
       "      <td>2019-07-01 00:02:21</td>\n",
       "      <td>2019-07-01 00:07:31</td>\n",
       "      <td>4941</td>\n",
       "      <td>310.0</td>\n",
       "      <td>300</td>\n",
       "      <td>Broadway &amp; Barry Ave</td>\n",
       "      <td>232</td>\n",
       "      <td>Pine Grove Ave &amp; Waveland Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>5.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>23479406</td>\n",
       "      <td>2019-07-01 00:06:51</td>\n",
       "      <td>2019-07-01 00:26:22</td>\n",
       "      <td>2758</td>\n",
       "      <td>1,171.0</td>\n",
       "      <td>624</td>\n",
       "      <td>Dearborn St &amp; Van Buren St</td>\n",
       "      <td>237</td>\n",
       "      <td>MLK Jr Dr &amp; 29th St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1995.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>19.516667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>23479408</td>\n",
       "      <td>2019-07-01 00:08:24</td>\n",
       "      <td>2019-07-01 00:20:07</td>\n",
       "      <td>2447</td>\n",
       "      <td>703.0</td>\n",
       "      <td>239</td>\n",
       "      <td>Western Ave &amp; Leland Ave</td>\n",
       "      <td>455</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>40-49</td>\n",
       "      <td>11.716667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>23479409</td>\n",
       "      <td>2019-07-01 00:08:40</td>\n",
       "      <td>2019-07-01 00:27:50</td>\n",
       "      <td>6336</td>\n",
       "      <td>1,150.0</td>\n",
       "      <td>257</td>\n",
       "      <td>Lincoln Ave &amp; Waveland Ave</td>\n",
       "      <td>295</td>\n",
       "      <td>Broadway &amp; Argyle St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>19.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1640713</th>\n",
       "      <td>25223635</td>\n",
       "      <td>2019-09-30 23:56:27</td>\n",
       "      <td>2019-10-01 00:06:20</td>\n",
       "      <td>5690</td>\n",
       "      <td>593.0</td>\n",
       "      <td>84</td>\n",
       "      <td>Milwaukee Ave &amp; Grand Ave</td>\n",
       "      <td>134</td>\n",
       "      <td>Peoria St &amp; Jackson Blvd</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>9.883333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1640714</th>\n",
       "      <td>25223636</td>\n",
       "      <td>2019-09-30 23:57:02</td>\n",
       "      <td>2019-10-01 00:02:13</td>\n",
       "      <td>5079</td>\n",
       "      <td>310.0</td>\n",
       "      <td>268</td>\n",
       "      <td>Lake Shore Dr &amp; North Blvd</td>\n",
       "      <td>85</td>\n",
       "      <td>Michigan Ave &amp; Oak St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>5.183333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1640715</th>\n",
       "      <td>25223637</td>\n",
       "      <td>2019-09-30 23:57:45</td>\n",
       "      <td>2019-10-01 00:01:41</td>\n",
       "      <td>5535</td>\n",
       "      <td>235.0</td>\n",
       "      <td>423</td>\n",
       "      <td>University Ave &amp; 57th St</td>\n",
       "      <td>426</td>\n",
       "      <td>Ellis Ave &amp; 60th St</td>\n",
       "      <td>Customer</td>\n",
       "      <td>Male</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>11-19</td>\n",
       "      <td>3.933333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1640716</th>\n",
       "      <td>25223638</td>\n",
       "      <td>2019-09-30 23:57:47</td>\n",
       "      <td>2019-10-01 00:04:41</td>\n",
       "      <td>6120</td>\n",
       "      <td>414.0</td>\n",
       "      <td>110</td>\n",
       "      <td>Dearborn St &amp; Erie St</td>\n",
       "      <td>31</td>\n",
       "      <td>Franklin St &amp; Chicago Ave (Temp)</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1986.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>30-39</td>\n",
       "      <td>6.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1640717</th>\n",
       "      <td>25223639</td>\n",
       "      <td>2019-09-30 23:59:37</td>\n",
       "      <td>2019-10-01 00:05:17</td>\n",
       "      <td>60</td>\n",
       "      <td>339.0</td>\n",
       "      <td>265</td>\n",
       "      <td>Cottage Grove Ave &amp; Oakwood Blvd</td>\n",
       "      <td>179</td>\n",
       "      <td>MLK Jr Dr &amp; Pershing Rd</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Female</td>\n",
       "      <td>1996.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>5.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1353368 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          trip_id           start_time             end_time  bikeid  \\\n",
       "0        23479388  2019-07-01 00:00:27  2019-07-01 00:20:41    3591   \n",
       "5        23479393  2019-07-01 00:02:21  2019-07-01 00:07:31    4941   \n",
       "18       23479406  2019-07-01 00:06:51  2019-07-01 00:26:22    2758   \n",
       "20       23479408  2019-07-01 00:08:24  2019-07-01 00:20:07    2447   \n",
       "21       23479409  2019-07-01 00:08:40  2019-07-01 00:27:50    6336   \n",
       "...           ...                  ...                  ...     ...   \n",
       "1640713  25223635  2019-09-30 23:56:27  2019-10-01 00:06:20    5690   \n",
       "1640714  25223636  2019-09-30 23:57:02  2019-10-01 00:02:13    5079   \n",
       "1640715  25223637  2019-09-30 23:57:45  2019-10-01 00:01:41    5535   \n",
       "1640716  25223638  2019-09-30 23:57:47  2019-10-01 00:04:41    6120   \n",
       "1640717  25223639  2019-09-30 23:59:37  2019-10-01 00:05:17      60   \n",
       "\n",
       "        tripduration  from_station_id                 from_station_name  \\\n",
       "0            1,214.0              117          Wilton Ave & Belmont Ave   \n",
       "5              310.0              300              Broadway & Barry Ave   \n",
       "18           1,171.0              624        Dearborn St & Van Buren St   \n",
       "20             703.0              239          Western Ave & Leland Ave   \n",
       "21           1,150.0              257        Lincoln Ave & Waveland Ave   \n",
       "...              ...              ...                               ...   \n",
       "1640713        593.0               84         Milwaukee Ave & Grand Ave   \n",
       "1640714        310.0              268        Lake Shore Dr & North Blvd   \n",
       "1640715        235.0              423          University Ave & 57th St   \n",
       "1640716        414.0              110             Dearborn St & Erie St   \n",
       "1640717        339.0              265  Cottage Grove Ave & Oakwood Blvd   \n",
       "\n",
       "         to_station_id                   to_station_name    usertype  gender  \\\n",
       "0                  497         Kimball Ave & Belmont Ave  Subscriber    Male   \n",
       "5                  232     Pine Grove Ave & Waveland Ave  Subscriber    Male   \n",
       "18                 237               MLK Jr Dr & 29th St  Subscriber    Male   \n",
       "20                 455      Maplewood Ave & Peterson Ave  Subscriber    Male   \n",
       "21                 295              Broadway & Argyle St  Subscriber    Male   \n",
       "...                ...                               ...         ...     ...   \n",
       "1640713            134          Peoria St & Jackson Blvd  Subscriber    Male   \n",
       "1640714             85             Michigan Ave & Oak St  Subscriber    Male   \n",
       "1640715            426               Ellis Ave & 60th St    Customer    Male   \n",
       "1640716             31  Franklin St & Chicago Ave (Temp)  Subscriber    Male   \n",
       "1640717            179           MLK Jr Dr & Pershing Rd  Subscriber  Female   \n",
       "\n",
       "         birthyear   age Age Group  time difference  \n",
       "0           1992.0  27.0     20-29        20.233333  \n",
       "5           1990.0  29.0     20-29         5.166667  \n",
       "18          1995.0  24.0     20-29        19.516667  \n",
       "20          1970.0  49.0     40-49        11.716667  \n",
       "21          1993.0  26.0     20-29        19.166667  \n",
       "...            ...   ...       ...              ...  \n",
       "1640713     1993.0  26.0     20-29         9.883333  \n",
       "1640714     1992.0  27.0     20-29         5.183333  \n",
       "1640715     2000.0  19.0     11-19         3.933333  \n",
       "1640716     1986.0  33.0     30-39         6.900000  \n",
       "1640717     1996.0  23.0     20-29         5.666667  \n",
       "\n",
       "[1353368 rows x 15 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "divvy_df['time difference'] = ((pd.to_datetime(divvy_df['end_time']) - \n",
    "                            pd.to_datetime(divvy_df['start_time']))\n",
    "                                .dt.total_seconds() / 60)\n",
    "divvy_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Here we converted the 'start_time' and 'end_time' to the datetime format "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert date columns to datetime format\n",
    "divvy_df['start_time'] = pd.to_datetime(divvy_df['start_time'])\n",
    "divvy_df['end_time'] = pd.to_datetime(divvy_df['end_time'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Then with the columns in the datetime format, we sliced the date from the time into their own columns. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_id</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>from_station_id</th>\n",
       "      <th>from_station_name</th>\n",
       "      <th>to_station_id</th>\n",
       "      <th>to_station_name</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>age</th>\n",
       "      <th>Age Group</th>\n",
       "      <th>time difference</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23479388</td>\n",
       "      <td>00:00:27</td>\n",
       "      <td>00:20:41</td>\n",
       "      <td>3591</td>\n",
       "      <td>1,214.0</td>\n",
       "      <td>117</td>\n",
       "      <td>Wilton Ave &amp; Belmont Ave</td>\n",
       "      <td>497</td>\n",
       "      <td>Kimball Ave &amp; Belmont Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>20.233333</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2019-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23479393</td>\n",
       "      <td>00:02:21</td>\n",
       "      <td>00:07:31</td>\n",
       "      <td>4941</td>\n",
       "      <td>310.0</td>\n",
       "      <td>300</td>\n",
       "      <td>Broadway &amp; Barry Ave</td>\n",
       "      <td>232</td>\n",
       "      <td>Pine Grove Ave &amp; Waveland Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>5.166667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2019-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>23479406</td>\n",
       "      <td>00:06:51</td>\n",
       "      <td>00:26:22</td>\n",
       "      <td>2758</td>\n",
       "      <td>1,171.0</td>\n",
       "      <td>624</td>\n",
       "      <td>Dearborn St &amp; Van Buren St</td>\n",
       "      <td>237</td>\n",
       "      <td>MLK Jr Dr &amp; 29th St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1995.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>19.516667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2019-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>23479408</td>\n",
       "      <td>00:08:24</td>\n",
       "      <td>00:20:07</td>\n",
       "      <td>2447</td>\n",
       "      <td>703.0</td>\n",
       "      <td>239</td>\n",
       "      <td>Western Ave &amp; Leland Ave</td>\n",
       "      <td>455</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>40-49</td>\n",
       "      <td>11.716667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2019-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>23479409</td>\n",
       "      <td>00:08:40</td>\n",
       "      <td>00:27:50</td>\n",
       "      <td>6336</td>\n",
       "      <td>1,150.0</td>\n",
       "      <td>257</td>\n",
       "      <td>Lincoln Ave &amp; Waveland Ave</td>\n",
       "      <td>295</td>\n",
       "      <td>Broadway &amp; Argyle St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>20-29</td>\n",
       "      <td>19.166667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2019-07-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trip_id start_time  end_time  bikeid tripduration  from_station_id  \\\n",
       "0   23479388   00:00:27  00:20:41    3591      1,214.0              117   \n",
       "5   23479393   00:02:21  00:07:31    4941        310.0              300   \n",
       "18  23479406   00:06:51  00:26:22    2758      1,171.0              624   \n",
       "20  23479408   00:08:24  00:20:07    2447        703.0              239   \n",
       "21  23479409   00:08:40  00:27:50    6336      1,150.0              257   \n",
       "\n",
       "             from_station_name  to_station_id                to_station_name  \\\n",
       "0     Wilton Ave & Belmont Ave            497      Kimball Ave & Belmont Ave   \n",
       "5         Broadway & Barry Ave            232  Pine Grove Ave & Waveland Ave   \n",
       "18  Dearborn St & Van Buren St            237            MLK Jr Dr & 29th St   \n",
       "20    Western Ave & Leland Ave            455   Maplewood Ave & Peterson Ave   \n",
       "21  Lincoln Ave & Waveland Ave            295           Broadway & Argyle St   \n",
       "\n",
       "      usertype gender  birthyear   age Age Group  time difference  start_date  \\\n",
       "0   Subscriber   Male     1992.0  27.0     20-29        20.233333  2019-07-01   \n",
       "5   Subscriber   Male     1990.0  29.0     20-29         5.166667  2019-07-01   \n",
       "18  Subscriber   Male     1995.0  24.0     20-29        19.516667  2019-07-01   \n",
       "20  Subscriber   Male     1970.0  49.0     40-49        11.716667  2019-07-01   \n",
       "21  Subscriber   Male     1993.0  26.0     20-29        19.166667  2019-07-01   \n",
       "\n",
       "      end_date  \n",
       "0   2019-07-01  \n",
       "5   2019-07-01  \n",
       "18  2019-07-01  \n",
       "20  2019-07-01  \n",
       "21  2019-07-01  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Split date from time and create columns\n",
    "divvy_df['start_date'] = divvy_df['start_time'].dt.date\n",
    "divvy_df['start_time'] = divvy_df['start_time'].dt.time\n",
    "divvy_df['end_date'] = divvy_df['end_time'].dt.date\n",
    "divvy_df['end_time'] = divvy_df['end_time'].dt.time\n",
    "divvy_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# To make the dataframe easier to read, in light of the new columns, we reorganized the date and time columns to be next to each other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>bikeid</th>\n",
       "      <th>start_date</th>\n",
       "      <th>start_time</th>\n",
       "      <th>time difference</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_time</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>from_station_id</th>\n",
       "      <th>from_station_name</th>\n",
       "      <th>to_station_id</th>\n",
       "      <th>to_station_name</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthyear</th>\n",
       "      <th>age</th>\n",
       "      <th>Age Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23479388</td>\n",
       "      <td>3591</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:00:27</td>\n",
       "      <td>20.233333</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:20:41</td>\n",
       "      <td>1,214.0</td>\n",
       "      <td>117</td>\n",
       "      <td>Wilton Ave &amp; Belmont Ave</td>\n",
       "      <td>497</td>\n",
       "      <td>Kimball Ave &amp; Belmont Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23479393</td>\n",
       "      <td>4941</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:02:21</td>\n",
       "      <td>5.166667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:07:31</td>\n",
       "      <td>310.0</td>\n",
       "      <td>300</td>\n",
       "      <td>Broadway &amp; Barry Ave</td>\n",
       "      <td>232</td>\n",
       "      <td>Pine Grove Ave &amp; Waveland Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>23479406</td>\n",
       "      <td>2758</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:06:51</td>\n",
       "      <td>19.516667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:26:22</td>\n",
       "      <td>1,171.0</td>\n",
       "      <td>624</td>\n",
       "      <td>Dearborn St &amp; Van Buren St</td>\n",
       "      <td>237</td>\n",
       "      <td>MLK Jr Dr &amp; 29th St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1995.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>23479408</td>\n",
       "      <td>2447</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:08:24</td>\n",
       "      <td>11.716667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:20:07</td>\n",
       "      <td>703.0</td>\n",
       "      <td>239</td>\n",
       "      <td>Western Ave &amp; Leland Ave</td>\n",
       "      <td>455</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>40-49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>23479409</td>\n",
       "      <td>6336</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:08:40</td>\n",
       "      <td>19.166667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:27:50</td>\n",
       "      <td>1,150.0</td>\n",
       "      <td>257</td>\n",
       "      <td>Lincoln Ave &amp; Waveland Ave</td>\n",
       "      <td>295</td>\n",
       "      <td>Broadway &amp; Argyle St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>23479418</td>\n",
       "      <td>4525</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:15:06</td>\n",
       "      <td>21.566667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:36:40</td>\n",
       "      <td>1,294.0</td>\n",
       "      <td>240</td>\n",
       "      <td>Sheridan Rd &amp; Irving Park Rd</td>\n",
       "      <td>326</td>\n",
       "      <td>Clark St &amp; Leland Ave</td>\n",
       "      <td>Customer</td>\n",
       "      <td>Male</td>\n",
       "      <td>1987.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>30-39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>23479419</td>\n",
       "      <td>1132</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:18:53</td>\n",
       "      <td>27.066667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:45:57</td>\n",
       "      <td>1,624.0</td>\n",
       "      <td>6</td>\n",
       "      <td>Dusable Harbor</td>\n",
       "      <td>321</td>\n",
       "      <td>Wabash Ave &amp; 9th St</td>\n",
       "      <td>Customer</td>\n",
       "      <td>Female</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>23479420</td>\n",
       "      <td>5166</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:18:53</td>\n",
       "      <td>27.016667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:45:54</td>\n",
       "      <td>1,621.0</td>\n",
       "      <td>6</td>\n",
       "      <td>Dusable Harbor</td>\n",
       "      <td>321</td>\n",
       "      <td>Wabash Ave &amp; 9th St</td>\n",
       "      <td>Customer</td>\n",
       "      <td>Female</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>23479421</td>\n",
       "      <td>240</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:19:41</td>\n",
       "      <td>26.216667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:45:54</td>\n",
       "      <td>1,573.0</td>\n",
       "      <td>6</td>\n",
       "      <td>Dusable Harbor</td>\n",
       "      <td>321</td>\n",
       "      <td>Wabash Ave &amp; 9th St</td>\n",
       "      <td>Customer</td>\n",
       "      <td>Female</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>20-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>23479423</td>\n",
       "      <td>1830</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:22:56</td>\n",
       "      <td>16.716667</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>00:39:39</td>\n",
       "      <td>1,003.0</td>\n",
       "      <td>203</td>\n",
       "      <td>Western Ave &amp; 21st St</td>\n",
       "      <td>203</td>\n",
       "      <td>Western Ave &amp; 21st St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>40-49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     trip_id  bikeid  start_date start_time  time difference    end_date  \\\n",
       "0   23479388    3591  2019-07-01   00:00:27        20.233333  2019-07-01   \n",
       "5   23479393    4941  2019-07-01   00:02:21         5.166667  2019-07-01   \n",
       "18  23479406    2758  2019-07-01   00:06:51        19.516667  2019-07-01   \n",
       "20  23479408    2447  2019-07-01   00:08:24        11.716667  2019-07-01   \n",
       "21  23479409    6336  2019-07-01   00:08:40        19.166667  2019-07-01   \n",
       "29  23479418    4525  2019-07-01   00:15:06        21.566667  2019-07-01   \n",
       "30  23479419    1132  2019-07-01   00:18:53        27.066667  2019-07-01   \n",
       "31  23479420    5166  2019-07-01   00:18:53        27.016667  2019-07-01   \n",
       "32  23479421     240  2019-07-01   00:19:41        26.216667  2019-07-01   \n",
       "34  23479423    1830  2019-07-01   00:22:56        16.716667  2019-07-01   \n",
       "\n",
       "    end_time tripduration  from_station_id             from_station_name  \\\n",
       "0   00:20:41      1,214.0              117      Wilton Ave & Belmont Ave   \n",
       "5   00:07:31        310.0              300          Broadway & Barry Ave   \n",
       "18  00:26:22      1,171.0              624    Dearborn St & Van Buren St   \n",
       "20  00:20:07        703.0              239      Western Ave & Leland Ave   \n",
       "21  00:27:50      1,150.0              257    Lincoln Ave & Waveland Ave   \n",
       "29  00:36:40      1,294.0              240  Sheridan Rd & Irving Park Rd   \n",
       "30  00:45:57      1,624.0                6                Dusable Harbor   \n",
       "31  00:45:54      1,621.0                6                Dusable Harbor   \n",
       "32  00:45:54      1,573.0                6                Dusable Harbor   \n",
       "34  00:39:39      1,003.0              203         Western Ave & 21st St   \n",
       "\n",
       "    to_station_id                to_station_name    usertype  gender  \\\n",
       "0             497      Kimball Ave & Belmont Ave  Subscriber    Male   \n",
       "5             232  Pine Grove Ave & Waveland Ave  Subscriber    Male   \n",
       "18            237            MLK Jr Dr & 29th St  Subscriber    Male   \n",
       "20            455   Maplewood Ave & Peterson Ave  Subscriber    Male   \n",
       "21            295           Broadway & Argyle St  Subscriber    Male   \n",
       "29            326          Clark St & Leland Ave    Customer    Male   \n",
       "30            321            Wabash Ave & 9th St    Customer  Female   \n",
       "31            321            Wabash Ave & 9th St    Customer  Female   \n",
       "32            321            Wabash Ave & 9th St    Customer  Female   \n",
       "34            203          Western Ave & 21st St  Subscriber    Male   \n",
       "\n",
       "    birthyear   age Age Group  \n",
       "0      1992.0  27.0     20-29  \n",
       "5      1990.0  29.0     20-29  \n",
       "18     1995.0  24.0     20-29  \n",
       "20     1970.0  49.0     40-49  \n",
       "21     1993.0  26.0     20-29  \n",
       "29     1987.0  32.0     30-39  \n",
       "30     1998.0  21.0     20-29  \n",
       "31     1997.0  22.0     20-29  \n",
       "32     1997.0  22.0     20-29  \n",
       "34     1970.0  49.0     40-49  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Organize columns in dataframe\n",
    "divvy_df = divvy_df[[\"trip_id\", \"bikeid\", \"start_date\", \"start_time\", \"time difference\", \"end_date\", \"end_time\", \"tripduration\", \"from_station_id\", \"from_station_name\", \"to_station_id\", \"to_station_name\", \"usertype\", \"gender\", \"birthyear\", \"age\", \"Age Group\"]]\n",
    "divvy_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}