def testCircularFks(self): schema = test_schema + "circular_fks" tdf = TicDatFactory(table_one=[["A Field"], []], table_two=[["B Field"], []], table_three=[["C Field"], []]) tdf.add_foreign_key("table_one", "table_two", ["A Field", "B Field"]) tdf.add_foreign_key("table_two", "table_three", ["B Field", "C Field"]) tdf.add_foreign_key("table_three", "table_one", ["C Field", "A Field"]) tdf.pgsql.write_schema(self.engine, schema, include_ancillary_info=False) t_ = [["a"], ["b"], ["c"]] dat = tdf.TicDat(table_one=t_, table_two=t_, table_three=t_) tdf.pgsql.write_data(dat, self.engine, schema)
# this version of the file uses CPLEX import time import datetime import os from docplex.mp.model import Model 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.
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", "Max Promotions", min=0, max=float("inf"), inclusive_max=False, must_be_int=True) input_schema.add_foreign_key("products", "max_promotions", ["Family", "Product Family"]) input_schema.add_foreign_key("forecast_sales", "products", ["Product", "Name"]) solution_schema = TicDatFactory( parameters=[["Key"], ["Value"]], product_pricing=[["Product"], ["Cost Per Unit", "Family", "Promotional Status"]]) def solve(dat): assert input_schema.good_tic_dat_object(dat) assert not input_schema.find_foreign_key_failures(dat) assert not input_schema.find_data_type_failures(dat) normal_price = {pdct: 0 for pdct in dat.products} for pdct, price in dat.forecast_sales:
# will read from a model stored in .csv files in the csv_data directory # and write the solution to .csv files in the solution_csv_data directory from ticdat import TicDatFactory, standard_main, Model, Slicer # ------------------------ define the input schema -------------------------------- input_schema = TicDatFactory ( commodities = [["Name"],[]], nodes = [["Name"],[]], 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)
from ticdat import TicDatFactory from tts_diet.tooltips import input_schema_tooltips, solution_schema_tooltips # ------------------------ define the input schema -------------------------------- # There are three input tables, with 4 primary key fields and 4 data fields. input_schema = TicDatFactory(categories=[["Name"], ["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,
# this version of the file uses Gurobi import gurobipy as gu # ------------------------ define the input schema -------------------------------- 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"),
# to solution_data.xlsx. # # Note that file requires diet.lng to be in the same directory from ticdat import TicDatFactory, standard_main, lingo_run # ------------------------ define the input schema -------------------------------- # There are three input tables, with 4 primary key fields and 4 data fields. input_schema = TicDatFactory(categories=[["Name"], ["Min Nutrition", "Max Nutrition"]], foods=[["Name"], ["Cost"]], nutrition_quantities=[["Food", "Category"], ["Quantity"]]) # add foreign key constraints 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,
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, must_be_int=True) input_schema.set_data_type("jobs", "Durations2", min=0, max=float("inf"), inclusive_max=False, must_be_int=True)
# the sum of the slacks, and minimize the total payment to workers. # Finally, we minimize a quadratic objective that # tries to balance the workload among the workers. # 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"]]
from ticdat import TicDatFactory, dat_restricted, sln_restricted input_schema = TicDatFactory ( categories = [["Name"],["Min Nutrition", "Max Nutrition"]], foods = [["Name"],["Cost"]], nutrition_quantities = [["Food", "Category"], ["Quantity"]], stupid_table = [["Fo", "Cat"],["Junk"]]) input_schema.add_foreign_key("nutrition_quantities", "foods", ["Food", "Name"]) input_schema.add_foreign_key("stupid_table", "nutrition_quantities", [["Fo", "Food"],["Cat", "Category"]]) input_schema.add_foreign_key("nutrition_quantities", "categories", ["Category", "Name"]) 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", "Quantity", min=0, max=float("inf"), inclusive_min=True, inclusive_max=False) input_schema.add_data_row_predicate( "categories", predicate_name="Min Max Check", predicate=lambda row : row["Max Nutrition"] >= row["Min Nutrition"]) input_schema.set_default_value("categories", "Max Nutrition", float("inf")) solution_schema = TicDatFactory( parameters = [["Parameter"],["Value"]], buy_food = [["Food"],["Quantity"]], consume_nutrition = [["Category"],["Quantity"]], weird_table = [["This","That"],["Thother"]]) hard_coded_solution_dict = { # there is a deliberate flaw in the data - we are consuming 91.9 not 91.0 units of protein
"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): return { "distance_matrix": {k: v["Distance"] for k, v in dat.distances.items()} } input_schema.add_data_row_predicate( "distances",
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
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"])
# Solve a multi-commodity flow problem. from gurobipy import * from ticdat import TicDatFactory # define the input schema. dataFactory = TicDatFactory ( commodities = [["name"],[]], nodes = [["name"],[]], 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"]])
import time import datetime import os import gurobipy as gu from ticdat import TicDatFactory, Progress, LogFile, Slicer, standard_main, gurobi_env # ------------------------ 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=[["key"], ["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", "key",