income=None,

purposes=purposes,

purposes_round_trip=purposes_round_trip,

#purpose_peak_flag=purpose_peak_flag,

bus_modes=bus_modes,

rail_modes=rail_modes,

np_rows=NP_ROWS):

" rail/bus costs = SUM((peak trips * peak fare) + (op trips * op fare))"

"""

The main assumption is that work-related trips are on peak, and others are off. Here are the mappings:

purposes_pk=[('hbw', 1), ('nhbw', 2)]

purposes_op=[('obo', 3), ('hbs', 4), ('hbo', 5)]

The mode_choice_od table disaggregates to income, mode, purpose. For instance, we have data for

trips from home to work, walking to the express bus, on-peak for each OD pair. From the fares_fares table,

we know the fare for the express bus on-peak.

So we can simply aggregate across all the mode choice/purpose pairs to capture all trips associated with some

income group and assign the correct fair to each.

We do this by coming up with a bunch of SELECT statements like this pair:

'--adding bus_rail_fares for hbw. wexpbus - outbound leg

SELECT DISTINCT

test_fares_fares.origin,

test_fares_fares.dest,

test_yesred_nohwy_mode_choice_od.hbw_inc1_wexpbus * test_fares_fares.wexpbus_pk

FROM

test_yesred_nohwy_mode_choice_od , test_fares_fares

WHERE

test_yesred_nohwy_mode_choice_od.origin=test_fares_fares.origin AND

test_fares_fares.dest=test_yesred_nohwy_mode_choice_od.dest

ORDER BY

test_fares_fares.origin, test_fares_fares.dest

--adding bus_rail_fares for hbw. wexpbus - return leg

SELECT DISTINCT

test_fares_fares.origin,

test_fares_fares.dest,

test_yesred_nohwy_mode_choice_od.hbw_inc1_wexpbus * test_fares_fares.wexpbus_pk

FROM

test_yesred_nohwy_mode_choice_od , test_fares_fares

WHERE

test_yesred_nohwy_mode_choice_od.dest=test_fares_fares.origin AND

test_yesred_nohwy_mode_choice_od.origin=test_fares_fares.dest

ORDER BY

test_fares_fares.origin, test_fares_fares.dest

"""

#general info for this metric

topic = 'bus_rail_fares'

trips_table = '{}mode_choice_od'.format(scenario)

metrics_table= '{}fares_fares'.format(prefix)

logger.info('Beginning aggregation of {} data'.format(topic))

#initialize null np array

npa=np.zeros(((np_rows-1)**2, 3)) #orig, dest, value

fresh_npa_array=True

for purpose in purposes:

#peak or off peak as f(purpose)

pk_flag=purpose_peak_flag[purpose]

#round trip of one-way (round trip for home based journeys)?

trip_legs=['outbound']

if purpose in purposes_round_trip:

trip_legs.append('return')

#calculate each leg of the trip separately

for trip_leg in trip_legs:

#loop thru appropriate modes (no fares w/ auto) and compose SELECT

for mode in bus_modes+rail_modes:

# --adding {topic} for {purpose}. {mode} - {trip_leg} leg\n'

select= '--adding {} for {}. {} - {} leg\n'.format(topic, purpose, mode, trip_leg)

# SELECT DISTINCT

select += 'SELECT DISTINCT\n '

# {metrics_table}.origin

select += '\t{}.origin,\n'.format(metrics_table)

# {metrics_table}.dest

select += '\t{}.dest,\n'.format(metrics_table)

# '{trips_table}.{purpose}_{income}_{mode} * {metrics_table}.{mode}_{pk_flag} '

stmt= '\t{}.{}_{}_{} * {}.{}_{}\n '

select+= stmt.format(trips_table, purpose, income, mode, metrics_table, mode, pk_flag)

# FROM {trips_table} , {metrics_table}

select += 'FROM \n\t {} , {} \n '.format( trips_table, metrics_table)

if trip_leg== 'outbound':

