get_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "53f22109",
   "metadata": {},
   "source": [
    "# Get BiciMAD data and store it in a database\n",
    "\n",
    "This Notebooks gets the availbale BiciMAd data from https://opendata.emtmadrid.es/Datos-estaticos/Datos-generales-(1) and stores them in a database.\n",
    "\n",
    "The following database tables will be removed (if they already exist) and then created:\n",
    "   * trips\n",
    "   * situation: stations hourly situation\n",
    "   \n",
    "You need to manually download https://datos.madrid.es/egobfiles/MANUAL/208327/bases_bicimad.csv and save it in `data/bases_bicimad.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8a525302",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T15:21:41.460455Z",
     "start_time": "2021-06-04T15:21:39.917987Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pyunpack in /opt/conda/lib/python3.9/site-packages (0.2.2)\n",
      "Requirement already satisfied: easyprocess in /opt/conda/lib/python3.9/site-packages (from pyunpack) (0.3)\n",
      "Requirement already satisfied: entrypoint2 in /opt/conda/lib/python3.9/site-packages (from pyunpack) (0.2.4)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install pyunpack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "212b808e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T16:57:55.200098Z",
     "start_time": "2021-06-04T16:57:55.195424Z"
    }
   },
   "outputs": [],
   "source": [
    "import ipynb.fs\n",
    "from .full.functions import *\n",
    "import os\n",
    "import re\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "from sqlalchemy import create_engine, text\n",
    "from sqlalchemy.types import Integer, Text, Float, DateTime, Boolean\n",
    "import urllib\n",
    "import zipfile\n",
    "from pyunpack import Archive\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "284fe669",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T16:57:55.898153Z",
     "start_time": "2021-06-04T16:57:55.874192Z"
    }
   },
   "outputs": [],
   "source": [
    "# construct an engine connection string\n",
    "engine_string = \"postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}\".format(\n",
    "    user = 'postgres',\n",
    "    password = '{password}',\n",
    "    host = 'postgres-bicimad',\n",
    "    port = 5432,\n",
    "    database = 'postgres',\n",
    ")\n",
    "# create sqlalchemy engine\n",
    "engine = create_engine(engine_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "782da829",
   "metadata": {},
   "source": [
    "## Get trips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e89a4786",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T15:21:43.911169Z",
     "start_time": "2021-06-04T15:21:43.698672Z"
    }
   },
   "outputs": [],
   "source": [
    "base_url = \"https://opendata.emtmadrid.es\"\n",
    "url = base_url + \"/Datos-estaticos/Datos-generales-(1)\"\n",
    "reqs = requests.get(url)\n",
    "soup = BeautifulSoup(reqs.text, \"html.parser\")\n",
    " \n",
    "urls = []\n",
    "for link in soup.find_all('a'):\n",
    "    href = link.get('href')\n",
    "    if href != None and re.match(\"^/getattachment/.*(_Usage_Bicimad|_movements|_movements-json).*.aspx$\", href):\n",
    "        urls.append(base_url + href)\n",
    "\n",
    "extract_dir = \"tmp_trips\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "dbbf8c3c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-13T15:16:42.551019Z",
     "start_time": "2021-05-13T15:16:42.548963Z"
    }
   },
   "outputs": [],
   "source": [
    "for u in urls:\n",
    "    zip_path, _ = urllib.request.urlretrieve(u)\n",
    "    with zipfile.ZipFile(zip_path, \"r\") as f:\n",
    "        f.extractall(extract_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d43454ee",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T15:21:45.892610Z",
     "start_time": "2021-06-04T15:21:45.838902Z"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "stations = load_stations('data/bases_bicimad.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "384a9d10",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T15:21:46.635082Z",
     "start_time": "2021-06-04T15:21:46.540465Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff7cc375310>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = text('DROP TABLE IF EXISTS \"trips\";')\n",
    "engine.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "725ab0b8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T15:21:48.276787Z",
     "start_time": "2021-06-04T15:21:48.272917Z"
    }
   },
   "outputs": [],
   "source": [
    "dtypes = {\n",
    "    \"_id\": Text(),\n",
    "    \"user_day_code\": Text(),\n",
    "    \"idplug_base\": Integer(),\n",
    "    \"user_type\": Integer(),\n",
    "    \"idunplug_base\": Integer(),\n",
    "    \"travel_time\": Integer(),\n",
    "    \"idunplug_station\": Integer(),\n",
    "    \"ageRange\": Integer(),\n",
    "    \"idplug_station\": Integer(),\n",
    "    \"unplug_hourTime\": DateTime(),\n",
    "    \"zip_code\": Float(),\n",
    "    \"hour\": Integer(),\n",
    "    \"day_type\": Boolean(),\n",
    "    \"o_dist_km0\": Float(),\n",
    "    \"d_dist_km0\": Float(),\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5f780cb2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T15:46:19.322368Z",
     "start_time": "2021-06-04T15:21:49.440837Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tmp_trips/.ipynb_checkpoints\n",
      "0.00014448165893554688\n",
      "tmp_trips/201704_Usage_Bicimad.json\n",
      "37.42339324951172\n",
      "tmp_trips/201705_Usage_Bicimad.json\n",
      "44.70170521736145\n",
      "tmp_trips/201706_Usage_Bicimad.json\n",
      "58.63076686859131\n",
      "tmp_trips/201707_Usage_Bicimad.json\n",
      "52.60178565979004\n",
      "tmp_trips/201708_Usage_Bicimad.json\n",
      "46.71214532852173\n",
      "tmp_trips/201709_Usage_Bicimad.json\n",
      "63.4159791469574\n",
      "tmp_trips/201710_Usage_Bicimad.json\n",
      "44.50591015815735\n",
      "tmp_trips/201711_Usage_Bicimad.json\n",
      "23.508381843566895\n",
      "tmp_trips/201712_Usage_Bicimad.json\n",
      "17.07199263572693\n",
      "tmp_trips/201801_Usage_Bicimad.json\n",
      "17.503194093704224\n",
      "tmp_trips/201802_Usage_Bicimad.json\n",
      "15.570703506469727\n",
      "tmp_trips/201803_Usage_Bicimad.json\n",
      "13.14189076423645\n",
      "tmp_trips/201804_Usage_Bicimad.json\n",
      "20.191070795059204\n",
      "tmp_trips/201805_Usage_Bicimad.json\n",
      "33.924418926239014\n",
      "tmp_trips/201806_Usage_Bicimad.json\n",
      "50.87822437286377\n",
      "tmp_trips/201807_Usage_Bicimad.json\n",
      "63.397239208221436\n",
      "tmp_trips/201808_Usage_Bicimad.json\n",
      "45.299758195877075\n",
      "tmp_trips/201809_Usage_Bicimad.json\n",
      "70.88166880607605\n",
      "tmp_trips/201810_Usage_Bicimad.json\n",
      "64.31868863105774\n",
      "tmp_trips/201811_Usage_Bicimad.json\n",
      "51.19065880775452\n",
      "tmp_trips/201812_Usage_Bicimad.json\n",
      "42.73888087272644\n",
      "tmp_trips/201901_Usage_Bicimad.json\n",
      "42.01194739341736\n",
      "tmp_trips/201902_Usage_Bicimad.json\n",
      "37.95501351356506\n",
      "tmp_trips/201903_Usage_Bicimad.json\n",
      "25.149267196655273\n",
      "tmp_trips/201904_Usage_Bicimad.json\n",
      "42.23330497741699\n",
      "tmp_trips/201905_Usage_Bicimad.json\n",
      "37.229966163635254\n",
      "tmp_trips/201906_Usage_Bicimad.json\n",
      "42.84371495246887\n",
      "tmp_trips/201907_movements.json\n",
      "8.410742044448853\n",
      "tmp_trips/201908_movements.json\n",
      "18.56927227973938\n",
      "tmp_trips/201909_movements.json\n",
      "25.966991186141968\n",
      "tmp_trips/201910_movements.json\n",
      "27.426687717437744\n",
      "tmp_trips/201911_movements.json\n",
      "19.48613405227661\n",
      "tmp_trips/201912_movements.json\n",
      "14.828031301498413\n",
      "tmp_trips/202001_movements.json\n",
      "16.692328453063965\n",
      "tmp_trips/202002_movements.json\n",
      "20.835936784744263\n",
      "tmp_trips/202003_movements.json\n",
      "8.878390312194824\n",
      "tmp_trips/202004_movements.json\n",
      "0.5861029624938965\n",
      "tmp_trips/202005_movements.json\n",
      "15.335300922393799\n",
      "tmp_trips/202006_movements.json\n",
      "12.548245429992676\n",
      "tmp_trips/202007_movements.json\n",
      "26.261778593063354\n",
      "tmp_trips/202008_movements.json\n",
      "17.59839701652527\n",
      "tmp_trips/202009_movements.json\n",
      "26.41893982887268\n",
      "tmp_trips/202010_movements.json\n",
      "24.184221982955933\n",
      "tmp_trips/202011_movements.json\n",
      "19.054115295410156\n",
      "tmp_trips/202012_movements.json\n",
      "16.154552698135376\n",
      "tmp_trips/202101_movements.json\n",
      "7.566312789916992\n",
      "tmp_trips/202102_movements.json\n",
      "16.194313049316406\n",
      "tmp_trips/202103_movements.json\n",
      "21.841331958770752\n",
      "tmp_trips/__MACOSX\n",
      "0.0002288818359375\n"
     ]
    }
   ],
   "source": [
    "for f in sorted(os.listdir(extract_dir)):\n",
    "    start = time.time()\n",
    "    print(extract_dir + '/' + f)\n",
    "    if f[-5:] == '.json':\n",
    "        load_trips(stations, extract_dir + '/' + f,\n",
    "                   calc_ratios=False).to_sql('trips',\n",
    "                                             engine,\n",
    "                                             if_exists='append',\n",
    "                                             dtype=dtypes,\n",
    "                                             index=False)\n",
    "    end = time.time()\n",
    "    print(end - start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f671592e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T15:58:21.026578Z",
     "start_time": "2021-06-04T15:58:10.617364Z"
    }
   },
   "outputs": [],
   "source": [
    "with engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "    DELETE FROM\n",
    "        trips T1\n",
    "    USING\n",
    "        trips T2\n",
    "    WHERE\n",
    "        T1.\"_id\" = T2.\"_id\"\n",
    "        AND T1.ctid < T2.ctid; -- delete the \"older\" ones\n",
    "    \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d0f2bd29",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T15:59:16.222331Z",
     "start_time": "2021-06-04T15:58:50.825581Z"
    }
   },
   "outputs": [],
   "source": [
    "with engine.connect() as con:\n",
    "    con.execute('ALTER TABLE trips ADD PRIMARY KEY (\"_id\");')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "f8e9c68b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-02T11:41:31.874757Z",
     "start_time": "2021-06-02T11:41:31.872327Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# df = pd.read_sql(\n",
    "#     \"\"\"SELECT\n",
    "#         \"unplug_hourTime\",\n",
    "#         concat_ws('->', \"idunplug_station\", \"idplug_station\") AS od,\n",
    "#         \"travel_time\",\n",
    "#         AVG(\"travel_time\")\n",
    "#             OVER (\n",
    "#             PARTITION BY \"idunplug_station\", \"idplug_station\"\n",
    "#             ORDER BY \"unplug_hourTime\" \n",
    "#                   RANGE BETWEEN '28 day' PRECEDING AND current row)\n",
    "#             AS rolling_average\n",
    "#     FROM\n",
    "#         trips\n",
    "#     LIMIT\n",
    "#         10000\n",
    "#     \"\"\", engine)\n",
    "# df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "3d27025e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-02T11:41:35.405847Z",
     "start_time": "2021-06-02T11:41:35.403816Z"
    }
   },
   "outputs": [],
   "source": [
    "# px.line(df, x='unplug_hourTime', y=['rolling_average'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "52c49e61",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T16:23:50.323908Z",
     "start_time": "2021-06-04T16:19:01.903911Z"
    }
   },
   "outputs": [],
   "source": [
    "with engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "    ALTER TABLE trips \n",
    "        ADD COLUMN IF NOT EXISTS \"od_time_ratio2\" real;\n",
    "    ALTER TABLE trips \n",
    "        ADD COLUMN IF NOT EXISTS \"o_time_ratio2\" real;\n",
    "    ALTER TABLE trips \n",
    "        ADD COLUMN IF NOT EXISTS \"d_time_ratio2\" real;\n",
    "        \n",
    "    UPDATE trips\n",
    "        SET\n",
    "            \"od_time_ratio2\" = subquery.od,\n",
    "            \"o_time_ratio2\" = subquery.o,\n",
    "            \"d_time_ratio2\" = subquery.d\n",
    "        FROM (\n",
    "            SELECT\n",
    "                \"_id\",\n",
    "                CASE\n",
    "                    WHEN \"travel_time\" BETWEEN 1 AND 8 * 3600 THEN\n",
    "                        \"travel_time\" / NULLIF(GREATEST(0, AVG(\"travel_time\")\n",
    "                            OVER (\n",
    "                            PARTITION BY \"idunplug_station\", \"idplug_station\"\n",
    "                            ORDER BY \"unplug_hourTime\" \n",
    "                                  RANGE BETWEEN '28 day' PRECEDING AND current row)), 0)\n",
    "                    ELSE\n",
    "                        NULL\n",
    "                END\n",
    "                    AS od,\n",
    "                CASE\n",
    "                    WHEN \"travel_time\" BETWEEN 1 AND 8 * 3600 THEN\n",
    "                        \"travel_time\" / NULLIF(GREATEST(0, AVG(\"travel_time\")\n",
    "                            OVER (\n",
    "                            PARTITION BY \"idunplug_station\"\n",
    "                            ORDER BY \"unplug_hourTime\" \n",
    "                                  RANGE BETWEEN '28 day' PRECEDING AND current row)), 0)\n",
    "                    ELSE\n",
    "                        NULL\n",
    "                END\n",
    "                    AS o,\n",
    "                CASE\n",
    "                    WHEN \"travel_time\" BETWEEN 1 AND 8 * 3600 THEN\n",
    "                        \"travel_time\" / NULLIF(GREATEST(0, AVG(\"travel_time\")\n",
    "                            OVER (\n",
    "                            PARTITION BY \"idplug_station\"\n",
    "                            ORDER BY \"unplug_hourTime\" \n",
    "                                  RANGE BETWEEN '28 day' PRECEDING AND current row)), 0)\n",
    "                    ELSE\n",
    "                        NULL\n",
    "                END\n",
    "                    AS d\n",
    "            FROM\n",
    "                trips\n",
    "            ) as subquery\n",
    "        WHERE\n",
    "            trips.\"_id\" = subquery.\"_id\"\n",
    "    \"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2d23c7c",
   "metadata": {},
   "source": [
    "## Get statuses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "072d6664",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T16:58:00.401430Z",
     "start_time": "2021-06-04T16:58:00.220973Z"
    }
   },
   "outputs": [],
   "source": [
    "base_url = \"https://opendata.emtmadrid.es\"\n",
    "url = base_url + \"/Datos-estaticos/Datos-generales-(1)\"\n",
    "reqs = requests.get(url)\n",
    "soup = BeautifulSoup(reqs.text, \"html.parser\")\n",
    " \n",
    "urls = []\n",
    "for link in soup.find_all('a'):\n",
    "    href = link.get('href')\n",
    "    if href != None and re.match(\"^/getattachment/.*/(Bicimad_[Ee]?[sS]ta[ct]ions_\\d{6}|BiciMAD_stations_20\\d{2}_\\d_\\d|\\d{6}(_stations|-json)?).aspx$\", href):\n",
    "        urls.append(base_url + href)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f4baff7a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T16:58:00.728773Z",
     "start_time": "2021-06-04T16:58:00.726529Z"
    }
   },
   "outputs": [],
   "source": [
    "extract_dir = \"tmp_situations\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "914afbb8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-05-31T09:51:25.481137Z",
     "start_time": "2021-05-31T09:50:55.422757Z"
    },
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "for u in urls:\n",
    "    zip_path, _ = urllib.request.urlretrieve(u)\n",
    "    try:\n",
    "        with zipfile.ZipFile(zip_path, \"r\") as f:\n",
    "            f.extractall(extract_dir)\n",
    "    except zipfile.BadZipFile:\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "317807c8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T16:58:02.148623Z",
     "start_time": "2021-06-04T16:58:02.132014Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f522d77fc10>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = text('DROP TABLE IF EXISTS \"situations\";')\n",
    "engine.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b8497e04",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T16:58:02.793736Z",
     "start_time": "2021-06-04T16:58:02.789624Z"
    }
   },
   "outputs": [],
   "source": [
    "dtypes = {\n",
    "    \"date\": DateTime(),\n",
    "    \"activate\": Boolean(),\n",
    "    \"name\": Text(),\n",
    "    \"reservations_count\": Integer(),\n",
    "    \"light\": Integer(),\n",
    "    \"total_bases\": Integer(),\n",
    "    \"free_bases\": Integer(),\n",
    "    \"number\": Text(),\n",
    "    \"longitude\": Float(),\n",
    "    \"no_available\": Integer(),\n",
    "    \"address\": Text(),\n",
    "    \"latitude\": Float(),\n",
    "    \"dock_bikes\": Integer(),\n",
    "    \"id\": Integer(),\n",
    "    \"hour\": Integer(),\n",
    "    \"day_type\": Boolean(),\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3520b426",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-04T17:01:15.121750Z",
     "start_time": "2021-06-04T16:58:03.627380Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tmp_situations/202001.json\n",
      "9.698063611984253\n",
      "tmp_situations/202002.json\n",
      "9.119324207305908\n",
      "tmp_situations/202003.json\n",
      "9.77554726600647\n",
      "tmp_situations/202004.json\n",
      "9.460395097732544\n",
      "tmp_situations/202005.json\n",
      "9.856130838394165\n",
      "tmp_situations/202006_stations.json\n",
      "9.56312346458435\n",
      "tmp_situations/202007.json\n",
      "9.747358083724976\n",
      "tmp_situations/202008.json\n",
      "9.879436016082764\n",
      "tmp_situations/202009.json\n",
      "9.611745834350586\n",
      "tmp_situations/202010.json\n",
      "9.831257104873657\n",
      "tmp_situations/202011.json\n",
      "9.827429056167603\n",
      "tmp_situations/202012.json\n",
      "11.081334114074707\n",
      "tmp_situations/202101.json\n",
      "12.08172345161438\n",
      "tmp_situations/202102.json\n",
      "10.77942419052124\n",
      "tmp_situations/202103.json\n",
      "12.518405199050903\n",
      "tmp_situations/Bicimad_Estacions_201809.json\n",
      "7.693673849105835\n",
      "tmp_situations/Bicimad_Estacions_201810.json\n",
      "8.031645774841309\n",
      "tmp_situations/Bicimad_Stations_201811.json\n",
      "7.750242233276367\n",
      "tmp_situations/Bicimad_Stations_201812.json\n",
      "7.609273910522461\n",
      "tmp_situations/Bicimad_Stations_201901.json\n",
      "7.5708630084991455\n",
      "tmp_situations/__MACOSX\n",
      "1.33514404296875e-05\n"
     ]
    }
   ],
   "source": [
    "for f in sorted(os.listdir(extract_dir)):\n",
    "    start = time.time()\n",
    "    print(extract_dir + '/' + f)\n",
    "    if f[-5:] == '.json':\n",
    "        load_situations(extract_dir + '/' + f).to_sql('situations',\n",
    "                                                      engine,\n",
    "                                                      if_exists='append',\n",
    "                                                      index=False,\n",
    "                                                      dtype=dtypes)\n",
    "    end = time.time()\n",
    "    print(end - start)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54c0f6e4",
   "metadata": {},
   "source": [
    "## Reading from the db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "13013f50",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-02T08:43:25.120420Z",
     "start_time": "2021-06-02T08:43:25.093963Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.read_sql(\"SELECT * FROM trips LIMIT 1000\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2d39f085",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-02T08:43:25.667197Z",
     "start_time": "2021-06-02T08:43:25.646936Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>_id</th>\n",
       "      <th>user_day_code</th>\n",
       "      <th>idplug_base</th>\n",
       "      <th>user_type</th>\n",
       "      <th>idunplug_base</th>\n",
       "      <th>travel_time</th>\n",
       "      <th>idunplug_station</th>\n",
       "      <th>ageRange</th>\n",
       "      <th>idplug_station</th>\n",
       "      <th>unplug_hourTime</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>od_time_ratio</th>\n",
       "      <th>o_time_ratio</th>\n",
       "      <th>d_time_ratio</th>\n",
       "      <th>hour</th>\n",
       "      <th>day_type</th>\n",
       "      <th>o_dist_km0</th>\n",
       "      <th>d_dist_km0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>58e5e5e7865e9c221ca45898</td>\n",
       "      <td>df843a74bba22d2b363e4e96f74b99391276e71dfea92e...</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>169</td>\n",
       "      <td>41</td>\n",
       "      <td>4</td>\n",
       "      <td>50</td>\n",
       "      <td>2017-03-31 23:00:00+00:00</td>\n",
       "      <td>28005.0</td>\n",
       "      <td>0.670009</td>\n",
       "      <td>0.143761</td>\n",
       "      <td>0.167736</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>636.518095</td>\n",
       "      <td>1288.365395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>58e5e5e7865e9c221ca458a7</td>\n",
       "      <td>6a7cf4393301d25f5a2eeaff8c7ba5d406f514ebd086aa...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>271</td>\n",
       "      <td>95</td>\n",
       "      <td>5</td>\n",
       "      <td>90</td>\n",
       "      <td>2017-03-31 23:00:00+00:00</td>\n",
       "      <td>28043.0</td>\n",
       "      <td>0.290996</td>\n",
       "      <td>0.262172</td>\n",
       "      <td>0.246034</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1580.506989</td>\n",
       "      <td>1949.659347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>58e5e5e7865e9c221ca458b2</td>\n",
       "      <td>877deab4cc256bb87e02abc1db75005ac2b66d65be8849...</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>171</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>2017-03-31 23:00:00+00:00</td>\n",
       "      <td>28053.0</td>\n",
       "      <td>0.737705</td>\n",
       "      <td>0.169862</td>\n",
       "      <td>0.177441</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1117.123486</td>\n",
       "      <td>769.549748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>58e5e5e7865e9c221ca458b8</td>\n",
       "      <td>91bbf6c3ba396f84cce4e96e59be42b0824a16c8a1bf89...</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>388</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>57</td>\n",
       "      <td>2017-03-31 23:00:00+00:00</td>\n",
       "      <td>28012.0</td>\n",
       "      <td>0.388014</td>\n",
       "      <td>0.370689</td>\n",
       "      <td>0.362608</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>551.058095</td>\n",
       "      <td>1635.858302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>58e5e5e7865e9c221ca458b9</td>\n",
       "      <td>4476cabb023b8b4473e5b6e78a1a79e0ebc8910c8e9989...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>381</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>43</td>\n",
       "      <td>2017-03-31 23:00:00+00:00</td>\n",
       "      <td>28042.0</td>\n",
       "      <td>0.581058</td>\n",
       "      <td>0.364001</td>\n",
       "      <td>0.353920</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>551.058095</td>\n",
       "      <td>731.321696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>995</td>\n",
       "      <td>58e5e5ed865e9c221ca45d0a</td>\n",
       "      <td>4843b8e8a0587a1745305d5fc58066408480383ebfc240...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>390</td>\n",
       "      <td>99</td>\n",
       "      <td>4</td>\n",
       "      <td>45</td>\n",
       "      <td>2017-04-01 03:00:00+00:00</td>\n",
       "      <td>28012.0</td>\n",
       "      <td>0.729881</td>\n",
       "      <td>0.405945</td>\n",
       "      <td>0.451409</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2746.394035</td>\n",
       "      <td>1237.860197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>996</td>\n",
       "      <td>58e5e5ed865e9c221ca45d0c</td>\n",
       "      <td>b8953f56422257e71ca31b5c347dfeb7cb42ca4fac12a3...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>210</td>\n",
       "      <td>173</td>\n",
       "      <td>3</td>\n",
       "      <td>138</td>\n",
       "      <td>2017-04-01 03:00:00+00:00</td>\n",
       "      <td>28002.0</td>\n",
       "      <td>0.083129</td>\n",
       "      <td>0.184534</td>\n",
       "      <td>0.160503</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2049.725535</td>\n",
       "      <td>3393.410018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>997</td>\n",
       "      <td>58e5e5ed865e9c221ca45d0d</td>\n",
       "      <td>17031713ced93bcea71352ed10899dd9e6ccdb252514b1...</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>763</td>\n",
       "      <td>74</td>\n",
       "      <td>5</td>\n",
       "      <td>135</td>\n",
       "      <td>2017-04-01 03:00:00+00:00</td>\n",
       "      <td>28037.0</td>\n",
       "      <td>0.579787</td>\n",
       "      <td>0.695482</td>\n",
       "      <td>0.510137</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2375.574276</td>\n",
       "      <td>2915.287342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>998</td>\n",
       "      <td>58e5e5ed865e9c221ca45d0e</td>\n",
       "      <td>ae009ae55b6542d112edca55fa5cb772c4d5d6b7d4178a...</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>572</td>\n",
       "      <td>81</td>\n",
       "      <td>4</td>\n",
       "      <td>171</td>\n",
       "      <td>2017-04-01 03:00:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.987058</td>\n",
       "      <td>0.442372</td>\n",
       "      <td>0.461608</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1274.381217</td>\n",
       "      <td>1924.914926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>999</td>\n",
       "      <td>58e5e5ed865e9c221ca45d0f</td>\n",
       "      <td>bd0870f56bdf71dec1f0bfb36924233a8cc6eb2e1fb158...</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>397</td>\n",
       "      <td>37</td>\n",
       "      <td>3</td>\n",
       "      <td>47</td>\n",
       "      <td>2017-04-01 03:00:00+00:00</td>\n",
       "      <td>28012.0</td>\n",
       "      <td>1.378841</td>\n",
       "      <td>0.374636</td>\n",
       "      <td>0.346381</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1080.732454</td>\n",
       "      <td>1333.108011</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index                       _id  \\\n",
       "0        0  58e5e5e7865e9c221ca45898   \n",
       "1        1  58e5e5e7865e9c221ca458a7   \n",
       "2        2  58e5e5e7865e9c221ca458b2   \n",
       "3        3  58e5e5e7865e9c221ca458b8   \n",
       "4        4  58e5e5e7865e9c221ca458b9   \n",
       "..     ...                       ...   \n",
       "995    995  58e5e5ed865e9c221ca45d0a   \n",
       "996    996  58e5e5ed865e9c221ca45d0c   \n",
       "997    997  58e5e5ed865e9c221ca45d0d   \n",
       "998    998  58e5e5ed865e9c221ca45d0e   \n",
       "999    999  58e5e5ed865e9c221ca45d0f   \n",
       "\n",
       "                                         user_day_code  idplug_base  \\\n",
       "0    df843a74bba22d2b363e4e96f74b99391276e71dfea92e...           17   \n",
       "1    6a7cf4393301d25f5a2eeaff8c7ba5d406f514ebd086aa...            2   \n",
       "2    877deab4cc256bb87e02abc1db75005ac2b66d65be8849...            5   \n",
       "3    91bbf6c3ba396f84cce4e96e59be42b0824a16c8a1bf89...           18   \n",
       "4    4476cabb023b8b4473e5b6e78a1a79e0ebc8910c8e9989...            2   \n",
       "..                                                 ...          ...   \n",
       "995  4843b8e8a0587a1745305d5fc58066408480383ebfc240...            1   \n",
       "996  b8953f56422257e71ca31b5c347dfeb7cb42ca4fac12a3...            3   \n",
       "997  17031713ced93bcea71352ed10899dd9e6ccdb252514b1...           14   \n",
       "998  ae009ae55b6542d112edca55fa5cb772c4d5d6b7d4178a...           16   \n",
       "999  bd0870f56bdf71dec1f0bfb36924233a8cc6eb2e1fb158...           15   \n",
       "\n",
       "     user_type  idunplug_base  travel_time  idunplug_station  ageRange  \\\n",
       "0            1              1          169                41         4   \n",
       "1            1             14          271                95         5   \n",
       "2            1              5          171                15         4   \n",
       "3            1             20          388                23         4   \n",
       "4            1              2          381                23         4   \n",
       "..         ...            ...          ...               ...       ...   \n",
       "995          1             10          390                99         4   \n",
       "996          1             12          210               173         3   \n",
       "997          1              6          763                74         5   \n",
       "998          3              3          572                81         4   \n",
       "999          1             15          397                37         3   \n",
       "\n",
       "     idplug_station           unplug_hourTime  zip_code  od_time_ratio  \\\n",
       "0                50 2017-03-31 23:00:00+00:00   28005.0       0.670009   \n",
       "1                90 2017-03-31 23:00:00+00:00   28043.0       0.290996   \n",
       "2                16 2017-03-31 23:00:00+00:00   28053.0       0.737705   \n",
       "3                57 2017-03-31 23:00:00+00:00   28012.0       0.388014   \n",
       "4                43 2017-03-31 23:00:00+00:00   28042.0       0.581058   \n",
       "..              ...                       ...       ...            ...   \n",
       "995              45 2017-04-01 03:00:00+00:00   28012.0       0.729881   \n",
       "996             138 2017-04-01 03:00:00+00:00   28002.0       0.083129   \n",
       "997             135 2017-04-01 03:00:00+00:00   28037.0       0.579787   \n",
       "998             171 2017-04-01 03:00:00+00:00       NaN       0.987058   \n",
       "999              47 2017-04-01 03:00:00+00:00   28012.0       1.378841   \n",
       "\n",
       "     o_time_ratio  d_time_ratio  hour  day_type   o_dist_km0   d_dist_km0  \n",
       "0        0.143761      0.167736     1         0   636.518095  1288.365395  \n",
       "1        0.262172      0.246034     1         0  1580.506989  1949.659347  \n",
       "2        0.169862      0.177441     1         0  1117.123486   769.549748  \n",
       "3        0.370689      0.362608     1         0   551.058095  1635.858302  \n",
       "4        0.364001      0.353920     1         0   551.058095   731.321696  \n",
       "..            ...           ...   ...       ...          ...          ...  \n",
       "995      0.405945      0.451409     5         0  2746.394035  1237.860197  \n",
       "996      0.184534      0.160503     5         0  2049.725535  3393.410018  \n",
       "997      0.695482      0.510137     5         0  2375.574276  2915.287342  \n",
       "998      0.442372      0.461608     5         0  1274.381217  1924.914926  \n",
       "999      0.374636      0.346381     5         0  1080.732454  1333.108011  \n",
       "\n",
       "[1000 rows x 19 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}