!date

import pandas as pd
import pandas.io.sql as psql
import mysql.connector as sql

import urllib2
import urllib
import json
import time

import pickle
with open('sql.config','rb') as fp:
    config = pickle.load(fp)

con = sql.connect(**config)
df = psql.frame_query('''
    SELECT DISTINCT(STAD) 
    FROM games;''', con)

con.close()

main_url = "http://maps.googleapis.com/maps/api/geocode/json?"

ELEVATION_BASE_URL = 'http://maps.googleapis.com/maps/api/elevation/json'
LOCATION_BASE_URL = 'http://maps.googleapis.com/maps/api/geocode/json'

def getAlt(d):    
    loc_args = {'address': d, 'sensor': 'false'}
    url = LOCATION_BASE_URL + '?' + urllib.urlencode(loc_args)
    page = urllib2.urlopen(url)
    data = json.load(page)
    time.sleep(0.1)
    if 'results' in data and len(data['results'])>0:
        ll = data['results'][0]['geometry']['location']
        loc = ','.join(str(x) for x in [ll['lat'],ll['lng']])
        el_args = {'locations': loc, 'sensor': 'false'}
        url = ELEVATION_BASE_URL + '?' + urllib.urlencode(el_args)
        page = urllib2.urlopen(url)
        el_data = json.load(page)
        if 'results' in el_data:   
            return ll['lat'], ll['lng'], el_data['results'][0]['elevation']
        else:
            return None, None, None
    return None, None, None

df['lat'], df['lng'], df['alt'] = zip(*df.STAD.map(getAlt))
df.head()

df[df.lat.isnull()].head(12)

df.ix[df.STAD.str.contains('Network Associates Coliseum'),'alt'] = 13.11
df.ix[df.STAD.str.contains('Trans World Dome'),'alt'] = 142.03
df.ix[df.STAD.str.contains('PSINet'),'alt'] = 3.05
df.ix[df.STAD.str.contains('RCA'),'alt'] = 216.34
df.ix[df.STAD.str.contains('Seahawks'),'alt'] = 5.31
df.ix[df.STAD.str.contains('Qwest'),'alt'] = 5.31
df.ix[df.STAD.str.contains('McAfee'),'alt'] = 10.74
df.ix[df.STAD.str.contains('Azteca'),'alt'] = 2268.13
df.ix[df.STAD.str.contains('Mall of America'),'alt'] = 253.42
df.ix[df.STAD.str.contains('New Meadowlands'),'alt'] = 0.86
df.ix[df.STAD.str.contains('Mercedes-Benz'),'alt'] = 0.0006
df.ix[df.STAD.str.contains('Snapdragon'),'alt'] = 22.89


df[df.lat.isnull()].head(12)

df.to_csv('stadium_with_altitude.csv')

%matplotlib inline
import matplotlib.pyplot as plt

df.alt.hist(figsize=(12,8), bins=20)
plt.xlabel('Elevation (m)')
plt.ylabel('Count')

print 'Denver elevation: %.2f (m)' % df[df.STAD.str.contains('Mile')].alt[:1]
print 'Mexico City elevation: %.2f (m)' % df[df.STAD.str.contains('Azteca')].alt[:1]

from IPython.core.display import HTML
def css_styling():
    styles = open("style.css", "r").read()
    return HTML(styles)
css_styling()