#use OD pairs from trip table same as metric table's

# WHERE {trips_table}.origin={metrics_table}.origin AND

select +='WHERE \n\t{}.origin={}.origin AND \n'.format( trips_table, metrics_table)

# {metrics_table}.dest={metrics_table}.dest)

select +='\t{}.dest={}.dest \n'.format(metrics_table, trips_table)

else:

#use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)

# WHERE {trips_table}.dest={metrics_table}.origin AND

select +='WHERE \n\t{}.dest={}.origin AND \n'.format( trips_table, metrics_table)

# {metrics_table}.origin={metrics_table}.dest)

select +='\t{}.origin={}.dest \n'.format(trips_table, metrics_table)

# ORDER BY {metrics_table}.origin, {metrics_table}.dest

select +='ORDER BY \n\t{}.origin, {}.dest\n\n'.format( metrics_table, metrics_table)

logger.debug('executing:\n' + select)

#some queries can't succeed because there are not tables to support them e.g., autodistance for wexpbus mode

good_table=True

try:

curs.execute(select)

except:

msg = '--Nevermind. NOT adding {} for {}. {} - {} leg - no table to support it\n'

logger.debug(msg.format(topic, purpose, mode, trip_leg))

good_table=False

break

if good_table:

res=np.array(curs.fetchall())

#add the results to the last column of the aggregation array

#grab the OD columns for the first result

if fresh_npa_array:

npa=res

fresh_npa_array=False

#add only the last column of subsequent ones

else:

npa[:,-1]+=res[:,-1]

logger.info('writing data for {} {} for {}: {} {} {}'.format(pk_flag, topic, scenario, income, purpose, mode))

logger.debug('executing {}\n'.format(select))

income=None,

purposes=purposes,

purposes_round_trip=purposes_round_trip,

bus_modes=bus_modes,

rail_modes=rail_modes,

np_rows=NP_ROWS,

topic=None):

"""Aggregate time costs for mass transit. Roll up topics over purpose and mode.

Cf aggregate_bus_rail_fares() for more verbose documentation."""

"""Keeps topics (initialwaittime, bustime, etc.) separate for now. For final analysis it may makes sense to consolodate

waiting: initialwaittime, transfertime

bus time: wexpbus, dexpbus, wbus, dbus

train time: wrail, wcrail, drail, dcrail

... but it's easier to combine later than have to separate."""

"""Typical SELECTS

--adding xferwaittime for hbw. wbus - outbound leg

SELECT DISTINCT

test_yesred_nohwy_transit_od_timecost.origin,

test_yesred_nohwy_transit_od_timecost.dest,

test_yesred_nohwy_mode_choice_od.hbw_inc1_wbus * test_yesred_nohwy_transit_od_timecost.pk_wbus_xferwaittime

FROM

test_yesred_nohwy_mode_choice_od , test_yesred_nohwy_transit_od_timecost

WHERE

test_yesred_nohwy_mode_choice_od.origin=test_yesred_nohwy_transit_od_timecost.origin AND

test_yesred_nohwy_transit_od_timecost.dest=test_yesred_nohwy_mode_choice_od.dest

ORDER BY

test_yesred_nohwy_transit_od_timecost.origin, test_yesred_nohwy_transit_od_timecost.dest

--adding xferwaittime for hbw. wbus - return leg

SELECT DISTINCT

test_yesred_nohwy_transit_od_timecost.origin,

test_yesred_nohwy_transit_od_timecost.dest,

test_yesred_nohwy_mode_choice_od.hbw_inc1_wbus * test_yesred_nohwy_transit_od_timecost.pk_wbus_xferwaittime

--adding xferwaittime for hbw. wbus - return leg

SELECT DISTINCT

test_yesred_nohwy_transit_od_timecost.origin,

test_yesred_nohwy_transit_od_timecost.dest,

test_yesred_nohwy_mode_choice_od.hbw_inc1_wbus * test_yesred_nohwy_transit_od_timecost.pk_wbus_xferwaittime

FROM

test_yesred_nohwy_mode_choice_od , test_yesred_nohwy_transit_od_timecost

WHERE

test_yesred_nohwy_mode_choice_od.dest=test_yesred_nohwy_transit_od_timecost.origin AND

test_yesred_nohwy_mode_choice_od.origin=test_yesred_nohwy_transit_od_timecost.dest

ORDER BY

test_yesred_nohwy_transit_od_timecost.origin, test_yesred_nohwy_transit_od_timecost.dest

"""

