Python_Proficiency_Test_vb2496.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Python Proficiency Test vb2496.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "vZ69k_i9wAZU",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 224
        },
        "outputId": "fe097022-071b-44af-9bee-dc41f5a60921"
      },
      "source": [
        "pip install download"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Collecting download\n",
            "  Downloading https://files.pythonhosted.org/packages/37/45/01e7455a9659528e77a414b222326d4c525796e4f571bbabcb2e0ff3d1f4/download-0.3.5-py3-none-any.whl\n",
            "Requirement already satisfied: requests in /usr/local/lib/python3.6/dist-packages (from download) (2.23.0)\n",
            "Requirement already satisfied: six in /usr/local/lib/python3.6/dist-packages (from download) (1.15.0)\n",
            "Requirement already satisfied: tqdm in /usr/local/lib/python3.6/dist-packages (from download) (4.41.1)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.6/dist-packages (from requests->download) (2020.6.20)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.6/dist-packages (from requests->download) (1.24.3)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.6/dist-packages (from requests->download) (2.10)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.6/dist-packages (from requests->download) (3.0.4)\n",
            "Installing collected packages: download\n",
            "Successfully installed download-0.3.5\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XiWMMhmhwKfb",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# importing all libraries\n",
        "from download import download\n",
        "import pandas as pd\n",
        "import numpy as np"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9WZNgP9rwZmu",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 119
        },
        "outputId": "c076b854-c470-4b43-a744-5bca010b46a6"
      },
      "source": [
        "# downloading the data from the location and storing it in the path tmp/aq\n",
        "path = download('https://s3.amazonaws.com/tripdata/201306-citibike-tripdata.zip','/tmp/aq', kind = 'zip')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Creating data folder...\n",
            "Downloading data from https://s3.amazonaws.com/tripdata/201306-citibike-tripdata.zip (16.0 MB)\n",
            "\n",
            "file_sizes: 100%|██████████████████████████| 16.8M/16.8M [00:00<00:00, 86.2MB/s]\n",
            "Extracting zip file...\n",
            "Successfully downloaded / unzipped to /tmp/aq\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AnD3b4XbwoMM",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# storing the data as mentioned in the question\n",
        "data = pd.read_csv('/tmp/aq/201306-citibike-tripdata.csv') \\\n",
        "                  [['tripduration', 'starttime', 'start station name', \\\n",
        "                                 'end station name', 'bikeid', 'usertype']] \\\n",
        ".rename(columns = {'start station name':'start_station_name', 'end station name':'end_station_name'})\n",
        "data = data[data.end_station_name.notnull()] #removing not null values from end_station_name"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xBAUG7Isw76N",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 221
        },
        "outputId": "609fd6ad-bb9b-43e1-ce4f-627a2cfcf716"
      },
      "source": [
        "print(data.shape)\n",
        "data.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "(559644, 6)\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "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>tripduration</th>\n",
              "      <th>starttime</th>\n",
              "      <th>start_station_name</th>\n",
              "      <th>end_station_name</th>\n",
              "      <th>bikeid</th>\n",
              "      <th>usertype</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>695</td>\n",
              "      <td>2013-06-01 00:00:01</td>\n",
              "      <td>Broadway &amp; W 24 St</td>\n",
              "      <td>9 Ave &amp; W 18 St</td>\n",
              "      <td>19678</td>\n",
              "      <td>Subscriber</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>693</td>\n",
              "      <td>2013-06-01 00:00:08</td>\n",
              "      <td>Broadway &amp; W 24 St</td>\n",
              "      <td>9 Ave &amp; W 18 St</td>\n",
              "      <td>16649</td>\n",
              "      <td>Subscriber</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2059</td>\n",
              "      <td>2013-06-01 00:00:44</td>\n",
              "      <td>Hicks St &amp; Montague St</td>\n",
              "      <td>Hicks St &amp; Montague St</td>\n",
              "      <td>19599</td>\n",
              "      <td>Customer</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>123</td>\n",
              "      <td>2013-06-01 00:01:04</td>\n",
              "      <td>E 15 St &amp; Irving Pl</td>\n",
              "      <td>Washington Park</td>\n",
              "      <td>16352</td>\n",
              "      <td>Subscriber</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1521</td>\n",
              "      <td>2013-06-01 00:01:22</td>\n",
              "      <td>Little West St &amp; 1 Pl</td>\n",
              "      <td>State St &amp; Smith St</td>\n",
              "      <td>15567</td>\n",
              "      <td>Subscriber</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   tripduration            starttime  ... bikeid    usertype\n",
              "0           695  2013-06-01 00:00:01  ...  19678  Subscriber\n",
              "1           693  2013-06-01 00:00:08  ...  16649  Subscriber\n",
              "2          2059  2013-06-01 00:00:44  ...  19599    Customer\n",
              "3           123  2013-06-01 00:01:04  ...  16352  Subscriber\n",
              "4          1521  2013-06-01 00:01:22  ...  15567  Subscriber\n",
              "\n",
              "[5 rows x 6 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "56w5hHdXzoh1",
        "colab_type": "text"
      },
      "source": [
        "#### **Question 1**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RAV-l6yXz5Yt",
        "colab_type": "text"
      },
      "source": [
        "Create a dataframe that - for each bike - tells us how many trips it was used for and the average duration of those trips."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AmgJU_lmz5GT",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "trips = pd.DataFrame() #creating a dataframe\n",
        "trips['no_of_trips'] = data.groupby(\"bikeid\")[\"bikeid\"].count() #finding the number of trips by each bike\n",
        "trips['avg_duration'] = data.groupby(\"bikeid\")[\"tripduration\"].mean() #avg duration of the trips"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fza6e9Wl3Rg5",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 51
        },
        "outputId": "43c31a54-efe7-4d18-ef68-32967b1f79f8"
      },
      "source": [
        "# testing for bikeid 14529\n",
        "print(data[data['bikeid']==14529].shape)\n",
        "print(data[data['bikeid']==14529]['tripduration'].sum(axis=0)/data[data['bikeid']==14529].shape[0])"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "(51, 6)\n",
            "2096.9803921568628\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "t1_KXnLqxm-b",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "outputId": "b5ac2e3a-00fc-429f-c2de-cf8133e859d5"
      },
      "source": [
        "trips.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>no_of_trips</th>\n",
              "      <th>avg_duration</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>bikeid</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>14529</th>\n",
              "      <td>51</td>\n",
              "      <td>2096.980392</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14531</th>\n",
              "      <td>142</td>\n",
              "      <td>1112.408451</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14532</th>\n",
              "      <td>146</td>\n",
              "      <td>875.376712</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14533</th>\n",
              "      <td>87</td>\n",
              "      <td>1276.574713</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14534</th>\n",
              "      <td>121</td>\n",
              "      <td>1624.355372</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        no_of_trips  avg_duration\n",
              "bikeid                           \n",
              "14529            51   2096.980392\n",
              "14531           142   1112.408451\n",
              "14532           146    875.376712\n",
              "14533            87   1276.574713\n",
              "14534           121   1624.355372"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j3hFcEl8993F",
        "colab_type": "text"
      },
      "source": [
        "#### **Question 2**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e6VjbCBy_bOy",
        "colab_type": "text"
      },
      "source": [
        "The usertype column describes the user type for that trip. For \"Subscribers\", trips shorter than 45 minutes are free, and trips over 45 minutes incur a fee. For \"Customers\", trips shorter than 30 minutes are free, and trips over 30 minutes incur a fee. Create a dataframe that - for each station - lists the proportion of trips started at that station that were free."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TPq41gO_EPp8",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#function for creating an indicator for the free rides\n",
        "def f(row):\n",
        "  if (row['tripduration'] < 2700) & (row['usertype'] == 'Subscriber'):\n",
        "    val = \"Free\"\n",
        "  elif (row['tripduration'] < 1800) & (row['usertype'] == 'Customer'):\n",
        "    val = \"Free\"\n",
        "  else:\n",
        "    val = \"Not Free\"\n",
        "  return val"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "JrFYvnZQ6J0L",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "data['free_trip_ind'] = data.apply(f, axis=1) #applying the function to the dataframe"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "idp5tn81L-ku",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "station_name = data.groupby([\"start_station_name\",\"free_trip_ind\"]).agg({'bikeid': 'count'}) #grouping the data and finding the count based on the starting station and the free rides\n",
        "trip_pct = station_name.groupby(level=0).apply(lambda x:100 * x / float(x.sum())) #finding the percentage for both free and not free rides\n",
        "trip_pct.rename(columns = {'bikeid':'proportion'}, inplace = True) \n",
        "trip_pct = trip_pct.reset_index()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xxpoVDfFNlxc",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "free_trip_df = trip_pct[trip_pct['free_trip_ind'] == 'Free'] #selecting the free rides\n",
        "free_trip_df = free_trip_df[['start_station_name','proportion']]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HZQNBryHNzlN",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "0083712b-4f5a-4da5-80e9-9156b8e6b3e5"
      },
      "source": [
        "free_trip_df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>start_station_name</th>\n",
              "      <th>proportion</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>95.640569</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1 Ave &amp; E 18 St</td>\n",
              "      <td>94.128440</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1 Ave &amp; E 30 St</td>\n",
              "      <td>93.571862</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>1 Ave &amp; E 44 St</td>\n",
              "      <td>91.082045</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>10 Ave &amp; W 28 St</td>\n",
              "      <td>92.548435</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  start_station_name  proportion\n",
              "0    1 Ave & E 16 St   95.640569\n",
              "2    1 Ave & E 18 St   94.128440\n",
              "4    1 Ave & E 30 St   93.571862\n",
              "6    1 Ave & E 44 St   91.082045\n",
              "8   10 Ave & W 28 St   92.548435"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oHuVvRvOWWoK",
        "colab_type": "text"
      },
      "source": [
        "#### **Question 3**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FLM-3NN8Wcqo",
        "colab_type": "text"
      },
      "source": [
        "For each station, we define the station's \"drop off popularity\" as the number of times a bike was dropped off at that station in our data. For each station, we define the station's \"proxy popularity\" by looking at all the trips *originating* from that station, and averaging the drop off popularity of the stations these trips were ended at. For example, suppose station X had three trips in our data; one trip ending at station A (drop off popularity 1500), one ending at station B (drop off popularity 1200), and one ending at station C (drop off popularity 1600), then the proxy popularity for station X is (1500 + 1200 + 1600)/3 = 1433.33. Create a dataframe that - for each station - finds its proxy popularity."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xpZ2tdcNOxLs",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#finding the drop off popularity by counting the number of bikes that terminate at a particular station\n",
        "drop_off_df = pd.DataFrame()\n",
        "drop_off_df['drop_off_popularity'] = data.groupby(\"end_station_name\")[\"bikeid\"].count()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Cc9kn1g9dcWq",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#merging the above dataframe with the calculated drop_off_popularity with the orignal one\n",
        "df_final = pd.merge(data, drop_off_df, on='end_station_name')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "drlJXmFql53g",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#finding the mean of the the drop_off_popularity for each starting station\n",
        "proxy_pop_df = df_final.groupby(\"start_station_name\")[\"drop_off_popularity\"].mean()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jT-qkU0GE_4R",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "proxy_pop_df = proxy_pop_df.reset_index()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "M8KXehcvrYOB",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "772e6774-b3f8-4fef-add3-3a79916c9b96"
      },
      "source": [
        "proxy_pop_df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>start_station_name</th>\n",
              "      <th>drop_off_popularity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2327.218861</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1 Ave &amp; E 18 St</td>\n",
              "      <td>2331.233028</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1 Ave &amp; E 30 St</td>\n",
              "      <td>2227.709521</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1 Ave &amp; E 44 St</td>\n",
              "      <td>2271.000595</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10 Ave &amp; W 28 St</td>\n",
              "      <td>2469.861401</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  start_station_name  drop_off_popularity\n",
              "0    1 Ave & E 16 St          2327.218861\n",
              "1    1 Ave & E 18 St          2331.233028\n",
              "2    1 Ave & E 30 St          2227.709521\n",
              "3    1 Ave & E 44 St          2271.000595\n",
              "4   10 Ave & W 28 St          2469.861401"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f6XfkRbSpZpW",
        "colab_type": "text"
      },
      "source": [
        "#### **Question 4**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P1f-e62lpeM8",
        "colab_type": "text"
      },
      "source": [
        "\n",
        "Fit a linear regression model to predict the number of bikes that will be picked up from a station in a given hour based on the hour of day, day of week, and the number of bikes that were picked up from that station in the previous hour. Fit the model on the whole dataset - do not use separate training/test sets.\n",
        "\n",
        "A colleague of yours suggests that every night, you should use the linear regression model you just created to estimate the number of bikes that will be required at each station the next day, and use it to re-balance bikes across the system. What is the glaring flaw in this suggestion?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YMyrCpaEP6FI",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "c2d78069-f923-4032-acdd-624faf1a3e3f"
      },
      "source": [
        "data.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>tripduration</th>\n",
              "      <th>starttime</th>\n",
              "      <th>start_station_name</th>\n",
              "      <th>end_station_name</th>\n",
              "      <th>bikeid</th>\n",
              "      <th>usertype</th>\n",
              "      <th>free_trip_ind</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>695</td>\n",
              "      <td>2013-06-01 00:00:01</td>\n",
              "      <td>Broadway &amp; W 24 St</td>\n",
              "      <td>9 Ave &amp; W 18 St</td>\n",
              "      <td>19678</td>\n",
              "      <td>Subscriber</td>\n",
              "      <td>Free</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>693</td>\n",
              "      <td>2013-06-01 00:00:08</td>\n",
              "      <td>Broadway &amp; W 24 St</td>\n",
              "      <td>9 Ave &amp; W 18 St</td>\n",
              "      <td>16649</td>\n",
              "      <td>Subscriber</td>\n",
              "      <td>Free</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2059</td>\n",
              "      <td>2013-06-01 00:00:44</td>\n",
              "      <td>Hicks St &amp; Montague St</td>\n",
              "      <td>Hicks St &amp; Montague St</td>\n",
              "      <td>19599</td>\n",
              "      <td>Customer</td>\n",
              "      <td>Not Free</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>123</td>\n",
              "      <td>2013-06-01 00:01:04</td>\n",
              "      <td>E 15 St &amp; Irving Pl</td>\n",
              "      <td>Washington Park</td>\n",
              "      <td>16352</td>\n",
              "      <td>Subscriber</td>\n",
              "      <td>Free</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1521</td>\n",
              "      <td>2013-06-01 00:01:22</td>\n",
              "      <td>Little West St &amp; 1 Pl</td>\n",
              "      <td>State St &amp; Smith St</td>\n",
              "      <td>15567</td>\n",
              "      <td>Subscriber</td>\n",
              "      <td>Free</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   tripduration            starttime  ...    usertype free_trip_ind\n",
              "0           695  2013-06-01 00:00:01  ...  Subscriber          Free\n",
              "1           693  2013-06-01 00:00:08  ...  Subscriber          Free\n",
              "2          2059  2013-06-01 00:00:44  ...    Customer      Not Free\n",
              "3           123  2013-06-01 00:01:04  ...  Subscriber          Free\n",
              "4          1521  2013-06-01 00:01:22  ...  Subscriber          Free\n",
              "\n",
              "[5 rows x 7 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CsdX38kgGNXC",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "data['date'] = pd.to_datetime(data['starttime']).dt.date #extracting the date \n",
        "data['day_of_week'] = pd.to_datetime(data['starttime']).dt.day_name() #extracting the day of the week\n",
        "data['datehour'] = pd.to_datetime(data['starttime']).dt.hour #extracting hour"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZcqrHeDVGNSR",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#the number of bikes taken from each station on a particular date from a given start station \n",
        "regress_df = data.groupby([\"start_station_name\",\"date\",\"day_of_week\",\"datehour\"])[\"bikeid\"].count()\n",
        "regress_df = regress_df.reset_index()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mnvusjvqF3p-",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "c2451d68-218e-4e01-8d25-8af82f0eb70f"
      },
      "source": [
        "regress_df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>start_station_name</th>\n",
              "      <th>date</th>\n",
              "      <th>day_of_week</th>\n",
              "      <th>datehour</th>\n",
              "      <th>bikeid</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>9</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>10</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>12</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>14</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  start_station_name        date day_of_week  datehour  bikeid\n",
              "0    1 Ave & E 16 St  2013-06-01    Saturday         0       1\n",
              "1    1 Ave & E 16 St  2013-06-01    Saturday         9       3\n",
              "2    1 Ave & E 16 St  2013-06-01    Saturday        10       1\n",
              "3    1 Ave & E 16 St  2013-06-01    Saturday        12       3\n",
              "4    1 Ave & E 16 St  2013-06-01    Saturday        14       1"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w5MAa6QEX8JD",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# calculating the time diff with the previous row to evaluate only those rows where the time diff is 1\n",
        "regress_df['timediff'] = regress_df.sort_values(by=['datehour'],ascending=False).groupby([\"start_station_name\",\"date\"])\\\n",
        "[\"datehour\"].diff(-1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "q7c5TRaobXeF",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#testing for the above case\n",
        "# regress_df[(pd.to_datetime(regress_df['date']) == '2013-06-01') & (regress_df['start_station_name'] == '1 Ave & E 16 St')] "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Bo2cJshfYgOA",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "lag_df = regress_df[regress_df['timediff'] == 1]\n",
        "no_lag_df = regress_df[regress_df['timediff'] != 1]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9tLXxbKvfLfB",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 241
        },
        "outputId": "56002e0e-7ac1-4738-8dda-1f5171c910c0"
      },
      "source": [
        "#creating a column which holds the value of the previous hour no of bikes\n",
        "lag_df['lagged'] = (lag_df.sort_values(by=['datehour'], ascending=True)\n",
        "                        .groupby(['start_station_name','date'])['bikeid'].shift(1))\n",
        "no_lag_df['lagged'] = 0"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame.\n",
            "Try using .loc[row_indexer,col_indexer] = value instead\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
            "  This is separate from the ipykernel package so we can avoid doing imports until\n",
            "/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame.\n",
            "Try using .loc[row_indexer,col_indexer] = value instead\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
            "  after removing the cwd from sys.path.\n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uQnCVTDdgQ4a",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "e70822d5-40e5-4e59-a392-834ddbdccb33"
      },
      "source": [
        "lag_df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>start_station_name</th>\n",
              "      <th>date</th>\n",
              "      <th>day_of_week</th>\n",
              "      <th>datehour</th>\n",
              "      <th>bikeid</th>\n",
              "      <th>timediff</th>\n",
              "      <th>lagged</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>10</td>\n",
              "      <td>1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>15</td>\n",
              "      <td>1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>16</td>\n",
              "      <td>1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>19</td>\n",
              "      <td>2</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>2013-06-01</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>20</td>\n",
              "      <td>4</td>\n",
              "      <td>1.0</td>\n",
              "      <td>2.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  start_station_name        date day_of_week  ...  bikeid  timediff  lagged\n",
              "2    1 Ave & E 16 St  2013-06-01    Saturday  ...       1       1.0     NaN\n",
              "5    1 Ave & E 16 St  2013-06-01    Saturday  ...       1       1.0     1.0\n",
              "6    1 Ave & E 16 St  2013-06-01    Saturday  ...       1       1.0     1.0\n",
              "8    1 Ave & E 16 St  2013-06-01    Saturday  ...       2       1.0     1.0\n",
              "9    1 Ave & E 16 St  2013-06-01    Saturday  ...       4       1.0     2.0\n",
              "\n",
              "[5 rows x 7 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nyDeO3t8fYcY",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "final_df = pd.concat([lag_df, no_lag_df])\n",
        "final_df[\"lagged\"].fillna(0, inplace=True)\n",
        "final_df = final_df[['start_station_name','day_of_week','datehour','lagged','bikeid']].sort_values\\\n",
        "(['start_station_name','day_of_week','datehour'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HBdaSHrN2iSA",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df = pd.DataFrame()\n",
        "df['lag_no_of_bikes'] = final_df.groupby(['start_station_name','day_of_week','datehour'])[\"lagged\"].mean()\n",
        "df[\"no_of_bikes\"] = final_df.groupby(['start_station_name','day_of_week','datehour'])[\"bikeid\"].mean()\n",
        "df.reset_index(inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dNFD6ici4M_R",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 359
        },
        "outputId": "8f3ac052-1188-442a-9c7e-4dffd167da1e"
      },
      "source": [
        "df.head(10)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>start_station_name</th>\n",
              "      <th>day_of_week</th>\n",
              "      <th>datehour</th>\n",
              "      <th>lag_no_of_bikes</th>\n",
              "      <th>no_of_bikes</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>0</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>3.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>2</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>1.500000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>4</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>1.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>6</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>2.500000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>7</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>5.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>8</td>\n",
              "      <td>4.666667</td>\n",
              "      <td>15.666667</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>9</td>\n",
              "      <td>15.666667</td>\n",
              "      <td>15.333333</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>10</td>\n",
              "      <td>15.333333</td>\n",
              "      <td>1.666667</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>11</td>\n",
              "      <td>1.666667</td>\n",
              "      <td>2.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>1 Ave &amp; E 16 St</td>\n",
              "      <td>Friday</td>\n",
              "      <td>12</td>\n",
              "      <td>1.500000</td>\n",
              "      <td>3.000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  start_station_name day_of_week  datehour  lag_no_of_bikes  no_of_bikes\n",
              "0    1 Ave & E 16 St      Friday         0         0.000000     3.000000\n",
              "1    1 Ave & E 16 St      Friday         2         0.000000     1.500000\n",
              "2    1 Ave & E 16 St      Friday         4         0.000000     1.000000\n",
              "3    1 Ave & E 16 St      Friday         6         0.000000     2.500000\n",
              "4    1 Ave & E 16 St      Friday         7         0.000000     5.000000\n",
              "5    1 Ave & E 16 St      Friday         8         4.666667    15.666667\n",
              "6    1 Ave & E 16 St      Friday         9        15.666667    15.333333\n",
              "7    1 Ave & E 16 St      Friday        10        15.333333     1.666667\n",
              "8    1 Ave & E 16 St      Friday        11         1.666667     2.000000\n",
              "9    1 Ave & E 16 St      Friday        12         1.500000     3.000000"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 43
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z4bySc2-Jo9H",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#creating the station name merged with the day of the week and datehour as the index\n",
        "df[\"inde_x\"] = df[\"start_station_name\"] + \" | \" + df[\"day_of_week\"] + \" | \" + df[\"datehour\"].astype(str)\n",
        "df = df.set_index('inde_x')\n",
        "df.drop(['start_station_name'],axis = 1,inplace = True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-0U3V8RVmXyU",
        "colab_type": "text"
      },
      "source": [
        "**Label Encoding for Weeks**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K3MS8CvxlF3S",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#converting the weekdays into labels\n",
        "from sklearn.preprocessing import LabelEncoder\n",
        "labelencoder = LabelEncoder()\n",
        "df['day_of_week_cat'] = labelencoder.fit_transform(df['day_of_week'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hjVzlcjQsX4_",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 136
        },
        "outputId": "8dd2b61f-1389-4b83-c59e-d83f041c3258"
      },
      "source": [
        "#storing the values for future reference\n",
        "weekday_dict = pd.Series(df.day_of_week.values,index=df.day_of_week_cat).to_dict()\n",
        "weekday_dict"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{0: 'Friday',\n",
              " 1: 'Monday',\n",
              " 2: 'Saturday',\n",
              " 3: 'Sunday',\n",
              " 4: 'Thursday',\n",
              " 5: 'Tuesday',\n",
              " 6: 'Wednesday'}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pE4l3vcqsw5w",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "new_df = df.drop(['day_of_week'],axis = 1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "JNwwyHRgnEG1",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "outputId": "bb911783-50c0-450a-f9db-8217c62650f1"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>day_of_week</th>\n",
              "      <th>datehour</th>\n",
              "      <th>lag_no_of_bikes</th>\n",
              "      <th>no_of_bikes</th>\n",
              "      <th>day_of_week_cat</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>inde_x</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 0</th>\n",
              "      <td>Friday</td>\n",
              "      <td>0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 2</th>\n",
              "      <td>Friday</td>\n",
              "      <td>2</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.5</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 4</th>\n",
              "      <td>Friday</td>\n",
              "      <td>4</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 6</th>\n",
              "      <td>Friday</td>\n",
              "      <td>6</td>\n",
              "      <td>0.0</td>\n",
              "      <td>2.5</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 7</th>\n",
              "      <td>Friday</td>\n",
              "      <td>7</td>\n",
              "      <td>0.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                             day_of_week  ...  day_of_week_cat\n",
              "inde_x                                    ...                 \n",
              "1 Ave & E 16 St | Friday | 0      Friday  ...                0\n",
              "1 Ave & E 16 St | Friday | 2      Friday  ...                0\n",
              "1 Ave & E 16 St | Friday | 4      Friday  ...                0\n",
              "1 Ave & E 16 St | Friday | 6      Friday  ...                0\n",
              "1 Ave & E 16 St | Friday | 7      Friday  ...                0\n",
              "\n",
              "[5 rows x 5 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vAn6lhvftMCd",
        "colab_type": "text"
      },
      "source": [
        "**One Hot Encoding**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HgPogDO-z8Bd",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#creating dummy values for the weekdays\n",
        "df_enc = pd.get_dummies(df, columns=[\"day_of_week_cat\"], prefix=[\"weekday_\"] )"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4gjQPlRE8fMJ",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 595
        },
        "outputId": "208fedd7-c63a-4995-ff97-6bcd6d62e957"
      },
      "source": [
        "df_enc.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>day_of_week</th>\n",
              "      <th>datehour</th>\n",
              "      <th>lag_no_of_bikes</th>\n",
              "      <th>no_of_bikes</th>\n",
              "      <th>weekday__0</th>\n",
              "      <th>weekday__1</th>\n",
              "      <th>weekday__2</th>\n",
              "      <th>weekday__3</th>\n",
              "      <th>weekday__4</th>\n",
              "      <th>weekday__5</th>\n",
              "      <th>weekday__6</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>inde_x</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 0</th>\n",
              "      <td>Friday</td>\n",
              "      <td>0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 2</th>\n",
              "      <td>Friday</td>\n",
              "      <td>2</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.5</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 4</th>\n",
              "      <td>Friday</td>\n",
              "      <td>4</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 6</th>\n",
              "      <td>Friday</td>\n",
              "      <td>6</td>\n",
              "      <td>0.0</td>\n",
              "      <td>2.5</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1 Ave &amp; E 16 St | Friday | 7</th>\n",
              "      <td>Friday</td>\n",
              "      <td>7</td>\n",
              "      <td>0.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                             day_of_week  datehour  ...  weekday__5  weekday__6\n",
              "inde_x                                              ...                        \n",
              "1 Ave & E 16 St | Friday | 0      Friday         0  ...           0           0\n",
              "1 Ave & E 16 St | Friday | 2      Friday         2  ...           0           0\n",
              "1 Ave & E 16 St | Friday | 4      Friday         4  ...           0           0\n",
              "1 Ave & E 16 St | Friday | 6      Friday         6  ...           0           0\n",
              "1 Ave & E 16 St | Friday | 7      Friday         7  ...           0           0\n",
              "\n",
              "[5 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 54
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7sDvgRKl5EY_",
        "colab_type": "text"
      },
      "source": [
        "**Linear Regression**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_RDc2dZ6z76g",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "from sklearn import linear_model\n",
        "from sklearn.linear_model import LinearRegression"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sdseYVqpz7zi",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "X = df_enc[['datehour','lag_no_of_bikes', 'weekday__0', 'weekday__1', 'weekday__2', 'weekday__3', \n",
        "             'weekday__4', 'weekday__5', 'weekday__6']].values\n",
        "y = df_enc['no_of_bikes'].values             "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Sjey7sWwz7pn",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "lm = linear_model.LinearRegression()\n",
        "model = lm.fit(X,y)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QESdyGroz7dr",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "predictions = lm.predict(X)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MNjTCVc39pjK",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "23a9d7c7-751a-4d2d-e012-e4fcb0876943"
      },
      "source": [
        "lm.score(X,y)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.558404332014879"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 60
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SUqFehIj9pal",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 51
        },
        "outputId": "343eeea9-345f-4db7-9eec-2c0c5962a3b8"
      },
      "source": [
        "lm.coef_"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array([-0.03504413,  0.68577046,  0.06164362, -0.10333369,  0.13450595,\n",
              "        0.07767696, -0.09036461, -0.10299637,  0.02286814])"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 61
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L6RvJn5gA54j",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "9853e624-d7c8-4d6e-f6d3-da5375a7b836"
      },
      "source": [
        "lm.intercept_"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "2.013202298134525"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 62
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hi_i3HNbCJYQ",
        "colab_type": "text"
      },
      "source": [
        "By predicting the number of bikes required for the next day on the previous night, we will not be able to use the feature for the “number of bikes picked up from a station in the previous hour” to the full extent. Vital information from the feature will be lost by predicting the bike demand way before the actual time. This is the glaring problem with the approach.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rbW10_A9topI",
        "colab_type": "text"
      },
      "source": [
        "#### **Question 5**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XK1KbjA6tt22",
        "colab_type": "text"
      },
      "source": [
        "Explain what the author of this code was doing, and why they were doing it."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DgKqjiJwsmM3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "  (data.assign(h = lambda x : pd.to_datetime(x.starttime).dt.hour)\n",
        "  .groupby('h')\n",
        "  .tripduration\n",
        "  .agg([np.mean,np.std,len])\n",
        "  .reset_index()\n",
        "  .assign(stde = lambda x : x['std'] / np.sqrt(x['len']))).head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6hPDN_jG3Fyi",
        "colab_type": "text"
      },
      "source": [
        "**Answer**\n",
        "\n",
        "The author is doing an univariate analysis of the tripduration based on the hour of the day. First the hour of the day is calculated from the starttime and then the average, standard deviation, the total trip durations during each hour and grouped by hour.\n",
        "\n",
        "The author does this to analyze during which hour of the day users tend to spend more time on the trips."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xq6XHECd8vwj",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}