Create a gist now

Instantly share code, notes, and snippets.

anonymous /gist:d5ddd97c10b001f9fa69 Secret
Created Aug 21, 2013

import MySQLdb
db = MySQLdb.connect(host="", user="", passwd="", db="")
cur = db.cursor()
#create table bikenyc_ind as select * FROM `bike_ind_newyork` WHERE timestamp > '2013-07-16 22:00' and timestamp < '2013-08-16 22:00'
cur.execute("select tfl_id, bikes, timestamp, extract(hour from timestamp) as hour, date(timestamp) as date from bikenyc_ind order by tfl_id, timestamp")
curr_tflid = -1
curr_bikes = -1
events_7_10_and_16_19 = 0
events_10_16 = 0
events_19_7 = 0
print "docking_station_id,events_rushhours,events_intrapeak,events_eveningnight";
for row in cur.fetchall():
if curr_tflid == -1: #First row
curr_tflid = row[0]
curr_bikes = row[1]
if curr_tflid != row[0]: #New docking station
print str(curr_tflid) + "," + str(events_7_10_and_16_19) + "," + str(events_10_16) + "," + str(events_19_7)
curr_tflid = row[0]
curr_bikes = row[1]
events_7_10_and_16_19 = 0
events_10_16 = 0
events_19_7 = 0
else:
if curr_bikes != row[1]: #Event detected
delta = curr_bikes - row[1]
if delta < 0:
delta = delta * -1
if row[3] >= 21:
events_7_10_and_16_19 = events_7_10_and_16_19 + delta
elif row[3] >= 15:
events_10_16 = events_10_16 + delta
elif row[3] >= 12:
events_7_10_and_16_19 = events_7_10_and_16_19 + delta
else:
events_19_7 = events_19_7 + delta
curr_bikes = row[1]
#Print results for final docking station.
print str(curr_tflid) + "," + str(events_7_10_and_16_19) + "," + str(events_10_16) + "," + str(events_19_7)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment