Traveline Data Merge.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:194a4db4beb46ff7c1d5d74ddb0e0073b5f133992f4d41d05a9bb4a1b4b15fb8"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Merge Traveline bus stop data with Naptan information\n",
      "import pandas as pd\n",
      "from pandas import read_csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Import the stops/route data (generated by traveline.py script)\n",
      "stops_df = read_csv('data/AnnotatedStopPointRef.csv')\n",
      "routes_df = read_csv('data/RouteSection.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Import Naptan stops (this file is downloaded from http://data.gov.uk/dataset/naptan - it's the zip of CSV files)\n",
      "naptan_df = read_csv('../data/naptan/NaPTANcsv/Stops.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "/home/normal/Projects/HackDays/BathHacked2014/env/local/lib/python2.7/site-packages/pandas/io/parsers.py:1150: DtypeWarning: Columns (1,2,5,6,7,9,11,12,13,15,20,21,22,23,24,34,35,36) have mixed types. Specify dtype option on import or set low_memory=False.\n",
        "  data = self._reader.read(nrows)\n"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Merge traveline and naptan stop data\n",
      "stops_df = pd.merge(stops_df, naptan_df, left_on='StopPointRef', right_on='AtcoCode')\n",
      "len(stops_df)\n",
      "\n",
      "# Write bus stops data\n",
      "stops_df.to_csv('data/traveline_naptan_stops.csv')\n",
      "#len(stops_df)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Enrich traveline routes with Naptan info\n",
      "stops_lat_long = naptan_df[[\"AtcoCode\", \"NaptanCode\", \"LocalityName\", \"Latitude\", \"Longitude\"]]\n",
      "#stops_lat_long\n",
      "routes_info_df = pd.merge(routes_df, stops_lat_long, left_on='From', right_on='AtcoCode')\n",
      "del routes_info_df['NaptanCode']\n",
      "routes_info_df.rename(columns={'AtcoCode': 'From_AtcoCode', \n",
      "                               'Latitude': 'From_Latitude', \n",
      "                               'Longitude': 'From_Longitude',\n",
      "                               'LocalityName': 'From_LocalityName'}, inplace=True)\n",
      "routes_info_df = pd.merge(routes_info_df, stops_lat_long, left_on='To', right_on='AtcoCode')\n",
      "routes_info_df.rename(columns={'AtcoCode': 'To_AtcoCode', \n",
      "                               'Latitude': 'To_Latitude', \n",
      "                               'Longitude': 'To_Longitude',\n",
      "                               'LocalityName': 'To_LocalityName'}, inplace=True)\n",
      "\n",
      "# Write bus route data\n",
      "routes_info_df.to_csv('data/routes_with_latlong.csv')\n",
      "routes_info_df_bath = routes_info_df[routes_info_df['To_LocalityName'] == 'Bath City Centre']\n",
      "routes_info_df_bath.to_csv('data/routes_with_latlong_bath.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}