#general info for this metric

trips_table = '{}mode_choice_od'.format(scenario)

metrics_table= '{}transit_od_timecost'.format(scenario)

logger.info('Beginning aggregation of {} data'.format(topic))

#initialize null np array

npa=np.zeros(((np_rows-1)**2, 3)) #orig, dest, value

fresh_npa_array=True

for purpose in purposes:

#peak or off peak as f(purpose)

pk_flag=purpose_peak_flag[purpose]

#round trip of one-way (round trip for home based journeys)?

trip_legs=['outbound']

if purpose in purposes_round_trip:

trip_legs.append('return')

#calculate each leg of the trip separately

for trip_leg in trip_legs:

#loop thru appropriate modes (no fares w/ auto) and compose SELECT

for mode in bus_modes+rail_modes:

# --adding {topic} for {purpose}. {mode} - {trip_leg} leg\n'

select= '--adding {} for {}. {} - {} leg\n'.format(topic, purpose, mode, trip_leg)

# SELECT DISTINCT

select += 'SELECT DISTINCT\n '

# {metrics_table}.origin

select += '\t{}.origin,\n'.format(metrics_table)

# {metrics_table}.dest

select += '\t{}.dest,\n'.format(metrics_table)

# '{trips_table}.{purpose}_{income}_{mode} * {metrics_table}.{pk_flag}_{mode}_{topic} '

stmt= '\t{}.{}_{}_{} * {}.{}_{}_{}\n '

select+= stmt.format(trips_table, purpose, income, mode, metrics_table, pk_flag, mode, topic)

#print(select)

# FROM {trips_table} , {metrics_table}

select += 'FROM \n\t {} , {} \n '.format( trips_table, metrics_table)

if trip_leg== 'outbound':

#use OD pairs from trip table same as metric table's

# WHERE {trips_table}.origin={metrics_table}.origin AND

select +='WHERE \n\t{}.origin={}.origin AND \n'.format( trips_table, metrics_table)

# {metrics_table}.dest={metrics_table}.dest)

select +='\t{}.dest={}.dest \n'.format(metrics_table, trips_table)

else:

#use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)

# WHERE {trips_table}.dest={metrics_table}.origin AND

select +='WHERE \n\t{}.dest={}.origin AND \n'.format( trips_table, metrics_table)

# {metrics_table}.origin={metrics_table}.dest)

select +='\t{}.origin={}.dest \n'.format(trips_table, metrics_table)

# ORDER BY {metrics_table}.origin, {metrics_table}.dest

select +='ORDER BY \n\t{}.origin, {}.dest\n\n'.format( metrics_table, metrics_table)

logger.debug('executing:\n' +select)

#some queries can't succeed because there are not tables to support them e.g., autodistance for wexpbus mode

good_table=True

try:

curs.execute(select)

except:

msg = '--Nevermind. NOT adding {} for {}. {} - {} leg - no table to support it\n'

logger.debug(msg.format(topic, purpose, mode, trip_leg))

good_table=False

break

if good_table:

res=np.array(curs.fetchall())

#add the results to the last column of the aggregation array

#grab the OD columns for the first result

if fresh_npa_array:

npa=res

fresh_npa_array=False

#add only the last column of subsequent ones

else:

npa[:,-1]+=res[:,-1]

logger.info('writing data for {} {} for {}: {} {} {}'.format(pk_flag, topic, scenario, income, purpose, mode))

