bcolz.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Leer, procesar y almacenar (bcolz y hdf5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import bcolz\n",
    "from bquery import ctable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'pd' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-1-645f1d31007f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m df = pd.read_csv('trip_fare_1.csv', names=['medallion', 'hack_license', 'vendor_id', 'pickup_datetime', \n\u001b[0m\u001b[1;32m      2\u001b[0m                                            \u001b[0;34m'payment_type'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m'fare_amount'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'surcharge'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'mta_tax'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'tip_amount'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m                                            'tolls_amount', 'total_amount'], header=0)\n",
      "\u001b[0;31mNameError\u001b[0m: name 'pd' is not defined"
     ]
    }
   ],
   "source": [
    "# Puedes descargarlo de https://nyctaxitrips.blob.core.windows.net/data/trip_data_1.csv.zip\n",
    "df = pd.read_csv('trip_fare_1.csv', names=['medallion', 'hack_license', 'vendor_id', 'pickup_datetime', \n",
    "                                           'payment_type','fare_amount', 'surcharge', 'mta_tax', 'tip_amount', \n",
    "                                           'tolls_amount', 'total_amount'], header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.to_hdf('trip_fare_1.h5', 'df')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ctable.fromdataframe(df, rootdir='trip_fare_1.bcolz', mode='w')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "del df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def pandas_test(df):\n",
    "    return df.eval('cos((3+tip_amount)/sqrt(2*fare_amount))')\n",
    "    \n",
    "def pandas_test_2():\n",
    "    df2 = pd.read_hdf('trip_fare_1.h5', 'df')\n",
    "    results = df.eval('cos((3+tip_amount)/sqrt(2*fare_amount))')\n",
    "    del df\n",
    "    return results\n",
    "    \n",
    "def bcolz_test():\n",
    "    ct = ctable(rootdir='trip_fare_1.bcolz', mode='r')\n",
    "    return ct.eval('cos((3+tip_amount)/sqrt(2*fare_amount))', out_flavor=\"numpy\")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ctable((14776615,), [('medallion', 'S32'), ('hack_license', 'S32'), ('vendor_id', 'S3'), ('pickup_datetime', 'S19'), ('payment_type', 'S3'), ('fare_amount', '<f8'), ('surcharge', '<f8'), ('mta_tax', '<f8'), ('tip_amount', '<f8'), ('tolls_amount', '<f8'), ('total_amount', '<f8')])\n",
       "  nbytes: 1.89 GB; cbytes: 1.20 GB; ratio: 1.57\n",
       "  cparams := cparams(clevel=5, shuffle=1, cname='blosclz')\n",
       "  rootdir := 'trip_fare_1.bcolz'\n",
       "[ (b'89D227B655E5C82AECF13C3F540D4CF4', b'BA96DE419E711691B9445D6A6307C170', b'CMT', b'2013-01-01 15:11:48', b'CSH', 6.5, 0.0, 0.5, 0.0, 0.0, 7.0)\n",
       " (b'0BD7C8F5BA12B88E0B67BED28BEA73D8', b'9FD8F69F0804BDB5549F40E9DA1BE472', b'CMT', b'2013-01-06 00:18:35', b'CSH', 6.0, 0.5, 0.5, 0.0, 0.0, 7.0)\n",
       " (b'0BD7C8F5BA12B88E0B67BED28BEA73D8', b'9FD8F69F0804BDB5549F40E9DA1BE472', b'CMT', b'2013-01-05 18:49:41', b'CSH', 5.5, 1.0, 0.5, 0.0, 0.0, 7.0)\n",
       " ...,\n",
       " (b'D83F9AC0E33F6F19869C243BE6AB6FE5', b'85A55B6772275374EF90AC9457DC1F83', b'CMT', b'2013-01-10 13:29:23', b'CSH', 6.0, 0.0, 0.5, 0.0, 0.0, 6.5)\n",
       " (b'04E59442A7DDBCE515E33CD355D866E7', b'7913172189931A1A1632562B10AB53C4', b'CMT', b'2013-01-06 16:30:15', b'CSH', 9.5, 0.0, 0.5, 0.0, 0.0, 10.0)\n",
       " (b'D30BED60331C79E3F7ACD05B325ED42F', b'B5E1D2461A5BCC8819188DACEC17CD69', b'CMT', b'2013-01-05 20:38:46', b'CSH', 5.0, 0.5, 0.5, 0.0, 0.0, 6.0)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ct = ctable(rootdir='trip_fare_1.bcolz', mode='r')\n",
    "ct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.67336137,  0.64785934,  0.61805196, ...,  0.64785934,\n",
       "        0.77236055,  0.58275361])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bcolz_test()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>medallion</th>\n",
       "      <th>hack_license</th>\n",
       "      <th>vendor_id</th>\n",
       "      <th>pickup_datetime</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>surcharge</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>89D227B655E5C82AECF13C3F540D4CF4</td>\n",
       "      <td>BA96DE419E711691B9445D6A6307C170</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-01 15:11:48</td>\n",
       "      <td>CSH</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0BD7C8F5BA12B88E0B67BED28BEA73D8</td>\n",
       "      <td>9FD8F69F0804BDB5549F40E9DA1BE472</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-06 00:18:35</td>\n",
       "      <td>CSH</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0BD7C8F5BA12B88E0B67BED28BEA73D8</td>\n",
       "      <td>9FD8F69F0804BDB5549F40E9DA1BE472</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-05 18:49:41</td>\n",
       "      <td>CSH</td>\n",
       "      <td>5.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DFD2202EE08F7A8DC9A57B02ACB81FE2</td>\n",
       "      <td>51EE87E3205C985EF8431D850C786310</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 23:54:15</td>\n",
       "      <td>CSH</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DFD2202EE08F7A8DC9A57B02ACB81FE2</td>\n",
       "      <td>51EE87E3205C985EF8431D850C786310</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 23:25:03</td>\n",
       "      <td>CSH</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20D9ECB2CA0767CF7A01564DF2844A3E</td>\n",
       "      <td>598CCE5B9C1918568DEE71F43CF26CD2</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 15:27:48</td>\n",
       "      <td>CSH</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>496644932DF3932605C22C7926FF0FE0</td>\n",
       "      <td>513189AD756FF14FE670D10B92FAF04C</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 11:01:15</td>\n",
       "      <td>CSH</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0B57B9633A2FECD3D3B1944AFC7471CF</td>\n",
       "      <td>CCD4367B417ED6634D986F573A552A62</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 12:39:18</td>\n",
       "      <td>CSH</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>39.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2C0E91FF20A856C891483ED63589F982</td>\n",
       "      <td>1DA2F6543A62B8ED934771661A9D2FA0</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 18:15:47</td>\n",
       "      <td>CSH</td>\n",
       "      <td>5.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2D4B95E2FA7B2E85118EC5CA4570FA58</td>\n",
       "      <td>CD2F522EEE1FF5F5A8D8B679E23576B3</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 15:33:28</td>\n",
       "      <td>CSH</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>E12F6AF991172EAC3553144A0AF75A19</td>\n",
       "      <td>06918214E951FA0003D1CC54955C2AB0</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 13:11:52</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>E12F6AF991172EAC3553144A0AF75A19</td>\n",
       "      <td>06918214E951FA0003D1CC54955C2AB0</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 09:50:05</td>\n",
       "      <td>CSH</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>78FFD9CD0CDA541F335EF8B38FB494D6</td>\n",
       "      <td>E949C583ECF62C8F03FDCE1484954A08</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 12:07:08</td>\n",
       "      <td>CSH</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>237F49C3ECC11F5024B254268F054384</td>\n",
       "      <td>93C363DDF8ED9385D65FAD07CE3F5F07</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 07:35:47</td>\n",
       "      <td>CSH</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3349F919AA8AE5DC9C50A3773EA45BD8</td>\n",
       "      <td>7CE849FEF67514F080AF80D990F7EF7F</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 15:42:29</td>\n",
       "      <td>CSH</td>\n",
       "      <td>15.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3349F919AA8AE5DC9C50A3773EA45BD8</td>\n",
       "      <td>7CE849FEF67514F080AF80D990F7EF7F</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 14:27:28</td>\n",
       "      <td>CSH</td>\n",
       "      <td>16.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4C005EEBAA7BF26B84B21586332488A2</td>\n",
       "      <td>351BE7D984BE17DB2FA80A748E816472</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 22:09:59</td>\n",
       "      <td>CSH</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>7D99C30FCE69B1A9DD27E2AEAC9BFA0C</td>\n",
       "      <td>460C3F57DD9CB2265DB75B14CD70224D</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 17:18:16</td>\n",
       "      <td>CSH</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>E6FBF80668FE0611AEA44FD9574A7E32</td>\n",
       "      <td>36773E80775F26CD1158EB5450A61C79</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 06:08:51</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0C5296F3C8B16E702F8F2E06F5106552</td>\n",
       "      <td>D2363240A9295EF570FC6069BC4F4C92</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 22:25:46</td>\n",
       "      <td>CSH</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>D8AADA4E722C87C10E609654612630DD</td>\n",
       "      <td>8F1E4F779A4D67431C3FCFA4E70A87DC</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 23:41:51</td>\n",
       "      <td>CSH</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>57.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>B6FC8357E0D53B0F1FA897D536A20F5C</td>\n",
       "      <td>113A5BA8513934DEE97A342E3535DE96</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 18:05:36</td>\n",
       "      <td>CSH</td>\n",
       "      <td>15.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>E12F6AF991172EAC3553144A0AF75A19</td>\n",
       "      <td>06918214E951FA0003D1CC54955C2AB0</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 13:29:25</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>4C005EEBAA7BF26B84B21586332488A2</td>\n",
       "      <td>351BE7D984BE17DB2FA80A748E816472</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 21:13:02</td>\n",
       "      <td>CSH</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>E9BA494DC1A1F26D186394EFBB8327CF</td>\n",
       "      <td>BBF604B3A2426BFBAAC982AAAA0BF1B5</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-01 18:36:53</td>\n",
       "      <td>CSH</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>E6FBF80668FE0611AEA44FD9574A7E32</td>\n",
       "      <td>36773E80775F26CD1158EB5450A61C79</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 08:17:06</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0C5296F3C8B16E702F8F2E06F5106552</td>\n",
       "      <td>D2363240A9295EF570FC6069BC4F4C92</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 22:39:03</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>A3B17384165197E18CA0A1BB61277EE9</td>\n",
       "      <td>B8396B62883EA332EFD2771A6B031D05</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 06:26:32</td>\n",
       "      <td>CSH</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>8E189DABE265CC03FEF4BFC695B32559</td>\n",
       "      <td>906939B31C0B93768242A58A682412B8</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-01 14:02:01</td>\n",
       "      <td>CSH</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>36.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>312E0CB058D7FC1A6494EDB66D360CD2</td>\n",
       "      <td>7B5156F38990963332B33298C8BAE25E</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-05 11:54:49</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776585</th>\n",
       "      <td>237F49C3ECC11F5024B254268F054384</td>\n",
       "      <td>93C363DDF8ED9385D65FAD07CE3F5F07</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 07:48:00</td>\n",
       "      <td>CSH</td>\n",
       "      <td>11.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776586</th>\n",
       "      <td>237F49C3ECC11F5024B254268F054384</td>\n",
       "      <td>93C363DDF8ED9385D65FAD07CE3F5F07</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 09:08:37</td>\n",
       "      <td>CSH</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776587</th>\n",
       "      <td>4C005EEBAA7BF26B84B21586332488A2</td>\n",
       "      <td>351BE7D984BE17DB2FA80A748E816472</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 23:47:47</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776588</th>\n",
       "      <td>32D306FD3A4D7CEDCFD8D644294DD5B7</td>\n",
       "      <td>05EACD5B806FC3D01A377606D37CF327</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 15:11:04</td>\n",
       "      <td>CSH</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776589</th>\n",
       "      <td>32D306FD3A4D7CEDCFD8D644294DD5B7</td>\n",
       "      <td>05EACD5B806FC3D01A377606D37CF327</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 08:25:24</td>\n",
       "      <td>CSH</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776590</th>\n",
       "      <td>50296A056AF5D221536DC98ABB1E3274</td>\n",
       "      <td>4F3E6D0E69CABD8954F8A126D54C5C20</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 02:37:37</td>\n",
       "      <td>CSH</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776591</th>\n",
       "      <td>C6CE309A20A523E64FDCDECF150A2EC1</td>\n",
       "      <td>3DC3C9552D190C5D26B0358C23E449A6</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 08:02:56</td>\n",
       "      <td>CSH</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776592</th>\n",
       "      <td>C6CE309A20A523E64FDCDECF150A2EC1</td>\n",
       "      <td>3DC3C9552D190C5D26B0358C23E449A6</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 15:00:16</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776593</th>\n",
       "      <td>5E162F2D7F569949769648C56A23C525</td>\n",
       "      <td>6F7932F2B112DAABF1F2A385FE2AB819</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 23:16:34</td>\n",
       "      <td>CSH</td>\n",
       "      <td>38.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>44.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776594</th>\n",
       "      <td>1836E1591EE79F810104064B945747AB</td>\n",
       "      <td>18EDD3FC80A78DC262E2442FEEA55F01</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 03:05:09</td>\n",
       "      <td>CSH</td>\n",
       "      <td>15.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776595</th>\n",
       "      <td>1836E1591EE79F810104064B945747AB</td>\n",
       "      <td>18EDD3FC80A78DC262E2442FEEA55F01</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 17:54:25</td>\n",
       "      <td>CSH</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776596</th>\n",
       "      <td>A5A69FAD94C3DC7854F9DDEA994B6380</td>\n",
       "      <td>178BA04DD1A4E3A184C86AA3F110A742</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 08:42:47</td>\n",
       "      <td>CSH</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776597</th>\n",
       "      <td>5E162F2D7F569949769648C56A23C525</td>\n",
       "      <td>6F7932F2B112DAABF1F2A385FE2AB819</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 20:01:08</td>\n",
       "      <td>CSH</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776598</th>\n",
       "      <td>B36D470B76BD8F6D720A83D08891754E</td>\n",
       "      <td>A86011E8B4D0FCC19283230950DFCB3E</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 22:01:57</td>\n",
       "      <td>CSH</td>\n",
       "      <td>19.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776599</th>\n",
       "      <td>47CFD616C057758044C57D1B4BEFE759</td>\n",
       "      <td>A6DCBA545B1E1A2DC24BFEE1C0A54029</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 19:48:22</td>\n",
       "      <td>CSH</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776600</th>\n",
       "      <td>0CC08DE5645A7137059499EA11886805</td>\n",
       "      <td>4F2627F9FD34A9B9EFCCFC5147048345</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 00:26:18</td>\n",
       "      <td>CSH</td>\n",
       "      <td>8.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776601</th>\n",
       "      <td>0CC08DE5645A7137059499EA11886805</td>\n",
       "      <td>4F2627F9FD34A9B9EFCCFC5147048345</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 19:57:17</td>\n",
       "      <td>CSH</td>\n",
       "      <td>13.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776602</th>\n",
       "      <td>B7A16B1C396F4A5113257DDEB043D953</td>\n",
       "      <td>3DC4ACD88D75EF04FBC9CCDFEEF7C71E</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 18:50:36</td>\n",
       "      <td>CSH</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776603</th>\n",
       "      <td>E71CDEFCB364DA0D385629612EE30642</td>\n",
       "      <td>C7259B75B62CB850082BDEB4149DC075</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 21:46:45</td>\n",
       "      <td>CSH</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776604</th>\n",
       "      <td>E71CDEFCB364DA0D385629612EE30642</td>\n",
       "      <td>C7259B75B62CB850082BDEB4149DC075</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 21:53:35</td>\n",
       "      <td>CSH</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776605</th>\n",
       "      <td>A8262FA0AFCB6C7229F6888EAFBDE076</td>\n",
       "      <td>1BDF89260FEF1AE6FDDE839A0278D31D</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 07:29:06</td>\n",
       "      <td>CSH</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>57.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776606</th>\n",
       "      <td>A8262FA0AFCB6C7229F6888EAFBDE076</td>\n",
       "      <td>1BDF89260FEF1AE6FDDE839A0278D31D</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-07 14:30:23</td>\n",
       "      <td>CSH</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776607</th>\n",
       "      <td>F33EF464441839C6F0DABAABBC93B45D</td>\n",
       "      <td>313F66DD09C308EADA3B307F6B8CF7A9</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 10:56:47</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776608</th>\n",
       "      <td>56CE01E7DBE0E6449FA1758F082D8884</td>\n",
       "      <td>4C6FE2FCFED26629D515D291EC1516A0</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 14:50:01</td>\n",
       "      <td>CSH</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776609</th>\n",
       "      <td>32201027CDC62D654DC3AD9747A07C96</td>\n",
       "      <td>B8DDB9F8143017E22104050B26C2A65D</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-05 08:58:18</td>\n",
       "      <td>CSH</td>\n",
       "      <td>10.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776610</th>\n",
       "      <td>B33E71CD9E8FE1BE3B70FEB6E807DD15</td>\n",
       "      <td>BAF57796E45D921BB23217E17A372FF6</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-06 04:58:23</td>\n",
       "      <td>CSH</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776611</th>\n",
       "      <td>ED160B76D5349C8AC1ECF22CD4B8D538</td>\n",
       "      <td>3B93F6DA5DEBDE9560993FA624C4FF76</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-08 14:42:04</td>\n",
       "      <td>CSH</td>\n",
       "      <td>7.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776612</th>\n",
       "      <td>D83F9AC0E33F6F19869C243BE6AB6FE5</td>\n",
       "      <td>85A55B6772275374EF90AC9457DC1F83</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-10 13:29:23</td>\n",
       "      <td>CSH</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776613</th>\n",
       "      <td>04E59442A7DDBCE515E33CD355D866E7</td>\n",
       "      <td>7913172189931A1A1632562B10AB53C4</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-06 16:30:15</td>\n",
       "      <td>CSH</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14776614</th>\n",
       "      <td>D30BED60331C79E3F7ACD05B325ED42F</td>\n",
       "      <td>B5E1D2461A5BCC8819188DACEC17CD69</td>\n",
       "      <td>CMT</td>\n",
       "      <td>2013-01-05 20:38:46</td>\n",
       "      <td>CSH</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14776615 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 medallion                      hack_license  \\\n",
       "0         89D227B655E5C82AECF13C3F540D4CF4  BA96DE419E711691B9445D6A6307C170   \n",
       "1         0BD7C8F5BA12B88E0B67BED28BEA73D8  9FD8F69F0804BDB5549F40E9DA1BE472   \n",
       "2         0BD7C8F5BA12B88E0B67BED28BEA73D8  9FD8F69F0804BDB5549F40E9DA1BE472   \n",
       "3         DFD2202EE08F7A8DC9A57B02ACB81FE2  51EE87E3205C985EF8431D850C786310   \n",
       "4         DFD2202EE08F7A8DC9A57B02ACB81FE2  51EE87E3205C985EF8431D850C786310   \n",
       "5         20D9ECB2CA0767CF7A01564DF2844A3E  598CCE5B9C1918568DEE71F43CF26CD2   \n",
       "6         496644932DF3932605C22C7926FF0FE0  513189AD756FF14FE670D10B92FAF04C   \n",
       "7         0B57B9633A2FECD3D3B1944AFC7471CF  CCD4367B417ED6634D986F573A552A62   \n",
       "8         2C0E91FF20A856C891483ED63589F982  1DA2F6543A62B8ED934771661A9D2FA0   \n",
       "9         2D4B95E2FA7B2E85118EC5CA4570FA58  CD2F522EEE1FF5F5A8D8B679E23576B3   \n",
       "10        E12F6AF991172EAC3553144A0AF75A19  06918214E951FA0003D1CC54955C2AB0   \n",
       "11        E12F6AF991172EAC3553144A0AF75A19  06918214E951FA0003D1CC54955C2AB0   \n",
       "12        78FFD9CD0CDA541F335EF8B38FB494D6  E949C583ECF62C8F03FDCE1484954A08   \n",
       "13        237F49C3ECC11F5024B254268F054384  93C363DDF8ED9385D65FAD07CE3F5F07   \n",
       "14        3349F919AA8AE5DC9C50A3773EA45BD8  7CE849FEF67514F080AF80D990F7EF7F   \n",
       "15        3349F919AA8AE5DC9C50A3773EA45BD8  7CE849FEF67514F080AF80D990F7EF7F   \n",
       "16        4C005EEBAA7BF26B84B21586332488A2  351BE7D984BE17DB2FA80A748E816472   \n",
       "17        7D99C30FCE69B1A9DD27E2AEAC9BFA0C  460C3F57DD9CB2265DB75B14CD70224D   \n",
       "18        E6FBF80668FE0611AEA44FD9574A7E32  36773E80775F26CD1158EB5450A61C79   \n",
       "19        0C5296F3C8B16E702F8F2E06F5106552  D2363240A9295EF570FC6069BC4F4C92   \n",
       "20        D8AADA4E722C87C10E609654612630DD  8F1E4F779A4D67431C3FCFA4E70A87DC   \n",
       "21        B6FC8357E0D53B0F1FA897D536A20F5C  113A5BA8513934DEE97A342E3535DE96   \n",
       "22        E12F6AF991172EAC3553144A0AF75A19  06918214E951FA0003D1CC54955C2AB0   \n",
       "23        4C005EEBAA7BF26B84B21586332488A2  351BE7D984BE17DB2FA80A748E816472   \n",
       "24        E9BA494DC1A1F26D186394EFBB8327CF  BBF604B3A2426BFBAAC982AAAA0BF1B5   \n",
       "25        E6FBF80668FE0611AEA44FD9574A7E32  36773E80775F26CD1158EB5450A61C79   \n",
       "26        0C5296F3C8B16E702F8F2E06F5106552  D2363240A9295EF570FC6069BC4F4C92   \n",
       "27        A3B17384165197E18CA0A1BB61277EE9  B8396B62883EA332EFD2771A6B031D05   \n",
       "28        8E189DABE265CC03FEF4BFC695B32559  906939B31C0B93768242A58A682412B8   \n",
       "29        312E0CB058D7FC1A6494EDB66D360CD2  7B5156F38990963332B33298C8BAE25E   \n",
       "...                                    ...                               ...   \n",
       "14776585  237F49C3ECC11F5024B254268F054384  93C363DDF8ED9385D65FAD07CE3F5F07   \n",
       "14776586  237F49C3ECC11F5024B254268F054384  93C363DDF8ED9385D65FAD07CE3F5F07   \n",
       "14776587  4C005EEBAA7BF26B84B21586332488A2  351BE7D984BE17DB2FA80A748E816472   \n",
       "14776588  32D306FD3A4D7CEDCFD8D644294DD5B7  05EACD5B806FC3D01A377606D37CF327   \n",
       "14776589  32D306FD3A4D7CEDCFD8D644294DD5B7  05EACD5B806FC3D01A377606D37CF327   \n",
       "14776590  50296A056AF5D221536DC98ABB1E3274  4F3E6D0E69CABD8954F8A126D54C5C20   \n",
       "14776591  C6CE309A20A523E64FDCDECF150A2EC1  3DC3C9552D190C5D26B0358C23E449A6   \n",
       "14776592  C6CE309A20A523E64FDCDECF150A2EC1  3DC3C9552D190C5D26B0358C23E449A6   \n",
       "14776593  5E162F2D7F569949769648C56A23C525  6F7932F2B112DAABF1F2A385FE2AB819   \n",
       "14776594  1836E1591EE79F810104064B945747AB  18EDD3FC80A78DC262E2442FEEA55F01   \n",
       "14776595  1836E1591EE79F810104064B945747AB  18EDD3FC80A78DC262E2442FEEA55F01   \n",
       "14776596  A5A69FAD94C3DC7854F9DDEA994B6380  178BA04DD1A4E3A184C86AA3F110A742   \n",
       "14776597  5E162F2D7F569949769648C56A23C525  6F7932F2B112DAABF1F2A385FE2AB819   \n",
       "14776598  B36D470B76BD8F6D720A83D08891754E  A86011E8B4D0FCC19283230950DFCB3E   \n",
       "14776599  47CFD616C057758044C57D1B4BEFE759  A6DCBA545B1E1A2DC24BFEE1C0A54029   \n",
       "14776600  0CC08DE5645A7137059499EA11886805  4F2627F9FD34A9B9EFCCFC5147048345   \n",
       "14776601  0CC08DE5645A7137059499EA11886805  4F2627F9FD34A9B9EFCCFC5147048345   \n",
       "14776602  B7A16B1C396F4A5113257DDEB043D953  3DC4ACD88D75EF04FBC9CCDFEEF7C71E   \n",
       "14776603  E71CDEFCB364DA0D385629612EE30642  C7259B75B62CB850082BDEB4149DC075   \n",
       "14776604  E71CDEFCB364DA0D385629612EE30642  C7259B75B62CB850082BDEB4149DC075   \n",
       "14776605  A8262FA0AFCB6C7229F6888EAFBDE076  1BDF89260FEF1AE6FDDE839A0278D31D   \n",
       "14776606  A8262FA0AFCB6C7229F6888EAFBDE076  1BDF89260FEF1AE6FDDE839A0278D31D   \n",
       "14776607  F33EF464441839C6F0DABAABBC93B45D  313F66DD09C308EADA3B307F6B8CF7A9   \n",
       "14776608  56CE01E7DBE0E6449FA1758F082D8884  4C6FE2FCFED26629D515D291EC1516A0   \n",
       "14776609  32201027CDC62D654DC3AD9747A07C96  B8DDB9F8143017E22104050B26C2A65D   \n",
       "14776610  B33E71CD9E8FE1BE3B70FEB6E807DD15  BAF57796E45D921BB23217E17A372FF6   \n",
       "14776611  ED160B76D5349C8AC1ECF22CD4B8D538  3B93F6DA5DEBDE9560993FA624C4FF76   \n",
       "14776612  D83F9AC0E33F6F19869C243BE6AB6FE5  85A55B6772275374EF90AC9457DC1F83   \n",
       "14776613  04E59442A7DDBCE515E33CD355D866E7  7913172189931A1A1632562B10AB53C4   \n",
       "14776614  D30BED60331C79E3F7ACD05B325ED42F  B5E1D2461A5BCC8819188DACEC17CD69   \n",
       "\n",
       "         vendor_id      pickup_datetime payment_type  fare_amount  surcharge  \\\n",
       "0              CMT  2013-01-01 15:11:48          CSH          6.5        0.0   \n",
       "1              CMT  2013-01-06 00:18:35          CSH          6.0        0.5   \n",
       "2              CMT  2013-01-05 18:49:41          CSH          5.5        1.0   \n",
       "3              CMT  2013-01-07 23:54:15          CSH          5.0        0.5   \n",
       "4              CMT  2013-01-07 23:25:03          CSH          9.5        0.5   \n",
       "5              CMT  2013-01-07 15:27:48          CSH          9.5        0.0   \n",
       "6              CMT  2013-01-08 11:01:15          CSH          6.0        0.0   \n",
       "7              CMT  2013-01-07 12:39:18          CSH         34.0        0.0   \n",
       "8              CMT  2013-01-07 18:15:47          CSH          5.5        1.0   \n",
       "9              CMT  2013-01-07 15:33:28          CSH         13.0        0.0   \n",
       "10             CMT  2013-01-08 13:11:52          CSH          7.5        0.0   \n",
       "11             CMT  2013-01-08 09:50:05          CSH          9.0        0.0   \n",
       "12             CMT  2013-01-10 12:07:08          CSH          9.5        0.0   \n",
       "13             CMT  2013-01-07 07:35:47          CSH         10.0        0.0   \n",
       "14             CMT  2013-01-10 15:42:29          CSH         15.5        0.0   \n",
       "15             CMT  2013-01-10 14:27:28          CSH         16.5        0.0   \n",
       "16             CMT  2013-01-07 22:09:59          CSH          9.0        0.5   \n",
       "17             CMT  2013-01-07 17:18:16          CSH          4.5        1.0   \n",
       "18             CMT  2013-01-07 06:08:51          CSH          7.0        0.0   \n",
       "19             CMT  2013-01-07 22:25:46          CSH         10.5        0.5   \n",
       "20             CMT  2013-01-10 23:41:51          CSH         52.0        0.0   \n",
       "21             CMT  2013-01-07 18:05:36          CSH         15.5        1.0   \n",
       "22             CMT  2013-01-08 13:29:25          CSH          7.5        0.0   \n",
       "23             CMT  2013-01-07 21:13:02          CSH          8.0        0.5   \n",
       "24             CMT  2013-01-01 18:36:53          CSH          4.0        0.0   \n",
       "25             CMT  2013-01-07 08:17:06          CSH          7.0        0.0   \n",
       "26             CMT  2013-01-07 22:39:03          CSH          7.0        0.5   \n",
       "27             CMT  2013-01-07 06:26:32          CSH          4.0        0.5   \n",
       "28             CMT  2013-01-01 14:02:01          CSH         31.0        0.0   \n",
       "29             CMT  2013-01-05 11:54:49          CSH          7.5        0.0   \n",
       "...            ...                  ...          ...          ...        ...   \n",
       "14776585       CMT  2013-01-07 07:48:00          CSH         11.5        0.0   \n",
       "14776586       CMT  2013-01-07 09:08:37          CSH          5.0        0.0   \n",
       "14776587       CMT  2013-01-07 23:47:47          CSH          7.5        0.5   \n",
       "14776588       CMT  2013-01-08 15:11:04          CSH         11.0        0.0   \n",
       "14776589       CMT  2013-01-08 08:25:24          CSH          8.0        0.0   \n",
       "14776590       CMT  2013-01-08 02:37:37          CSH         26.0        0.5   \n",
       "14776591       CMT  2013-01-10 08:02:56          CSH         11.0        0.0   \n",
       "14776592       CMT  2013-01-10 15:00:16          CSH          7.0        0.0   \n",
       "14776593       CMT  2013-01-07 23:16:34          CSH         38.5        0.5   \n",
       "14776594       CMT  2013-01-08 03:05:09          CSH         15.5        0.5   \n",
       "14776595       CMT  2013-01-07 17:54:25          CSH         14.0        1.0   \n",
       "14776596       CMT  2013-01-10 08:42:47          CSH          8.5        0.0   \n",
       "14776597       CMT  2013-01-07 20:01:08          CSH          8.5        0.5   \n",
       "14776598       CMT  2013-01-07 22:01:57          CSH         19.5        0.5   \n",
       "14776599       CMT  2013-01-07 19:48:22          CSH          4.5        1.0   \n",
       "14776600       CMT  2013-01-08 00:26:18          CSH          8.5        0.5   \n",
       "14776601       CMT  2013-01-07 19:57:17          CSH         13.5        1.0   \n",
       "14776602       CMT  2013-01-07 18:50:36          CSH         10.0        1.0   \n",
       "14776603       CMT  2013-01-07 21:46:45          CSH          5.5        0.5   \n",
       "14776604       CMT  2013-01-07 21:53:35          CSH          4.0        0.5   \n",
       "14776605       CMT  2013-01-07 07:29:06          CSH         52.0        0.0   \n",
       "14776606       CMT  2013-01-07 14:30:23          CSH          9.5        0.0   \n",
       "14776607       CMT  2013-01-10 10:56:47          CSH          7.5        0.0   \n",
       "14776608       CMT  2013-01-10 14:50:01          CSH         20.0        0.0   \n",
       "14776609       CMT  2013-01-05 08:58:18          CSH         10.5        0.0   \n",
       "14776610       CMT  2013-01-06 04:58:23          CSH         13.0        0.5   \n",
       "14776611       CMT  2013-01-08 14:42:04          CSH          7.5        0.0   \n",
       "14776612       CMT  2013-01-10 13:29:23          CSH          6.0        0.0   \n",
       "14776613       CMT  2013-01-06 16:30:15          CSH          9.5        0.0   \n",
       "14776614       CMT  2013-01-05 20:38:46          CSH          5.0        0.5   \n",
       "\n",
       "          mta_tax  tip_amount  tolls_amount  total_amount  \n",
       "0             0.5         0.0           0.0           7.0  \n",
       "1             0.5         0.0           0.0           7.0  \n",
       "2             0.5         0.0           0.0           7.0  \n",
       "3             0.5         0.0           0.0           6.0  \n",
       "4             0.5         0.0           0.0          10.5  \n",
       "5             0.5         0.0           0.0          10.0  \n",
       "6             0.5         0.0           0.0           6.5  \n",
       "7             0.5         0.0           4.8          39.3  \n",
       "8             0.5         0.0           0.0           7.0  \n",
       "9             0.5         0.0           0.0          13.5  \n",
       "10            0.5         0.0           0.0           8.0  \n",
       "11            0.5         0.0           0.0           9.5  \n",
       "12            0.5         0.0           0.0          10.0  \n",
       "13            0.5         0.0           0.0          10.5  \n",
       "14            0.5         0.0           0.0          16.0  \n",
       "15            0.5         0.0           0.0          17.0  \n",
       "16            0.5         0.0           0.0          10.0  \n",
       "17            0.5         0.0           0.0           6.0  \n",
       "18            0.5         0.0           0.0           7.5  \n",
       "19            0.5         0.0           0.0          11.5  \n",
       "20            0.5         0.0           4.8          57.3  \n",
       "21            0.5         0.0           0.0          17.0  \n",
       "22            0.5         0.0           0.0           8.0  \n",
       "23            0.5         0.0           0.0           9.0  \n",
       "24            0.5         0.0           0.0           4.5  \n",
       "25            0.5         0.0           0.0           7.5  \n",
       "26            0.5         0.0           0.0           8.0  \n",
       "27            0.5         0.0           0.0           5.0  \n",
       "28            0.5         0.0           4.8          36.3  \n",
       "29            0.5         0.0           0.0           8.0  \n",
       "...           ...         ...           ...           ...  \n",
       "14776585      0.5         0.0           0.0          12.0  \n",
       "14776586      0.5         0.0           0.0           5.5  \n",
       "14776587      0.5         0.0           0.0           8.5  \n",
       "14776588      0.5         0.0           0.0          11.5  \n",
       "14776589      0.5         0.0           0.0           8.5  \n",
       "14776590      0.5         0.0           0.0          27.0  \n",
       "14776591      0.5         0.0           0.0          11.5  \n",
       "14776592      0.5         0.0           0.0           7.5  \n",
       "14776593      0.5         0.0           4.8          44.3  \n",
       "14776594      0.5         0.0           0.0          16.5  \n",
       "14776595      0.5         0.0           0.0          15.5  \n",
       "14776596      0.5         0.0           0.0           9.0  \n",
       "14776597      0.5         0.0           0.0           9.5  \n",
       "14776598      0.5         0.0           0.0          20.5  \n",
       "14776599      0.5         0.0           0.0           6.0  \n",
       "14776600      0.5         0.0           0.0           9.5  \n",
       "14776601      0.5         0.0           0.0          15.0  \n",
       "14776602      0.5         0.0           0.0          11.5  \n",
       "14776603      0.5         0.0           0.0           6.5  \n",
       "14776604      0.5         0.0           0.0           5.0  \n",
       "14776605      0.5         0.0           4.8          57.3  \n",
       "14776606      0.5         0.0           0.0          10.0  \n",
       "14776607      0.5         0.0           0.0           8.0  \n",
       "14776608      0.5         0.0           0.0          20.5  \n",
       "14776609      0.5         0.0           0.0          11.0  \n",
       "14776610      0.5         0.0           0.0          14.0  \n",
       "14776611      0.5         0.0           0.0           8.0  \n",
       "14776612      0.5         0.0           0.0           6.5  \n",
       "14776613      0.5         0.0           0.0          10.0  \n",
       "14776614      0.5         0.0           0.0           6.0  \n",
       "\n",
       "[14776615 rows x 11 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_hdf('trip_fare_1.h5', 'df')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 loop, best of 3: 331 ms per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit pandas_test(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 loop, best of 3: 237 ms per loop\n"
     ]
    }
   ],
   "source": [
    "%timeit bcolz_test()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%time pandas_test_2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ctable((5,), [('payment_type', 'S3'), ('total_amount_sum', '<f8')])\n",
       "  nbytes: 55; cbytes: 32.00 KB; ratio: 0.00\n",
       "  cparams := cparams(clevel=5, shuffle=1, cname='blosclz')\n",
       "[(b'CSH', 81165508.6397333) (b'DIS', 148355.5500000001)\n",
       " (b'NOC', 384536.9599999939) (b'CRD', 124347719.71988983)\n",
       " (b'UNK', 131766.54000000018)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ct.groupby(['payment_type'], [['total_amount', 'sum', 'total_amount_sum']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 777 ms, sys: 70.6 ms, total: 847 ms\n",
      "Wall time: 851 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "payment_type\n",
       "CRD    1.243477e+08\n",
       "CSH    8.116551e+07\n",
       "DIS    1.483556e+05\n",
       "NOC    3.845370e+05\n",
       "UNK    1.317665e+05\n",
       "Name: total_amount, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df.groupby(['payment_type'])['total_amount'].sum()"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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": 1
}