raw csv to aggregates.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Paris live traffic information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !ls -lh traffic\n",
    "# !rm data/aggregates.pd.h5 data/no_commas.h5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm_notebook as tqdm\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load map from counter id to geo location"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we prepare a dictionary mapping from the counter id `id_arc_trafic` to its `lat,lon` position.\n",
    "Since the db is tiny, we load everything in memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>id_arc</th>\n",
       "      <th>id_arc_tra</th>\n",
       "      <th>SHAPE.LEN</th>\n",
       "      <th>geo_shape</th>\n",
       "      <th>geo_point_2d</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1009</td>\n",
       "      <td>1106.0</td>\n",
       "      <td>4301.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{\"type\": \"LineString\", \"coordinates\": [[2.2879...</td>\n",
       "      <td>[48.8804688997, 2.28668204502]</td>\n",
       "      <td>48.880469</td>\n",
       "      <td>2.286682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1013</td>\n",
       "      <td>1110.0</td>\n",
       "      <td>5868.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{\"type\": \"LineString\", \"coordinates\": [[2.4033...</td>\n",
       "      <td>[48.8362023805, 2.4044924136]</td>\n",
       "      <td>48.836202</td>\n",
       "      <td>2.404492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1016</td>\n",
       "      <td>1114.0</td>\n",
       "      <td>242.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{\"type\": \"LineString\", \"coordinates\": [[2.3237...</td>\n",
       "      <td>[48.8688115513, 2.32343707534]</td>\n",
       "      <td>48.868812</td>\n",
       "      <td>2.323437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1027</td>\n",
       "      <td>1125.0</td>\n",
       "      <td>1368.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{\"type\": \"LineString\", \"coordinates\": [[2.3668...</td>\n",
       "      <td>[48.8639194501, 2.3663424412]</td>\n",
       "      <td>48.863919</td>\n",
       "      <td>2.366342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1028</td>\n",
       "      <td>1126.0</td>\n",
       "      <td>1366.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{\"type\": \"LineString\", \"coordinates\": [[2.3658...</td>\n",
       "      <td>[48.8649639807, 2.36570360277]</td>\n",
       "      <td>48.864964</td>\n",
       "      <td>2.365704</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID  id_arc  id_arc_tra  SHAPE.LEN  \\\n",
       "0      1009  1106.0      4301.0        0.0   \n",
       "1      1013  1110.0      5868.0        0.0   \n",
       "2      1016  1114.0       242.0        0.0   \n",
       "3      1027  1125.0      1368.0        0.0   \n",
       "4      1028  1126.0      1366.0        0.0   \n",
       "\n",
       "                                           geo_shape  \\\n",
       "0  {\"type\": \"LineString\", \"coordinates\": [[2.2879...   \n",
       "1  {\"type\": \"LineString\", \"coordinates\": [[2.4033...   \n",
       "2  {\"type\": \"LineString\", \"coordinates\": [[2.3237...   \n",
       "3  {\"type\": \"LineString\", \"coordinates\": [[2.3668...   \n",
       "4  {\"type\": \"LineString\", \"coordinates\": [[2.3658...   \n",
       "\n",
       "                     geo_point_2d        lat       lon  \n",
       "0  [48.8804688997, 2.28668204502]  48.880469  2.286682  \n",
       "1   [48.8362023805, 2.4044924136]  48.836202  2.404492  \n",
       "2  [48.8688115513, 2.32343707534]  48.868812  2.323437  \n",
       "3   [48.8639194501, 2.3663424412]  48.863919  2.366342  \n",
       "4  [48.8649639807, 2.36570360277]  48.864964  2.365704  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from string import atof\n",
    "def c_geo_point_2d(x):\n",
    "    out = []\n",
    "    for i in x.split(','):\n",
    "        try:\n",
    "            out.append(atof(i))\n",
    "        except ValueError,msg:\n",
    "            return [np.nan,np.nan]\n",
    "            raise(ValueError(' x='+str(x)))\n",
    "    return out\n",
    "converters = {'geo_point_2d':c_geo_point_2d }\n",
    "\n",
    "positions = pd.read_csv('data/referentiel-comptages-routiers.csv',delimiter=';',converters=converters)\n",
    "positions['lat']=positions['geo_point_2d'].apply(lambda x:x[0])\n",
    "positions['lon']=positions['geo_point_2d'].apply(lambda x:x[1])\n",
    "\n",
    "from collections import defaultdict\n",
    "posdict = defaultdict(lambda :{'lat':0,'lon':0})\n",
    "for j,i in positions[['id_arc_tra','lat','lon']].iterrows():\n",
    "    id_arc_tra = float(i.id_arc_tra)\n",
    "    lat = i.lat\n",
    "    lon = i.lon\n",
    "    posdict[id_arc_tra]={'lat':lat,'lon':lon}\n",
    "positions.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'OBJECTID', u'id_arc', u'id_arc_tra', u'SHAPE.LEN', u'geo_shape',\n",
       "       u'geo_point_2d', u'lat', u'lon'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "positions.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 \n",
    "!rm data/referentiel-comptages-routiers.sqlite\n",
    "with sqlite3.connect('data/referentiel-comptages-routiers.sqlite') as conn:\n",
    "    positions[['id_arc_tra','lat','lon']].to_sql('where',conn,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prepare an `sql` db of traffic records\n",
    "In contrast to an `hdf` format, an `sqlite` database offers optimized aggregation \n",
    "essentially for free.  Here we copy the original `csv` file to the db operating on chunks of \n",
    "5e4 items using the `chunk_size` paramers of pandas' `read_csv`. \n",
    "On the fly, we map from counter id `id_arc_trafic` to its gps position and precompute some columns. In particular the `BDay` columns, meaning `business day` is true when the day is mon->fri, false otherwise, holidays are not considered.\n",
    "The automatic type detection algorithms fails for 3 columns leading to a `ValueError` when sending the rows to the `db`.  The type of these 3 lines is specified to be 16-bit int or floats, which is enough to store what we need."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Machine parameters for int16\n",
      "---------------------------------------------------------------\n",
      "min = -32768\n",
      "max = 32767\n",
      "---------------------------------------------------------------\n",
      "\n",
      "Machine parameters for float16\n",
      "---------------------------------------------------------------\n",
      "precision=  3   resolution= 1.00040e-03\n",
      "machep=   -10   eps=        9.76562e-04\n",
      "negep =   -11   epsneg=     4.88281e-04\n",
      "minexp=   -14   tiny=       6.10352e-05\n",
      "maxexp=    16   max=        6.55040e+04\n",
      "nexp  =     5   min=        -max\n",
      "---------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print np.iinfo(np.int16)\n",
    "print np.finfo(np.float16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3 as sql\n",
    "conn = sql.connect('traffic.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rm: cannot remove 'traffic/no_commas.h5': No such file or directory\r\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0154bc12cded4372903e9f7a64f3eaec"
      }
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "in_it = pd.read_csv('traffic/no_commas.csv',delimiter=';',parse_dates=['horodate'],chunksize=50000,\n",
    "                    dtype={'id_arc_trafic':np.int16, # this is NOT bcs I'm pedantic\n",
    "                           'debit':np.float16,       # but bcs otherwise it won't infer the right columns types\n",
    "                           'taux':np.float16})       # and will bail out with a misterious \"ValueError\"\n",
    "from itertools import izip\n",
    "for chunk in tqdm(in_it):\n",
    "    try:\n",
    "        # line by line pre-processing\n",
    "        chunk.debit = chunk.debit.apply(lambda x: 0 if np.isnan(x) else x)\n",
    "        chunk.taux = chunk.taux.apply(lambda x: 0 if np.isnan(x) else x)\n",
    "        chunk=chunk.assign(hour=chunk.horodate.apply(lambda x:x.hour))\n",
    "        chunk=chunk.assign(month=chunk.horodate.apply(lambda x:x.month))\n",
    "        chunk=chunk.assign(BDay=chunk.horodate.apply(lambda x:x.isoweekday() not in (6,7)))\n",
    "        # map from counter id to its position\n",
    "        chunk = chunk.assign(lat=chunk.id_arc_trafic.apply(lambda x:posdict[float(x)]['lat']))\n",
    "        chunk = chunk.assign(lon=chunk.id_arc_trafic.apply(lambda x:posdict[float(x)]['lon']))        \n",
    "        chunk = chunk[['hour','month','BDay','debit','taux','id_arc_trafic','lat','lon']]        \n",
    "        \n",
    "        chunk.to_sql('traffic',conn,if_exists='append')\n",
    "    except ValueError,msg:\n",
    "        print msg\n",
    "        print chunk\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3,), (4,), (1,), (2,), (5,), (6,), (7,), (8,), (9,), (10,), (11,), (12,)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#safety checks\n",
    "conn.commit()\n",
    "cur = conn.cursor()\n",
    "cur.execute('SELECT DISTINCT month FROM traffic')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(31583340,)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('select count(hour) from traffic')\n",
    "cur.fetchall()[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregate records\n",
    "We keep only records of business days (when `BDay==True`) and group by hour and month, meaning that we are averaging over the\n",
    "weekdays. The results of the query are read in chunks of 200 records and directly stored in a (this time small) hdf file using `pandas.HDFStore`. In this way the work is done mostly out-of-memory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# aggregate by hour, month, and traffic counter (id_arc_trafic)\n",
    "sql_query = 'SELECT hour,month,id_arc_trafic,AVG(debit),AVG(taux),lat,lon FROM traffic WHERE BDay GROUP BY hour, month, id_arc_trafic;'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7ebef96f596c414388b517adcbcb142c"
      }
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "!rm traffic/aggregates.hdf \n",
    "out = pd.HDFStore('traffic/aggregates.hdf')\n",
    "for rows in tqdm(pd.read_sql_query(sql_query,conn,chunksize=200)):\n",
    "    out.append(key = '/aggregates',value = rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "agg = out['/aggregates']\n",
    "out.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hour</th>\n",
       "      <th>month</th>\n",
       "      <th>id_arc_trafic</th>\n",
       "      <th>AVG(debit)</th>\n",
       "      <th>AVG(taux)</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1188.714286</td>\n",
       "      <td>4.638207</td>\n",
       "      <td>48.859838</td>\n",
       "      <td>2.334242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1188.714286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.859375</td>\n",
       "      <td>2.336451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1188.714286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.859019</td>\n",
       "      <td>2.339782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1188.714286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.858747</td>\n",
       "      <td>2.341134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1479.809524</td>\n",
       "      <td>6.076404</td>\n",
       "      <td>48.858214</td>\n",
       "      <td>2.343447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1479.809524</td>\n",
       "      <td>13.699405</td>\n",
       "      <td>48.857746</td>\n",
       "      <td>2.345062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1479.809524</td>\n",
       "      <td>11.016927</td>\n",
       "      <td>48.857355</td>\n",
       "      <td>2.346448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>1392.595238</td>\n",
       "      <td>8.957589</td>\n",
       "      <td>48.856938</td>\n",
       "      <td>2.348014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>1364.214286</td>\n",
       "      <td>7.782878</td>\n",
       "      <td>48.856589</td>\n",
       "      <td>2.349191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1364.214286</td>\n",
       "      <td>7.782878</td>\n",
       "      <td>48.856269</td>\n",
       "      <td>2.350029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1540.571429</td>\n",
       "      <td>9.654390</td>\n",
       "      <td>48.855648</td>\n",
       "      <td>2.351637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>1540.571429</td>\n",
       "      <td>10.699870</td>\n",
       "      <td>48.854795</td>\n",
       "      <td>2.353740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>324.952381</td>\n",
       "      <td>2.404925</td>\n",
       "      <td>48.854881</td>\n",
       "      <td>2.353860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>338.380952</td>\n",
       "      <td>1.776158</td>\n",
       "      <td>48.855719</td>\n",
       "      <td>2.351751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>338.380952</td>\n",
       "      <td>2.666806</td>\n",
       "      <td>48.856459</td>\n",
       "      <td>2.350875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>338.380952</td>\n",
       "      <td>0.445249</td>\n",
       "      <td>48.857045</td>\n",
       "      <td>2.350203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.362026</td>\n",
       "      <td>48.862239</td>\n",
       "      <td>2.334798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>338.380952</td>\n",
       "      <td>2.804688</td>\n",
       "      <td>48.857519</td>\n",
       "      <td>2.348571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>21</td>\n",
       "      <td>981.523810</td>\n",
       "      <td>3.695173</td>\n",
       "      <td>48.856416</td>\n",
       "      <td>2.356051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>981.523810</td>\n",
       "      <td>3.695173</td>\n",
       "      <td>48.856744</td>\n",
       "      <td>2.354633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>918.642857</td>\n",
       "      <td>4.735770</td>\n",
       "      <td>48.857121</td>\n",
       "      <td>2.353057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>918.642857</td>\n",
       "      <td>5.552083</td>\n",
       "      <td>48.857413</td>\n",
       "      <td>2.351899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>999.119048</td>\n",
       "      <td>5.824079</td>\n",
       "      <td>48.857809</td>\n",
       "      <td>2.350472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>26</td>\n",
       "      <td>999.119048</td>\n",
       "      <td>11.059198</td>\n",
       "      <td>48.858303</td>\n",
       "      <td>2.348870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>999.119048</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.858661</td>\n",
       "      <td>2.347822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1056.190476</td>\n",
       "      <td>6.621745</td>\n",
       "      <td>48.858959</td>\n",
       "      <td>2.346927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>1056.190476</td>\n",
       "      <td>6.095331</td>\n",
       "      <td>48.859501</td>\n",
       "      <td>2.345237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1056.190476</td>\n",
       "      <td>6.095331</td>\n",
       "      <td>48.859780</td>\n",
       "      <td>2.344365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>1056.190476</td>\n",
       "      <td>5.461821</td>\n",
       "      <td>48.860008</td>\n",
       "      <td>2.343651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>1056.190476</td>\n",
       "      <td>5.461821</td>\n",
       "      <td>48.860679</td>\n",
       "      <td>2.341583</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6936</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.862253</td>\n",
       "      <td>2.413408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6937</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.852407</td>\n",
       "      <td>2.414498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6938</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.845208</td>\n",
       "      <td>2.413945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6939</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.842771</td>\n",
       "      <td>2.413549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6940</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.831201</td>\n",
       "      <td>2.403014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6941</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.835044</td>\n",
       "      <td>2.411673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6942</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.848212</td>\n",
       "      <td>2.414986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6943</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.855652</td>\n",
       "      <td>2.414209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6944</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.880679</td>\n",
       "      <td>2.405660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6945</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.900516</td>\n",
       "      <td>2.382387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6946</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.900912</td>\n",
       "      <td>2.367545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6947</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.901063</td>\n",
       "      <td>2.361038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6948</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.901087</td>\n",
       "      <td>2.342005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6949</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.891027</td>\n",
       "      <td>2.299367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6950</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.885111</td>\n",
       "      <td>2.286641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6951</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.870373</td>\n",
       "      <td>2.271898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6952</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.861634</td>\n",
       "      <td>2.263959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6953</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.859350</td>\n",
       "      <td>2.261782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6954</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.846408</td>\n",
       "      <td>2.254357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6955</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.832910</td>\n",
       "      <td>2.278801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6956</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.824170</td>\n",
       "      <td>2.306444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6957</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.822075</td>\n",
       "      <td>2.315902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6959</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.816072</td>\n",
       "      <td>2.363022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6960</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.820158</td>\n",
       "      <td>2.375422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6961</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.881249</td>\n",
       "      <td>2.401148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6962</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>48.820594</td>\n",
       "      <td>2.322511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6971</td>\n",
       "      <td>532.318182</td>\n",
       "      <td>2.572843</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6996</td>\n",
       "      <td>302.363636</td>\n",
       "      <td>15.004084</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6997</td>\n",
       "      <td>287.409091</td>\n",
       "      <td>2.222878</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>6998</td>\n",
       "      <td>144.909091</td>\n",
       "      <td>0.813732</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>831168 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     hour  month  id_arc_trafic   AVG(debit)  AVG(taux)        lat       lon\n",
       "0       0      1              1  1188.714286   4.638207  48.859838  2.334242\n",
       "1       0      1              2  1188.714286   0.000000  48.859375  2.336451\n",
       "2       0      1              3  1188.714286   0.000000  48.859019  2.339782\n",
       "3       0      1              4  1188.714286   0.000000  48.858747  2.341134\n",
       "4       0      1              5  1479.809524   6.076404  48.858214  2.343447\n",
       "5       0      1              6  1479.809524  13.699405  48.857746  2.345062\n",
       "6       0      1              7  1479.809524  11.016927  48.857355  2.346448\n",
       "7       0      1              8  1392.595238   8.957589  48.856938  2.348014\n",
       "8       0      1              9  1364.214286   7.782878  48.856589  2.349191\n",
       "9       0      1             10  1364.214286   7.782878  48.856269  2.350029\n",
       "10      0      1             11  1540.571429   9.654390  48.855648  2.351637\n",
       "11      0      1             12  1540.571429  10.699870  48.854795  2.353740\n",
       "12      0      1             13   324.952381   2.404925  48.854881  2.353860\n",
       "13      0      1             14   338.380952   1.776158  48.855719  2.351751\n",
       "14      0      1             15   338.380952   2.666806  48.856459  2.350875\n",
       "15      0      1             16   338.380952   0.445249  48.857045  2.350203\n",
       "16      0      1             17     0.000000   4.362026  48.862239  2.334798\n",
       "17      0      1             18   338.380952   2.804688  48.857519  2.348571\n",
       "18      0      1             21   981.523810   3.695173  48.856416  2.356051\n",
       "19      0      1             22   981.523810   3.695173  48.856744  2.354633\n",
       "20      0      1             23   918.642857   4.735770  48.857121  2.353057\n",
       "21      0      1             24   918.642857   5.552083  48.857413  2.351899\n",
       "22      0      1             25   999.119048   5.824079  48.857809  2.350472\n",
       "23      0      1             26   999.119048  11.059198  48.858303  2.348870\n",
       "24      0      1             27   999.119048   0.000000  48.858661  2.347822\n",
       "25      0      1             28  1056.190476   6.621745  48.858959  2.346927\n",
       "26      0      1             29  1056.190476   6.095331  48.859501  2.345237\n",
       "27      0      1             30  1056.190476   6.095331  48.859780  2.344365\n",
       "28      0      1             31  1056.190476   5.461821  48.860008  2.343651\n",
       "29      0      1             32  1056.190476   5.461821  48.860679  2.341583\n",
       "..    ...    ...            ...          ...        ...        ...       ...\n",
       "138    23     12           6936     0.000000   0.000000  48.862253  2.413408\n",
       "139    23     12           6937     0.000000   0.000000  48.852407  2.414498\n",
       "140    23     12           6938     0.000000   0.000000  48.845208  2.413945\n",
       "141    23     12           6939     0.000000   0.000000  48.842771  2.413549\n",
       "142    23     12           6940     0.000000   0.000000  48.831201  2.403014\n",
       "143    23     12           6941     0.000000   0.000000  48.835044  2.411673\n",
       "144    23     12           6942     0.000000   0.000000  48.848212  2.414986\n",
       "145    23     12           6943     0.000000   0.000000  48.855652  2.414209\n",
       "146    23     12           6944     0.000000   0.000000  48.880679  2.405660\n",
       "147    23     12           6945     0.000000   0.000000  48.900516  2.382387\n",
       "148    23     12           6946     0.000000   0.000000  48.900912  2.367545\n",
       "149    23     12           6947     0.000000   0.000000  48.901063  2.361038\n",
       "150    23     12           6948     0.000000   0.000000  48.901087  2.342005\n",
       "151    23     12           6949     0.000000   0.000000  48.891027  2.299367\n",
       "152    23     12           6950     0.000000   0.000000  48.885111  2.286641\n",
       "153    23     12           6951     0.000000   0.000000  48.870373  2.271898\n",
       "154    23     12           6952     0.000000   0.000000  48.861634  2.263959\n",
       "155    23     12           6953     0.000000   0.000000  48.859350  2.261782\n",
       "156    23     12           6954     0.000000   0.000000  48.846408  2.254357\n",
       "157    23     12           6955     0.000000   0.000000  48.832910  2.278801\n",
       "158    23     12           6956     0.000000   0.000000  48.824170  2.306444\n",
       "159    23     12           6957     0.000000   0.000000  48.822075  2.315902\n",
       "160    23     12           6959     0.000000   0.000000  48.816072  2.363022\n",
       "161    23     12           6960     0.000000   0.000000  48.820158  2.375422\n",
       "162    23     12           6961     0.000000   0.000000  48.881249  2.401148\n",
       "163    23     12           6962     0.000000   0.000000  48.820594  2.322511\n",
       "164    23     12           6971   532.318182   2.572843   0.000000  0.000000\n",
       "165    23     12           6996   302.363636  15.004084   0.000000  0.000000\n",
       "166    23     12           6997   287.409091   2.222878   0.000000  0.000000\n",
       "167    23     12           6998   144.909091   0.813732   0.000000  0.000000\n",
       "\n",
       "[831168 rows x 7 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}