gistfile1.txt

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)