preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import the necessary libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.options.mode.chained_assignment = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataset 1: Divvy Bike Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the various csv files from Nov 2019 to Oct 2020 (includes Oct 2019 as well)\n",
    "d1 = pd.read_csv(\"divvy/Divvy_Trips_2019_Q4.csv\")\n",
    "d2 = pd.read_csv(\"divvy/divvydata_Jan-Mar2020.csv\")\n",
    "d3 = pd.read_csv(\"divvy/202004-divvy-tripdata.csv\")\n",
    "d4 = pd.read_csv(\"divvy/202005-divvy-tripdata.csv\")\n",
    "d5 = pd.read_csv(\"divvy/202006-divvy-tripdata.csv\")\n",
    "d6 = pd.read_csv(\"divvy/202007-divvy-tripdata.csv\")\n",
    "d7 = pd.read_csv(\"divvy/202008-divvy-tripdata.csv\")\n",
    "d8 = pd.read_csv(\"divvy/202009-divvy-tripdata.csv\")\n",
    "d9 = pd.read_csv(\"divvy/202010-divvy-tripdata.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>started_at</th>\n",
       "      <th>ended_at</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>member_casual</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthyear</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25223640</td>\n",
       "      <td>2019-10-01 00:01:39</td>\n",
       "      <td>2019-10-01 00:17:20</td>\n",
       "      <td>2215</td>\n",
       "      <td>940.0</td>\n",
       "      <td>20</td>\n",
       "      <td>Sheffield Ave &amp; Kingsbury St</td>\n",
       "      <td>309</td>\n",
       "      <td>Leavitt St &amp; Armitage Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1987.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25223641</td>\n",
       "      <td>2019-10-01 00:02:16</td>\n",
       "      <td>2019-10-01 00:06:34</td>\n",
       "      <td>6328</td>\n",
       "      <td>258.0</td>\n",
       "      <td>19</td>\n",
       "      <td>Throop (Loomis) St &amp; Taylor St</td>\n",
       "      <td>241</td>\n",
       "      <td>Morgan St &amp; Polk St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1998.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25223642</td>\n",
       "      <td>2019-10-01 00:04:32</td>\n",
       "      <td>2019-10-01 00:18:43</td>\n",
       "      <td>3003</td>\n",
       "      <td>850.0</td>\n",
       "      <td>84</td>\n",
       "      <td>Milwaukee Ave &amp; Grand Ave</td>\n",
       "      <td>199</td>\n",
       "      <td>Wabash Ave &amp; Grand Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Female</td>\n",
       "      <td>1991.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25223643</td>\n",
       "      <td>2019-10-01 00:04:32</td>\n",
       "      <td>2019-10-01 00:43:43</td>\n",
       "      <td>3275</td>\n",
       "      <td>2,350.0</td>\n",
       "      <td>313</td>\n",
       "      <td>Lakeview Ave &amp; Fullerton Pkwy</td>\n",
       "      <td>290</td>\n",
       "      <td>Kedzie Ave &amp; Palmer Ct</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1990.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25223644</td>\n",
       "      <td>2019-10-01 00:04:34</td>\n",
       "      <td>2019-10-01 00:35:42</td>\n",
       "      <td>5294</td>\n",
       "      <td>1,867.0</td>\n",
       "      <td>210</td>\n",
       "      <td>Ashland Ave &amp; Division St</td>\n",
       "      <td>382</td>\n",
       "      <td>Western Ave &amp; Congress Pkwy</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1987.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704049</th>\n",
       "      <td>25962900</td>\n",
       "      <td>2019-12-31 23:56:13</td>\n",
       "      <td>2020-01-01 00:15:45</td>\n",
       "      <td>2196</td>\n",
       "      <td>1,172.0</td>\n",
       "      <td>112</td>\n",
       "      <td>Green St &amp; Randolph St</td>\n",
       "      <td>225</td>\n",
       "      <td>Halsted St &amp; Dickens Ave</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704050</th>\n",
       "      <td>25962901</td>\n",
       "      <td>2019-12-31 23:56:34</td>\n",
       "      <td>2020-01-01 00:22:08</td>\n",
       "      <td>4877</td>\n",
       "      <td>1,533.0</td>\n",
       "      <td>90</td>\n",
       "      <td>Millennium Park</td>\n",
       "      <td>90</td>\n",
       "      <td>Millennium Park</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1992.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704051</th>\n",
       "      <td>25962902</td>\n",
       "      <td>2019-12-31 23:57:05</td>\n",
       "      <td>2020-01-01 00:05:46</td>\n",
       "      <td>863</td>\n",
       "      <td>520.0</td>\n",
       "      <td>623</td>\n",
       "      <td>Michigan Ave &amp; 8th St</td>\n",
       "      <td>52</td>\n",
       "      <td>Michigan Ave &amp; Lake St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1967.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704052</th>\n",
       "      <td>25962903</td>\n",
       "      <td>2019-12-31 23:57:11</td>\n",
       "      <td>2020-01-01 00:05:45</td>\n",
       "      <td>2637</td>\n",
       "      <td>514.0</td>\n",
       "      <td>623</td>\n",
       "      <td>Michigan Ave &amp; 8th St</td>\n",
       "      <td>52</td>\n",
       "      <td>Michigan Ave &amp; Lake St</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Female</td>\n",
       "      <td>1970.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704053</th>\n",
       "      <td>25962904</td>\n",
       "      <td>2019-12-31 23:57:17</td>\n",
       "      <td>2019-12-31 23:59:18</td>\n",
       "      <td>5930</td>\n",
       "      <td>120.0</td>\n",
       "      <td>256</td>\n",
       "      <td>Broadway &amp; Sheridan Rd</td>\n",
       "      <td>240</td>\n",
       "      <td>Sheridan Rd &amp; Irving Park Rd</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>1982.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>704054 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         ride_id           started_at             ended_at  bikeid  \\\n",
       "0       25223640  2019-10-01 00:01:39  2019-10-01 00:17:20    2215   \n",
       "1       25223641  2019-10-01 00:02:16  2019-10-01 00:06:34    6328   \n",
       "2       25223642  2019-10-01 00:04:32  2019-10-01 00:18:43    3003   \n",
       "3       25223643  2019-10-01 00:04:32  2019-10-01 00:43:43    3275   \n",
       "4       25223644  2019-10-01 00:04:34  2019-10-01 00:35:42    5294   \n",
       "...          ...                  ...                  ...     ...   \n",
       "704049  25962900  2019-12-31 23:56:13  2020-01-01 00:15:45    2196   \n",
       "704050  25962901  2019-12-31 23:56:34  2020-01-01 00:22:08    4877   \n",
       "704051  25962902  2019-12-31 23:57:05  2020-01-01 00:05:46     863   \n",
       "704052  25962903  2019-12-31 23:57:11  2020-01-01 00:05:45    2637   \n",
       "704053  25962904  2019-12-31 23:57:17  2019-12-31 23:59:18    5930   \n",
       "\n",
       "       tripduration  start_station_id              start_station_name  \\\n",
       "0             940.0                20    Sheffield Ave & Kingsbury St   \n",
       "1             258.0                19  Throop (Loomis) St & Taylor St   \n",
       "2             850.0                84       Milwaukee Ave & Grand Ave   \n",
       "3           2,350.0               313   Lakeview Ave & Fullerton Pkwy   \n",
       "4           1,867.0               210       Ashland Ave & Division St   \n",
       "...             ...               ...                             ...   \n",
       "704049      1,172.0               112          Green St & Randolph St   \n",
       "704050      1,533.0                90                 Millennium Park   \n",
       "704051        520.0               623           Michigan Ave & 8th St   \n",
       "704052        514.0               623           Michigan Ave & 8th St   \n",
       "704053        120.0               256          Broadway & Sheridan Rd   \n",
       "\n",
       "        end_station_id              end_station_name member_casual  gender  \\\n",
       "0                  309     Leavitt St & Armitage Ave    Subscriber    Male   \n",
       "1                  241           Morgan St & Polk St    Subscriber    Male   \n",
       "2                  199        Wabash Ave & Grand Ave    Subscriber  Female   \n",
       "3                  290        Kedzie Ave & Palmer Ct    Subscriber    Male   \n",
       "4                  382   Western Ave & Congress Pkwy    Subscriber    Male   \n",
       "...                ...                           ...           ...     ...   \n",
       "704049             225      Halsted St & Dickens Ave    Subscriber    Male   \n",
       "704050              90               Millennium Park    Subscriber    Male   \n",
       "704051              52        Michigan Ave & Lake St    Subscriber    Male   \n",
       "704052              52        Michigan Ave & Lake St    Subscriber  Female   \n",
       "704053             240  Sheridan Rd & Irving Park Rd    Subscriber    Male   \n",
       "\n",
       "        birthyear  \n",
       "0          1987.0  \n",
       "1          1998.0  \n",
       "2          1991.0  \n",
       "3          1990.0  \n",
       "4          1987.0  \n",
       "...           ...  \n",
       "704049     1981.0  \n",
       "704050     1992.0  \n",
       "704051     1967.0  \n",
       "704052     1970.0  \n",
       "704053     1982.0  \n",
       "\n",
       "[704054 rows x 12 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# since the data of the 4th quarter of 2019 has different attributes than the rest of the other datasets, rename the attributes \n",
    "d1.rename({'trip_id': 'ride_id', 'start_time':'started_at', 'end_time': 'ended_at', 'from_station_name':'start_station_name', 'from_station_id': 'start_station_id', 'to_station_name': 'end_station_name', 'to_station_id': 'end_station_id', 'usertype': 'member_casual'}, axis=1, inplace=True)\n",
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ride_id</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>member_casual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A847FADBBC638E45</td>\n",
       "      <td>2020-04-26 17:45:14</td>\n",
       "      <td>2020-04-26 18:12:03</td>\n",
       "      <td>Eckhart Park</td>\n",
       "      <td>86</td>\n",
       "      <td>Lincoln Ave &amp; Diversey Pkwy</td>\n",
       "      <td>152.0</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5405B80E996FF60D</td>\n",
       "      <td>2020-04-17 17:08:54</td>\n",
       "      <td>2020-04-17 17:17:03</td>\n",
       "      <td>Drake Ave &amp; Fullerton Ave</td>\n",
       "      <td>503</td>\n",
       "      <td>Kosciuszko Park</td>\n",
       "      <td>499.0</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5DD24A79A4E006F4</td>\n",
       "      <td>2020-04-01 17:54:13</td>\n",
       "      <td>2020-04-01 18:08:36</td>\n",
       "      <td>McClurg Ct &amp; Erie St</td>\n",
       "      <td>142</td>\n",
       "      <td>Indiana Ave &amp; Roosevelt Rd</td>\n",
       "      <td>255.0</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2A59BBDF5CDBA725</td>\n",
       "      <td>2020-04-07 12:50:19</td>\n",
       "      <td>2020-04-07 13:02:31</td>\n",
       "      <td>California Ave &amp; Division St</td>\n",
       "      <td>216</td>\n",
       "      <td>Wood St &amp; Augusta Blvd</td>\n",
       "      <td>657.0</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>27AD306C119C6158</td>\n",
       "      <td>2020-04-18 10:22:59</td>\n",
       "      <td>2020-04-18 11:15:54</td>\n",
       "      <td>Rush St &amp; Hubbard St</td>\n",
       "      <td>125</td>\n",
       "      <td>Sheridan Rd &amp; Lawrence Ave</td>\n",
       "      <td>323.0</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84771</th>\n",
       "      <td>200E9CDFC5685AA0</td>\n",
       "      <td>2020-04-16 16:10:16</td>\n",
       "      <td>2020-04-16 16:23:11</td>\n",
       "      <td>Dearborn Pkwy &amp; Delaware Pl</td>\n",
       "      <td>140</td>\n",
       "      <td>Dearborn Pkwy &amp; Delaware Pl</td>\n",
       "      <td>140.0</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84772</th>\n",
       "      <td>F58A8F2ABCB5D95B</td>\n",
       "      <td>2020-04-30 17:56:12</td>\n",
       "      <td>2020-04-30 18:15:21</td>\n",
       "      <td>Kimbark Ave &amp; 53rd St</td>\n",
       "      <td>322</td>\n",
       "      <td>Cottage Grove Ave &amp; 51st St</td>\n",
       "      <td>351.0</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84773</th>\n",
       "      <td>A3754693A80E4913</td>\n",
       "      <td>2020-04-24 19:57:33</td>\n",
       "      <td>2020-04-24 21:50:43</td>\n",
       "      <td>Sedgwick St &amp; Schiller St</td>\n",
       "      <td>236</td>\n",
       "      <td>Wells St &amp; Elm St</td>\n",
       "      <td>182.0</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84774</th>\n",
       "      <td>D610CABB67F7B744</td>\n",
       "      <td>2020-04-02 17:59:55</td>\n",
       "      <td>2020-04-02 18:42:26</td>\n",
       "      <td>Damen Ave &amp; Charleston St</td>\n",
       "      <td>310</td>\n",
       "      <td>Damen Ave &amp; Charleston St</td>\n",
       "      <td>310.0</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84775</th>\n",
       "      <td>79634521ABE4CF69</td>\n",
       "      <td>2020-04-04 01:31:27</td>\n",
       "      <td>2020-04-04 01:37:27</td>\n",
       "      <td>Clybourn Ave &amp; Division St</td>\n",
       "      <td>138</td>\n",
       "      <td>Clybourn Ave &amp; Division St</td>\n",
       "      <td>138.0</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84776 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ride_id           started_at             ended_at  \\\n",
       "0      A847FADBBC638E45  2020-04-26 17:45:14  2020-04-26 18:12:03   \n",
       "1      5405B80E996FF60D  2020-04-17 17:08:54  2020-04-17 17:17:03   \n",
       "2      5DD24A79A4E006F4  2020-04-01 17:54:13  2020-04-01 18:08:36   \n",
       "3      2A59BBDF5CDBA725  2020-04-07 12:50:19  2020-04-07 13:02:31   \n",
       "4      27AD306C119C6158  2020-04-18 10:22:59  2020-04-18 11:15:54   \n",
       "...                 ...                  ...                  ...   \n",
       "84771  200E9CDFC5685AA0  2020-04-16 16:10:16  2020-04-16 16:23:11   \n",
       "84772  F58A8F2ABCB5D95B  2020-04-30 17:56:12  2020-04-30 18:15:21   \n",
       "84773  A3754693A80E4913  2020-04-24 19:57:33  2020-04-24 21:50:43   \n",
       "84774  D610CABB67F7B744  2020-04-02 17:59:55  2020-04-02 18:42:26   \n",
       "84775  79634521ABE4CF69  2020-04-04 01:31:27  2020-04-04 01:37:27   \n",
       "\n",
       "                 start_station_name  start_station_id  \\\n",
       "0                      Eckhart Park                86   \n",
       "1         Drake Ave & Fullerton Ave               503   \n",
       "2              McClurg Ct & Erie St               142   \n",
       "3      California Ave & Division St               216   \n",
       "4              Rush St & Hubbard St               125   \n",
       "...                             ...               ...   \n",
       "84771   Dearborn Pkwy & Delaware Pl               140   \n",
       "84772         Kimbark Ave & 53rd St               322   \n",
       "84773     Sedgwick St & Schiller St               236   \n",
       "84774     Damen Ave & Charleston St               310   \n",
       "84775    Clybourn Ave & Division St               138   \n",
       "\n",
       "                  end_station_name  end_station_id member_casual  \n",
       "0      Lincoln Ave & Diversey Pkwy           152.0        member  \n",
       "1                  Kosciuszko Park           499.0        member  \n",
       "2       Indiana Ave & Roosevelt Rd           255.0        member  \n",
       "3           Wood St & Augusta Blvd           657.0        member  \n",
       "4       Sheridan Rd & Lawrence Ave           323.0        casual  \n",
       "...                            ...             ...           ...  \n",
       "84771  Dearborn Pkwy & Delaware Pl           140.0        member  \n",
       "84772  Cottage Grove Ave & 51st St           351.0        casual  \n",
       "84773            Wells St & Elm St           182.0        casual  \n",
       "84774    Damen Ave & Charleston St           310.0        casual  \n",
       "84775   Clybourn Ave & Division St           138.0        casual  \n",
       "\n",
       "[84776 rows x 8 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract the necessary columns from all the datasets\n",
    "d1 = d1[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "d2 = d2[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "d3 = d3[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "d4 = d4[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "d5 = d5[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "d6 = d6[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "d7 = d7[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "d8 = d8[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "d9 = d9[['ride_id', 'started_at', 'ended_at', 'start_station_name', 'start_station_id', 'end_station_name', 'end_station_id', 'member_casual']]\n",
    "\n",
    "d3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>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>member_casual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25223640</td>\n",
       "      <td>2019-10-01 00:01:39</td>\n",
       "      <td>2019-10-01 00:17:20</td>\n",
       "      <td>Sheffield Ave &amp; Kingsbury St</td>\n",
       "      <td>20.0</td>\n",
       "      <td>Leavitt St &amp; Armitage Ave</td>\n",
       "      <td>309.0</td>\n",
       "      <td>Subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25223641</td>\n",
       "      <td>2019-10-01 00:02:16</td>\n",
       "      <td>2019-10-01 00:06:34</td>\n",
       "      <td>Throop (Loomis) St &amp; Taylor St</td>\n",
       "      <td>19.0</td>\n",
       "      <td>Morgan St &amp; Polk St</td>\n",
       "      <td>241.0</td>\n",
       "      <td>Subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25223642</td>\n",
       "      <td>2019-10-01 00:04:32</td>\n",
       "      <td>2019-10-01 00:18:43</td>\n",
       "      <td>Milwaukee Ave &amp; Grand Ave</td>\n",
       "      <td>84.0</td>\n",
       "      <td>Wabash Ave &amp; Grand Ave</td>\n",
       "      <td>199.0</td>\n",
       "      <td>Subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25223643</td>\n",
       "      <td>2019-10-01 00:04:32</td>\n",
       "      <td>2019-10-01 00:43:43</td>\n",
       "      <td>Lakeview Ave &amp; Fullerton Pkwy</td>\n",
       "      <td>313.0</td>\n",
       "      <td>Kedzie Ave &amp; Palmer Ct</td>\n",
       "      <td>290.0</td>\n",
       "      <td>Subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25223644</td>\n",
       "      <td>2019-10-01 00:04:34</td>\n",
       "      <td>2019-10-01 00:35:42</td>\n",
       "      <td>Ashland Ave &amp; Division St</td>\n",
       "      <td>210.0</td>\n",
       "      <td>Western Ave &amp; Congress Pkwy</td>\n",
       "      <td>382.0</td>\n",
       "      <td>Subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388648</th>\n",
       "      <td>73F13E3E24954C57</td>\n",
       "      <td>2020-10-28 08:31:24</td>\n",
       "      <td>2020-10-28 08:37:50</td>\n",
       "      <td>Leavitt St &amp; Division St</td>\n",
       "      <td>658.0</td>\n",
       "      <td>Wood St &amp; Augusta Blvd</td>\n",
       "      <td>657.0</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388649</th>\n",
       "      <td>6F68E6F5704E2C08</td>\n",
       "      <td>2020-10-06 10:04:49</td>\n",
       "      <td>2020-10-06 10:13:25</td>\n",
       "      <td>Aberdeen St &amp; Jackson Blvd</td>\n",
       "      <td>21.0</td>\n",
       "      <td>Loomis St &amp; Lexington St</td>\n",
       "      <td>320.0</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388650</th>\n",
       "      <td>79C12697BEBF5B0A</td>\n",
       "      <td>2020-10-15 06:54:49</td>\n",
       "      <td>2020-10-15 07:22:23</td>\n",
       "      <td>California Ave &amp; Altgeld St</td>\n",
       "      <td>502.0</td>\n",
       "      <td>California Ave &amp; Altgeld St</td>\n",
       "      <td>502.0</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388651</th>\n",
       "      <td>907E10168C2F53FE</td>\n",
       "      <td>2020-10-16 10:29:59</td>\n",
       "      <td>2020-10-16 10:50:10</td>\n",
       "      <td>Lincoln Ave &amp; Diversey Pkwy</td>\n",
       "      <td>152.0</td>\n",
       "      <td>Ashland Ave &amp; Blackhawk St</td>\n",
       "      <td>333.0</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388652</th>\n",
       "      <td>A12644A5B2CA6B4E</td>\n",
       "      <td>2020-10-31 20:13:00</td>\n",
       "      <td>2020-10-31 20:24:28</td>\n",
       "      <td>Clark St &amp; Newport St</td>\n",
       "      <td>632.0</td>\n",
       "      <td>Clark St &amp; Winnemac Ave</td>\n",
       "      <td>325.0</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3854448 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ride_id           started_at             ended_at  \\\n",
       "0               25223640  2019-10-01 00:01:39  2019-10-01 00:17:20   \n",
       "1               25223641  2019-10-01 00:02:16  2019-10-01 00:06:34   \n",
       "2               25223642  2019-10-01 00:04:32  2019-10-01 00:18:43   \n",
       "3               25223643  2019-10-01 00:04:32  2019-10-01 00:43:43   \n",
       "4               25223644  2019-10-01 00:04:34  2019-10-01 00:35:42   \n",
       "...                  ...                  ...                  ...   \n",
       "388648  73F13E3E24954C57  2020-10-28 08:31:24  2020-10-28 08:37:50   \n",
       "388649  6F68E6F5704E2C08  2020-10-06 10:04:49  2020-10-06 10:13:25   \n",
       "388650  79C12697BEBF5B0A  2020-10-15 06:54:49  2020-10-15 07:22:23   \n",
       "388651  907E10168C2F53FE  2020-10-16 10:29:59  2020-10-16 10:50:10   \n",
       "388652  A12644A5B2CA6B4E  2020-10-31 20:13:00  2020-10-31 20:24:28   \n",
       "\n",
       "                    start_station_name  start_station_id  \\\n",
       "0         Sheffield Ave & Kingsbury St              20.0   \n",
       "1       Throop (Loomis) St & Taylor St              19.0   \n",
       "2            Milwaukee Ave & Grand Ave              84.0   \n",
       "3        Lakeview Ave & Fullerton Pkwy             313.0   \n",
       "4            Ashland Ave & Division St             210.0   \n",
       "...                                ...               ...   \n",
       "388648        Leavitt St & Division St             658.0   \n",
       "388649      Aberdeen St & Jackson Blvd              21.0   \n",
       "388650     California Ave & Altgeld St             502.0   \n",
       "388651     Lincoln Ave & Diversey Pkwy             152.0   \n",
       "388652           Clark St & Newport St             632.0   \n",
       "\n",
       "                   end_station_name  end_station_id member_casual  \n",
       "0         Leavitt St & Armitage Ave           309.0    Subscriber  \n",
       "1               Morgan St & Polk St           241.0    Subscriber  \n",
       "2            Wabash Ave & Grand Ave           199.0    Subscriber  \n",
       "3            Kedzie Ave & Palmer Ct           290.0    Subscriber  \n",
       "4       Western Ave & Congress Pkwy           382.0    Subscriber  \n",
       "...                             ...             ...           ...  \n",
       "388648       Wood St & Augusta Blvd           657.0        member  \n",
       "388649     Loomis St & Lexington St           320.0        member  \n",
       "388650  California Ave & Altgeld St           502.0        casual  \n",
       "388651   Ashland Ave & Blackhawk St           333.0        casual  \n",
       "388652      Clark St & Winnemac Ave           325.0        casual  \n",
       "\n",
       "[3854448 rows x 8 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge all the datasets to get the complete data for the Divvy bikes\n",
    "divvy_data = pd.concat([d1, d2, d3, d4, d5, d6, d7, d8, d9])\n",
    "divvy_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>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>member_casual</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25223640</td>\n",
       "      <td>2019-10-01 00:01:39</td>\n",
       "      <td>2019-10-01 00:17:20</td>\n",
       "      <td>Sheffield Ave &amp; Kingsbury St</td>\n",
       "      <td>20.0</td>\n",
       "      <td>Leavitt St &amp; Armitage Ave</td>\n",
       "      <td>309.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25223641</td>\n",
       "      <td>2019-10-01 00:02:16</td>\n",
       "      <td>2019-10-01 00:06:34</td>\n",
       "      <td>Throop (Loomis) St &amp; Taylor St</td>\n",
       "      <td>19.0</td>\n",
       "      <td>Morgan St &amp; Polk St</td>\n",
       "      <td>241.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25223642</td>\n",
       "      <td>2019-10-01 00:04:32</td>\n",
       "      <td>2019-10-01 00:18:43</td>\n",
       "      <td>Milwaukee Ave &amp; Grand Ave</td>\n",
       "      <td>84.0</td>\n",
       "      <td>Wabash Ave &amp; Grand Ave</td>\n",
       "      <td>199.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25223643</td>\n",
       "      <td>2019-10-01 00:04:32</td>\n",
       "      <td>2019-10-01 00:43:43</td>\n",
       "      <td>Lakeview Ave &amp; Fullerton Pkwy</td>\n",
       "      <td>313.0</td>\n",
       "      <td>Kedzie Ave &amp; Palmer Ct</td>\n",
       "      <td>290.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25223644</td>\n",
       "      <td>2019-10-01 00:04:34</td>\n",
       "      <td>2019-10-01 00:35:42</td>\n",
       "      <td>Ashland Ave &amp; Division St</td>\n",
       "      <td>210.0</td>\n",
       "      <td>Western Ave &amp; Congress Pkwy</td>\n",
       "      <td>382.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388648</th>\n",
       "      <td>73F13E3E24954C57</td>\n",
       "      <td>2020-10-28 08:31:24</td>\n",
       "      <td>2020-10-28 08:37:50</td>\n",
       "      <td>Leavitt St &amp; Division St</td>\n",
       "      <td>658.0</td>\n",
       "      <td>Wood St &amp; Augusta Blvd</td>\n",
       "      <td>657.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>28</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388649</th>\n",
       "      <td>6F68E6F5704E2C08</td>\n",
       "      <td>2020-10-06 10:04:49</td>\n",
       "      <td>2020-10-06 10:13:25</td>\n",
       "      <td>Aberdeen St &amp; Jackson Blvd</td>\n",
       "      <td>21.0</td>\n",
       "      <td>Loomis St &amp; Lexington St</td>\n",
       "      <td>320.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388650</th>\n",
       "      <td>79C12697BEBF5B0A</td>\n",
       "      <td>2020-10-15 06:54:49</td>\n",
       "      <td>2020-10-15 07:22:23</td>\n",
       "      <td>California Ave &amp; Altgeld St</td>\n",
       "      <td>502.0</td>\n",
       "      <td>California Ave &amp; Altgeld St</td>\n",
       "      <td>502.0</td>\n",
       "      <td>casual</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388651</th>\n",
       "      <td>907E10168C2F53FE</td>\n",
       "      <td>2020-10-16 10:29:59</td>\n",
       "      <td>2020-10-16 10:50:10</td>\n",
       "      <td>Lincoln Ave &amp; Diversey Pkwy</td>\n",
       "      <td>152.0</td>\n",
       "      <td>Ashland Ave &amp; Blackhawk St</td>\n",
       "      <td>333.0</td>\n",
       "      <td>casual</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388652</th>\n",
       "      <td>A12644A5B2CA6B4E</td>\n",
       "      <td>2020-10-31 20:13:00</td>\n",
       "      <td>2020-10-31 20:24:28</td>\n",
       "      <td>Clark St &amp; Newport St</td>\n",
       "      <td>632.0</td>\n",
       "      <td>Clark St &amp; Winnemac Ave</td>\n",
       "      <td>325.0</td>\n",
       "      <td>casual</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3854448 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ride_id          started_at            ended_at  \\\n",
       "0               25223640 2019-10-01 00:01:39 2019-10-01 00:17:20   \n",
       "1               25223641 2019-10-01 00:02:16 2019-10-01 00:06:34   \n",
       "2               25223642 2019-10-01 00:04:32 2019-10-01 00:18:43   \n",
       "3               25223643 2019-10-01 00:04:32 2019-10-01 00:43:43   \n",
       "4               25223644 2019-10-01 00:04:34 2019-10-01 00:35:42   \n",
       "...                  ...                 ...                 ...   \n",
       "388648  73F13E3E24954C57 2020-10-28 08:31:24 2020-10-28 08:37:50   \n",
       "388649  6F68E6F5704E2C08 2020-10-06 10:04:49 2020-10-06 10:13:25   \n",
       "388650  79C12697BEBF5B0A 2020-10-15 06:54:49 2020-10-15 07:22:23   \n",
       "388651  907E10168C2F53FE 2020-10-16 10:29:59 2020-10-16 10:50:10   \n",
       "388652  A12644A5B2CA6B4E 2020-10-31 20:13:00 2020-10-31 20:24:28   \n",
       "\n",
       "                    start_station_name  start_station_id  \\\n",
       "0         Sheffield Ave & Kingsbury St              20.0   \n",
       "1       Throop (Loomis) St & Taylor St              19.0   \n",
       "2            Milwaukee Ave & Grand Ave              84.0   \n",
       "3        Lakeview Ave & Fullerton Pkwy             313.0   \n",
       "4            Ashland Ave & Division St             210.0   \n",
       "...                                ...               ...   \n",
       "388648        Leavitt St & Division St             658.0   \n",
       "388649      Aberdeen St & Jackson Blvd              21.0   \n",
       "388650     California Ave & Altgeld St             502.0   \n",
       "388651     Lincoln Ave & Diversey Pkwy             152.0   \n",
       "388652           Clark St & Newport St             632.0   \n",
       "\n",
       "                   end_station_name  end_station_id member_casual  Year  \\\n",
       "0         Leavitt St & Armitage Ave           309.0    Subscriber  2019   \n",
       "1               Morgan St & Polk St           241.0    Subscriber  2019   \n",
       "2            Wabash Ave & Grand Ave           199.0    Subscriber  2019   \n",
       "3            Kedzie Ave & Palmer Ct           290.0    Subscriber  2019   \n",
       "4       Western Ave & Congress Pkwy           382.0    Subscriber  2019   \n",
       "...                             ...             ...           ...   ...   \n",
       "388648       Wood St & Augusta Blvd           657.0        member  2020   \n",
       "388649     Loomis St & Lexington St           320.0        member  2020   \n",
       "388650  California Ave & Altgeld St           502.0        casual  2020   \n",
       "388651   Ashland Ave & Blackhawk St           333.0        casual  2020   \n",
       "388652      Clark St & Winnemac Ave           325.0        casual  2020   \n",
       "\n",
       "        Month  Day  Hour  \n",
       "0          10    1     0  \n",
       "1          10    1     0  \n",
       "2          10    1     0  \n",
       "3          10    1     0  \n",
       "4          10    1     0  \n",
       "...       ...  ...   ...  \n",
       "388648     10   28     8  \n",
       "388649     10    6    10  \n",
       "388650     10   15     6  \n",
       "388651     10   16    10  \n",
       "388652     10   31    20  \n",
       "\n",
       "[3854448 rows x 12 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the date columns (start and end times) in the divvy data to datetime format \n",
    "divvy_data['started_at'] = pd.to_datetime(divvy_data.started_at)\n",
    "divvy_data['ended_at'] = pd.to_datetime(divvy_data.ended_at)\n",
    "\n",
    "# make new columns 'Year', 'Month', 'Day' and 'Hour' based on the 'started_at' column\n",
    "divvy_data['Year'] = divvy_data.started_at.dt.year\n",
    "divvy_data['Month'] = divvy_data.started_at.dt.month\n",
    "divvy_data['Day'] = divvy_data.started_at.dt.day\n",
    "divvy_data['Hour'] = divvy_data.started_at.dt.hour\n",
    "\n",
    "# save the resuting dataframe to a csv\n",
    "divvy_data.to_csv(\"divvy_final.csv\", index=False)\n",
    "divvy_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataset 2 - Weather Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Date</th>\n",
       "      <th>d</th>\n",
       "      <th>Time</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Wind Gust</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Condition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11/01/19 23:53</td>\n",
       "      <td>11/1/2019</td>\n",
       "      <td>11:53 PM</td>\n",
       "      <td>27  F</td>\n",
       "      <td>81</td>\n",
       "      <td>7 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11/01/19 00:53</td>\n",
       "      <td>11/1/2019</td>\n",
       "      <td>12:53 AM</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11/01/19 01:53</td>\n",
       "      <td>11/1/2019</td>\n",
       "      <td>1:53 AM</td>\n",
       "      <td>25  F</td>\n",
       "      <td>85</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11/01/19 02:53</td>\n",
       "      <td>11/1/2019</td>\n",
       "      <td>2:53 AM</td>\n",
       "      <td>26  F</td>\n",
       "      <td>84</td>\n",
       "      <td>6 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11/01/19 03:53</td>\n",
       "      <td>11/1/2019</td>\n",
       "      <td>3:53 AM</td>\n",
       "      <td>25  F</td>\n",
       "      <td>88</td>\n",
       "      <td>5 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10331</th>\n",
       "      <td>10/31/20 18:53</td>\n",
       "      <td>10/31/2020</td>\n",
       "      <td>6:53 PM</td>\n",
       "      <td>55 F</td>\n",
       "      <td>51</td>\n",
       "      <td>15mph</td>\n",
       "      <td>30mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10332</th>\n",
       "      <td>10/31/20 19:53</td>\n",
       "      <td>10/31/2020</td>\n",
       "      <td>7:53 PM</td>\n",
       "      <td>54 F</td>\n",
       "      <td>55</td>\n",
       "      <td>18mph</td>\n",
       "      <td>29mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10333</th>\n",
       "      <td>10/31/20 20:53</td>\n",
       "      <td>10/31/2020</td>\n",
       "      <td>8:53 PM</td>\n",
       "      <td>54 F</td>\n",
       "      <td>55</td>\n",
       "      <td>18mph</td>\n",
       "      <td>29mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10334</th>\n",
       "      <td>10/31/20 21:53</td>\n",
       "      <td>10/31/2020</td>\n",
       "      <td>9:53 PM</td>\n",
       "      <td>52 F</td>\n",
       "      <td>66</td>\n",
       "      <td>16mph</td>\n",
       "      <td>30mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10335</th>\n",
       "      <td>10/31/20 22:53</td>\n",
       "      <td>10/31/2020</td>\n",
       "      <td>10:53 PM</td>\n",
       "      <td>53 F</td>\n",
       "      <td>61</td>\n",
       "      <td>16mph</td>\n",
       "      <td>25mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10336 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Date           d      Time Temperature  Humidity Wind Speed  \\\n",
       "0      11/01/19 23:53   11/1/2019  11:53 PM       27  F        81      7 mph   \n",
       "1      11/01/19 00:53   11/1/2019  12:53 AM       26  F        81      8 mph   \n",
       "2      11/01/19 01:53   11/1/2019   1:53 AM       25  F        85      8 mph   \n",
       "3      11/01/19 02:53   11/1/2019   2:53 AM       26  F        84      6 mph   \n",
       "4      11/01/19 03:53   11/1/2019   3:53 AM       25  F        88      5 mph   \n",
       "...               ...         ...       ...         ...       ...        ...   \n",
       "10331  10/31/20 18:53  10/31/2020   6:53 PM        55 F        51      15mph   \n",
       "10332  10/31/20 19:53  10/31/2020   7:53 PM        54 F        55      18mph   \n",
       "10333  10/31/20 20:53  10/31/2020   8:53 PM        54 F        55      18mph   \n",
       "10334  10/31/20 21:53  10/31/2020   9:53 PM        52 F        66      16mph   \n",
       "10335  10/31/20 22:53  10/31/2020  10:53 PM        53 F        61      16mph   \n",
       "\n",
       "      Wind Gust Precipitation      Condition  \n",
       "0         0 mph        0.0 in           Fair  \n",
       "1         0 mph        0.0 in           Fair  \n",
       "2         0 mph        0.0 in           Fair  \n",
       "3         0 mph        0.0 in           Fair  \n",
       "4         0 mph        0.0 in           Fair  \n",
       "...         ...           ...            ...  \n",
       "10331     30mph         0.0in  Partly Cloudy  \n",
       "10332     29mph         0.0in  Mostly Cloudy  \n",
       "10333     29mph         0.0in         Cloudy  \n",
       "10334     30mph         0.0in         Cloudy  \n",
       "10335     25mph         0.0in  Mostly Cloudy  \n",
       "\n",
       "[10336 rows x 9 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read the weather data from the csv file into a pandas dataframe\n",
    "weather = pd.read_csv(\"weather/weather.csv\")\n",
    "weather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Date</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Wind Gust</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Condition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11/01/19 23:53</td>\n",
       "      <td>27  F</td>\n",
       "      <td>81</td>\n",
       "      <td>7 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11/01/19 00:53</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11/01/19 01:53</td>\n",
       "      <td>25  F</td>\n",
       "      <td>85</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11/01/19 02:53</td>\n",
       "      <td>26  F</td>\n",
       "      <td>84</td>\n",
       "      <td>6 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11/01/19 03:53</td>\n",
       "      <td>25  F</td>\n",
       "      <td>88</td>\n",
       "      <td>5 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10331</th>\n",
       "      <td>10/31/20 18:53</td>\n",
       "      <td>55 F</td>\n",
       "      <td>51</td>\n",
       "      <td>15mph</td>\n",
       "      <td>30mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10332</th>\n",
       "      <td>10/31/20 19:53</td>\n",
       "      <td>54 F</td>\n",
       "      <td>55</td>\n",
       "      <td>18mph</td>\n",
       "      <td>29mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10333</th>\n",
       "      <td>10/31/20 20:53</td>\n",
       "      <td>54 F</td>\n",
       "      <td>55</td>\n",
       "      <td>18mph</td>\n",
       "      <td>29mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10334</th>\n",
       "      <td>10/31/20 21:53</td>\n",
       "      <td>52 F</td>\n",
       "      <td>66</td>\n",
       "      <td>16mph</td>\n",
       "      <td>30mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10335</th>\n",
       "      <td>10/31/20 22:53</td>\n",
       "      <td>53 F</td>\n",
       "      <td>61</td>\n",
       "      <td>16mph</td>\n",
       "      <td>25mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10336 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Date Temperature  Humidity Wind Speed Wind Gust  \\\n",
       "0      11/01/19 23:53       27  F        81      7 mph     0 mph   \n",
       "1      11/01/19 00:53       26  F        81      8 mph     0 mph   \n",
       "2      11/01/19 01:53       25  F        85      8 mph     0 mph   \n",
       "3      11/01/19 02:53       26  F        84      6 mph     0 mph   \n",
       "4      11/01/19 03:53       25  F        88      5 mph     0 mph   \n",
       "...               ...         ...       ...        ...       ...   \n",
       "10331  10/31/20 18:53        55 F        51      15mph     30mph   \n",
       "10332  10/31/20 19:53        54 F        55      18mph     29mph   \n",
       "10333  10/31/20 20:53        54 F        55      18mph     29mph   \n",
       "10334  10/31/20 21:53        52 F        66      16mph     30mph   \n",
       "10335  10/31/20 22:53        53 F        61      16mph     25mph   \n",
       "\n",
       "      Precipitation      Condition  \n",
       "0            0.0 in           Fair  \n",
       "1            0.0 in           Fair  \n",
       "2            0.0 in           Fair  \n",
       "3            0.0 in           Fair  \n",
       "4            0.0 in           Fair  \n",
       "...             ...            ...  \n",
       "10331         0.0in  Partly Cloudy  \n",
       "10332         0.0in  Mostly Cloudy  \n",
       "10333         0.0in         Cloudy  \n",
       "10334         0.0in         Cloudy  \n",
       "10335         0.0in  Mostly Cloudy  \n",
       "\n",
       "[10336 rows x 7 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract the relevant columns needed for our predictions\n",
    "weather = weather[['Date', 'Temperature', 'Humidity', 'Wind Speed', 'Wind Gust', 'Precipitation', 'Condition']]\n",
    "weather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Date</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Wind Gust</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Condition</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-01 23:53:00</td>\n",
       "      <td>27  F</td>\n",
       "      <td>81</td>\n",
       "      <td>7 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-01 00:53:00</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-11-01 01:53:00</td>\n",
       "      <td>25  F</td>\n",
       "      <td>85</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-11-01 02:53:00</td>\n",
       "      <td>26  F</td>\n",
       "      <td>84</td>\n",
       "      <td>6 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-11-01 03:53:00</td>\n",
       "      <td>25  F</td>\n",
       "      <td>88</td>\n",
       "      <td>5 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10331</th>\n",
       "      <td>2020-10-31 18:53:00</td>\n",
       "      <td>55 F</td>\n",
       "      <td>51</td>\n",
       "      <td>15mph</td>\n",
       "      <td>30mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10332</th>\n",
       "      <td>2020-10-31 19:53:00</td>\n",
       "      <td>54 F</td>\n",
       "      <td>55</td>\n",
       "      <td>18mph</td>\n",
       "      <td>29mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10333</th>\n",
       "      <td>2020-10-31 20:53:00</td>\n",
       "      <td>54 F</td>\n",
       "      <td>55</td>\n",
       "      <td>18mph</td>\n",
       "      <td>29mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10334</th>\n",
       "      <td>2020-10-31 21:53:00</td>\n",
       "      <td>52 F</td>\n",
       "      <td>66</td>\n",
       "      <td>16mph</td>\n",
       "      <td>30mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10335</th>\n",
       "      <td>2020-10-31 22:53:00</td>\n",
       "      <td>53 F</td>\n",
       "      <td>61</td>\n",
       "      <td>16mph</td>\n",
       "      <td>25mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Mostly Cloudy</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10336 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Date Temperature  Humidity Wind Speed Wind Gust  \\\n",
       "0     2019-11-01 23:53:00       27  F        81      7 mph     0 mph   \n",
       "1     2019-11-01 00:53:00       26  F        81      8 mph     0 mph   \n",
       "2     2019-11-01 01:53:00       25  F        85      8 mph     0 mph   \n",
       "3     2019-11-01 02:53:00       26  F        84      6 mph     0 mph   \n",
       "4     2019-11-01 03:53:00       25  F        88      5 mph     0 mph   \n",
       "...                   ...         ...       ...        ...       ...   \n",
       "10331 2020-10-31 18:53:00        55 F        51      15mph     30mph   \n",
       "10332 2020-10-31 19:53:00        54 F        55      18mph     29mph   \n",
       "10333 2020-10-31 20:53:00        54 F        55      18mph     29mph   \n",
       "10334 2020-10-31 21:53:00        52 F        66      16mph     30mph   \n",
       "10335 2020-10-31 22:53:00        53 F        61      16mph     25mph   \n",
       "\n",
       "      Precipitation      Condition  Year  Month  Day  Hour  \n",
       "0            0.0 in           Fair  2019     11    1    23  \n",
       "1            0.0 in           Fair  2019     11    1     0  \n",
       "2            0.0 in           Fair  2019     11    1     1  \n",
       "3            0.0 in           Fair  2019     11    1     2  \n",
       "4            0.0 in           Fair  2019     11    1     3  \n",
       "...             ...            ...   ...    ...  ...   ...  \n",
       "10331         0.0in  Partly Cloudy  2020     10   31    18  \n",
       "10332         0.0in  Mostly Cloudy  2020     10   31    19  \n",
       "10333         0.0in         Cloudy  2020     10   31    20  \n",
       "10334         0.0in         Cloudy  2020     10   31    21  \n",
       "10335         0.0in  Mostly Cloudy  2020     10   31    22  \n",
       "\n",
       "[10336 rows x 11 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert the weather date to datetime format\n",
    "weather['Date'] = pd.to_datetime(weather.Date)\n",
    "\n",
    "# create new columns 'Year', 'Month', 'Day' and 'Hour' based on the date column\n",
    "weather['Year'] = weather.Date.dt.year\n",
    "weather['Month'] = weather.Date.dt.month\n",
    "weather['Day'] = weather.Date.dt.day\n",
    "weather['Hour'] = weather.Date.dt.hour\n",
    "\n",
    "# save the dataframe to a csv file\n",
    "weather.to_csv(\"weather_final.csv\", index=False)\n",
    "weather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>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>member_casual</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Wind Gust</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Condition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25612325</td>\n",
       "      <td>2019-11-01 00:00:45</td>\n",
       "      <td>2019-11-01 00:08:10</td>\n",
       "      <td>Ellis Ave &amp; 55th St</td>\n",
       "      <td>420.0</td>\n",
       "      <td>Harper Ave &amp; 59th St</td>\n",
       "      <td>425.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25612326</td>\n",
       "      <td>2019-11-01 00:01:13</td>\n",
       "      <td>2019-11-01 00:30:09</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>Morgan St &amp; Polk St</td>\n",
       "      <td>241.0</td>\n",
       "      <td>Customer</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25612327</td>\n",
       "      <td>2019-11-01 00:01:42</td>\n",
       "      <td>2019-11-01 00:38:32</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>May St &amp; Taylor St</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25612328</td>\n",
       "      <td>2019-11-01 00:02:24</td>\n",
       "      <td>2019-11-01 00:09:27</td>\n",
       "      <td>Lincoln Ave &amp; Winona St</td>\n",
       "      <td>472.0</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>455.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25612329</td>\n",
       "      <td>2019-11-01 00:02:30</td>\n",
       "      <td>2019-11-01 00:04:09</td>\n",
       "      <td>Clinton St &amp; Madison St</td>\n",
       "      <td>77.0</td>\n",
       "      <td>Clinton St &amp; Washington Blvd</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3803530</th>\n",
       "      <td>7BBA6E8E6E19BA46</td>\n",
       "      <td>2020-10-22 05:17:20</td>\n",
       "      <td>2020-10-22 05:21:09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>49 F</td>\n",
       "      <td>90</td>\n",
       "      <td>16mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Light Rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3803531</th>\n",
       "      <td>5DE9694204DED47B</td>\n",
       "      <td>2020-10-22 05:16:55</td>\n",
       "      <td>2020-10-22 05:17:01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>48 F</td>\n",
       "      <td>93</td>\n",
       "      <td>9mph</td>\n",
       "      <td>22mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Light Drizzle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3803532</th>\n",
       "      <td>5DE9694204DED47B</td>\n",
       "      <td>2020-10-22 05:16:55</td>\n",
       "      <td>2020-10-22 05:17:01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>48 F</td>\n",
       "      <td>93</td>\n",
       "      <td>13mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Light Rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3803533</th>\n",
       "      <td>5DE9694204DED47B</td>\n",
       "      <td>2020-10-22 05:16:55</td>\n",
       "      <td>2020-10-22 05:17:01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>48 F</td>\n",
       "      <td>93</td>\n",
       "      <td>16mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Light Rain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3803534</th>\n",
       "      <td>5DE9694204DED47B</td>\n",
       "      <td>2020-10-22 05:16:55</td>\n",
       "      <td>2020-10-22 05:17:01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>49 F</td>\n",
       "      <td>90</td>\n",
       "      <td>16mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Light Rain</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3803535 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ride_id          started_at            ended_at  \\\n",
       "0                25612325 2019-11-01 00:00:45 2019-11-01 00:08:10   \n",
       "1                25612326 2019-11-01 00:01:13 2019-11-01 00:30:09   \n",
       "2                25612327 2019-11-01 00:01:42 2019-11-01 00:38:32   \n",
       "3                25612328 2019-11-01 00:02:24 2019-11-01 00:09:27   \n",
       "4                25612329 2019-11-01 00:02:30 2019-11-01 00:04:09   \n",
       "...                   ...                 ...                 ...   \n",
       "3803530  7BBA6E8E6E19BA46 2020-10-22 05:17:20 2020-10-22 05:21:09   \n",
       "3803531  5DE9694204DED47B 2020-10-22 05:16:55 2020-10-22 05:17:01   \n",
       "3803532  5DE9694204DED47B 2020-10-22 05:16:55 2020-10-22 05:17:01   \n",
       "3803533  5DE9694204DED47B 2020-10-22 05:16:55 2020-10-22 05:17:01   \n",
       "3803534  5DE9694204DED47B 2020-10-22 05:16:55 2020-10-22 05:17:01   \n",
       "\n",
       "                  start_station_name  start_station_id  \\\n",
       "0                Ellis Ave & 55th St             420.0   \n",
       "1        Western Ave & Winnebago Ave             116.0   \n",
       "2        Western Ave & Winnebago Ave             116.0   \n",
       "3            Lincoln Ave & Winona St             472.0   \n",
       "4            Clinton St & Madison St              77.0   \n",
       "...                              ...               ...   \n",
       "3803530                          NaN               NaN   \n",
       "3803531                          NaN               NaN   \n",
       "3803532                          NaN               NaN   \n",
       "3803533                          NaN               NaN   \n",
       "3803534                          NaN               NaN   \n",
       "\n",
       "                     end_station_name  end_station_id member_casual  Year  \\\n",
       "0                Harper Ave & 59th St           425.0    Subscriber  2019   \n",
       "1                 Morgan St & Polk St           241.0      Customer  2019   \n",
       "2                  May St & Taylor St            22.0    Subscriber  2019   \n",
       "3        Maplewood Ave & Peterson Ave           455.0    Subscriber  2019   \n",
       "4        Clinton St & Washington Blvd            91.0    Subscriber  2019   \n",
       "...                               ...             ...           ...   ...   \n",
       "3803530                           NaN             NaN        member  2020   \n",
       "3803531                           NaN             NaN        member  2020   \n",
       "3803532                           NaN             NaN        member  2020   \n",
       "3803533                           NaN             NaN        member  2020   \n",
       "3803534                           NaN             NaN        member  2020   \n",
       "\n",
       "         Month  Day  Hour Temperature  Humidity Wind Speed Wind Gust  \\\n",
       "0           11    1     0       26  F        81      8 mph     0 mph   \n",
       "1           11    1     0       26  F        81      8 mph     0 mph   \n",
       "2           11    1     0       26  F        81      8 mph     0 mph   \n",
       "3           11    1     0       26  F        81      8 mph     0 mph   \n",
       "4           11    1     0       26  F        81      8 mph     0 mph   \n",
       "...        ...  ...   ...         ...       ...        ...       ...   \n",
       "3803530     10   22     5        49 F        90      16mph      0mph   \n",
       "3803531     10   22     5        48 F        93       9mph     22mph   \n",
       "3803532     10   22     5        48 F        93      13mph      0mph   \n",
       "3803533     10   22     5        48 F        93      16mph      0mph   \n",
       "3803534     10   22     5        49 F        90      16mph      0mph   \n",
       "\n",
       "        Precipitation      Condition  \n",
       "0              0.0 in           Fair  \n",
       "1              0.0 in           Fair  \n",
       "2              0.0 in           Fair  \n",
       "3              0.0 in           Fair  \n",
       "4              0.0 in           Fair  \n",
       "...               ...            ...  \n",
       "3803530         0.0in     Light Rain  \n",
       "3803531         0.0in  Light Drizzle  \n",
       "3803532         0.0in     Light Rain  \n",
       "3803533         0.0in     Light Rain  \n",
       "3803534         0.0in     Light Rain  \n",
       "\n",
       "[3803535 rows x 18 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge both the divvy and weather data based on the extracted year, month, day and hour\n",
    "merged_df = divvy_data.merge(weather, how='inner', on=['Year', 'Month', 'Day', 'Hour'])\n",
    "\n",
    "# drop the 'Date' column from the merged data , since we are using the 'started_at' column from divvy_data\n",
    "merged_df.drop(['Date'], axis=1, inplace=True)\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataset 3: Holidays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>SNo</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Date</th>\n",
       "      <th>Official Name</th>\n",
       "      <th>Remarks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Veterans Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>11/28/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>12/25/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Christmas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1/1/2020</td>\n",
       "      <td>January 1(Fixed)</td>\n",
       "      <td>New Year's Day</td>\n",
       "      <td>Celebrates beginning of the Gregorian calendar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>Third Monday in January</td>\n",
       "      <td>Birthday of Martin Luther King, Jr.</td>\n",
       "      <td>Honors Dr. Martin Luther King, Jr., Civil Righ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>First January 20 following a Presidential elec...</td>\n",
       "      <td>Inauguration Day</td>\n",
       "      <td>Observed only by federal government employees ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>2/17/2020</td>\n",
       "      <td>Third Monday in February</td>\n",
       "      <td>Washington's Birthday</td>\n",
       "      <td>Washington's Birthday was first declared a fed...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>5/25/2020</td>\n",
       "      <td>Last Monday in May</td>\n",
       "      <td>Memorial Day</td>\n",
       "      <td>Honors the nation's war dead from the Civil Wa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.0</td>\n",
       "      <td>7/4/2020</td>\n",
       "      <td>4-Jul</td>\n",
       "      <td>Independence Day</td>\n",
       "      <td>Celebrates the signing of the Declaration of I...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10.0</td>\n",
       "      <td>9/7/2020</td>\n",
       "      <td>First Monday in September</td>\n",
       "      <td>Labor Day</td>\n",
       "      <td>Celebrates the achievements of workers and the...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11.0</td>\n",
       "      <td>10/12/2020</td>\n",
       "      <td>Second Monday in October</td>\n",
       "      <td>Columbus Day</td>\n",
       "      <td>Honors Christopher Columbus, traditional disco...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SNo  Unnamed: 1                                               Date  \\\n",
       "0    1.0  11/11/2019                                                NaN   \n",
       "1    2.0  11/28/2019                                                NaN   \n",
       "2    3.0  12/25/2019                                                NaN   \n",
       "3    4.0    1/1/2020                                   January 1(Fixed)   \n",
       "4    5.0   1/20/2020                            Third Monday in January   \n",
       "5    6.0   1/20/2020  First January 20 following a Presidential elec...   \n",
       "6    7.0   2/17/2020                           Third Monday in February   \n",
       "7    8.0   5/25/2020                                 Last Monday in May   \n",
       "8    9.0    7/4/2020                                              4-Jul   \n",
       "9   10.0    9/7/2020                          First Monday in September   \n",
       "10  11.0  10/12/2020                           Second Monday in October   \n",
       "11   NaN         NaN                                                NaN   \n",
       "12   NaN         NaN                                                NaN   \n",
       "13   NaN         NaN                                                NaN   \n",
       "\n",
       "                          Official Name  \\\n",
       "0                          Veterans Day   \n",
       "1                      Thanksgiving Day   \n",
       "2                             Christmas   \n",
       "3                        New Year's Day   \n",
       "4   Birthday of Martin Luther King, Jr.   \n",
       "5                      Inauguration Day   \n",
       "6                 Washington's Birthday   \n",
       "7                          Memorial Day   \n",
       "8                      Independence Day   \n",
       "9                             Labor Day   \n",
       "10                         Columbus Day   \n",
       "11                                  NaN   \n",
       "12                                  NaN   \n",
       "13                                  NaN   \n",
       "\n",
       "                                              Remarks  \n",
       "0                                                 NaN  \n",
       "1                                                 NaN  \n",
       "2                                                 NaN  \n",
       "3   Celebrates beginning of the Gregorian calendar...  \n",
       "4   Honors Dr. Martin Luther King, Jr., Civil Righ...  \n",
       "5   Observed only by federal government employees ...  \n",
       "6   Washington's Birthday was first declared a fed...  \n",
       "7   Honors the nation's war dead from the Civil Wa...  \n",
       "8   Celebrates the signing of the Declaration of I...  \n",
       "9   Celebrates the achievements of workers and the...  \n",
       "10  Honors Christopher Columbus, traditional disco...  \n",
       "11                                                NaN  \n",
       "12                                                NaN  \n",
       "13                                                NaN  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read the list of holidays from the csv file\n",
    "holidays = pd.read_csv(\"holidays/list-us-public-holidays-454j.csv\", encoding=\"ISO-8859-1\")\n",
    "holidays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove the last 3 rows, since they are all NaNs\n",
    "holidays.drop(holidays.tail(3).index,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>SNo</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Date</th>\n",
       "      <th>Official Name</th>\n",
       "      <th>Remarks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>11/11/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Veterans Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>11/28/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>12/25/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Christmas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1/1/2020</td>\n",
       "      <td>January 1(Fixed)</td>\n",
       "      <td>New Year's Day</td>\n",
       "      <td>Celebrates beginning of the Gregorian calendar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>Third Monday in January</td>\n",
       "      <td>Birthday of Martin Luther King, Jr.</td>\n",
       "      <td>Honors Dr. Martin Luther King, Jr., Civil Righ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>First January 20 following a Presidential elec...</td>\n",
       "      <td>Inauguration Day</td>\n",
       "      <td>Observed only by federal government employees ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>2/17/2020</td>\n",
       "      <td>Third Monday in February</td>\n",
       "      <td>Washington's Birthday</td>\n",
       "      <td>Washington's Birthday was first declared a fed...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>5/25/2020</td>\n",
       "      <td>Last Monday in May</td>\n",
       "      <td>Memorial Day</td>\n",
       "      <td>Honors the nation's war dead from the Civil Wa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.0</td>\n",
       "      <td>7/4/2020</td>\n",
       "      <td>4-Jul</td>\n",
       "      <td>Independence Day</td>\n",
       "      <td>Celebrates the signing of the Declaration of I...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10.0</td>\n",
       "      <td>9/7/2020</td>\n",
       "      <td>First Monday in September</td>\n",
       "      <td>Labor Day</td>\n",
       "      <td>Celebrates the achievements of workers and the...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11.0</td>\n",
       "      <td>10/12/2020</td>\n",
       "      <td>Second Monday in October</td>\n",
       "      <td>Columbus Day</td>\n",
       "      <td>Honors Christopher Columbus, traditional disco...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SNo  Unnamed: 1                                               Date  \\\n",
       "0    1.0  11/11/2019                                                NaN   \n",
       "1    2.0  11/28/2019                                                NaN   \n",
       "2    3.0  12/25/2019                                                NaN   \n",
       "3    4.0    1/1/2020                                   January 1(Fixed)   \n",
       "4    5.0   1/20/2020                            Third Monday in January   \n",
       "5    6.0   1/20/2020  First January 20 following a Presidential elec...   \n",
       "6    7.0   2/17/2020                           Third Monday in February   \n",
       "7    8.0   5/25/2020                                 Last Monday in May   \n",
       "8    9.0    7/4/2020                                              4-Jul   \n",
       "9   10.0    9/7/2020                          First Monday in September   \n",
       "10  11.0  10/12/2020                           Second Monday in October   \n",
       "\n",
       "                          Official Name  \\\n",
       "0                          Veterans Day   \n",
       "1                      Thanksgiving Day   \n",
       "2                             Christmas   \n",
       "3                        New Year's Day   \n",
       "4   Birthday of Martin Luther King, Jr.   \n",
       "5                      Inauguration Day   \n",
       "6                 Washington's Birthday   \n",
       "7                          Memorial Day   \n",
       "8                      Independence Day   \n",
       "9                             Labor Day   \n",
       "10                         Columbus Day   \n",
       "\n",
       "                                              Remarks  \n",
       "0                                                 NaN  \n",
       "1                                                 NaN  \n",
       "2                                                 NaN  \n",
       "3   Celebrates beginning of the Gregorian calendar...  \n",
       "4   Honors Dr. Martin Luther King, Jr., Civil Righ...  \n",
       "5   Observed only by federal government employees ...  \n",
       "6   Washington's Birthday was first declared a fed...  \n",
       "7   Honors the nation's war dead from the Civil Wa...  \n",
       "8   Celebrates the signing of the Declaration of I...  \n",
       "9   Celebrates the achievements of workers and the...  \n",
       "10  Honors Christopher Columbus, traditional disco...  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holidays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Official Name</th>\n",
       "      <th>Remarks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11/11/2019</td>\n",
       "      <td>Veterans Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11/28/2019</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12/25/2019</td>\n",
       "      <td>Christmas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1/1/2020</td>\n",
       "      <td>New Year's Day</td>\n",
       "      <td>Celebrates beginning of the Gregorian calendar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>Birthday of Martin Luther King, Jr.</td>\n",
       "      <td>Honors Dr. Martin Luther King, Jr., Civil Righ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1/20/2020</td>\n",
       "      <td>Inauguration Day</td>\n",
       "      <td>Observed only by federal government employees ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2/17/2020</td>\n",
       "      <td>Washington's Birthday</td>\n",
       "      <td>Washington's Birthday was first declared a fed...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5/25/2020</td>\n",
       "      <td>Memorial Day</td>\n",
       "      <td>Honors the nation's war dead from the Civil Wa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7/4/2020</td>\n",
       "      <td>Independence Day</td>\n",
       "      <td>Celebrates the signing of the Declaration of I...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9/7/2020</td>\n",
       "      <td>Labor Day</td>\n",
       "      <td>Celebrates the achievements of workers and the...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10/12/2020</td>\n",
       "      <td>Columbus Day</td>\n",
       "      <td>Honors Christopher Columbus, traditional disco...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date                        Official Name  \\\n",
       "0   11/11/2019                         Veterans Day   \n",
       "1   11/28/2019                     Thanksgiving Day   \n",
       "2   12/25/2019                            Christmas   \n",
       "3     1/1/2020                       New Year's Day   \n",
       "4    1/20/2020  Birthday of Martin Luther King, Jr.   \n",
       "5    1/20/2020                     Inauguration Day   \n",
       "6    2/17/2020                Washington's Birthday   \n",
       "7    5/25/2020                         Memorial Day   \n",
       "8     7/4/2020                     Independence Day   \n",
       "9     9/7/2020                            Labor Day   \n",
       "10  10/12/2020                         Columbus Day   \n",
       "\n",
       "                                              Remarks  \n",
       "0                                                 NaN  \n",
       "1                                                 NaN  \n",
       "2                                                 NaN  \n",
       "3   Celebrates beginning of the Gregorian calendar...  \n",
       "4   Honors Dr. Martin Luther King, Jr., Civil Righ...  \n",
       "5   Observed only by federal government employees ...  \n",
       "6   Washington's Birthday was first declared a fed...  \n",
       "7   Honors the nation's war dead from the Civil Wa...  \n",
       "8   Celebrates the signing of the Declaration of I...  \n",
       "9   Celebrates the achievements of workers and the...  \n",
       "10  Honors Christopher Columbus, traditional disco...  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract the relevant columns\n",
    "holidays = holidays[['Unnamed: 1', 'Official Name', 'Remarks']]\n",
    "\n",
    "# rename the Unnamed column to Date\n",
    "holidays.rename({'Unnamed: 1': 'Date'}, axis=1, inplace=True)\n",
    "holidays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert the date column to datetime format (for easier processing)\n",
    "holidays.Date = pd.to_datetime(holidays.Date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Date</th>\n",
       "      <th>Official Name</th>\n",
       "      <th>Remarks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>Veterans Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-28</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>Christmas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>New Year's Day</td>\n",
       "      <td>Celebrates beginning of the Gregorian calendar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>Birthday of Martin Luther King, Jr.</td>\n",
       "      <td>Honors Dr. Martin Luther King, Jr., Civil Righ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>Inauguration Day</td>\n",
       "      <td>Observed only by federal government employees ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>Washington's Birthday</td>\n",
       "      <td>Washington's Birthday was first declared a fed...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-05-25</td>\n",
       "      <td>Memorial Day</td>\n",
       "      <td>Honors the nation's war dead from the Civil Wa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-07-04</td>\n",
       "      <td>Independence Day</td>\n",
       "      <td>Celebrates the signing of the Declaration of I...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-09-07</td>\n",
       "      <td>Labor Day</td>\n",
       "      <td>Celebrates the achievements of workers and the...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-10-12</td>\n",
       "      <td>Columbus Day</td>\n",
       "      <td>Honors Christopher Columbus, traditional disco...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-04-06</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-04-07</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-04-08</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date                        Official Name  \\\n",
       "0  2019-11-11                         Veterans Day   \n",
       "1  2019-11-28                     Thanksgiving Day   \n",
       "2  2019-12-25                            Christmas   \n",
       "3  2020-01-01                       New Year's Day   \n",
       "4  2020-01-20  Birthday of Martin Luther King, Jr.   \n",
       "5  2020-01-20                     Inauguration Day   \n",
       "6  2020-02-17                Washington's Birthday   \n",
       "7  2020-05-25                         Memorial Day   \n",
       "8  2020-07-04                     Independence Day   \n",
       "9  2020-09-07                            Labor Day   \n",
       "10 2020-10-12                         Columbus Day   \n",
       "11 2020-04-06                         Spring Break   \n",
       "12 2020-04-07                         Spring Break   \n",
       "13 2020-04-08                         Spring Break   \n",
       "14 2020-04-09                         Spring Break   \n",
       "15 2020-04-10                         Spring Break   \n",
       "\n",
       "                                              Remarks  \n",
       "0                                                 NaN  \n",
       "1                                                 NaN  \n",
       "2                                                 NaN  \n",
       "3   Celebrates beginning of the Gregorian calendar...  \n",
       "4   Honors Dr. Martin Luther King, Jr., Civil Righ...  \n",
       "5   Observed only by federal government employees ...  \n",
       "6   Washington's Birthday was first declared a fed...  \n",
       "7   Honors the nation's war dead from the Civil Wa...  \n",
       "8   Celebrates the signing of the Declaration of I...  \n",
       "9   Celebrates the achievements of workers and the...  \n",
       "10  Honors Christopher Columbus, traditional disco...  \n",
       "11                                                     \n",
       "12                                                     \n",
       "13                                                     \n",
       "14                                                     \n",
       "15                                                     "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the list of dates for spring break\n",
    "vacations = {'Spring Break': pd.date_range('4/6/2020', '4/10/2020')}\n",
    "\n",
    "for desc, hols in vacations.items():\n",
    "    for hol in hols:\n",
    "        # get the date from the timestamp\n",
    "        hol=pd.to_datetime(hol).date()\n",
    "        # and append to the dataframe\n",
    "        holidays.loc[len(holidays.index)] = [hol, desc, '']\n",
    "        \n",
    "# assign it to the Date column\n",
    "holidays.Date = pd.to_datetime(holidays.Date)\n",
    "holidays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Date</th>\n",
       "      <th>Official Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-18</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-19</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-06-20</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-21</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-06-22</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>2020-08-30</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>2020-08-31</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>2020-09-03</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date    Official Name\n",
       "0  2020-06-18  Summer Vacation\n",
       "1  2020-06-19  Summer Vacation\n",
       "2  2020-06-20  Summer Vacation\n",
       "3  2020-06-21  Summer Vacation\n",
       "4  2020-06-22  Summer Vacation\n",
       "..        ...              ...\n",
       "72 2020-08-30  Summer Vacation\n",
       "73 2020-08-31  Summer Vacation\n",
       "74 2020-09-01  Summer Vacation\n",
       "75 2020-09-02  Summer Vacation\n",
       "76 2020-09-03  Summer Vacation\n",
       "\n",
       "[77 rows x 2 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get list of dates for summer vacation\n",
    "d = pd.date_range('6/18/2020', '9/3/2020') # datetimeindex obj\n",
    "d1 = list(holidays['Date']) # series\n",
    "# reassign the dates from the Date column in d1\n",
    "d1=[x.date() for x in d1]\n",
    "d2 = []\n",
    "for date in d:\n",
    "    # if the date isn't already in d1, add to the empty list d2\n",
    "    if date.date() not in d1:\n",
    "        d2.append(date.date())\n",
    "        \n",
    "# and assign it to a new dataframe\n",
    "sdates = pd.DataFrame(d2, columns=['Date'])\n",
    "# give the value as Summer Vacation\n",
    "sdates['Official Name'] = 'Summer Vacation'\n",
    "# convert the date into datetime format\n",
    "sdates['Date'] = pd.to_datetime(sdates.Date)\n",
    "sdates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Date</th>\n",
       "      <th>Official Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-12-27</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-12-29</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date    Official Name\n",
       "0 2019-12-23  Winter Vacation\n",
       "1 2019-12-24  Winter Vacation\n",
       "2 2019-12-26  Winter Vacation\n",
       "3 2019-12-27  Winter Vacation\n",
       "4 2019-12-28  Winter Vacation\n",
       "5 2019-12-29  Winter Vacation\n",
       "6 2019-12-30  Winter Vacation\n",
       "7 2019-12-31  Winter Vacation\n",
       "8 2020-01-02  Winter Vacation\n",
       "9 2020-01-03  Winter Vacation"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get list of dates for winter vacation\n",
    "d = pd.date_range('12/23/2019', '1/3/2020') \n",
    "d1 = list(holidays['Date']) # series\n",
    "\n",
    "# reassign the dates in the Date column\n",
    "d1=[x.date() for x in d1]\n",
    "d2 = []\n",
    "for date in d:\n",
    "    # if the date isn't in d1\n",
    "    if date.date() not in d1:\n",
    "        # add to the empty list d2\n",
    "        d2.append(date.date())\n",
    "# create a new dataframe with that list\n",
    "wdates = pd.DataFrame(d2, columns=['Date'])\n",
    "# and assign a value of Winter Vacation for all the records\n",
    "wdates['Official Name'] = 'Winter Vacation'\n",
    "# convert the Date attribute to datetime format\n",
    "wdates['Date'] = pd.to_datetime(wdates.Date)\n",
    "wdates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Date</th>\n",
       "      <th>Official Name_x</th>\n",
       "      <th>Remarks</th>\n",
       "      <th>Official Name_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>Veterans Day</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-28</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>Christmas</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2019-12-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2019-12-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>New Year's Day</td>\n",
       "      <td>Celebrates beginning of the Gregorian calendar...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>Inauguration Day</td>\n",
       "      <td>Observed only by federal government employees ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>Birthday of Martin Luther King, Jr.</td>\n",
       "      <td>Honors Dr. Martin Luther King, Jr., Civil Righ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>Washington's Birthday</td>\n",
       "      <td>Washington's Birthday was first declared a fed...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-04-06</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-04-07</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-04-08</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-05-25</td>\n",
       "      <td>Memorial Day</td>\n",
       "      <td>Honors the nation's war dead from the Civil Wa...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-07-04</td>\n",
       "      <td>Independence Day</td>\n",
       "      <td>Celebrates the signing of the Declaration of I...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-09-07</td>\n",
       "      <td>Labor Day</td>\n",
       "      <td>Celebrates the achievements of workers and the...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-10-12</td>\n",
       "      <td>Columbus Day</td>\n",
       "      <td>Honors Christopher Columbus, traditional disco...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date                      Official Name_x  \\\n",
       "0  2019-11-11                         Veterans Day   \n",
       "1  2019-11-28                     Thanksgiving Day   \n",
       "16 2019-12-23                                  NaN   \n",
       "17 2019-12-24                                  NaN   \n",
       "2  2019-12-25                            Christmas   \n",
       "18 2019-12-26                                  NaN   \n",
       "19 2019-12-27                                  NaN   \n",
       "20 2019-12-28                                  NaN   \n",
       "21 2019-12-29                                  NaN   \n",
       "22 2019-12-30                                  NaN   \n",
       "23 2019-12-31                                  NaN   \n",
       "3  2020-01-01                       New Year's Day   \n",
       "24 2020-01-02                                  NaN   \n",
       "25 2020-01-03                                  NaN   \n",
       "5  2020-01-20                     Inauguration Day   \n",
       "4  2020-01-20  Birthday of Martin Luther King, Jr.   \n",
       "6  2020-02-17                Washington's Birthday   \n",
       "11 2020-04-06                         Spring Break   \n",
       "12 2020-04-07                         Spring Break   \n",
       "13 2020-04-08                         Spring Break   \n",
       "14 2020-04-09                         Spring Break   \n",
       "15 2020-04-10                         Spring Break   \n",
       "7  2020-05-25                         Memorial Day   \n",
       "8  2020-07-04                     Independence Day   \n",
       "9  2020-09-07                            Labor Day   \n",
       "10 2020-10-12                         Columbus Day   \n",
       "\n",
       "                                              Remarks  Official Name_y  \n",
       "0                                                 NaN              NaN  \n",
       "1                                                 NaN              NaN  \n",
       "16                                                NaN  Winter Vacation  \n",
       "17                                                NaN  Winter Vacation  \n",
       "2                                                 NaN              NaN  \n",
       "18                                                NaN  Winter Vacation  \n",
       "19                                                NaN  Winter Vacation  \n",
       "20                                                NaN  Winter Vacation  \n",
       "21                                                NaN  Winter Vacation  \n",
       "22                                                NaN  Winter Vacation  \n",
       "23                                                NaN  Winter Vacation  \n",
       "3   Celebrates beginning of the Gregorian calendar...              NaN  \n",
       "24                                                NaN  Winter Vacation  \n",
       "25                                                NaN  Winter Vacation  \n",
       "5   Observed only by federal government employees ...              NaN  \n",
       "4   Honors Dr. Martin Luther King, Jr., Civil Righ...              NaN  \n",
       "6   Washington's Birthday was first declared a fed...              NaN  \n",
       "11                                                                 NaN  \n",
       "12                                                                 NaN  \n",
       "13                                                                 NaN  \n",
       "14                                                                 NaN  \n",
       "15                                                                 NaN  \n",
       "7   Honors the nation's war dead from the Civil Wa...              NaN  \n",
       "8   Celebrates the signing of the Declaration of I...              NaN  \n",
       "9   Celebrates the achievements of workers and the...              NaN  \n",
       "10  Honors Christopher Columbus, traditional disco...              NaN  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge the holidays and winter break dates\n",
    "hols = holidays.merge(wdates, how='outer', on=['Date'])\n",
    "# sort by Date in ascending order\n",
    "hols.sort_values(by='Date', inplace=True)\n",
    "hols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Official Name_x</th>\n",
       "      <th>Remarks</th>\n",
       "      <th>Official Name_y</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>Veterans Day</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Veterans Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-28</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>Christmas</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Christmas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2019-12-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2019-12-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>New Year's Day</td>\n",
       "      <td>Celebrates beginning of the Gregorian calendar...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Year's Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>Inauguration Day</td>\n",
       "      <td>Observed only by federal government employees ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Inauguration Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>Birthday of Martin Luther King, Jr.</td>\n",
       "      <td>Honors Dr. Martin Luther King, Jr., Civil Righ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Birthday of Martin Luther King, Jr.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>Washington's Birthday</td>\n",
       "      <td>Washington's Birthday was first declared a fed...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Washington's Birthday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-04-06</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-04-07</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-04-08</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>Spring Break</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-05-25</td>\n",
       "      <td>Memorial Day</td>\n",
       "      <td>Honors the nation's war dead from the Civil Wa...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Memorial Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-07-04</td>\n",
       "      <td>Independence Day</td>\n",
       "      <td>Celebrates the signing of the Declaration of I...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Independence Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-09-07</td>\n",
       "      <td>Labor Day</td>\n",
       "      <td>Celebrates the achievements of workers and the...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Labor Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-10-12</td>\n",
       "      <td>Columbus Day</td>\n",
       "      <td>Honors Christopher Columbus, traditional disco...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Columbus Day</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date                      Official Name_x  \\\n",
       "0  2019-11-11                         Veterans Day   \n",
       "1  2019-11-28                     Thanksgiving Day   \n",
       "16 2019-12-23                                  NaN   \n",
       "17 2019-12-24                                  NaN   \n",
       "2  2019-12-25                            Christmas   \n",
       "18 2019-12-26                                  NaN   \n",
       "19 2019-12-27                                  NaN   \n",
       "20 2019-12-28                                  NaN   \n",
       "21 2019-12-29                                  NaN   \n",
       "22 2019-12-30                                  NaN   \n",
       "23 2019-12-31                                  NaN   \n",
       "3  2020-01-01                       New Year's Day   \n",
       "24 2020-01-02                                  NaN   \n",
       "25 2020-01-03                                  NaN   \n",
       "5  2020-01-20                     Inauguration Day   \n",
       "4  2020-01-20  Birthday of Martin Luther King, Jr.   \n",
       "6  2020-02-17                Washington's Birthday   \n",
       "11 2020-04-06                         Spring Break   \n",
       "12 2020-04-07                         Spring Break   \n",
       "13 2020-04-08                         Spring Break   \n",
       "14 2020-04-09                         Spring Break   \n",
       "15 2020-04-10                         Spring Break   \n",
       "7  2020-05-25                         Memorial Day   \n",
       "8  2020-07-04                     Independence Day   \n",
       "9  2020-09-07                            Labor Day   \n",
       "10 2020-10-12                         Columbus Day   \n",
       "\n",
       "                                              Remarks  Official Name_y  \\\n",
       "0                                                 NaN              NaN   \n",
       "1                                                 NaN              NaN   \n",
       "16                                                NaN  Winter Vacation   \n",
       "17                                                NaN  Winter Vacation   \n",
       "2                                                 NaN              NaN   \n",
       "18                                                NaN  Winter Vacation   \n",
       "19                                                NaN  Winter Vacation   \n",
       "20                                                NaN  Winter Vacation   \n",
       "21                                                NaN  Winter Vacation   \n",
       "22                                                NaN  Winter Vacation   \n",
       "23                                                NaN  Winter Vacation   \n",
       "3   Celebrates beginning of the Gregorian calendar...              NaN   \n",
       "24                                                NaN  Winter Vacation   \n",
       "25                                                NaN  Winter Vacation   \n",
       "5   Observed only by federal government employees ...              NaN   \n",
       "4   Honors Dr. Martin Luther King, Jr., Civil Righ...              NaN   \n",
       "6   Washington's Birthday was first declared a fed...              NaN   \n",
       "11                                                                 NaN   \n",
       "12                                                                 NaN   \n",
       "13                                                                 NaN   \n",
       "14                                                                 NaN   \n",
       "15                                                                 NaN   \n",
       "7   Honors the nation's war dead from the Civil Wa...              NaN   \n",
       "8   Celebrates the signing of the Declaration of I...              NaN   \n",
       "9   Celebrates the achievements of workers and the...              NaN   \n",
       "10  Honors Christopher Columbus, traditional disco...              NaN   \n",
       "\n",
       "                                   Name  \n",
       "0                          Veterans Day  \n",
       "1                      Thanksgiving Day  \n",
       "16                      Winter Vacation  \n",
       "17                      Winter Vacation  \n",
       "2                             Christmas  \n",
       "18                      Winter Vacation  \n",
       "19                      Winter Vacation  \n",
       "20                      Winter Vacation  \n",
       "21                      Winter Vacation  \n",
       "22                      Winter Vacation  \n",
       "23                      Winter Vacation  \n",
       "3                        New Year's Day  \n",
       "24                      Winter Vacation  \n",
       "25                      Winter Vacation  \n",
       "5                      Inauguration Day  \n",
       "4   Birthday of Martin Luther King, Jr.  \n",
       "6                 Washington's Birthday  \n",
       "11                         Spring Break  \n",
       "12                         Spring Break  \n",
       "13                         Spring Break  \n",
       "14                         Spring Break  \n",
       "15                         Spring Break  \n",
       "7                          Memorial Day  \n",
       "8                      Independence Day  \n",
       "9                             Labor Day  \n",
       "10                         Columbus Day  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a new column Name and assgn it h Official Name_x, if NaN, assign with Official Name_y\n",
    "hols['Name']=hols['Official Name_x'].fillna(hols['Official Name_y'])\n",
    "hols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Date</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>Veterans Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-28</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>Christmas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019-12-26</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2019-12-27</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2019-12-29</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>New Year's Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>Winter Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>Inauguration Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-20</td>\n",
       "      <td>Birthday of Martin Luther King, Jr.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-02-17</td>\n",
       "      <td>Washington's Birthday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020-04-06</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2020-04-07</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2020-04-08</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2020-04-09</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2020-04-10</td>\n",
       "      <td>Spring Break</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-05-25</td>\n",
       "      <td>Memorial Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-07-04</td>\n",
       "      <td>Independence Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-09-07</td>\n",
       "      <td>Labor Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2020-10-12</td>\n",
       "      <td>Columbus Day</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date                                 Name\n",
       "0  2019-11-11                         Veterans Day\n",
       "1  2019-11-28                     Thanksgiving Day\n",
       "16 2019-12-23                      Winter Vacation\n",
       "17 2019-12-24                      Winter Vacation\n",
       "2  2019-12-25                            Christmas\n",
       "18 2019-12-26                      Winter Vacation\n",
       "19 2019-12-27                      Winter Vacation\n",
       "20 2019-12-28                      Winter Vacation\n",
       "21 2019-12-29                      Winter Vacation\n",
       "22 2019-12-30                      Winter Vacation\n",
       "23 2019-12-31                      Winter Vacation\n",
       "3  2020-01-01                       New Year's Day\n",
       "24 2020-01-02                      Winter Vacation\n",
       "25 2020-01-03                      Winter Vacation\n",
       "5  2020-01-20                     Inauguration Day\n",
       "4  2020-01-20  Birthday of Martin Luther King, Jr.\n",
       "6  2020-02-17                Washington's Birthday\n",
       "11 2020-04-06                         Spring Break\n",
       "12 2020-04-07                         Spring Break\n",
       "13 2020-04-08                         Spring Break\n",
       "14 2020-04-09                         Spring Break\n",
       "15 2020-04-10                         Spring Break\n",
       "7  2020-05-25                         Memorial Day\n",
       "8  2020-07-04                     Independence Day\n",
       "9  2020-09-07                            Labor Day\n",
       "10 2020-10-12                         Columbus Day"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove the unecessary columns from the dataframe\n",
    "hols.drop('Official Name_y', axis=1, inplace=True)\n",
    "hols.drop('Remarks', axis=1, inplace=True)\n",
    "hols.drop('Official Name_x', axis=1, inplace=True)\n",
    "hols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Date</th>\n",
       "      <th>Name</th>\n",
       "      <th>Official Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>Veterans Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-28</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>Christmas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2020-09-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2020-09-07</td>\n",
       "      <td>Labor Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2020-10-12</td>\n",
       "      <td>Columbus Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>103 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date              Name    Official Name\n",
       "0   2019-11-11      Veterans Day              NaN\n",
       "1   2019-11-28  Thanksgiving Day              NaN\n",
       "2   2019-12-23   Winter Vacation              NaN\n",
       "3   2019-12-24   Winter Vacation              NaN\n",
       "4   2019-12-25         Christmas              NaN\n",
       "..         ...               ...              ...\n",
       "100 2020-09-01               NaN  Summer Vacation\n",
       "101 2020-09-02               NaN  Summer Vacation\n",
       "102 2020-09-03               NaN  Summer Vacation\n",
       "24  2020-09-07         Labor Day              NaN\n",
       "25  2020-10-12      Columbus Day              NaN\n",
       "\n",
       "[103 rows x 3 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge the merged dataframe with the summer dates\n",
    "hols = hols.merge(sdates, how='outer', on=['Date'])\n",
    "# sort by Date in ascending order\n",
    "hols.sort_values(by='Date', inplace=True)\n",
    "hols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Date</th>\n",
       "      <th>Name</th>\n",
       "      <th>Official Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>Veterans Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-28</td>\n",
       "      <td>Thanksgiving Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-12-24</td>\n",
       "      <td>Winter Vacation</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-12-25</td>\n",
       "      <td>Christmas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>Summer Vacation</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2020-09-02</td>\n",
       "      <td>Summer Vacation</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2020-09-03</td>\n",
       "      <td>Summer Vacation</td>\n",
       "      <td>Summer Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2020-09-07</td>\n",
       "      <td>Labor Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2020-10-12</td>\n",
       "      <td>Columbus Day</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>103 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date              Name    Official Name\n",
       "0   2019-11-11      Veterans Day              NaN\n",
       "1   2019-11-28  Thanksgiving Day              NaN\n",
       "2   2019-12-23   Winter Vacation              NaN\n",
       "3   2019-12-24   Winter Vacation              NaN\n",
       "4   2019-12-25         Christmas              NaN\n",
       "..         ...               ...              ...\n",
       "100 2020-09-01   Summer Vacation  Summer Vacation\n",
       "101 2020-09-02   Summer Vacation  Summer Vacation\n",
       "102 2020-09-03   Summer Vacation  Summer Vacation\n",
       "24  2020-09-07         Labor Day              NaN\n",
       "25  2020-10-12      Columbus Day              NaN\n",
       "\n",
       "[103 rows x 3 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# assign the Name column with values from the Name column, if NaN, fill with values from Official Name\n",
    "hols['Name']=hols['Name'].fillna(hols['Official Name'])\n",
    "hols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop Official Name from the dataframe\n",
    "hols.drop('Official Name', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the results into a csv file\n",
    "hols.to_csv('hols.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Date</th>\n",
       "      <th>Official Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-02</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-11-03</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>289</th>\n",
       "      <td>2020-11-26</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290</th>\n",
       "      <td>2020-11-27</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>2020-11-28</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>2020-11-29</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>2020-11-30</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>294 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Official Name\n",
       "0   2019-11-01   Not Holiday\n",
       "1   2019-11-02   Not Holiday\n",
       "2   2019-11-03   Not Holiday\n",
       "3   2019-11-04   Not Holiday\n",
       "4   2019-11-05   Not Holiday\n",
       "..         ...           ...\n",
       "289 2020-11-26   Not Holiday\n",
       "290 2020-11-27   Not Holiday\n",
       "291 2020-11-28   Not Holiday\n",
       "292 2020-11-29   Not Holiday\n",
       "293 2020-11-30   Not Holiday\n",
       "\n",
       "[294 rows x 2 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the list of dates from 11/1/2019 to 11/30/2019\n",
    "d = pd.date_range('11/1/2019', '11/30/2020')\n",
    "d1 = list(hols['Date'])\n",
    "# reassign dates for the Date column\n",
    "d1 = [x.date() for x in d1]\n",
    "d3 = list(wdates['Date']) \n",
    "# reassign dates for the Date column in d3\n",
    "d3=[x.date() for x in d3]\n",
    "d2 = []\n",
    "for date in d:\n",
    "    # if the date is not in the merged data as well as not in the winter dates\n",
    "    if date.date() not in d1 and date.date() not in d3:\n",
    "        # add the date to the empty list d2\n",
    "        d2.append(date.date())\n",
    "# create a dataframe with d2 as the Date column\n",
    "dates = pd.DataFrame(d2, columns=['Date'])\n",
    "# if no name, assign as Not Holiday\n",
    "dates['Official Name'] = 'Not Holiday'\n",
    "# convert the Date attribute to datetime format\n",
    "dates['Date'] = pd.to_datetime(dates.Date)\n",
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Date</th>\n",
       "      <th>Official Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-11-02</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-11-03</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>289</th>\n",
       "      <td>2020-11-26</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290</th>\n",
       "      <td>2020-11-27</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>2020-11-28</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>2020-11-29</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>2020-11-30</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>294 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Official Name\n",
       "0   2019-11-01   Not Holiday\n",
       "1   2019-11-02       Weekend\n",
       "2   2019-11-03       Weekend\n",
       "3   2019-11-04   Not Holiday\n",
       "4   2019-11-05   Not Holiday\n",
       "..         ...           ...\n",
       "289 2020-11-26   Not Holiday\n",
       "290 2020-11-27   Not Holiday\n",
       "291 2020-11-28       Weekend\n",
       "292 2020-11-29       Weekend\n",
       "293 2020-11-30   Not Holiday\n",
       "\n",
       "[294 rows x 2 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates.Date = pd.to_datetime(dates.Date)\n",
    "\n",
    "# check if the day is a weekend, if so, rerwite its value to Weekend\n",
    "dates['Official Name']=np.where(dates.Date.dt.weekday > 4 , 'Weekend', 'Not Holiday')\n",
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Date</th>\n",
       "      <th>Name</th>\n",
       "      <th>Official Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>2019-11-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2019-11-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>2020-11-26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>2020-11-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>2020-11-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>2020-11-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>2020-11-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>397 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Name Official Name\n",
       "103 2019-11-01  NaN   Not Holiday\n",
       "104 2019-11-02  NaN       Weekend\n",
       "105 2019-11-03  NaN       Weekend\n",
       "106 2019-11-04  NaN   Not Holiday\n",
       "107 2019-11-05  NaN   Not Holiday\n",
       "..         ...  ...           ...\n",
       "392 2020-11-26  NaN   Not Holiday\n",
       "393 2020-11-27  NaN   Not Holiday\n",
       "394 2020-11-28  NaN       Weekend\n",
       "395 2020-11-29  NaN       Weekend\n",
       "396 2020-11-30  NaN   Not Holiday\n",
       "\n",
       "[397 rows x 3 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hols.Date = pd.to_datetime(hols.Date)\n",
    "dates.Date = pd.to_datetime(dates.Date)\n",
    "\n",
    "# merge the previously merged dataset with the dates dataset\n",
    "hol = hols.merge(dates, how='outer', on=['Date'])\n",
    "# and sort by Date in ascending order\n",
    "hol.sort_values(by='Date', inplace=True)\n",
    "hol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new column Holiday_Name and assign Official Name values to that column, if NaN, assign the corresponding values from the Name column\n",
    "hol['Holiday_Name']=hol['Official Name'].fillna(hol['Name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the unnecessary columns\n",
    "hol.drop('Official Name', axis=1, inplace=True)\n",
    "hol.drop('Name', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Date</th>\n",
       "      <th>Holiday_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>2019-11-02</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2019-11-03</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>2020-11-26</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>2020-11-27</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>2020-11-28</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>2020-11-29</td>\n",
       "      <td>Weekend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>2020-11-30</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>397 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Holiday_Name\n",
       "103 2019-11-01  Not Holiday\n",
       "104 2019-11-02      Weekend\n",
       "105 2019-11-03      Weekend\n",
       "106 2019-11-04  Not Holiday\n",
       "107 2019-11-05  Not Holiday\n",
       "..         ...          ...\n",
       "392 2020-11-26  Not Holiday\n",
       "393 2020-11-27  Not Holiday\n",
       "394 2020-11-28      Weekend\n",
       "395 2020-11-29      Weekend\n",
       "396 2020-11-30  Not Holiday\n",
       "\n",
       "[397 rows x 2 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Holiday_Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>2019-11-02</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2019-11-03</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>2020-11-26</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>2020-11-27</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>2020-11-28</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>2020-11-29</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>2020-11-30</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>397 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Holiday_Name  Year  Month  Day\n",
       "103 2019-11-01  Not Holiday  2019     11    1\n",
       "104 2019-11-02      Weekend  2019     11    2\n",
       "105 2019-11-03      Weekend  2019     11    3\n",
       "106 2019-11-04  Not Holiday  2019     11    4\n",
       "107 2019-11-05  Not Holiday  2019     11    5\n",
       "..         ...          ...   ...    ...  ...\n",
       "392 2020-11-26  Not Holiday  2020     11   26\n",
       "393 2020-11-27  Not Holiday  2020     11   27\n",
       "394 2020-11-28      Weekend  2020     11   28\n",
       "395 2020-11-29      Weekend  2020     11   29\n",
       "396 2020-11-30  Not Holiday  2020     11   30\n",
       "\n",
       "[397 rows x 5 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract the Year, Month and Day columns and create them as separate columns\n",
    "hol['Year'] = hol.Date.dt.year\n",
    "hol['Month'] = hol.Date.dt.month\n",
    "hol['Day'] = hol.Date.dt.day\n",
    "hol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>index</th>\n",
       "      <th>Date</th>\n",
       "      <th>Holiday_Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Weekend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103</td>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>104</td>\n",
       "      <td>2019-11-02</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>105</td>\n",
       "      <td>2019-11-03</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>106</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>107</td>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>392</td>\n",
       "      <td>2020-11-26</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>393</td>\n",
       "      <td>2020-11-27</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>394</td>\n",
       "      <td>2020-11-28</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>395</td>\n",
       "      <td>2020-11-29</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>396</td>\n",
       "      <td>2020-11-30</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>397 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index       Date Holiday_Name  Year  Month  Day  Weekend\n",
       "0      103 2019-11-01  Not Holiday  2019     11    1        0\n",
       "1      104 2019-11-02      Weekend  2019     11    2        1\n",
       "2      105 2019-11-03      Weekend  2019     11    3        1\n",
       "3      106 2019-11-04  Not Holiday  2019     11    4        0\n",
       "4      107 2019-11-05  Not Holiday  2019     11    5        0\n",
       "..     ...        ...          ...   ...    ...  ...      ...\n",
       "392    392 2020-11-26  Not Holiday  2020     11   26        0\n",
       "393    393 2020-11-27  Not Holiday  2020     11   27        0\n",
       "394    394 2020-11-28      Weekend  2020     11   28        1\n",
       "395    395 2020-11-29      Weekend  2020     11   29        1\n",
       "396    396 2020-11-30  Not Holiday  2020     11   30        0\n",
       "\n",
       "[397 rows x 7 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check for weekend in the final dataset\n",
    "hol['Weekend'] = np.where(hol.Date.dt.weekday > 4, 1, 0)\n",
    "# reset the index to start from 0\n",
    "hol.reset_index(inplace=True)\n",
    "hol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>index</th>\n",
       "      <th>Date</th>\n",
       "      <th>Holiday_Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Weekend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103</td>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>104</td>\n",
       "      <td>2019-11-02</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>105</td>\n",
       "      <td>2019-11-03</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>106</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>107</td>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>392</td>\n",
       "      <td>2020-11-26</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>393</td>\n",
       "      <td>2020-11-27</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>394</td>\n",
       "      <td>2020-11-28</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>395</td>\n",
       "      <td>2020-11-29</td>\n",
       "      <td>Weekend</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>396</td>\n",
       "      <td>2020-11-30</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>2020</td>\n",
       "      <td>11</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>397 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index       Date Holiday_Name  Year  Month  Day  Weekend\n",
       "0      103 2019-11-01  Not Holiday  2019     11    1        0\n",
       "1      104 2019-11-02      Weekend  2019     11    2        1\n",
       "2      105 2019-11-03      Weekend  2019     11    3        1\n",
       "3      106 2019-11-04  Not Holiday  2019     11    4        0\n",
       "4      107 2019-11-05  Not Holiday  2019     11    5        0\n",
       "..     ...        ...          ...   ...    ...  ...      ...\n",
       "392    392 2020-11-26  Not Holiday  2020     11   26        0\n",
       "393    393 2020-11-27  Not Holiday  2020     11   27        0\n",
       "394    394 2020-11-28      Weekend  2020     11   28        1\n",
       "395    395 2020-11-29      Weekend  2020     11   29        1\n",
       "396    396 2020-11-30  Not Holiday  2020     11   30        0\n",
       "\n",
       "[397 rows x 7 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# prevents overwriting of existing holidays\n",
    "for i in range(len(hol['Date'])):\n",
    "    if hol['Weekend'][i] == 1:\n",
    "        if hol['Holiday_Name'][i] == \"Not Holiday\":\n",
    "            hol['Holiday_Name'][i] = 'Weekend'\n",
    "hol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the dataset to a csv\n",
    "hol.to_csv('holidays_final.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>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>member_casual</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>...</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Wind Gust</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Condition</th>\n",
       "      <th>index</th>\n",
       "      <th>Holiday_Name</th>\n",
       "      <th>Weekend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25612325</td>\n",
       "      <td>2019-11-01 00:00:45</td>\n",
       "      <td>2019-11-01 00:08:10</td>\n",
       "      <td>Ellis Ave &amp; 55th St</td>\n",
       "      <td>420.0</td>\n",
       "      <td>Harper Ave &amp; 59th St</td>\n",
       "      <td>425.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25612326</td>\n",
       "      <td>2019-11-01 00:01:13</td>\n",
       "      <td>2019-11-01 00:30:09</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>Morgan St &amp; Polk St</td>\n",
       "      <td>241.0</td>\n",
       "      <td>Customer</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25612327</td>\n",
       "      <td>2019-11-01 00:01:42</td>\n",
       "      <td>2019-11-01 00:38:32</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>May St &amp; Taylor St</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25612328</td>\n",
       "      <td>2019-11-01 00:02:24</td>\n",
       "      <td>2019-11-01 00:09:27</td>\n",
       "      <td>Lincoln Ave &amp; Winona St</td>\n",
       "      <td>472.0</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>455.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25612329</td>\n",
       "      <td>2019-11-01 00:02:30</td>\n",
       "      <td>2019-11-01 00:04:09</td>\n",
       "      <td>Clinton St &amp; Madison St</td>\n",
       "      <td>77.0</td>\n",
       "      <td>Clinton St &amp; Washington Blvd</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807053</th>\n",
       "      <td>A4D50CAC9CCFB180</td>\n",
       "      <td>2020-10-20 01:51:54</td>\n",
       "      <td>2020-10-20 01:59:11</td>\n",
       "      <td>Kimbark Ave &amp; 53rd St</td>\n",
       "      <td>322.0</td>\n",
       "      <td>Blackstone Ave &amp; Hyde Park Blvd</td>\n",
       "      <td>121.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>9mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807054</th>\n",
       "      <td>2D551117FD01DCE1</td>\n",
       "      <td>2020-10-20 01:04:29</td>\n",
       "      <td>2020-10-20 01:19:33</td>\n",
       "      <td>Pine Grove Ave &amp; Irving Park Rd</td>\n",
       "      <td>254.0</td>\n",
       "      <td>Glenwood Ave &amp; Morse Ave</td>\n",
       "      <td>447.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807055</th>\n",
       "      <td>2D551117FD01DCE1</td>\n",
       "      <td>2020-10-20 01:04:29</td>\n",
       "      <td>2020-10-20 01:19:33</td>\n",
       "      <td>Pine Grove Ave &amp; Irving Park Rd</td>\n",
       "      <td>254.0</td>\n",
       "      <td>Glenwood Ave &amp; Morse Ave</td>\n",
       "      <td>447.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>9mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807056</th>\n",
       "      <td>09264F6A1F8E4CEE</td>\n",
       "      <td>2020-10-20 01:38:07</td>\n",
       "      <td>2020-10-20 01:48:04</td>\n",
       "      <td>St. Clair St &amp; Erie St</td>\n",
       "      <td>211.0</td>\n",
       "      <td>Clark St &amp; Armitage Ave</td>\n",
       "      <td>94.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807057</th>\n",
       "      <td>09264F6A1F8E4CEE</td>\n",
       "      <td>2020-10-20 01:38:07</td>\n",
       "      <td>2020-10-20 01:48:04</td>\n",
       "      <td>St. Clair St &amp; Erie St</td>\n",
       "      <td>211.0</td>\n",
       "      <td>Clark St &amp; Armitage Ave</td>\n",
       "      <td>94.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>9mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3807058 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ride_id          started_at            ended_at  \\\n",
       "0                25612325 2019-11-01 00:00:45 2019-11-01 00:08:10   \n",
       "1                25612326 2019-11-01 00:01:13 2019-11-01 00:30:09   \n",
       "2                25612327 2019-11-01 00:01:42 2019-11-01 00:38:32   \n",
       "3                25612328 2019-11-01 00:02:24 2019-11-01 00:09:27   \n",
       "4                25612329 2019-11-01 00:02:30 2019-11-01 00:04:09   \n",
       "...                   ...                 ...                 ...   \n",
       "3807053  A4D50CAC9CCFB180 2020-10-20 01:51:54 2020-10-20 01:59:11   \n",
       "3807054  2D551117FD01DCE1 2020-10-20 01:04:29 2020-10-20 01:19:33   \n",
       "3807055  2D551117FD01DCE1 2020-10-20 01:04:29 2020-10-20 01:19:33   \n",
       "3807056  09264F6A1F8E4CEE 2020-10-20 01:38:07 2020-10-20 01:48:04   \n",
       "3807057  09264F6A1F8E4CEE 2020-10-20 01:38:07 2020-10-20 01:48:04   \n",
       "\n",
       "                      start_station_name  start_station_id  \\\n",
       "0                    Ellis Ave & 55th St             420.0   \n",
       "1            Western Ave & Winnebago Ave             116.0   \n",
       "2            Western Ave & Winnebago Ave             116.0   \n",
       "3                Lincoln Ave & Winona St             472.0   \n",
       "4                Clinton St & Madison St              77.0   \n",
       "...                                  ...               ...   \n",
       "3807053            Kimbark Ave & 53rd St             322.0   \n",
       "3807054  Pine Grove Ave & Irving Park Rd             254.0   \n",
       "3807055  Pine Grove Ave & Irving Park Rd             254.0   \n",
       "3807056           St. Clair St & Erie St             211.0   \n",
       "3807057           St. Clair St & Erie St             211.0   \n",
       "\n",
       "                        end_station_name  end_station_id member_casual  Year  \\\n",
       "0                   Harper Ave & 59th St           425.0    Subscriber  2019   \n",
       "1                    Morgan St & Polk St           241.0      Customer  2019   \n",
       "2                     May St & Taylor St            22.0    Subscriber  2019   \n",
       "3           Maplewood Ave & Peterson Ave           455.0    Subscriber  2019   \n",
       "4           Clinton St & Washington Blvd            91.0    Subscriber  2019   \n",
       "...                                  ...             ...           ...   ...   \n",
       "3807053  Blackstone Ave & Hyde Park Blvd           121.0        member  2020   \n",
       "3807054         Glenwood Ave & Morse Ave           447.0        member  2020   \n",
       "3807055         Glenwood Ave & Morse Ave           447.0        member  2020   \n",
       "3807056          Clark St & Armitage Ave            94.0        member  2020   \n",
       "3807057          Clark St & Armitage Ave            94.0        member  2020   \n",
       "\n",
       "         Month  ...  Hour  Temperature Humidity  Wind Speed Wind Gust  \\\n",
       "0           11  ...     0        26  F       81       8 mph     0 mph   \n",
       "1           11  ...     0        26  F       81       8 mph     0 mph   \n",
       "2           11  ...     0        26  F       81       8 mph     0 mph   \n",
       "3           11  ...     0        26  F       81       8 mph     0 mph   \n",
       "4           11  ...     0        26  F       81       8 mph     0 mph   \n",
       "...        ...  ...   ...          ...      ...         ...       ...   \n",
       "3807053     10  ...     1         44 F       79        9mph      0mph   \n",
       "3807054     10  ...     1         44 F       79        8mph      0mph   \n",
       "3807055     10  ...     1         44 F       79        9mph      0mph   \n",
       "3807056     10  ...     1         44 F       79        8mph      0mph   \n",
       "3807057     10  ...     1         44 F       79        9mph      0mph   \n",
       "\n",
       "        Precipitation Condition index  Holiday_Name Weekend  \n",
       "0              0.0 in      Fair   103   Not Holiday       0  \n",
       "1              0.0 in      Fair   103   Not Holiday       0  \n",
       "2              0.0 in      Fair   103   Not Holiday       0  \n",
       "3              0.0 in      Fair   103   Not Holiday       0  \n",
       "4              0.0 in      Fair   103   Not Holiday       0  \n",
       "...               ...       ...   ...           ...     ...  \n",
       "3807053         0.0in    Cloudy   355   Not Holiday       0  \n",
       "3807054         0.0in    Cloudy   355   Not Holiday       0  \n",
       "3807055         0.0in    Cloudy   355   Not Holiday       0  \n",
       "3807056         0.0in    Cloudy   355   Not Holiday       0  \n",
       "3807057         0.0in    Cloudy   355   Not Holiday       0  \n",
       "\n",
       "[3807058 rows x 21 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge the final holidays dataset with the divvy + weather data\n",
    "final_merged_data = merged_df.merge(hol, how='inner', on=['Year', 'Month', 'Day'])\n",
    "# and drop the Date column\n",
    "final_merged_data.drop(\"Date\", axis=1, inplace=True)\n",
    "final_merged_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the final resulting dataframe to a csv (serves as the unclean merged data)\n",
    "final_merged_data.to_csv(\"final_merged_data.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the Weekend attribute\n",
    "final_merged_data.drop(\"Weekend\", axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3807058, 20)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_merged_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>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>member_casual</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Wind Gust</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Condition</th>\n",
       "      <th>index</th>\n",
       "      <th>Holiday_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25612325</td>\n",
       "      <td>2019-11-01 00:00:45</td>\n",
       "      <td>2019-11-01 00:08:10</td>\n",
       "      <td>Ellis Ave &amp; 55th St</td>\n",
       "      <td>420.0</td>\n",
       "      <td>Harper Ave &amp; 59th St</td>\n",
       "      <td>425.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25612326</td>\n",
       "      <td>2019-11-01 00:01:13</td>\n",
       "      <td>2019-11-01 00:30:09</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>Morgan St &amp; Polk St</td>\n",
       "      <td>241.0</td>\n",
       "      <td>Customer</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25612327</td>\n",
       "      <td>2019-11-01 00:01:42</td>\n",
       "      <td>2019-11-01 00:38:32</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>May St &amp; Taylor St</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25612328</td>\n",
       "      <td>2019-11-01 00:02:24</td>\n",
       "      <td>2019-11-01 00:09:27</td>\n",
       "      <td>Lincoln Ave &amp; Winona St</td>\n",
       "      <td>472.0</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>455.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25612329</td>\n",
       "      <td>2019-11-01 00:02:30</td>\n",
       "      <td>2019-11-01 00:04:09</td>\n",
       "      <td>Clinton St &amp; Madison St</td>\n",
       "      <td>77.0</td>\n",
       "      <td>Clinton St &amp; Washington Blvd</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807053</th>\n",
       "      <td>A4D50CAC9CCFB180</td>\n",
       "      <td>2020-10-20 01:51:54</td>\n",
       "      <td>2020-10-20 01:59:11</td>\n",
       "      <td>Kimbark Ave &amp; 53rd St</td>\n",
       "      <td>322.0</td>\n",
       "      <td>Blackstone Ave &amp; Hyde Park Blvd</td>\n",
       "      <td>121.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>9mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807054</th>\n",
       "      <td>2D551117FD01DCE1</td>\n",
       "      <td>2020-10-20 01:04:29</td>\n",
       "      <td>2020-10-20 01:19:33</td>\n",
       "      <td>Pine Grove Ave &amp; Irving Park Rd</td>\n",
       "      <td>254.0</td>\n",
       "      <td>Glenwood Ave &amp; Morse Ave</td>\n",
       "      <td>447.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807055</th>\n",
       "      <td>2D551117FD01DCE1</td>\n",
       "      <td>2020-10-20 01:04:29</td>\n",
       "      <td>2020-10-20 01:19:33</td>\n",
       "      <td>Pine Grove Ave &amp; Irving Park Rd</td>\n",
       "      <td>254.0</td>\n",
       "      <td>Glenwood Ave &amp; Morse Ave</td>\n",
       "      <td>447.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>9mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807056</th>\n",
       "      <td>09264F6A1F8E4CEE</td>\n",
       "      <td>2020-10-20 01:38:07</td>\n",
       "      <td>2020-10-20 01:48:04</td>\n",
       "      <td>St. Clair St &amp; Erie St</td>\n",
       "      <td>211.0</td>\n",
       "      <td>Clark St &amp; Armitage Ave</td>\n",
       "      <td>94.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807057</th>\n",
       "      <td>09264F6A1F8E4CEE</td>\n",
       "      <td>2020-10-20 01:38:07</td>\n",
       "      <td>2020-10-20 01:48:04</td>\n",
       "      <td>St. Clair St &amp; Erie St</td>\n",
       "      <td>211.0</td>\n",
       "      <td>Clark St &amp; Armitage Ave</td>\n",
       "      <td>94.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>9mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3807058 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ride_id          started_at            ended_at  \\\n",
       "0                25612325 2019-11-01 00:00:45 2019-11-01 00:08:10   \n",
       "1                25612326 2019-11-01 00:01:13 2019-11-01 00:30:09   \n",
       "2                25612327 2019-11-01 00:01:42 2019-11-01 00:38:32   \n",
       "3                25612328 2019-11-01 00:02:24 2019-11-01 00:09:27   \n",
       "4                25612329 2019-11-01 00:02:30 2019-11-01 00:04:09   \n",
       "...                   ...                 ...                 ...   \n",
       "3807053  A4D50CAC9CCFB180 2020-10-20 01:51:54 2020-10-20 01:59:11   \n",
       "3807054  2D551117FD01DCE1 2020-10-20 01:04:29 2020-10-20 01:19:33   \n",
       "3807055  2D551117FD01DCE1 2020-10-20 01:04:29 2020-10-20 01:19:33   \n",
       "3807056  09264F6A1F8E4CEE 2020-10-20 01:38:07 2020-10-20 01:48:04   \n",
       "3807057  09264F6A1F8E4CEE 2020-10-20 01:38:07 2020-10-20 01:48:04   \n",
       "\n",
       "                      start_station_name  start_station_id  \\\n",
       "0                    Ellis Ave & 55th St             420.0   \n",
       "1            Western Ave & Winnebago Ave             116.0   \n",
       "2            Western Ave & Winnebago Ave             116.0   \n",
       "3                Lincoln Ave & Winona St             472.0   \n",
       "4                Clinton St & Madison St              77.0   \n",
       "...                                  ...               ...   \n",
       "3807053            Kimbark Ave & 53rd St             322.0   \n",
       "3807054  Pine Grove Ave & Irving Park Rd             254.0   \n",
       "3807055  Pine Grove Ave & Irving Park Rd             254.0   \n",
       "3807056           St. Clair St & Erie St             211.0   \n",
       "3807057           St. Clair St & Erie St             211.0   \n",
       "\n",
       "                        end_station_name  end_station_id member_casual  Year  \\\n",
       "0                   Harper Ave & 59th St           425.0    Subscriber  2019   \n",
       "1                    Morgan St & Polk St           241.0      Customer  2019   \n",
       "2                     May St & Taylor St            22.0    Subscriber  2019   \n",
       "3           Maplewood Ave & Peterson Ave           455.0    Subscriber  2019   \n",
       "4           Clinton St & Washington Blvd            91.0    Subscriber  2019   \n",
       "...                                  ...             ...           ...   ...   \n",
       "3807053  Blackstone Ave & Hyde Park Blvd           121.0        member  2020   \n",
       "3807054         Glenwood Ave & Morse Ave           447.0        member  2020   \n",
       "3807055         Glenwood Ave & Morse Ave           447.0        member  2020   \n",
       "3807056          Clark St & Armitage Ave            94.0        member  2020   \n",
       "3807057          Clark St & Armitage Ave            94.0        member  2020   \n",
       "\n",
       "         Month  Day  Hour Temperature  Humidity Wind Speed Wind Gust  \\\n",
       "0           11    1     0       26  F        81      8 mph     0 mph   \n",
       "1           11    1     0       26  F        81      8 mph     0 mph   \n",
       "2           11    1     0       26  F        81      8 mph     0 mph   \n",
       "3           11    1     0       26  F        81      8 mph     0 mph   \n",
       "4           11    1     0       26  F        81      8 mph     0 mph   \n",
       "...        ...  ...   ...         ...       ...        ...       ...   \n",
       "3807053     10   20     1        44 F        79       9mph      0mph   \n",
       "3807054     10   20     1        44 F        79       8mph      0mph   \n",
       "3807055     10   20     1        44 F        79       9mph      0mph   \n",
       "3807056     10   20     1        44 F        79       8mph      0mph   \n",
       "3807057     10   20     1        44 F        79       9mph      0mph   \n",
       "\n",
       "        Precipitation Condition  index Holiday_Name  \n",
       "0              0.0 in      Fair    103  Not Holiday  \n",
       "1              0.0 in      Fair    103  Not Holiday  \n",
       "2              0.0 in      Fair    103  Not Holiday  \n",
       "3              0.0 in      Fair    103  Not Holiday  \n",
       "4              0.0 in      Fair    103  Not Holiday  \n",
       "...               ...       ...    ...          ...  \n",
       "3807053         0.0in    Cloudy    355  Not Holiday  \n",
       "3807054         0.0in    Cloudy    355  Not Holiday  \n",
       "3807055         0.0in    Cloudy    355  Not Holiday  \n",
       "3807056         0.0in    Cloudy    355  Not Holiday  \n",
       "3807057         0.0in    Cloudy    355  Not Holiday  \n",
       "\n",
       "[3807058 rows x 20 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_merged_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of duplicate rows:  325240\n",
      "Number of rows with missing values:  106490\n"
     ]
    }
   ],
   "source": [
    "# count number of duplicate rows and missing values in the dataframe\n",
    "print(\"Number of duplicate rows: \", len(final_merged_data) - len(final_merged_data.drop_duplicates(subset='ride_id', keep='first')))\n",
    "print(\"Number of rows with missing values: \", len(final_merged_data) - len(final_merged_data.dropna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ride_id</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>member_casual</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Wind Gust</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Condition</th>\n",
       "      <th>index</th>\n",
       "      <th>Holiday_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25612325</td>\n",
       "      <td>2019-11-01 00:00:45</td>\n",
       "      <td>2019-11-01 00:08:10</td>\n",
       "      <td>Ellis Ave &amp; 55th St</td>\n",
       "      <td>420.0</td>\n",
       "      <td>Harper Ave &amp; 59th St</td>\n",
       "      <td>425.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25612326</td>\n",
       "      <td>2019-11-01 00:01:13</td>\n",
       "      <td>2019-11-01 00:30:09</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>Morgan St &amp; Polk St</td>\n",
       "      <td>241.0</td>\n",
       "      <td>Customer</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25612327</td>\n",
       "      <td>2019-11-01 00:01:42</td>\n",
       "      <td>2019-11-01 00:38:32</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>May St &amp; Taylor St</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25612328</td>\n",
       "      <td>2019-11-01 00:02:24</td>\n",
       "      <td>2019-11-01 00:09:27</td>\n",
       "      <td>Lincoln Ave &amp; Winona St</td>\n",
       "      <td>472.0</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>455.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25612329</td>\n",
       "      <td>2019-11-01 00:02:30</td>\n",
       "      <td>2019-11-01 00:04:09</td>\n",
       "      <td>Clinton St &amp; Madison St</td>\n",
       "      <td>77.0</td>\n",
       "      <td>Clinton St &amp; Washington Blvd</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807048</th>\n",
       "      <td>23638F49B7E7D116</td>\n",
       "      <td>2020-10-20 01:23:37</td>\n",
       "      <td>2020-10-20 01:39:30</td>\n",
       "      <td>Racine Ave &amp; 35th St</td>\n",
       "      <td>367.0</td>\n",
       "      <td>Racine Ave &amp; 18th St</td>\n",
       "      <td>15.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807050</th>\n",
       "      <td>3048BAC5F769B4DD</td>\n",
       "      <td>2020-10-20 01:20:30</td>\n",
       "      <td>2020-10-20 01:36:44</td>\n",
       "      <td>Wells St &amp; Evergreen Ave</td>\n",
       "      <td>291.0</td>\n",
       "      <td>Wabash Ave &amp; Roosevelt Rd</td>\n",
       "      <td>59.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807052</th>\n",
       "      <td>A4D50CAC9CCFB180</td>\n",
       "      <td>2020-10-20 01:51:54</td>\n",
       "      <td>2020-10-20 01:59:11</td>\n",
       "      <td>Kimbark Ave &amp; 53rd St</td>\n",
       "      <td>322.0</td>\n",
       "      <td>Blackstone Ave &amp; Hyde Park Blvd</td>\n",
       "      <td>121.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807054</th>\n",
       "      <td>2D551117FD01DCE1</td>\n",
       "      <td>2020-10-20 01:04:29</td>\n",
       "      <td>2020-10-20 01:19:33</td>\n",
       "      <td>Pine Grove Ave &amp; Irving Park Rd</td>\n",
       "      <td>254.0</td>\n",
       "      <td>Glenwood Ave &amp; Morse Ave</td>\n",
       "      <td>447.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807056</th>\n",
       "      <td>09264F6A1F8E4CEE</td>\n",
       "      <td>2020-10-20 01:38:07</td>\n",
       "      <td>2020-10-20 01:48:04</td>\n",
       "      <td>St. Clair St &amp; Erie St</td>\n",
       "      <td>211.0</td>\n",
       "      <td>Clark St &amp; Armitage Ave</td>\n",
       "      <td>94.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3481818 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ride_id          started_at            ended_at  \\\n",
       "0                25612325 2019-11-01 00:00:45 2019-11-01 00:08:10   \n",
       "1                25612326 2019-11-01 00:01:13 2019-11-01 00:30:09   \n",
       "2                25612327 2019-11-01 00:01:42 2019-11-01 00:38:32   \n",
       "3                25612328 2019-11-01 00:02:24 2019-11-01 00:09:27   \n",
       "4                25612329 2019-11-01 00:02:30 2019-11-01 00:04:09   \n",
       "...                   ...                 ...                 ...   \n",
       "3807048  23638F49B7E7D116 2020-10-20 01:23:37 2020-10-20 01:39:30   \n",
       "3807050  3048BAC5F769B4DD 2020-10-20 01:20:30 2020-10-20 01:36:44   \n",
       "3807052  A4D50CAC9CCFB180 2020-10-20 01:51:54 2020-10-20 01:59:11   \n",
       "3807054  2D551117FD01DCE1 2020-10-20 01:04:29 2020-10-20 01:19:33   \n",
       "3807056  09264F6A1F8E4CEE 2020-10-20 01:38:07 2020-10-20 01:48:04   \n",
       "\n",
       "                      start_station_name  start_station_id  \\\n",
       "0                    Ellis Ave & 55th St             420.0   \n",
       "1            Western Ave & Winnebago Ave             116.0   \n",
       "2            Western Ave & Winnebago Ave             116.0   \n",
       "3                Lincoln Ave & Winona St             472.0   \n",
       "4                Clinton St & Madison St              77.0   \n",
       "...                                  ...               ...   \n",
       "3807048             Racine Ave & 35th St             367.0   \n",
       "3807050         Wells St & Evergreen Ave             291.0   \n",
       "3807052            Kimbark Ave & 53rd St             322.0   \n",
       "3807054  Pine Grove Ave & Irving Park Rd             254.0   \n",
       "3807056           St. Clair St & Erie St             211.0   \n",
       "\n",
       "                        end_station_name  end_station_id member_casual  Year  \\\n",
       "0                   Harper Ave & 59th St           425.0    Subscriber  2019   \n",
       "1                    Morgan St & Polk St           241.0      Customer  2019   \n",
       "2                     May St & Taylor St            22.0    Subscriber  2019   \n",
       "3           Maplewood Ave & Peterson Ave           455.0    Subscriber  2019   \n",
       "4           Clinton St & Washington Blvd            91.0    Subscriber  2019   \n",
       "...                                  ...             ...           ...   ...   \n",
       "3807048             Racine Ave & 18th St            15.0        member  2020   \n",
       "3807050        Wabash Ave & Roosevelt Rd            59.0        member  2020   \n",
       "3807052  Blackstone Ave & Hyde Park Blvd           121.0        member  2020   \n",
       "3807054         Glenwood Ave & Morse Ave           447.0        member  2020   \n",
       "3807056          Clark St & Armitage Ave            94.0        member  2020   \n",
       "\n",
       "         Month  Day  Hour Temperature  Humidity Wind Speed Wind Gust  \\\n",
       "0           11    1     0       26  F        81      8 mph     0 mph   \n",
       "1           11    1     0       26  F        81      8 mph     0 mph   \n",
       "2           11    1     0       26  F        81      8 mph     0 mph   \n",
       "3           11    1     0       26  F        81      8 mph     0 mph   \n",
       "4           11    1     0       26  F        81      8 mph     0 mph   \n",
       "...        ...  ...   ...         ...       ...        ...       ...   \n",
       "3807048     10   20     1        44 F        79       8mph      0mph   \n",
       "3807050     10   20     1        44 F        79       8mph      0mph   \n",
       "3807052     10   20     1        44 F        79       8mph      0mph   \n",
       "3807054     10   20     1        44 F        79       8mph      0mph   \n",
       "3807056     10   20     1        44 F        79       8mph      0mph   \n",
       "\n",
       "        Precipitation Condition  index Holiday_Name  \n",
       "0              0.0 in      Fair    103  Not Holiday  \n",
       "1              0.0 in      Fair    103  Not Holiday  \n",
       "2              0.0 in      Fair    103  Not Holiday  \n",
       "3              0.0 in      Fair    103  Not Holiday  \n",
       "4              0.0 in      Fair    103  Not Holiday  \n",
       "...               ...       ...    ...          ...  \n",
       "3807048         0.0in    Cloudy    355  Not Holiday  \n",
       "3807050         0.0in    Cloudy    355  Not Holiday  \n",
       "3807052         0.0in    Cloudy    355  Not Holiday  \n",
       "3807054         0.0in    Cloudy    355  Not Holiday  \n",
       "3807056         0.0in    Cloudy    355  Not Holiday  \n",
       "\n",
       "[3481818 rows x 20 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop duplicate rows in the dataframe except the forst occurrence\n",
    "cleaned_data = final_merged_data.drop_duplicates(subset='ride_id', keep='first')\n",
    "cleaned_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ride_id</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>member_casual</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Wind Gust</th>\n",
       "      <th>Precipitation</th>\n",
       "      <th>Condition</th>\n",
       "      <th>index</th>\n",
       "      <th>Holiday_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25612325</td>\n",
       "      <td>2019-11-01 00:00:45</td>\n",
       "      <td>2019-11-01 00:08:10</td>\n",
       "      <td>Ellis Ave &amp; 55th St</td>\n",
       "      <td>420.0</td>\n",
       "      <td>Harper Ave &amp; 59th St</td>\n",
       "      <td>425.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25612326</td>\n",
       "      <td>2019-11-01 00:01:13</td>\n",
       "      <td>2019-11-01 00:30:09</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>Morgan St &amp; Polk St</td>\n",
       "      <td>241.0</td>\n",
       "      <td>Customer</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25612327</td>\n",
       "      <td>2019-11-01 00:01:42</td>\n",
       "      <td>2019-11-01 00:38:32</td>\n",
       "      <td>Western Ave &amp; Winnebago Ave</td>\n",
       "      <td>116.0</td>\n",
       "      <td>May St &amp; Taylor St</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25612328</td>\n",
       "      <td>2019-11-01 00:02:24</td>\n",
       "      <td>2019-11-01 00:09:27</td>\n",
       "      <td>Lincoln Ave &amp; Winona St</td>\n",
       "      <td>472.0</td>\n",
       "      <td>Maplewood Ave &amp; Peterson Ave</td>\n",
       "      <td>455.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25612329</td>\n",
       "      <td>2019-11-01 00:02:30</td>\n",
       "      <td>2019-11-01 00:04:09</td>\n",
       "      <td>Clinton St &amp; Madison St</td>\n",
       "      <td>77.0</td>\n",
       "      <td>Clinton St &amp; Washington Blvd</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2019</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>26  F</td>\n",
       "      <td>81</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>0.0 in</td>\n",
       "      <td>Fair</td>\n",
       "      <td>103</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807048</th>\n",
       "      <td>23638F49B7E7D116</td>\n",
       "      <td>2020-10-20 01:23:37</td>\n",
       "      <td>2020-10-20 01:39:30</td>\n",
       "      <td>Racine Ave &amp; 35th St</td>\n",
       "      <td>367.0</td>\n",
       "      <td>Racine Ave &amp; 18th St</td>\n",
       "      <td>15.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807050</th>\n",
       "      <td>3048BAC5F769B4DD</td>\n",
       "      <td>2020-10-20 01:20:30</td>\n",
       "      <td>2020-10-20 01:36:44</td>\n",
       "      <td>Wells St &amp; Evergreen Ave</td>\n",
       "      <td>291.0</td>\n",
       "      <td>Wabash Ave &amp; Roosevelt Rd</td>\n",
       "      <td>59.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807052</th>\n",
       "      <td>A4D50CAC9CCFB180</td>\n",
       "      <td>2020-10-20 01:51:54</td>\n",
       "      <td>2020-10-20 01:59:11</td>\n",
       "      <td>Kimbark Ave &amp; 53rd St</td>\n",
       "      <td>322.0</td>\n",
       "      <td>Blackstone Ave &amp; Hyde Park Blvd</td>\n",
       "      <td>121.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807054</th>\n",
       "      <td>2D551117FD01DCE1</td>\n",
       "      <td>2020-10-20 01:04:29</td>\n",
       "      <td>2020-10-20 01:19:33</td>\n",
       "      <td>Pine Grove Ave &amp; Irving Park Rd</td>\n",
       "      <td>254.0</td>\n",
       "      <td>Glenwood Ave &amp; Morse Ave</td>\n",
       "      <td>447.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807056</th>\n",
       "      <td>09264F6A1F8E4CEE</td>\n",
       "      <td>2020-10-20 01:38:07</td>\n",
       "      <td>2020-10-20 01:48:04</td>\n",
       "      <td>St. Clair St &amp; Erie St</td>\n",
       "      <td>211.0</td>\n",
       "      <td>Clark St &amp; Armitage Ave</td>\n",
       "      <td>94.0</td>\n",
       "      <td>member</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>44 F</td>\n",
       "      <td>79</td>\n",
       "      <td>8mph</td>\n",
       "      <td>0mph</td>\n",
       "      <td>0.0in</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>355</td>\n",
       "      <td>Not Holiday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3384105 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ride_id          started_at            ended_at  \\\n",
       "0                25612325 2019-11-01 00:00:45 2019-11-01 00:08:10   \n",
       "1                25612326 2019-11-01 00:01:13 2019-11-01 00:30:09   \n",
       "2                25612327 2019-11-01 00:01:42 2019-11-01 00:38:32   \n",
       "3                25612328 2019-11-01 00:02:24 2019-11-01 00:09:27   \n",
       "4                25612329 2019-11-01 00:02:30 2019-11-01 00:04:09   \n",
       "...                   ...                 ...                 ...   \n",
       "3807048  23638F49B7E7D116 2020-10-20 01:23:37 2020-10-20 01:39:30   \n",
       "3807050  3048BAC5F769B4DD 2020-10-20 01:20:30 2020-10-20 01:36:44   \n",
       "3807052  A4D50CAC9CCFB180 2020-10-20 01:51:54 2020-10-20 01:59:11   \n",
       "3807054  2D551117FD01DCE1 2020-10-20 01:04:29 2020-10-20 01:19:33   \n",
       "3807056  09264F6A1F8E4CEE 2020-10-20 01:38:07 2020-10-20 01:48:04   \n",
       "\n",
       "                      start_station_name  start_station_id  \\\n",
       "0                    Ellis Ave & 55th St             420.0   \n",
       "1            Western Ave & Winnebago Ave             116.0   \n",
       "2            Western Ave & Winnebago Ave             116.0   \n",
       "3                Lincoln Ave & Winona St             472.0   \n",
       "4                Clinton St & Madison St              77.0   \n",
       "...                                  ...               ...   \n",
       "3807048             Racine Ave & 35th St             367.0   \n",
       "3807050         Wells St & Evergreen Ave             291.0   \n",
       "3807052            Kimbark Ave & 53rd St             322.0   \n",
       "3807054  Pine Grove Ave & Irving Park Rd             254.0   \n",
       "3807056           St. Clair St & Erie St             211.0   \n",
       "\n",
       "                        end_station_name  end_station_id member_casual  Year  \\\n",
       "0                   Harper Ave & 59th St           425.0    Subscriber  2019   \n",
       "1                    Morgan St & Polk St           241.0      Customer  2019   \n",
       "2                     May St & Taylor St            22.0    Subscriber  2019   \n",
       "3           Maplewood Ave & Peterson Ave           455.0    Subscriber  2019   \n",
       "4           Clinton St & Washington Blvd            91.0    Subscriber  2019   \n",
       "...                                  ...             ...           ...   ...   \n",
       "3807048             Racine Ave & 18th St            15.0        member  2020   \n",
       "3807050        Wabash Ave & Roosevelt Rd            59.0        member  2020   \n",
       "3807052  Blackstone Ave & Hyde Park Blvd           121.0        member  2020   \n",
       "3807054         Glenwood Ave & Morse Ave           447.0        member  2020   \n",
       "3807056          Clark St & Armitage Ave            94.0        member  2020   \n",
       "\n",
       "         Month  Day  Hour Temperature  Humidity Wind Speed Wind Gust  \\\n",
       "0           11    1     0       26  F        81      8 mph     0 mph   \n",
       "1           11    1     0       26  F        81      8 mph     0 mph   \n",
       "2           11    1     0       26  F        81      8 mph     0 mph   \n",
       "3           11    1     0       26  F        81      8 mph     0 mph   \n",
       "4           11    1     0       26  F        81      8 mph     0 mph   \n",
       "...        ...  ...   ...         ...       ...        ...       ...   \n",
       "3807048     10   20     1        44 F        79       8mph      0mph   \n",
       "3807050     10   20     1        44 F        79       8mph      0mph   \n",
       "3807052     10   20     1        44 F        79       8mph      0mph   \n",
       "3807054     10   20     1        44 F        79       8mph      0mph   \n",
       "3807056     10   20     1        44 F        79       8mph      0mph   \n",
       "\n",
       "        Precipitation Condition  index Holiday_Name  \n",
       "0              0.0 in      Fair    103  Not Holiday  \n",
       "1              0.0 in      Fair    103  Not Holiday  \n",
       "2              0.0 in      Fair    103  Not Holiday  \n",
       "3              0.0 in      Fair    103  Not Holiday  \n",
       "4              0.0 in      Fair    103  Not Holiday  \n",
       "...               ...       ...    ...          ...  \n",
       "3807048         0.0in    Cloudy    355  Not Holiday  \n",
       "3807050         0.0in    Cloudy    355  Not Holiday  \n",
       "3807052         0.0in    Cloudy    355  Not Holiday  \n",
       "3807054         0.0in    Cloudy    355  Not Holiday  \n",
       "3807056         0.0in    Cloudy    355  Not Holiday  \n",
       "\n",
       "[3384105 rows x 20 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop the NaN (missing) values from the dataframe\n",
    "cleaned_data.dropna(inplace=True)\n",
    "cleaned_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3384105, 20)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NUmber of rows dropped:  422953\n"
     ]
    }
   ],
   "source": [
    "print(\"NUmber of rows dropped: \", final_merged_data.shape[0] - cleaned_data.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save this dataframe to a csv file (serves as the cleaned data)\n",
    "cleaned_data.to_csv('cleaned_data.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}