Skip to content

Instantly share code, notes, and snippets.

/gist:d5ddd97c10b001f9fa69 Secret
Created Aug 21, 2013

Embed
What would you like to do?
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
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.