logger.debug('executing {}\n'.format(select))

a=1

income=None,

purposes=purposes,

purposes_round_trip=purposes_round_trip,

bus_modes=bus_modes,

rail_modes=rail_modes,

np_rows=NP_ROWS,

topic=None,

occupancy=None):

"""Aggregate costs for highway-only travel. Roll up topics over purpose, tod.

Keeps occupancies (sov, hov2, hov3) and costs (time, distance, toll) separate"""

"""Typical SELECTS

"""

"""

# hwy_toll_hov_am

# hbo_inc1_md_hov3"""

#general info for this metric

trips_table = '{}trips_purpose_income_tod_occ'.format(scenario)

metrics_table= '{}loaded_hwy_od_timecost'.format(scenario)

logger.info('Beginning aggregation of {} data'.format(topic))

#initialize null np array

npa=np.zeros(((np_rows-1)**2, 3)) #orig, dest, value

fresh_npa_array=True

for purpose in purposes:

#peak or off peak as f(purpose)

#pk_flag=purpose_peak_flag[purpose]

#round trip of one-way (round trip for home based journeys)?

trip_legs=['outbound']

if purpose in purposes_round_trip:

trip_legs.append('return')

#calculate each leg of the trip separately

for trip_leg in trip_legs:

#loop thru appropriate modes and compose SELECT

for mode in drive_modes:

for tod in tod_hwy_loaded:

# --adding {topic} for {purpose} {mode} {occupancy}- {trip_leg} leg\n'

select= '--adding {} for {} {} {} - {} leg\n'.format(topic, purpose, mode, occupancy, trip_leg)

# SELECT DISTINCT

select += 'SELECT DISTINCT\n '

# {metrics_table}.origin

select += '\t{}.origin,\n'.format(metrics_table)

# {metrics_table}.dest

select += '\t{}.dest,\n'.format(metrics_table)

if occupancy == 'hov2' or occupancy=='hov3':

occ_for_loaded = 'hov'

else:

occ_for_loaded='sov'

# '{trips_table}.{purpose}_{income}_{tod}_{occ} * {metrics_table}.hwy_{topic}_{occupancy}_{tod} '

stmt= '\t{}.{}_{}_{}_{} * {}.hwy_{}_{}_{}\n '

select+= stmt.format(trips_table, purpose, income, tod, occupancy, \

metrics_table, topic, occ_for_loaded, tod)

# FROM {trips_table} , {metrics_table}

select += 'FROM \n\t {} , {} \n '.format( trips_table, metrics_table)

if trip_leg== 'outbound':

#use OD pairs from trip table same as metric table's

# WHERE {trips_table}.origin={metrics_table}.origin AND

select +='WHERE \n\t{}.origin={}.origin AND \n'.format( trips_table, metrics_table)

# {metrics_table}.dest={metrics_table}.dest)

select +='\t{}.dest={}.dest \n'.format(metrics_table, trips_table)

else:

#use transposed OD pairs from trip table (origin = metrics.dest, dest=metrics.origin)

# WHERE {trips_table}.dest={metrics_table}.origin AND

select +='WHERE \n\t{}.dest={}.origin AND \n'.format( trips_table, metrics_table)

# {metrics_table}.origin={metrics_table}.dest)

select +='\t{}.origin={}.dest \n'.format(trips_table, metrics_table)

# ORDER BY {metrics_table}.origin, {metrics_table}.dest

select +='ORDER BY \n\t{}.origin, {}.dest\n\n'.format( metrics_table, metrics_table)

logger.debug('executing:\n' +select)

#some queries can't succeed because there are not tables to support them e.g., autodistance for wexpbus mode

good_table=True

try:

print(select)

curs.execute('END')

curs.execute(select)

conn.commit()

except :

exc_type, exc_value, exc_traceback = sys.exc_info()

msg = '--Nevermind. NOT adding {} for {}. {} - {} leg - no table to support it\n'

logger.debug(msg.format(topic, purpose, mode, trip_leg))

