Traveline Data Merge.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:8d7a0db4bdb10e99ec8cdeb9551388b4755b30fde2dfa0cd5be9f46bbd2e886e"
 },
 "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\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 \n",
      "naptan_df = read_csv('../data/naptan/NaPTANcsv/Stops.csv')\n",
      "#naptan_df = naptan_df[pd.notnull(naptan_df['NaptanCode'])]\n",
      "#naptan_df[naptan_df.NaptanCode.notnull()]\n",
      "#naptan_df = naptan_df[pd.isnull(naptan_df['NaptanCode'])]\n",
      "#naptan_df = naptan_df[naptan_df['NaptanCode'].str.contains('bth')]\n",
      "#naptan_df = naptan_df[naptan_df['NaptanCode'].str.startswith('bth')]\n",
      "\n",
      "#naptan_df = read_csv('../data/naptan/BathStops.csv')\n",
      "#naptan_df[\"Latitude\"]\n",
      "#len(naptan_df)\n",
      "#naptan_df\n",
      "#pd.isnull(naptan_df['NaptanCode'].str.startswith('bth')).unique()"
     ],
     "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",
      "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",
      "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": {}
  }
 ]
}