def parcel_is_allowed(form): form_to_btype = sim.get_injectable("form_to_btype") # we have zoning by building type but want # to know if specific forms are allowed allowed = [sim.get_table('zoning_baseline') ['type%d' % typ] == 't' for typ in form_to_btype[form]] return pd.concat(allowed, axis=1).max(axis=1).\ reindex(sim.get_table('parcels').index).fillna(False)
def parcel_is_allowed(form): form_to_btype = sim.get_injectable("form_to_btype") # we have zoning by building type but want # to know if specific forms are allowed allowed = [sim.get_table('zoning_baseline') ['type%d' % typ] == 't' for typ in form_to_btype[form]] return pd.concat(allowed, axis=1).max(axis=1).\ reindex(sim.get_table('parcels').index).fillna(False)
def pecas_price(buildings, pecas_prices): buildings = buildings.to_frame(columns = ['development_type_id', 'luz_id']) pecas_prices = pecas_prices.to_frame() year = sim.get_injectable('year') if year is None: year = 2012 pecas_prices = pecas_prices[pecas_prices.year == year] merged = pd.merge(buildings.reset_index(), pecas_prices, left_on = ['luz_id', 'development_type_id'], right_on = ['luz_id', 'development_type_id']).set_index('building_id') return pd.Series(data = merged.price, index = buildings.index).fillna(0)
def pecas_price(costar, pecas_prices): costar = costar.to_frame(columns = ['development_type_id', 'luz_id']) pecas_prices = pecas_prices.to_frame() year = sim.get_injectable('year') if year is None: year = 2012 pecas_prices = pecas_prices[pecas_prices.year == year] costar.index.name = 'costar_id' merged = pd.merge(costar.reset_index(), pecas_prices, left_on = ['luz_id', 'development_type_id'], right_on = ['luz_id', 'development_type_id']).set_index('costar_id') return pd.Series(data = merged.price, index = costar.index).fillna(0)
def conditional_upzone(scenario, attr_name, upzone_name): scenario_inputs = sim.get_injectable("scenario_inputs") zoning_baseline = sim.get_table( scenario_inputs["baseline"]["zoning_table_name"]) attr = zoning_baseline[attr_name] if scenario != "baseline": zoning_scenario = sim.get_table( scenario_inputs[scenario]["zoning_table_name"]) upzone = zoning_scenario[upzone_name].dropna() attr = pd.concat([attr, upzone], axis=1).max(skipna=True, axis=1) return attr
def conditional_upzone(scenario, attr_name, upzone_name): scenario_inputs = sim.get_injectable("scenario_inputs") zoning_baseline = sim.get_table( scenario_inputs["baseline"]["zoning_table_name"]) attr = zoning_baseline[attr_name] if scenario != "baseline": zoning_scenario = sim.get_table( scenario_inputs[scenario]["zoning_table_name"]) upzone = zoning_scenario[upzone_name].dropna() attr = pd.concat([attr, upzone], axis=1).max(skipna=True, axis=1) return attr
def job_spaces(): store = sim.get_injectable('store') b = sim.get_table('buildings').to_frame(['luz_id', 'development_type_id','non_residential_sqft']) bsqft_job = store['building_sqft_per_job'] merged = pd.merge(b.reset_index(), bsqft_job, left_on = ['luz_id', 'development_type_id'], right_on = ['luz_id', 'development_type_id']) merged = merged.set_index('building_id') merged.sqft_per_emp[merged.sqft_per_emp < 40] = 40 merged['job_spaces'] = np.ceil(merged.non_residential_sqft / merged.sqft_per_emp) job_spaces = pd.Series(merged.job_spaces, index = b.index) b['job_spaces'] = job_spaces b.job_spaces[b.job_spaces.isnull()] = np.ceil(b.non_residential_sqft/200.0) return b.job_spaces
def pecas_price(buildings, pecas_prices): buildings = buildings.to_frame(columns=['development_type_id', 'luz_id']) pecas_prices = pecas_prices.to_frame() year = sim.get_injectable('year') if year is None: year = 2012 pecas_prices = pecas_prices[pecas_prices.year == year] merged = pd.merge(buildings.reset_index(), pecas_prices, left_on=['luz_id', 'development_type_id'], right_on=['luz_id', 'development_type_id' ]).set_index('building_id') return pd.Series(data=merged.price, index=buildings.index).fillna(0)
def pecas_price(costar, pecas_prices): costar = costar.to_frame(columns=['development_type_id', 'luz_id']) pecas_prices = pecas_prices.to_frame() year = sim.get_injectable('year') if year is None: year = 2012 pecas_prices = pecas_prices[pecas_prices.year == year] costar.index.name = 'costar_id' merged = pd.merge(costar.reset_index(), pecas_prices, left_on=['luz_id', 'development_type_id'], right_on=['luz_id', 'development_type_id']).set_index('costar_id') return pd.Series(data=merged.price, index=costar.index).fillna(0)
def job_spaces(): store = sim.get_injectable('store') b = sim.get_table('buildings').to_frame( ['luz_id', 'development_type_id', 'non_residential_sqft']) bsqft_job = store['building_sqft_per_job'] merged = pd.merge(b.reset_index(), bsqft_job, left_on=['luz_id', 'development_type_id'], right_on=['luz_id', 'development_type_id']) merged = merged.set_index('building_id') merged.sqft_per_emp[merged.sqft_per_emp < 40] = 40 merged['job_spaces'] = np.ceil(merged.non_residential_sqft / merged.sqft_per_emp) job_spaces = pd.Series(merged.job_spaces, index=b.index) b['job_spaces'] = job_spaces b.job_spaces[b.job_spaces.isnull()] = np.ceil(b.non_residential_sqft / 200.0) return b.job_spaces
def fill_nas_from_config(dfname, df): df_cnt = len(df) fillna_config = sim.get_injectable("fillna_config") fillna_config_df = fillna_config[dfname] for fname in fillna_config_df: filltyp, dtyp = fillna_config_df[fname] s_cnt = df[fname].count() fill_cnt = df_cnt - s_cnt if filltyp == "zero": val = 0 elif filltyp == "mode": val = df[fname].dropna().value_counts().idxmax() elif filltyp == "median": val = df[fname].dropna().quantile() else: assert 0, "Fill type not found!" print "Filling column {} with value {} ({} values)".\ format(fname, val, fill_cnt) df[fname] = df[fname].fillna(val).astype(dtyp) return df
def fill_nas_from_config(dfname, df): df_cnt = len(df) fillna_config = sim.get_injectable("fillna_config") fillna_config_df = fillna_config[dfname] for fname in fillna_config_df: filltyp, dtyp = fillna_config_df[fname] s_cnt = df[fname].count() fill_cnt = df_cnt - s_cnt if filltyp == "zero": val = 0 elif filltyp == "mode": val = df[fname].dropna().value_counts().idxmax() elif filltyp == "median": val = df[fname].dropna().quantile() else: assert 0, "Fill type not found!" print "Filling column {} with value {} ({} values)".\ format(fname, val, fill_cnt) df[fname] = df[fname].fillna(val).astype(dtyp) return df
def lcm_simulate(cfg, choosers, buildings, join_tbls, out_fname, supply_fname, vacant_fname, enable_supply_correction=None): """ Simulate the location choices for the specified choosers Parameters ---------- cfg : string The name of the yaml config file from which to read the location choice model choosers : DataFrameWrapper A dataframe of agents doing the choosing buildings : DataFrameWrapper A dataframe of buildings which the choosers are locating in and which have a supply join_tbls : list of strings A list of land use dataframes to give neighborhood info around the buildings - will be joined to the buildings using existing broadcasts. out_fname : string The column name to write the simulated location to supply_fname : string The string in the buildings table that indicates the amount of available units there are for choosers, vacant or not vacant_fname : string The string in the buildings table that indicates the amount of vacant units there will be for choosers enable_supply_correction : Python dict Should contain keys "price_col" and "submarket_col" which are set to the column names in buildings which contain the column for prices and an identifier which segments buildings into submarkets """ cfg = misc.config(cfg) choosers_df = to_frame(choosers, [], cfg, additional_columns=[out_fname]) additional_columns = [supply_fname, vacant_fname] if enable_supply_correction is not None and \ "submarket_col" in enable_supply_correction: additional_columns += [enable_supply_correction["submarket_col"]] if enable_supply_correction is not None and \ "price_col" in enable_supply_correction: additional_columns += [enable_supply_correction["price_col"]] locations_df = to_frame(buildings, join_tbls, cfg, additional_columns=additional_columns) available_units = buildings[supply_fname] vacant_units = buildings[vacant_fname] print "There are %d total available units" % available_units.sum() print " and %d total choosers" % len(choosers) print " but there are %d overfull buildings" % \ len(vacant_units[vacant_units < 0]) vacant_units = vacant_units[vacant_units > 0] # sometimes there are vacant units for buildings that are not in the # locations_df, which happens for reasons explained in the warning below indexes = np.repeat(vacant_units.index.values, vacant_units.values.astype('int')) isin = pd.Series(indexes).isin(locations_df.index) missing = len(isin[isin == False]) indexes = indexes[isin.values] units = locations_df.loc[indexes].reset_index() check_nas(units) print " for a total of %d temporarily empty units" % vacant_units.sum() print " in %d buildings total in the region" % len(vacant_units) if missing > 0: print "WARNING: %d indexes aren't found in the locations df -" % \ missing print " this is usually because of a few records that don't join " print " correctly between the locations df and the aggregations tables" movers = choosers_df[choosers_df[out_fname] == -1] print "There are %d total movers for this LCM" % len(movers) if enable_supply_correction is not None: assert isinstance(enable_supply_correction, dict) assert "price_col" in enable_supply_correction price_col = enable_supply_correction["price_col"] assert "submarket_col" in enable_supply_correction submarket_col = enable_supply_correction["submarket_col"] lcm = yaml_to_class(cfg).from_yaml(str_or_buffer=cfg) if enable_supply_correction.get("warm_start", False) is True: raise NotImplementedError() multiplier_func = enable_supply_correction.get("multiplier_func", None) if multiplier_func is not None: multiplier_func = sim.get_injectable(multiplier_func) kwargs = enable_supply_correction.get('kwargs', {}) new_prices, submarkets_ratios = supply_and_demand( lcm, movers, units, submarket_col, price_col, base_multiplier=None, multiplier_func=multiplier_func, **kwargs) # we will only get back new prices for those alternatives # that pass the filter - might need to specify the table in # order to get the complete index of possible submarkets submarket_table = enable_supply_correction.get("submarket_table", None) if submarket_table is not None: submarkets_ratios = submarkets_ratios.reindex( sim.get_table(submarket_table).index).fillna(1) # write final shifters to the submarket_table for use in debugging sim.get_table(submarket_table)["price_shifters"] = submarkets_ratios print "Running supply and demand" print "Simulated Prices" print buildings[price_col].describe() print "Submarket Price Shifters" print submarkets_ratios.describe() # we want new prices on the buildings, not on the units, so apply # shifters directly to buildings and ignore unit prices sim.add_column(buildings.name, price_col+"_hedonic", buildings[price_col]) new_prices = buildings[price_col] * \ submarkets_ratios.loc[buildings[submarket_col]].values buildings.update_col_from_series(price_col, new_prices) print "Adjusted Prices" print buildings[price_col].describe() if len(movers) > vacant_units.sum(): print "WARNING: Not enough locations for movers" print " reducing locations to size of movers for performance gain" movers = movers.head(vacant_units.sum()) new_units, _ = yaml_to_class(cfg).predict_from_cfg(movers, units, cfg) # new_units returns nans when there aren't enough units, # get rid of them and they'll stay as -1s new_units = new_units.dropna() # go from units back to buildings new_buildings = pd.Series(units.loc[new_units.values][out_fname].values, index=new_units.index) choosers.update_col_from_series(out_fname, new_buildings) _print_number_unplaced(choosers, out_fname) if enable_supply_correction is not None: new_prices = buildings[price_col] if "clip_final_price_low" in enable_supply_correction: new_prices = new_prices.clip(lower=enable_supply_correction[ "clip_final_price_low"]) if "clip_final_price_high" in enable_supply_correction: new_prices = new_prices.clip(upper=enable_supply_correction[ "clip_final_price_high"]) buildings.update_col_from_series(price_col, new_prices) vacant_units = buildings[vacant_fname] print " and there are now %d empty units" % vacant_units.sum() print " and %d overfull buildings" % len(vacant_units[vacant_units < 0])
def random_type(form): form_to_btype = sim.get_injectable("form_to_btype") return random.choice(form_to_btype[form])
def buildings_to_uc(buildings): year = get_year() # Export newly predicted buildings (from proforma or Sitespec) to Urban Canvas b = buildings.to_frame(buildings.local_columns) new_buildings = b[(b.note=='simulated') | (b.note.str.startswith('Sitespec'))] new_buildings = new_buildings[new_buildings.year_built == year] new_buildings = new_buildings.reset_index() new_buildings = new_buildings.rename(columns = {'development_type_id':'building_type_id'}) new_buildings['building_sqft'] = new_buildings.residential_sqft + new_buildings.non_residential_sqft new_buildings['sqft_per_unit'] = new_buildings.residential_sqft/new_buildings.residential_units del new_buildings['res_price_per_sqft'] del new_buildings['nonres_rent_per_sqft'] new_buildings.parcel_id = new_buildings.parcel_id.astype('int32') new_buildings.residential_units = new_buildings.residential_units.astype('int32') new_buildings.non_residential_sqft = new_buildings.non_residential_sqft.astype('int32') new_buildings.stories = new_buildings.stories.astype('int32') new_buildings.residential_sqft = new_buildings.residential_sqft.astype('int32') new_buildings.building_sqft = new_buildings.building_sqft.fillna(0).astype('int32') new_buildings.sqft_per_unit = new_buildings.sqft_per_unit.fillna(0).astype('int32') # Urban Canvas database connection conn_string = "host='urbancanvas.cp2xwchuariu.us-west-2.rds.amazonaws.com' dbname='sandag_testing' user='******' password='******' port=5432" if 'uc_conn' not in sim._INJECTABLES.keys(): conn=psycopg2.connect(conn_string) cur = conn.cursor() sim.add_injectable('uc_conn', conn) sim.add_injectable('uc_cur', cur) else: conn = sim.get_injectable('uc_conn') cur = sim.get_injectable('uc_cur') def exec_sql_uc(query): try: cur.execute(query) conn.commit() except: conn=psycopg2.connect(conn_string) cur = conn.cursor() sim.add_injectable('uc_conn', conn) sim.add_injectable('uc_cur', cur) cur.execute(query) conn.commit() def get_val_from_uc_db(query): try: result = sql.read_frame(query, conn) return result.values[0][0] except: conn=psycopg2.connect(conn_string) cur = conn.cursor() sim.add_injectable('uc_conn', conn) sim.add_injectable('uc_cur', cur) result = sql.read_frame(query, conn) return result.values[0][0] max_bid = get_val_from_uc_db("select max(building_id) FROM building where building_id<100000000;") new_buildings.building_id = np.arange(max_bid+1, max_bid+1+len(new_buildings)) if 'projects_num' not in sim._INJECTABLES.keys(): exec_sql_uc("INSERT INTO scenario(id, name, type) select nextval('scenario_id_seq'), 'Run #' || cast(currval('scenario_id_seq') as character varying), 1;") nextval = get_val_from_uc_db("SELECT MAX(ID) FROM SCENARIO WHERE ID < 100000;") sim.add_injectable('projects_num', nextval) exec_sql_uc("INSERT INTO scenario_project(scenario, project) VALUES(%s, 1);" % nextval) exec_sql_uc("INSERT INTO scenario_project(scenario, project) VALUES(%s, %s);" % (nextval,nextval)) else: nextval = sim.get_injectable('projects_num') nextval = '{' + str(nextval) + '}' new_buildings['projects'] = nextval valid_from = '{' + str(year) + '-1-1}' new_buildings['valid_from'] = valid_from print 'Exporting %s buildings to Urbancanvas database for project %s and year %s.' % (len(new_buildings),nextval,year) output = cStringIO.StringIO() new_buildings.to_csv(output, sep='\t', header=False, index=False) output.seek(0) cur.copy_from(output, 'building', columns =tuple(new_buildings.columns.values.tolist())) conn.commit()
def get_run_filename(): if 'run_no' not in sim.list_injectables(): get_run_no() return os.path.join(misc.runs_dir(), "run%d.h5" % sim.get_injectable("run_no"))
def feasibility(parcels, settings, parcel_sales_price_sqft_func, parcel_is_allowed_func): # Fee table preprocessing fee_schedule = sim.get_table('fee_schedule').to_frame() parcel_fee_schedule = sim.get_table('parcel_fee_schedule').to_frame() parcels = sim.get_table('parcels').to_frame(columns = ['zoning_id','development_type_id']) fee_schedule = fee_schedule.groupby(['fee_schedule_id', 'development_type_id']).development_fee_per_unit_space_initial.mean().reset_index() parcel_use_allowed_callback = sim.get_injectable('parcel_is_allowed_func') def run_proforma_lookup(parcels, fees, pf, use, form, residential_to_yearly, parcel_filter = None): if parcel_filter: parcels = parcels.query(parcel_filter) # add prices for each use (rents). Apply fees parcels[use] = misc.reindex(sim.get_table('nodes')[use], sim.get_table('parcels').node_id) - fees #Calibration shifters calibration_shifters = pd.read_csv('.\\data\\calibration\\msa_shifters.csv').set_index('msa_id').to_dict() if use == 'residential': shifter_name = 'res_price_shifter' else: shifter_name = 'nonres_price_shifter' parcels[shifter_name] = 1.0 shifters = calibration_shifters[shifter_name] for msa_id in shifters.keys(): shift = shifters[msa_id] parcels[shifter_name][parcels.msa_id == msa_id] = shift parcels[use] = parcels[use] * parcels[shifter_name] #LUZ shifter if use == 'residential': target_luz = pd.read_csv('.\\data\\calibration\\target_luz.csv').values.flatten() luz_shifter = pd.read_csv('.\\data\\calibration\\luz_du_shifter.csv').values[0][0] parcels[use][parcels.luz_id.isin(target_luz)] = parcels[use][parcels.luz_id.isin(target_luz)] * luz_shifter # convert from cost to yearly rent if residential_to_yearly: parcels[use] *= pf.config.cap_rate # Price minimum if hedonic predicts outlier parcels[use][parcels[use] <= .5] = .5 parcels[use][parcels[use].isnull()] = .5 print "Describe of the yearly rent by use" print parcels[use].describe() allowed = parcel_use_allowed_callback(form).loc[parcels.index] feasibility = pf.lookup(form, parcels[allowed], only_built=True, pass_through=[]) if use == 'residential': def iter_feasibility(feasibility, price_scaling_factor): if price_scaling_factor > 3.0: return feasibility # Get targets target_units = residential_space_targets()[form] #Calculate number of profitable units d = {} d[form] = feasibility feas = pd.concat(d.values(), keys=d.keys(), axis=1) dev = developer.Developer(feas) profitable_units = run_developer(dev, form, target_units, get_year(), build = False) print 'Feasibility given current prices/zonining indicates %s profitable units and target of %s' % (profitable_units, target_units) if profitable_units < target_units: price_scaling_factor += .1 print 'Scaling prices up by factor of %s' % price_scaling_factor parcels[use] = parcels[use] * price_scaling_factor feasibility = pf.lookup(form, parcels[allowed], only_built=True, pass_through=[]) return iter_feasibility(feasibility, price_scaling_factor) else: price_scaling_factor += .1 parcels[use] = parcels[use] * price_scaling_factor feasibility = pf.lookup(form, parcels[allowed], only_built=True, pass_through=[]) return feasibility feasibility = iter_feasibility(feasibility, 1.0) elif use != 'residential': def iter_feasibility(feasibility, price_scaling_factor): if price_scaling_factor > 3.0: return feasibility # Get targets targets = non_residential_space_targets() target_units = targets[form]/400 #Calculate number of profitable units feasibility['current_units'] = parcels.total_job_spaces feasibility["parcel_size"] = parcels.parcel_size feasibility = feasibility[feasibility.parcel_size < 200000] feasibility['job_spaces'] = np.round(feasibility.non_residential_sqft / 400.0) feasibility['net_units'] = feasibility.job_spaces - feasibility.current_units feasibility.net_units = feasibility.net_units.fillna(0) profitable_units = int(feasibility.net_units.sum()) print 'Feasibility given current prices/zonining indicates %s profitable units and target of %s' % (profitable_units, target_units) if profitable_units < target_units: price_scaling_factor += .1 print 'Scaling prices up by factor of %s' % price_scaling_factor parcels[use] = parcels[use] * price_scaling_factor feasibility = pf.lookup(form, parcels[allowed], only_built=True, pass_through=[]) return iter_feasibility(feasibility, price_scaling_factor) else: return feasibility feasibility = iter_feasibility(feasibility, 1.0) print len(feasibility) return feasibility def residential_proforma(form, devtype_id, parking_rate): print form use = 'residential' parcels = sim.get_table('parcels').to_frame() residential_to_yearly = True parcel_filter = settings['feasibility']['parcel_filter'] #parcel_filter = None pfc = sqftproforma.SqFtProFormaConfig() pfc.forms = {form: {use : 1.0}} pfc.uses = [use] pfc.residential_uses = [True] pfc.parking_rates = {use : parking_rate} pfc.costs = {use : [170.0, 190.0, 210.0, 240.0]} #Fees fee_schedule_devtype = fee_schedule[fee_schedule.development_type_id == devtype_id] parcel_fee_schedule_devtype = pd.merge(parcel_fee_schedule, fee_schedule_devtype, left_on = 'fee_schedule_id', right_on = 'fee_schedule_id') parcel_fee_schedule_devtype['development_fee_per_unit'] = parcel_fee_schedule_devtype.development_fee_per_unit_space_initial*parcel_fee_schedule_devtype.portion parcel_fees_processed = parcel_fee_schedule_devtype.groupby('parcel_id').development_fee_per_unit.sum() fees = pd.Series(data = parcel_fees_processed, index = parcels.index).fillna(0) pf = sqftproforma.SqFtProForma(pfc) return run_proforma_lookup(parcels, fees, pf, use, form, residential_to_yearly, parcel_filter = parcel_filter) def nonresidential_proforma(form, devtype_id, use, parking_rate): print form parcels = sim.get_table('parcels').to_frame() residential_to_yearly = False parcel_filter = settings['feasibility']['parcel_filter'] #parcel_filter = None pfc = sqftproforma.SqFtProFormaConfig() pfc.forms = {form: {use : 1.0}} pfc.uses = [use] pfc.residential_uses = [False] pfc.parking_rates = {use : parking_rate} if use == 'retail': pfc.costs = {use : [160.0, 175.0, 200.0, 230.0]} elif use == 'industrial': pfc.costs = {use : [140.0, 175.0, 200.0, 230.0]} else: #office pfc.costs = {use : [160.0, 175.0, 200.0, 230.0]} #Fees fee_schedule_devtype = fee_schedule[fee_schedule.development_type_id == devtype_id] parcel_fee_schedule_devtype = pd.merge(parcel_fee_schedule, fee_schedule_devtype, left_on = 'fee_schedule_id', right_on = 'fee_schedule_id') parcel_fee_schedule_devtype['development_fee_per_unit'] = parcel_fee_schedule_devtype.development_fee_per_unit_space_initial*parcel_fee_schedule_devtype.portion parcel_fees_processed = parcel_fee_schedule_devtype.groupby('parcel_id').development_fee_per_unit.sum() fees = pd.Series(data = parcel_fees_processed, index = parcels.index).fillna(0) pf = sqftproforma.SqFtProForma(pfc) fees = fees*pf.config.cap_rate return run_proforma_lookup(parcels, fees, pf, use, form, residential_to_yearly, parcel_filter = parcel_filter) d = {} ##SF DETACHED proforma (devtype 19) form = 'sf_detached' devtype_id = 19 d[form] = residential_proforma(form, devtype_id, parking_rate = 1.0) ##SF ATTACHED proforma (devtype 20) form = 'sf_attached' devtype_id = 20 d[form] = residential_proforma(form, devtype_id, parking_rate = 1.0) ##MF_RESIDENTIAL proforma (devtype 21) form = 'mf_residential' devtype_id = 21 d[form] = residential_proforma(form, devtype_id, parking_rate = 1.0) ##OFFICE (devtype 4) form = 'office' devtype_id = 4 d[form] = nonresidential_proforma(form, devtype_id, form, parking_rate = 1.0) ##RETAIL (devtype 5) form = 'retail' devtype_id = 5 d[form] = nonresidential_proforma(form, devtype_id, form, parking_rate = 2.0) ##LIGHT INDUSTRIAL (devtype 2) form = 'light_industrial' devtype_id = 2 d[form] = nonresidential_proforma(form, devtype_id, 'industrial', parking_rate = .6) ##HEAVY INDUSTRIAL (devtype 3) form = 'heavy_industrial' devtype_id = 3 d[form] = nonresidential_proforma(form, devtype_id, 'industrial', parking_rate = .6) far_predictions = pd.concat(d.values(), keys=d.keys(), axis=1) sim.add_table("feasibility", far_predictions)
def buildings_to_uc(buildings): year = get_year() # Export newly predicted buildings (from proforma or Sitespec) to Urban Canvas b = buildings.to_frame(buildings.local_columns) new_buildings = b[(b.note == 'simulated') | (b.note.str.startswith('Sitespec'))] new_buildings = new_buildings[new_buildings.year_built == year] new_buildings = new_buildings.reset_index() new_buildings = new_buildings.rename( columns={'development_type_id': 'building_type_id'}) new_buildings[ 'building_sqft'] = new_buildings.residential_sqft + new_buildings.non_residential_sqft new_buildings[ 'sqft_per_unit'] = new_buildings.residential_sqft / new_buildings.residential_units del new_buildings['res_price_per_sqft'] del new_buildings['nonres_rent_per_sqft'] new_buildings.parcel_id = new_buildings.parcel_id.astype('int32') new_buildings.residential_units = new_buildings.residential_units.astype( 'int32') new_buildings.non_residential_sqft = new_buildings.non_residential_sqft.astype( 'int32') new_buildings.stories = new_buildings.stories.astype('int32') new_buildings.residential_sqft = new_buildings.residential_sqft.astype( 'int32') new_buildings.building_sqft = new_buildings.building_sqft.fillna(0).astype( 'int32') new_buildings.sqft_per_unit = new_buildings.sqft_per_unit.fillna(0).astype( 'int32') # Urban Canvas database connection conn_string = "host='urbancanvas.cp2xwchuariu.us-west-2.rds.amazonaws.com' dbname='sandag_testing' user='******' password='******' port=5432" if 'uc_conn' not in sim._INJECTABLES.keys(): conn = psycopg2.connect(conn_string) cur = conn.cursor() sim.add_injectable('uc_conn', conn) sim.add_injectable('uc_cur', cur) else: conn = sim.get_injectable('uc_conn') cur = sim.get_injectable('uc_cur') def exec_sql_uc(query): try: cur.execute(query) conn.commit() except: conn = psycopg2.connect(conn_string) cur = conn.cursor() sim.add_injectable('uc_conn', conn) sim.add_injectable('uc_cur', cur) cur.execute(query) conn.commit() def get_val_from_uc_db(query): try: result = sql.read_frame(query, conn) return result.values[0][0] except: conn = psycopg2.connect(conn_string) cur = conn.cursor() sim.add_injectable('uc_conn', conn) sim.add_injectable('uc_cur', cur) result = sql.read_frame(query, conn) return result.values[0][0] max_bid = get_val_from_uc_db( "select max(building_id) FROM building where building_id<100000000;") new_buildings.building_id = np.arange(max_bid + 1, max_bid + 1 + len(new_buildings)) if 'projects_num' not in sim._INJECTABLES.keys(): exec_sql_uc( "INSERT INTO scenario(id, name, type) select nextval('scenario_id_seq'), 'Run #' || cast(currval('scenario_id_seq') as character varying), 1;" ) nextval = get_val_from_uc_db( "SELECT MAX(ID) FROM SCENARIO WHERE ID < 100000;") sim.add_injectable('projects_num', nextval) exec_sql_uc( "INSERT INTO scenario_project(scenario, project) VALUES(%s, 1);" % nextval) exec_sql_uc( "INSERT INTO scenario_project(scenario, project) VALUES(%s, %s);" % (nextval, nextval)) else: nextval = sim.get_injectable('projects_num') nextval = '{' + str(nextval) + '}' new_buildings['projects'] = nextval valid_from = '{' + str(year) + '-1-1}' new_buildings['valid_from'] = valid_from print 'Exporting %s buildings to Urbancanvas database for project %s and year %s.' % ( len(new_buildings), nextval, year) output = cStringIO.StringIO() new_buildings.to_csv(output, sep='\t', header=False, index=False) output.seek(0) cur.copy_from(output, 'building', columns=tuple(new_buildings.columns.values.tolist())) conn.commit()
def change_scenario(scenario): assert scenario in sim.get_injectable("scenario_inputs"), \ "Invalid scenario name" print "Changing scenario to '%s'" % scenario sim.add_injectable("scenario", scenario)
def lcm_simulate(cfg, choosers, buildings, join_tbls, out_fname, supply_fname, vacant_fname, enable_supply_correction=None): """ Simulate the location choices for the specified choosers Parameters ---------- cfg : string The name of the yaml config file from which to read the location choice model choosers : DataFrameWrapper A dataframe of agents doing the choosing buildings : DataFrameWrapper A dataframe of buildings which the choosers are locating in and which have a supply join_tbls : list of strings A list of land use dataframes to give neighborhood info around the buildings - will be joined to the buildings using existing broadcasts. out_fname : string The column name to write the simulated location to supply_fname : string The string in the buildings table that indicates the amount of available units there are for choosers, vacant or not vacant_fname : string The string in the buildings table that indicates the amount of vacant units there will be for choosers enable_supply_correction : Python dict Should contain keys "price_col" and "submarket_col" which are set to the column names in buildings which contain the column for prices and an identifier which segments buildings into submarkets """ cfg = misc.config(cfg) choosers_df = utils.to_frame(choosers, [], cfg, additional_columns=[out_fname]) additional_columns = [supply_fname, vacant_fname] if enable_supply_correction is not None and \ "submarket_col" in enable_supply_correction: additional_columns += [enable_supply_correction["submarket_col"]] if enable_supply_correction is not None and \ "price_col" in enable_supply_correction: additional_columns += [enable_supply_correction["price_col"]] locations_df = utils.to_frame(buildings, join_tbls, cfg, additional_columns=additional_columns) available_units = buildings[supply_fname] vacant_units = buildings[vacant_fname] print "There are %d total available units" % available_units.sum() print " and %d total choosers" % len(choosers) print " but there are %d overfull buildings" % \ len(vacant_units[vacant_units < 0]) vacant_units = vacant_units[vacant_units > 0] # sometimes there are vacant units for buildings that are not in the # locations_df, which happens for reasons explained in the warning below indexes = np.repeat(vacant_units.index.values, vacant_units.values.astype('int')) isin = pd.Series(indexes).isin(locations_df.index) missing = len(isin[isin == False]) indexes = indexes[isin.values] units = locations_df.loc[indexes].reset_index() utils.check_nas(units) print " for a total of %d temporarily empty units" % vacant_units.sum() print " in %d buildings total in the region" % len(vacant_units) if missing > 0: print "WARNING: %d indexes aren't found in the locations df -" % \ missing print " this is usually because of a few records that don't join " print " correctly between the locations df and the aggregations tables" movers = choosers_df[choosers_df[out_fname] == -1] print "There are %d total movers for this LCM" % len(movers) if enable_supply_correction is not None: assert isinstance(enable_supply_correction, dict) assert "price_col" in enable_supply_correction price_col = enable_supply_correction["price_col"] assert "submarket_col" in enable_supply_correction submarket_col = enable_supply_correction["submarket_col"] lcm = utils.yaml_to_class(cfg).from_yaml(str_or_buffer=cfg) if enable_supply_correction.get("warm_start", False) is True: raise NotImplementedError() multiplier_func = enable_supply_correction.get("multiplier_func", None) if multiplier_func is not None: multiplier_func = sim.get_injectable(multiplier_func) kwargs = enable_supply_correction.get('kwargs', {}) new_prices, submarkets_ratios = supply_and_demand( lcm, movers, units, submarket_col, price_col, base_multiplier=None, multiplier_func=multiplier_func, **kwargs) # we will only get back new prices for those alternatives # that pass the filter - might need to specify the table in # order to get the complete index of possible submarkets submarket_table = enable_supply_correction.get("submarket_table", None) if submarket_table is not None: submarkets_ratios = submarkets_ratios.reindex( sim.get_table(submarket_table).index).fillna(1) # write final shifters to the submarket_table for use in debugging sim.get_table(submarket_table)["price_shifters"] = submarkets_ratios print "Running supply and demand" print "Simulated Prices" print buildings[price_col].describe() print "Submarket Price Shifters" print submarkets_ratios.describe() # we want new prices on the buildings, not on the units, so apply # shifters directly to buildings and ignore unit prices sim.add_column(buildings.name, price_col+"_hedonic", buildings[price_col]) new_prices = buildings[price_col] * \ submarkets_ratios.loc[buildings[submarket_col]].values buildings.update_col_from_series(price_col, new_prices) print "Adjusted Prices" print buildings[price_col].describe() #if len(movers) > vacant_units.sum(): # print "WARNING: Not enough locations for movers" # print " reducing locations to size of movers for performance gain" # movers = movers.head(vacant_units.sum()) new_units, _ = utils.yaml_to_class(cfg).predict_from_cfg(movers, units, cfg, location_ratio=100.0) # new_units returns nans when there aren't enough units, # get rid of them and they'll stay as -1s new_units = new_units.dropna() # go from units back to buildings new_buildings = pd.Series(units.loc[new_units.values][out_fname].values, index=new_units.index) choosers.update_col_from_series(out_fname, new_buildings) utils._print_number_unplaced(choosers, out_fname) if enable_supply_correction is not None: new_prices = buildings[price_col] if "clip_final_price_low" in enable_supply_correction: new_prices = new_prices.clip(lower=enable_supply_correction[ "clip_final_price_low"]) if "clip_final_price_high" in enable_supply_correction: new_prices = new_prices.clip(upper=enable_supply_correction[ "clip_final_price_high"]) buildings.update_col_from_series(price_col, new_prices) vacant_units = buildings[vacant_fname] print " and there are now %d empty units" % vacant_units.sum() print " and %d overfull buildings" % len(vacant_units[vacant_units < 0])
def run_developer(forms, agents, buildings, buildings_all, supply_fname, parcel_size, ave_unit_size, total_units, feasibility, year=None, target_vacancy=.1, form_to_btype_callback=None, add_more_columns_callback=None, max_parcel_size=34647265, residential=True, bldg_sqft_per_job=400.0, min_unit_size=400, remove_developed_buildings=True, unplace_agents=['households', 'jobs']): """ Run the developer model to pick and build buildings Parameters ---------- forms : string or list of strings Passed directly dev.pick agents : DataFrame Wrapper Used to compute the current demand for units/floorspace in the area buildings : DataFrame Wrapper Used to compute the current supply of units/floorspace in the area buildings_all: Buildings for the entire region, used to write back to buildings table supply_fname : string Identifies the column in buildings which indicates the supply of units/floorspace parcel_size : Series Passed directly to dev.pick ave_unit_size : Series Passed directly to dev.pick - average residential unit size total_units : Series Passed directly to dev.pick - total current residential_units / job_spaces feasibility : DataFrame Wrapper The output from feasibility above (the table called 'feasibility') year : int The year of the simulation - will be assigned to 'year_built' on the new buildings target_vacancy : float The target vacancy rate - used to determine how much to build form_to_btype_callback : function Will be used to convert the 'forms' in the pro forma to 'building_type_id' in the larger model add_more_columns_callback : function Takes a dataframe and returns a dataframe - is used to make custom modifications to the new buildings that get added max_parcel_size : float Passed directly to dev.pick - max parcel size to consider min_unit_size : float Passed directly to dev.pick - min unit size that is valid residential : boolean Passed directly to dev.pick - switches between adding/computing residential_units and job_spaces bldg_sqft_per_job : float Passed directly to dev.pick - specified the multiplier between floor spaces and job spaces for this form (does not vary by parcel as ave_unit_size does) remove_redeveloped_buildings : optional, boolean (default True) Remove all buildings on the parcels which are being developed on unplace_agents : optional : list of strings (default ['households', 'jobs']) For all tables in the list, will look for field building_id and set it to -1 for buildings which are removed - only executed if remove_developed_buildings is true Returns ------- Writes the result back to the buildings table and returns the new buildings with available debugging information on each new building """ dev = developer.Developer(feasibility.to_frame()) #dev = WFRCDeveloper.WFRCDeveloper(feasibility.to_frame()) target_units = dev.\ compute_units_to_build(len(agents), buildings[supply_fname].sum(), target_vacancy) print "{:,} feasible buildings before running developer".format( len(dev.feasibility)) new_buildings = dev.pick(forms, target_units, parcel_size, ave_unit_size, total_units, max_parcel_size=max_parcel_size, min_unit_size=min_unit_size, drop_after_build=True, residential=residential, bldg_sqft_per_job=bldg_sqft_per_job) sim.add_table("feasibility", dev.feasibility) year = sim.get_injectable('year') if new_buildings is None: return if len(new_buildings) == 0: return new_buildings if not isinstance(forms, list): # form gets set only if forms is a list new_buildings["form"] = forms if form_to_btype_callback is not None: new_buildings["building_type_id"] = new_buildings.\ apply(form_to_btype_callback, axis=1) new_buildings["stories"] = new_buildings.stories.apply(np.ceil) new_buildings["note"] = "simulated" ret_buildings = new_buildings if add_more_columns_callback is not None: new_buildings = add_more_columns_callback(new_buildings) if year is not None: new_buildings["year_built"] = year print "Adding {:,} buildings with {:,} {}".\ format(len(new_buildings), int(new_buildings[supply_fname].sum()), supply_fname) print "{:,} feasible buildings after running developer".format( len(dev.feasibility)) old_buildings = buildings.to_frame(buildings.local_columns) old_buildings_all = buildings_all.to_frame(buildings.local_columns) new_buildings = new_buildings[buildings.local_columns] if remove_developed_buildings: redev_buildings = old_buildings.parcel_id.isin(new_buildings.parcel_id) redev_buildings_all = old_buildings_all.parcel_id.isin(new_buildings.parcel_id) l = len(old_buildings) drop_buildings = old_buildings[redev_buildings] drop_buildings_all = old_buildings_all[redev_buildings_all] old_buildings = old_buildings[np.logical_not(redev_buildings)] old_buildings_all = old_buildings_all[np.logical_not(redev_buildings_all)] l2 = len(old_buildings) print "before dropped l:" + str(l) print "after dropped l2: " + str(l2) #print redev_buildings #print drop_buildings if l2-l > 0: print "Dropped {} buildings because they were redeveloped".\ format(l2-l) for tbl in unplace_agents: agents = sim.get_table(tbl) agents = agents.to_frame(agents.local_columns) #displaced_agents = agents.building_id.isin(drop_buildings.index) displaced_agents = agents.building_id.isin(drop_buildings_all.index) print "Unplaced {} before: {}".format(tbl, len(agents.query( "building_id == -1"))) agents.building_id[displaced_agents] = -1 print "Unplaced {} after: {}".format(tbl, len(agents.query( "building_id == -1"))) sim.add_table(tbl, agents) all_buildings = dev.merge(old_buildings_all, new_buildings) sim.add_table("buildings", all_buildings) return ret_buildings
def change_scenario(scenario): assert scenario in sim.get_injectable("scenario_inputs"), \ "Invalid scenario name" print "Changing scenario to '%s'" % scenario sim.add_injectable("scenario", scenario)
def get_year(): year = sim.get_injectable('year') if year is None: year = 2012 return year
def feasibility(parcels, settings, parcel_sales_price_sqft_func, parcel_is_allowed_func): # Fee table preprocessing fee_schedule = sim.get_table('fee_schedule').to_frame() parcel_fee_schedule = sim.get_table('parcel_fee_schedule').to_frame() parcels = sim.get_table('parcels').to_frame( columns=['zoning_id', 'development_type_id']) fee_schedule = fee_schedule.groupby([ 'fee_schedule_id', 'development_type_id' ]).development_fee_per_unit_space_initial.mean().reset_index() parcel_use_allowed_callback = sim.get_injectable('parcel_is_allowed_func') def run_proforma_lookup(parcels, fees, pf, use, form, residential_to_yearly, parcel_filter=None): if parcel_filter: parcels = parcels.query(parcel_filter) # add prices for each use (rents). Apply fees parcels[use] = misc.reindex( sim.get_table('nodes')[use], sim.get_table('parcels').node_id) - fees #Calibration shifters calibration_shifters = pd.read_csv( '.\\data\\calibration\\msa_shifters.csv').set_index( 'msa_id').to_dict() if use == 'residential': shifter_name = 'res_price_shifter' else: shifter_name = 'nonres_price_shifter' parcels[shifter_name] = 1.0 shifters = calibration_shifters[shifter_name] for msa_id in shifters.keys(): shift = shifters[msa_id] parcels[shifter_name][parcels.msa_id == msa_id] = shift parcels[use] = parcels[use] * parcels[shifter_name] #LUZ shifter if use == 'residential': target_luz = pd.read_csv( '.\\data\\calibration\\target_luz.csv').values.flatten() luz_shifter = pd.read_csv( '.\\data\\calibration\\luz_du_shifter.csv').values[0][0] parcels[use][parcels.luz_id.isin(target_luz)] = parcels[use][ parcels.luz_id.isin(target_luz)] * luz_shifter # convert from cost to yearly rent if residential_to_yearly: parcels[use] *= pf.config.cap_rate # Price minimum if hedonic predicts outlier parcels[use][parcels[use] <= .5] = .5 parcels[use][parcels[use].isnull()] = .5 print "Describe of the yearly rent by use" print parcels[use].describe() allowed = parcel_use_allowed_callback(form).loc[parcels.index] feasibility = pf.lookup(form, parcels[allowed], only_built=True, pass_through=[]) if use == 'residential': def iter_feasibility(feasibility, price_scaling_factor): if price_scaling_factor > 3.0: return feasibility # Get targets target_units = residential_space_targets()[form] #Calculate number of profitable units d = {} d[form] = feasibility feas = pd.concat(d.values(), keys=d.keys(), axis=1) dev = developer.Developer(feas) profitable_units = run_developer(dev, form, target_units, get_year(), build=False) print 'Feasibility given current prices/zonining indicates %s profitable units and target of %s' % ( profitable_units, target_units) if profitable_units < target_units: price_scaling_factor += .1 print 'Scaling prices up by factor of %s' % price_scaling_factor parcels[use] = parcels[use] * price_scaling_factor feasibility = pf.lookup(form, parcels[allowed], only_built=True, pass_through=[]) return iter_feasibility(feasibility, price_scaling_factor) else: price_scaling_factor += .1 parcels[use] = parcels[use] * price_scaling_factor feasibility = pf.lookup(form, parcels[allowed], only_built=True, pass_through=[]) return feasibility feasibility = iter_feasibility(feasibility, 1.0) elif use != 'residential': def iter_feasibility(feasibility, price_scaling_factor): if price_scaling_factor > 3.0: return feasibility # Get targets targets = non_residential_space_targets() target_units = targets[form] / 400 #Calculate number of profitable units feasibility['current_units'] = parcels.total_job_spaces feasibility["parcel_size"] = parcels.parcel_size feasibility = feasibility[feasibility.parcel_size < 200000] feasibility['job_spaces'] = np.round( feasibility.non_residential_sqft / 400.0) feasibility[ 'net_units'] = feasibility.job_spaces - feasibility.current_units feasibility.net_units = feasibility.net_units.fillna(0) profitable_units = int(feasibility.net_units.sum()) print 'Feasibility given current prices/zonining indicates %s profitable units and target of %s' % ( profitable_units, target_units) if profitable_units < target_units: price_scaling_factor += .1 print 'Scaling prices up by factor of %s' % price_scaling_factor parcels[use] = parcels[use] * price_scaling_factor feasibility = pf.lookup(form, parcels[allowed], only_built=True, pass_through=[]) return iter_feasibility(feasibility, price_scaling_factor) else: return feasibility feasibility = iter_feasibility(feasibility, 1.0) print len(feasibility) return feasibility def residential_proforma(form, devtype_id, parking_rate): print form use = 'residential' parcels = sim.get_table('parcels').to_frame() residential_to_yearly = True parcel_filter = settings['feasibility']['parcel_filter'] #parcel_filter = None pfc = sqftproforma.SqFtProFormaConfig() pfc.forms = {form: {use: 1.0}} pfc.uses = [use] pfc.residential_uses = [True] pfc.parking_rates = {use: parking_rate} pfc.costs = {use: [170.0, 190.0, 210.0, 240.0]} #Fees fee_schedule_devtype = fee_schedule[fee_schedule.development_type_id == devtype_id] parcel_fee_schedule_devtype = pd.merge(parcel_fee_schedule, fee_schedule_devtype, left_on='fee_schedule_id', right_on='fee_schedule_id') parcel_fee_schedule_devtype[ 'development_fee_per_unit'] = parcel_fee_schedule_devtype.development_fee_per_unit_space_initial * parcel_fee_schedule_devtype.portion parcel_fees_processed = parcel_fee_schedule_devtype.groupby( 'parcel_id').development_fee_per_unit.sum() fees = pd.Series(data=parcel_fees_processed, index=parcels.index).fillna(0) pf = sqftproforma.SqFtProForma(pfc) return run_proforma_lookup(parcels, fees, pf, use, form, residential_to_yearly, parcel_filter=parcel_filter) def nonresidential_proforma(form, devtype_id, use, parking_rate): print form parcels = sim.get_table('parcels').to_frame() residential_to_yearly = False parcel_filter = settings['feasibility']['parcel_filter'] #parcel_filter = None pfc = sqftproforma.SqFtProFormaConfig() pfc.forms = {form: {use: 1.0}} pfc.uses = [use] pfc.residential_uses = [False] pfc.parking_rates = {use: parking_rate} if use == 'retail': pfc.costs = {use: [160.0, 175.0, 200.0, 230.0]} elif use == 'industrial': pfc.costs = {use: [140.0, 175.0, 200.0, 230.0]} else: #office pfc.costs = {use: [160.0, 175.0, 200.0, 230.0]} #Fees fee_schedule_devtype = fee_schedule[fee_schedule.development_type_id == devtype_id] parcel_fee_schedule_devtype = pd.merge(parcel_fee_schedule, fee_schedule_devtype, left_on='fee_schedule_id', right_on='fee_schedule_id') parcel_fee_schedule_devtype[ 'development_fee_per_unit'] = parcel_fee_schedule_devtype.development_fee_per_unit_space_initial * parcel_fee_schedule_devtype.portion parcel_fees_processed = parcel_fee_schedule_devtype.groupby( 'parcel_id').development_fee_per_unit.sum() fees = pd.Series(data=parcel_fees_processed, index=parcels.index).fillna(0) pf = sqftproforma.SqFtProForma(pfc) fees = fees * pf.config.cap_rate return run_proforma_lookup(parcels, fees, pf, use, form, residential_to_yearly, parcel_filter=parcel_filter) d = {} ##SF DETACHED proforma (devtype 19) form = 'sf_detached' devtype_id = 19 d[form] = residential_proforma(form, devtype_id, parking_rate=1.0) ##SF ATTACHED proforma (devtype 20) form = 'sf_attached' devtype_id = 20 d[form] = residential_proforma(form, devtype_id, parking_rate=1.0) ##MF_RESIDENTIAL proforma (devtype 21) form = 'mf_residential' devtype_id = 21 d[form] = residential_proforma(form, devtype_id, parking_rate=1.0) ##OFFICE (devtype 4) form = 'office' devtype_id = 4 d[form] = nonresidential_proforma(form, devtype_id, form, parking_rate=1.0) ##RETAIL (devtype 5) form = 'retail' devtype_id = 5 d[form] = nonresidential_proforma(form, devtype_id, form, parking_rate=2.0) ##LIGHT INDUSTRIAL (devtype 2) form = 'light_industrial' devtype_id = 2 d[form] = nonresidential_proforma(form, devtype_id, 'industrial', parking_rate=.6) ##HEAVY INDUSTRIAL (devtype 3) form = 'heavy_industrial' devtype_id = 3 d[form] = nonresidential_proforma(form, devtype_id, 'industrial', parking_rate=.6) far_predictions = pd.concat(d.values(), keys=d.keys(), axis=1) sim.add_table("feasibility", far_predictions)
def get_run_no(): if 'run_no' not in sim.list_injectables(): sim.add_injectable("run_no", misc.get_run_number()) return sim.get_injectable("run_no")
def random_type(form): form_to_btype = sim.get_injectable("form_to_btype") return random.choice(form_to_btype[form])
def get_year(): year = sim.get_injectable('year') if year is None: year = 2012 return year