Notebook_2_transform.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Notebook_2_transform.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyOpXxFlJfkzl4HZqQBXvEzv",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/HP-Nunes/SMCapstone_GColab/blob/main/Notebook_2_transform.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XJlyDqXyVmdn"
      },
      "source": [
        "# **2. Transformation**\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fgzwa-NVWAyV"
      },
      "source": [
        "### Boilerplate Code"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VT-xnQ9xWxOh",
        "outputId": "c5182f84-c7a3-4984-dc51-64e6d099b3d4"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')\n",
        "\n",
        "!pip install -r drive/MyDrive/Gcolab/requirements.txt"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n",
            "Requirement already satisfied: pandas in /usr/local/lib/python3.7/dist-packages (from -r drive/MyDrive/Gcolab/requirements.txt (line 1)) (1.1.5)\n",
            "Requirement already satisfied: numpy in /usr/local/lib/python3.7/dist-packages (from -r drive/MyDrive/Gcolab/requirements.txt (line 2)) (1.19.5)\n",
            "Requirement already satisfied: seaborn in /usr/local/lib/python3.7/dist-packages (from -r drive/MyDrive/Gcolab/requirements.txt (line 3)) (0.11.2)\n",
            "Requirement already satisfied: matplotlib in /usr/local/lib/python3.7/dist-packages (from -r drive/MyDrive/Gcolab/requirements.txt (line 4)) (3.2.2)\n",
            "Requirement already satisfied: glob2 in /usr/local/lib/python3.7/dist-packages (from -r drive/MyDrive/Gcolab/requirements.txt (line 5)) (0.7)\n",
            "Collecting chart_studio\n",
            "  Downloading chart_studio-1.1.0-py3-none-any.whl (64 kB)\n",
            "\u001b[K     |████████████████████████████████| 64 kB 2.5 MB/s \n",
            "\u001b[?25hCollecting plotly==5.3.1\n",
            "  Downloading plotly-5.3.1-py2.py3-none-any.whl (23.9 MB)\n",
            "\u001b[K     |████████████████████████████████| 23.9 MB 1.1 MB/s \n",
            "\u001b[?25hCollecting geopandas\n",
            "  Downloading geopandas-0.10.2-py2.py3-none-any.whl (1.0 MB)\n",
            "\u001b[K     |████████████████████████████████| 1.0 MB 60.8 MB/s \n",
            "\u001b[?25hCollecting contextily\n",
            "  Downloading contextily-1.2.0-py3-none-any.whl (16 kB)\n",
            "Collecting pygeos\n",
            "  Downloading pygeos-0.10.2-cp37-cp37m-manylinux_2_12_x86_64.manylinux2010_x86_64.whl (1.8 MB)\n",
            "\u001b[K     |████████████████████████████████| 1.8 MB 72.2 MB/s \n",
            "\u001b[?25hRequirement already satisfied: shapely in /usr/local/lib/python3.7/dist-packages (from -r drive/MyDrive/Gcolab/requirements.txt (line 13)) (1.7.1)\n",
            "Collecting pyproj\n",
            "  Downloading pyproj-3.2.1-cp37-cp37m-manylinux2010_x86_64.whl (6.3 MB)\n",
            "\u001b[K     |████████████████████████████████| 6.3 MB 61.6 MB/s \n",
            "\u001b[?25hCollecting fiona\n",
            "  Downloading Fiona-1.8.20-cp37-cp37m-manylinux1_x86_64.whl (15.4 MB)\n",
            "\u001b[K     |████████████████████████████████| 15.4 MB 82.7 MB/s \n",
            "\u001b[?25hCollecting tenacity>=6.2.0\n",
            "  Downloading tenacity-8.0.1-py3-none-any.whl (24 kB)\n",
            "Requirement already satisfied: six in /usr/local/lib/python3.7/dist-packages (from plotly==5.3.1->-r drive/MyDrive/Gcolab/requirements.txt (line 7)) (1.15.0)\n",
            "Requirement already satisfied: pytz>=2017.2 in /usr/local/lib/python3.7/dist-packages (from pandas->-r drive/MyDrive/Gcolab/requirements.txt (line 1)) (2018.9)\n",
            "Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas->-r drive/MyDrive/Gcolab/requirements.txt (line 1)) (2.8.2)\n",
            "Requirement already satisfied: scipy>=1.0 in /usr/local/lib/python3.7/dist-packages (from seaborn->-r drive/MyDrive/Gcolab/requirements.txt (line 3)) (1.4.1)\n",
            "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib->-r drive/MyDrive/Gcolab/requirements.txt (line 4)) (2.4.7)\n",
            "Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.7/dist-packages (from matplotlib->-r drive/MyDrive/Gcolab/requirements.txt (line 4)) (0.10.0)\n",
            "Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib->-r drive/MyDrive/Gcolab/requirements.txt (line 4)) (1.3.2)\n",
            "Requirement already satisfied: requests in /usr/local/lib/python3.7/dist-packages (from chart_studio->-r drive/MyDrive/Gcolab/requirements.txt (line 6)) (2.23.0)\n",
            "Requirement already satisfied: retrying>=1.3.3 in /usr/local/lib/python3.7/dist-packages (from chart_studio->-r drive/MyDrive/Gcolab/requirements.txt (line 6)) (1.3.3)\n",
            "Requirement already satisfied: certifi in /usr/local/lib/python3.7/dist-packages (from pyproj->-r drive/MyDrive/Gcolab/requirements.txt (line 14)) (2021.5.30)\n",
            "Requirement already satisfied: attrs>=17 in /usr/local/lib/python3.7/dist-packages (from fiona->-r drive/MyDrive/Gcolab/requirements.txt (line 15)) (21.2.0)\n",
            "Collecting cligj>=0.5\n",
            "  Downloading cligj-0.7.2-py3-none-any.whl (7.1 kB)\n",
            "Requirement already satisfied: setuptools in /usr/local/lib/python3.7/dist-packages (from fiona->-r drive/MyDrive/Gcolab/requirements.txt (line 15)) (57.4.0)\n",
            "Requirement already satisfied: click>=4.0 in /usr/local/lib/python3.7/dist-packages (from fiona->-r drive/MyDrive/Gcolab/requirements.txt (line 15)) (7.1.2)\n",
            "Collecting click-plugins>=1.0\n",
            "  Downloading click_plugins-1.1.1-py2.py3-none-any.whl (7.5 kB)\n",
            "Collecting munch\n",
            "  Downloading munch-2.5.0-py2.py3-none-any.whl (10 kB)\n",
            "Requirement already satisfied: pillow in /usr/local/lib/python3.7/dist-packages (from contextily->-r drive/MyDrive/Gcolab/requirements.txt (line 11)) (7.1.2)\n",
            "Requirement already satisfied: joblib in /usr/local/lib/python3.7/dist-packages (from contextily->-r drive/MyDrive/Gcolab/requirements.txt (line 11)) (1.0.1)\n",
            "Collecting xyzservices\n",
            "  Downloading xyzservices-2021.10.0-py3-none-any.whl (34 kB)\n",
            "Requirement already satisfied: geopy in /usr/local/lib/python3.7/dist-packages (from contextily->-r drive/MyDrive/Gcolab/requirements.txt (line 11)) (1.17.0)\n",
            "Collecting rasterio\n",
            "  Downloading rasterio-1.2.10-cp37-cp37m-manylinux1_x86_64.whl (19.3 MB)\n",
            "\u001b[K     |████████████████████████████████| 19.3 MB 54.3 MB/s \n",
            "\u001b[?25hCollecting mercantile\n",
            "  Downloading mercantile-1.2.1-py3-none-any.whl (14 kB)\n",
            "Requirement already satisfied: geographiclib<2,>=1.49 in /usr/local/lib/python3.7/dist-packages (from geopy->contextily->-r drive/MyDrive/Gcolab/requirements.txt (line 11)) (1.52)\n",
            "Collecting snuggs>=1.4.1\n",
            "  Downloading snuggs-1.4.7-py3-none-any.whl (5.4 kB)\n",
            "Collecting affine\n",
            "  Downloading affine-2.3.0-py2.py3-none-any.whl (15 kB)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests->chart_studio->-r drive/MyDrive/Gcolab/requirements.txt (line 6)) (1.24.3)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests->chart_studio->-r drive/MyDrive/Gcolab/requirements.txt (line 6)) (3.0.4)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests->chart_studio->-r drive/MyDrive/Gcolab/requirements.txt (line 6)) (2.10)\n",
            "Installing collected packages: tenacity, snuggs, munch, cligj, click-plugins, affine, xyzservices, rasterio, pyproj, plotly, mercantile, fiona, pygeos, geopandas, contextily, chart-studio\n",
            "  Attempting uninstall: plotly\n",
            "    Found existing installation: plotly 4.4.1\n",
            "    Uninstalling plotly-4.4.1:\n",
            "      Successfully uninstalled plotly-4.4.1\n",
            "Successfully installed affine-2.3.0 chart-studio-1.1.0 click-plugins-1.1.1 cligj-0.7.2 contextily-1.2.0 fiona-1.8.20 geopandas-0.10.2 mercantile-1.2.1 munch-2.5.0 plotly-5.3.1 pygeos-0.10.2 pyproj-3.2.1 rasterio-1.2.10 snuggs-1.4.7 tenacity-8.0.1 xyzservices-2021.10.0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yxQEI0YCWBdB",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8146718e-c345-49a2-cc7f-8ee6f383c03f"
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import seaborn as sns\n",
        "import matplotlib.pyplot as plt\n",
        "import glob\n",
        "import geopandas as gpd\n",
        "import contextily # https://geographicdata.science/book/notebooks/08_point_pattern_analysis.html\n",
        "import pygeos\n",
        "import shapely.speedups\n",
        "from pyproj import CRS\n",
        "from fiona.crs import to_string, from_epsg\n",
        "pd.set_option('display.max_columns', None)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/geopandas/_compat.py:115: UserWarning: The Shapely GEOS version (3.8.0-CAPI-1.13.1 ) is incompatible with the GEOS version PyGEOS was compiled with (3.9.1-CAPI-1.14.2). Conversions between both will be slow.\n",
            "  shapely_geos_version, geos_capi_version_string\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sZMeme9PWF0c"
      },
      "source": [
        "## Data Cleaning\n",
        "---\n",
        "### Objectives\n",
        "\n",
        "*   Standardize the data schema across all years;\n",
        "*   Filter the records across all sets (i.e. no datapoints outside of the city of San Francisco, remove NULLs and 'artificial' records [errors, strange outliers, testing sites]);\n",
        "*   Identify the attributes of interests (and drop redundant/disinterested fields), and convert the fields to the appropriate data-type if need be;\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 156
        },
        "id": "hH5H5is3WF8w",
        "outputId": "44da0bc1-b8c2-434d-a2db-9f1d7d6b2b3c"
      },
      "source": [
        "# Import the raw csv yearly datasets into their respective pandas dataframe\n",
        "\n",
        "df19raw = pd.read_csv(r'drive/MyDrive/Gcolab/rawdata/systemdatalyft/2019lyftraw.csv',float_precision=None)\n",
        "display(df19raw.shape)\n",
        "df20raw = pd.read_csv(r'drive/MyDrive/Gcolab/rawdata/systemdatalyft/2020lyftraw.csv',float_precision=None)\n",
        "display(df20raw.shape)\n",
        "df21raw = pd.read_csv(r'drive/MyDrive/Gcolab/rawdata/systemdatalyft/2021lyftraw.csv',float_precision=None)\n",
        "display(df21raw.shape)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2718: DtypeWarning: Columns (13,15) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  interactivity=interactivity, compiler=compiler, result=result)\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(2506983, 16)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2718: DtypeWarning: Columns (0,1,2,3,5,7,12,14,16,17,23,24) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  interactivity=interactivity, compiler=compiler, result=result)\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(2143557, 25)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(1492990, 14)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qB4ZT0FcqC87"
      },
      "source": [
        "def cleaning2019(df):\n",
        "\n",
        "  columns = ['start_time','end_time']\n",
        "  for column in columns:\n",
        "    df[column] = pd.to_datetime(df[column],dayfirst=True)       \n",
        "    df[column + str('_day')] = df[column].dt.day_name()\n",
        "    df[column + str('_month')] = df[column].dt.month_name()\n",
        "    df[column + str('_hour')] = df[column].dt.round('H').dt.hour\n",
        "    df[column + str('_year')] = df[column].dt.year\n",
        "\n",
        "  df = df.drop(['fname'],axis=1) # not needed\n",
        "\n",
        "  df = df[df.start_station_name != 'Prototype Lab'] # removing test trips \n",
        "\n",
        "  df = df[(df['start_station_latitude'] < 37.82) & (df['start_station_latitude'] > 37.7) & (df['start_station_longitude'] < -122.36) & (df['start_station_longitude'] > -122.52)]\n",
        "      # retain locations only for the city of San Francisco\n",
        "      \n",
        "  cols = ['start_station_latitude', 'start_station_longitude','end_station_latitude','end_station_longitude']\n",
        "  df[cols] = df[cols].round(6)\n",
        "      \n",
        "  df['duration_min'] = df['duration_sec'] // 60\n",
        "\n",
        "  df['bike_share_for_all_trip'] = df['bike_share_for_all_trip'].fillna('No')\n",
        "\n",
        "  # Create a categorical variable to distinguish between Docked-to-Docked, Dockless-to-Docked, Docked-to-Dockless, and Dockless-to-Dockless trips\n",
        "  df['trip_type'] = ''\n",
        "  df.loc[(df['start_station_id'].notnull() & df['end_station_id'].notnull()),'trip_type'] = 'Docked-to-Docked'\n",
        "  df.loc[(df['start_station_id'].notnull() & df['end_station_id'].isnull()),'trip_type'] = 'Docked-to-Dockless'\n",
        "  df.loc[(df['start_station_id'].isnull() & df['end_station_id'].notnull()),'trip_type'] = 'Dockless-to-Docked'\n",
        "  df.loc[(df['start_station_id'].isnull() & df['end_station_id'].isnull()),'trip_type'] = 'Dockless-to-Dockless'\n",
        "\n",
        "  df = df.sort_values(by='start_time')\n",
        "\n",
        "  ## ! Important: the formal ebike_trip designation has been changed to meet the 2020 dataset's schema\n",
        "  ## It is now referred to as rideable_type with outputs either as classic_bike or electric_bike.\n",
        "  df['rideable_type'] = \"\"\n",
        "  df.loc[df.trip_type == 'Docked-to-Docked', 'rideable_type'] = \"classic_bike\" # A \"No\" means \"Classic Bike\";\n",
        "  ## I am also assuming that all Docked trips occur via Classic Bikes, although they could also include eBikes\n",
        "  df.loc[df.trip_type != 'Docked-to-Docked', 'rideable_type'] = \"electric_bike\"\n",
        "  df['est_cost'] = \"\"\n",
        "  df['est_cost'] = pd.to_numeric(df['est_cost'], downcast='integer')\n",
        "\n",
        "  df = df.drop_duplicates() # drops duplicated rows (precautionary wrangling)\n",
        "\n",
        "  return df\n",
        "##########################################################################################################################################\n",
        "##########################################################################################################################################\n",
        "def cleaning2020(df):\n",
        "\n",
        "  columns = ['start_time','end_time','started_at','ended_at']\n",
        "  for column in columns:\n",
        "    df[column] = pd.to_datetime(df[column],dayfirst=True)       \n",
        "    df[column + str('_day')] = df[column].dt.day_name()\n",
        "    df[column + str('_month')] = df[column].dt.month_name()\n",
        "    df[column + str('_hour')] = df[column].dt.round('H').dt.hour\n",
        "    df[column + str('_year')] = df[column].dt.year\n",
        "  df = df.sort_values(by='start_time')\n",
        "\n",
        "  # This effectively removes the 3,342 rows with no data for end points.\n",
        "  df = df.loc[~((df['end_station_latitude'].isnull()) & (df['end_station_longitude'].isnull()) & (df['end_lat'].isnull()) & (df['end_lng'].isnull()))]\n",
        "  # Extracts all coordinates from one set of coordinate fields to another, to keep things standardized.\n",
        "  df['start_station_longitude'] = df['start_station_longitude'].fillna(df['start_lng'])\n",
        "  df['start_station_latitude'] = df['start_station_latitude'].fillna(df['start_lat'])\n",
        "  df['end_station_longitude'] = df['end_station_longitude'].fillna(df['end_lng'])\n",
        "  df['end_station_latitude'] = df['end_station_latitude'].fillna(df['end_lat'])\n",
        "  # Ditto with the start time/end time fields.\n",
        "  df['start_time'] = df['start_time'].fillna(df['started_at'])\n",
        "  df['end_time'] = df['end_time'].fillna(df['ended_at'])\n",
        "  # duration_sec has missing values; recalc from reformatted time fields\n",
        "  df['duration_sec'] = (df['end_time'] - df['start_time']).dt.total_seconds()\n",
        "  df['duration_min'] = df['duration_sec'] // 60\n",
        "  df = df[(df['duration_sec'] > 0)] ## native error in the raw dataset\n",
        "                      ## Some end times have an earlier timestamp than start times.\n",
        "  ## There are apparent errors in the start and end time fields, where some values seem to be flipped.\n",
        "      ## This leads to \"negative\" duration. Not an artifact of wrangling or operations, but of the raw data.\n",
        "  columns = ['fname','start_lat','start_lng','end_lat','end_lng','started_at','ended_at']\n",
        "  df = df.drop(columns,axis=1) # these columns are not needed\n",
        "  df = df[(df['start_station_latitude'] < 37.82) & (df['start_station_latitude'] > 37.7) & (df['start_station_longitude'] < -122.36) & (df['start_station_longitude'] > -122.52)]\n",
        "      # retain locations only for the city of San Francisco\n",
        "  cols = ['start_station_latitude', 'start_station_longitude','end_station_latitude','end_station_longitude']\n",
        "  df[cols] = df[cols].round(6)\n",
        "\n",
        "  df['trip_type'] = ''\n",
        "\n",
        "  df.loc[(df['start_station_id'].notnull() & df['end_station_id'].notnull()),'trip_type'] = 'Docked-to-Docked'\n",
        "  df.loc[(df['start_station_id'].notnull() & df['end_station_id'].isnull()),'trip_type'] = 'Docked-to-Dockless'\n",
        "  df.loc[(df['start_station_id'].isnull() & df['end_station_id'].notnull()),'trip_type'] = 'Dockless-to-Docked'\n",
        "  df.loc[(df['start_station_id'].isnull() & df['end_station_id'].isnull()),'trip_type'] = 'Dockless-to-Dockless'\n",
        "\n",
        "  df['est_cost'] = \"\"\n",
        "  df['est_cost'] = pd.to_numeric(df['est_cost'], downcast='integer')\n",
        "\n",
        "  # df['is_equity'] = df['is_equity'].fillna(value=True)\n",
        "\n",
        "  ## Missing user_type data.\n",
        "  df.loc[df['member_casual'] == 'casual', 'user_type'] = \"Customer\" # casual = customer (one time purchase);\n",
        "  df.loc[df['member_casual'] == 'member', 'user_type'] = \"Subscriber\" # member = subscriber (membership holder);\n",
        "\n",
        "  df = df.drop_duplicates()\n",
        "\n",
        "  return df\n",
        "##########################################################################################################################################\n",
        "##########################################################################################################################################\n",
        "def cleaning2021(df):\n",
        "  df = df.rename(columns={'started_at': 'start_time','ended_at':'end_time','start_lat':'start_station_latitude',\n",
        "                     'start_lng':'start_station_longitude','end_lat':'end_station_latitude','end_lng':'end_station_longitude'})\n",
        "  \n",
        "  columns = ['start_time','end_time']\n",
        "  for column in columns:\n",
        "    df[column] = pd.to_datetime(df[column],dayfirst=True)       \n",
        "    df[column + str('_day')] = df[column].dt.day_name()\n",
        "    df[column + str('_month')] = df[column].dt.month_name()\n",
        "    df[column + str('_hour')] = df[column].dt.round('H').dt.hour\n",
        "    df[column + str('_year')] = df[column].dt.year\n",
        "\n",
        "  df = df.sort_values(by='start_time')\n",
        "\n",
        "  df = df[(df['start_station_latitude'] < 37.82) & (df['start_station_latitude'] > 37.7) & (df['start_station_longitude'] < -122.36) & (df['start_station_longitude'] > -122.52)]\n",
        "    # retain locations only for the city of San Francisco\n",
        "    \n",
        "  cols = ['start_station_latitude', 'start_station_longitude','end_station_latitude','end_station_longitude']\n",
        "  df[cols] = df[cols].round(6)\n",
        "      \n",
        "  df['duration_sec'] = (df['end_time'] - df['start_time']).dt.total_seconds()\n",
        "  df['duration_min'] = df['duration_sec'] // 60\n",
        "  df = df[(df['duration_sec'] > 0)]\n",
        "\n",
        "  df['trip_type'] = ''\n",
        "  df.loc[(df['start_station_id'].notnull() & df['end_station_id'].notnull()),'trip_type'] = 'Docked-to-Docked'\n",
        "  df.loc[(df['start_station_id'].notnull() & df['end_station_id'].isnull()),'trip_type'] = 'Docked-to-Dockless'\n",
        "  df.loc[(df['start_station_id'].isnull() & df['end_station_id'].notnull()),'trip_type'] = 'Dockless-to-Docked'\n",
        "  df.loc[(df['start_station_id'].isnull() & df['end_station_id'].isnull()),'trip_type'] = 'Dockless-to-Dockless'\n",
        "\n",
        "  df.loc[df['member_casual'] == 'casual', 'user_type'] = \"Customer\" \n",
        "  df.loc[df['member_casual'] == 'member', 'user_type'] = \"Subscriber\"\n",
        "\n",
        "  df = df.drop_duplicates() # drops duplicated rows (precautionary wrangling)\n",
        "  \n",
        "  return df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "d13hO6gtvmAf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "883afce4-158a-4d94-bc58-0d0150cf1823"
      },
      "source": [
        "df19 = cleaning2019(df19raw)\n",
        "df20 = cleaning2020(df20raw)\n",
        "df21 = cleaning2021(df21raw)\n",
        "\n",
        "print(\"For the 2019 dataset, we have dropped \" + str(len(df19raw) - len(df19)) + \" rows, about \" + str(100 - (100 * (len(df19) / len(df19raw)))) + \"% from the original raw dataset.\")\n",
        "print(\"For the 2020 dataset, we have dropped \" + str(len(df20raw) - len(df20)) + \" rows, about \" + str(100 - (100 * (len(df20) / len(df20raw)))) + \"% from the original raw dataset.\")\n",
        "print(\"For the 2021 dataset, we have dropped \" + str(len(df21raw) - len(df21)) + \" rows, about \" + str(100 - (100 * (len(df21) / len(df21raw)))) + \"% from the original raw dataset.\")"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "For the 2019 dataset, we have dropped 654044 rows, about 26.088888516595446% from the original raw dataset.\n",
            "For the 2020 dataset, we have dropped 389649 rows, about 18.177683168677106% from the original raw dataset.\n",
            "For the 2021 dataset, we have dropped 217101 rows, about 14.541356606541228% from the original raw dataset.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zL4RCvVAw7p1"
      },
      "source": [
        "## Feature Engineering\n",
        "---\n",
        "### Objectives\n",
        "\n",
        "*   Add geospatial feature attribute through spatial intersection with georeferenced datasets;\n",
        "*   Estimate the revenue generated for each trip;\n",
        "*   Assign categorical values for each trip.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pupODYRoFWLx"
      },
      "source": [
        "### Generating New Geospatial Attributes"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Vq331E8LxYuE"
      },
      "source": [
        "fname = 'drive/MyDrive/Gcolab/rawdata/DataSF/SF_N.geojson' # Your filepath here\n",
        "poly = gpd.read_file(fname)\n",
        "# print(type(poly))\n",
        "# poly.head()\n",
        "\n",
        "def pythonicGIS(df):\n",
        "  # I am intersecting a GeoJSON of San Francisco's neighborhoods (polygon units) for each trip's starting and ending datapoints' location; hence the multiple spatial joins\n",
        "  df_startN = gpd.GeoDataFrame(\n",
        "  df, crs='EPSG:4326',\n",
        "      geometry=gpd.points_from_xy(df.start_station_longitude, df.start_station_latitude))\n",
        "  type(df_startN) # check here if the conversion worked\n",
        "  df_startN_int = gpd.sjoin(df_startN,poly,how=\"inner\", predicate='intersects')\n",
        "  df_startN_int = df_startN_int.rename(columns={'name': 'start_neigh'})\n",
        "\n",
        "  del df_startN_int['index_right']\n",
        "\n",
        "  df_endN = gpd.GeoDataFrame(\n",
        "  df_startN_int, crs='EPSG:4326',\n",
        "      geometry=gpd.points_from_xy(df_startN_int.end_station_longitude, df_startN_int.end_station_latitude))\n",
        "  type(df_endN) # check here if the conversion worked\n",
        "  df_endN_int = gpd.sjoin(df_endN,poly,how=\"inner\", predicate='intersects')\n",
        "  df_endN_int = df_endN_int.rename(columns={'name': 'end_neigh'})\n",
        "\n",
        "  del df_endN_int['index_right']\n",
        "\n",
        "  df = df_endN_int\n",
        "\n",
        "  # list_cap = ['Sutro Heights','Outer Richmond','Cayuga','Mission Terace','Excelsior','University Mound','Portola','McLaren Park','Sunnydale',\n",
        "  #           'Visitacion Valley','Candlestick Point SRA','Hunters Point','Bayview','India Bassin','Silver Terrace',\n",
        "  #           'Apparel City','Produce Market','Bret Harte']\n",
        "\n",
        "  # list_exemption = ['Seacliff','Outer Richmond','University Mound','Portola','Sunnydale','Visitacion Valley',\n",
        "  #             'Candlestick Point SRA','Hunters Point','Apparel City','Bernal Heights',\n",
        "  #             'Holly Park','Excelsior','McLaren Park'] ## Approximation from the service maps shown on Bay Wheels\n",
        "\n",
        "  # df.loc[df['end_neigh'].isin(list_cap), 'ebike_cap'] = 'Yes'\n",
        "  # df.loc[df['start_neigh'].isin(list_cap), 'ebike_cap'] = 'Yes'\n",
        "  # df.loc[df['end_neigh'].isin(list_exemption), 'ebike_exemption'] = 'Yes'\n",
        "  # df['ebike_cap'] = df['ebike_cap'].fillna(value='No')\n",
        "  # df['ebike_exemption'] = df['ebike_exemption'].fillna(value='No')\n",
        "\n",
        "  return df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dXncs2LT4SDU",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 68
        },
        "outputId": "f202a180-8cf5-4514-d6ac-f366420ec1da"
      },
      "source": [
        "df19 = pythonicGIS(df19)\n",
        "display(df19.shape)\n",
        "df20 = pythonicGIS(df20)\n",
        "display(df20.shape)\n",
        "df21 = pythonicGIS(df21)\n",
        "display(df21.shape)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(1852712, 30)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(1732467, 40)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(1253169, 29)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c9xlP7kG0YLh"
      },
      "source": [
        "### Estimating Revenue\n",
        "\n",
        "This is where things got dense, since the fees changed in mid-2020. Therefore, I couldn't simply run a single helper function, but had to tailor for \"pre-2020\" and \"post-2020\" fee changes.\n",
        "---\n",
        "#### 2019 Pricing Scheme\n",
        "\n",
        "Bay Wheels' rates for e-bikes changed on [March 2nd, 2020](https://medium.com/@baywheels/sf-ebike-pricing-details-1b97667f1e5a), from the [2019](https://www.tunneltime.io/san-francisco-usa/bay-wheels) rates:\n",
        "\n",
        "<img width=\"200\" height=\"183\" src=\"https://github.com/HP-Nunes/SMCapstone_GColab/blob/main/drive/MyDrive/Gcolab/2019pricing.png?raw=1\">\n",
        "\n",
        "Bay Wheels also include an annual [Bike Share for All](https://www.lyft.com/bikes/bay-wheels/bike-share-for-all) (BSFA) membership plan for low-income Bay Area residents.\n",
        "---\n",
        "#### 2020 Pricing Scheme\n",
        "\n",
        "Bay Wheels' rates for e-bikes changed on [March 2nd, 2020](https://medium.com/@baywheels/sf-ebike-pricing-details-1b97667f1e5a), to the [following](https://www.lyft.com/bikes/bay-wheels/pricing):\n",
        "\n",
        "<img width=\"200\" height=\"183\" src=\"https://github.com/HP-Nunes/SMCapstone_GColab/blob/main/drive/MyDrive/Gcolab/2020pricing.png?raw=1\">\n",
        "\n",
        "\n",
        "Bay Wheels also include an annual [Bike Share for All](https://www.lyft.com/bikes/bay-wheels/bike-share-for-all) (BSFA) membership plan for low-income Bay Area residents."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zex2Jigyb_iX"
      },
      "source": [
        "#### Helper Functions: estimating trip revenue"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ANunpc59yCnl"
      },
      "source": [
        "## 2019 fare estimates\n",
        "def revenueEST_2019(df):\n",
        "\n",
        "    conditions  = [\n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] == 'classic_bike') & (df['duration_sec'] <= 1800) \n",
        "        & (df['bike_share_for_all_trip'] == 'No') # 324360\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] == 'classic_bike') & (df['duration_sec'] > 1800)\n",
        "        & (df['bike_share_for_all_trip'] == 'No') # 48725\n",
        "        ),\n",
        "        (\n",
        "        (df['bike_share_for_all_trip'] == 'Yes') & (df['rideable_type'] == 'classic_bike') & (df['duration_sec'] <= 3600)\n",
        "        ), # 57049\n",
        "        (\n",
        "        (df['bike_share_for_all_trip'] == 'Yes') & (df['rideable_type'] == 'classic_bike') & (df['duration_sec'] > 3600)\n",
        "        ), # 784\n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 900)\n",
        "        & (df['bike_share_for_all_trip'] == 'No') # 5501\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 900)\n",
        "        & (df['bike_share_for_all_trip'] == 'No') # 1466\n",
        "        ),\n",
        "        (\n",
        "        (df['bike_share_for_all_trip'] == 'Yes') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 3600) # 0\n",
        "        ), #0\n",
        "        (\n",
        "        (df['bike_share_for_all_trip'] == 'Yes') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 3600) # 0\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'classic_bike') & (df['duration_sec'] <= 2700) \n",
        "        & (df['bike_share_for_all_trip'] == 'No') # 1355375\n",
        "        ), \n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'classic_bike') & (df['duration_sec'] > 2700)\n",
        "        & (df['bike_share_for_all_trip'] == 'No') # 7588\n",
        "        ), \n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 2700)\n",
        "        & (df['bike_share_for_all_trip'] == 'No') # 50255\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 2700)\n",
        "        & (df['bike_share_for_all_trip'] == 'No') # 1180\n",
        "        ),\n",
        "    ]\n",
        "  \n",
        "    overhead_base_customer_classicBike1 = 2\n",
        "    overhead_time_customer_classicBike2 = 3 * (abs(((df['duration_sec'] // 900)) - 2))\n",
        "    overhead_base_BSFA_classicBike3 = 0\n",
        "    overhead_time_BSFA_classicBike4 = 2 * (abs(((df['duration_sec'] // 900)) - 4))\n",
        "    overhead_base_customer_ebike5 = 2 # $2 for the firt 15 min.\n",
        "    overhead_time_customer_ebike6   = 2 + (3 * (abs(((df['duration_sec'] // 900)) - 1))) # $2 + $3/15 min.\n",
        "    overhead_base_BSFA_ebike_NoCaps15 = 0 # No charges within the first 60 mins\n",
        "    overhead_time_BSFA_ebike_NoCaps16 = (3 * (abs(((df['duration_sec'] // 900)) - 4))) # $3 every 15 mins. after 60 mins.\n",
        "  \n",
        "    overhead_base_subscriber_classicBike17 = 0\n",
        "    overhead_time_subscriber_classicBike18 = 3 * (abs(((df['duration_sec'] // 900)) - 3))\n",
        "  \n",
        "    overhead_base_subscriber_ebike_NoCaps_ParkingFee25 = 0 # No charges within the first 45 mins\n",
        "  \n",
        "    overhead_time_subscriber_ebike_NoCaps_ParkingFee26 = (3 * (abs(((df['duration_sec'] // 900)) - 3))) # $3 every 15 mins. after 45 mins.\n",
        "  \n",
        "    choices = [\n",
        "    overhead_base_customer_classicBike1,overhead_time_customer_classicBike2,overhead_base_BSFA_classicBike3,overhead_time_BSFA_classicBike4,\n",
        "    overhead_base_customer_ebike5,overhead_time_customer_ebike6,\n",
        "    overhead_base_BSFA_ebike_NoCaps15,overhead_time_BSFA_ebike_NoCaps16,\n",
        "    overhead_base_subscriber_classicBike17,overhead_time_subscriber_classicBike18,\n",
        "    overhead_base_subscriber_ebike_NoCaps_ParkingFee25,overhead_time_subscriber_ebike_NoCaps_ParkingFee26\n",
        "    ]\n",
        "  \n",
        "    df[\"est_cost\"] = np.select(conditions, choices, default=0).round(2)\n",
        "  \n",
        "  \n",
        "    return df\n",
        "####################################################################################################################################################################\n",
        "####################################################################################################################################################################\n",
        "## pre-2020 fare estimates\n",
        "def revenueEST_pre2020(df):\n",
        "\n",
        "  df_oldrates = df[df['start_time'].dt.strftime('%Y-%m-%d') < '2020-03-02']\n",
        "\n",
        "  df_oldrates.loc[df_oldrates['trip_type'] == 'Docked-to-Docked', 'rideable_type'] = 'classic_bike'\n",
        "  df_oldrates.loc[df_oldrates['trip_type'] != 'Docked-to-Docked', 'rideable_type'] = 'electric_bike'\n",
        "\n",
        "  conditions  = [\n",
        "      (\n",
        "      (df_oldrates['user_type'] == 'Customer') & (df_oldrates['rideable_type'] == 'classic_bike') & (df_oldrates['duration_sec'] <= 1800) \n",
        "      ),\n",
        "      (\n",
        "      (df_oldrates['user_type'] == 'Customer') & (df_oldrates['rideable_type'] == 'classic_bike') & (df_oldrates['duration_sec'] > 1800)\n",
        "      ),\n",
        "      (\n",
        "      (df_oldrates['rideable_type'] == 'classic_bike') & (df_oldrates['duration_sec'] <= 3600)\n",
        "      ), # 57049\n",
        "      (\n",
        "      (df_oldrates['rideable_type'] == 'classic_bike') & (df_oldrates['duration_sec'] > 3600)\n",
        "      ), # 784\n",
        "      (\n",
        "      (df_oldrates['user_type'] == 'Customer') & (df_oldrates['rideable_type'] == 'electric_bike') & (df_oldrates['duration_sec'] <= 900)\n",
        "      ),\n",
        "      (\n",
        "      (df_oldrates['user_type'] == 'Customer') & (df_oldrates['rideable_type'] == 'electric_bike') & (df_oldrates['duration_sec'] > 900)\n",
        "      ),\n",
        "      (\n",
        "      (df_oldrates['rideable_type'] == 'electric_bike') & (df_oldrates['duration_sec'] <= 3600) # 0\n",
        "      ), #0\n",
        "      (\n",
        "      (df_oldrates['rideable_type'] == 'electric_bike') & (df_oldrates['duration_sec'] > 3600) # 0\n",
        "      ),\n",
        "      (\n",
        "      (df_oldrates['user_type'] == 'Subscriber') & (df_oldrates['rideable_type'] == 'classic_bike') & (df_oldrates['duration_sec'] <= 2700) \n",
        "      ), \n",
        "      (\n",
        "      (df_oldrates['user_type'] == 'Subscriber') & (df_oldrates['rideable_type'] == 'classic_bike') & (df_oldrates['duration_sec'] > 2700)\n",
        "      ), \n",
        "      (\n",
        "      (df_oldrates['user_type'] == 'Subscriber') & (df_oldrates['rideable_type'] == 'electric_bike') & (df_oldrates['duration_sec'] <= 2700)\n",
        "      ),\n",
        "      (\n",
        "      (df_oldrates['user_type'] == 'Subscriber') & (df_oldrates['rideable_type'] == 'electric_bike') & (df_oldrates['duration_sec'] > 2700)\n",
        "      ),\n",
        "  ]\n",
        "\n",
        "  overhead_base_customer_classicBike1 = 2\n",
        "  overhead_time_customer_classicBike2 = 3 * (abs(((df_oldrates['duration_sec'] // 900)) - 2))\n",
        "  overhead_base_BSFA_classicBike3 = 0\n",
        "  overhead_time_BSFA_classicBike4 = 2 * (abs(((df_oldrates['duration_sec'] // 900)) - 4))\n",
        "  overhead_base_customer_ebike5 = 2 # $2 for the firt 15 min.\n",
        "  overhead_time_customer_ebike6   = 2 + (3 * (abs(((df_oldrates['duration_sec'] // 900)) - 1))) # $2 + $3/15 min.\n",
        "  overhead_base_BSFA_ebike_NoCaps15 = 0 # No charges within the first 60 mins\n",
        "  overhead_time_BSFA_ebike_NoCaps16 = (3 * (abs(((df_oldrates['duration_sec'] // 900)) - 4))) # $3 every 15 mins. after 60 mins.\n",
        "\n",
        "  overhead_base_subscriber_classicBike17 = 0\n",
        "  overhead_time_subscriber_classicBike18 = 3 * (abs(((df_oldrates['duration_sec'] // 900)) - 3))\n",
        "\n",
        "  overhead_base_subscriber_ebike_NoCaps_ParkingFee25 = 0 # No charges within the first 45 mins\n",
        "\n",
        "  overhead_time_subscriber_ebike_NoCaps_ParkingFee26 = (3 * (abs(((df_oldrates['duration_sec'] // 900)) - 3))) # $3 every 15 mins. after 45 mins.\n",
        "\n",
        "  choices = [\n",
        "  overhead_base_customer_classicBike1,overhead_time_customer_classicBike2,overhead_base_BSFA_classicBike3,overhead_time_BSFA_classicBike4,\n",
        "  overhead_base_customer_ebike5,overhead_time_customer_ebike6,\n",
        "  overhead_base_BSFA_ebike_NoCaps15,overhead_time_BSFA_ebike_NoCaps16,\n",
        "  overhead_base_subscriber_classicBike17,overhead_time_subscriber_classicBike18,\n",
        "  overhead_base_subscriber_ebike_NoCaps_ParkingFee25,overhead_time_subscriber_ebike_NoCaps_ParkingFee26\n",
        "  ]\n",
        "\n",
        "  df_oldrates[\"est_cost\"] = np.select(conditions, choices, default=0).round(2)\n",
        "\n",
        "  return df_oldrates\n",
        "####################################################################################################################################################################\n",
        "####################################################################################################################################################################\n",
        "## post-2020 fare estimates\n",
        "def revenueEST_post2020(df):\n",
        "\n",
        "  df_newrates = df[df['start_time'].dt.strftime('%Y-%m-%d') >= '2020-03-02']\n",
        "\n",
        "  df_newrates.loc[(df_newrates['trip_type'] == 'Dock-to-Dock') & (df_newrates['rideable_type'].isnull()),'rideable_type'] = 'classic_bike'\n",
        "  df_newrates.loc[(df_newrates['trip_type'] != 'Dock-to-Dock') & (df_newrates['rideable_type'].isnull()),'rideable_type'] = 'electric_bike'\n",
        "\n",
        "  list_cap = ['Sutro Heights','Outer Richmond','Cayuga','Mission Terace','Excelsior','University Mound','Portola','McLaren Park','Sunnydale',\n",
        "         'Visitacion Valley','Candlestick Point SRA','Hunters Point','Bayview','India Bassin','Silver Terrace',\n",
        "         'Apparel City','Produce Market','Bret Harte']\n",
        "\n",
        "  list_exemption = ['Seacliff','Outer Richmond','University Mound','Portola','Sunnydale','Visitacion Valley',\n",
        "            'Candlestick Point SRA','Hunters Point','Apparel City','Bernal Heights',\n",
        "            'Holly Park','Excelsior','McLaren Park'] ## Approximation from the service maps shown on Bay Wheels\n",
        "\n",
        "  df_newrates.loc[df_newrates['end_neigh'].isin(list_cap), 'ebike_cap'] = 'Yes'\n",
        "  df_newrates.loc[df_newrates['start_neigh'].isin(list_cap), 'ebike_cap'] = 'Yes'\n",
        "  df_newrates.loc[df_newrates['end_neigh'].isin(list_exemption), 'ebike_exemption'] = 'Yes'\n",
        "  df_newrates['ebike_cap'] = df_newrates['ebike_cap'].fillna(value='No')\n",
        "  df_newrates['ebike_exemption'] = df_newrates['ebike_exemption'].fillna(value='No')\n",
        "  df_newrates['is_equity'] = df_newrates['is_equity'].fillna(value=True)\n",
        "            \n",
        "  conditions  = [\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Customer') & (df_newrates['rideable_type'] != 'electric_bike') & (df_newrates['duration_sec'] <= 1800) \n",
        "      & (df_newrates['is_equity'] == False) # 324360\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Customer') & (df_newrates['rideable_type'] != 'electric_bike') & (df_newrates['duration_sec'] > 1800)\n",
        "      & (df_newrates['is_equity'] == False) # 48725\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] != 'electric_bike') & (df_newrates['duration_sec'] <= 3600)\n",
        "      ), # 57049\n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] != 'electric_bike') & (df_newrates['duration_sec'] > 3600)\n",
        "      ), # 784\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Customer') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 1800)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_exemption'] == 'Yes') # 102\n",
        "      ), ## !! ORIGINAL QUERY (EDITED ABOVE) !! \n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Customer') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 1800)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_exemption'] == 'Yes') # 26\n",
        "      ), ## !! ORIGINAL QUERY (EDITED ABOVE) !!   \n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Customer') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 1800)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_exemption'] == 'No') \n",
        "      & ((df_newrates['trip_type'] == 'Dockless_to_Dockless') | (df_newrates['trip_type'] == 'Docked-to-Dockless')) # 5011\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Customer') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 1800)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_exemption'] == 'No') \n",
        "      & ((df_newrates['trip_type'] == 'Dockless_to_Dockless') | (df_newrates['trip_type'] == 'Docked-to-Dockless')) # 1331\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 3600)\n",
        "      & (df_newrates['ebike_cap'] == 'Yes')  & (df_newrates['ebike_exemption'] == 'Yes') # 0\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 3600)\n",
        "      & (df_newrates['ebike_cap'] == 'Yes')  & (df_newrates['ebike_exemption'] == 'Yes') # 0\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 3600)\n",
        "      & (df_newrates['ebike_cap'] == 'Yes')  & (df_newrates['ebike_exemption'] == 'No')  # 0\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 3600)\n",
        "      & (df_newrates['ebike_cap'] == 'Yes')  & (df_newrates['ebike_exemption'] == 'No')  # 0\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 3600)\n",
        "      & (df_newrates['ebike_cap'] == 'No')  & (df_newrates['ebike_exemption'] == 'No')  # 0\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 3600)\n",
        "      & (df_newrates['ebike_cap'] == 'No')  & (df_newrates['ebike_exemption'] == 'No') # 0\n",
        "      ),   \n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 3600)\n",
        "      & (df_newrates['ebike_cap'] == 'No')  & (df_newrates['ebike_exemption'] == 'Yes') # 0\n",
        "      ), ## !! ORIGINAL QUERY (EDITED ABOVE) !! \n",
        "      (\n",
        "      (df_newrates['is_equity'] == True) & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 3600)\n",
        "      & (df_newrates['ebike_cap'] == 'No')  & (df_newrates['ebike_exemption'] == 'Yes')  # 0\n",
        "      ), ## !! ORIGINAL QUERY (EDITED ABOVE) !! \n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] != 'electric_bike') & (df_newrates['duration_sec'] <= 2700) \n",
        "      & (df_newrates['is_equity'] == False) # 1355375\n",
        "      ), \n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] != 'electric_bike') & (df_newrates['duration_sec'] > 2700)\n",
        "      & (df_newrates['is_equity'] == False) # 7588\n",
        "      ),   \n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 2700)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_cap'] == 'Yes') & (df_newrates['ebike_exemption'] == 'Yes') #160\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 2700)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_cap'] == 'Yes') & (df_newrates['ebike_exemption'] == 'Yes') # 19\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 2700)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_cap'] == 'Yes') & (df_newrates['ebike_exemption'] == 'No')\n",
        "      & ((df_newrates['trip_type'] == 'Dockless_to_Dockless') | (df_newrates['trip_type'] == 'Docked-to-Dockless')) # 358\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 2700)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_cap'] == 'Yes') & (df_newrates['ebike_exemption'] == 'No')\n",
        "      & ((df_newrates['trip_type'] == 'Dockless_to_Dockless') | (df_newrates['trip_type'] == 'Docked-to-Dockless')) # 24\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 2700)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_cap'] == 'No') & (df_newrates['ebike_exemption'] == 'Yes') # 573\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 2700)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_cap'] == 'No') & (df_newrates['ebike_exemption'] == 'Yes') #11\n",
        "      ),\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] <= 2700)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_cap'] == 'No') & (df_newrates['ebike_exemption'] == 'No')\n",
        "      & ((df_newrates['trip_type'] == 'Dockless_to_Dockless') | (df_newrates['trip_type'] == 'Docked-to-Dockless')) #39428\n",
        "      ),  ## !! ORIGINAL QUERY (EDITED ABOVE) !!\n",
        "      (\n",
        "      (df_newrates['user_type'] == 'Subscriber') & (df_newrates['rideable_type'] == 'electric_bike') & (df_newrates['duration_sec'] > 2700)\n",
        "      & (df_newrates['is_equity'] == False) & (df_newrates['ebike_cap'] == 'No') & (df_newrates['ebike_exemption'] == 'No')\n",
        "      & ((df_newrates['trip_type'] == 'Dockless_to_Dockless') | (df_newrates['trip_type'] == 'Docked-to-Dockless')) # 1025\n",
        "      ),  ## !! ORIGINAL QUERY (EDITED ABOVE) !!\n",
        "  ]\n",
        "\n",
        "  overhead_base_customer_classicBike1 = 2 # 573384\n",
        "  overhead_time_customer_classicBike2 = 3 * (abs(((df_newrates['duration_sec'] // 900)) - 2)) # 2027670\n",
        "  overhead_base_BSFA_classicBike3 = 0\n",
        "  overhead_time_BSFA_classicBike4 = 2 * (abs(((df_newrates['duration_sec'] // 900)) - 4))\n",
        "  overhead_base_customer_ebike5 = 2 + (0.20 * (df_newrates['duration_sec'] / 60)) # Post-March 2020 Pricing Scheme\n",
        "  overhead_time_customer_ebike6   = 5 + (3 * (abs(((df_newrates['duration_sec'] // 900)) - 2))) # Post-March 2020 Pricing Scheme # A maximum of $5 can be charged within 30 mins.\n",
        "  overhead_base_customer_ebike_ParkingFee7 = 4 + (0.20 * (df_newrates['duration_sec'] / 60)) # Parking Fee adds a $2 surcharge\n",
        "  overhead_time_customer_ebike_ParkingFee8  = 7 + (3 * (abs(((df_newrates['duration_sec'] // 900)) - 2))) # Parking Fee adds a $2 surcharge\n",
        "  overhead_base_BSFA_ebike_Capped9 = 0.05 * (np.clip(df_newrates['duration_sec'],0,1200)) # Capped at $1, at a rate of 0.05 cent/min., that represents 20 minutes, or 600 seconds\n",
        "  overhead_time_BSFA_ebike_Capped10 = 1 + (2 * (abs(((df_newrates['duration_sec'] // 900)) - 4))) # Capped at $1\n",
        "  overhead_base_BSFA_ebike_Capped_ParkingFee11 = 2 + (0.05 * ((np.clip(df_newrates['duration_sec'],0,1200)) / 60)) # Parking Fee adds a $2 surcharge\n",
        "  overhead_time_BSFA_ebike_Capped_ParkingFee12 = 3 + (2 * (abs(((df_newrates['duration_sec'] // 900)) - 4))) # Parking Fee adds a $2 surcharge\n",
        "  overhead_base_BSFA_ebike_NoCaps_ParkingFee13 = 2 + (0.05 * (df_newrates['duration_sec'] / 60))\n",
        "  overhead_time_BSFA_ebike_NoCaps_ParkingFee14 = 5 + (2 * (abs(((df_newrates['duration_sec'] // 900)) - 4))) # Parking Fee adds a $2 surcharge + the maximum riding fee that can be charged within 1 hours with no caps is $3\n",
        "  overhead_base_BSFA_ebike_NoCaps15 = (0.05 * (df_newrates['duration_sec'] / 60)) # Post-March 2020 Pricing Scheme\n",
        "  overhead_time_BSFA_ebike_NoCaps16 = 3 + (2 * (abs(((df_newrates['duration_sec'] // 900)) - 4))) # Post-March 2020 Pricing Scheme\n",
        "  overhead_base_subscriber_classicBike17 = 0\n",
        "  overhead_time_subscriber_classicBike18 = 3 * (abs(((df_newrates['duration_sec'] // 900)) - 3))\n",
        "  overhead_base_subscriber_ebike_Capped19 = 0.15 * ((np.clip(df_newrates['duration_sec'],0,800) / 60)) # Capped at $2, at a rate of 0.15 cent/min., that represents approx. 13.3 minutes, or rounded to 800 seconds\n",
        "  overhead_time_subscriber_ebike_Capped20 = 2 + (2 * (abs(((df_newrates['duration_sec'] // 900)) - 3))) # Capped at $2 within the first 45 mins.\n",
        "  overhead_base_subscriber_ebike_Capped_ParkingFee21 = 2 + (0.15 * ((np.clip(df_newrates['duration_sec'],0,800) / 60)))\n",
        "  overhead_time_subscriber_ebike_Capped_ParkingFee22 = 4 + (2 * (abs(((df_newrates['duration_sec'] // 900)) - 3))) # Parking Fee adds a $2 surcharge\n",
        "  overhead_base_subscriber_ebike_NoCaps23 = 0.15 * ((df_newrates['duration_sec'] / 60))\n",
        "  overhead_time_subscriber_ebike_NoCaps24 = 6.75 + (2 * (abs(((df_newrates['duration_sec'] // 900)) - 3))) # A maximum of $6.75 can be charged within 45 mins\n",
        "  overhead_base_subscriber_ebike_NoCaps_ParkingFee25 = 2 + (0.15 * ((df_newrates['duration_sec'] / 60))) # Post-March 2020 Pricing Scheme # Parking Fee adds a $2 surcharge\n",
        "  overhead_time_subscriber_ebike_NoCaps_ParkingFee26 = 8.75 + (2 * (abs(((df_newrates['duration_sec'] // 900)) - 3))) # Post-March 2020 Pricing Scheme # Parking Fee adds a $2 surcharge \n",
        "\n",
        "  choices = [\n",
        "  overhead_base_customer_classicBike1,overhead_time_customer_classicBike2,overhead_base_BSFA_classicBike3,overhead_time_BSFA_classicBike4,\n",
        "  overhead_base_customer_ebike5,overhead_time_customer_ebike6,\n",
        "  overhead_base_customer_ebike_ParkingFee7,overhead_time_customer_ebike_ParkingFee8,\n",
        "  overhead_base_BSFA_ebike_Capped9,overhead_time_BSFA_ebike_Capped10,overhead_base_BSFA_ebike_Capped_ParkingFee11,\n",
        "  overhead_time_BSFA_ebike_Capped_ParkingFee12,overhead_base_BSFA_ebike_NoCaps_ParkingFee13,overhead_time_BSFA_ebike_NoCaps_ParkingFee14,\n",
        "  overhead_base_BSFA_ebike_NoCaps15,overhead_time_BSFA_ebike_NoCaps16,\n",
        "  overhead_base_subscriber_classicBike17,overhead_time_subscriber_classicBike18,\n",
        "  overhead_base_subscriber_ebike_Capped19,overhead_time_subscriber_ebike_Capped20,overhead_base_subscriber_ebike_Capped_ParkingFee21,\n",
        "  overhead_time_subscriber_ebike_Capped_ParkingFee22,overhead_base_subscriber_ebike_NoCaps23,overhead_time_subscriber_ebike_NoCaps24,\n",
        "  overhead_base_subscriber_ebike_NoCaps_ParkingFee25,overhead_time_subscriber_ebike_NoCaps_ParkingFee26\n",
        "  ]\n",
        "\n",
        "  df_newrates[\"est_cost\"] = np.select(conditions, choices, default=0).round(2)\n",
        "\n",
        "  return df_newrates\n",
        "####################################################################################################################################################################\n",
        "####################################################################################################################################################################\n",
        "## Nearly identical to the post-2020 fare estimates function, but with the \"equity\" variable removed.\n",
        "def revenueEST_2021(df):  \n",
        "\n",
        "  list_cap = ['Sutro Heights','Outer Richmond','Cayuga','Mission Terace','Excelsior','University Mound','Portola','McLaren Park','Sunnydale',\n",
        "         'Visitacion Valley','Candlestick Point SRA','Hunters Point','Bayview','India Bassin','Silver Terrace',\n",
        "         'Apparel City','Produce Market','Bret Harte']\n",
        "\n",
        "  list_exemption = ['Seacliff','Outer Richmond','University Mound','Portola','Sunnydale','Visitacion Valley',\n",
        "            'Candlestick Point SRA','Hunters Point','Apparel City','Bernal Heights',\n",
        "            'Holly Park','Excelsior','McLaren Park'] ## Approximation from the service maps shown on Bay Wheels\n",
        "\n",
        "  df.loc[df['end_neigh'].isin(list_cap), 'ebike_cap'] = 'Yes'\n",
        "  df.loc[df['start_neigh'].isin(list_cap), 'ebike_cap'] = 'Yes'\n",
        "  df.loc[df['end_neigh'].isin(list_exemption), 'ebike_exemption'] = 'Yes'\n",
        "  df['ebike_cap'] = df['ebike_cap'].fillna(value='No')\n",
        "  df['ebike_exemption'] = df['ebike_exemption'].fillna(value='No')\n",
        "\n",
        "\n",
        "  conditions  = [\n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] != 'electric_bike') & (df['duration_sec'] <= 1800) \n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] != 'electric_bike') & (df['duration_sec'] > 1800)\n",
        "        ),\n",
        "        (\n",
        "        (df['rideable_type'] != 'electric_bike') & (df['duration_sec'] <= 3600)\n",
        "        ), # 57049\n",
        "        (\n",
        "        (df['rideable_type'] != 'electric_bike') & (df['duration_sec'] > 3600)\n",
        "        ), # 784\n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 1800)\n",
        "        & (df['ebike_exemption'] == 'Yes') # 102\n",
        "        ), ## !! ORIGINAL QUERY (EDITED ABOVE) !! \n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 1800)\n",
        "        & (df['ebike_exemption'] == 'Yes') # 26\n",
        "        ), ## !! ORIGINAL QUERY (EDITED ABOVE) !!   \n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 1800)\n",
        "        & (df['ebike_exemption'] == 'No') \n",
        "        & ((df['trip_type'] == 'Dockless_to_Dockless') | (df['trip_type'] == 'Docked-to-Dockless')) # 5011\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Customer') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 1800)\n",
        "        & (df['ebike_exemption'] == 'No') \n",
        "        & ((df['trip_type'] == 'Dockless_to_Dockless') | (df['trip_type'] == 'Docked-to-Dockless')) # 1331\n",
        "        ),\n",
        "        (\n",
        "        (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 3600)\n",
        "        & (df['ebike_cap'] == 'Yes')  & (df['ebike_exemption'] == 'Yes') # 0\n",
        "        ),\n",
        "        (\n",
        "        (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 3600)\n",
        "        & (df['ebike_cap'] == 'Yes')  & (df['ebike_exemption'] == 'Yes') # 0\n",
        "        ),\n",
        "        (\n",
        "        (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 3600)\n",
        "        & (df['ebike_cap'] == 'Yes')  & (df['ebike_exemption'] == 'No')  # 0\n",
        "        ),\n",
        "        (\n",
        "        (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 3600)\n",
        "        & (df['ebike_cap'] == 'Yes')  & (df['ebike_exemption'] == 'No')  # 0\n",
        "        ),\n",
        "        (\n",
        "        (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 3600)\n",
        "        & (df['ebike_cap'] == 'No')  & (df['ebike_exemption'] == 'No')  # 0\n",
        "        ),\n",
        "        (\n",
        "        (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 3600)\n",
        "        & (df['ebike_cap'] == 'No')  & (df['ebike_exemption'] == 'No') # 0\n",
        "        ),   \n",
        "        (\n",
        "        (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 3600)\n",
        "        & (df['ebike_cap'] == 'No')  & (df['ebike_exemption'] == 'Yes') # 0\n",
        "        ), ## !! ORIGINAL QUERY (EDITED ABOVE) !! \n",
        "        (\n",
        "        (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 3600)\n",
        "        & (df['ebike_cap'] == 'No')  & (df['ebike_exemption'] == 'Yes')  # 0\n",
        "        ), ## !! ORIGINAL QUERY (EDITED ABOVE) !! \n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] != 'electric_bike') & (df['duration_sec'] <= 2700) \n",
        "        ), \n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] != 'electric_bike') & (df['duration_sec'] > 2700)\n",
        "        ),   \n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 2700)\n",
        "        & (df['ebike_cap'] == 'Yes') & (df['ebike_exemption'] == 'Yes') #160\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 2700)\n",
        "        & (df['ebike_cap'] == 'Yes') & (df['ebike_exemption'] == 'Yes') # 19\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 2700)\n",
        "        & (df['ebike_cap'] == 'Yes') & (df['ebike_exemption'] == 'No')\n",
        "        & ((df['trip_type'] == 'Dockless_to_Dockless') | (df['trip_type'] == 'Docked-to-Dockless')) # 358\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 2700)\n",
        "        & (df['ebike_cap'] == 'Yes') & (df['ebike_exemption'] == 'No')\n",
        "        & ((df['trip_type'] == 'Dockless_to_Dockless') | (df['trip_type'] == 'Docked-to-Dockless')) # 24\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 2700)\n",
        "        & (df['ebike_cap'] == 'No') & (df['ebike_exemption'] == 'Yes') # 573\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 2700)\n",
        "        & (df['ebike_cap'] == 'No') & (df['ebike_exemption'] == 'Yes') #11\n",
        "        ),\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] <= 2700)\n",
        "        & (df['ebike_cap'] == 'No') & (df['ebike_exemption'] == 'No')\n",
        "        & ((df['trip_type'] == 'Dockless_to_Dockless') | (df['trip_type'] == 'Docked-to-Dockless')) #39428\n",
        "        ),  ## !! ORIGINAL QUERY (EDITED ABOVE) !!\n",
        "        (\n",
        "        (df['user_type'] == 'Subscriber') & (df['rideable_type'] == 'electric_bike') & (df['duration_sec'] > 2700)\n",
        "        & (df['ebike_cap'] == 'No') & (df['ebike_exemption'] == 'No')\n",
        "        & ((df['trip_type'] == 'Dockless_to_Dockless') | (df['trip_type'] == 'Docked-to-Dockless')) # 1025\n",
        "        ),  ## !! ORIGINAL QUERY (EDITED ABOVE) !!\n",
        "    ]\n",
        "  \n",
        "  overhead_base_customer_classicBike1 = 2 # 573384\n",
        "  overhead_time_customer_classicBike2 = 3 * (abs(((df['duration_sec'] // 900)) - 2)) # 2027670\n",
        "  overhead_base_BSFA_classicBike3 = 0\n",
        "  overhead_time_BSFA_classicBike4 = 2 * (abs(((df['duration_sec'] // 900)) - 4))\n",
        "  overhead_base_customer_ebike5 = 2 + (0.20 * (df['duration_sec'] / 60)) # Post-March 2020 Pricing Scheme\n",
        "  overhead_time_customer_ebike6   = 5 + (3 * (abs(((df['duration_sec'] // 900)) - 2))) # Post-March 2020 Pricing Scheme # A maximum of $5 can be charged within 30 mins.\n",
        "  overhead_base_customer_ebike_ParkingFee7 = 4 + (0.20 * (df['duration_sec'] / 60)) # Parking Fee adds a $2 surcharge\n",
        "  overhead_time_customer_ebike_ParkingFee8  = 7 + (3 * (abs(((df['duration_sec'] // 900)) - 2))) # Parking Fee adds a $2 surcharge\n",
        "  overhead_base_BSFA_ebike_Capped9 = 0.05 * (np.clip(df['duration_sec'],0,1200)) # Capped at $1, at a rate of 0.05 cent/min., that represents 20 minutes, or 600 seconds\n",
        "  overhead_time_BSFA_ebike_Capped10 = 1 + (2 * (abs(((df['duration_sec'] // 900)) - 4))) # Capped at $1\n",
        "  overhead_base_BSFA_ebike_Capped_ParkingFee11 = 2 + (0.05 * ((np.clip(df['duration_sec'],0,1200)) / 60)) # Parking Fee adds a $2 surcharge\n",
        "  overhead_time_BSFA_ebike_Capped_ParkingFee12 = 3 + (2 * (abs(((df['duration_sec'] // 900)) - 4))) # Parking Fee adds a $2 surcharge\n",
        "  overhead_base_BSFA_ebike_NoCaps_ParkingFee13 = 2 + (0.05 * (df['duration_sec'] / 60))\n",
        "  overhead_time_BSFA_ebike_NoCaps_ParkingFee14 = 5 + (2 * (abs(((df['duration_sec'] // 900)) - 4))) # Parking Fee adds a $2 surcharge + the maximum riding fee that can be charged within 1 hours with no caps is $3\n",
        "  overhead_base_BSFA_ebike_NoCaps15 = (0.05 * (df['duration_sec'] / 60)) # Post-March 2020 Pricing Scheme\n",
        "  overhead_time_BSFA_ebike_NoCaps16 = 3 + (2 * (abs(((df['duration_sec'] // 900)) - 4))) # Post-March 2020 Pricing Scheme\n",
        "  overhead_base_subscriber_classicBike17 = 0\n",
        "  overhead_time_subscriber_classicBike18 = 3 * (abs(((df['duration_sec'] // 900)) - 3))\n",
        "  overhead_base_subscriber_ebike_Capped19 = 0.15 * ((np.clip(df['duration_sec'],0,800) / 60)) # Capped at $2, at a rate of 0.15 cent/min., that represents approx. 13.3 minutes, or rounded to 800 seconds\n",
        "  overhead_time_subscriber_ebike_Capped20 = 2 + (2 * (abs(((df['duration_sec'] // 900)) - 3))) # Capped at $2 within the first 45 mins.\n",
        "  overhead_base_subscriber_ebike_Capped_ParkingFee21 = 2 + (0.15 * ((np.clip(df['duration_sec'],0,800) / 60)))\n",
        "  overhead_time_subscriber_ebike_Capped_ParkingFee22 = 4 + (2 * (abs(((df['duration_sec'] // 900)) - 3))) # Parking Fee adds a $2 surcharge\n",
        "  overhead_base_subscriber_ebike_NoCaps23 = 0.15 * ((df['duration_sec'] / 60))\n",
        "  overhead_time_subscriber_ebike_NoCaps24 = 6.75 + (2 * (abs(((df['duration_sec'] // 900)) - 3))) # A maximum of $6.75 can be charged within 45 mins\n",
        "  overhead_base_subscriber_ebike_NoCaps_ParkingFee25 = 2 + (0.15 * ((df['duration_sec'] / 60))) # Post-March 2020 Pricing Scheme # Parking Fee adds a $2 surcharge\n",
        "  overhead_time_subscriber_ebike_NoCaps_ParkingFee26 = 8.75 + (2 * (abs(((df['duration_sec'] // 900)) - 3))) # Post-March 2020 Pricing Scheme # Parking Fee adds a $2 surcharge \n",
        "  \n",
        "  choices = [\n",
        "  overhead_base_customer_classicBike1,overhead_time_customer_classicBike2,overhead_base_BSFA_classicBike3,overhead_time_BSFA_classicBike4,\n",
        "  overhead_base_customer_ebike5,overhead_time_customer_ebike6,\n",
        "  overhead_base_customer_ebike_ParkingFee7,overhead_time_customer_ebike_ParkingFee8,\n",
        "  overhead_base_BSFA_ebike_Capped9,overhead_time_BSFA_ebike_Capped10,overhead_base_BSFA_ebike_Capped_ParkingFee11,\n",
        "  overhead_time_BSFA_ebike_Capped_ParkingFee12,overhead_base_BSFA_ebike_NoCaps_ParkingFee13,overhead_time_BSFA_ebike_NoCaps_ParkingFee14,\n",
        "  overhead_base_BSFA_ebike_NoCaps15,overhead_time_BSFA_ebike_NoCaps16,\n",
        "  overhead_base_subscriber_classicBike17,overhead_time_subscriber_classicBike18,\n",
        "  overhead_base_subscriber_ebike_Capped19,overhead_time_subscriber_ebike_Capped20,overhead_base_subscriber_ebike_Capped_ParkingFee21,\n",
        "  overhead_time_subscriber_ebike_Capped_ParkingFee22,overhead_base_subscriber_ebike_NoCaps23,overhead_time_subscriber_ebike_NoCaps24,\n",
        "  overhead_base_subscriber_ebike_NoCaps_ParkingFee25,overhead_time_subscriber_ebike_NoCaps_ParkingFee26\n",
        "  ]\n",
        "  \n",
        "  df[\"est_cost\"] = np.select(conditions, choices, default=0).round(2)\n",
        "  \n",
        "  return df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 510
        },
        "id": "sdW2WYFG4CuS",
        "outputId": "2fb0229c-ecfd-418e-ff8e-c2f0af9ffb54"
      },
      "source": [
        "df2019 = revenueEST_2019(df19)\n",
        "print(df2019.est_cost.sum().round(2)) # $1,307,108\n",
        "##################################\n",
        "df20_old = revenueEST_pre2020(df20)\n",
        "display(df20_old[\"est_cost\"].sum()) # $535,392\n",
        "df20_new = revenueEST_post2020(df20)\n",
        "display(df20_new[\"est_cost\"].sum()) # $3,589,525 // 3867352.03\n",
        "df2020 = df20_old.append(df20_new, ignore_index=True)\n",
        "print(df2020.est_cost.sum().round(2)) # $4,124,917 // 4,402,744.03\n",
        "##################################\n",
        "df2021 = revenueEST_2021(df21)\n",
        "print(df2021.est_cost.sum().round(2)) # $4,293,155"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1307108\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/pandas/core/indexing.py:1763: 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",
            "  isetter(loc, value)\n",
            "/usr/local/lib/python3.7/dist-packages/geopandas/geodataframe.py:1351: 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",
            "  super().__setitem__(key, value)\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "535392.0"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/pandas/core/indexing.py:1763: 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",
            "  isetter(loc, value)\n",
            "/usr/local/lib/python3.7/dist-packages/geopandas/geodataframe.py:1351: 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",
            "  super().__setitem__(key, value)\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "3867352.03"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "4402744.03\n",
            "4293154.94\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k6XKmlLtlka5"
      },
      "source": [
        "#### Save to CSV"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jTzMN7_dllKx",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e9a4b3ae-5ac6-4f46-d911-e8dceaba2309"
      },
      "source": [
        "# from google.colab import drive\n",
        "# drive.mount('/drive')\n",
        "\n",
        "# df2019.to_csv('/drive/MyDrive/Gcolab/modifiedData/lyft/lyft2019.csv',index=False)\n",
        "# df2020.to_csv('/drive/MyDrive/Gcolab/modifiedData/lyft/lyft2020.csv',index=False)\n",
        "# df2021.to_csv('/drive/MyDrive/Gcolab/modifiedData/lyft/lyft2021.csv',index=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T_mkZG24jo0B"
      },
      "source": [
        "## One CSV to rule them all"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hGa5JqoMx3wT",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 156
        },
        "outputId": "3d028c8c-893e-45f8-b02c-8e9fd47cf22a"
      },
      "source": [
        "# Import the raw csv yearly datasets into their respective pandas dataframe\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive')\n",
        "\n",
        "df2019 = pd.read_csv(r'drive/MyDrive/Gcolab/modifiedData/lyft/lyft2019.csv',float_precision=None)\n",
        "display(df2019.shape)\n",
        "df2020 = pd.read_csv(r'drive/MyDrive/Gcolab/modifiedData/lyft/lyft2020.csv',float_precision=None)\n",
        "display(df2020.shape)\n",
        "df2021 = pd.read_csv(r'drive/MyDrive/Gcolab/modifiedData/lyft/lyft2021.csv',float_precision=None)\n",
        "display(df2021.shape)\n",
        "\n",
        "dfX = df2020.append(df2019, ignore_index=True)\n",
        "df = df2021.append(dfX, ignore_index=True)\n",
        "\n",
        "display(df.shape)\n",
        "# (4838348, 44)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(1852712, 30)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2718: DtypeWarning: Columns (0,3,5,6,7,17,18,19,22,23,26,27,30,31,40,41) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  interactivity=interactivity, compiler=compiler, result=result)\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(1732467, 42)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(1253169, 32)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(4838348, 44)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xYMtAA9p6T8v"
      },
      "source": [
        "##### Combine CSVs &Ad Hoc Cleaning"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EpnNp-Rijpdc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "42396a74-770b-4f4a-e225-403dffe69845"
      },
      "source": [
        "del df['ride_id']\n",
        "del df['member_casual']\n",
        "del df['fname']\n",
        "\n",
        "columns = ['start_station_name', 'start_station_id','end_station_name','end_station_id','bike_id','rental_access_method','bike_share_for_all_trip','is_equity']\n",
        "# 'bike_id','rental_access_method','bike_share_for_all_trip','is_equity' are deprecated fields; I'm retaining those for partial/ad hoc analysis.\n",
        "df[columns] = df.loc[:, columns].fillna(\"\")\n",
        "df = df.dropna(axis=1)\n",
        "\n",
        "def convert2datetime(dataframe,columns): \n",
        "    for column in columns:\n",
        "        dataframe[column] = pd.to_datetime(dataframe[column],dayfirst=True)       \n",
        "        dataframe[column + str('_day')] = dataframe[column].dt.day_name()\n",
        "        dataframe[column + str('_month')] = dataframe[column].dt.month_name()\n",
        "        dataframe[column + str('_hour')] = dataframe[column].dt.round('H').dt.hour\n",
        "        dataframe[column + str('_year')] = dataframe[column].dt.year\n",
        "\n",
        "    return dataframe.dtypes\n",
        "\n",
        "cols = ['start_time','end_time']\n",
        "\n",
        "convert2datetime(df,cols)\n",
        "\n",
        "df['Quarter'] = df['start_time'].dt.to_period(\"Q\")\n",
        "\n",
        "df['route_neigh'] = \"From \" + df['start_neigh'] + \" To \" + df['end_neigh']\n",
        "\n",
        "df['rideable_type'] = df['rideable_type'].replace({'classic_bike': 'docked_bike'})\n",
        "\n",
        "df = df.sort_values(by='start_time')\n",
        "\n",
        "lon = df[['start_station_longitude','end_station_longitude']].values\n",
        "lat = df[['start_station_latitude','end_station_latitude']].values\n",
        "\n",
        "from pyproj import Transformer # convert coords to UTM to get distance in meters.\n",
        "# https://gis.stackexchange.com/questions/334271/converting-large-data-with-lat-and-long-into-x-and-y\n",
        "trans = Transformer.from_crs(\n",
        "    \"epsg:4326\",\n",
        "    \"+proj=utm +zone=10 +ellps=WGS84\",\n",
        "    always_xy=True,\n",
        ")\n",
        "lon, lat = trans.transform(lon, lat)\n",
        "df[['start_lon_utm','end_lon_utm']] = lon\n",
        "df[['start_lat_utm','end_lat_utm']] = lat\n",
        "\n",
        "x1 = df.start_lon_utm.values\n",
        "x2 = df.end_lon_utm.values\n",
        "\n",
        "y1 = df.start_lat_utm.values\n",
        "y2 = df.end_lat_utm.values\n",
        "\n",
        "df['eucl_dist_m'] = np.sqrt( np.square( x2 - x1 )  + np.square( y2 - y1 ) ).round(0)\n",
        "\n",
        "del df['start_lat_utm']\n",
        "del df['end_lat_utm']\n",
        "del df['start_lon_utm']\n",
        "del df['end_lon_utm']\n",
        "\n",
        "## Datapoints that didn't get a match with ID attributes, but are valid (slight difference in naming conventions of stations)\n",
        "replace_values = {'Steuart St at Market St' : 'Market St at Steuart St',\n",
        "                 'Broadway at Kearny':'Broadway at Kearny St',\n",
        "                  'Mendall St at Fairfax Ave':'Mendell St at Fairfax Ave',\n",
        "                  'Green St at Van Ness St':'Green St at Van Ness Ave',\n",
        "                  'Clement St at 32nd Avenue':'Clement St at 32nd Ave',\n",
        "                  '5th St at Folsom':'Folsom St at 5th St',\n",
        "                  'San Francisco Caltrain Station 2  (Townsend St at 4th St)':'San Francisco Caltrain (Townsend St at 4th St)'\n",
        "                 } \n",
        "df = df.replace({\"start_station_name\": replace_values}) \n",
        "df = df.replace({\"end_station_name\": replace_values}) \n",
        "\n",
        "# Stations in the dataset that are irrelevant to the analysis (Test sites etc.)\n",
        "df = df[~df['start_station_name'].astype(str).isin(['Golden Gate Ave at Hyde St', '16th St Depot','SF Depot-2 (Minnesota St Outbound)',\n",
        "                           '16th Depot Bike Fleet Station', '23rd St at San Bruno Ave','Howard workshop - Station in a Box',\n",
        "                           'Outside Lands (Temporary Station)','Lab - Howard','SF STATION IN A BOX 1','Minnesota St Depot'])]\n",
        "df = df[~df['end_station_name'].astype(str).isin(['Golden Gate Ave at Hyde St', '16th St Depot','SF Depot-2 (Minnesota St Outbound)',\n",
        "                           '16th Depot Bike Fleet Station', '23rd St at San Bruno Ave','Howard workshop - Station in a Box',\n",
        "                           'Outside Lands (Temporary Station)','Lab - Howard','SF STATION IN A BOX 1','Minnesota St Depot'])]\n",
        "df = df[~df['start_station_id'].astype(str).isin(['33', '344', '449', '136', '428', '367'])]\n",
        "df = df[~df['end_station_id'].astype(str).isin(['33', '344', '449', '136', '428', '367'])]\n",
        "\n",
        "\n",
        "display(df.shape)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(4822411, 34)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZX80FZXj6hWA"
      },
      "source": [
        "##### Feature Engineering, Round 2\n",
        "\n",
        "---\n",
        "###### Merging dock station's attributes to the Master dataset"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "55hG0XBv2wJ9",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d3abcd4d-53ed-4a18-e664-3ecb60e61e03"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')\n",
        "stationdf = pd.read_csv(r'drive/MyDrive/Gcolab/rawdata/DataSF/Bay_Area_Bikeshare_Stations.csv',float_precision=None,dtype={\"Station ID DOMO\": \"string\", \"Station ID\": \"string\"})\n",
        "\n",
        "stationdf = stationdf[['Station ID','Station ID DOMO','Station Name','Has Kiosk','Dock Count','Station Latitude','Station Longitude']]\n",
        "\n",
        "start_stationdf = stationdf.add_prefix('start_')\n",
        "end_stationdf = stationdf.add_prefix('end_')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "snfBxHxTdrMH"
      },
      "source": [
        "What's happening here is that I am trying to add the docking station's information to the wrangled Lyft dataset. I first join the two dataset on a common ID, i.e. the station's name; where it gets tricky is that 1) I need to join separately for the start and end's station, and 2) station's name, although it joins the majority of the dataset, still does not capture all of the data. Therefore, the messy code below is essentially me taking subsets of the data that were not joined by station's name, and iterated upon by joining with variation of station's ID (again, the change in data schema between years in the raw Lyft system dataset did not do me any favors). Hence this was quite a tedious process, which I began by spittling the wrangled Lyft dataset by the trip_type category (Dockless-to-Dockless, Docked-to-Docked, Docked-to-Dockless, and Dockless-to-Docked...obviously the Dockless-to-Dockless subset did not need to be joined, and was appended last). \n",
        "\n",
        "So yes, there is a method to this madness, which took me a bit of time to work myself backward after I returned to this project after a few months on hold; perhaps this is revealing that I still have ways to write cleaner and more concise code. I mean, sure, I could have written a helper function and looped some of the batch processing that took place...but I was also double-checking every steps and tracking how much of the data was getting matches or dropped. Look it's not my best code OK but it got the job done and sometimes after spending a stupid amount of time working through a hydra-like problem just to join some bits of data to 4 million rows you know what I'll take it. Live and learn. Learn from my mistakes as I learn from mine. I don't know why I'm writing all this I guess it's a form of coping from something that should be called *post-mortem programmatis* or whatev. OK peace out."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "M-PlIy71Y5v4"
      },
      "source": [
        "# Merge the DATASF's docking dataset to the Master Lyft dataset; remove all dockless trips first, then re-append later.\n",
        "\n",
        "# print(len(df.loc[df['trip_type'] != 'Dockless-to-Dockless']))\n",
        "# print(len(df.loc[df['trip_type'] == 'Dockless-to-Dockless']))\n",
        "\n",
        "df_dl2dl = df.loc[df['trip_type'] == 'Dockless-to-Dockless']\n",
        "df_d2d = df.loc[df['trip_type'] == 'Docked-to-Docked']\n",
        "df_d2dl = df.loc[df['trip_type'] == 'Docked-to-Dockless']\n",
        "df_dl2d = df.loc[df['trip_type'] == 'Dockless-to-Docked']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 255
        },
        "id": "mhtpcAf6UPeu",
        "outputId": "eee97914-2279-4cb1-f8b3-b37d1896c0ea"
      },
      "source": [
        "# Match all d2d sets -- START\n",
        "\n",
        "merged_df = df_d2d.merge(start_stationdf, left_on=['start_station_name'], right_on = ['start_Station Name'], how='left')\n",
        "print(merged_df.shape) # 3467999 >> TOTAL ROWS\n",
        "## start_merged_df will be appended.\n",
        "start_merged_df = merged_df.dropna(axis=0, subset=['start_Station Name']) # append candidate 1, Merging on Start Station's Name\n",
        "print(start_merged_df.shape) # 3425450 / 3467999 >> 98.7% total match >> TOTAL ROWS that were merged with start_stationdf\n",
        "## start_merged_df2 will be appended.\n",
        "start_notmerged_df = merged_df[merged_df['start_Station Name'].isnull()]\n",
        "print(start_notmerged_df.shape) # 42549 >> 1% of the set (referred below as subset) unmatched >> TOTAL ROWS that were not merged with start_stationdf; everything below is to ensure that all possible matches (using different fields) are met.\n",
        "start_notmerged_df = start_notmerged_df[start_notmerged_df.columns[:-7]]\n",
        "start_notmerged_df['start_station_id'] = start_notmerged_df['start_station_id'].astype(str).str.replace('.0','')\n",
        "merged_df2 = start_notmerged_df.merge(start_stationdf, left_on=['start_station_id'], right_on = ['start_Station ID'], how='left') # Merging on Start Station's unique ID\n",
        "start_merged_df2 = merged_df2.dropna(axis=0, subset=['start_Station ID']) # append candidate 2\n",
        "print(start_merged_df2.shape) # 27458 / 42549 >> 64.5% of the subset matched based on station ID field\n",
        "\n",
        "start_notmerged_df2 = merged_df2[merged_df2['start_Station ID'].isnull()]\n",
        "print(start_notmerged_df2.shape) # 15091 / 42549 >> Still 35% of the subset left to be ID.\n",
        "start_notmerged_df2 = start_notmerged_df2[start_notmerged_df2.columns[:-7]]\n",
        "merged_df3 = start_notmerged_df2.merge(start_stationdf, left_on=['start_station_id'], right_on = ['start_Station ID DOMO'], how='left') # Merging on Start Station's unique ID <> \"ID Domo\"\n",
        "start_merged_df3 = merged_df3.dropna(axis=0, subset=['start_Station ID DOMO']) # append candidate 3, 430\n",
        "print(start_merged_df3.shape) # 4191 / 15091 >> 28% of the subset id.\n",
        "\n",
        "start_notmerged_df3 = merged_df3[merged_df3['start_Station ID DOMO'].isnull()]\n",
        "print(start_notmerged_df3.shape) # 10900 rows left to be ID >> 0.003% of the d2d set Dropped\n",
        "start_no_merges = start_notmerged_df3[start_notmerged_df3['start_station_id'].isnull()]\n",
        "print(start_no_merges.shape) \n",
        "\n",
        "start_append1 = start_merged_df.append(start_merged_df2, ignore_index=True)\n",
        "print(start_append1.shape) # 3452908\n",
        "start_append2 = start_append1.append(start_merged_df3, ignore_index=True)\n",
        "print(start_append2.shape) # 3457099\n",
        "start_append3 = start_append2.append(start_notmerged_df3, ignore_index=True)\n",
        "print(start_append3.shape) # 3457099\n",
        "merged_df1 = start_append3.merge(end_stationdf, left_on=['end_station_name'], right_on = ['end_Station Name'], how='left')\n",
        "print(merged_df1.shape) # 3467999\n",
        "merged_df1 = merged_df1.dropna(axis=0, subset=['start_Dock Count','end_Dock Count'])\n",
        "print(len(merged_df1)) # 98.5% of the d2d set has been matched.\n",
        "\n",
        "### \n",
        "end_merged_df = merged_df1.dropna(axis=0, subset=['end_Station Name']) # append candidate 1, Merging on Start Station's Name\n",
        "\n",
        "end_notmerged_df = merged_df1[merged_df1['end_Station Name'].isnull()]\n",
        "\n",
        "end_notmerged_df = end_notmerged_df[end_notmerged_df.columns[:-7]]\n",
        "\n",
        "end_notmerged_df['end_station_id'] = end_notmerged_df['end_station_id'].astype(str).str.replace('.0','')\n",
        "\n",
        "merged_df2 = end_notmerged_df.merge(end_stationdf, left_on=['end_station_id'], right_on = ['end_Station ID'], how='left')\n",
        "\n",
        "end_merged_df2 = merged_df2.dropna(axis=0, subset=['end_Station ID']) # append candidate 2, 30000\n",
        "\n",
        "end_notmerged_df2 = merged_df2[merged_df2['end_Station ID'].isnull()]\n",
        "\n",
        "end_notmerged_df2 = end_notmerged_df2[end_notmerged_df2.columns[:-7]]\n",
        "\n",
        "merged_df3 = end_notmerged_df2.merge(end_stationdf, left_on=['end_station_id'], right_on = ['end_Station ID DOMO'], how='left')\n",
        "\n",
        "end_merged_df3 = merged_df3.dropna(axis=0, subset=['end_Station ID DOMO']) # append candidate 3, 430\n",
        "\n",
        "end_notmerged_df3 = merged_df3[merged_df3['end_Station ID DOMO'].isnull()]\n",
        "\n",
        "end_notmerged_df3 = end_notmerged_df3[end_notmerged_df3['end_station_id'].isnull()]\n",
        "\n",
        "end_no_merges = end_notmerged_df3[end_notmerged_df3['end_station_id'].isnull()]\n",
        "\n",
        "end_append1 = end_merged_df.append(end_merged_df2, ignore_index=True)\n",
        "\n",
        "end_append2 = end_append1.append(end_merged_df3, ignore_index=True)\n",
        "\n",
        "merged_df1X = end_append2.append(end_notmerged_df3, ignore_index=True)\n",
        "\n",
        "display(merged_df1X.shape)\n",
        "### 3,416,602 / 3,467,999 rows"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(3467999, 41)\n",
            "(3425450, 41)\n",
            "(42549, 41)\n",
            "(27458, 41)\n",
            "(15091, 41)\n",
            "(4191, 41)\n",
            "(10900, 41)\n",
            "(0, 41)\n",
            "(3452908, 41)\n",
            "(3457099, 41)\n",
            "(3467999, 41)\n",
            "(3467999, 48)\n",
            "3416602\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(3416602, 48)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RiUhkVmsVH3N",
        "outputId": "ae19997d-6f82-4e45-a640-8566003679a4"
      },
      "source": [
        "# Match all df_d2dl sets\n",
        "\n",
        "merged_df = df_d2dl.merge(start_stationdf, left_on=['start_station_name'], right_on = ['start_Station Name'], how='left')\n",
        "print(merged_df.shape) # 348636 >> TOTAL ROWS\n",
        "## start_merged_df will be appended.\n",
        "start_merged_df = merged_df.dropna(axis=0, subset=['start_Station Name']) # append candidate 1, Merging on Start Station's Name\n",
        "print(start_merged_df.shape) # 344138 / 348636 >> 98.7% total match >> TOTAL ROWS that were merged with start_stationdf\n",
        "## start_merged_df2 will be appended.\n",
        "start_notmerged_df = merged_df[merged_df['start_Station Name'].isnull()]\n",
        "print(start_notmerged_df.shape) # 42549 >> 1% of the set (referred below as subset) unmatched >> TOTAL ROWS that were not merged with start_stationdf; everything below is to ensure that all possible matches (using different fields) are met.\n",
        "start_notmerged_df = start_notmerged_df[start_notmerged_df.columns[:-7]]\n",
        "start_notmerged_df['start_station_id'] = start_notmerged_df['start_station_id'].astype(str).str.replace('.0','')\n",
        "merged_df2 = start_notmerged_df.merge(start_stationdf, left_on=['start_station_id'], right_on = ['start_Station ID'], how='left') # Merging on Start Station's unique ID\n",
        "start_merged_df2 = merged_df2.dropna(axis=0, subset=['start_Station ID']) # append candidate 2\n",
        "print(start_merged_df2.shape) # 27458 / 42549 >> 64.5% of the subset matched based on station ID field\n",
        "\n",
        "start_notmerged_df2 = merged_df2[merged_df2['start_Station ID'].isnull()]\n",
        "print(start_notmerged_df2.shape) # 15091 / 42549 >> Still 35% of the subset left to be ID.\n",
        "start_notmerged_df2 = start_notmerged_df2[start_notmerged_df2.columns[:-7]]\n",
        "merged_df3 = start_notmerged_df2.merge(start_stationdf, left_on=['start_station_id'], right_on = ['start_Station ID DOMO'], how='left') # Merging on Start Station's unique ID <> \"ID Domo\"\n",
        "start_merged_df3 = merged_df3.dropna(axis=0, subset=['start_Station ID DOMO']) # append candidate 3, 430\n",
        "print(start_merged_df3.shape) # 4191 / 15091 >> 28% of the subset id.\n",
        "\n",
        "start_notmerged_df3 = merged_df3[merged_df3['start_Station ID DOMO'].isnull()]\n",
        "print(start_notmerged_df3.shape) # 10900 rows left to be ID >> 0.003% of the d2d set Dropped\n",
        "start_no_merges = start_notmerged_df3[start_notmerged_df3['start_station_id'].isnull()]\n",
        "print(start_no_merges.shape) \n",
        "\n",
        "start_append1 = start_merged_df.append(start_merged_df2, ignore_index=True)\n",
        "print(start_append1.shape) # 3452908\n",
        "start_append2 = start_append1.append(start_merged_df3, ignore_index=True)\n",
        "print(start_append2.shape) # 3457099\n",
        "start_append3 = start_append2.append(start_notmerged_df3, ignore_index=True)\n",
        "print(start_append3.shape) # 3457099\n",
        "\n",
        "merged_df2X = start_append3.dropna(axis=0, subset=['start_Dock Count'])\n",
        "print(len(merged_df2X)) # 99% of the df_d2dl set has been matched."
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(348636, 41)\n",
            "(344138, 41)\n",
            "(4498, 41)\n",
            "(1685, 41)\n",
            "(2813, 41)\n",
            "(900, 41)\n",
            "(1913, 41)\n",
            "(0, 41)\n",
            "(345823, 41)\n",
            "(346723, 41)\n",
            "(348636, 41)\n",
            "346723\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CVocMj5Fru6T",
        "outputId": "7b75d50f-6143-491f-96b7-2345908e6121"
      },
      "source": [
        "# Match all df_dl2d sets\n",
        "\n",
        "merged_df = df_dl2d.merge(end_stationdf, left_on=['end_station_name'], right_on = ['end_Station Name'], how='left')\n",
        "print(merged_df.shape) # 348636 >> TOTAL ROWS\n",
        "## end_merged_df will be appended.\n",
        "end_merged_df = merged_df.dropna(axis=0, subset=['end_Station Name']) # append candidate 1, Merging on end Station's Name\n",
        "print(end_merged_df.shape) # 344138 / 348636 >> 98.7% total match >> TOTAL ROWS that were merged with end_stationdf\n",
        "## end_merged_df2 will be appended.\n",
        "end_notmerged_df = merged_df[merged_df['end_Station Name'].isnull()]\n",
        "print(end_notmerged_df.shape) # 42549 >> 1% of the set (referred below as subset) unmatched >> TOTAL ROWS that were not merged with end_stationdf; everything below is to ensure that all possible matches (using different fields) are met.\n",
        "end_notmerged_df = end_notmerged_df[end_notmerged_df.columns[:-7]]\n",
        "end_notmerged_df['end_station_id'] = end_notmerged_df['end_station_id'].astype(str).str.replace('.0','')\n",
        "merged_df2 = end_notmerged_df.merge(end_stationdf, left_on=['end_station_id'], right_on = ['end_Station ID'], how='left') # Merging on end Station's unique ID\n",
        "end_merged_df2 = merged_df2.dropna(axis=0, subset=['end_Station ID']) # append candidate 2\n",
        "print(end_merged_df2.shape) # 27458 / 42549 >> 64.5% of the subset matched based on station ID field\n",
        "\n",
        "end_notmerged_df2 = merged_df2[merged_df2['end_Station ID'].isnull()]\n",
        "print(end_notmerged_df2.shape) # 15091 / 42549 >> Still 35% of the subset left to be ID.\n",
        "end_notmerged_df2 = end_notmerged_df2[end_notmerged_df2.columns[:-7]]\n",
        "merged_df3 = end_notmerged_df2.merge(end_stationdf, left_on=['end_station_id'], right_on = ['end_Station ID DOMO'], how='left') # Merging on end Station's unique ID <> \"ID Domo\"\n",
        "end_merged_df3 = merged_df3.dropna(axis=0, subset=['end_Station ID DOMO']) # append candidate 3, 430\n",
        "print(end_merged_df3.shape) # 4191 / 15091 >> 28% of the subset id.\n",
        "\n",
        "end_notmerged_df3 = merged_df3[merged_df3['end_Station ID DOMO'].isnull()]\n",
        "print(end_notmerged_df3.shape) # 10900 rows left to be ID >> 0.003% of the d2d set Dropped\n",
        "end_no_merges = end_notmerged_df3[end_notmerged_df3['end_station_id'].isnull()]\n",
        "print(end_no_merges.shape) \n",
        "\n",
        "end_append1 = end_merged_df.append(end_merged_df2, ignore_index=True)\n",
        "print(end_append1.shape) # 3452908\n",
        "end_append2 = end_append1.append(end_merged_df3, ignore_index=True)\n",
        "print(end_append2.shape) # 3457099\n",
        "end_append3 = end_append2.append(end_notmerged_df3, ignore_index=True)\n",
        "print(end_append3.shape) # 3457099\n",
        "\n",
        "merged_df3X = end_append3.dropna(axis=0, subset=['end_Dock Count'])\n",
        "\n",
        "print(len(merged_df3X)) # 318615/320062 of the df_dl2d set has been matched."
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(320062, 41)\n",
            "(316175, 41)\n",
            "(3887, 41)\n",
            "(1633, 41)\n",
            "(2254, 41)\n",
            "(807, 41)\n",
            "(1447, 41)\n",
            "(0, 41)\n",
            "(317808, 41)\n",
            "(318615, 41)\n",
            "(320062, 41)\n",
            "318615\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "id": "a5L5gtQBQ5dN",
        "outputId": "416845f5-e97f-4167-d11f-17b206aa4a0d"
      },
      "source": [
        "final_append1 = merged_df1X.append(merged_df2X, ignore_index=True)\n",
        "final_append2 = final_append1.append(merged_df3X, ignore_index=True)\n",
        "df_master = final_append2.append(df_dl2dl, ignore_index=True)\n",
        "\n",
        "display(df_master.shape)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(4767654, 48)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HWKu7dj79VOx"
      },
      "source": [
        "df = df_master\n",
        "\n",
        "df.loc[df['trip_type'] == 'Docked-to-Docked','start_station_latitude'] = df['start_Station Latitude']\n",
        "df.loc[df['trip_type'] == 'Docked-to-Docked','start_station_longitude'] = df['start_Station Longitude']\n",
        "df.loc[df['trip_type'] == 'Docked-to-Docked','end_station_latitude'] = df['end_Station Latitude']\n",
        "df.loc[df['trip_type'] == 'Docked-to-Docked','end_station_longitude'] = df['end_Station Longitude']\n",
        "\n",
        "df.loc[df['trip_type'] == 'Docked-to-Dockless','start_station_latitude'] = df['start_Station Latitude']\n",
        "df.loc[df['trip_type'] == 'Docked-to-Dockless','start_station_longitude'] = df['start_Station Longitude']\n",
        "\n",
        "df.loc[df['trip_type'] == 'Dockless-to-Docked','end_station_latitude'] = df['end_Station Latitude']\n",
        "df.loc[df['trip_type'] == 'Dockless-to-Docked','end_station_longitude'] = df['end_Station Longitude']\n",
        "\n",
        "columns = ['start_Station Latitude','start_Station Longitude','end_Station Latitude','end_Station Longitude',\n",
        "           'start_Station ID','start_Station ID DOMO','end_Station ID','end_Station ID DOMO','start_Station Name','end_Station Name','geometry']\n",
        "df.drop(columns, inplace=True, axis=1)\n",
        "\n",
        "df['start_station_name'] = df.start_station_name.fillna('Dockless Stationnement')\n",
        "df['end_station_name'] = df.end_station_name.fillna('Dockless Stationnement')\n",
        "df['route_stations'] = \"From \" + df['start_station_name'] + \" To \" + df['end_station_name']"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fNcL-jYqzphf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "3151167d-1e88-494c-ba31-4f9997ea639f"
      },
      "source": [
        "display(df.shape)\n",
        "display(df.dtypes)\n",
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "(4767654, 38)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "rideable_type                      object\n",
              "start_time                 datetime64[ns]\n",
              "end_time                   datetime64[ns]\n",
              "start_station_name                 object\n",
              "start_station_id                   object\n",
              "end_station_name                   object\n",
              "end_station_id                     object\n",
              "start_station_latitude            float64\n",
              "start_station_longitude           float64\n",
              "end_station_latitude              float64\n",
              "end_station_longitude             float64\n",
              "duration_sec                      float64\n",
              "duration_min                      float64\n",
              "trip_type                          object\n",
              "user_type                          object\n",
              "start_neigh                        object\n",
              "end_neigh                          object\n",
              "est_cost                          float64\n",
              "is_equity                          object\n",
              "bike_id                            object\n",
              "rental_access_method               object\n",
              "bike_share_for_all_trip            object\n",
              "start_time_day                     object\n",
              "start_time_month                   object\n",
              "start_time_hour                     int64\n",
              "start_time_year                     int64\n",
              "end_time_day                       object\n",
              "end_time_month                     object\n",
              "end_time_hour                       int64\n",
              "end_time_year                       int64\n",
              "Quarter                     period[Q-DEC]\n",
              "route_neigh                        object\n",
              "eucl_dist_m                       float64\n",
              "start_Has Kiosk                    object\n",
              "start_Dock Count                  float64\n",
              "end_Has Kiosk                      object\n",
              "end_Dock Count                    float64\n",
              "route_stations                     object\n",
              "dtype: object"
            ]
          },
          "metadata": {}
        },
        {
          "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>rideable_type</th>\n",
              "      <th>start_time</th>\n",
              "      <th>end_time</th>\n",
              "      <th>start_station_name</th>\n",
              "      <th>start_station_id</th>\n",
              "      <th>end_station_name</th>\n",
              "      <th>end_station_id</th>\n",
              "      <th>start_station_latitude</th>\n",
              "      <th>start_station_longitude</th>\n",
              "      <th>end_station_latitude</th>\n",
              "      <th>end_station_longitude</th>\n",
              "      <th>duration_sec</th>\n",
              "      <th>duration_min</th>\n",
              "      <th>trip_type</th>\n",
              "      <th>user_type</th>\n",
              "      <th>start_neigh</th>\n",
              "      <th>end_neigh</th>\n",
              "      <th>est_cost</th>\n",
              "      <th>is_equity</th>\n",
              "      <th>bike_id</th>\n",
              "      <th>rental_access_method</th>\n",
              "      <th>bike_share_for_all_trip</th>\n",
              "      <th>start_time_day</th>\n",
              "      <th>start_time_month</th>\n",
              "      <th>start_time_hour</th>\n",
              "      <th>start_time_year</th>\n",
              "      <th>end_time_day</th>\n",
              "      <th>end_time_month</th>\n",
              "      <th>end_time_hour</th>\n",
              "      <th>end_time_year</th>\n",
              "      <th>Quarter</th>\n",
              "      <th>route_neigh</th>\n",
              "      <th>eucl_dist_m</th>\n",
              "      <th>start_Has Kiosk</th>\n",
              "      <th>start_Dock Count</th>\n",
              "      <th>end_Has Kiosk</th>\n",
              "      <th>end_Dock Count</th>\n",
              "      <th>route_stations</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>docked_bike</td>\n",
              "      <td>2019-01-01 00:08:39.659</td>\n",
              "      <td>2019-01-01 00:38:06.848</td>\n",
              "      <td>Market St at Steuart St</td>\n",
              "      <td>16</td>\n",
              "      <td>Jackson Playground</td>\n",
              "      <td>115</td>\n",
              "      <td>37.794525</td>\n",
              "      <td>-122.394880</td>\n",
              "      <td>37.764965</td>\n",
              "      <td>-122.399025</td>\n",
              "      <td>1767.0</td>\n",
              "      <td>29.0</td>\n",
              "      <td>Docked-to-Docked</td>\n",
              "      <td>Customer</td>\n",
              "      <td>Financial District</td>\n",
              "      <td>Potrero Hill</td>\n",
              "      <td>2.0</td>\n",
              "      <td></td>\n",
              "      <td>1705</td>\n",
              "      <td></td>\n",
              "      <td>No</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>0</td>\n",
              "      <td>2019</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>1</td>\n",
              "      <td>2019</td>\n",
              "      <td>2019Q1</td>\n",
              "      <td>From Financial District To Potrero Hill</td>\n",
              "      <td>3252.0</td>\n",
              "      <td>True</td>\n",
              "      <td>25.0</td>\n",
              "      <td>True</td>\n",
              "      <td>27.0</td>\n",
              "      <td>From Market St at Steuart St To Jackson Playgr...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>docked_bike</td>\n",
              "      <td>2019-01-01 00:14:55.921</td>\n",
              "      <td>2019-01-01 00:25:02.215</td>\n",
              "      <td>The Embarcadero at Steuart St</td>\n",
              "      <td>23</td>\n",
              "      <td>Berry St at King St</td>\n",
              "      <td>91</td>\n",
              "      <td>37.791401</td>\n",
              "      <td>-122.391038</td>\n",
              "      <td>37.771762</td>\n",
              "      <td>-122.398438</td>\n",
              "      <td>606.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>Docked-to-Docked</td>\n",
              "      <td>Subscriber</td>\n",
              "      <td>Financial District</td>\n",
              "      <td>Mission Bay</td>\n",
              "      <td>0.0</td>\n",
              "      <td></td>\n",
              "      <td>5112</td>\n",
              "      <td></td>\n",
              "      <td>No</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>0</td>\n",
              "      <td>2019</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>0</td>\n",
              "      <td>2019</td>\n",
              "      <td>2019Q1</td>\n",
              "      <td>From Financial District To Mission Bay</td>\n",
              "      <td>2281.0</td>\n",
              "      <td>True</td>\n",
              "      <td>23.0</td>\n",
              "      <td>True</td>\n",
              "      <td>23.0</td>\n",
              "      <td>From The Embarcadero at Steuart St To Berry St...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>docked_bike</td>\n",
              "      <td>2019-01-01 00:15:11.324</td>\n",
              "      <td>2019-01-01 00:47:09.221</td>\n",
              "      <td>Montgomery St BART Station (Market St at 2nd St)</td>\n",
              "      <td>21</td>\n",
              "      <td>Civic Center/UN Plaza BART Station (Market St ...</td>\n",
              "      <td>44</td>\n",
              "      <td>37.789625</td>\n",
              "      <td>-122.400811</td>\n",
              "      <td>37.781074</td>\n",
              "      <td>-122.411738</td>\n",
              "      <td>1917.0</td>\n",
              "      <td>31.0</td>\n",
              "      <td>Docked-to-Docked</td>\n",
              "      <td>Subscriber</td>\n",
              "      <td>Financial District</td>\n",
              "      <td>South of Market</td>\n",
              "      <td>0.0</td>\n",
              "      <td></td>\n",
              "      <td>4838</td>\n",
              "      <td></td>\n",
              "      <td>No</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>0</td>\n",
              "      <td>2019</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>1</td>\n",
              "      <td>2019</td>\n",
              "      <td>2019Q1</td>\n",
              "      <td>From Financial District To South of Market</td>\n",
              "      <td>1351.0</td>\n",
              "      <td>True</td>\n",
              "      <td>39.0</td>\n",
              "      <td>True</td>\n",
              "      <td>31.0</td>\n",
              "      <td>From Montgomery St BART Station (Market St at ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>docked_bike</td>\n",
              "      <td>2019-01-01 00:15:26.529</td>\n",
              "      <td>2019-01-01 00:47:19.626</td>\n",
              "      <td>Montgomery St BART Station (Market St at 2nd St)</td>\n",
              "      <td>21</td>\n",
              "      <td>Civic Center/UN Plaza BART Station (Market St ...</td>\n",
              "      <td>44</td>\n",
              "      <td>37.789625</td>\n",
              "      <td>-122.400811</td>\n",
              "      <td>37.781074</td>\n",
              "      <td>-122.411738</td>\n",
              "      <td>1913.0</td>\n",
              "      <td>31.0</td>\n",
              "      <td>Docked-to-Docked</td>\n",
              "      <td>Subscriber</td>\n",
              "      <td>Financial District</td>\n",
              "      <td>South of Market</td>\n",
              "      <td>0.0</td>\n",
              "      <td></td>\n",
              "      <td>5423</td>\n",
              "      <td></td>\n",
              "      <td>No</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>0</td>\n",
              "      <td>2019</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>1</td>\n",
              "      <td>2019</td>\n",
              "      <td>2019Q1</td>\n",
              "      <td>From Financial District To South of Market</td>\n",
              "      <td>1351.0</td>\n",
              "      <td>True</td>\n",
              "      <td>39.0</td>\n",
              "      <td>True</td>\n",
              "      <td>31.0</td>\n",
              "      <td>From Montgomery St BART Station (Market St at ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>docked_bike</td>\n",
              "      <td>2019-01-01 00:16:36.845</td>\n",
              "      <td>2019-01-01 00:23:07.253</td>\n",
              "      <td>17th St at Valencia St</td>\n",
              "      <td>109</td>\n",
              "      <td>Valencia St at Cesar Chavez St</td>\n",
              "      <td>141</td>\n",
              "      <td>37.763333</td>\n",
              "      <td>-122.422055</td>\n",
              "      <td>37.747998</td>\n",
              "      <td>-122.420219</td>\n",
              "      <td>390.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>Docked-to-Docked</td>\n",
              "      <td>Subscriber</td>\n",
              "      <td>Mission Dolores</td>\n",
              "      <td>Bernal Heights</td>\n",
              "      <td>0.0</td>\n",
              "      <td></td>\n",
              "      <td>5059</td>\n",
              "      <td></td>\n",
              "      <td>No</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>0</td>\n",
              "      <td>2019</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>January</td>\n",
              "      <td>0</td>\n",
              "      <td>2019</td>\n",
              "      <td>2019Q1</td>\n",
              "      <td>From Mission Dolores To Bernal Heights</td>\n",
              "      <td>1706.0</td>\n",
              "      <td>True</td>\n",
              "      <td>23.0</td>\n",
              "      <td>True</td>\n",
              "      <td>19.0</td>\n",
              "      <td>From 17th St at Valencia St To Valencia St at ...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  rideable_type              start_time                end_time  \\\n",
              "0   docked_bike 2019-01-01 00:08:39.659 2019-01-01 00:38:06.848   \n",
              "1   docked_bike 2019-01-01 00:14:55.921 2019-01-01 00:25:02.215   \n",
              "2   docked_bike 2019-01-01 00:15:11.324 2019-01-01 00:47:09.221   \n",
              "3   docked_bike 2019-01-01 00:15:26.529 2019-01-01 00:47:19.626   \n",
              "4   docked_bike 2019-01-01 00:16:36.845 2019-01-01 00:23:07.253   \n",
              "\n",
              "                                 start_station_name start_station_id  \\\n",
              "0                           Market St at Steuart St               16   \n",
              "1                     The Embarcadero at Steuart St               23   \n",
              "2  Montgomery St BART Station (Market St at 2nd St)               21   \n",
              "3  Montgomery St BART Station (Market St at 2nd St)               21   \n",
              "4                            17th St at Valencia St              109   \n",
              "\n",
              "                                    end_station_name end_station_id  \\\n",
              "0                                 Jackson Playground            115   \n",
              "1                                Berry St at King St             91   \n",
              "2  Civic Center/UN Plaza BART Station (Market St ...             44   \n",
              "3  Civic Center/UN Plaza BART Station (Market St ...             44   \n",
              "4                     Valencia St at Cesar Chavez St            141   \n",
              "\n",
              "   start_station_latitude  start_station_longitude  end_station_latitude  \\\n",
              "0               37.794525              -122.394880             37.764965   \n",
              "1               37.791401              -122.391038             37.771762   \n",
              "2               37.789625              -122.400811             37.781074   \n",
              "3               37.789625              -122.400811             37.781074   \n",
              "4               37.763333              -122.422055             37.747998   \n",
              "\n",
              "   end_station_longitude  duration_sec  duration_min         trip_type  \\\n",
              "0            -122.399025        1767.0          29.0  Docked-to-Docked   \n",
              "1            -122.398438         606.0          10.0  Docked-to-Docked   \n",
              "2            -122.411738        1917.0          31.0  Docked-to-Docked   \n",
              "3            -122.411738        1913.0          31.0  Docked-to-Docked   \n",
              "4            -122.420219         390.0           6.0  Docked-to-Docked   \n",
              "\n",
              "    user_type         start_neigh        end_neigh  est_cost is_equity  \\\n",
              "0    Customer  Financial District     Potrero Hill       2.0             \n",
              "1  Subscriber  Financial District      Mission Bay       0.0             \n",
              "2  Subscriber  Financial District  South of Market       0.0             \n",
              "3  Subscriber  Financial District  South of Market       0.0             \n",
              "4  Subscriber     Mission Dolores   Bernal Heights       0.0             \n",
              "\n",
              "  bike_id rental_access_method bike_share_for_all_trip start_time_day  \\\n",
              "0    1705                                           No        Tuesday   \n",
              "1    5112                                           No        Tuesday   \n",
              "2    4838                                           No        Tuesday   \n",
              "3    5423                                           No        Tuesday   \n",
              "4    5059                                           No        Tuesday   \n",
              "\n",
              "  start_time_month  start_time_hour  start_time_year end_time_day  \\\n",
              "0          January                0             2019      Tuesday   \n",
              "1          January                0             2019      Tuesday   \n",
              "2          January                0             2019      Tuesday   \n",
              "3          January                0             2019      Tuesday   \n",
              "4          January                0             2019      Tuesday   \n",
              "\n",
              "  end_time_month  end_time_hour  end_time_year Quarter  \\\n",
              "0        January              1           2019  2019Q1   \n",
              "1        January              0           2019  2019Q1   \n",
              "2        January              1           2019  2019Q1   \n",
              "3        January              1           2019  2019Q1   \n",
              "4        January              0           2019  2019Q1   \n",
              "\n",
              "                                  route_neigh  eucl_dist_m start_Has Kiosk  \\\n",
              "0     From Financial District To Potrero Hill       3252.0            True   \n",
              "1      From Financial District To Mission Bay       2281.0            True   \n",
              "2  From Financial District To South of Market       1351.0            True   \n",
              "3  From Financial District To South of Market       1351.0            True   \n",
              "4      From Mission Dolores To Bernal Heights       1706.0            True   \n",
              "\n",
              "   start_Dock Count end_Has Kiosk  end_Dock Count  \\\n",
              "0              25.0          True            27.0   \n",
              "1              23.0          True            23.0   \n",
              "2              39.0          True            31.0   \n",
              "3              39.0          True            31.0   \n",
              "4              23.0          True            19.0   \n",
              "\n",
              "                                      route_stations  \n",
              "0  From Market St at Steuart St To Jackson Playgr...  \n",
              "1  From The Embarcadero at Steuart St To Berry St...  \n",
              "2  From Montgomery St BART Station (Market St at ...  \n",
              "3  From Montgomery St BART Station (Market St at ...  \n",
              "4  From 17th St at Valencia St To Valencia St at ...  "
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7O03iwJd4dfj",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f82baa72-5675-47fa-f731-0b747dbe0fb1"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/drive')\n",
        "\n",
        "df.to_csv('/drive/MyDrive/Gcolab/modifiedData/lyft/masterdataset_lyft.csv',index=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7vBgSdV_g4LC"
      },
      "source": [
        "##### Feature Engineering, Round 3: Geospatial Attributes\n",
        "\n",
        "---\n",
        "###### Intersecting GEOJSON with the Master's dataset"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WH4ph798hx8k"
      },
      "source": [
        "# from google.colab import drive\n",
        "# drive.mount('/content/drive')\n",
        "\n",
        "# df = pd.read_csv(r'drive/MyDrive/Gcolab/modifiedData/lyft/masterdataset_lyft.csv',float_precision=None)\n",
        "# display(df.shape)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gfVwCCjHg4pm"
      },
      "source": [
        "# fname = 'drive/MyDrive/Gcolab/rawdata/DataSF/elevation.geojson' # Your filepath here\n",
        "# poly = gpd.read_file(fname)"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}