def test_time_stamp(self): tdf = TicDatFactory(table=[["Blah"], ["Timed Info"]]) tdf.set_data_type("table", "Timed Info", nullable=True) tdf.set_default_value("table", "Timed Info", None) dat = tdf.TicDat() dat.table[1] = dateutil.parser.parse("2014-05-01 18:47:05.069722") dat.table[2] = dateutil.parser.parse("2014-05-02 18:47:05.178768") pgtf = tdf.pgsql pgtf.write_schema(self.engine, test_schema, forced_field_types={ ('table', 'Blah'): "integer", ('table', 'Timed Info'): "timestamp" }) pgtf.write_data(dat, self.engine, test_schema, dsn=self.postgresql.dsn()) dat_2 = pgtf.create_tic_dat(self.engine, test_schema) self.assertTrue(tdf._same_data(dat, dat_2)) self.assertTrue( all( isinstance(row["Timed Info"], datetime.datetime) for row in dat_2.table.values())) self.assertFalse( any(isinstance(k, datetime.datetime) for k in dat_2.table)) pdf = PanDatFactory.create_from_full_schema( tdf.schema(include_ancillary_info=True)) def same_data(pan_dat, pan_dat_2): df1, df2 = pan_dat.table, pan_dat_2.table if list(df1["Blah"]) != list(df2["Blah"]): return False for dt1, dt2 in zip(df1["Timed Info"], df2["Timed Info"]): delta = dt1 - dt2 if abs(delta.total_seconds()) > 1e-6: return False return True pan_dat = pdf.pgsql.create_pan_dat(self.engine, test_schema) pan_dat_2 = pan_dat_maker(tdf.schema(), dat_2) self.assertTrue(same_data(pan_dat, pan_dat_2)) for df in [_.table for _ in [pan_dat, pan_dat_2]]: for i in range(len(df)): self.assertFalse( isinstance(df.loc[i, "Blah"], datetime.datetime)) self.assertTrue( isinstance(df.loc[i, "Timed Info"], datetime.datetime)) pan_dat.table.loc[1, "Timed Info"] = dateutil.parser.parse( "2014-05-02 18:48:05.178768") self.assertFalse(same_data(pan_dat, pan_dat_2)) pdf.pgsql.write_data(pan_dat, self.engine, test_schema) pan_dat_2 = pdf.pgsql.create_pan_dat(self.engine, test_schema) self.assertTrue(same_data(pan_dat, pan_dat_2)) dat.table[2] = dateutil.parser.parse("2014-05-02 18:48:05.178768") self.assertFalse(tdf._same_data(dat, dat_2))
def write_schema(self, engine, schema, forced_field_types=None, include_ancillary_info=True): """ :param engine: typically a sqlalchemy database engine with drivertype postgres (really just needs an .execute) :param schema: a string naming the postgres schema to populate (will create if needed) :param forced_field_types : A dictionary mappying (table, field) to a field type Absent forcing, types are inferred from tic_dat_factory.data_types if possible, and set via the assumption that PK fields are text and data fields are floats if not. :param include_ancillary_info : boolean. If False, no primary key or foreign key info will be written :return: """ self._check_good_pgtd_compatible_table_field_names() forced_field_types = forced_field_types or {} all_fields = lambda t: self.tdf.primary_key_fields.get(t, ( )) + self.tdf.data_fields.get(t, ()) good_forced_field_type_entry = lambda k, v: isinstance(k, tuple) and len(k) == 2 \ and k[1] in all_fields(k[0]) and v in \ ["text", "integer", "float", "bool", "boolean", "timestamp"] verify( dictish(forced_field_types) and all( good_forced_field_type_entry(k, v) for k, v in forced_field_types.items()), "bad forced_field_types argument") if not include_ancillary_info: from ticdat import TicDatFactory tdf = TicDatFactory(**{ t: [[], pks + dfs] for t, (pks, dfs) in self.tdf.schema().items() }) for t, dts in self.tdf.data_types.items(): for f, dt in dts.items(): tdf.set_data_type(t, f, *dt) forced_field_types_ = { (t, f): "text" for t, (pks, dfs) in self.tdf.schema().items() for f in pks if f not in tdf.data_types.get(t, {}) } forced_field_types_.update(forced_field_types) return PostgresTicFactory(tdf).write_schema( engine, schema, forced_field_types_) verify(not getattr(self.tdf, "generic_tables", None), "TicDat for postgres does not yet support generic tables") if schema not in [ row[0] for row in engine.execute( "select schema_name from information_schema.schemata") ]: engine.execute(sa.schema.CreateSchema(schema)) for str in self._get_schema_sql(self.tdf.all_tables, schema, forced_field_types): engine.execute(str)
def test_true_false(self): if not self.can_run: return tdf = TicDatFactory(table=[["pkf"], ["df1", "df2"]]) tdf.set_data_type("table", "df2", min=-float("inf")) dat = tdf.TicDat(table=[["d1", True, 100], ["d2", False, 200], ["d3", False, -float("inf")]]) self.assertTrue(len(dat.table) == 3) self.assertFalse(tdf.find_data_type_failures(dat)) pgtf = tdf.pgsql ex = None try: pgtf.write_data(None, self.engine, test_schema) except utils.TicDatError as te: ex = str(te) self.assertTrue(ex and "Not a valid TicDat object" in ex) pgtf.write_schema(self.engine, test_schema, forced_field_types={("table", "df1"): "bool"}) pgtf.write_data(dat, self.engine, test_schema) self.assertFalse(pgtf.find_duplicates(self.engine, test_schema)) pg_tic_dat = pgtf.create_tic_dat(self.engine, test_schema) self.assertTrue(tdf._same_data(dat, pg_tic_dat))
def test_ints_and_strings_and_lists(self): if not self.can_run: return tdf = TicDatFactory(t_one=[[], ["str_field", "int_field"]], t_two=[["str_field", "int_field"], []]) for t in tdf.all_tables: tdf.set_data_type(t, "str_field", strings_allowed=['This', 'That'], number_allowed=False) tdf.set_data_type(t, "int_field", must_be_int=True) dat = tdf.TicDat(t_one=[["This", 1], ["That", 2], ["This", 111], ["That", 211]], t_two=[["This", 10], ["That", 9]]) self.assertFalse(tdf.find_data_type_failures(dat)) self.assertTrue(len(dat.t_one) == 4) self.assertTrue(len(dat.t_two) == 2) pgtf = tdf.pgsql pgtf.write_schema(self.engine, test_schema) pgtf.write_data(dat, self.engine, test_schema) self.assertFalse(pgtf.find_duplicates(self.engine, test_schema)) pg_tic_dat = pgtf.create_tic_dat(self.engine, test_schema) self.assertTrue(tdf._same_data(dat, pg_tic_dat))
def testNullsAndInf(self): tdf = TicDatFactory(table=[["field one"], ["field two"]]) for f in ["field one", "field two"]: tdf.set_data_type("table", f, nullable=True) dat = tdf.TicDat( table=[[None, 100], [200, 109], [0, 300], [300, None], [400, 0]]) schema = test_schema + "_bool_defaults" tdf.pgsql.write_schema(self.engine, schema, include_ancillary_info=False) tdf.pgsql.write_data(dat, self.engine, schema) dat_1 = tdf.pgsql.create_tic_dat(self.engine, schema) self.assertTrue(tdf._same_data(dat, dat_1)) tdf = TicDatFactory(table=[["field one"], ["field two"]]) for f in ["field one", "field two"]: tdf.set_data_type("table", f, max=float("inf"), inclusive_max=True) tdf.set_infinity_io_flag(None) dat_inf = tdf.TicDat(table=[[float("inf"), 100], [200, 109], [0, 300], [300, float("inf")], [400, 0]]) dat_1 = tdf.pgsql.create_tic_dat(self.engine, schema) self.assertTrue(tdf._same_data(dat_inf, dat_1)) tdf.pgsql.write_data(dat_inf, self.engine, schema) dat_1 = tdf.pgsql.create_tic_dat(self.engine, schema) self.assertTrue(tdf._same_data(dat_inf, dat_1)) tdf = TicDatFactory(table=[["field one"], ["field two"]]) for f in ["field one", "field two"]: tdf.set_data_type("table", f, min=-float("inf"), inclusive_min=True) tdf.set_infinity_io_flag(None) dat_1 = tdf.pgsql.create_tic_dat(self.engine, schema) self.assertFalse(tdf._same_data(dat_inf, dat_1)) dat_inf = tdf.TicDat(table=[[float("-inf"), 100], [200, 109], [0, 300], [300, -float("inf")], [400, 0]]) self.assertTrue(tdf._same_data(dat_inf, dat_1))
import gurobipy as gu from ticdat import TicDatFactory, standard_main, gurobi_env # ------------------------ define the input schema -------------------------------- input_schema = TicDatFactory( workers=[["Name"], ["Payment"]], shifts=[["Name"], ["Requirement"]], availability=[["Worker", "Shift"], []] ) # Define the foreign key relationships input_schema.add_foreign_key("availability", "workers", ['Worker', 'Name']) input_schema.add_foreign_key("availability", "shifts", ['Shift', 'Name']) # Define the data types input_schema.set_data_type("workers", "Payment", min=0, max=float("inf"), inclusive_min=True, inclusive_max=True) input_schema.set_data_type("shifts", "Requirement", min=0, max=float("inf"), inclusive_min=True, inclusive_max=True) # --------------------------------------------------------------------------------- # ------------------------ define the output schema ------------------------------- solution_schema = TicDatFactory( assignments=[["Worker", "Shift"], []], slacks = [["Shift"], ["Slack"]], total_shifts=[["Worker"], ["Total Number Of Shifts"]], parameters=[["Parameter"], ["Value"]] ) # --------------------------------------------------------------------------------- # ------------------------ solving section----------------------------------------- def solve(dat):
# should be 2360.0 obj value from ticdat import TicDatFactory, standard_main from amplpy import AMPL from ticdat.utils import verify input_schema = TicDatFactory ( roster = [["Name"],["Grade"]], positions = [["Position"],["Position Importance", "Position Group"]], player_ratings = [["Name", "Position Group"], ["Rating"]], innings = [["Inning"],["Inning Group"]], position_constraints = [["Position Group", "Inning Group", "Grade"],["Min Players", "Max Players"]]) input_schema.set_data_type("positions", "Position Importance",min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) # input_schema.add_foreign_key("player_ratings", "roster", ["Name", "Name"]) # input_schema.add_foreign_key("player_ratings", "positions", ["Position Group", "Position Group"]) input_schema.set_data_type("player_ratings", "Rating", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) # input_schema.add_foreign_key("position_constraints", "positions", ["Position Group", "Position Group"]) # input_schema.add_foreign_key("position_constraints", "innings", ["Inning Group", "Inning Group"]) # input_schema.add_foreign_key("position_constraints", "roster", ["Grade", "Grade"]) input_schema.set_data_type("position_constraints", "Min Players", min=0, max=float("inf"), inclusive_min=True, inclusive_max=True) input_schema.set_data_type("position_constraints", "Max Players", min=0, max=float("inf"), inclusive_min=False, inclusive_max=True) input_schema.add_data_row_predicate( "position_constraints", predicate_name="Position Min Max Check",
default_value=float("inf"), inclusive_min=False, inclusive_max=True, min=0, max=float("inf"), must_be_int=False) input_schema.add_parameter("Objective", "Minimize Average Service Distance", strings_allowed=[ "Minimize Average Service Distance", "Maximize Percent High Service Demand" ], number_allowed=False) input_schema.set_data_type("cities", "Demand", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.set_data_type("distances", "Distance", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.add_foreign_key("distances", "cities", ['Source', 'Name']) input_schema.add_foreign_key("distances", "cities", ['Destination', 'Name']) # The distance matrix is bi-directionally safe. I.e. if the same source/dest and dest/source exist then the # distances must match. If only one is present, it can fall back to the other in the code. def _distance_matrix(dat):
try: # if you don't have gurobipy installed, the code will still load and then fail on solve import gurobipy as gu except: gu = None from ticdat import TicDatFactory, standard_main from itertools import product # ------------------------ define the input schema -------------------------------- input_schema = TicDatFactory ( parameters=[["Parameter"], ["Value"]], load_amounts=[["Amount"],[]], number_of_one_way_trips=[["Number"],[]], amount_leftover=[["Amount"], []]) input_schema.set_data_type("load_amounts", "Amount", min=0, max=float("inf"), inclusive_min=False, inclusive_max=False) input_schema.set_data_type("number_of_one_way_trips", "Number", min=0, max=float("inf"), inclusive_min=False, inclusive_max=False, must_be_int=True) input_schema.set_data_type("amount_leftover", "Amount", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.add_parameter("One Way Price", default_value=2.25, min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.add_parameter("Amount Leftover Constraint", default_value="Upper Bound", number_allowed=False, strings_allowed=["Equality", "Upper Bound", "Upper Bound With Leftover Multiple Rule"]) # --------------------------------------------------------------------------------- # ------------------------ define the output schema -------------------------------
# python soda_promotion_optimizer.py -i input_data.xlsx -o solution_data.xlsx # will read from a model stored in the file input_data.xlsx and write the solution # to solution_data.xlsx. from ticdat import TicDatFactory, standard_main, Slicer, gurobi_env import gurobipy as gu input_schema = TicDatFactory(parameters=[["Key"], ["Value"]], products=[["Name"], ["Family"]], forecast_sales=[["Product", "Cost Per Unit"], ["Sales"]], max_promotions=[["Product Family"], ["Max Promotions"]]) input_schema.set_data_type("parameters", "Key", number_allowed=False, strings_allowed=("Maximum Total Investment", )) input_schema.set_data_type("parameters", "Value", min=0, max=float("inf"), inclusive_max=True) input_schema.set_data_type("forecast_sales", "Cost Per Unit", min=0, max=float("inf")) input_schema.set_data_type("max_promotions", "Product Family", number_allowed=False, strings_allowed=("Dark", "Clear")) input_schema.set_data_type("max_promotions",
["Min Nutrition", "Max Nutrition"]], foods=[["Name"], ["Cost"]], nutrition_quantities=[["Food", "Category"], ["Quantity"]]) for (tbl, fld), tip in input_schema_tooltips.items(): input_schema.set_tooltip(tbl, fld, tip) # Define the foreign key relationships input_schema.add_foreign_key("nutrition_quantities", "foods", ["Food", "Name"]) input_schema.add_foreign_key("nutrition_quantities", "categories", ["Category", "Name"]) # Define the data types input_schema.set_data_type("categories", "Min Nutrition", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.set_data_type("categories", "Max Nutrition", min=0, max=float("inf"), inclusive_min=True, inclusive_max=True) input_schema.set_data_type("foods", "Cost", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.set_data_type("nutrition_quantities",
input_schema = TicDatFactory ( parameters = [["Key"],["Value"]], players = [['Player Name'], ['Position', 'Average Draft Position', 'Expected Points', 'Draft Status']], roster_requirements = [['Position'], ['Min Num Starters', 'Max Num Starters', 'Min Num Reserve', 'Max Num Reserve', 'Flex Status']], my_draft_positions = [['Draft Position'],[]] ) # add foreign key constraints (optional, but helps with preventing garbage-in, garbage-out) input_schema.add_foreign_key("players", "roster_requirements", ['Position', 'Position']) # set data types (optional, but helps with preventing garbage-in, garbage-out) input_schema.set_data_type("parameters", "Key", number_allowed = False, strings_allowed = ["Starter Weight", "Reserve Weight", "Maximum Number of Flex Starters"]) input_schema.set_data_type("parameters", "Value", min=0, max=float("inf"), inclusive_min = True, inclusive_max = False) input_schema.set_data_type("players", "Average Draft Position", min=0, max=float("inf"), inclusive_min = False, inclusive_max = False) input_schema.set_data_type("players", "Expected Points", min=-float("inf"), max=float("inf"), inclusive_min = False, inclusive_max = False) input_schema.set_data_type("players", "Draft Status", strings_allowed = ["Un-drafted", "Drafted By Me", "Drafted By Someone Else"]) for fld in ("Min Num Starters", "Min Num Reserve", "Max Num Reserve"): input_schema.set_data_type("roster_requirements", fld, min=0, max=float("inf"), inclusive_min = True, inclusive_max = False, must_be_int = True) input_schema.set_data_type("roster_requirements", "Max Num Starters", min=0, max=float("inf"), inclusive_min = False, inclusive_max = True, must_be_int = True) input_schema.set_data_type("roster_requirements", "Flex Status", number_allowed = False,
def testDateTime(self): schema = test_schema + "_datetime" tdf = TicDatFactory(table_with_stuffs=[["field one"], ["field two"]], parameters=[["a"], ["b"]]) tdf.add_parameter("p1", "Dec 15 1970", datetime=True) tdf.add_parameter("p2", None, datetime=True, nullable=True) tdf.set_data_type("table_with_stuffs", "field one", datetime=True) tdf.set_data_type("table_with_stuffs", "field two", datetime=True, nullable=True) dat = tdf.TicDat(table_with_stuffs=[[ dateutil.parser.parse("July 11 1972"), None ], [datetime.datetime.now(), dateutil.parser.parse("Sept 11 2011")]], parameters=[["p1", "7/11/1911"], ["p2", None]]) self.assertFalse( tdf.find_data_type_failures(dat) or tdf.find_data_row_failures(dat)) tdf.pgsql.write_schema(self.engine, schema) tdf.pgsql.write_data(dat, self.engine, schema) dat_1 = tdf.pgsql.create_tic_dat(self.engine, schema) self.assertFalse( tdf._same_data(dat, dat_1, nans_are_same_for_data_rows=True)) self.assertTrue( all( len(getattr(dat, t)) == len(getattr(dat_1, t)) for t in tdf.all_tables)) self.assertFalse( tdf.find_data_type_failures(dat_1) or tdf.find_data_row_failures(dat_1)) self.assertTrue( isinstance(dat_1.parameters["p1"]["b"], datetime.datetime)) self.assertTrue( all( isinstance(_, datetime.datetime) for _ in dat_1.table_with_stuffs)) self.assertTrue( len([_ for _ in dat_1.table_with_stuffs if pd.isnull(_)]) == 0) self.assertTrue( all( isinstance(_, datetime.datetime) or pd.isnull(_) for v in dat_1.table_with_stuffs.values() for _ in v.values())) self.assertTrue( len([ _ for v in dat_1.table_with_stuffs.values() for _ in v.values() if pd.isnull(_) ]) == 1) pdf = PanDatFactory.create_from_full_schema( tdf.schema(include_ancillary_info=True)) pan_dat = pdf.pgsql.create_pan_dat(self.engine, schema) dat_2 = pdf.copy_to_tic_dat(pan_dat) # pandas can be a real PIA sometimes, hacking around some weird downcasting for k in list(dat_2.table_with_stuffs): dat_2.table_with_stuffs[pd.Timestamp( k)] = dat_2.table_with_stuffs.pop(k) self.assertTrue( tdf._same_data(dat_1, dat_2, nans_are_same_for_data_rows=True)) pdf.pgsql.write_data(pan_dat, self.engine, schema) dat_3 = pdf.copy_to_tic_dat( pdf.pgsql.create_pan_dat(self.engine, schema)) for k in list(dat_3.table_with_stuffs): dat_3.table_with_stuffs[pd.Timestamp( k)] = dat_3.table_with_stuffs.pop(k) self.assertTrue( tdf._same_data(dat_1, dat_3, nans_are_same_for_data_rows=True))
arcs = [["source", "destination"],["capacity"]], cost = [["commodity", "source", "destination"], ["cost"]], inflow = [["commodity", "node"],["quantity"]] ) # add foreign key constraints dataFactory.add_foreign_key("arcs", "nodes", ['source', 'name']) dataFactory.add_foreign_key("arcs", "nodes", ['destination', 'name']) dataFactory.add_foreign_key("cost", "nodes", ['source', 'name']) dataFactory.add_foreign_key("cost", "nodes", ['destination', 'name']) dataFactory.add_foreign_key("cost", "commodities", ['commodity', 'name']) dataFactory.add_foreign_key("inflow", "commodities", ['commodity', 'name']) dataFactory.add_foreign_key("inflow", "nodes", ['node', 'name']) # the whole schema has only three data fields to type dataFactory.set_data_type("arcs", "capacity") dataFactory.set_data_type("cost", "cost") # except quantity which allows negatives dataFactory.set_data_type("inflow", "quantity", min=-float("inf"), inclusive_min=False) solutionFactory = TicDatFactory( flow = [["commodity", "source", "destination"], ["quantity"]]) def create_model(dat): ''' :param dat: a good ticdat for the dataFactory :return: a gurobi model and dictionary of gurboi flow variables ''' assert dataFactory.good_tic_dat_object(dat)
# ------------------------ define the input schema -------------------------------- # There are three input tables, with 4 primary key fields and 4 data fields. input_schema = TicDatFactory(sites=[['Name'], ['Demand', 'Center Status']], distance=[['Source', 'Destination'], ['Distance']], parameters=[["Parameter"], ["Value"]]) # add foreign key constraints input_schema.add_foreign_key("distance", "sites", ['Source', 'Name']) input_schema.add_foreign_key("distance", "sites", ['Destination', 'Name']) # center_status is a flag field which can take one of two string values. input_schema.set_data_type( "sites", "Center Status", number_allowed=False, strings_allowed=["Can Be Center", "Pure Demand Point"]) # The default type of non infinite, non negative works for distance and demand input_schema.set_data_type("sites", "Demand") input_schema.set_data_type("distance", "Distance") input_schema.add_parameter("Number of Centroids", default_value=1, inclusive_min=False, inclusive_max=False, min=0, max=float("inf"), must_be_int=True) input_schema.add_parameter("MIP Gap", default_value=0.001,
# # Note that file requires jobs.mod to be in the same directory from ticdat import TicDatFactory, standard_main, opl_run import time import datetime # ------------------------ define the input schema -------------------------------- # There are three input tables, with 4 primary key fields and 4 data fields. input_schema = TicDatFactory( parameters=[["Parameter"], ["Value"]], machines=[["Name"], []], jobs=[["Name"], ["Machine1", "Durations1", "Machine2", "Durations2"]]) input_schema.set_data_type("parameters", "Parameter", number_allowed=False, strings_allowed=("Load Duration", )) input_schema.set_data_type("parameters", "Value", min=0, max=float("inf"), inclusive_max=False) input_schema.add_foreign_key("jobs", "machines", ["Machine1", "Name"]) input_schema.add_foreign_key("jobs", "machines", ["Machine2", "Name"]) input_schema.set_data_type("jobs", "Durations1", min=0, max=float("inf"), inclusive_max=False,
# ------------------------ define the input schema -------------------------------- # There are three input tables, with 4 primary key fields and 4 data fields. input_schema = TicDatFactory(sites=[['Name'], ['Demand', 'Center Status']], distance=[['Source', 'Destination'], ['Distance']], parameters=[["Parameter"], ["Value"]]) # add foreign key constraints input_schema.add_foreign_key("distance", "sites", ['Source', 'Name']) input_schema.add_foreign_key("distance", "sites", ['Destination', 'Name']) # center_status is a flag field which can take one of two string values. input_schema.set_data_type( "sites", "Center Status", number_allowed=False, strings_allowed=["Can Be Center", "Pure Demand Point"]) # The default type of non infinite, non negative works for distance input_schema.set_data_type("distance", "Distance") # There are three types of parameters input_schema.set_data_type( "parameters", "Parameter", number_allowed=False, strings_allowed=["Number of Centroids", "MIP Gap", "Formulation"]) input_schema.set_data_type("parameters", "Value", number_allowed=True, strings_allowed=["Weak", "Strong"])
# will read from a model stored in the file input_data.xlsx and write the solution # to solution_data.xlsx. # Note that this file requires oil_blend.mod to be in the same directory from ticdat import TicDatFactory, standard_main, opl_run # ------------------------ define the input schema -------------------------------- input_schema = TicDatFactory ( parameters = [["Parameter"],["Value"]], gas = [["Name"],["Demand","Sales Price","Min Octane Rating", "Max Lead Contents"]], oil = [["Name"],["Supply","Purchase Price","Octane Rating", "Lead Contents"]]) # no foreign keys input_schema.set_data_type("parameters", "Parameter", number_allowed = False, strings_allowed= ["Maximum Production", "Production Cost"]) input_schema.set_data_type("parameters", "Value", min=0, max=float("inf"), inclusive_min=True, inclusive_max=True) input_schema.set_data_type("gas", "Demand", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.set_data_type("gas", "Sales Price", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.set_data_type("gas", "Min Octane Rating", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.set_data_type("gas", "Max Lead Contents", min=0, max=float("inf"), inclusive_min=True, inclusive_max=True) input_schema.set_data_type("oil", "Supply", min=0, max=float("inf"), inclusive_min=True, inclusive_max=True) input_schema.set_data_type("oil", "Purchase Price", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.set_data_type("oil", "Octane Rating", min=0, max=float("inf"),
# solution to metrorail_solution_data.json. # this version of the file uses amplpy and Gurobi from amplpy import AMPL from ticdat import TicDatFactory, standard_main, ampl_format from itertools import product # ------------------------ define the input schema -------------------------------- input_schema = TicDatFactory(parameters=[["Key"], ["Value"]], load_amounts=[["Amount"], []], number_of_one_way_trips=[["Number"], []], amount_leftover=[["Amount"], []]) input_schema.set_data_type("load_amounts", "Amount", min=0, max=float("inf"), inclusive_min=False, inclusive_max=False) input_schema.set_data_type("number_of_one_way_trips", "Number", min=0, max=float("inf"), inclusive_min=False, inclusive_max=False, must_be_int=True) input_schema.set_data_type("amount_leftover", "Amount", min=0, max=float("inf"),
arcs = [["Source", "Destination"],["Capacity"]], cost = [["Commodity", "Source", "Destination"], ["Cost"]], inflow = [["Commodity", "Node"],["Quantity"]] ) # Define the foreign key relationships input_schema.add_foreign_key("arcs", "nodes", ['Source', 'Name']) input_schema.add_foreign_key("arcs", "nodes", ['Destination', 'Name']) input_schema.add_foreign_key("cost", "nodes", ['Source', 'Name']) input_schema.add_foreign_key("cost", "nodes", ['Destination', 'Name']) input_schema.add_foreign_key("cost", "commodities", ['Commodity', 'Name']) input_schema.add_foreign_key("inflow", "commodities", ['Commodity', 'Name']) input_schema.add_foreign_key("inflow", "nodes", ['Node', 'Name']) # Define the data types input_schema.set_data_type("arcs", "Capacity", min=0, max=float("inf"), inclusive_min=True, inclusive_max=True) input_schema.set_data_type("cost", "Cost", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.set_data_type("inflow", "Quantity", min=-float("inf"), max=float("inf"), inclusive_min=False, inclusive_max=False) # The default-default of zero makes sense everywhere except for Capacity input_schema.set_default_value("arcs", "Capacity", float("inf")) # --------------------------------------------------------------------------------- # ------------------------ define the output schema ------------------------------- solution_schema = TicDatFactory( flow = [["Commodity", "Source", "Destination"], ["Quantity"]], parameters = [["Key"],["Value"]]) # ---------------------------------------------------------------------------------
def _testFantop(modelType, sqlFile): dataFactory = TicDatFactory(parameters=[["Key"], ["Value"]], players=[['Player Name'], [ 'Position', 'Average Draft Position', 'Expected Points', 'Draft Status' ]], roster_requirements=[['Position'], [ 'Min Num Starters', 'Max Num Starters', 'Min Num Reserve', 'Max Num Reserve', 'Flex Status' ]], my_draft_positions=[['Draft Position'], []]) # add foreign key constraints (optional, but helps with preventing garbage-in, garbage-out) dataFactory.add_foreign_key("players", "roster_requirements", ['Position', 'Position']) # set data types (optional, but helps with preventing garbage-in, garbage-out) dataFactory.set_data_type("parameters", "Key", number_allowed=False, strings_allowed=[ "Starter Weight", "Reserve Weight", "Maximum Number of Flex Starters" ]) dataFactory.set_data_type("parameters", "Value", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) dataFactory.set_data_type("players", "Average Draft Position", min=0, max=float("inf"), inclusive_min=False, inclusive_max=False) dataFactory.set_data_type("players", "Expected Points", min=-float("inf"), max=float("inf"), inclusive_min=False, inclusive_max=False) dataFactory.set_data_type("players", "Draft Status", strings_allowed=[ "Un-drafted", "Drafted By Me", "Drafted By Someone Else" ]) for fld in ("Min Num Starters", "Min Num Reserve", "Max Num Reserve"): dataFactory.set_data_type("roster_requirements", fld, min=0, max=float("inf"), inclusive_min=True, inclusive_max=False, must_be_int=True) dataFactory.set_data_type("roster_requirements", "Max Num Starters", min=0, max=float("inf"), inclusive_min=False, inclusive_max=True, must_be_int=True) dataFactory.set_data_type( "roster_requirements", "Flex Status", number_allowed=False, strings_allowed=["Flex Eligible", "Flex Ineligible"]) dataFactory.set_data_type("my_draft_positions", "Draft Position", min=0, max=float("inf"), inclusive_min=False, inclusive_max=False, must_be_int=True) solutionFactory = TicDatFactory(my_draft=[[ 'Player Name' ], [ 'Draft Position', 'Position', 'Planned Or Actual', 'Starter Or Reserve' ]]) dat = dataFactory.sql.create_tic_dat_from_sql(os.path.join( _codeDir(), sqlFile), freeze_it=True) assert dataFactory.good_tic_dat_object(dat) assert not dataFactory.find_foreign_key_failures(dat) assert not dataFactory.find_data_type_failures(dat) expected_draft_position = {} # for our purposes, its fine to assume all those drafted by someone else are drafted # prior to any players drafted by me for player_name in sorted( dat.players, key=lambda _p: { "Un-drafted": dat.players[_p]["Average Draft Position"], "Drafted By Me": -1, "Drafted By Someone Else": -2 }[dat.players[_p]["Draft Status"]]): expected_draft_position[player_name] = len(expected_draft_position) + 1 assert max(expected_draft_position.values()) == len( set(expected_draft_position.values())) == len(dat.players) assert min(expected_draft_position.values()) == 1 already_drafted_by_me = { player_name for player_name, row in dat.players.items() if row["Draft Status"] == "Drafted By Me" } can_be_drafted_by_me = { player_name for player_name, row in dat.players.items() if row["Draft Status"] != "Drafted By Someone Else" } m = Model(modelType, 'fantop') my_starters = { player_name: m.add_var(type="binary", name="starter_%s" % player_name) for player_name in can_be_drafted_by_me } my_reserves = { player_name: m.add_var(type="binary", name="reserve_%s" % player_name) for player_name in can_be_drafted_by_me } for player_name in can_be_drafted_by_me: if player_name in already_drafted_by_me: m.add_constraint(my_starters[player_name] + my_reserves[player_name] == 1, name="already_drafted_%s" % player_name) else: m.add_constraint( my_starters[player_name] + my_reserves[player_name] <= 1, name="cant_draft_twice_%s" % player_name) for i, draft_position in enumerate(sorted(dat.my_draft_positions)): m.add_constraint(m.sum( my_starters[player_name] + my_reserves[player_name] for player_name in can_be_drafted_by_me if expected_draft_position[player_name] < draft_position) <= i, name="at_most_%s_can_be_ahead_of_%s" % (i, draft_position)) my_draft_size = m.sum(my_starters[player_name] + my_reserves[player_name] for player_name in can_be_drafted_by_me) m.add_constraint(my_draft_size >= len(already_drafted_by_me) + 1, name="need_to_extend_by_at_least_one") m.add_constraint(my_draft_size <= len(dat.my_draft_positions), name="cant_exceed_draft_total") for position, row in dat.roster_requirements.items(): players = { player_name for player_name in can_be_drafted_by_me if dat.players[player_name]["Position"] == position } starters = m.sum(my_starters[player_name] for player_name in players) reserves = m.sum(my_reserves[player_name] for player_name in players) m.add_constraint(starters >= row["Min Num Starters"], name="min_starters_%s" % position) m.add_constraint(starters <= row["Max Num Starters"], name="max_starters_%s" % position) m.add_constraint(reserves >= row["Min Num Reserve"], name="min_reserve_%s" % position) m.add_constraint(reserves <= row["Max Num Reserve"], name="max_reserve_%s" % position) if "Maximum Number of Flex Starters" in dat.parameters: players = { player_name for player_name in can_be_drafted_by_me if dat.roster_requirements[dat.players[player_name]["Position"]] ["Flex Status"] == "Flex Eligible" } m.add_constraint( m.sum(my_starters[player_name] for player_name in players) <= dat.parameters["Maximum Number of Flex Starters"]["Value"], name="max_flex") starter_weight = dat.parameters["Starter Weight"][ "Value"] if "Starter Weight" in dat.parameters else 1 reserve_weight = dat.parameters["Reserve Weight"][ "Value"] if "Reserve Weight" in dat.parameters else 1 m.set_objective(m.sum(dat.players[player_name]["Expected Points"] * (my_starters[player_name] * starter_weight + my_reserves[player_name] * reserve_weight) for player_name in can_be_drafted_by_me), sense="maximize") if not m.optimize(): return sln = solutionFactory.TicDat() def almostone(x): return abs(m.get_solution_value(x) - 1) < 0.0001 picked = sorted([ player_name for player_name in can_be_drafted_by_me if almostone(my_starters[player_name]) or almostone(my_reserves[player_name]) ], key=lambda _p: expected_draft_position[_p]) assert len(picked) <= len(dat.my_draft_positions) if len(picked) < len(dat.my_draft_positions): print( "Your model is over-constrained, and thus only a partial draft was possible" ) draft_yield = 0 for player_name, draft_position in zip(picked, sorted(dat.my_draft_positions)): draft_yield += dat.players[player_name]["Expected Points"] * \ (starter_weight if almostone(my_starters[player_name]) else reserve_weight) assert draft_position <= expected_draft_position[player_name] sln.my_draft[player_name]["Draft Position"] = draft_position sln.my_draft[player_name]["Position"] = dat.players[player_name][ "Position"] sln.my_draft[player_name][ "Planned Or Actual"] = "Actual" if player_name in already_drafted_by_me else "Planned" sln.my_draft[player_name]["Starter Or Reserve"] = \ "Starter" if almostone(my_starters[player_name]) else "Reserve" return sln, draft_yield
from ticdat import TicDatFactory, Progress, LogFile, Slicer, standard_main # ------------------------ define the input schema -------------------------------- # There are three input tables, with 4 primary key fields and 4 data fields. dataFactory = TicDatFactory(sites=[['name'], ['demand', 'center_status']], distance=[['source', 'destination'], ['distance']], parameters=[["key"], ["value"]]) # add foreign key constraints dataFactory.add_foreign_key("distance", "sites", ['source', 'name']) dataFactory.add_foreign_key("distance", "sites", ['destination', 'name']) # center_status is a flag field which can take one of two string values. dataFactory.set_data_type( "sites", "center_status", number_allowed=False, strings_allowed=["Can Be Center", "Pure Demand Point"]) # The default type of non infinite, non negative works for distance dataFactory.set_data_type("distance", "distance") # --------------------------------------------------------------------------------- # ------------------------ define the output schema ------------------------------- # There are three solution tables, with 2 primary key fields and 3 # data fields amongst them. solutionFactory = TicDatFactory(openings=[['site'], []], assignments=[['site', 'assigned_to'], []], parameters=[["key"], ["value"]]) # ---------------------------------------------------------------------------------
"Min Innings Played", "Max Innings Played" ]], positions=[["Position"], [ "Position Importance", "Position Group", "Consecutive Innings Only" ]], player_ratings=[["Name", "Position Group"], ["Rating"]], innings=[["Inning"], ["Inning Group"]], position_constraints=[["Position Group", "Inning Group", "Grade"], ["Min Players", "Max Players"]], parameters=[["Key"], ["Value"]]) input_schema.set_data_type("roster", "Min Innings Played", min=0, max=9, inclusive_min=True, inclusive_max=True) input_schema.set_data_type("roster", "Max Innings Played", min=0, max=9, inclusive_min=False, inclusive_max=True) input_schema.add_data_row_predicate( "roster", predicate_name="Roster Min Max Check", predicate=lambda row: row["Max Innings Played"] >= row["Min Innings Played" ]) input_schema.add_foreign_key("roster", "innings", ["Arrival Inning", "Inning"]) input_schema.add_foreign_key("roster", "innings",