good_table=False

break

if good_table:

res=np.array(curs.fetchall())

#add the results to the last column of the aggregation array

#grab the OD columns for the first result

if fresh_npa_array:

npa=res

fresh_npa_array=False

#add only the last column of subsequent ones

else:

npa[:,-1]+=res[:,-1]

logger.info('writing data for {} {} for {}: {} {} {}'.format(occupancy, topic, scenario, income, purpose, mode))

logger.debug('executing {}\n'.format(select))

"adds newly-acquired data column to the master array"

if include_od_cols:

#if we're going to add OD columns, that implies a new master, so override initialization

master_npa=np.zeros((len(npa), NP_COLS))

#load all three columns

for col in range(0, 3):

master_npa[:,col]=npa[:,col]

else:

#just load the values

master_npa[:,master_col]+=npa[:,-1]

for scenario in scenarios:

scenario=prefix + scenario+'_'

for income in incomes:

#initialize np array

master_npa=np.zeros(((NP_ROWS-1)**2, NP_COLS))

master_col =0 #numpy column index

master_headers=['origin', 'dest']

#************** bus/rail fares **************#

#add orig, dest columns to master_npa, since this is the first one loaded

col_name = 'bus_rail_fares' #column name in master_npa array

routine=aggregate_bus_rail_fares #the method run to process this

#add orig, dest columns to master_npa, since this is the first one loaded

master_npa = add_to_master( master_npa=master_npa,

npa=routine(scenario=scenario, income=income),

master_col=master_col,

include_od_cols=True)

master_headers.append(col_name)

master_col +=3 #accounts for addition of od cols

logger.info('done rolling up {} {} {}'.format(scenario, income, col_name))

#**************bus/ time time, distance**************#

#create an aggregate across all puropses, times,

routine=aggregate_transit_metrics

for metric in transit_metrics: #walktime, etc.

col_name=metric

master_npa = add_to_master( master_npa=master_npa,

npa=routine(scenario=scenario, income=income, topic = metric),

master_col=master_col,

include_od_cols=False

)

master_headers.append(col_name)

master_col +=1

logger.info('done rolling up {} {} {}'.format(scenario, income, col_name))

#**************highway distance/time**************#

#create an aggregate across all puropses, times,

routine=aggregate_hwy_costs

for metric in metrics: #toll, distance, time

for occupancy in occupancy_hwy_tod: #sov, hov2, hov3

col_name=metric+ '_' +occupancy

master_npa = add_to_master( master_npa=master_npa,

npa=routine(scenario=scenario, income=income, topic = metric, occupancy=occupancy),

master_col=master_col,

include_od_cols=False

)

master_headers.append(col_name)

master_col +=1

logger.info('done rolling up {} {} {}'.format(scenario, income, col_name))

db_table_name='aggregated_{}_{}' .format(scenario, income)

create_db_table(tname=db_table_name, scenario=scenario, income=income,

data=master_npa, master_headers=master_headers)

logger.debug('Success in creating aggregate db table for {} {}'.format(scenario, income))

"creates a new table for this income group and scenario"

curs.execute('END')

sql = 'DROP TABLE IF EXISTS {}'.format(tname)

curs.execute(sql)

logger.debug('dropping table {}'.format(tname))

sql='CREATE TABLE IF NOT EXISTS {} ('.format(tname)

for col in master_headers:

sql+=' {} float,'.format(str(col))

sql = sql[:-1] + ');' #

print(sql)

curs.execute(sql)

conn.commit()

##TODO: should be a clever way simply to load the array (alt: dump file and load; also could just do INSERTs)

#for line in data:

#sql="INSERT INTO {} (".format(tname)

#for col in master_headers:

#sql+=' {}, '.format(col)

#sql=sql[:-2] + ") VALUES ("

#for value in line.split(','):

#sql+=' {},'.format(value)

#sql=sql[:-2] + ") "

#print(sql)

#curs.execute(sql)