opendatascicon_boston_20150531.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Open Data Science Conference 2015 (Boston)\n",
    "\n",
    "# The NYC Taxi Trip Dataset (2013)\n",
    "### **173 million records**, i.e. taxi trips, 28 GB of uncompressed Data\n",
    "<img src=\"nyc_cab.JPG?raw=true\" align=\"left\">\n",
    "\n",
    "http://de.wikipedia.org/wiki/New_York_City_Taxi_Cabs#/media/File:USACab.JPG"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A first look at the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# TODO: adjust directory\n",
    "data_dir = '/Users/Felix/taxi_dataset/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "head = pd.read_csv(data_dir + 'trip_data_1.csv', nrows=20)\n",
    "head"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## qgrid (by Quantopian)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import qgrid\n",
    "qgrid.nbinstall()\n",
    "qgrid.show_grid(head)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's have a second look...this time in Excel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## By default, `pandas` uses\n",
    "* ### `XlsxWriter` to write `.xlsx`\n",
    "* ### `openpyxl` to write `.xlsm`\n",
    "* ### `xlwt` to write `.xls`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "head.to_excel('pandas_out.xlsx', 'Sheet1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!open pandas_out.xlsx  # on Windows: !start pandas_out.xlsx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Manipulating existing Excel sheets is ... limited\n",
    "\n",
    "* ### Openpyxl can do it, but not everything is supported (e.g. charts are not)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "writer = pd.ExcelWriter('pandas_out.xlsx', engine='openpyxl')\n",
    "writer.book = load_workbook('pandas_out.xlsx')\n",
    "head.to_excel(writer, 'Sheet2')\n",
    "\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!open pandas_out.xlsx  # on Windows: !start pandas_out.xlsx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `xlwings`: interacts with an open/unsaved Workbook\n",
    "* ### Windows: by wrapping `pywin32` (COM interface)\n",
    "* ### Mac: by wrapping `appscript` (AppleScript)\n",
    "* ### In turn, xlwings needs an installation of Microsoft Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from xlwings import Application, Workbook, Range, Sheet, Chart, ChartType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "wb = Workbook()\n",
    "Range(\"A1\").value = head\n",
    "Sheet(1).autofit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Sheet.add('Sheet2')\n",
    "Range('Sheet2', 'B2').value = head\n",
    "Sheet(2).autofit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2d arrays: nested lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# A1 Notation\n",
    "Sheet(1).activate()\n",
    "Range('Sheet1', 'H1:I2').value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# The same with Index notation (Excel-1-based)\n",
    "Range(1, (1,8),(2,9)).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Get a contiguous Range of cells (as in: \"Ctrl-Shift-Down-and-Right\")\n",
    "out = Range(1, 'M2').table.value\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Assign to top-left corner\n",
    "Range(1, 'B25').value = out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `table` returns a `Range` object, so we can use any `Range` attribute/method on it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Range('B25').table.clear_contents()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `NumPy` Arrays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Range(1, 'M2', asarray=True).table.value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `pandas` DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data = Range('B1').table.value\n",
    "df = pd.DataFrame(data[1:], columns=data[0])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's do some `pandas` magic on the Taxi dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `pickup_datetime` and `passenger_count` have been stored in an `HDF5` file (see end of Notebook)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# No attempts have been made to optimize this...\n",
    "store = pd.HDFStore(data_dir + 'test.h5')\n",
    "df = store['df']\n",
    "store.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's create a \"behavior\" table: total passengers per hour and weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = df.resample('1H', how={'passenger_count': np.sum})\n",
    "grouped = df.groupby([df.index.time, df.index.weekday])\n",
    "behavior = grouped['passenger_count'].aggregate(np.sum).unstack()\n",
    "behavior.columns = ['MO', 'TU', 'WE', 'TH', 'FR', 'SA', 'SU']\n",
    "behavior"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now let's use Excel to create a heatmap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "wb_heatmap = Workbook()\n",
    "Range('A1').value = behavior\n",
    "Range('A:A').number_format = 'HH:MM'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's add a Chart, too"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "chart = Chart.add(source_data=Range('A1').table,\n",
    "                  chart_type=ChartType.xlLine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulate attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "chart.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "chart.name = 'taxi'\n",
    "chart.name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# One more thing: PDF Reporting with ReportLab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from reportlab.platypus import SimpleDocTemplate, Table\n",
    "\n",
    "# reportlab initialization with container for Flowables\n",
    "doc = SimpleDocTemplate(\"report_basics.pdf\")\n",
    "elements = []\n",
    "\n",
    "# Create reportlab table from Excel data\n",
    "data = Range('B1').table.value\n",
    "table = Table(data)\n",
    "\n",
    "# Compose content and write PDF document\n",
    "elements.append(table)\n",
    "doc.build(elements)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!open report_basics.pdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to get the data\n",
    "`$ wget https://nyctaxitrips.blob.core.windows.net/data/trip_data_{1,2,3,4,5,6,7,8,9,10,11,12}.csv.zip`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save `passenger_count` into an `HDF5` file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Again: not optimized and you'll need a bit of memory to process this...\n",
    "parts = []\n",
    "for i in range(1,13):\n",
    "    part = pd.read_csv(data_dir + 'trip_data_{0}.csv'.format(i),\n",
    "                       parse_dates=True, index_col='pickup_datetime', \n",
    "                       usecols=[5,7], skipinitialspace=True)\n",
    "    part.sort_index(inplace=True)\n",
    "    parts.append(part)\n",
    "    \n",
    "# Make one big DataFrame and save to HDF5\n",
    "df = pd.concat(parts)\n",
    "store = pd.HDFStore(data_dir + 'passenger_count.h5')\n",
    "store['df'] = df\n",
    "store.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# See also:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "http://chriswhong.com/open-data/foil_nyc_taxi/  \n",
    "http://www.theguardian.com/technology/2014/jun/27/new-york-taxi-details-anonymised-data-researchers-warn  \n",
    "http://nyctaxi.herokuapp.com/  \n",
    "\n",
    "See these two Notebooks by Continuum Analytics about how to get the data and how to work with Blaze/bcolz on it:\n",
    "http://nbviewer.ipython.org/github/ContinuumIO/blaze/blob/gh-pages/notebooks/timings-csv.ipynb\n",
    "http://nbviewer.ipython.org/github/ContinuumIO/blaze/blob/gh-pages/notebooks/timings-bcolz.ipynb"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}