In [1]:

    

# For staff outside London, they 
# Pick a random sample of UK postcodes.
import pandas as pd


    

In [2]:

    

# get secret key
from mylibrary.secrets import app_id, app_key, orig_name, dest_name, orig_pc, dest_pc
from mylibrary.connections import cursor, conn, engine, Automapped_Base, session


    

    




/Users/robinlinacre/anaconda/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2505: SAWarning: Did not recognize type 'geometry' of column 'geom'
  (attype, name))

In [3]:

    

sql = """
select  s.postcode, s.geom, g.name from tt_gh.staff_locations as s, tt_gh.gb_and_london as g
where st_contains(g.geom,s.geom)  and g.name='london'
"""
london_staff = pd.read_sql(sql, conn)
london_staff.sample(5)
print(len(london_staff))


    

    




1000

In [4]:

    

# RUNNING THIS WILL DELETE ALL YOUR JOURNEYTIME RESULTS!!!

# for name in (orig_name, dest_name):
    
#     sql = """
#     drop table if exists tt_gh.london_journeytime_{name};
    
#     create table tt_gh.london_journeytime_{name} as
#     select row_number() over () as id, s.postcode, s.geom from tt_gh.staff_locations as  s, tt_gh.gb_and_london as g
#     where st_contains(g.geom,s.geom) and g.name = 'london'
#     """.format(name = name)
#     cur = conn.cursor()
#     cur.execute(sql)
#     conn.commit()

#     sql = """
#     ALTER TABLE tt_gh.london_journeytime_{name} 
#     ADD tfl_request text, ADD tfl_response json,
#     ADD querydict json,
#     ADD journey_time int,
#     ADD tfl_message text;
   
#     ALTER TABLE tt_gh.london_journeytime_{name} ADD PRIMARY KEY (id);

#     """.format(name=name)
#     cur = conn.cursor()
#     cur.execute(sql)
#     conn.commit()


    

In [4]:

    

from mylibrary.connections import cursor, conn, engine, Automapped_Base, session

param_dict = {
    "to": orig_pc.replace(" ",""),
    "id": app_id,
    "key": app_key,
    "time": "1000", 
    "date": "20160517",
    "nationalsearch": False
    }

param_dicts = []
param_dicts.append(param_dict)

d1 = param_dict.copy()
d1["time"] = "1500"
d1["date"] = "20160518"
param_dicts.append(d1)

d2 = param_dict.copy()
d2["time"] = "1600"
d2["date"] = "20160519"
param_dicts.append(d2)


    

In [5]:

    

from sqlalchemy import or_
from mylibrary.tfl_helpers import get_journeyplanner_raw_content, status_of_tfl_response_journey, get_journeyplanner_results

London_orig = Automapped_Base.classes.london_journeytime_stpauls
London_dest = Automapped_Base.classes.london_journeytime_camden


    

In [5]:

    

for London in (London_orig,London_dest):
    ids  = session.query(London.id).filter(or_(London.tfl_message != 'ok',London.tfl_message == None)).all()
#     ids  = session.query(London.id).all()
    for id in ids:
        station = session.query(London).filter(London.id == id).one()
        for d in param_dicts:
            d["from"] = station.postcode
            if "stpauls" in London.__table__.name:
                d["to"] = orig_pc.replace(" ","")
            if "camden" in London.__table__.name:
                d["to"] = dest_pc.replace(" ","")

        journey_object = get_journeyplanner_results(param_dicts)

        station.tfl_request = journey_object["request_url"]
        station.tfl_response = journey_object["tfl_response"]
        station.querydict = journey_object["query_dict"]
        station.tfl_message = journey_object["tfl_message"]

        session.add(station)
        session.commit()


    

    




Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Traceback (most recent call last):
  File "mylibrary/tfl_helpers.py", line 30, in status_of_tfl_response_journey
    parsed_response = json.loads(content)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/Users/robinlinacre/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

In [6]:

    

# Finally pull out journeytime
def get_duration(station):
    tfl_response_json = json.loads(station.tfl_response)
    return_object = {}
    return_object["duration"] = tfl_response_json["journeys"][0]["duration"]
    return return_object 

import json


for London in (London_orig, London_dest):
    ids = session.query(London.id).filter(London.tfl_message == "ok").all()
    for id in ids:
        try:
            station = session.query(London).filter(London.id == id).one()
            journey = get_duration(station)
            station.journey_time = journey["duration"]
            session.add(station)
            session.commit()
        except:
            pass


    

In [ ]: