journeys_data_nogeom.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4b4e368c-26d2-442c-8d12-eb58ba353e84",
   "metadata": {},
   "source": [
    "# Fetching the journeys data for analysis\n",
    "\n",
    "Download the CSV data from TfL, and save in a GeoPandas geodataframe.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3f68f977-24b1-4f7d-a47a-26d2a96c1a1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converts journeys into geometry\n",
    "\n",
    "import os\n",
    "import csv\n",
    "import json\n",
    "import urllib.request\n",
    "from requests import get\n",
    "\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import rasterio\n",
    "import rasterio.plot\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from shapely.geometry import LineString"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f3246b48-fe9b-43e7-9101-8fa8cf60a3fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# set paths\n",
    "DL_path = \"data/cycles/DL_data\"\n",
    "points_path = \"data/cycles/points\"\n",
    "points_fn = \"BikePoints.geojson\"\n",
    "journeys_path = \"data/cycles/journeys\"\n",
    "\n",
    "# set source URL and filename\n",
    "source_url = \"https://cycling.data.tfl.gov.uk/usage-stats/\"\n",
    "source_fn = [\n",
    "    \"346JourneyDataExtract28Nov2022-04Dec2022.csv\",\n",
    "    \"347JourneyDataExtract05Dec2022-11Dec2022.csv\",\n",
    "    \"348JourneyDataExtract12Dec2022-18Dec2022.csv\",\n",
    "    \"349JourneyDataExtract19Dec2022-25Dec2022.csv\",\n",
    "    \"350JourneyDataExtract26Dec2022-01Jan2023.csv\",\n",
    "    \"351JourneyDataExtract02Jan2023-08Jan2023.csv\",\n",
    "    \"352JourneyDataExtract09Jan2023-15Jan2023.csv\",\n",
    "    \"353JourneyDataExtract16Jan2023-22Jan2023.csv\",\n",
    "    \"354JourneyDataExtract23Jan2023-29Jan2023.csv\",\n",
    "    \"355JourneyDataExtract30Jan2023-05Feb2023.csv\",\n",
    "    \"356JourneyDataExtract06Feb2023-12Feb2023.csv\",\n",
    "    \"357JourneyDataExtract13Feb2023-19Feb2023.csv\",\n",
    "    \"358JourneyDataExtract20Feb2023-26Feb2023.csv\",\n",
    "    \"359JourneyDataExtract27Feb2023-05Mar2023.csv\",\n",
    "    \"360JourneyDataExtract06Mar2023-12Mar2023.csv\",\n",
    "    \"361JourneyDataExtract13Mar2023-19Mar2023.csv\",\n",
    "    \"362JourneyDataExtract20Mar2023-26Mar2023.csv\",\n",
    "    \"363JourneyDataExtract27Mar2023-02Apr2023.csv\",\n",
    "    \"364JourneyDataExtract03Apr2023-09Apr2023.csv\",\n",
    "    \"365JourneyDataExtract10Apr2023-16Apr2023.csv\",\n",
    "    \"366JourneyDataExtract17Apr2023-23Apr2023.csv\",\n",
    "    \"367JourneyDataExtract24Apr2023-30Apr2023.csv\",\n",
    "    \"368JourneyDataExtract01May2023-07May2023.csv\",\n",
    "    \"369JourneyDataExtract08May2023-14May2023.csv\",\n",
    "    \"370JourneyDataExtract15May2023-21May2023.csv\",\n",
    "    \"371JourneyDataExtract22May2023-28May2023.csv\",\n",
    "    \"372JourneyDataExtract29May2023-04Jun2023.csv\",\n",
    "    \"373JourneyDataExtract05Jun2023-11Jun2023.csv\",\n",
    "    \"374JourneyDataExtract12Jun2023-18Jun2023.csv\",\n",
    "    \"375JourneyDataExtract19Jun2023-30Jun2023.csv\",\n",
    "    \"376JourneyDataExtract01Jul2023-14Jul2023.csv\",\n",
    "    \"377JourneyDataExtract15Jul2023-31Jul2023.csv\",\n",
    "    \"378JourneyDataExtract01Aug2023-14Aug2023.csv\",\n",
    "    \"378JourneyDataExtract15Aug2023-31Aug2023.csv\",\n",
    "    \"379JourneyDataExtract01Sep2023-14Sep2023.csv\",\n",
    "    \"380JourneyDataExtract15Sep2023-30Sep2023.csv\",\n",
    "    \"381JourneyDataExtract01Oct2023-14Oct2023.csv\",\n",
    "    \"382JourneyDataExtract15Oct2023-31Oct2023.csv\",\n",
    "    \"383JourneyDataExtract01Nov2023-14Nov2023.csv\",\n",
    "    \"384JourneyDataExtract15Nov2023-30Nov2023.csv\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "26f6ad17-6c94-4ca5-8b8d-0dc0e1bb0b3a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Found in local: 346JourneyDataExtract28Nov2022-04Dec2022.csv\n",
      "Found in local: 347JourneyDataExtract05Dec2022-11Dec2022.csv\n",
      "Found in local: 348JourneyDataExtract12Dec2022-18Dec2022.csv\n",
      "Found in local: 349JourneyDataExtract19Dec2022-25Dec2022.csv\n",
      "Found in local: 350JourneyDataExtract26Dec2022-01Jan2023.csv\n",
      "Found in local: 351JourneyDataExtract02Jan2023-08Jan2023.csv\n",
      "Found in local: 352JourneyDataExtract09Jan2023-15Jan2023.csv\n",
      "Found in local: 353JourneyDataExtract16Jan2023-22Jan2023.csv\n",
      "Found in local: 354JourneyDataExtract23Jan2023-29Jan2023.csv\n",
      "Found in local: 355JourneyDataExtract30Jan2023-05Feb2023.csv\n",
      "Found in local: 356JourneyDataExtract06Feb2023-12Feb2023.csv\n",
      "Found in local: 357JourneyDataExtract13Feb2023-19Feb2023.csv\n",
      "Found in local: 358JourneyDataExtract20Feb2023-26Feb2023.csv\n",
      "Found in local: 359JourneyDataExtract27Feb2023-05Mar2023.csv\n",
      "Found in local: 360JourneyDataExtract06Mar2023-12Mar2023.csv\n",
      "Found in local: 361JourneyDataExtract13Mar2023-19Mar2023.csv\n",
      "Found in local: 362JourneyDataExtract20Mar2023-26Mar2023.csv\n",
      "Found in local: 363JourneyDataExtract27Mar2023-02Apr2023.csv\n",
      "Found in local: 364JourneyDataExtract03Apr2023-09Apr2023.csv\n",
      "Found in local: 365JourneyDataExtract10Apr2023-16Apr2023.csv\n",
      "Found in local: 366JourneyDataExtract17Apr2023-23Apr2023.csv\n",
      "Found in local: 367JourneyDataExtract24Apr2023-30Apr2023.csv\n",
      "Found in local: 368JourneyDataExtract01May2023-07May2023.csv\n",
      "Found in local: 369JourneyDataExtract08May2023-14May2023.csv\n",
      "Found in local: 370JourneyDataExtract15May2023-21May2023.csv\n",
      "Found in local: 371JourneyDataExtract22May2023-28May2023.csv\n",
      "Found in local: 372JourneyDataExtract29May2023-04Jun2023.csv\n",
      "Found in local: 373JourneyDataExtract05Jun2023-11Jun2023.csv\n",
      "Found in local: 374JourneyDataExtract12Jun2023-18Jun2023.csv\n",
      "Found in local: 375JourneyDataExtract19Jun2023-30Jun2023.csv\n",
      "Found in local: 376JourneyDataExtract01Jul2023-14Jul2023.csv\n",
      "Found in local: 377JourneyDataExtract15Jul2023-31Jul2023.csv\n",
      "Found in local: 378JourneyDataExtract01Aug2023-14Aug2023.csv\n",
      "Found in local: 378JourneyDataExtract15Aug2023-31Aug2023.csv\n",
      "Found in local: 379JourneyDataExtract01Sep2023-14Sep2023.csv\n",
      "Found in local: 380JourneyDataExtract15Sep2023-30Sep2023.csv\n",
      "Found in local: 381JourneyDataExtract01Oct2023-14Oct2023.csv\n",
      "Found in local: 382JourneyDataExtract15Oct2023-31Oct2023.csv\n",
      "Found in local: 383JourneyDataExtract01Nov2023-14Nov2023.csv\n",
      "Found in local: 384JourneyDataExtract15Nov2023-30Nov2023.csv\n",
      "Done\n"
     ]
    }
   ],
   "source": [
    "# download journeys\n",
    "\n",
    "# creates saving directory if does not exist\n",
    "if not os.path.exists(journeys_path):\n",
    "    os.makedirs(journeys_path)\n",
    "\n",
    "for fn in source_fn:\n",
    "    # check if local exists\n",
    "    if not os.path.exists(os.path.join(journeys_path, fn)):\n",
    "        source = source_url + fn\n",
    "        print(f\"Downloading   : {fn}\")\n",
    "        with open(os.path.join(journeys_path, fn), \"wb\") as file:\n",
    "            response = get(source)\n",
    "            file.write(response.content)\n",
    "    else:\n",
    "        print(f\"Found in local: {fn}\")\n",
    "\n",
    "print('Done')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a208e9ec-c61b-4ce2-a529-32b1922385ab",
   "metadata": {},
   "source": [
    "## Create dataframe from journeys\n",
    "\n",
    "Using the above dataset, a dataframe of journeys are created in this script below.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ee069c6b-bd55-4147-9232-9124a30f59fb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 12.8 s\n",
      "Wall time: 19.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# test flag, set 1 for testing\n",
    "test_flag = 0\n",
    "\n",
    "\n",
    "journeys_df = []\n",
    "# load data\n",
    "# keeping it as a list to avoid memory crashes in further analysis\n",
    "# merging will come at the very end\n",
    "for idx, fn in enumerate(source_fn):\n",
    "    journeys_df.append(pd.read_csv(os.path.join(journeys_path, fn), low_memory = False))\n",
    "    if test_flag == 1:\n",
    "        break\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d53e64a-6762-4110-9719-da6fb2c18e33",
   "metadata": {},
   "source": [
    "## Join with height data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6a9b1d71-fe87-4b94-ab96-3e26d2661b7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# set path\n",
    "points_path = 'data/cycles/points'\n",
    "points_parquet_fn = 'docking_stations.geoparquet'\n",
    "journeys_df_fn = 'journeys.parquet'\n",
    "\n",
    "# load points gdf\n",
    "points_gdf = gpd.read_parquet(os.path.join(points_path, points_parquet_fn))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6aed82f8-ff0a-4d87-af24-92b1fe463087",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge the points data to the journeys data\n",
    "\n",
    "# add prefix to data points\n",
    "start_points = points_gdf.add_prefix('start_')\n",
    "end_points = points_gdf.add_prefix('end_')\n",
    "\n",
    "# create new list\n",
    "journeys_df_merged = []\n",
    "\n",
    "for df in journeys_df:     \n",
    "    # merge the start point data\n",
    "    temp_df = df.merge(start_points, left_on = 'Start station', right_on = 'start_name')\n",
    "    # merge the end point data\n",
    "    temp_df = temp_df.merge(end_points, left_on = 'End station', right_on = 'end_name')\n",
    "\n",
    "    journeys_df_merged.append(temp_df)\n",
    "\n",
    "# delete the unnneccesary df\n",
    "del(start_points)\n",
    "del(end_points)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "74211f72-9e9e-464c-8a10-a6cc72e47ea7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# extract only the required columns\n",
    "columns = ['Number', 'Start date', 'Start station number', 'start_location', 'start_zone', \n",
    "           'start_LSOA11CD', 'start_LSOA11NM', 'start_MSOA11CD', 'start_MSOA11NM', 'start_cc_zone', 'start_height', 'start_geometry',\n",
    "           'End date', 'End station number', 'end_location', 'end_zone', \n",
    "           'end_LSOA11CD', 'end_LSOA11NM', 'end_MSOA11CD', 'end_MSOA11NM', 'end_cc_zone', 'end_height', 'end_geometry',\n",
    "           'Bike number', 'Bike model', 'Total duration (ms)']\n",
    "\n",
    "for df in journeys_df_merged:\n",
    "    df = df[columns].copy()\n",
    "    # clean dates\n",
    "    dates = ['Start date', 'End date']\n",
    "    for d in dates:\n",
    "        df[d] = pd.to_datetime(df[d], format = '%Y-%m-%d %H:%M')\n",
    "    \n",
    "    # make bike model into categorical data\n",
    "    # this should only have 2 types: 'CLASSIC' and 'PBSC_EBIKE'\n",
    "    df['Bike model'] = df['Bike model'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "816f572b-6c0e-421b-bf8a-17770a2a21f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make one large df from the list of df\n",
    "df_concat = pd.concat(journeys_df_merged, ignore_index = True)\n",
    "\n",
    "# delete the unnneccesary df\n",
    "del(journeys_df_merged)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a658f96c-3541-461e-a1c5-cacfee75f002",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate the height difference\n",
    "df_concat['height_diff'] = df_concat.end_height - df_concat.start_height\n",
    "\n",
    "# save the distance of journey as a column\n",
    "# define as GeoSeries\n",
    "start_geoseries = gpd.GeoSeries(df_concat.start_geometry, crs = 'EPSG:27700')\n",
    "end_geoseries = gpd.GeoSeries(df_concat.end_geometry, crs = 'EPSG:27700')\n",
    "\n",
    "df_concat['distance'] = start_geoseries.distance(end_geoseries, align = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "18752b32-456a-4487-947c-40713a991239",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    8.491612e+06\n",
       "mean    -2.200772e-01\n",
       "std      9.166256e+00\n",
       "min     -4.387600e+01\n",
       "25%     -4.118500e+00\n",
       "50%      0.000000e+00\n",
       "75%      3.757999e+00\n",
       "max      4.387600e+01\n",
       "Name: height_diff, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_concat.height_diff.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "15ae5f73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Number</th>\n",
       "      <th>Start date</th>\n",
       "      <th>Start station number</th>\n",
       "      <th>Start station</th>\n",
       "      <th>End date</th>\n",
       "      <th>End station number</th>\n",
       "      <th>End station</th>\n",
       "      <th>Bike number</th>\n",
       "      <th>Bike model</th>\n",
       "      <th>Total duration</th>\n",
       "      <th>...</th>\n",
       "      <th>end_zone</th>\n",
       "      <th>end_height</th>\n",
       "      <th>end_LSOA11CD</th>\n",
       "      <th>end_LSOA11NM</th>\n",
       "      <th>end_MSOA11CD</th>\n",
       "      <th>end_MSOA11NM</th>\n",
       "      <th>end_cc_zone</th>\n",
       "      <th>end_geometry</th>\n",
       "      <th>height_diff</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>127293434</td>\n",
       "      <td>2022-12-04 23:59</td>\n",
       "      <td>1194</td>\n",
       "      <td>Westminster University, Marylebone</td>\n",
       "      <td>2022-12-05 00:08</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>24140</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>9m 5s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>-1.193249</td>\n",
       "      <td>1540.854577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>127256557</td>\n",
       "      <td>2022-12-02 20:06</td>\n",
       "      <td>1085</td>\n",
       "      <td>Old Quebec Street, Marylebone</td>\n",
       "      <td>2022-12-02 20:11</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>51668</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>5m 17s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>-0.800749</td>\n",
       "      <td>1073.865608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>127222561</td>\n",
       "      <td>2022-12-01 15:48</td>\n",
       "      <td>1085</td>\n",
       "      <td>Old Quebec Street, Marylebone</td>\n",
       "      <td>2022-12-01 15:54</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>57546</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>5m 24s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>-0.800749</td>\n",
       "      <td>1073.865608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>127198699</td>\n",
       "      <td>2022-11-30 18:05</td>\n",
       "      <td>1085</td>\n",
       "      <td>Old Quebec Street, Marylebone</td>\n",
       "      <td>2022-11-30 18:11</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>52833</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>6m 37s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>-0.800749</td>\n",
       "      <td>1073.865608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>127193452</td>\n",
       "      <td>2022-11-30 15:44</td>\n",
       "      <td>1085</td>\n",
       "      <td>Old Quebec Street, Marylebone</td>\n",
       "      <td>2022-11-30 15:49</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>55552</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>5m 18s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>-0.800749</td>\n",
       "      <td>1073.865608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>127268831</td>\n",
       "      <td>2022-12-03 14:15</td>\n",
       "      <td>200096</td>\n",
       "      <td>Green Park Station, Mayfair</td>\n",
       "      <td>2022-12-03 14:36</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>14273</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>21m 42s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>7.563251</td>\n",
       "      <td>2342.151738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>127268836</td>\n",
       "      <td>2022-12-03 14:15</td>\n",
       "      <td>200096</td>\n",
       "      <td>Green Park Station, Mayfair</td>\n",
       "      <td>2022-12-03 14:36</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>53406</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>21m 35s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>7.563251</td>\n",
       "      <td>2342.151738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>127222344</td>\n",
       "      <td>2022-12-01 15:39</td>\n",
       "      <td>200096</td>\n",
       "      <td>Green Park Station, Mayfair</td>\n",
       "      <td>2022-12-01 15:51</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>52680</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>11m 55s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>7.563251</td>\n",
       "      <td>2342.151738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>127200688</td>\n",
       "      <td>2022-11-30 18:45</td>\n",
       "      <td>200096</td>\n",
       "      <td>Green Park Station, Mayfair</td>\n",
       "      <td>2022-11-30 18:59</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>50109</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>14m 44s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>7.563251</td>\n",
       "      <td>2342.151738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>127168974</td>\n",
       "      <td>2022-11-29 17:46</td>\n",
       "      <td>200096</td>\n",
       "      <td>Green Park Station, Mayfair</td>\n",
       "      <td>2022-11-29 18:00</td>\n",
       "      <td>300079</td>\n",
       "      <td>London Street, Paddington</td>\n",
       "      <td>23321</td>\n",
       "      <td>CLASSIC</td>\n",
       "      <td>13m 37s</td>\n",
       "      <td>...</td>\n",
       "      <td>Paddington</td>\n",
       "      <td>26.312</td>\n",
       "      <td>E01033594</td>\n",
       "      <td>Westminster 015G</td>\n",
       "      <td>E02000974</td>\n",
       "      <td>Westminster 015</td>\n",
       "      <td>False</td>\n",
       "      <td>POINT (526821.250 181157.755)</td>\n",
       "      <td>7.563251</td>\n",
       "      <td>2342.151738</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Number        Start date Start station number  \\\n",
       "0  127293434  2022-12-04 23:59                 1194   \n",
       "1  127256557  2022-12-02 20:06                 1085   \n",
       "2  127222561  2022-12-01 15:48                 1085   \n",
       "3  127198699  2022-11-30 18:05                 1085   \n",
       "4  127193452  2022-11-30 15:44                 1085   \n",
       "5  127268831  2022-12-03 14:15               200096   \n",
       "6  127268836  2022-12-03 14:15               200096   \n",
       "7  127222344  2022-12-01 15:39               200096   \n",
       "8  127200688  2022-11-30 18:45               200096   \n",
       "9  127168974  2022-11-29 17:46               200096   \n",
       "\n",
       "                        Start station          End date End station number  \\\n",
       "0  Westminster University, Marylebone  2022-12-05 00:08             300079   \n",
       "1       Old Quebec Street, Marylebone  2022-12-02 20:11             300079   \n",
       "2       Old Quebec Street, Marylebone  2022-12-01 15:54             300079   \n",
       "3       Old Quebec Street, Marylebone  2022-11-30 18:11             300079   \n",
       "4       Old Quebec Street, Marylebone  2022-11-30 15:49             300079   \n",
       "5         Green Park Station, Mayfair  2022-12-03 14:36             300079   \n",
       "6         Green Park Station, Mayfair  2022-12-03 14:36             300079   \n",
       "7         Green Park Station, Mayfair  2022-12-01 15:51             300079   \n",
       "8         Green Park Station, Mayfair  2022-11-30 18:59             300079   \n",
       "9         Green Park Station, Mayfair  2022-11-29 18:00             300079   \n",
       "\n",
       "                 End station  Bike number Bike model Total duration  ...  \\\n",
       "0  London Street, Paddington        24140    CLASSIC          9m 5s  ...   \n",
       "1  London Street, Paddington        51668    CLASSIC         5m 17s  ...   \n",
       "2  London Street, Paddington        57546    CLASSIC         5m 24s  ...   \n",
       "3  London Street, Paddington        52833    CLASSIC         6m 37s  ...   \n",
       "4  London Street, Paddington        55552    CLASSIC         5m 18s  ...   \n",
       "5  London Street, Paddington        14273    CLASSIC        21m 42s  ...   \n",
       "6  London Street, Paddington        53406    CLASSIC        21m 35s  ...   \n",
       "7  London Street, Paddington        52680    CLASSIC        11m 55s  ...   \n",
       "8  London Street, Paddington        50109    CLASSIC        14m 44s  ...   \n",
       "9  London Street, Paddington        23321    CLASSIC        13m 37s  ...   \n",
       "\n",
       "     end_zone end_height end_LSOA11CD      end_LSOA11NM end_MSOA11CD  \\\n",
       "0  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "1  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "2  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "3  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "4  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "5  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "6  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "7  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "8  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "9  Paddington     26.312    E01033594  Westminster 015G    E02000974   \n",
       "\n",
       "      end_MSOA11NM  end_cc_zone                   end_geometry height_diff  \\\n",
       "0  Westminster 015        False  POINT (526821.250 181157.755)   -1.193249   \n",
       "1  Westminster 015        False  POINT (526821.250 181157.755)   -0.800749   \n",
       "2  Westminster 015        False  POINT (526821.250 181157.755)   -0.800749   \n",
       "3  Westminster 015        False  POINT (526821.250 181157.755)   -0.800749   \n",
       "4  Westminster 015        False  POINT (526821.250 181157.755)   -0.800749   \n",
       "5  Westminster 015        False  POINT (526821.250 181157.755)    7.563251   \n",
       "6  Westminster 015        False  POINT (526821.250 181157.755)    7.563251   \n",
       "7  Westminster 015        False  POINT (526821.250 181157.755)    7.563251   \n",
       "8  Westminster 015        False  POINT (526821.250 181157.755)    7.563251   \n",
       "9  Westminster 015        False  POINT (526821.250 181157.755)    7.563251   \n",
       "\n",
       "      distance  \n",
       "0  1540.854577  \n",
       "1  1073.865608  \n",
       "2  1073.865608  \n",
       "3  1073.865608  \n",
       "4  1073.865608  \n",
       "5  2342.151738  \n",
       "6  2342.151738  \n",
       "7  2342.151738  \n",
       "8  2342.151738  \n",
       "9  2342.151738  \n",
       "\n",
       "[10 rows x 37 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_concat.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "0ac24eff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create geodataframe\n",
    "df_concat['geometry'] = df_concat.start_geometry\n",
    "\n",
    "df_concat_gdf = gpd.GeoDataFrame(df_concat, geometry = df_concat.geometry, crs = 'EPSG:27700')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "15ff1739",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert dates into datetime format\n",
    "dates = ['Start date', 'End date']\n",
    "for c in dates:\n",
    "    df_concat_gdf[c] = pd.to_datetime(df_concat_gdf[c], format = '%Y-%m-%d %H:%M')\n",
    "\n",
    "# convert bike model into categories\n",
    "df_concat_gdf['Bike model'] = df_concat_gdf['Bike model'].astype('category')\n",
    "\n",
    "# convert number of ports into numeric\n",
    "numbers = ['start_ports', 'end_ports']\n",
    "for c in numbers:\n",
    "    df_concat_gdf[c] = df_concat_gdf[c].astype('int')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "79123dbb-fb89-4319-9235-60956fbebdd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save file\n",
    "\n",
    "df_concat_fn = 'journeys_df.parquet'\n",
    "df_concat_gdf.drop(['Start station number', 'End station number'], axis = 1).to_parquet(os.path.join(journeys_path, df_concat_fn))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}