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