part1_data_prepare_process.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2 align='center'> Cyclistic: How Does a Bike-Share Navigate Speedy Success? </h2>\n",
    "\n",
    "<h4 align='center'> (Part 1: Data Cleaning & Transformation) </h4>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Import libraries and datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "bt_202004 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202004-divvy-tripdata.csv')\n",
    "bt_202005 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202005-divvy-tripdata.csv')\n",
    "bt_202006 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202006-divvy-tripdata.csv')\n",
    "bt_202007 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202007-divvy-tripdata.csv')\n",
    "bt_202008 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202008-divvy-tripdata.csv')\n",
    "bt_202009 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202009-divvy-tripdata.csv')\n",
    "bt_202010 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202010-divvy-tripdata.csv')\n",
    "bt_202011 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202011-divvy-tripdata.csv')\n",
    "bt_202012 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202012-divvy-tripdata.csv')\n",
    "bt_202101 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202101-divvy-tripdata.csv')\n",
    "bt_202102 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202102-divvy-tripdata.csv')\n",
    "bt_202103 = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/202103-divvy-tripdata.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Examine datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### By briefly examining all 12 datasets, I've noticed these datasets have been organized in the same format of 13 columns:\n",
    "\n",
    "| Column | This column measures |\n",
    "| :---: | :---: |\n",
    "| *ride_id* | unique id of each bike ride initiated |\n",
    "| *rideable_type* | 3 types of bikes used by Cycslistic |\n",
    "| *started_at* | start date and time of each bike ride |\n",
    "| *ended_at* | end date and time of each bike ride |\n",
    "| *start_station_name* | bike station name where each bike ride starts |\n",
    "| *start_station_id* | bike station id where each bike ride starts |\n",
    "| *end_station_name* | bike station name where each bike ride ends |\n",
    "| *end_station_id* | bike station id where each bike ride ends |\n",
    "| *start_lat* | latitude of bike station where each bike ride starts |\n",
    "| *start_lng* | longitude of biek station where each bike ride starts |\n",
    "| *end_lat* | latitude of bike station where each bike ride ends |\n",
    "| *end_lng* | longitude of bike station where each bike ride ends |\n",
    "| *member_casual* | whether a bike rider is a member of Cyclistic program or not |\n",
    "\n",
    "#### For each dataset, I will perform these data cleaning and transformations:\n",
    "- check the uniqueness of *ride_id*\n",
    "- drop any NULL values in all columns\n",
    "- check the data types of each column, and convert to the correct type if necessary\n",
    "- check the consistency between *start_station_name* and *start_station_id*\n",
    "- check the consistency between *end_station_name* and *end_station_id*\n",
    "- drop irrelevant columns: *start_lat*, *start_lng*, *end_lat*, *end_lng*\n",
    "\n",
    "***Note:*** \n",
    "\n",
    "For all 12 datasets, *started_at* and *ended_at* columns are strings, which they should be datetimes. However, I will leave them \"as-is\" now since my goal for this this phase of data cleaning is mainly clean out invalid data and transform data for further analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-1) dataset: bike trips of April 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202004.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                object\n",
       "rideable_type          object\n",
       "started_at             object\n",
       "ended_at               object\n",
       "start_station_name     object\n",
       "start_station_id        int64\n",
       "end_station_name       object\n",
       "end_station_id        float64\n",
       "member_casual          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202004.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "180BFC2CEBA46994    1\n",
       "8509DEDDDAA9FFBB    1\n",
       "5B87B45E922A3295    1\n",
       "689A3BF47F4D1F3D    1\n",
       "9C2E445CDC3F0793    1\n",
       "                   ..\n",
       "DB18E2F0B9FDD7BC    1\n",
       "9F8A694B13CA745B    1\n",
       "795891B9DB26AF89    1\n",
       "A4F063F6183C3B38    1\n",
       "A47C0641866DFD75    1\n",
       "Name: ride_id, Length: 84776, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202004['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                0\n",
       "rideable_type          0\n",
       "started_at             0\n",
       "ended_at               0\n",
       "start_station_name     0\n",
       "start_station_id       0\n",
       "end_station_name      99\n",
       "end_station_id        99\n",
       "member_casual          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202004.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202004.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert 'end_station_id' column from floating points to integers\n",
    "bt_202004['end_station_id'] = bt_202004['end_station_id'].astype('int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-2) dataset: bike trips of May 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202005.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                object\n",
       "rideable_type          object\n",
       "started_at             object\n",
       "ended_at               object\n",
       "start_station_name     object\n",
       "start_station_id        int64\n",
       "end_station_name       object\n",
       "end_station_id        float64\n",
       "member_casual          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202005.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9DA1D40B49BE87ED    1\n",
       "93AC49BEBB95603C    1\n",
       "029F9FB40454F748    1\n",
       "C2960CFC94029246    1\n",
       "B2263918A268ED11    1\n",
       "                   ..\n",
       "8C493B18A238FB13    1\n",
       "2AEEC9C39CC4B885    1\n",
       "4B9915803027704D    1\n",
       "5CB85E22CB30FDBC    1\n",
       "3371E3D02F2E55DC    1\n",
       "Name: ride_id, Length: 200274, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202005['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                 0\n",
       "rideable_type           0\n",
       "started_at              0\n",
       "ended_at                0\n",
       "start_station_name      0\n",
       "start_station_id        0\n",
       "end_station_name      321\n",
       "end_station_id        321\n",
       "member_casual           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202005.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202005.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert 'end_station_id' from floating points to integers\n",
    "bt_202005['end_station_id'] = bt_202005['end_station_id'].astype('int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-3) dataset: bike trips of June 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202006.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                object\n",
       "rideable_type          object\n",
       "started_at             object\n",
       "ended_at               object\n",
       "start_station_name     object\n",
       "start_station_id        int64\n",
       "end_station_name       object\n",
       "end_station_id        float64\n",
       "member_casual          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202006.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1EE2632EBD249B4B    1\n",
       "39DBCAD831F4816B    1\n",
       "95FBADB59D875758    1\n",
       "FC76A830FE106B0C    1\n",
       "F3E5D7709A2DFC61    1\n",
       "                   ..\n",
       "47A4903A5D7A4F8A    1\n",
       "23DE09463AC304A9    1\n",
       "42E81992E3CDA0E3    1\n",
       "63CA826F8129C963    1\n",
       "696615A559CD365A    1\n",
       "Name: ride_id, Length: 343005, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202006['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                 0\n",
       "rideable_type           0\n",
       "started_at              0\n",
       "ended_at                0\n",
       "start_station_name      0\n",
       "start_station_id        0\n",
       "end_station_name      468\n",
       "end_station_id        468\n",
       "member_casual           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202006.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202006.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert 'end_station_id' from floating points to integers\n",
    "bt_202006['end_station_id'] = bt_202006['end_station_id'].astype('int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-4) dataset: bike trips of July 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202007.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                object\n",
       "rideable_type          object\n",
       "started_at             object\n",
       "ended_at               object\n",
       "start_station_name     object\n",
       "start_station_id      float64\n",
       "end_station_name       object\n",
       "end_station_id        float64\n",
       "member_casual          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202007.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37F18C5D0C6ADFC9    1\n",
       "5AE0ED2811D1A181    1\n",
       "D94C8F00A434E68B    1\n",
       "552CA14FA6DD95A9    1\n",
       "48801333DE8F78FA    1\n",
       "                   ..\n",
       "51D0355A0ED747C3    1\n",
       "6765B9813FCF4FE4    1\n",
       "0FBC7E5C58B55444    1\n",
       "FFB0F68524DF45E5    1\n",
       "DBB9BDE0A2070964    1\n",
       "Name: ride_id, Length: 551480, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202007['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                 0\n",
       "rideable_type           0\n",
       "started_at              0\n",
       "ended_at                0\n",
       "start_station_name    149\n",
       "start_station_id      152\n",
       "end_station_name      967\n",
       "end_station_id        969\n",
       "member_casual           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202007.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202007.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert 'start_station_id' column from floating points to integers\n",
    "bt_202007['start_station_id'] = bt_202007['start_station_id'].astype('int64')\n",
    "\n",
    "# convert 'end_station_id' column from floating points to integers\n",
    "bt_202007['end_station_id'] = bt_202007['end_station_id'].astype('int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-5) dataset: bike trips of August 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202008.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                object\n",
       "rideable_type          object\n",
       "started_at             object\n",
       "ended_at               object\n",
       "start_station_name     object\n",
       "start_station_id      float64\n",
       "end_station_name       object\n",
       "end_station_id        float64\n",
       "member_casual          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202008.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BC10BD9EA2FF6100    1\n",
       "651B5008DF498A35    1\n",
       "A96E3EB2AA84BA4C    1\n",
       "376D892277265523    1\n",
       "DF157958D55CFF61    1\n",
       "                   ..\n",
       "7AFCAE0B95D8FC7E    1\n",
       "074817E17F039D9E    1\n",
       "EEA7454B2C642A2B    1\n",
       "B62630C9347EA24D    1\n",
       "4C331F38463DB43F    1\n",
       "Name: ride_id, Length: 622361, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202008['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                   0\n",
       "rideable_type             0\n",
       "started_at                0\n",
       "ended_at                  0\n",
       "start_station_name     7595\n",
       "start_station_id       7691\n",
       "end_station_name      10035\n",
       "end_station_id        10110\n",
       "member_casual             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202008.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202008.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert 'start_station_id' column from floating points to integers\n",
    "bt_202008['start_station_id'] = bt_202008['start_station_id'].astype('int64')\n",
    "\n",
    "# convert 'end_station_id' column from floating points to integers\n",
    "bt_202008['end_station_id'] = bt_202008['end_station_id'].astype('int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-6) dataset: bike trips of September 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202009.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                object\n",
       "rideable_type          object\n",
       "started_at             object\n",
       "ended_at               object\n",
       "start_station_name     object\n",
       "start_station_id      float64\n",
       "end_station_name       object\n",
       "end_station_id        float64\n",
       "member_casual          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202009.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "C38CFDF0A9CB024D    1\n",
       "5DB7DB39B859D7D5    1\n",
       "AAED78CB97E9FC86    1\n",
       "A66282B15D0FBC66    1\n",
       "58E30991B514B877    1\n",
       "                   ..\n",
       "C741DDFDC4E96E60    1\n",
       "5C64D660E7D3E5FC    1\n",
       "8A7A71760A24853E    1\n",
       "285CBF451A7CA7DA    1\n",
       "6299733F5451D8A2    1\n",
       "Name: ride_id, Length: 532958, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202009['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                   0\n",
       "rideable_type             0\n",
       "started_at                0\n",
       "ended_at                  0\n",
       "start_station_name    19691\n",
       "start_station_id      19901\n",
       "end_station_name      23373\n",
       "end_station_id        23524\n",
       "member_casual             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202009.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202009.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert 'start_station_id' column from floating points to integers\n",
    "bt_202009['start_station_id'] = bt_202009['start_station_id'].astype('int64')\n",
    "\n",
    "# convert 'end_station_id' column from floating points to integers\n",
    "bt_202009['end_station_id'] = bt_202009['end_station_id'].astype('int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-7) dataset: bike trips of October 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202010.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                object\n",
       "rideable_type          object\n",
       "started_at             object\n",
       "ended_at               object\n",
       "start_station_name     object\n",
       "start_station_id      float64\n",
       "end_station_name       object\n",
       "end_station_id        float64\n",
       "member_casual          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202010.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "529C47F0822DC5C0    1\n",
       "3501346A5E53B621    1\n",
       "643794E6635E95EF    1\n",
       "100FD8233A8F9952    1\n",
       "CE323640AE8819E6    1\n",
       "                   ..\n",
       "CEBFCB046378858F    1\n",
       "4F83930DBBF450FD    1\n",
       "A32229F2E45808FC    1\n",
       "571D240C7E9AB8AF    1\n",
       "FE4F6793524CF371    1\n",
       "Name: ride_id, Length: 388653, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202010['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                   0\n",
       "rideable_type             0\n",
       "started_at                0\n",
       "ended_at                  0\n",
       "start_station_name    31198\n",
       "start_station_id      31405\n",
       "end_station_name      35631\n",
       "end_station_id        35787\n",
       "member_casual             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202010.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202010.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert 'start_station_id' column from floating points to integers\n",
    "bt_202010['start_station_id'] = bt_202010['start_station_id'].astype('int64')\n",
    "\n",
    "# convert 'end_station_id' column from floating points to integers\n",
    "bt_202010['end_station_id'] = bt_202010['end_station_id'].astype('int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-8) dataset: bike trips of November 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202011.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                object\n",
       "rideable_type          object\n",
       "started_at             object\n",
       "ended_at               object\n",
       "start_station_name     object\n",
       "start_station_id      float64\n",
       "end_station_name       object\n",
       "end_station_id        float64\n",
       "member_casual          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202011.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "670D924F0A941808    1\n",
       "A6111D8F8B5EC300    1\n",
       "37ABDE4F6F8B13C3    1\n",
       "8A31E13EC06FC3EF    1\n",
       "1C81244028D1B74A    1\n",
       "                   ..\n",
       "D20ADADA52FAA92C    1\n",
       "7488FE9C5D8BEF7B    1\n",
       "6826EBF87881E5C1    1\n",
       "8BC746D2D951308F    1\n",
       "F8EA85941899FB8E    1\n",
       "Name: ride_id, Length: 259716, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202011['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                   0\n",
       "rideable_type             0\n",
       "started_at                0\n",
       "ended_at                  0\n",
       "start_station_name    24324\n",
       "start_station_id      24434\n",
       "end_station_name      26749\n",
       "end_station_id        26826\n",
       "member_casual             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202011.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202011.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert 'start_station_id' column from floating points to integers\n",
    "bt_202011['start_station_id'] = bt_202011['start_station_id'].astype('int64')\n",
    "\n",
    "# convert 'end_station_id' column from floating points to integers\n",
    "bt_202011['end_station_id'] = bt_202011['end_station_id'].astype('int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-9) dataset: bike trips of December 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202012.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id               object\n",
       "rideable_type         object\n",
       "started_at            object\n",
       "ended_at              object\n",
       "start_station_name    object\n",
       "start_station_id      object\n",
       "end_station_name      object\n",
       "end_station_id        object\n",
       "member_casual         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202012.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "B990CBAEAD194EA4    1\n",
       "7F1AD2AE92A14C0A    1\n",
       "380DC29DF49D4F85    1\n",
       "7BA60FA22FEF1AED    1\n",
       "7574D453E5146A83    1\n",
       "                   ..\n",
       "815F6FCB970E21C6    1\n",
       "DD3F5639D5E79367    1\n",
       "672A2535006E067B    1\n",
       "AC3FF8C84C46ACB4    1\n",
       "EC10274146377638    1\n",
       "Name: ride_id, Length: 131573, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202012['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                   0\n",
       "rideable_type             0\n",
       "started_at                0\n",
       "ended_at                  0\n",
       "start_station_name    11699\n",
       "start_station_id      11699\n",
       "end_station_name      13237\n",
       "end_station_id        13237\n",
       "member_casual             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202012.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202012.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:***\n",
    "\n",
    "After examining *start_station_id* and *end_station_id* in this dataset, I found an inconsistency issue between station ids and their corresponding names:\n",
    "- for example, if a start or end station's name is \"Rhodes Ave & 32nd St\", then its corresponding id is 263 in all previous nine datasets; However, in this dataset it is associated with id 13215. \n",
    "\n",
    "Therefore, I will re-validate the correct ids from cross-referecing all previous datasets, and then cast these two columns into integer datatype."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get all pairs of station names and ids from previous 8 datasets\n",
    "\n",
    "station_id = dict(zip(bt_202004['start_station_name'], bt_202004['start_station_id']))\n",
    "station_id.update(zip(bt_202004['end_station_name'], bt_202004['end_station_id']))\n",
    "\n",
    "station_id.update(zip(bt_202005['start_station_name'], bt_202005['start_station_id']))\n",
    "station_id.update(zip(bt_202005['end_station_name'], bt_202005['end_station_id']))\n",
    "\n",
    "station_id.update(zip(bt_202006['start_station_name'], bt_202006['start_station_id']))\n",
    "station_id.update(zip(bt_202006['end_station_name'], bt_202006['end_station_id']))\n",
    "\n",
    "station_id.update(zip(bt_202007['start_station_name'], bt_202007['start_station_id']))\n",
    "station_id.update(zip(bt_202007['end_station_name'], bt_202007['end_station_id']))\n",
    "\n",
    "station_id.update(zip(bt_202008['start_station_name'], bt_202008['start_station_id']))\n",
    "station_id.update(zip(bt_202008['end_station_name'], bt_202005['end_station_id']))\n",
    "\n",
    "station_id.update(zip(bt_202009['start_station_name'], bt_202009['start_station_id']))\n",
    "station_id.update(zip(bt_202009['end_station_name'], bt_202009['end_station_id']))\n",
    "\n",
    "station_id.update(zip(bt_202010['start_station_name'], bt_202010['start_station_id']))\n",
    "station_id.update(zip(bt_202010['end_station_name'], bt_202010['end_station_id']))\n",
    "\n",
    "station_id.update(zip(bt_202011['start_station_name'], bt_202011['start_station_id']))\n",
    "station_id.update(zip(bt_202011['end_station_name'], bt_202011['end_station_id']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### I will create a function 'correct_station_name_id' to grab all start station names and end station names from the input dataframe, and determine if each station's name appears in *station_id* dictionary:\n",
    "- if it does exist, update its corresponding id from the input dataframe to the correct one;\n",
    "- if it does not exist, mark the station's name in a list 'not_changed' for further cleaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "not_changed = []\n",
    "\n",
    "def correct_station_name_id(dataframe):\n",
    "    start_station_names = set(dataframe['start_station_name'])\n",
    "    end_station_names = set(dataframe['end_station_name'])\n",
    "    \n",
    "    # correct start_station_ids in the dataset\n",
    "    for name in start_station_names:\n",
    "        if name not in station_id.keys():\n",
    "            not_changed.append(name) # catch exceptions in case any station name haven't appeared in previous 8 datasets\n",
    "        else:\n",
    "            dataframe.loc[dataframe['start_station_name'] == name, 'start_station_id'] = station_id[name]\n",
    "    \n",
    "    # correct end_station_ids in the dataset\n",
    "    for name in end_station_names:\n",
    "        if name in not_changed:\n",
    "            continue\n",
    "        elif name not in station_id.keys():\n",
    "            not_changed.append(name) # catch exceptions in case any station name haven't appeared in previous 8 datasets\n",
    "        else:\n",
    "            dataframe.loc[dataframe['end_station_name'] == name, 'end_station_id'] = station_id[name]\n",
    "    \n",
    "    print('These stations below are not existed in the previous 8 datasets:')\n",
    "    return not_changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "These stations below are not existed in the previous 8 datasets:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['W Oakdale Ave & N Broadway',\n",
       " 'N Green St & W Lake St',\n",
       " 'W Armitage Ave & N Sheffield Ave',\n",
       " 'Base - 2132 W Hubbard Warehouse',\n",
       " 'N Carpenter St & W Lake St']"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# update stations' ids in bt_202012 and catch stations that're not existed from previous 8 datasets\n",
    "correct_station_name_id(bt_202012)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### As showed above, 5 station names were caught as exceptions in *not_changed*. \n",
    "\n",
    "#### Therefore, I've found the official dataset of all Divvy Bicycle Stations at <a href=\"https://data.cityofchicago.org/d/bbyy-e7gq?category=Transportation&view_name=Divvy-Bicycle-Stations\"> here </a> on <a href=\"httP://data.cityofchicago.org\"> The Chicago Data Portal website. </a></href>\n",
    "- I will download this dataset and import it below as the official reference to get the correct station names and/or corresponding ids.</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# import the official bike stations information dataset\n",
    "official_station_info = pd.read_csv('~/PROJECTS/02_cyclistic_bike_share/original_datasets/official_bike_stations_info.csv')\n",
    "\n",
    "# get the station name and id in pairs from the official station information dataset\n",
    "official_station_name_id = dict(zip(official_station_info['Station Name'], official_station_info['ID']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### I will create a function 'get_correct_id' to get the correct id of each station from *official_station_name_id* which is not existed in the previous 8 datasets:\n",
    "- if it does exist, print out the station name and its corresponding id;\n",
    "- if it does not exist, print out the station name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def get_correct_id():\n",
    "    cannot_find_this_station = []\n",
    "    for name in not_changed:\n",
    "        if name not in official_station_name_id.keys():\n",
    "            if name not in cannot_find_this_station:\n",
    "                cannot_find_this_station.append(name)\n",
    "        else:\n",
    "            correct_id = official_station_name_id[name]\n",
    "            print(name, ': ', correct_id)\n",
    "            print()\n",
    "    \n",
    "    print('These stations below cannot be found in the official stations information dataset:')\n",
    "    return cannot_find_this_station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "W Oakdale Ave & N Broadway :  1436495100903691938\n",
      "\n",
      "N Green St & W Lake St :  1436495109493626546\n",
      "\n",
      "W Armitage Ave & N Sheffield Ave :  1436495105198659242\n",
      "\n",
      "N Carpenter St & W Lake St :  1436495105198659246\n",
      "\n",
      "These stations below cannot be found in the official stations information dataset:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Base - 2132 W Hubbard Warehouse']"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the correct ids of stations in not_changed\n",
    "get_correct_id()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:*** \n",
    "\n",
    "The station \"Base - 2132 W Hubbard Warehouse\" cannot be found in the official station information dataset. Due to lack of information, I will drop these records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop records with start_station_name as 'Base - 2132 W Hubbard Warehouse'\n",
    "bt_202012.drop(bt_202012[bt_202012['start_station_name'] == 'Base - 2132 W Hubbard Warehouse'].index, \n",
    "               inplace = True)\n",
    "\n",
    "# drop records with end_station_name as 'Base - 2132 W Hubbard Warehouse'\n",
    "bt_202012.drop(bt_202012[bt_202012['end_station_name'] == 'Base - 2132 W Hubbard Warehouse'].index, \n",
    "               inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:***\n",
    "\n",
    "For the other 4 stations, I will manually update the ids in bt_202012 </h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Green St & W Lake St'\n",
    "filt_a1 = (bt_202012['start_station_name'] == 'N Green St & W Lake St')\n",
    "bt_202012.loc[filt_a1, 'start_station_id'] = 1436495109493626546\n",
    "\n",
    "filt_a2 = (bt_202012['end_station_name'] == 'N Green St & W Lake St')\n",
    "bt_202012.loc[filt_a2, 'end_station_id'] = 1436495109493626546"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Oakdale Ave & N Broadway'\n",
    "filt_b1 = (bt_202012['start_station_name'] == 'W Oakdale Ave & N Broadway')\n",
    "bt_202012.loc[filt_b1, 'start_station_id'] = 1436495100903691938\n",
    "\n",
    "filt_b2 = (bt_202012['end_station_name'] == 'W Oakdale Ave & N Broadway')\n",
    "bt_202012.loc[filt_b2, 'end_station_id'] = 1436495100903691938"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Armitage Ave & N Sheffield Ave'\n",
    "filt_c1 = (bt_202012['start_station_name'] == 'W Armitage Ave & N Sheffield Ave')\n",
    "bt_202012.loc[filt_c1, 'start_station_id'] = 1436495105198659242\n",
    "\n",
    "filt_c2 = (bt_202012['end_station_name'] == 'W Armitage Ave & N Sheffield Ave')\n",
    "bt_202012.loc[filt_c2, 'end_station_id'] = 1436495105198659242"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Carpenter St & W Lake St'\n",
    "filt_d1 = (bt_202012['start_station_name'] == 'N Carpenter St & W Lake St')\n",
    "bt_202012.loc[filt_d1, 'start_station_id'] = 1436495105198659246\n",
    "\n",
    "filt_d2 = (bt_202012['end_station_name'] == 'N Carpenter St & W Lake St')\n",
    "bt_202012.loc[filt_d2, 'end_station_id'] = 1436495105198659246"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-10) dataset: bike trips of January 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202101.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id               object\n",
       "rideable_type         object\n",
       "started_at            object\n",
       "ended_at              object\n",
       "start_station_name    object\n",
       "start_station_id      object\n",
       "end_station_name      object\n",
       "end_station_id        object\n",
       "member_casual         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202101.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5352EE4B7007BA45    1\n",
       "5506B158F04CA5BF    1\n",
       "995F861B1B2524C9    1\n",
       "E5CFFAE3A6F6CBEE    1\n",
       "FE285BBC342840BA    1\n",
       "                   ..\n",
       "14557A062DA65751    1\n",
       "625E6804CD6C756E    1\n",
       "F03EE2FB00F571DF    1\n",
       "3EB57EF1C89A26C8    1\n",
       "8A3C1330E06CE106    1\n",
       "Name: ride_id, Length: 96834, dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202101['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                   0\n",
       "rideable_type             0\n",
       "started_at                0\n",
       "ended_at                  0\n",
       "start_station_name     8625\n",
       "start_station_id       8625\n",
       "end_station_name      10277\n",
       "end_station_id        10277\n",
       "member_casual             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202101.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202101.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### After examining *start_station_id* and *end_station_id* in this dataset, I found the same inconsistency issue between station ids and their corresponding names as before. \n",
    "    \n",
    "#### Therefore, I will re-run the whole process of validating station names with corresponding ids using:\n",
    "- the official stations information dataset from Chicago Data Portal website;\n",
    "- the dictionary \"station_id\" of all stations and ids existed in the previous 8 datasets;\n",
    "- the function \"correct_station_name_id\" to either update ids or catch exceptions;\n",
    "- the function \"get_correct_id\" to grab the correct ids for exceptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "These stations below are not existed in the previous 8 datasets:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['W Oakdale Ave & N Broadway',\n",
       " 'N Paulina St & Lincoln Ave',\n",
       " 'Malcolm X College Vaccination Site',\n",
       " 'N Green St & W Lake St',\n",
       " 'Broadway & Wilson - Truman College Vaccination Site',\n",
       " 'W Armitage Ave & N Sheffield Ave',\n",
       " 'N Southport Ave & W Newport Ave',\n",
       " 'N Sheffield Ave & W Wellington Ave',\n",
       " 'Base - 2132 W Hubbard Warehouse',\n",
       " 'Avenue L & 114th St',\n",
       " 'N Carpenter St & W Lake St',\n",
       " 'N Damen Ave & W Wabansia St',\n",
       " 'Western & 28th - Velasquez Institute Vaccination Site',\n",
       " 'W Washington Blvd & N Peoria St']"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reset the not_changed list to empty\n",
    "not_changed = []\n",
    "\n",
    "# update stations' ids in bt_202101 or catch stations that're not existed from previous 8 datasets\n",
    "correct_station_name_id(bt_202101)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "W Oakdale Ave & N Broadway :  1436495100903691938\n",
      "\n",
      "N Paulina St & Lincoln Ave :  1436495122378528446\n",
      "\n",
      "Malcolm X College Vaccination Site :  631\n",
      "\n",
      "N Green St & W Lake St :  1436495109493626546\n",
      "\n",
      "Broadway & Wilson - Truman College Vaccination Site :  293\n",
      "\n",
      "W Armitage Ave & N Sheffield Ave :  1436495105198659242\n",
      "\n",
      "N Southport Ave & W Newport Ave :  1436495115557663136\n",
      "\n",
      "N Sheffield Ave & W Wellington Ave :  1436495118083561146\n",
      "\n",
      "Avenue L & 114th St :  1448642175142467184\n",
      "\n",
      "N Carpenter St & W Lake St :  1436495105198659246\n",
      "\n",
      "Western & 28th - Velasquez Institute Vaccination Site :  446\n",
      "\n",
      "W Washington Blvd & N Peoria St :  1436495109493626544\n",
      "\n",
      "These stations below cannot be found in the official stations information dataset:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Base - 2132 W Hubbard Warehouse', 'N Damen Ave & W Wabansia St']"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the correct ids of exceptional stations in list not_changed\n",
    "get_correct_id()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:***\n",
    "\n",
    "The stations 'Base - 2132 W Hubbard Warehouse' and 'N Damen Ave & W Wabansia St' cannot be found in the official station information dataset. Due to lack of information, I will drop these records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop records with start_station_name as 'Base - 2132 W Hubbard Warehouse'\n",
    "bt_202101.drop(bt_202101[bt_202101['start_station_name'] == 'Base - 2132 W Hubbard Warehouse'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with end_station_name as 'Base - 2132 W Hubbard Warehouse'\n",
    "bt_202101.drop(bt_202101[bt_202101['end_station_name'] == 'Base - 2132 W Hubbard Warehouse'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with start_station_name as 'N Damen Ave & W Wabansia St'\n",
    "bt_202101.drop(bt_202101[bt_202101['start_station_name'] == 'N Damen Ave & W Wabansia St'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with end_station_name as 'N Damen Ave & W Wabansia St'\n",
    "bt_202101.drop(bt_202101[bt_202101['end_station_name'] == 'N Damen Ave & W Wabansia St'].index, \n",
    "               inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:***\n",
    "\n",
    "For the other 12 stations, I will manually update the ids in bt_202101 </h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Malcolm X College Vaccination Site'\n",
    "filt_a1 = (bt_202101['start_station_name'] == 'Malcolm X College Vaccination Site')\n",
    "bt_202101.loc[filt_a1, 'start_station_id'] = 631\n",
    "\n",
    "filt_a2 = (bt_202101['end_station_name'] == 'Malcolm X College Vaccination Site')\n",
    "bt_202101.loc[filt_a2, 'end_station_id'] = 631"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Southport Ave & W Newport Ave'\n",
    "filt_b1 = (bt_202101['start_station_name'] == 'N Southport Ave & W Newport Ave')\n",
    "bt_202101.loc[filt_b1, 'start_station_id'] = 1436495115557663136\n",
    "\n",
    "filt_b2 = (bt_202101['end_station_name'] == 'N Southport Ave & W Newport Ave')\n",
    "bt_202101.loc[filt_b2, 'end_station_id'] = 1436495115557663136"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Sheffield Ave & W Wellington Ave'\n",
    "filt_c1 = (bt_202101['start_station_name'] == 'N Sheffield Ave & W Wellington Ave')\n",
    "bt_202101.loc[filt_c1, 'start_station_id'] = 1436495118083561146\n",
    "\n",
    "filt_c2 = (bt_202101['end_station_name'] == 'N Sheffield Ave & W Wellington Ave')\n",
    "bt_202101.loc[filt_c2, 'end_station_id'] = 1436495115557663136"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Paulina St & Lincoln Ave'\n",
    "filt_d1 = (bt_202101['start_station_name'] == 'N Paulina St & Lincoln Ave')\n",
    "bt_202101.loc[filt_d1, 'start_station_id'] = 1436495122378528446"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Green St & W Lake St'\n",
    "filt_e1 = (bt_202101['start_station_name'] == 'N Green St & W Lake St')\n",
    "bt_202101.loc[filt_e1, 'start_station_id'] = 1436495109493626546\n",
    "\n",
    "filt_e2 = (bt_202101['end_station_name'] == 'N Green St & W Lake St')\n",
    "bt_202101.loc[filt_e2, 'end_station_id'] = 1436495109493626546"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Oakdale Ave & N Broadway'\n",
    "filt_f1 = (bt_202101['start_station_name'] == 'W Oakdale Ave & N Broadway')\n",
    "bt_202101.loc[filt_f1, 'start_station_id'] = 1436495100903691938\n",
    "\n",
    "filt_f2 = (bt_202101['end_station_name'] == 'W Oakdale Ave & N Broadway')\n",
    "bt_202101.loc[filt_f2, 'end_station_id'] = 1436495100903691938"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Armitage Ave & N Sheffield Ave'\n",
    "filt_g1 = (bt_202101['start_station_name'] == 'W Armitage Ave & N Sheffield Ave')\n",
    "bt_202101.loc[filt_g1, 'start_station_id'] = 1436495105198659242\n",
    "\n",
    "filt_g2 = (bt_202101['end_station_name'] == 'W Armitage Ave & N Sheffield Ave')\n",
    "bt_202101.loc[filt_g2, 'end_station_id'] = 1436495105198659242"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Broadway & Wilson - Truman College Vaccination Site'\n",
    "filt_h1 = (bt_202101['start_station_name'] == 'Broadway & Wilson - Truman College Vaccination Site')\n",
    "bt_202101.loc[filt_h1, 'start_station_id'] = 293\n",
    "\n",
    "filt_h2 = (bt_202101['end_station_name'] == 'Broadway & Wilson - Truman College Vaccination Site')\n",
    "bt_202101.loc[filt_h2, 'end_station_id'] = 293"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Carpenter St & W Lake St'\n",
    "filt_i1 = (bt_202101['end_station_name'] == 'N Carpenter St & W Lake St')\n",
    "bt_202101.loc[filt_i1, 'end_station_id'] = 1436495105198659246"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Western & 28th - Velasquez Institute Vaccination Site'\n",
    "filt_j1 = (bt_202101['end_station_name'] == 'Western & 28th - Velasquez Institute Vaccination Site')\n",
    "bt_202101.loc[filt_j1, 'end_station_id'] = 446"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Washington Blvd & N Peoria St'\n",
    "filt_k1 = (bt_202101['end_station_name'] == 'W Washington Blvd & N Peoria St')\n",
    "bt_202101.loc[filt_k1, 'end_station_id'] = 1436495109493626544"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Avenue L & 114th St'\n",
    "filt_l1 = (bt_202101['end_station_name'] == 'Avenue L & 114th St')\n",
    "bt_202101.loc[filt_l1, 'end_station_id'] = 1448642175142467184"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-11) dataset: bike trips of Feburary 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202102.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id               object\n",
       "rideable_type         object\n",
       "started_at            object\n",
       "ended_at              object\n",
       "start_station_name    object\n",
       "start_station_id      object\n",
       "end_station_name      object\n",
       "end_station_id        object\n",
       "member_casual         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202102.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BEDCDEC418EE3E9E    1\n",
       "7FD24B6A74774BD1    1\n",
       "952F32C68DA31DB7    1\n",
       "E1857BC43C61E582    1\n",
       "DE15856A79C7D4FE    1\n",
       "                   ..\n",
       "DAC95B1BB797CF7E    1\n",
       "AD34B8ACBF76B13D    1\n",
       "AC121EBEC03B02C4    1\n",
       "9832A441D41D835D    1\n",
       "5F5FC1F78D682453    1\n",
       "Name: ride_id, Length: 49622, dtype: int64"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202102['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                  0\n",
       "rideable_type            0\n",
       "started_at               0\n",
       "ended_at                 0\n",
       "start_station_name    4046\n",
       "start_station_id      4046\n",
       "end_station_name      5358\n",
       "end_station_id        5358\n",
       "member_casual            0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202102.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202102.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### After examining *start_station_id* and *end_station_id* in this dataset, I found the same inconsistency issue between station ids and their corresponding names as before. \n",
    "    \n",
    "#### Therefore, I will re-run the whole process of validating station names with corresponding ids using:\n",
    "- the official stations information dataset from Chicago Data Portal website;\n",
    "- the dictionary \"station_id\" of all stations and ids existed in the previous 8 datasets;\n",
    "- the function \"correct_station_name_id\" to either update ids or catch exceptions;\n",
    "- the function \"get_correct_id\" to grab the correct ids for exceptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "These stations below are not existed in the previous 8 datasets:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['W Oakdale Ave & N Broadway',\n",
       " 'N Paulina St & Lincoln Ave',\n",
       " 'Malcolm X College Vaccination Site',\n",
       " 'Broadway & Wilson - Truman College Vaccination Site',\n",
       " 'W Armitage Ave & N Sheffield Ave',\n",
       " 'W Washington Blvd & N Peoria St',\n",
       " 'Base - 2132 W Hubbard Warehouse',\n",
       " 'N Hampden Ct & W Diversey Ave',\n",
       " 'N Carpenter St & W Lake St',\n",
       " 'N Green St & W Lake St',\n",
       " 'Western & 28th - Velasquez Institute Vaccination Site',\n",
       " 'N Sheffield Ave & W Wellington Ave']"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reset the not_changed list to empty\n",
    "not_changed = []\n",
    "\n",
    "# update stations' ids in bt_202102 or catch stations that're not existed from previous 8 datasets\n",
    "correct_station_name_id(bt_202102)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "W Oakdale Ave & N Broadway :  1436495100903691938\n",
      "\n",
      "N Paulina St & Lincoln Ave :  1436495122378528446\n",
      "\n",
      "Malcolm X College Vaccination Site :  631\n",
      "\n",
      "Broadway & Wilson - Truman College Vaccination Site :  293\n",
      "\n",
      "W Armitage Ave & N Sheffield Ave :  1436495105198659242\n",
      "\n",
      "W Washington Blvd & N Peoria St :  1436495109493626544\n",
      "\n",
      "N Carpenter St & W Lake St :  1436495105198659246\n",
      "\n",
      "N Green St & W Lake St :  1436495109493626546\n",
      "\n",
      "Western & 28th - Velasquez Institute Vaccination Site :  446\n",
      "\n",
      "N Sheffield Ave & W Wellington Ave :  1436495118083561146\n",
      "\n",
      "These stations below cannot be found in the official stations information dataset:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Base - 2132 W Hubbard Warehouse', 'N Hampden Ct & W Diversey Ave']"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the correct ids of exceptional stations in list not_changed\n",
    "get_correct_id()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:***\n",
    "\n",
    "The stations 'Base - 2132 W Hubbard Warehouse' and 'N Hampden Ct & W Diversey Ave' cannot be found in the official station information dataset. Due to lack of information, I will drop these records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop records with start_station_name as 'Base - 2132 W Hubbard Warehouse'\n",
    "bt_202102.drop(bt_202102[bt_202102['start_station_name'] == 'Base - 2132 W Hubbard Warehouse'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with end_station_name as 'Base - 2132 W Hubbard Warehouse'\n",
    "bt_202102.drop(bt_202102[bt_202102['end_station_name'] == 'Base - 2132 W Hubbard Warehouse'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with start_station_name as 'N Hampden Ct & W Diversey Ave'\n",
    "bt_202102.drop(bt_202102[bt_202102['start_station_name'] == 'N Hampden Ct & W Diversey Ave'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with end_station_name as 'N Hampden Ct & W Diversey Ave'\n",
    "bt_202102.drop(bt_202102[bt_202102['end_station_name'] == 'N Hampden Ct & W Diversey Ave'].index, \n",
    "               inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:***\n",
    "\n",
    "For the other 10 stations, I will manually update the ids in bt_202102"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Malcolm X College Vaccination Site'\n",
    "filt_a1 = (bt_202102['start_station_name'] == 'Malcolm X College Vaccination Site')\n",
    "bt_202102.loc[filt_a1, 'start_station_id'] = 631\n",
    "\n",
    "filt_a2 = (bt_202102['end_station_name'] == 'Malcolm X College Vaccination Site')\n",
    "bt_202102.loc[filt_a2, 'end_station_id'] = 631"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Paulina St & Lincoln Ave'\n",
    "filt_b1 = (bt_202102['start_station_name'] == 'N Paulina St & Lincoln Ave')\n",
    "bt_202102.loc[filt_b1, 'start_station_id'] = 1436495122378528446\n",
    "\n",
    "filt_b2 = (bt_202102['end_station_name'] == 'N Paulina St & Lincoln Ave')\n",
    "bt_202102.loc[filt_b2, 'end_station_id'] = 1436495122378528446"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Oakdale Ave & N Broadway'\n",
    "filt_c1 = (bt_202102['start_station_name'] == 'W Oakdale Ave & N Broadway')\n",
    "bt_202102.loc[filt_c1, 'start_station_id'] = 1436495100903691938\n",
    "\n",
    "filt_c2 = (bt_202102['end_station_name'] == 'W Oakdale Ave & N Broadway')\n",
    "bt_202102.loc[filt_c2, 'end_station_id'] = 1436495100903691938"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Washington Blvd & N Peoria St'\n",
    "filt_d1 = (bt_202102['start_station_name'] == 'W Washington Blvd & N Peoria St')\n",
    "bt_202102.loc[filt_d1, 'start_station_id'] = 1436495109493626544\n",
    "\n",
    "filt_d2 = (bt_202102['end_station_name'] == 'W Washington Blvd & N Peoria St')\n",
    "bt_202102.loc[filt_d2, 'end_station_id'] = 1436495109493626544"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Armitage Ave & N Sheffield Ave'\n",
    "filt_e1 = (bt_202102['start_station_name'] == 'W Armitage Ave & N Sheffield Ave')\n",
    "bt_202102.loc[filt_e1, 'start_station_id'] = 1436495105198659242\n",
    "\n",
    "filt_e2 = (bt_202102['end_station_name'] == 'W Armitage Ave & N Sheffield Ave')\n",
    "bt_202102.loc[filt_e2, 'end_station_id'] = 1436495105198659242"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Broadway & Wilson - Truman College Vaccination Site'\n",
    "filt_f1 = (bt_202102['start_station_name'] == 'Broadway & Wilson - Truman College Vaccination Site')\n",
    "bt_202102.loc[filt_f1, 'start_station_id'] = 293\n",
    "\n",
    "filt_f2 = (bt_202102['end_station_name'] == 'Broadway & Wilson - Truman College Vaccination Site')\n",
    "bt_202102.loc[filt_f2, 'end_station_id'] = 293"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Carpenter St & W Lake St'\n",
    "filt_g1 = (bt_202102['end_station_name'] == 'N Carpenter St & W Lake St')\n",
    "bt_202102.loc[filt_g1, 'end_station_id'] = 1436495105198659246"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Western & 28th - Velasquez Institute Vaccination Site'\n",
    "filt_h1 = (bt_202102['end_station_name'] == 'Western & 28th - Velasquez Institute Vaccination Site')\n",
    "bt_202102.loc[filt_h1, 'end_station_id'] = 446"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Sheffield Ave & W Wellington Ave'\n",
    "filt_i1 = (bt_202102['end_station_name'] == 'N Sheffield Ave & W Wellington Ave')\n",
    "bt_202102.loc[filt_i1, 'end_station_id'] = 1436495118083561146"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Green St & W Lake St'\n",
    "filt_j1 = (bt_202102['end_station_name'] == 'N Green St & W Lake St')\n",
    "bt_202102.loc[filt_j1, 'end_station_id'] = 1436495109493626546"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (2-12) dataset: bike trips of March 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop irrelevant columns: start_lat, start_lng, end_lat, end_lng\n",
    "bt_202103.drop(columns = ['start_lat', 'start_lng', 'end_lat', 'end_lng'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id               object\n",
       "rideable_type         object\n",
       "started_at            object\n",
       "ended_at              object\n",
       "start_station_name    object\n",
       "start_station_id      object\n",
       "end_station_name      object\n",
       "end_station_id        object\n",
       "member_casual         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bt_202103.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "76A1C5D305B63FFF    1\n",
       "6373E7C6ADDA8648    1\n",
       "E776E0D82830FA93    1\n",
       "6BF9A57E48F85219    1\n",
       "171F7CB6F55A87A6    1\n",
       "                   ..\n",
       "00B2D81DD205863F    1\n",
       "153DBD11DCF784CD    1\n",
       "05E268EA182D34E7    1\n",
       "1FE7C0924FC356E8    1\n",
       "7595BEA1DBA8B969    1\n",
       "Name: ride_id, Length: 228496, dtype: int64"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check uniqueness of ride_id\n",
    "bt_202103['ride_id'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                   0\n",
       "rideable_type             0\n",
       "started_at                0\n",
       "ended_at                  0\n",
       "start_station_name    14848\n",
       "start_station_id      14848\n",
       "end_station_name      16727\n",
       "end_station_id        16727\n",
       "member_casual             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NAN values\n",
    "bt_202103.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all NAN values\n",
    "bt_202103.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### After examining *start_station_id* and *end_station_id* in this dataset, I found the same inconsistency issue between station ids and their corresponding names as before. \n",
    "    \n",
    "#### Therefore, I will re-run the whole process of validating station names with corresponding ids using:\n",
    "- the official stations information dataset from Chicago Data Portal website;\n",
    "- the dictionary \"station_id\" of all stations and ids existed in the previous 8 datasets;\n",
    "- the function \"correct_station_name_id\" to either update ids or catch exceptions;\n",
    "- the function \"get_correct_id\" to grab the correct ids for exceptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "These stations below are not existed in the previous 8 datasets:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['W Oakdale Ave & N Broadway',\n",
       " 'N Hampden Ct & W Diversey Ave',\n",
       " 'N Paulina St & Lincoln Ave',\n",
       " 'Halsted & 63rd - Kennedy-King Vaccination Site',\n",
       " 'Kedzie Ave & 110th St',\n",
       " 'Chicago State University',\n",
       " 'N Carpenter St & W Lake St',\n",
       " 'Malcolm X College Vaccination Site',\n",
       " 'N Green St & W Lake St',\n",
       " 'Broadway & Wilson - Truman College Vaccination Site',\n",
       " 'W Armitage Ave & N Sheffield Ave',\n",
       " 'Damen Ave & Wabansia Ave',\n",
       " 'Western & 28th - Velasquez Institute Vaccination Site',\n",
       " 'N Southport Ave & W Newport Ave',\n",
       " 'W Washington Blvd & N Peoria St',\n",
       " 'N Sheffield Ave & W Wellington Ave',\n",
       " 'Base - 2132 W Hubbard Warehouse',\n",
       " 'N Damen Ave & W Wabansia St']"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reset the not_changed list to empty\n",
    "not_changed = []\n",
    "\n",
    "# update stations' ids in bt_202103 or catch stations that're not existed from previous 8 datasets\n",
    "correct_station_name_id(bt_202103)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "W Oakdale Ave & N Broadway :  1436495100903691938\n",
      "\n",
      "N Paulina St & Lincoln Ave :  1436495122378528446\n",
      "\n",
      "Halsted & 63rd - Kennedy-King Vaccination Site :  388\n",
      "\n",
      "Kedzie Ave & 110th St :  736\n",
      "\n",
      "Chicago State University :  737\n",
      "\n",
      "N Carpenter St & W Lake St :  1436495105198659246\n",
      "\n",
      "Malcolm X College Vaccination Site :  631\n",
      "\n",
      "N Green St & W Lake St :  1436495109493626546\n",
      "\n",
      "Broadway & Wilson - Truman College Vaccination Site :  293\n",
      "\n",
      "W Armitage Ave & N Sheffield Ave :  1436495105198659242\n",
      "\n",
      "Damen Ave & Wabansia Ave :  1521686986436309688\n",
      "\n",
      "Western & 28th - Velasquez Institute Vaccination Site :  446\n",
      "\n",
      "N Southport Ave & W Newport Ave :  1436495115557663136\n",
      "\n",
      "W Washington Blvd & N Peoria St :  1436495109493626544\n",
      "\n",
      "N Sheffield Ave & W Wellington Ave :  1436495118083561146\n",
      "\n",
      "These stations below cannot be found in the official stations information dataset:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['N Hampden Ct & W Diversey Ave',\n",
       " 'Base - 2132 W Hubbard Warehouse',\n",
       " 'N Damen Ave & W Wabansia St']"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the correct ids of exceptional stations in list not_changed\n",
    "get_correct_id()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:***\n",
    "\n",
    "The stations 'Base - 2132 W Hubbard Warehouse', 'N Hampden Ct & W Diversey Ave', and 'N Damen Ave & W Wabansia St' cannot be found in the official station information dataset. Due to lack of information, I will drop these records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop records with start_station_name as 'Base - 2132 W Hubbard Warehouse'\n",
    "bt_202103.drop(bt_202103[bt_202103['start_station_name'] == 'Base - 2132 W Hubbard Warehouse'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with end_station_name as 'Base - 2132 W Hubbard Warehouse'\n",
    "bt_202103.drop(bt_202103[bt_202103['end_station_name'] == 'Base - 2132 W Hubbard Warehouse'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with start_station_name as 'N Hampden Ct & W Diversey Ave'\n",
    "bt_202103.drop(bt_202103[bt_202103['start_station_name'] == 'N Hampden Ct & W Diversey Ave'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with end_station_name as 'N Hampden Ct & W Diversey Ave'\n",
    "bt_202103.drop(bt_202103[bt_202103['end_station_name'] == 'N Hampden Ct & W Diversey Ave'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with start_station_name as 'N Damen Ave & W Wabansia St'\n",
    "bt_202103.drop(bt_202103[bt_202103['start_station_name'] == 'N Damen Ave & W Wabansia St'].index, \n",
    "               inplace=True)\n",
    "\n",
    "# drop records with end_station_name as 'N Damen Ave & W Wabansia St'\n",
    "bt_202103.drop(bt_202103[bt_202103['end_station_name'] == 'N Damen Ave & W Wabansia St'].index, \n",
    "               inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Note:***\n",
    "\n",
    "For the other 15 stations, I will manually update the ids in bt_202102"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Broadway & Wilson - Truman College Vaccination Site'\n",
    "filt_a1 = (bt_202103['start_station_name'] == 'Broadway & Wilson - Truman College Vaccination Site')\n",
    "bt_202103.loc[filt_a1, 'start_station_id'] = 293\n",
    "\n",
    "filt_a2 = (bt_202103['end_station_name'] == 'Broadway & Wilson - Truman College Vaccination Site')\n",
    "bt_202103.loc[filt_a2, 'end_station_id'] = 293"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Damen Ave & Wabansia Ave'\n",
    "filt_b1 = (bt_202103['start_station_name'] == 'Damen Ave & Wabansia Ave')\n",
    "bt_202103.loc[filt_b1, 'start_station_id'] = 1521686986436309688\n",
    "\n",
    "filt_b2 = (bt_202103['end_station_name'] == 'Damen Ave & Wabansia Ave')\n",
    "bt_202103.loc[filt_b2, 'end_station_id'] = 1521686986436309688"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Western & 28th - Velasquez Institute Vaccination Site'\n",
    "filt_c1 = (bt_202103['start_station_name'] == 'Western & 28th - Velasquez Institute Vaccination Site')\n",
    "bt_202103.loc[filt_c1, 'start_station_id'] = 446\n",
    "\n",
    "filt_c2 = (bt_202103['end_station_name'] == 'Western & 28th - Velasquez Institute Vaccination Site')\n",
    "bt_202103.loc[filt_c2, 'end_station_id'] = 446"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Chicago State University'\n",
    "filt_d1 = (bt_202103['start_station_name'] == 'Chicago State University')\n",
    "bt_202103.loc[filt_d1, 'start_station_id'] = 737\n",
    "\n",
    "filt_d2 = (bt_202103['end_station_name'] == 'Chicago State University')\n",
    "bt_202103.loc[filt_d2, 'end_station_id'] = 737"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Armitage Ave & N Sheffield Ave'\n",
    "filt_e1 = (bt_202103['start_station_name'] == 'W Armitage Ave & N Sheffield Ave')\n",
    "bt_202103.loc[filt_e1, 'start_station_id'] = 1436495105198659242\n",
    "\n",
    "filt_e2 = (bt_202103['end_station_name'] == 'W Armitage Ave & N Sheffield Ave')\n",
    "bt_202103.loc[filt_e2, 'end_station_id'] = 1436495105198659242"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Washington Blvd & N Peoria St'\n",
    "filt_f1 = (bt_202103['start_station_name'] == 'W Washington Blvd & N Peoria St')\n",
    "bt_202103.loc[filt_f1, 'start_station_id'] = 1436495109493626544\n",
    "\n",
    "filt_f2 = (bt_202103['end_station_name'] == 'W Washington Blvd & N Peoria St')\n",
    "bt_202103.loc[filt_f2, 'end_station_id'] = 1436495109493626544"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Paulina St & Lincoln Ave'\n",
    "filt_g1 = (bt_202103['start_station_name'] == 'N Paulina St & Lincoln Ave')\n",
    "bt_202103.loc[filt_g1, 'start_station_id'] = 1436495122378528446\n",
    "              \n",
    "filt_g2 = (bt_202103['end_station_name'] == 'N Paulina St & Lincoln Ave')\n",
    "bt_202103.loc[filt_g2, 'end_station_id'] = 1436495122378528446"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Halsted & 63rd - Kennedy-King Vaccination Site'\n",
    "filt_h1 = (bt_202103['start_station_name'] == 'Halsted & 63rd - Kennedy-King Vaccination Site')\n",
    "bt_202103.loc[filt_h1, 'start_station_id'] = 388\n",
    "\n",
    "filt_h2 = (bt_202103['end_station_name'] == 'Halsted & 63rd - Kennedy-King Vaccination Site')\n",
    "bt_202103.loc[filt_h2, 'end_station_id'] = 388"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Kedzie Ave & 110th St'\n",
    "filt_i1 = (bt_202103['start_station_name'] == 'Kedzie Ave & 110th St')\n",
    "bt_202103.loc[filt_i1, 'start_station_id'] = 736\n",
    "\n",
    "filt_i2 = (bt_202103['end_station_name'] == 'Kedzie Ave & 110th St')\n",
    "bt_202103.loc[filt_i2, 'end_station_id'] = 736"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Carpenter St & W Lake St'\n",
    "filt_j1 = (bt_202103['start_station_name'] == 'N Carpenter St & W Lake St')\n",
    "bt_202103.loc[filt_j1, 'start_station_id'] = 1436495105198659246\n",
    "\n",
    "filt_j2 = (bt_202103['end_station_name'] == 'N Carpenter St & W Lake St')\n",
    "bt_202103.loc[filt_j2, 'end_station_id'] = 1436495105198659246"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'Malcolm X College Vaccination Site'\n",
    "filt_k1 = (bt_202103['start_station_name'] == 'Malcolm X College Vaccination Site')\n",
    "bt_202103.loc[filt_k1, 'start_station_id'] = 631\n",
    "\n",
    "filt_k2 = (bt_202103['end_station_name'] == 'Malcolm X College Vaccination Site')\n",
    "bt_202103.loc[filt_k2, 'end_station_id'] = 631"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Sheffield Ave & W Wellington Ave'\n",
    "filt_l1 = (bt_202103['start_station_name'] == 'N Sheffield Ave & W Wellington Ave')\n",
    "bt_202103.loc[filt_l1, 'start_station_id'] = 1436495118083561146\n",
    "\n",
    "filt_l2 = (bt_202103['end_station_name'] == 'N Sheffield Ave & W Wellington Ave')\n",
    "bt_202103.loc[filt_l2, 'end_station_id'] = 1436495118083561146"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'W Oakdale Ave & N Broadway'\n",
    "filt_m1 = (bt_202103['start_station_name'] == 'W Oakdale Ave & N Broadway')\n",
    "bt_202103.loc[filt_m1, 'start_station_id'] = 1436495100903691938\n",
    "\n",
    "filt_m2 = (bt_202103['end_station_name'] == 'W Oakdale Ave & N Broadway')\n",
    "bt_202103.loc[filt_m2, 'end_station_id'] = 1436495100903691938"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Southport Ave & W Newport Ave'\n",
    "filt_n1 = (bt_202103['start_station_name'] == 'N Southport Ave & W Newport Ave')\n",
    "bt_202103.loc[filt_n1, 'start_station_id'] = 1436495115557663136\n",
    "\n",
    "filt_n2 = (bt_202103['end_station_name'] == 'N Southport Ave & W Newport Ave')\n",
    "bt_202103.loc[filt_n2, 'end_station_id'] = 1436495115557663136"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update station's id with name of 'N Green St & W Lake St'\n",
    "filt_o1 = (bt_202103['start_station_name'] == 'N Green St & W Lake St')\n",
    "bt_202103.loc[filt_o1, 'start_station_id'] = 1436495109493626546\n",
    "\n",
    "filt_o2 = (bt_202103['end_station_name'] == 'N Green St & W Lake St')\n",
    "bt_202103.loc[filt_o2, 'end_station_id'] = 1436495109493626546"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Save cleaned datasets for next phase of data analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "bt_202004.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202004.csv', index = False)\n",
    "bt_202005.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202005.csv', index = False)\n",
    "bt_202006.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202006.csv', index = False)\n",
    "bt_202007.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202007.csv', index = False)\n",
    "bt_202008.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202008.csv', index = False)\n",
    "bt_202009.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202009.csv', index = False)\n",
    "bt_202010.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202010.csv', index = False)\n",
    "bt_202011.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202011.csv', index = False)\n",
    "bt_202012.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202012.csv', index = False)\n",
    "bt_202101.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202101.csv', index = False)\n",
    "bt_202102.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202102.csv', index = False)\n",
    "bt_202103.to_csv('~/PROJECTS/02_cyclistic_bike_share/cleaned_datasets/bt_202103.csv', index = False)"
   ]
  },
  {
   "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}