parse_data_geneva.ipynb

{
 "metadata": {
  "name": "parse_data_geneva"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import json\n",
      "import utils"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store = pd.HDFStore('data/geneva/data.hd5')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "replacements = {\n",
      "'date': 'date',\n",
      "'routeCode': 'id_route',\n",
      "'tripId': 'id_trip',\n",
      "'stopCode': 'id_stop',\n",
      "'tripDirection': 'trip_direction',\n",
      "'stopSequenceNr': 'stop_seq',\n",
      "'stopTimeSchedule': 'time_scheduled_arrival',\n",
      "'stopTimeReal': 'time_arrival',\n",
      "'stopLength': 'stop_distance',\n",
      "'passengerCountStopUp': 'count_boarding',\n",
      "'passengerCountStopDown': 'count_exiting',\n",
      "'passengerLoadStop': 'count',\n",
      "}\n",
      "pcounts = pd.read_csv('data/geneva/schedule-real-time.csv', \n",
      "    skiprows=[1],  # skip blank row\n",
      "    usecols=replacements.keys(),\n",
      "    parse_dates=['date', 'stopTimeSchedule', 'stopTimeReal'],\n",
      "    \n",
      ").rename(columns=replacements)\n",
      "\n",
      "pcounts"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 24,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 1169057 entries, 0 to 1169056\n",
        "Data columns:\n",
        "date                      1169057  non-null values\n",
        "id_route                  1168881  non-null values\n",
        "id_trip                   1169057  non-null values\n",
        "trip_direction            1169057  non-null values\n",
        "stop_seq                  1169057  non-null values\n",
        "id_stop                   1169057  non-null values\n",
        "stop_distance             1169057  non-null values\n",
        "time_scheduled_arrival    1169057  non-null values\n",
        "time_arrival              1169057  non-null values\n",
        "count_boarding            1169057  non-null values\n",
        "count_exiting             1169057  non-null values\n",
        "count                     1169057  non-null values\n",
        "dtypes: datetime64[ns](2), int64(6), object(4)"
       ]
      }
     ],
     "prompt_number": 24
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# A=inbound or R=outbound\n",
      "pcounts['trip_direction'] = pcounts.trip_direction.replace({'A': 1, 'R': 0})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 27
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for tcol in ['time_scheduled_arrival', 'time_arrival']:\n",
      "    nan_str = 'nan' if 'nan' in pcounts[tcol] else ''\n",
      "    pcounts[tcol] = pd.DatetimeIndex(pcounts[tcol].replace(nan_str, '')).values"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store['passenger_counts'] = pcounts"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 65
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Common data format"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pcounts[\n",
      "    ['date', 'id_route', 'id_trip', 'trip_direction', 'stop_seq', 'id_stop', 'stop_distance'] + \\\n",
      "    ['time_arrival', 'time_scheduled_arrival', 'count', 'count_boarding', 'count_exiting']\n",
      "    ].to_csv('data/common_format/geneva.csv', index=False)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 66
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Topojson"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Stops"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "with open('data/geneva/topojson/stops.json', 'r') as f: \n",
      "    stop_data = json.loads(f.read())\n",
      "\n",
      "replacements = {\n",
      "    # 'routeCode': 'id_route',\n",
      "    'stopCode': 'id_stop',\n",
      "    'stopName': 'name_stop',\n",
      "    # 'routeDirection': 'route_direction',\n",
      "    # 'routeDestination': 'name_route'\n",
      "    }\n",
      "\n",
      "stops_df = pd.DataFrame(map(lambda g: g['properties'], stop_data['objects']['stops']['geometries']))\n",
      "stops_df = stops_df[replacements.keys()].rename(columns=replacements).drop_duplicates()\n",
      "# convert direction inbound -- 1=False, 2=True, unknowns default to False\n",
      "# stops_df.route_direction = stops_df.route_direction.fillna(1).astype(int) - 1\n",
      "stops_df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 17,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 1896 entries, 0 to 4151\n",
        "Data columns:\n",
        "name_stop    1896  non-null values\n",
        "id_stop      1896  non-null values\n",
        "dtypes: object(2)"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "stop_data['objects']['stops']['geometries'] = \\\n",
      "    [stop_data['objects']['stops']['geometries'][i] for i in stops_df.index]\n",
      "\n",
      "for i, obj in enumerate(stop_data['objects']['stops']['geometries']):\n",
      "    obj['properties'] = stops_df.reset_index().ix[i].apply(utils.native_types).to_dict()\n",
      "    \n",
      "    \n",
      "with open('web/data/geneva/stops.json', 'w+') as f:\n",
      "    f.write(json.dumps(stop_data))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 18
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Routes"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "all_route_ids = pd.read_csv('data/common_format/geneva.csv').id_route.dropna().unique()\n",
      "print(len(all_route_ids))\n",
      "all_route_ids"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "64\n"
       ]
      },
      {
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "array(['E', '22', '11', '5', '23', '9', '1', 'L', 'A', '33', '34', 'K',\n",
        "       '8', 'C', 'G', '31', '42', '21', '14', '3', '15', '12', 'GB', 'EB',\n",
        "       'V', 'Z', 'VB', '28', '51', 'X', 'B', 'DN', '53', '45', '44', 'S',\n",
        "       '43', 'W', 'F', 'Y', 'D', '41', '47', '57', 'O', '4', '46', '151',\n",
        "       '7', '19', '6', '2', '10', '12B', 'NE', 'NK', 'NC', 'NT', 'ND',\n",
        "       'NM', 'NJ', 'NV', 'NO', 'NS'], dtype=object)"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "with open('data/geneva/topojson/routes.json', 'r') as f: \n",
      "    route_data = json.loads(f.read())\n",
      "    \n",
      "route_df = pd.DataFrame(map(\n",
      "    lambda obj: obj['properties'], route_data['objects']['routes']['geometries'])\n",
      "    )\n",
      "replacements = {\n",
      "    'routeCode': 'id_route',\n",
      "    'routeDestination': 'name_route'\n",
      "    }\n",
      "route_df = route_df.rename(columns=replacements)[replacements.values()]\n",
      "\n",
      "# filter to the unique routes in the passenger data\n",
      "route_df = route_df.ix[route_df.id_route.isin(all_route_ids)]\n",
      "\n",
      "# fill in missing name data \n",
      "route_df.ix[pd.isnull(route_df.name_route), 'name_route'] = \\\n",
      "    route_df.ix[pd.isnull(route_df.name_route), 'id_route']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "route_df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 18,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 110 entries, 0 to 167\n",
        "Data columns:\n",
        "id_route      110  non-null values\n",
        "name_route    110  non-null values\n",
        "dtypes: object(2)"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# filter the json routes to only ones with valid id_route\n",
      "route_data['objects']['routes']['geometries'] = \\\n",
      "    [route_data['objects']['routes']['geometries'][i] for i in route_df.index]\n",
      "\n",
      "for i, obj in enumerate(route_data['objects']['routes']['geometries']):\n",
      "    obj['properties'] = route_df.reset_index().ix[i].apply(utils.native_types).to_dict()\n",
      "\n",
      "with open('web/data/geneva/routes.json', 'w+') as f:\n",
      "    f.write(json.dumps(route_data))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 19
    }
   ],
   "metadata": {}
  }
 ]
}