Groupby constraint Gurobipy Pandas
Answered
Hi Everyone,
I have defined a decision variable x_ik using the pandas and gppd.add_vars() method. Below are the top 5 rows for a minimal example.
week_id x
resource_id store_num
A 27 2023_12 <gurobi.Var x[A,27]>
B 31 2023_15 <gurobi.Var x[B,31]>
C 185 2023_32 <gurobi.Var x[C,185]>
D 485 2023_08 <gurobi.Var x[D,485]>
E 52 2024_04 <gurobi.Var x[E,52]>
I want to add a constraint to enforce that on a given week a resource cannot be assigned to more than one store such as the one below where index i correspons to resource_id and index k corresponds to store_num.
How can we do this in gurobipy_pandas using the gppd.add_constrs()? I appreciate your help in advance.
Vusal
-
Hi Vusal,
Given your explanation, it seems to me that the decision variables should be defined as \(x_{ijk}\) being equal to 1 if resource \(i\) is assigned to store \(j\) at week \(k\). You can then use the gurobipy-pandas package as below to define the variables \(x\) and the constraints that ensure a resource cannot be assigned to more than one store at a given week.
resource_id = ["A", "B", "C", "D", "E"]
store_num = [27, 31, 185, 485, 52]
week_id = ["2023_12", "2023_15", "2023_32", "2023_08", "2024_04"]
model = gp.Model()
x = gppd.add_vars(
model,
pd.MultiIndex.from_product(
(resource_id, store_num, week_id),
names=("resource_id", "store_num", "week_id"),
),
vtype=GRB.BINARY,
name="x",
)
gppd.add_constrs(
model,
x.groupby(["resource_id", "week_id"]).sum(),
GRB.LESS_EQUAL,
1,
name="c1",
)Best regards,Maliheh0 -
Hi Maliheh,
Not really, since k is pre-defined (i.e., fixed) in reality, there was no point in adding week_id to the index list. So, I defined the variables as x_ik for specific i and k pairs and stored in the pandas dataframe df.
total_assignments = (df.groupby(['week_id','resource_id'])['x'].sum())
allocate_once = gppd.add_constrs(model, total_assignments, GRB.LESS_EQUAL, 1, name="allocate_once")Would this groupby expression work?
Thanks.
0 -
since k is pre-defined (i.e., fixed) in reality, there was no point in adding week_id to the index list
I do not understand this. The number of resources and stores are also fixed.
Do you have rows in your dataframe that have the same \(\texttt{resource_id}\) and \(\texttt{week_id}\) but different \(\texttt{store_num}\)?
Let us assume the \(\texttt{df}\) as below (I added one additional row to your example dataframe):
week_id x
resource_id store_num
A 27 2023_12 <gurobi.Var x[A,27]>
B 31 2023_15 <gurobi.Var x[B,31]>
C 185 2023_32 <gurobi.Var x[C,185]>
D 485 2023_08 <gurobi.Var x[D,485]>
E 52 2024_04 <gurobi.Var x[E,52]>
A 42 2023_12 <gurobi.Var x[A,42]>The statements
total_assignments = (df.groupby(['week_id','resource_id'])['x'].sum()) allocate_once = gppd.add_constrs(model, total_assignments, GRB.LESS_EQUAL, 1, name="allocate_once")
add constraints in the form below to the model:
x[D,485] <= 1.0
x[A,27] + x[A,42] <= 1.0
x[B,31] <= 1.0
x[C,185] <= 1.0
x[E,52] <= 1.0Best regards,
Maliheh
0 -
What I mean is that the model is useful for assigning resources to stores. Each store has a pre-defined week for the assignment. If we know, which store a resource is assigned to, we will know what the week of the assignment is because it is already predefined by decision-makers.
Because there are many stores, and predefined assignment weeks can sometimes overlap, I need a constraint to ensure that a person can't be assigned to two stores on a given week. As you mentioned, in my df, I have X variables defined for more than one store_num for a given resource_id and week_id. Thus, a constraint is needed to ensure only one assignment happens. Let me know what you think..
0 -
x[D,485] <= 1.0 x[A,27] + x[A,42] <= 1.0 x[B,31] <= 1.0 x[C,185] <= 1.0 x[E,52] <= 1.0
In reality, I only need to add this one
x[A,27] + x[A,42] <= 1.0
and the rest of the constraints are redundant because there is no more than one store_num for the same resource_id and week_id for those. Let me know if you can come up with a more efficient version.
0 -
I would not be worried too much about the redundant constraints if they are not too many. Anyways, the script below should avoid adding the redundant constraints:
total_assignments= (
df.groupby(["week_id", "resource_id"])
.filter(lambda x: len(x["x"]) > 1)
.groupby(["week_id", "resource_id"])["x"]
.sum()
)Maliheh0
Please sign in to leave a comment.
Comments
6 comments