Classifying Manhattan with BigQuery and TensorFlow

In [10]:

    

%%sql -d standard
SELECT
  timestamp,
  borough,
  latitude,
  longitude
FROM
  `bigquery-public-data.new_york.nypd_mv_collisions`
ORDER BY
  timestamp DESC
LIMIT
  15


    

    
Out[10]:





    
timestamp
borough
latitude
longitude
2017-08-04 23:50:00
QUEENS
40.725006
-73.8792
2017-08-04 23:45:00
QUEENS
40.680298
-73.7753
2017-08-04 23:45:00
BROOKLYN
 
 
2017-08-04 23:40:00
 
 
2017-08-04 23:40:00
QUEENS
40.691643
-73.82005
2017-08-04 23:35:00
40.772068
-73.92008
2017-08-04 23:35:00
MANHATTAN
40.760746
-73.99214
2017-08-04 23:30:00
40.68405
-73.97746
2017-08-04 23:30:00
QUEENS
40.686497
-73.80805
2017-08-04 23:30:00
QUEENS
40.699825
-73.88677
2017-08-04 23:27:00
40.60264
-73.98639
2017-08-04 23:13:00
MANHATTAN
40.758377
-73.994385
2017-08-04 23:00:00
40.55336
-74.20217
2017-08-04 23:00:00
BRONX
 
 
2017-08-04 23:00:00
40.738575
-73.845345
    
(rows: 15, time: 3.1s,    31MB processed, job: job_YlaOh7u8qVxJLfSdB-2bAQRNBSeI)

    
  

In [11]:

    

%%sql --module nyc_collisions
SELECT
  IF(borough = 'MANHATTAN', 1, 0) AS is_mt,
  latitude,
  longitude
FROM
  `bigquery-public-data.new_york.nypd_mv_collisions`
WHERE
  LENGTH(borough) > 0
  AND latitude IS NOT NULL AND latitude != 0.0
  AND longitude IS NOT NULL AND longitude != 0.0
  AND borough != 'BRONX'
ORDER BY
  RAND()
LIMIT
  10000


    

In [12]:

    

import datalab.bigquery as bq
nyc_cols = bq.Query(nyc_collisions).to_dataframe(dialect='standard').as_matrix()

import numpy as np
is_mt = nyc_cols[:,0].astype(np.int32)
latlng = nyc_cols[:,1:3].astype(np.float32)
print("Is Manhattan: " + str(is_mt))
print("\nLat/Lng: \n\n" + str(latlng))
print("\nLoaded " + str(is_mt.size) + " rows.")


    

    




Is Manhattan: [0 0 0 ..., 0 0 0]

Lat/Lng: 

[[ 40.70204544 -73.91559601]
 [ 40.61207962 -74.08526611]
 [ 40.76537704 -73.74395752]
 ..., 
 [ 40.6367836  -74.00259399]
 [ 40.68369293 -73.95396423]
 [ 40.63857651 -73.90413666]]

Loaded 10000 rows.

In [13]:

    

# standardization
from sklearn.preprocessing import StandardScaler
latlng_std = StandardScaler().fit_transform(latlng)

# plotting
import matplotlib.pyplot as plt
lat = latlng_std[:,0]
lng = latlng_std[:,1]
plt.scatter(lng[is_mt == 1], lat[is_mt == 1], c='b') # plot points in Manhattan in blue
plt.scatter(lng[is_mt == 0], lat[is_mt == 0], c='y') # plot points outside Manhattan in yellow
plt.show()


    

In [14]:

    

# 8,000 pairs for training
latlng_train = latlng_std[0:8000]
is_mt_train = is_mt[0:8000]

# 2,000 pairs for test
latlng_test = latlng_std[8000:10000]
is_mt_test = is_mt[8000:10000]


    

In [15]:

    

import tensorflow as tf
tf.logging.set_verbosity(tf.logging.ERROR) # supress warning messages

# define two feature columns with real values
feature_columns = [tf.contrib.layers.real_valued_column("", dimension=2)]

# create a neural network
dnnc = tf.contrib.learn.DNNClassifier(
  feature_columns=feature_columns,
  hidden_units=[20, 20, 20, 20],
  n_classes=2)

dnnc


    

    
Out[15]:





DNNClassifier(params={'head': <tensorflow.contrib.learn.python.learn.estimators.head._BinaryLogisticHead object at 0x7f7af81ee850>, 'hidden_units': [20, 20, 20, 20], 'feature_columns': (_RealValuedColumn(column_name='', dimension=2, default_value=None, dtype=tf.float32, normalizer=None),), 'embedding_lr_multipliers': None, 'optimizer': None, 'dropout': None, 'gradient_clip_norm': None, 'activation_fn': <function relu at 0x7f7b01be8c80>, 'input_layer_min_slice_size': None})

In [16]:

    

# plot a predicted map of Manhattan
def plot_predicted_map(classifier):
  is_mt_pred = classifier.predict(latlng_std, as_iterable=False) # an array of prediction results
  plt.scatter(lng[is_mt_pred == 1], lat[is_mt_pred == 1], c='b')
  plt.scatter(lng[is_mt_pred == 0], lat[is_mt_pred == 0], c='y')
  plt.show()

# print the accuracy of the neural network 
def print_accuracy(classifier):
  accuracy = classifier.evaluate(x=latlng_test, y=is_mt_test)["accuracy"]
  print('Accuracy: {:.2%}'.format(accuracy))
  
# train the model just for 1 step and print the accuracy
dnnc.fit(x=latlng_train, y=is_mt_train, steps=1)
plot_predicted_map(dnnc)
print_accuracy(dnnc)


    

    













    




Accuracy: 69.35%

In [17]:

    

steps = 20
for i in range (1, 6):
  dnnc.fit(x=latlng_train, y=is_mt_train, steps=steps)
  plot_predicted_map(dnnc)
  print('Steps: ' + str(i * steps))
  
print('\nTraining Finished.')
print_accuracy(dnnc)


    

    













    




Steps: 20






    













    




Steps: 40






    













    




Steps: 60






    













    




Steps: 80






    













    




Steps: 100

Training Finished.
Accuracy: 99.60%

In [ ]: