pandas-benchmark-bmtc-stoptimes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Testing Pandas changing a trip in stop_times.txt\n",
    "To do: \n",
    "- Open / load stop_times.txt\n",
    "- Retrieve a trip's entries\n",
    "- Do some changes or bring in another array\n",
    "- Delete those entries\n",
    "- Append the changed / new entries to the file\n",
    "- Measure how much time this all took.\n",
    "- Do this on a pure python program later.\n",
    "\n",
    "Where we're doing it : A 42MB stop_times.txt file having 1,176,283 entries.  \n",
    "Why: Need to benchmark "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import time\n",
    "stop_times = 'GTFSbmtc/test/stop_times.txt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          trip_id  route_id  stop_id  stop_sequence arrival_time  \\\n",
      "1175075  994_21_d       994       24              1     15:25:00   \n",
      "1175076  994_21_d       994     2034              2     15:29:14   \n",
      "1175077  994_21_d       994      624              3     15:33:28   \n",
      "1175078  994_21_d       994     1802              4     15:37:42   \n",
      "1175079  994_21_d       994     1803              5     15:41:56   \n",
      "1175080  994_21_d       994     1804              6     15:46:10   \n",
      "1175081  994_21_d       994     1876              7     15:50:24   \n",
      "1175082  994_21_d       994     1958              8     15:54:38   \n",
      "1175083  994_21_d       994     1959              9     15:58:52   \n",
      "1175084  994_21_d       994     1960             10     16:03:06   \n",
      "1175085  994_21_d       994     1961             11     16:07:20   \n",
      "1175086  994_21_d       994     1962             12     16:11:34   \n",
      "1175087  994_21_d       994     1963             13     16:15:48   \n",
      "1175088  994_21_d       994     1951             14     16:20:02   \n",
      "\n",
      "        departure_time  \n",
      "1175075       15:25:00  \n",
      "1175076       15:29:14  \n",
      "1175077       15:33:28  \n",
      "1175078       15:37:42  \n",
      "1175079       15:41:56  \n",
      "1175080       15:46:10  \n",
      "1175081       15:50:24  \n",
      "1175082       15:54:38  \n",
      "1175083       15:58:52  \n",
      "1175084       16:03:06  \n",
      "1175085       16:07:20  \n",
      "1175086       16:11:34  \n",
      "1175087       16:15:48  \n",
      "1175088       16:20:02  \n",
      "took 3.38 seconds.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "df = pd.read_csv(stop_times, na_filter=False)\n",
    "tripEntries = df.query(\"trip_id == '994_21_d'\")\n",
    "print(tripEntries)\n",
    "end = time.time()\n",
    "print(\"took {} seconds.\".format(round(end-start,2)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now testing the same on tinydb.. but first, let's LOAD stop_times.txt into tinyDB:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tinydb import TinyDB, Query\n",
    "from tinydb.operations import delete\n",
    "start = time.time()\n",
    "dbfile = 'GTFSbmtc/test/stop_times.json'\n",
    "db = TinyDB(dbfile, sort_keys=True, indent=2)\n",
    "db.purge_tables()\n",
    "\n",
    "dfInsert = pd.read_csv(stop_times, na_filter=False).to_dict(orient='records')\n",
    "feedDb = db.table('stop_times')\n",
    "'''\n",
    "feedDb.insert_multiple(dfInsert)\n",
    "del dfInsert\n",
    "'''\n",
    "db.close()\n",
    "end = time.time()\n",
    "print(\"Time taken: {} seconds.\".format(round(end-start,2)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "238 seconds it took to import stop__times.txt into tinydb json.  \n",
    "To purge the same, it took: 428 seconds.  \n",
    "WTF. Forget editing.. this will make the import and process times itself bloody painfull."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### back to pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%timeit df = pd.read_csv(stop_times, na_filter=False)\n",
    "trip_id = '1991_1_d'\n",
    "%timeit tripEntries = df.query('trip_id == \"' + trip_id + '\"')\n",
    "df = pd.read_csv(stop_times, na_filter=False)\n",
    "tripEntries = df.query('trip_id == \"' + trip_id + '\"')\n",
    "print(tripEntries)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inference: It seemed to take around 10 secs the very first time I ran it, but in subsequent runs (including with other values of trip_id) it got smaller.  \n",
    "Using %%time as advised [here](https://engineering.upside.com/a-beginners-guide-to-optimizing-pandas-code-for-speed-c09ef2c6a4d6), the analysis:  \n",
    "> The slowest run took 4.23 times longer than the fastest. This could mean that an intermediate result is being cached.  \n",
    "> 5.86 s ± 2.52 s per loop (mean ± std. dev. of 7 runs, 1 loop each)\n",
    "\n",
    "Edit: Using %timeit on individual lines instead. There it shows the the **file reading** is taking up most of the time, whereas the **query is taking much lesser time.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting a trip's rows from stop_times\n",
    "Ref Links: \n",
    "- https://stackoverflow.com/questions/18172851/deleting-dataframe-row-in-pandas-based-on-column-value  \n",
    "> df.query('line_race != 0')\n",
    "\n",
    "- https://stackoverflow.com/questions/30249697/fast-efficient-way-to-remove-rows-from-large-pandas-dataframe\n",
    "- https://pandas.pydata.org/pandas-docs/stable/comparison_with_sql.html#delete\n",
    "- https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.query.html  \n",
    "> try with inplace=True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%timeit\n",
    "start = time.time()\n",
    "%timeit df = pd.read_csv(stop_times, na_filter=False)\n",
    "trip_id = '1991_1_d'\n",
    "%timeit df.query('trip_id != \"' + trip_id + '\"',inplace=True)\n",
    "# this command edits the dataframe in place, to include only rows where trip_id is not '1991_1_d'\n",
    "\n",
    "end = time.time()\n",
    "print(\"took {} seconds.\".format(round(end-start,2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# checking df to see if that trip_id entries are there any longer\n",
    "df.query('trip_id == \"' + trip_id + '\"')\n",
    "# yep, it's gone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# try loc :: tips = tips.loc[tips['tip'] <= 9]\n",
    "start = time.time()\n",
    "%timeit df = pd.read_csv(stop_times, na_filter=False)\n",
    "trip_id = '1991_1_d'\n",
    "%timeit df2 = df.loc[df['trip_id'] != trip_id]\n",
    "end = time.time()\n",
    "print(\"took {} seconds.\".format(round(end-start,2)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inference from delete trials:\n",
    "- df.query seems to be marginally faster than df.loc. So, let's go with that. Anyways there's possibility of queries getting complicated. \n",
    "- More time seems to be going in reading the CSV.\n",
    "\n",
    "### How about writing to CSV after deleting:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%timeit df = pd.read_csv(stop_times, na_filter=False)\n",
    "trip_id = '1991_1_d'\n",
    "%timeit df.query('trip_id != \"' + trip_id + '\"',inplace=True)\n",
    "outfile = 'GTFSbmtc/test/stop_times_edited.txt'\n",
    "%timeit df.to_csv(outfile, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Full edit : remove a trip's entries and insert a modified set of entries\n",
    "Anyways we'll be doing the deleting only when we actually receive the modified set from frontend.  \n",
    "Ref:  \n",
    "- https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.append.html\n",
    "- "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tripEntries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# since our incoming stop_times data will be in the form of a dict, let's simulate that\n",
    "dict2Add = tripEntries.to_dict('records')\n",
    "dict2Add[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# reading csv.. this might already be done but let's time it anyways\n",
    "%timeit df = pd.read_csv(stop_times, na_filter=False)\n",
    "trip_id = '1991_1_d'\n",
    "# removing entries for trip = 1991_1_d\n",
    "%timeit df.query('trip_id != \"' + trip_id + '\"',inplace=True)\n",
    "\n",
    "# now we append.. \n",
    "%timeit df2add = pd.DataFrame(dict2Add)\n",
    "%timeit df2 = df.append(df2add, ignore_index=True)\n",
    "\n",
    "%timeit df2.query('trip_id == \"' + trip_id + '\"')\n",
    "\n",
    "outfile = 'GTFSbmtc/test/stop_times_edited.txt'\n",
    "\n",
    "%timeit df.to_csv(outfile, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inference:  \n",
    "- deleting entries took more time than appending, but still they're ~10x less as compared to reading the file\n",
    "- Writing the new file takes ~10x more time than reading, a little over 10s on my machine. So this is the most expensive part of it all.\n",
    "- But compared to the ~300 secs of tinydb this ~10sec writing time for stop_times.csv is still small and it's possible for "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Possible workflows\n",
    "\n",
    "#### Proposal 1:\n",
    "- web_wrapper.py loads all the CSV files as dataframes at start of session, before any API is called.\n",
    "- all changes made to dataframes only.\n",
    "- Write out to CSVs when commit.\n",
    "- Obvious drawback : Ctrl+C the main program and everything is GONE! Scratch it..\n",
    "\n",
    "#### Proposal 2:\n",
    "- load all the CSVs as dataframes from beginning.\n",
    "- At every \"Save changes..\" step, change the dataframe accordingly AND write out to that changed DF's CSV.\n",
    "- Other tables will be fine, but this entails waiting about 10sec when saving any changes to stop_times."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check append vs concat\n",
    "- https://stackoverflow.com/questions/15819050/pandas-dataframe-concat-vs-append"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1176283\n",
      "36\n",
      "1176247\n",
      "131 ms ± 4.12 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "1176283\n",
      "141 ms ± 12.6 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "1176283\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(stop_times, na_filter=False)\n",
    "print(len(df))\n",
    "trip_id = '1991_1_d'\n",
    "\n",
    "# extract trip entries for adding later\n",
    "tripEntries = df.query('trip_id == \"' + trip_id + '\"')\n",
    "print(len(tripEntries))\n",
    "\n",
    "# removing entries for trip = 1991_1_d\n",
    "df.query('trip_id != \"' + trip_id + '\"',inplace=True)\n",
    "print(len(df))\n",
    "\n",
    "# append.\n",
    "%timeit df2 = df.append(tripEntries, ignore_index=True)\n",
    "df2 = df.append(tripEntries, ignore_index=True)\n",
    "print(len(df2))\n",
    "\n",
    "# concat\n",
    "%timeit df3 = pd.concat([df,tripEntries], ignore_index=True)\n",
    "df3 = pd.concat([df,tripEntries], ignore_index=True)\n",
    "print(len(df3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "... **concat wins by a hair**\n",
    "or.. WAIT, now concat is coming a few ms late! Darn!  \n",
    "Nope, when trying in actual python script, concat wins."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check out HDF5\n",
    "- https://stackoverflow.com/questions/16628329/hdf5-concurrency-compression-i-o-performance\n",
    "- http://pandas-docs.github.io/pandas-docs-travis/10min.html#hdf5\n",
    "- https://stackoverflow.com/questions/19639596/efficiently-writing-large-pandas-data-frames-to-disk\n",
    "\n",
    "Tried running once.. errored out saying :  \n",
    "`ImportError: HDFStore requires PyTables, \"No module named 'tables'\" problem importing`\n",
    "\n",
    "tried direction installing tables via pip, errored out.\n",
    "\n",
    "Looking it up.. https://github.com/PyTables/PyTables\n",
    "For installing in Ununtu they gave a apt-get install command:  \n",
    "`https://github.com/PyTables/PyTables`  \n",
    "\n",
    "After pip install tables worked. (did sudo.. should put --user at the end next time!)\n",
    "\n",
    "testing command:  \n",
    "```\n",
    "python3 -m tables.tests.test_all\n",
    "-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=\n",
    "PyTables version:    3.4.3\n",
    "HDF5 version:        1.8.18\n",
    "NumPy version:       1.13.3\n",
    "Numexpr version:     2.6.4 (not using Intel's VML/MKL)\n",
    "Zlib version:        1.2.8 (in Python interpreter)\n",
    "LZO version:         2.09 (Feb 04 2015)\n",
    "BZIP2 version:       1.0.6 (6-Sept-2010)\n",
    "Blosc version:       1.14.3 (2018-04-06)\n",
    "Blosc compressors:   blosclz (1.1.0), lz4 (1.8.1), lz4hc (1.8.1), snappy (1.1.1), zlib (1.2.8), zstd (1.3.4)\n",
    "Blosc filters:       shuffle, bitshuffle\n",
    "Cython version:      0.27.3\n",
    "Python version:      3.5.2 (default, Nov 23 2017, 16:37:01) \n",
    "[GCC 5.4.0 20160609]\n",
    "Platform:            Linux-4.13.0-38-generic-i686-with-Ubuntu-16.04-xenial\n",
    "Byte-ordering:       little\n",
    "Detected cores:      2\n",
    "Default encoding:    utf-8\n",
    "Default FS encoding: utf-8\n",
    "Default locale:      (en_IN, ISO8859-1)\n",
    "-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(stop_times, na_filter=False)\n",
    "\n",
    "hdf5 = 'GTFSbmtc/test/stop_times.h5'\n",
    "%timeit df.to_hdf(hdf5,'df',mode='w')\n",
    "# have to put ,mode='w' so that it over-writes instead of appending\n",
    "\n",
    "outfile = 'GTFSbmtc/test/stop_times_edited.txt'\n",
    "%timeit df.to_csv(outfile, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inferenced: **HDF5 Rocks**  \n",
    "Also, apparently we can store multiple dataframes inside, just like tables in tinydb.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# testing all forms of HDF5 : fixed and table\n",
    "df = pd.read_csv(stop_times, na_filter=False)\n",
    "\n",
    "# Fixed HDF5:\n",
    "hdf5 = 'GTFSbmtc/test/stop_times.h5'\n",
    "%timeit df.to_hdf(hdf5,'df',mode='w')\n",
    "%timeit hdFixed = pd.read_hdf(hdf5,'df')\n",
    "\n",
    "# Table HDF5:\n",
    "hdf5Table = 'GTFSbmtc/test/stop_times-table.h5'\n",
    "%timeit df.to_hdf(hdf5Table,'df',format='table',mode='w')\n",
    "# %timeit hdTable = pd.read_hdf(hdf5Table,'df') # it's crashing the laptop here! Abort!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "My laptop is HANGING for the command for reading HDF5 in table form. So, let's leave it. Anyways, the table way writing also is showing as too slow. So, let's go with the default FIXED form of HDF5."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### terminal run\n",
    "Ran an independent python script to benchmarks times. Output of two runs:  \n",
    "```\n",
    "$ python3 edit-table.py\n",
    "Load csv took 2.143 seconds.\n",
    "36\n",
    "Querying out a trip's entries took 0.5772 seconds.\n",
    "1176247\n",
    "Removing a trip's entries from full df took 0.1984 seconds.\n",
    "1176283\n",
    "Appending trip entires to full df took 0.1177 seconds.\n",
    "1176283\n",
    "Concatenating trip entires to full df took 0.1142 seconds.\n",
    "Storing in .h5 file took 1.8359 seconds.\n",
    "Storing in .csv file took 9.7207 seconds.\n",
    "Reading .h5 file took 0.5111 seconds.\n",
    "\n",
    "\n",
    "\n",
    "$ python3 edit-table.py\n",
    "Load csv took 1.7913 seconds.\n",
    "36\n",
    "Querying out a trip's entries took 0.2839 seconds.\n",
    "1176247\n",
    "Removing a trip's entries from full df took 0.1998 seconds.\n",
    "1176283\n",
    "Appending trip entires to full df took 0.1195 seconds.\n",
    "1176283\n",
    "Concatenating trip entires to full df took 0.1145 seconds.\n",
    "Storing in .h5 file took 1.779 seconds.\n",
    "Storing in .csv file took 9.3677 seconds.\n",
    "Reading .h5 file took 0.6003 seconds.\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dropping a stop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loaded stop_times dataframe from .h5 file\n",
      "Querying stop_times dataframe for stop 640\n",
      "380 entries found.\n",
      "Query for stop thru full stop_times db took 0.1108 seconds.\n",
      "Dropping the stop from stop_times table\n",
      "1175903 entries remain in full dataframe.\n",
      "Removing a stop from full stop_times db took 0.3179 seconds.\n"
     ]
    }
   ],
   "source": [
    "stop_id = '640'\n",
    "hdf5 = 'GTFSbmtc/test/stop_times.h5'\n",
    "hdFixed = pd.read_hdf(hdf5,'stop_times')\n",
    "print('Loaded stop_times dataframe from .h5 file')\n",
    "\n",
    "print('Querying stop_times dataframe for stop ' + stop_id)\n",
    "start = time.time()\n",
    "stopEntries = hdFixed.query('stop_id == \"' + stop_id + '\"')\n",
    "end = time.time()\n",
    "print(str(len(stopEntries)) + ' entries found.')\n",
    "print(\"Query for stop thru full stop_times db took {} seconds.\".format(round(end-start,4)))\n",
    "\n",
    "print('Dropping the stop from stop_times table')\n",
    "start = time.time()\n",
    "hdFixed.query('stop_id != \"' + stop_id + '\"',inplace=True)\n",
    "end = time.time()\n",
    "print(str(len(hdFixed)) + ' entries remain in full dataframe.')\n",
    "print(\"Removing a stop from full stop_times db took {} seconds.\".format(round(end-start,4)))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Revised Workflow\n",
    "\n",
    "#### Proposal 3\n",
    "- All tables (or csvs in .txt files) get stored in .h5 versions at time of GTFS feed import.\n",
    "- Keep them in separate .h5 files of their own even though we apparently can store multiple tables in same file.. will help to do things quickly.\n",
    "- Blank slate: create empty dataframes with the prescribed columns and store in .h5 files\n",
    "- Reading DB: Load from .h5 file to pd dataframe, query dataframe.\n",
    "- Writing DB: In case of full table over-write, simply write to .h5 with `,mode='w'` so it doesn't append.\n",
    "- Writing DB: In case of partial over-write, do the routine done here: load from .h5 file, remove entries, concat new entries, write to .h5 file."
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}