capstone-case-study-data-preparation.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Case Study Data Preparation"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data used for the case study on Cyclistic is sourced from https://divvy-tripdata.s3.amazonaws.com/index.html. The dataset contains historical data on bike rides. In which, contains the columns:\n",
    "* **ride_id:** A unique identifier for each bike ride.\n",
    "* **rideable_type:** The type of bike for which what the user used for the bike ride.\n",
    "* **started_at:** The timestamp indicating the start time of the ride.\n",
    "* **ended_at:** The timestamp indicating the end time of the ride.\n",
    "* **start_station_name:** The name or location description of the docking station where the ride started.\n",
    "* **start_station_id:** The unique identifier of the docking station where the ride started.\n",
    "* **end_station_name:** The name or location description of the docking station where the ride ended.\n",
    "* **end_station_id:** The unique identifier of the docking station where the ride ended.\n",
    "* **start_lat:** The latitude coordinate of the docking station where the ride started.\n",
    "* **start_lng:** The longitude coordinate of the docking station where the ride started.\n",
    "* **end_lat:** The latitude coordinate of the docking station where the ride ended.\n",
    "* **end_lng:** The longitude coordinate of the docking station where the ride ended.\n",
    "* **member_casual:** The type of user, indicating whether the customer is a casual pass user or an annual member."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the a data composing of only **77% of it complete and 23% incomplete**, cleaning, preparing, and especially addressing those missing values would be crucial in order to have a sound analysis. \n",
    "\n",
    "![](reports/data-preparation/dataset-composition-complete-vs-observations-with-incomplete-data.png)\n",
    "![](reports/data-preparation/observations-with-incomplete-data-missing-data-count-by-variable.png)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initialization "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import warnings\n",
    "\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading the merged dataset: Trip data from year June-December of 2022  and January-May of 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/csv/divvy-tripdata-202206-202305-merged.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>ride_id</th>\n",
       "      <th>rideable_type</th>\n",
       "      <th>started_at</th>\n",
       "      <th>ended_at</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_lng</th>\n",
       "      <th>end_lat</th>\n",
       "      <th>end_lng</th>\n",
       "      <th>member_casual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>B12AD6565494C368</td>\n",
       "      <td>classic_bike</td>\n",
       "      <td>2022-06-09 22:28:32</td>\n",
       "      <td>2022-06-09 22:52:17</td>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "      <td>41.922695</td>\n",
       "      <td>-87.697153</td>\n",
       "      <td>41.922695</td>\n",
       "      <td>-87.697153</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>BAD4CB075003A605</td>\n",
       "      <td>electric_bike</td>\n",
       "      <td>2022-06-19 17:08:23</td>\n",
       "      <td>2022-06-19 17:08:25</td>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "      <td>41.922613</td>\n",
       "      <td>-87.697148</td>\n",
       "      <td>41.922695</td>\n",
       "      <td>-87.697153</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>76DAD9FC95774B53</td>\n",
       "      <td>electric_bike</td>\n",
       "      <td>2022-06-26 23:59:44</td>\n",
       "      <td>2022-06-27 00:25:26</td>\n",
       "      <td>Burnham Greenway &amp; 105th St</td>\n",
       "      <td>20222</td>\n",
       "      <td>Burnham Greenway &amp; 105th St</td>\n",
       "      <td>20222</td>\n",
       "      <td>41.704628</td>\n",
       "      <td>-87.528414</td>\n",
       "      <td>41.704575</td>\n",
       "      <td>-87.528232</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>47DE68ACCA138C13</td>\n",
       "      <td>electric_bike</td>\n",
       "      <td>2022-06-27 11:40:53</td>\n",
       "      <td>2022-06-27 11:50:16</td>\n",
       "      <td>Wood St &amp; Chicago Ave</td>\n",
       "      <td>637</td>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>13256</td>\n",
       "      <td>41.895611</td>\n",
       "      <td>-87.672098</td>\n",
       "      <td>41.903029</td>\n",
       "      <td>-87.697474</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>5D899636D3334ED5</td>\n",
       "      <td>classic_bike</td>\n",
       "      <td>2022-06-27 16:01:13</td>\n",
       "      <td>2022-06-27 16:35:56</td>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>13256</td>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>13256</td>\n",
       "      <td>41.903029</td>\n",
       "      <td>-87.697474</td>\n",
       "      <td>41.903029</td>\n",
       "      <td>-87.697474</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              ride_id  rideable_type           started_at  \\\n",
       "98   B12AD6565494C368   classic_bike  2022-06-09 22:28:32   \n",
       "137  BAD4CB075003A605  electric_bike  2022-06-19 17:08:23   \n",
       "214  76DAD9FC95774B53  electric_bike  2022-06-26 23:59:44   \n",
       "216  47DE68ACCA138C13  electric_bike  2022-06-27 11:40:53   \n",
       "233  5D899636D3334ED5   classic_bike  2022-06-27 16:01:13   \n",
       "\n",
       "                ended_at              start_station_name start_station_id  \\\n",
       "98   2022-06-09 22:52:17  California Ave & Milwaukee Ave            13084   \n",
       "137  2022-06-19 17:08:25  California Ave & Milwaukee Ave            13084   \n",
       "214  2022-06-27 00:25:26     Burnham Greenway & 105th St            20222   \n",
       "216  2022-06-27 11:50:16           Wood St & Chicago Ave              637   \n",
       "233  2022-06-27 16:35:56    California Ave & Division St            13256   \n",
       "\n",
       "                   end_station_name end_station_id  start_lat  start_lng  \\\n",
       "98   California Ave & Milwaukee Ave          13084  41.922695 -87.697153   \n",
       "137  California Ave & Milwaukee Ave          13084  41.922613 -87.697148   \n",
       "214     Burnham Greenway & 105th St          20222  41.704628 -87.528414   \n",
       "216    California Ave & Division St          13256  41.895611 -87.672098   \n",
       "233    California Ave & Division St          13256  41.903029 -87.697474   \n",
       "\n",
       "       end_lat    end_lng member_casual  \n",
       "98   41.922695 -87.697153        casual  \n",
       "137  41.922695 -87.697153        casual  \n",
       "214  41.704575 -87.528232        casual  \n",
       "216  41.903029 -87.697474        casual  \n",
       "233  41.903029 -87.697474        casual  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5829030 entries, 0 to 5829029\n",
      "Data columns (total 13 columns):\n",
      " #   Column              Non-Null Count    Dtype  \n",
      "---  ------              --------------    -----  \n",
      " 0   ride_id             5829030 non-null  object \n",
      " 1   rideable_type       5829030 non-null  object \n",
      " 2   started_at          5829030 non-null  object \n",
      " 3   ended_at            5829030 non-null  object \n",
      " 4   start_station_name  4994485 non-null  object \n",
      " 5   start_station_id    4994353 non-null  object \n",
      " 6   end_station_name    4937273 non-null  object \n",
      " 7   end_station_id      4937132 non-null  object \n",
      " 8   start_lat           5829030 non-null  float64\n",
      " 9   start_lng           5829030 non-null  float64\n",
      " 10  end_lat             5823069 non-null  float64\n",
      " 11  end_lng             5823069 non-null  float64\n",
      " 12  member_casual       5829030 non-null  object \n",
      "dtypes: float64(4), object(9)\n",
      "memory usage: 578.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info(verbose=True ,show_counts=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning value formats "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking rideable_type value counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "electric_bike    3083395\n",
       "classic_bike     2588434\n",
       "docked_bike       157201\n",
       "Name: rideable_type, dtype: int64"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['rideable_type'].value_counts()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With start_station_id and possibly end_station_id showing some values that behaves like a decimal as seen with the trailling \".0\", which is unproper for the rest of the format and a format for an 'id' specifically, this must be cleaned and removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Burnham Greenway &amp; 105th St</td>\n",
       "      <td>20222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>Wood St &amp; Chicago Ave</td>\n",
       "      <td>637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>13256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>Morgan Ave &amp; 14th Pl</td>\n",
       "      <td>TA1306000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5780082</th>\n",
       "      <td>Public Rack - Horner &amp; 85th</td>\n",
       "      <td>1273.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5795964</th>\n",
       "      <td>Public Rack - Central &amp; Leland</td>\n",
       "      <td>1192.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5800505</th>\n",
       "      <td>Public Rack - N. Oakview and W. Gregory St</td>\n",
       "      <td>1180.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5808498</th>\n",
       "      <td>Public Rack - Pulaski &amp; 84th</td>\n",
       "      <td>1260.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5818696</th>\n",
       "      <td>Public Rack - Archer Park</td>\n",
       "      <td>828</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1759 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 start_station_name start_station_id\n",
       "98                   California Ave & Milwaukee Ave            13084\n",
       "214                     Burnham Greenway & 105th St            20222\n",
       "216                           Wood St & Chicago Ave              637\n",
       "233                    California Ave & Division St            13256\n",
       "408                            Morgan Ave & 14th Pl     TA1306000002\n",
       "...                                             ...              ...\n",
       "5780082                 Public Rack - Horner & 85th           1273.0\n",
       "5795964              Public Rack - Central & Leland           1192.0\n",
       "5800505  Public Rack - N. Oakview and W. Gregory St           1180.0\n",
       "5808498                Public Rack - Pulaski & 84th           1260.0\n",
       "5818696                   Public Rack - Archer Park              828\n",
       "\n",
       "[1759 rows x 2 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna().loc[:, ['start_station_name', 'start_station_id']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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>end_station_name</th>\n",
       "      <th>end_station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Burnham Greenway &amp; 105th St</td>\n",
       "      <td>20222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>13256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>Morgan Ave &amp; 14th Pl</td>\n",
       "      <td>TA1306000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>756</th>\n",
       "      <td>Wood St &amp; Chicago Ave</td>\n",
       "      <td>637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5809585</th>\n",
       "      <td>Public Rack - Morgan St &amp; 87th St</td>\n",
       "      <td>640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5811892</th>\n",
       "      <td>Public Rack - Avers &amp; Granville</td>\n",
       "      <td>1215.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5812214</th>\n",
       "      <td>Public Rack - Karlov Ave &amp; Lawrence Ave</td>\n",
       "      <td>1127.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5813001</th>\n",
       "      <td>Public Rack - Mango &amp; Higgins</td>\n",
       "      <td>1202.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5827558</th>\n",
       "      <td>Public Rack - Kostner Ave &amp; 16th St</td>\n",
       "      <td>934</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1791 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                end_station_name end_station_id\n",
       "98                California Ave & Milwaukee Ave          13084\n",
       "214                  Burnham Greenway & 105th St          20222\n",
       "216                 California Ave & Division St          13256\n",
       "408                         Morgan Ave & 14th Pl   TA1306000002\n",
       "756                        Wood St & Chicago Ave            637\n",
       "...                                          ...            ...\n",
       "5809585        Public Rack - Morgan St & 87th St            640\n",
       "5811892          Public Rack - Avers & Granville         1215.0\n",
       "5812214  Public Rack - Karlov Ave & Lawrence Ave         1127.0\n",
       "5813001            Public Rack - Mango & Higgins         1202.0\n",
       "5827558      Public Rack - Kostner Ave & 16th St            934\n",
       "\n",
       "[1791 rows x 2 columns]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna().loc[:, ['end_station_name', 'end_station_id']].drop_duplicates()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cleaning the ids of the trailling \".0\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1273'"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Testing the code before doing an inplace operation\n",
    "df['start_station_id'].str.replace(\".0\", \"\", regex = False)[5780082]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Burnham Greenway &amp; 105th St</td>\n",
       "      <td>20222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>Wood St &amp; Chicago Ave</td>\n",
       "      <td>637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>13256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>Morgan Ave &amp; 14th Pl</td>\n",
       "      <td>TA1306000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5780082</th>\n",
       "      <td>Public Rack - Horner &amp; 85th</td>\n",
       "      <td>1273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5795964</th>\n",
       "      <td>Public Rack - Central &amp; Leland</td>\n",
       "      <td>1192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5800505</th>\n",
       "      <td>Public Rack - N. Oakview and W. Gregory St</td>\n",
       "      <td>1180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5808498</th>\n",
       "      <td>Public Rack - Pulaski &amp; 84th</td>\n",
       "      <td>1260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5818696</th>\n",
       "      <td>Public Rack - Archer Park</td>\n",
       "      <td>828</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1759 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 start_station_name start_station_id\n",
       "98                   California Ave & Milwaukee Ave            13084\n",
       "214                     Burnham Greenway & 105th St            20222\n",
       "216                           Wood St & Chicago Ave              637\n",
       "233                    California Ave & Division St            13256\n",
       "408                            Morgan Ave & 14th Pl     TA1306000002\n",
       "...                                             ...              ...\n",
       "5780082                 Public Rack - Horner & 85th             1273\n",
       "5795964              Public Rack - Central & Leland             1192\n",
       "5800505  Public Rack - N. Oakview and W. Gregory St             1180\n",
       "5808498                Public Rack - Pulaski & 84th             1260\n",
       "5818696                   Public Rack - Archer Park              828\n",
       "\n",
       "[1759 rows x 2 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cleaning of the trailling \".0s\"\n",
    "df['start_station_id'] = df['start_station_id'].str.replace(\".0\", \"\", regex = False)\n",
    "df['end_station_id'] = df['end_station_id'].str.replace(\".0\", \"\", regex = False)\n",
    "\n",
    "df.dropna().loc[:, ['start_station_name', 'start_station_id']].drop_duplicates()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Solving inconsistencies"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Having the station ids cleaned of the trailling \".0\"s, station names should be checked. In which, one station id must correspond to only one station name. Therefore, in the dataset where missing values along with duplicates are dropped, every value counts of station id should be equal to 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "535             3\n",
       "588             3\n",
       "661             3\n",
       "569             3\n",
       "577             3\n",
       "               ..\n",
       "KA1504000151    1\n",
       "TA1307000131    1\n",
       "TA1309000015    1\n",
       "021320          1\n",
       "1260            1\n",
       "Name: start_station_id, Length: 1419, dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df\n",
    " .dropna()\n",
    " .loc[:, ['start_station_name', 'start_station_id']]\n",
    " .drop_duplicates()['start_station_id']\n",
    " .value_counts())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the station names being inconsistent and having variations, this needs to be addressed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5473</th>\n",
       "      <td>Pulaski Rd &amp; Congress Pkwy</td>\n",
       "      <td>535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>662127</th>\n",
       "      <td>Zapata Academy</td>\n",
       "      <td>535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>925183</th>\n",
       "      <td>Public Rack - Zapata Academy</td>\n",
       "      <td>535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262684</th>\n",
       "      <td>South Chicago Ave &amp; 83rd St</td>\n",
       "      <td>588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1886534</th>\n",
       "      <td>Yale Ave &amp; 119th St</td>\n",
       "      <td>588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4996242</th>\n",
       "      <td>Public Rack - Yale Ave &amp; 119th St</td>\n",
       "      <td>588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86684</th>\n",
       "      <td>Evanston Civic Center</td>\n",
       "      <td>661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278193</th>\n",
       "      <td>Langley Ave &amp; 79th St</td>\n",
       "      <td>661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3071593</th>\n",
       "      <td>Public Rack - Langley Ave &amp; 79th St</td>\n",
       "      <td>661</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          start_station_name start_station_id\n",
       "5473              Pulaski Rd & Congress Pkwy              535\n",
       "662127                        Zapata Academy              535\n",
       "925183          Public Rack - Zapata Academy              535\n",
       "262684           South Chicago Ave & 83rd St              588\n",
       "1886534                  Yale Ave & 119th St              588\n",
       "4996242    Public Rack - Yale Ave & 119th St              588\n",
       "86684                  Evanston Civic Center              661\n",
       "278193                 Langley Ave & 79th St              661\n",
       "3071593  Public Rack - Langley Ave & 79th St              661"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df[df['start_station_id'].isin(['535', '588', '661'])]\n",
    " .dropna()\n",
    " .loc[:, ['start_station_name', 'start_station_id']]\n",
    " .drop_duplicates()\n",
    " .sort_values(by = 'start_station_id'))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With inconsistencies on the start_station_name and possibly end_station_name, it would be best to update the values using the [bicycle stations open data from Chicago Data Portal](https://data.cityofchicago.org/Transportation/Divvy-Bicycle-Stations/bbyy-e7gq/data) consisting of the same variables in order to be consistent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1419 entries, 0 to 1418\n",
      "Data columns (total 8 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   id                1419 non-null   object \n",
      " 1   station_name      1419 non-null   object \n",
      " 2   total_docks       1419 non-null   int64  \n",
      " 3   docks_in_service  1419 non-null   int64  \n",
      " 4   status            1419 non-null   object \n",
      " 5   latitude          1419 non-null   float64\n",
      " 6   longitude         1419 non-null   float64\n",
      " 7   location          1419 non-null   object \n",
      "dtypes: float64(2), int64(2), object(4)\n",
      "memory usage: 88.8+ KB\n"
     ]
    }
   ],
   "source": [
    "# Loading the dataset\n",
    "bicycle_stations_df = pd.read_csv('data/csv/Divvy_Bicycle_Stations.csv')\n",
    "\n",
    "# Cleaning dataset columns \n",
    "bicycle_stations_df.columns = bicycle_stations_df.columns.str.lower().str.replace(' ', '_', regex = False)\n",
    "bicycle_stations_df['id'] = bicycle_stations_df['id'].astype(object)\n",
    "\n",
    "bicycle_stations_df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking if id values match between the bicycle stations dataset and the main dataset using **Troy St & Jackson Blvd** station as a point of reference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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>station_name</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Troy St &amp; Jackson Blvd</td>\n",
       "      <td>1594046383808271024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Central Park Ave &amp; Bloomingdale Ave</td>\n",
       "      <td>641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Public Rack - Cornell Ave &amp; 87th Pl</td>\n",
       "      <td>1683527931525155814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Racine Ave &amp; 35th St</td>\n",
       "      <td>367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Public Rack - Francisco Ave &amp; Touhy Ave</td>\n",
       "      <td>1673852313397164648</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              station_name                   id\n",
       "0                   Troy St & Jackson Blvd  1594046383808271024\n",
       "1      Central Park Ave & Bloomingdale Ave                  641\n",
       "2      Public Rack - Cornell Ave & 87th Pl  1683527931525155814\n",
       "3                     Racine Ave & 35th St                  367\n",
       "4  Public Rack - Francisco Ave & Touhy Ave  1673852313397164648"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bicycle_stations_df.head().loc[:, ['station_name', 'id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>944314</th>\n",
       "      <td>Troy St &amp; Jackson Blvd</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1317496</th>\n",
       "      <td>Troy St &amp; Jackson Blvd</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1332381</th>\n",
       "      <td>Troy St &amp; Jackson Blvd</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1935051</th>\n",
       "      <td>Troy St &amp; Jackson Blvd</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018346</th>\n",
       "      <td>Troy St &amp; Jackson Blvd</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             start_station_name start_station_id\n",
       "944314   Troy St & Jackson Blvd              417\n",
       "1317496  Troy St & Jackson Blvd              417\n",
       "1332381  Troy St & Jackson Blvd              417\n",
       "1935051  Troy St & Jackson Blvd              417\n",
       "2018346  Troy St & Jackson Blvd              417"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df.dropna()[\n",
    "    df.dropna()['start_station_name']\n",
    "    .str.contains('Troy St & Jackson')]\n",
    ".head()\n",
    ".loc[:, ['start_station_name', 'start_station_id']])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the id to station name values not matching, the best route to take would be updating the station names using main dataset itself with the station name of the id's first occurence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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>station_name</th>\n",
       "      <th>station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Burnham Greenway &amp; 105th St</td>\n",
       "      <td>20222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wood St &amp; Chicago Ave</td>\n",
       "      <td>637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>13256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Morgan Ave &amp; 14th Pl</td>\n",
       "      <td>TA1306000002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     station_name    station_id\n",
       "0  California Ave & Milwaukee Ave         13084\n",
       "1     Burnham Greenway & 105th St         20222\n",
       "2           Wood St & Chicago Ave           637\n",
       "3    California Ave & Division St         13256\n",
       "4            Morgan Ave & 14th Pl  TA1306000002"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_name_to_id_column_names_mapping = {\n",
    "    'start_station_name': 'station_name', 'start_station_id': 'station_id',\n",
    "    'end_station_name': 'station_name', 'end_station_id': 'station_id'}\n",
    "\n",
    "station_name_to_id_df = (\n",
    "    pd.concat([\n",
    "        df.dropna().loc[:, ['start_station_name', 'start_station_id']].drop_duplicates().rename(columns = station_name_to_id_column_names_mapping),\n",
    "        df.dropna().loc[:, ['end_station_name', 'end_station_id']].drop_duplicates().rename(columns = station_name_to_id_column_names_mapping)], \n",
    "        ignore_index = True)\n",
    "    .drop_duplicates())\n",
    "\n",
    "station_name_to_id_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking for observations that has the same station id as its station name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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>station_name</th>\n",
       "      <th>station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1175</th>\n",
       "      <td>Divvy Valet - Oakwood Beach</td>\n",
       "      <td>Divvy Valet - Oakwood Beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1593</th>\n",
       "      <td>410</td>\n",
       "      <td>410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2662</th>\n",
       "      <td>DIVVY CASSETTE REPAIR MOBILE STATION</td>\n",
       "      <td>DIVVY CASSETTE REPAIR MOBILE STATION</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              station_name  \\\n",
       "1175           Divvy Valet - Oakwood Beach   \n",
       "1593                                   410   \n",
       "2662  DIVVY CASSETTE REPAIR MOBILE STATION   \n",
       "\n",
       "                                station_id  \n",
       "1175           Divvy Valet - Oakwood Beach  \n",
       "1593                                   410  \n",
       "2662  DIVVY CASSETTE REPAIR MOBILE STATION  "
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_name_to_id_df.query('`station_name` == `station_id`')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Making a list of station id for these observations with the same name and id to later ignore for id to name mapping and update the observations manually"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Divvy Valet - Oakwood Beach', '410', 'DIVVY CASSETTE REPAIR MOBILE STATION']"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_name_to_id_with_same_name_id_list = list(station_name_to_id_df.query('`station_name` == `station_id`')['station_id'].values)\n",
    "station_name_to_id_with_same_name_id_list"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Makign a list of station id of station id with station name variations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['564', '553', '604', '756', '569']"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_id_with_name_variation_list = list(\n",
    "    station_name_to_id_df['station_id'].value_counts()[\n",
    "        station_name_to_id_df['station_id'].value_counts() > 1]\n",
    "    .keys())\n",
    "\n",
    "station_id_with_name_variation_list[:5]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mapping station id to the station name of the first occurence of station id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'564': 'Racine Ave & 65th St',\n",
       " '553': 'Elizabeth St & 47th St',\n",
       " '604': 'Sheridan Rd & Noyes St (NU)',\n",
       " '756': 'Albany Ave & 63rd St',\n",
       " '569': 'Public Rack - Menard Ave & Grand Ave'}"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_id_station_name_map = {}\n",
    "for station_id in station_id_with_name_variation_list:\n",
    "    if station_id not in station_name_to_id_with_same_name_id_list:\n",
    "        key = station_id\n",
    "        value = station_name_to_id_df.query(f\"`station_id` == '{key}'\")['station_name'].iloc[0]\n",
    "        station_id_station_name_map.update({key : value})\n",
    "\n",
    "# Showing the first 5 items\n",
    "dict(list(station_id_station_name_map.items())[:5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['start_station_name'] = (df[['start_station_name', 'start_station_id']]\n",
    " .apply(\n",
    "    lambda x: \n",
    "        station_id_station_name_map.get(x['start_station_id']) \n",
    "        if x['start_station_id'] in station_id_with_name_variation_list \n",
    "            and x['start_station_id'] not in station_name_to_id_with_same_name_id_list\n",
    "            and not pd.isna(x['start_station_id']) \n",
    "        else x['start_station_name'], \n",
    "    axis = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['end_station_name'] = (df[['end_station_name', 'end_station_id']]\n",
    " .apply(\n",
    "    lambda x: \n",
    "        station_id_station_name_map.get(x['end_station_id']) \n",
    "        if x['end_station_id'] in station_id_with_name_variation_list\n",
    "            and x['end_station_id'] not in station_name_to_id_with_same_name_id_list\n",
    "            and not pd.isna(x['end_station_id'])\n",
    "        else x['end_station_name'], \n",
    "    axis = 1))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the station name mapped for consistency, no station id must have greater than one observation when missing values and duplicates are dropped."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "410             2\n",
       "13084           1\n",
       "385             1\n",
       "479             1\n",
       "755             1\n",
       "               ..\n",
       "20104           1\n",
       "432             1\n",
       "383             1\n",
       "TA1307000159    1\n",
       "1260            1\n",
       "Name: start_station_id, Length: 1419, dtype: int64"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df.dropna()\n",
    " .loc[:, ['start_station_name', 'start_station_id']]\n",
    " .drop_duplicates()['start_station_id']\n",
    " .value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "410       2\n",
       "13084     1\n",
       "580       1\n",
       "13050     1\n",
       "SL-007    1\n",
       "         ..\n",
       "552       1\n",
       "512       1\n",
       "16912     1\n",
       "397       1\n",
       "934       1\n",
       "Name: end_station_id, Length: 1423, dtype: int64"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df.dropna()\n",
    " .loc[:, ['end_station_name', 'end_station_id']]\n",
    " .drop_duplicates()['end_station_id']\n",
    " .value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "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>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5010119</th>\n",
       "      <td>Campbell Ave &amp; Augusta Blvd</td>\n",
       "      <td>410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5010275</th>\n",
       "      <td>410</td>\n",
       "      <td>410</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  start_station_name start_station_id\n",
       "5010119  Campbell Ave & Augusta Blvd              410\n",
       "5010275                          410              410"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(\"`start_station_id` == '410'\").loc[:, ['start_station_name', 'start_station_id']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['start_station_name'] = (df[['start_station_name', 'start_station_id']]\n",
    " .apply(\n",
    "    lambda x: \n",
    "        'Campbell Ave & Augusta Blvd'\n",
    "        if x['start_station_id'] == '410'\n",
    "        else x['start_station_name'], \n",
    "    axis = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['end_station_name'] = (df[['end_station_name', 'end_station_id']]\n",
    " .apply(\n",
    "    lambda x: \n",
    "        'Campbell Ave & Augusta Blvd'\n",
    "        if x['end_station_id'] == '410'\n",
    "        else x['end_station_name'], \n",
    "    axis = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Divvy Valet - Oakwood Beach', 'DIVVY CASSETTE REPAIR MOBILE STATION']"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_name_to_id_with_same_name_id_list.remove('410')\n",
    "station_name_to_id_with_same_name_id_list"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the case of the station names with the same station ids, it would be hard to determine what their actual station id is so it would be better to just drop it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "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>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>775655</th>\n",
       "      <td>Divvy Valet - Oakwood Beach</td>\n",
       "      <td>Divvy Valet - Oakwood Beach</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 start_station_name             start_station_id\n",
       "775655  Divvy Valet - Oakwood Beach  Divvy Valet - Oakwood Beach"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(\"`start_station_name` == 'Divvy Valet - Oakwood Beach'\").loc[:, ['start_station_name', 'start_station_id']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[~df['start_station_id'].isin(station_name_to_id_with_same_name_id_list)]\n",
    "df = df[~df['end_station_id'].isin(station_name_to_id_with_same_name_id_list)]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Addressing missing values"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filling missing station id my matching with coordinates of existing observation with complete data of station id, its latitude, and its longitude."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "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>station_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13084</td>\n",
       "      <td>41.922695</td>\n",
       "      <td>-87.697153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13084</td>\n",
       "      <td>41.922613</td>\n",
       "      <td>-87.697148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20222</td>\n",
       "      <td>41.704628</td>\n",
       "      <td>-87.528414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>637</td>\n",
       "      <td>41.895611</td>\n",
       "      <td>-87.672098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13256</td>\n",
       "      <td>41.903029</td>\n",
       "      <td>-87.697474</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  station_id   latitude  longitude\n",
       "0      13084  41.922695 -87.697153\n",
       "1      13084  41.922613 -87.697148\n",
       "2      20222  41.704628 -87.528414\n",
       "3        637  41.895611 -87.672098\n",
       "4      13256  41.903029 -87.697474"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_id_coordinates_column_names_mapping = {\n",
    "    \"start_station_id\": \"station_id\", \"start_lat\": \"latitude\", \"start_lng\": \"longitude\",\n",
    "    \"end_station_id\": \"station_id\", \"end_lat\": \"latitude\", \"end_lng\": \"longitude\"}\n",
    "\n",
    "station_id_coordinates_df = (pd.concat([\n",
    "    (df.dropna()\n",
    "     .loc[:, ['start_station_id', 'start_lat', 'start_lng']]\n",
    "     .drop_duplicates()\n",
    "     .rename(columns = station_id_coordinates_column_names_mapping)),\n",
    "    (df.dropna()\n",
    "     .loc[:, ['end_station_id', 'end_lat', 'end_lng']]\n",
    "     .drop_duplicates()\n",
    "     .rename(columns = station_id_coordinates_column_names_mapping))],\n",
    "    ignore_index = True)\n",
    ".drop_duplicates())\n",
    "\n",
    "station_id_coordinates_df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Making a coordinates to station id map which station id can be retrieved by accessing the coordinates as key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{(41.922695, -87.697153): '13084',\n",
       " (41.9226131666667, -87.6971478333333): '13084',\n",
       " (41.7046285, -87.5284143333333): '20222',\n",
       " (41.8956108333333, -87.6720975): '637',\n",
       " (41.903029, -87.697474): '13256'}"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "coordinates_station_id_map = {}\n",
    "\n",
    "station_id_coordinates_df.apply(\n",
    "    lambda x:\n",
    "        coordinates_station_id_map.update({ (x['latitude'], x['longitude']): x['station_id'] }),\n",
    "    axis = 1, )\n",
    "\n",
    "dict(list(coordinates_station_id_map.items())[:5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_station_id_by_matching_coord(data):\n",
    "    if not pd.isna(data.iloc[0]):\n",
    "        return data.iloc[0]\n",
    "    \n",
    "    station_id = coordinates_station_id_map.get((data.iloc[1], data.iloc[2]))\n",
    "    return station_id if station_id is not None else data.iloc[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trying code before implementation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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>start_station_id</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.89</td>\n",
       "      <td>-87.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.91</td>\n",
       "      <td>-87.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.91</td>\n",
       "      <td>-87.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.80</td>\n",
       "      <td>-87.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.91</td>\n",
       "      <td>-87.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>42.03</td>\n",
       "      <td>-87.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.91</td>\n",
       "      <td>-87.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.89</td>\n",
       "      <td>-87.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.89</td>\n",
       "      <td>-87.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.89</td>\n",
       "      <td>-87.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.89</td>\n",
       "      <td>-87.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.99</td>\n",
       "      <td>-87.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.94</td>\n",
       "      <td>-87.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.94</td>\n",
       "      <td>-87.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>41.94</td>\n",
       "      <td>-87.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   start_station_id  start_lat  start_lng\n",
       "0               NaN      41.89     -87.62\n",
       "1               NaN      41.91     -87.62\n",
       "2               NaN      41.91     -87.65\n",
       "3               NaN      41.80     -87.66\n",
       "4               NaN      41.91     -87.63\n",
       "5               NaN      42.03     -87.71\n",
       "6               NaN      41.91     -87.63\n",
       "7               NaN      41.89     -87.62\n",
       "8               NaN      41.89     -87.61\n",
       "9               NaN      41.89     -87.61\n",
       "10              NaN      41.89     -87.66\n",
       "11              NaN      41.99     -87.66\n",
       "12              NaN      41.94     -87.65\n",
       "13              NaN      41.94     -87.64\n",
       "14              NaN      41.94     -87.65"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Start station ids before implementation\n",
    "df.head(15).loc[:, ['start_station_id', 'start_lat', 'start_lng']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       NaN\n",
       "1       NaN\n",
       "2       NaN\n",
       "3      1042\n",
       "4       NaN\n",
       "5       NaN\n",
       "6       NaN\n",
       "7       NaN\n",
       "8       NaN\n",
       "9       NaN\n",
       "10      NaN\n",
       "11      NaN\n",
       "12    20256\n",
       "13    20252\n",
       "14    20256\n",
       "dtype: object"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How start station ids will look like after implementation\n",
    "df.head(15).loc[:, ['start_station_id', 'start_lat', 'start_lng']].apply(get_station_id_by_matching_coord, axis = 1)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Implementation of matching coordinates and getting the station id using the station id coordinates map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['start_station_id'] = df.loc[:, ['start_station_id', 'start_lat', 'start_lng']].apply(get_station_id_by_matching_coord, axis = 1)\n",
    "df['end_station_id'] = df.loc[:, ['end_station_id', 'end_lat', 'end_lng']].apply(get_station_id_by_matching_coord, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>station_name</th>\n",
       "      <th>station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>California Ave &amp; Milwaukee Ave</td>\n",
       "      <td>13084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Burnham Greenway &amp; 105th St</td>\n",
       "      <td>20222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wood St &amp; Chicago Ave</td>\n",
       "      <td>637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>13256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Morgan Ave &amp; 14th Pl</td>\n",
       "      <td>TA1306000002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     station_name    station_id\n",
       "0  California Ave & Milwaukee Ave         13084\n",
       "1     Burnham Greenway & 105th St         20222\n",
       "2           Wood St & Chicago Ave           637\n",
       "3    California Ave & Division St         13256\n",
       "4            Morgan Ave & 14th Pl  TA1306000002"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_name_to_id_df = (\n",
    "    pd.concat([\n",
    "        df.dropna().loc[:, ['start_station_name', 'start_station_id']].drop_duplicates().rename(columns = station_name_to_id_column_names_mapping),\n",
    "        df.dropna().loc[:, ['end_station_name', 'end_station_id']].drop_duplicates().rename(columns = station_name_to_id_column_names_mapping)], \n",
    "        ignore_index = True)\n",
    "    .drop_duplicates())\n",
    "\n",
    "station_name_to_id_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'13084': 'California Ave & Milwaukee Ave',\n",
       " '20222': 'Burnham Greenway & 105th St',\n",
       " '637': 'Wood St & Chicago Ave',\n",
       " '13256': 'California Ave & Division St',\n",
       " 'TA1306000002': 'Morgan Ave & 14th Pl'}"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_id_to_station_name_map = dict(zip(\n",
    "    list(station_name_to_id_df['station_id'].values), \n",
    "    list(station_name_to_id_df['station_name'].values)))\n",
    "\n",
    "dict(list(station_id_to_station_name_map.items())[:5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['start_station_name'] = df.loc[:, ['start_station_name', 'start_station_id']].apply(\n",
    "    lambda x:\n",
    "        station_id_to_station_name_map.get( x['start_station_id'] )\n",
    "        if pd.isna(x['start_station_name']) and not pd.isna(x['start_station_id']) else\n",
    "        x['start_station_name'],\n",
    "    axis = 1, )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['end_station_name'] = df.loc[:, ['end_station_name', 'end_station_id']].apply(\n",
    "    lambda x:\n",
    "        station_id_to_station_name_map.get( x['end_station_id'] )\n",
    "        if pd.isna(x['end_station_name']) and not pd.isna(x['end_station_id']) else\n",
    "        x['end_station_name'],\n",
    "    axis = 1, )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting to csv"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dropping observations with NaN values in any variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "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>ride_id</th>\n",
       "      <th>rideable_type</th>\n",
       "      <th>started_at</th>\n",
       "      <th>ended_at</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_lng</th>\n",
       "      <th>end_lat</th>\n",
       "      <th>end_lng</th>\n",
       "      <th>member_casual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0AFDFDED87B18711</td>\n",
       "      <td>electric_bike</td>\n",
       "      <td>2022-06-30 08:02:37</td>\n",
       "      <td>2022-06-30 08:07:30</td>\n",
       "      <td>N Sheffield Ave &amp; W Wellington Ave</td>\n",
       "      <td>20256</td>\n",
       "      <td>N Sheffield Ave &amp; W Wellington Ave</td>\n",
       "      <td>20256</td>\n",
       "      <td>41.94</td>\n",
       "      <td>-87.65</td>\n",
       "      <td>41.94</td>\n",
       "      <td>-87.65</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>73824977D6BA1702</td>\n",
       "      <td>electric_bike</td>\n",
       "      <td>2022-06-30 08:12:58</td>\n",
       "      <td>2022-06-30 08:17:04</td>\n",
       "      <td>N Sheffield Ave &amp; W Wellington Ave</td>\n",
       "      <td>20256</td>\n",
       "      <td>N Sheffield Ave &amp; W Wellington Ave</td>\n",
       "      <td>20256</td>\n",
       "      <td>41.94</td>\n",
       "      <td>-87.65</td>\n",
       "      <td>41.94</td>\n",
       "      <td>-87.65</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CF3DD3F4E16263F7</td>\n",
       "      <td>electric_bike</td>\n",
       "      <td>2022-06-30 18:42:45</td>\n",
       "      <td>2022-06-30 19:04:26</td>\n",
       "      <td>Public Rack - Kimball Ave &amp; Lawrence Ave</td>\n",
       "      <td>1134</td>\n",
       "      <td>Public Rack - Kimball Ave &amp; Lawrence Ave</td>\n",
       "      <td>1134</td>\n",
       "      <td>41.97</td>\n",
       "      <td>-87.71</td>\n",
       "      <td>41.97</td>\n",
       "      <td>-87.71</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>54D832624A983D4C</td>\n",
       "      <td>electric_bike</td>\n",
       "      <td>2022-06-30 15:07:51</td>\n",
       "      <td>2022-06-30 15:12:28</td>\n",
       "      <td>Public Rack - Jensen Park</td>\n",
       "      <td>932</td>\n",
       "      <td>Troy St &amp; Grace St</td>\n",
       "      <td>430</td>\n",
       "      <td>41.96</td>\n",
       "      <td>-87.72</td>\n",
       "      <td>41.95</td>\n",
       "      <td>-87.71</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>F6D59CBDC2418088</td>\n",
       "      <td>electric_bike</td>\n",
       "      <td>2022-06-30 13:42:32</td>\n",
       "      <td>2022-06-30 14:12:23</td>\n",
       "      <td>Troy St &amp; Grace St</td>\n",
       "      <td>430</td>\n",
       "      <td>Public Rack - Jensen Park</td>\n",
       "      <td>932</td>\n",
       "      <td>41.95</td>\n",
       "      <td>-87.71</td>\n",
       "      <td>41.96</td>\n",
       "      <td>-87.72</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ride_id  rideable_type           started_at             ended_at  \\\n",
       "12  0AFDFDED87B18711  electric_bike  2022-06-30 08:02:37  2022-06-30 08:07:30   \n",
       "14  73824977D6BA1702  electric_bike  2022-06-30 08:12:58  2022-06-30 08:17:04   \n",
       "16  CF3DD3F4E16263F7  electric_bike  2022-06-30 18:42:45  2022-06-30 19:04:26   \n",
       "23  54D832624A983D4C  electric_bike  2022-06-30 15:07:51  2022-06-30 15:12:28   \n",
       "24  F6D59CBDC2418088  electric_bike  2022-06-30 13:42:32  2022-06-30 14:12:23   \n",
       "\n",
       "                          start_station_name start_station_id  \\\n",
       "12        N Sheffield Ave & W Wellington Ave            20256   \n",
       "14        N Sheffield Ave & W Wellington Ave            20256   \n",
       "16  Public Rack - Kimball Ave & Lawrence Ave             1134   \n",
       "23                 Public Rack - Jensen Park              932   \n",
       "24                        Troy St & Grace St              430   \n",
       "\n",
       "                            end_station_name end_station_id  start_lat  \\\n",
       "12        N Sheffield Ave & W Wellington Ave          20256      41.94   \n",
       "14        N Sheffield Ave & W Wellington Ave          20256      41.94   \n",
       "16  Public Rack - Kimball Ave & Lawrence Ave           1134      41.97   \n",
       "23                        Troy St & Grace St            430      41.96   \n",
       "24                 Public Rack - Jensen Park            932      41.95   \n",
       "\n",
       "    start_lng  end_lat  end_lng member_casual  \n",
       "12     -87.65    41.94   -87.65        casual  \n",
       "14     -87.65    41.94   -87.65        casual  \n",
       "16     -87.71    41.97   -87.71        casual  \n",
       "23     -87.72    41.95   -87.71        casual  \n",
       "24     -87.71    41.96   -87.72        casual  "
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4943103 entries, 12 to 5829028\n",
      "Data columns (total 13 columns):\n",
      " #   Column              Non-Null Count    Dtype  \n",
      "---  ------              --------------    -----  \n",
      " 0   ride_id             4943103 non-null  object \n",
      " 1   rideable_type       4943103 non-null  object \n",
      " 2   started_at          4943103 non-null  object \n",
      " 3   ended_at            4943103 non-null  object \n",
      " 4   start_station_name  4943103 non-null  object \n",
      " 5   start_station_id    4943103 non-null  object \n",
      " 6   end_station_name    4943103 non-null  object \n",
      " 7   end_station_id      4943103 non-null  object \n",
      " 8   start_lat           4943103 non-null  float64\n",
      " 9   start_lng           4943103 non-null  float64\n",
      " 10  end_lat             4943103 non-null  float64\n",
      " 11  end_lng             4943103 non-null  float64\n",
      " 12  member_casual       4943103 non-null  object \n",
      "dtypes: float64(4), object(9)\n",
      "memory usage: 528.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info(verbose = True, show_counts = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data/csv/divvy-tripdata-202206-202305-merged-cleaned.csv', index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "dsa-py",
   "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.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}