def do_rollup_hwy_metrics( base=None, scenario=None, occupancy=None, income=None, master_npa=None, master_header=None, master_col=None, purposes=None, metric=None, include_od_columns=False, ): "rolls up the atomistic improvements in hwy metrics, adds them to master_npa array" routine = rollup_hwy_metrics col_name = metric + "_" + occupancy npa = routine(scenario=scenario, income=income, topic=metric, occupancy=occupancy) master_npa = add_to_master(master_npa=master_npa, npa=npa, master_col=master_col, include_od_cols=False) master_header.append(col_name) if include_od_columns: master_col += 3 # accounts for addition of od cols else: master_col += 1 logger.info("done rolling up hwy metrics {} {} {}".format(scenario, income, metric)) return (master_header, master_npa, master_col)
def do_rollup_transit_metrics( base=None, scenario=None, income=None, master_npa=None, master_col=None, master_headers=None, purposes=None, metric=None, name=None, include_od_columns=False, ): "rolls up the atomistic improvements in transit metrics, adds them to master_npa array" routine = rollup_transit_metrics col_name = name + "_" + metric + "_" + income npa = routine(scenario=scenario, base_scenario=base, income=income, topic=metric, purposes=purposes) master_npa = add_to_master(master_npa=master_npa, npa=npa, master_col=master_col, include_od_cols=False) master_headers.append(col_name) if include_od_columns: master_col += 3 # accounts for addition of od cols else: master_col += 1 logger.info("done rolling up transit metrics {} {} {}".format(scenario, income, name)) return (master_headers, master_npa, master_col)
def do_rollup_bus_rail_fares( base=None, scenario=None, income=None, master_npa=None, master_col=None, master_headers=None, include_od_columns=False, ): "rolls up the atomistic imporvements from fare changes" routine = rollup_bus_rail_fares col_name = "bus_rail_fares_{}".format(income) # column name in master_npa array routine = rollup_bus_rail_fares # the method run to process this master_npa = add_to_master( master_npa=master_npa, npa=routine(scenario=scenario, base_scenario=base, income=income), master_col=master_col, include_od_cols=include_od_columns, ) master_headers.append(col_name) if include_od_columns: master_col += 3 # accounts for addition of od cols else: master_col += 1 logger.info("done rolling up {} {} {}".format(scenario, income, col_name)) return (master_headers, master_npa, master_col)
def check_checksum_correctness_for_file(file_info_items): """Calculates checksum for the file from the files list (if the file is present in the directory) and defines if the checksum from the text file is the same as the calculated value by creating a log record. Args: file_info_items: a list with file name, algorithm type and checksum to be compared with the calculated one """ file_name, algorithm, checksum = file_info_items if os.path.isfile(os.path.join(dir_path, file_name)): calc_checksum = get_checksum(algorithm, os.path.join(dir_path, file_name)) if calc_checksum == checksum: logger.info('{} OK'.format(file_name)) else: logger.info('{} FAIL'.format(file_name)) else: logger.info('{} NOT FOUND'.format(file_name))
HTTP_SERVER = { 'ADDRESS': get_env('HTTP_SERVER', 'ADDRESS'), } SERVER_SETTINGS = { 'SERVER_PORT': int(get_env('SERVER_SETTINGS', 'SERVER_PORT', 8080)), 'SERVER_IP': get_env('SERVER_SETTINGS', 'SERVER_IP', "0.0.0.0"), 'WEB_BASE': get_env('SERVER_SETTINGS', 'WEB_BASE', "/api/v1"), 'WEB_SCHEME': get_env('SERVER_SETTINGS', 'WEB_SCHEME', "http") } APPLICATION_SETTINGS = { 'VERSION': VERSION, 'BASE_PATH': os.getcwd(), } LOG = {'LEVEL': get_env('LOG', 'LEVEL', 'INFO')} # check sanity and set appropriate logging level if LOG['LEVEL'] not in logging._levelToName.values(): LOG['LEVEL'] = 'INFO' logger.info("Setting LOG_LEVEL to {}.".format(LOG['LEVEL'])) logger.setLevel(LOG['LEVEL']) logger.debug("APPLICATION_SETTINGS: " + json.dumps(APPLICATION_SETTINGS)) logger.debug("HTTP_SERVER: " + json.dumps(HTTP_SERVER)) logger.debug("DATABASE: " + json.dumps(DATABASE)) logger.debug("SERVER_SETTINGS: " + json.dumps(SERVER_SETTINGS)) logger.debug("LOG: " + json.dumps(LOG))
from scripts import load_swagger_yaml, uuid_filter from handlers import ( by_uuid, index, drop_collection, get_total_users, by_email, post_user, post_users, update_user, delete_user, serve_static, ) logger.info({"Message": "Initializing Bottle..."}) # --- Configuration ---------------------------------------------------- app = application = Bottle() app.catchall = False wb = SERVER_SETTINGS['WEB_BASE'] app.router.add_filter("uuid", uuid_filter) swagger_from_yaml = load_swagger_yaml() app.config["api.swagger_spec"] = swagger_from_yaml def setup_routing(app): app.route(wb + "/get_user_by_uuid/<user_uuid>", ['GET'], by_uuid) app.route(wb + "/get_user_by_email/<user_email>", ['GET'], by_email) app.route(wb + "/get_total_users", ['GET'], get_total_users) app.route(wb + "/home", ['GET'], index) app.route(wb + "/post_user", ['POST'], post_user)
def aggregate_bus_rail_fares(scenario=None, 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)) return npa
def make_rollup_tables(): 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)) a=1
def aggregate_hwy_costs(scenario=None, 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)) return npa
def aggregate_transit_metrics(scenario=None, 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 return npa
def rollup_hwy_metrics( scenario=None, income=None, purposes=purposes, master_header=None, purposes_round_trip=purposes_round_trip, 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 base_trips_table = "{}trips_purpose_income_tod_occ".format(base_scenario) test_trips_table = "{}trips_purpose_income_tod_occ".format(scenario) base_metrics_table = "{}loaded_hwy_od_timecost".format(base_scenario) test_metrics_table = "{}loaded_hwy_od_timecost".format(scenario) # We'll need to leverage the time to reflect vehicle occupancy. Since the topic (metric) is an # input parameter, we can calculate a uniform multiplier here. Occupancy leverages only time. if topic == "time": mult = time_adjust[occupancy] else: mult = 1 cols_added = 5 # metric base, trips base, metric trial, trips trial, benefit logger.info("\n\n\n***** Beginning aggregation of highway data for {}".format(topic)) logger.info("Trips using {}\n and {}".format(base_trips_table, test_trips_table)) logger.info("Costs using {}\n and {}".format(base_metrics_table, test_metrics_table)) # this array will aggreate the info gleaned here export_array = np.zeros(((np_rows - 1) ** 2, 3)) this_export_array_col = -1 # initialize null np array npa = np.zeros(((np_rows - 1) ** 2, 3)) # orig, dest, value fresh_npa_array = True logger.info("Beginning aggregation of {} data".format(topic)) # we're passing in topic (metric), occupancy and income, purpose (grouped by business/personal as input, analyzed # atomisticly here and provided as aggregate as output values) for purpose in purposes: # rolls up whatever list of purposes provided (allows biz/personal segregation) # round trip of one-way (round trip for home based journeys)? trip_legs = ["outbound"] if purpose in purposes_round_trip: trip_legs.append("return") for tod in tod_hwy_loaded: #'am', 'pm', 'md', 'nt' logger.info("beginning benefit calcs for {} {} {} {}".format(purpose, tod, occupancy, topic)) need_npa_combined = True # holds outbound+return benefits rollup # flag for npa creation this_np_col = -1 # calculate benefits for each leg of the trip separately; combine the benefits from a round-trip at the end # of the 'trip_leg' loop. for trip_leg in trip_legs: if this_np_col < 0: npa = np.zeros(((np_rows - 1) ** 2, cols_added + 2)) # scratch array # this selects from the base and trial case tables for metrics_table, trips_table, name in zip( [base_metrics_table, test_metrics_table], [base_trips_table, test_trips_table], ["base", scenario] ): # --adding {topic} for {purpose} {tod} {occupancy}- {trip_leg} leg\n' select = "--adding {} for {} {} {} - {} leg\n".format(topic, purpose, tod, occupancy, trip_leg) 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}_{tod}_{occ} * {metrics_table}.hwy_{topic}_{occupancy}_{tod} * {mult}' stmt = "\t{}.{}_{}_{}_{} * {}.hwy_{}_{}_{} * mult \n " # mult leverages time for HOVs select += stmt.format( trips_table, purpose, income, tod, occupancy, metrics_table, topic, occupancy, tod ) # '{trips_table}.{purpose}_{income}_{tod}_{occ} ' stmt = "\t{}.{}_{}_{}_{}" select += stmt.format(trips_table, purpose, income, tod, occupancy) # 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(select) try: good_table = True curs.execute(select) except: # some empty columns were not produced (e.g., wexpbus_autodistance) because they don't apply relations = [ "\t{}.{}_{}_{}_{} * {}.hwy_{}_{}_{} * mult \n ".format( trips_table, purpose, income, tod, occupancy, metrics_table, topic, occupancy, tod ), "\t{}.{}_{}_{}".format(trips_table, purpose, income, tod), ] logger.warning( "This SELECT failed, probably because the data is n/a: {}".format(" ".join(relations)) ) good_table = False # close out any curs.execute("END") # if the query failed, we've busted out; so go to the next tod if good_table: res = np.array(curs.fetchall()) # This rolls up the costs and trips from both scenarios: npa is rows of: origin, dest, benefit base, trips base, benefit trial, trips trial if this_np_col < 0: # add first 4 columns of result to a the first 4 columns of the scratch array (origin, dest, base cost, base trips) npa[:, :4] = res this_np_col = 4 else: # add the cost, trips columns from the result to cols 4-6 of the scratch array (trial cost, trial trips) npa[:, 4:6] = res[:, -2:] this_np_col += 2 # calculate the benefits logger.info("calculating delta cs for {} {} {} ".format(scenario, purpose, mode)) npa = add_dlta_cons_surplus(npa) # npa_combined rolls up the atomized benefits, calculated separately for each leg of the journey. if need_npa_combined: npa_combined = npa need_npa_combined = False logger.info("adding benefits to new npa_combined array") else: # otherwise add the benefits from the second leg (the last column) to the combined_npa array npa_combined[:, -1] += npa[:, -1] logger.info( "done with both legs; adding return leg to npa_combined: {} {} ".format( purpose, mode ) ) # next mode at col 12 # if a mode fails to produce a clean query, don't bother trying to add the info if good_table: if this_export_array_col < 0: # not yet created; add the orig and destin columns, along with the cs deltas export_array[:, :2] = npa_combined[:, :2] export_array[:, -1] = npa_combined[:, -1] this_export_array_col = 3 logger.debug("creating new export array") else: # ... otherwise just add the new benfits to the cumulative total export_array[:, -1] += npa_combined[:, -1] logger.info("adding additional cs deltas to export array") ### logger.info("Done with mode {}\n\n".format(mode)) logger.info("Done with purpose {}".format(purpose)) return export_array
def rollup_bus_rail_fares( scenario=None, base_scenario=None, 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. --finding bus_rail_fares for hbw. wexpbus - outbound leg SELECT DISTINCT test_fares_fares.origin, test_fares_fares.dest, test_nored_nohwy_mode_choice_od.hbw_inc1_wexpbus * test_fares_fares.wexpbus_pk, test_nored_nohwy_mode_choice_od.hbw_inc1_wexpbus FROM test_nored_nohwy_mode_choice_od , test_fares_fares WHERE test_nored_nohwy_mode_choice_od.origin=test_fares_fares.origin AND test_fares_fares.dest=test_nored_nohwy_mode_choice_od.dest ORDER BY test_fares_fares.origin, test_fares_fares.dest """ # general info for this metric topic = "bus_rail_fares" base_trips_table = "{}_mode_choice_od".format(base_scenario) test_trips_table = "{}_mode_choice_od".format(scenario) base_metrics_table = "{}fares_fares".format(prefix) test_metrics_table = "{}fares_fares".format(prefix) cols_added = 5 # metric base, trips base, metric trial, trips trial, benefit logger.info("Beginning aggregation of {} fares data".format(topic)) logger.info("Trips using {}\n and {}".format(base_trips_table, test_trips_table)) logger.info("Costs using {}\n and {}".format(base_metrics_table, test_metrics_table)) export_array = np.zeros(((np_rows - 1) ** 2, 3)) this_export_array_col = -1 # loop over purposes and bus/rail modes, accumulating the atomistic consumer surplus changes, including # outbound + return for home-based trips for purpose in purposes: # peak or off peak as f(purpose) pk_flag = purpose_peak_flag[purpose] # round trip or one-way? (round trip for home based journeys) trip_legs = ["outbound"] if purpose in purposes_round_trip: trip_legs.append("return") # loop over all trips for both bus and rail modes for mode in bus_modes + rail_modes: logger.info("beginning benefit calcs for {} {} mode".format(purpose, mode)) # calculate benefits for each leg of the trip separately; combine the benefits from a round-trip at the end # of the 'trip_leg' loop. need_npa_combined = True # holds outbound+return benefits rollup # flag for npa creation this_np_col = -1 # construct the similar SELECT for both base and test scenarios (different tables), store results in np array 'npa', # calculate separately the benefits for each leg then roll the benefits up in np array 'combined_npa' # for each leg of the trip do a base versus trial case benefits calculation for trip_leg in trip_legs: if this_np_col < 0: npa = np.zeros(((np_rows - 1) ** 2, cols_added + 2)) # scratch array # this selects trips and costs from the base and trial case tables for metrics_table, trips_table, name in zip( [base_metrics_table, test_metrics_table], [base_trips_table, test_trips_table], ["base", scenario] ): logger.info("running {} case for {} {} {}".format(name, purpose, mode, trip_leg)) # --finding {topic} for {purpose}. {mode} - {trip_leg} leg\n' select = "--finding {} for {}. {} - {} leg - scenario: {}\n".format( topic, purpose, mode, trip_leg, name ) 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) # '{trips_table}.{purpose}_{income}_{mode} ' stmt = "\t{}.{}_{}_{}\n " select += stmt.format(trips_table, purpose, income, mode) # 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(select) curs.execute(select) res = np.array(curs.fetchall()) # This rolls up the costs and trips from both scenarios: npa is rows of: origin, dest, benefit base, trips base, benefit trial, trips trial if this_np_col < 0: # add first 4 columns of result to a the first 4 columns of the scratch array (origin, dest, base cost, base trips) npa[:, :4] = res this_np_col = 4 else: # add the cost, trips columns from the result to cols 4-6 of the scratch array (trial cost, trial trips) npa[:, 4:6] = res[:, -2:] this_np_col += 2 # calculate the benefits logger.info("calculating delta cs for {} {} {} ".format(scenario, purpose, mode)) npa = add_dlta_cons_surplus(npa) # npa_combined rolls up the atomized benefits, calculated separately for each leg of the journey. if need_npa_combined: npa_combined = npa need_npa_combined = False logger.info("adding benefits to new npa_combined array") else: # otherwise add the benefits from the second leg (the last column) to the combined_npa array npa_combined[:, -1] += npa[:, -1] logger.info( "done with both legs; adding return leg to npa_combined: {} {} ".format(purpose, mode) ) # add consumer surplus improvements to the export array if this_export_array_col < 0: # not yet created; add the orig and destin columns, along with the cs deltas export_array[:, :2] = npa_combined[:, :2] export_array[:, -1] = npa_combined[:, -1] this_export_array_col = 3 logger.debug("creating new export array") else: # ... otherwise just add the new benfits to the cumulative total export_array[:, -1] += npa_combined[:, -1] logger.info("adding additional cs deltas to export array") logger.info("Done with mode {}\n\n".format(mode)) logger.info("Done with purpose {}".format(purpose)) return export_array
def xmake_rollup_tables(): """This rolls up atomisitc improvements in cs between the base scenario and alternatives. The result is a new db table for each scerario. Columns are aggregates of the cs improvments for individual categories e.g., bus/rail fares.""" # note: race demograhics anticipated to be in table race_race. Stored by origin only base = prefix + base_scenario # prefix is just a flag used for testing - null string in production for scenario in test_scenarios: scenario = prefix + scenario master_headers = ["origin", "dest"] for income in incomes: tname = scenario + "_" + income + "_benefits" # initialize np array master_npa = np.zeros(((NP_ROWS - 1) ** 2, NP_COLS)) master_col = 0 # numpy column index if True: # include for source code folding only # ************** bus/rail fares **************# # add orig, dest columns to master_npa, if this is the first one loaded ##TODO: consider changing the following for production include_od_columns = True col_name = "bus_rail_fares_{}".format(income) # column name in master_npa array routine = rollup_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, base_scenario=base, income=income), master_col=master_col, include_od_cols=include_od_columns, ) master_headers.append(col_name) if include_od_columns: master_col += 3 # accounts for addition of od cols else: master_col += 1 logger.info("done rolling up {} {} {}".format(scenario, income, col_name)) if True: # include for source code folding only # **************bus/ rail time, distance**************# # create an aggregate across all puropses, times, ##TODO: False for production include_od_columns = False routine = rollup_transit_metrics # adding purposes loop so we can track work-related ('obo') and personal for differential VOT for purposes, name in zip([purposes_biz, purposes_personal], ["business", "personal"]): for metric in transit_metrics: # walktime, etc. col_name = name + "_" + metric + "_" + income npa = routine( scenario=scenario, base_scenario=base, income=income, topic=metric, purposes=purposes ) master_npa = add_to_master( master_npa=master_npa, npa=npa, master_col=master_col, include_od_cols=False ) master_headers.append(col_name) if include_od_columns: master_col += 3 # accounts for addition of od cols else: master_col += 1 logger.info("done rolling up transit metrics {} {} {}".format(scenario, income, name)) if True: # **************highway distance/time**************# # create an aggregate across all puropses, times, ##TODO: False for production include_od_columns = False routine = rollup_hwy_metrics # adding purposes loop so we can track work-related ('obo') and personal for differential VOT for purposes, name in zip([purposes_biz, purposes_personal], ["business", "personal"]): for metric in metrics: # toll, distance, time for occupancy in occupancy_hwy_tod: # sov, hov2, hov3 col_name = metric + "_" + occupancy npa = routine(scenario=scenario, income=income, topic=metric, occupancy=occupancy) master_npa = add_to_master( master_npa=master_npa, npa=npa, master_col=master_col, include_od_cols=False ) master_headers.append(col_name) if include_od_columns: master_col += 3 # accounts for addition of od cols else: master_col += 1 logger.info("done rolling up hwy metrics {} {} {}".format(scenario, income, name)) # income done logger.info("done with income group {}".format(income)) # scenario done logger.info("done with scenario {}".format(scenario)) 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)) a = 1
def make_rollup_tables(): """This rolls up atomisitc improvements in cs between the base scenario and alternatives. The result is a new db table for each scerario. Columns are aggregates of the cs improvments for individual categories e.g., bus/rail fares.""" base = prefix + base_scenario # prefix is just a flag used for testing - null string in production for scenario in test_scenarios: scenario = prefix + scenario master_headers = ["origin", "dest"] # name of db table to save tname = scenario + "_benefits" for income in incomes: # initialize np array; will hold all results for this scenario master_npa = np.zeros(((NP_ROWS - 1) ** 2, NP_COLS)) master_col = 0 # numpy column index # roll up rail + bus fares by income group. # o_rollup_bus_rail_fares(base = None, scenario=None, income=None, master_npa=None,include_od_columns=False) master_header, master_npa, master_col = do_rollup_bus_rail_fares( base=base, scenario=scenario, income=income, master_npa=master_npa, master_col=master_col, include_od_columns=True, master_headers=master_headers, ) # transit metrics (distance, toll, time) are calculated separately by type of travel (personal, biz) and metric for purposes, name in zip([purposes_biz, purposes_personal], ["business", "personal"]): for metric in transit_metrics: # walktime, etc. master_headers, master_npa, master_col = do_rollup_transit_metrics( base=base, scenario=scenario, income=income, master_npa=master_npa, master_col=master_col, master_headers=master_headers, purposes=purposes, metric=metric, name=name, include_od_columns=False, ) # highway (auto-only) metrics are calculated separately by type of travel (personal, biz) , occupancy and metri for purposes, name in zip([purposes_biz, purposes_personal], ["business", "personal"]): for metric in metrics: # toll, distance, time for occupancy in occupancy_hwy_tod: # sov, hov2, hov3 master_header, master_npa, master_col = do_rollup_hwy_metrics( base=base, scenario=scenario, occupancy=occupancy, income=income, master_npa=master_npa, master_header=master_header, master_col=master_col, purposes=purposes, metric=metric, include_od_columns=False, ) # income done logger.info("done with income group {}".format(income)) # scenario done logger.info("done with scenario {}".format(scenario)) 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)) a = 1
def main(scenarios=scenarios, DB=DB, ROOT_DIR=ROOT_DIR, ZONES=ZONES, map_file=map_file): "main entry point - loops over scenarios" msg='{} Starting benefits calculations using input file {}' logger.info(msg.format(datetime.now().strftime("%b %d %Y %H:%M:%S"), map_file)) print(msg.format(datetime.now().strftime("%b %d %Y %H:%M:%S"), map_file)) #This isn't the most efficient way to do it, but it's the most transparent: we'll loop through each base:scenario pair. For each, we'll read # the input file a line at a time and draw our consumer surplus benefits base_scenario = scenarios[0] ###for s in scenarios[1:]: for s in scenarios[0:]: ###run this for the 'red line' case only arr_dict={} #grab a reader for the input file reader=csv.DictReader(open(map_file)) #process one line at a time from the setup file for line_ix, line in enumerate(reader, start=1): # the info comes in as a dict - this cleans up the content, removing comments, etc. Return a dict. dmap = grabinfo(line) #these set processing parameters transpose=dmap['transpose'] #use transposed trip matrix? hov_adj=dmap['hov_adj'] #occupancy adjustment (hov2 implies double time costs, say) pct_hb=dmap['pct_hb'] #fraction of benefits occuring to origin node ('home base') #these set storage parameters arr_name=dmap['aggregate_to'] column_name= arr_name table_name=s['name']+"_"+dmap['dbtable'] #get information for the base case base_dir=base_scenario['location'] #root directory location base_name=base_scenario['name'] #name for this scenari #Build fully specified path names built from locations in mappyings.py; subdirectory determined by file name # then create np arrays out of them base_cost_file=get_full_filename(location=base_dir, filename=dmap['cost_file']) base_trips_file=get_full_filename(location=base_dir, filename=dmap['trip_file']) #try to create npa arrays from the raw data files; if they don't exist go on to the next line try: base_trips_raw = npa_from_file( base_trips_file) base_cost_per_trip_raw = npa_from_file( base_cost_file) except: exc_type, exc_value, exc_traceback = sys.exc_info() msg='Scenario {}: could not open requisite files \n {} {} specified in line {} of {}' logger.warning(msg.format(s['name'], exc_type, exc_value, line_ix, map_file)) continue #Costs and trips for the base case - returns base costs, base trips as square np # arrays w/o OD headers, trips transposed if needed base_costs, base_trips=prep_data( base_cost_per_trip_raw , base_trips_raw, transpose, hov_adj ) ##Process the scenario costs and trips the same way #test_dir = s['location'] ##grab the files and put them in np arrays #try: #test_cost_file=get_full_filename(location=test_dir, filename=dmap['cost_file']) #test_trip_file=get_full_filename(location=test_dir, filename=dmap['trip_file']) #except: #msg='Scenario {}: could not open requisite files \n {} {} specified in line {} of {}' #logger.warning(msg.format(s['name'], exc_type, exc_value, line_ix, map_file)) #test_trips_raw = npa_from_file( test_trip_file) #test_cost_per_trip_raw = npa_from_file( test_cost_file) #test_name=s['name'] ##Scenario case trips*cost/trip and trips used #test_costs, test_trips=prep_data( cost_per_trip=test_cost_per_trip_raw , trips=test_trips_raw, transpose=transpose, hov_adj=hov_adj ) #With all costs gathered, calculate the change in consumer surplus in square np array; produces a square np array ###cs_delta = get_cs_delta(base_trips, test_trips, base_costs, test_costs) my_trips=get_base_trips_only(base_trips=base_trips) #From the cs_delta matrix, assign benefits to the origin and destination node; produces a vector of nodes w/o OD headers # For home-based transit trips, both outbound and return accrue to home node, as do am and pm highway trips. # For mid-day and night-time highway trips, the benefit is split between origin and dest nodes. ###benefits_by_zone = calculate_benefits(cs_delta, pct_hb) trips_by_zone=calculate_benefits(my_trips, pct_hb) #the balance of this block stores the benefits and other information for posterity/more analysis #We'll aggregate the cs_delta by benefit type, denominated in natural units (minutes, dollars, miles). We can use scalars to transform #minutes to dollars, miles to CO2, etc. as a post-processing step. #We'll create an aggregation array if requested in the 'aggregate to' column. This bit of code adds the array to the arr_dict if needed, # then creates a null np array if needed. It adds a column of current benefits_by_zone to the array if the array is null; it increments # the array by the benefits just calculated otherwise. This essentially creates a bundle of an array containing all the summary information # we've gleaned from a scenario and where to store it when we're done. #to hold the results, we'll make a dict arr_dict{'aggregate_to': {'data':npa_of_data, # 'column': 'aggregate_to', # 'table': 'db_table}} #... where the 'aggregate_to' value comes from the input spreadsheet and serves as the name of the database column. #create the dict if needed (it doesnt yet exist) if not arr_name in arr_dict: arr_dict[arr_name]={} arr_dict[arr_name]['data']=None #update the dict with current state of the roll-up array arr_dict[arr_name]={ 'data': sum_col_to_np_array(npa=arr_dict[arr_name]['data'], ###vector=benefits_by_zone, vector=trips_by_zone, max_index_val=len(base_trips)), 'column': column_name, 'table': table_name } logger.debug('line {}\n\t{} -versus- {} \n\t {} \n\t {} \n\t base trips: {} test trips: {} sum dlta cs: {} (summary stats - not used in benefit calcs)'.format( line_ix, base_name, 'NA', ###test_name, dmap['trip_file'], dmap['cost_file'].split()[0], np.sum(base_trips), 'NA', ###np.sum(test_trips), np.sum(trips_by_zone))) #store the arrays in db tables (written after all the processing for a scenario is completed.) store_data(arr_dict=arr_dict, db=DB) finish = datetime.now() msg='Finished at {}. Processed {} files in {}.' elapsed=str(finish-start).split('.')[0] print(msg.format(datetime.now().strftime("%b %d %Y %H:%M:%S"), line_ix, elapsed)) logger.info(msg.format(datetime.now().strftime("%b %d %Y %H:%M:%S"), line_ix, elapsed))
def store_data(arr_dict=None, db = DB, create_new_tables=CREATE_NEW_TABLES, zones=ZONES): """Stores data from all the benefits calculations made to date into a relational database. The arr_dict contains all the arrays to be stored, the db table, and the db column. This will create new tables if requested and take care of creating the necessary columns called for by the arr_dict information""" #Create a new db if needed. A bit convoluted in psql because you need to establish a new connection # to address a different db. (In MySQL, it's as simple as going: USE database;) if True: conn = psycopg2.connect( user=login_info['user'], password=login_info['password'] ) curs = conn.cursor() #if the database is not available, try to create it try: curs.execute('END') curs.execute('CREATE DATABASE {}'.format(db)) logger.debug('creating new database') except psycopg2.ProgrammingError: pass #datbase already exists #make a new connection to the right database conn.close() conn = psycopg2.connect(database = db, user=login_info['user'], password=login_info['password'] ) curs = conn.cursor() #kill existing tables if instructed if create_new_tables: for arr in arr_dict: #print('trying {}'.format(arr_dict[arr]['table'])) curs.execute("END") curs.execute('DROP TABLE IF EXISTS {}'.format(arr_dict[arr]['table'])) conn.commit() #print('done with {}'.format(arr_dict[arr]['table'])) #loop through our arrays dict, augmenting the database where needed for arr in arr_dict: #for readability, make pointers to elements of this dict entry arr_table=arr_dict[arr]['table'] arr_data=arr_dict[arr]['data'] arr_column=arr_dict[arr]['column'] logger.info("storing data for table {} column {}".format(arr_table, arr_column)) #create a table if we need to; populate it with the zone data try: curs.execute('END') curs.execute('SELECT * from {} LIMIT 1'.format(arr_table)) conn.commit() except: #table does not exist curs.execute('CREATE TABLE {} ({} float PRIMARY KEY )'.format(arr_table, 'zone', 'zone')) for row in range(1, zones + 1): sql="INSERT INTO {} ({}) VALUES ({})".format(arr_table, 'zone', row) #print(sql) curs.execute(sql) conn.commit() curs.execute("END") #create a data column if we need to; popluate it with array data try: print(arr_column) curs.execute("SELECT {} FROM {} LIMIT 1".format(arr_column, arr_table)) except: #col does not exist curs.execute('END') sql="ALTER TABLE {} ADD {} float DEFAULT 0.0".format(arr_table, arr_column) curs.execute(sql) #print(sql) for row in arr_data: sql="UPDATE {} SET {}={} WHERE {}={}".format(arr_table, arr_column, row[1], 'zone', row[0]) curs.execute(sql) #print(sql) z=1 conn.commit() a=1
def rollup_transit_metrics( scenario=None, base_scenario=None, income=None, purposes=purposes, master_col=None, master_header=None, 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 """ # general info for this metric scenario = scenario + "_" base_scenario = base_scenario + "_" base_trips_table = "{}mode_choice_od".format(base_scenario) test_trips_table = "{}mode_choice_od".format(scenario) base_metrics_table = "{}transit_od_timecost".format(base_scenario) test_metrics_table = "{}transit_od_timecost".format(scenario) cols_added = 5 # metric base, trips base, metric trial, trips trial, benefit logger.info("\n\n\n***** Beginning aggregation of transit data for {}".format(topic)) logger.info("Trips using {}\n and {}".format(base_trips_table, test_trips_table)) logger.info("Costs using {}\n and {}".format(base_metrics_table, test_metrics_table)) # this array will aggreate the info gleaned here export_array = np.zeros(((np_rows - 1) ** 2, 3)) this_export_array_col = -1 # 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") # loop thru appropriate modes and compose SELECT for mode in bus_modes + rail_modes: logger.info("beginning benefit calcs for {} {} {}".format(purpose, mode, topic)) # calculate benefits for each leg of the trip separately; combine the benefits from a round-trip at the end # of the 'trip_leg' loop. need_npa_combined = True # holds outbound+return benefits rollup # flag for npa creation this_np_col = -1 # calculate each leg of the trip separately for trip_leg in trip_legs: if this_np_col < 0: npa = np.zeros(((np_rows - 1) ** 2, cols_added + 2)) # scratch array # this selects from the base and trial case tables for metrics_table, trips_table, name in zip( [base_metrics_table, test_metrics_table], [base_trips_table, test_trips_table], ["base", scenario] ): logger.info( "running {} case for {} {} {} using {} and {}".format( name, purpose, mode, trip_leg, trips_table, metrics_table ) ) # create SELECT statements # --adding {topic} for {purpose}. {mode} - {trip_leg} leg\n' select = "--adding {} for {}. {} - {} leg\n".format(topic, purpose, mode, trip_leg) select += "SELECT DISTINCT\n " # {metrics_table}.origin origin select += "\t{}.origin,\n".format(metrics_table) # {metrics_table}.dest destination select += "\t{}.dest,\n".format(metrics_table) # '{trips_table}.{purpose}_{income}_{mode} * {metrics_table}.{pk_flag}_{mode}_{topic} ' metric stmt = "\t{}.{}_{}_{} * {}.{}_{}_{},\n " select += stmt.format(trips_table, purpose, income, mode, metrics_table, pk_flag, mode, topic) # '{trips_table}.{purpose}_{income}_{mode} ' trips stmt = "\t{}.{}_{}_{}\n " select += stmt.format(trips_table, purpose, income, mode) # 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(select) try: good_table = True curs.execute(select) except: # some empty columns were not produced (e.g., wexpbus_autodistance) because they don't apply relations = [ "\t{}.{}_{}_{} * {}.{}_{}_{},\n ".format( trips_table, purpose, income, mode, metrics_table, pk_flag, mode, topic ), "\t{}.{}_{}_{}\n ".format(trips_table, purpose, income, mode), ] logger.warning( "This SELECT failed, probably because the data is n/a: {}".format(" ".join(relations)) ) good_table = False # close out any curs.execute("END") # if the query failed, we've busted out; so go to the next mode if good_table: res = np.array(curs.fetchall()) # This rolls up the costs and trips from both scenarios: npa is rows of: origin, dest, benefit base, trips base, benefit trial, trips trial if this_np_col < 0: # add first 4 columns of result to a the first 4 columns of the scratch array (origin, dest, base cost, base trips) npa[:, :4] = res this_np_col = 4 else: # add the cost, trips columns from the result to cols 4-6 of the scratch array (trial cost, trial trips) npa[:, 4:6] = res[:, -2:] this_np_col += 2 # calculate the benefits logger.info("calculating delta cs for {} {} {} ".format(scenario, purpose, mode)) npa = add_dlta_cons_surplus(npa) # npa_combined rolls up the atomized benefits, calculated separately for each leg of the journey. if need_npa_combined: npa_combined = npa need_npa_combined = False logger.info("adding benefits to new npa_combined array") else: # otherwise add the benefits from the second leg (the last column) to the combined_npa array npa_combined[:, -1] += npa[:, -1] logger.info( "done with both legs; adding return leg to npa_combined: {} {} ".format( purpose, mode ) ) # next mode at col 12 # if a mode fails to produce a clean query, don't bother trying to add the info if good_table: if this_export_array_col < 0: # not yet created; add the orig and destin columns, along with the cs deltas export_array[:, :2] = npa_combined[:, :2] export_array[:, -1] = npa_combined[:, -1] this_export_array_col = 3 logger.debug("creating new export array") else: # ... otherwise just add the new benfits to the cumulative total export_array[:, -1] += npa_combined[:, -1] logger.info("adding additional cs deltas to export array") ### logger.info("Done with mode {}\n\n".format(mode)) logger.info("Done with purpose {}".format(purpose)) return export_array