result not correct

Answered

ghada ouerfelli

December 07, 2022 22:52

• 
• 
• 

hello when I Change my database the result is 0, I want to read my database a,l, my initial stocks S1 and S2 From excel, I made them in lists but there are some errors that make the result 0

here the program 

and the excel database 

purchase order = a like l

and the initial stock S1 like S2 

import gurobipy as gp
import pandas as pd
import numpy as np
from gurobipy import GRB
#Read Purchase Order from Excel
df=pd.read_excel("purchase order.xlsx")
products=df.Article.values.tolist()
print(products)
m=df.drop(columns='Article',axis=1)
#print(m)
a=m.values.tolist()
print (a)
#a=[[0,0,10,0,0],[0,20,0,0,0],]

#Read Consommation from Excel
df=pd.read_excel("Consommation.xlsx")
b=df.drop(columns='Article',axis=1)
l=b.values.tolist()
print(l)
#l=[[0,2,3,1,2],[0,9,5,1,2],]

#Read initial Local stock from Excel
df=pd.read_excel("initial stock.xlsx")
b=df.drop(columns='Article',axis=1)
S1=b.values.tolist()
print(S1)

#S1[1,0].lb=1
#S1[1,0].ub=1
#S1[2,0].lb=2
#S1[2,0].ub=2



#Read initial Logis stock from Excel
df=pd.read_excel("S2.xlsx")
b=df.drop(columns='Article',axis=1)
S2=b.values.tolist()
print(S2)

#S2[1,0].lb=3
#S2[1,0].ub=3
#S2[2,0].lb=4
#S2[2,0].ub=4


m=gp.Model("Log")

weeks = [0,1,2,3,4]

#products=[1,2]
L=10
k=2



S=m.addVars(products,weeks,name='S')
S1=m.addVars(products,weeks,name='S1')
S2=m.addVars(products,weeks,obj=k,name='S2')


l1=m.addVars(products,weeks,name='l1')
l2=m.addVars(products,weeks,obj=k,name='l2')
l3=m.addVars(products,weeks,obj=k,name='l3')

a1=m.addVars(products,weeks,name='a1')
a2=m.addVars(products,weeks,obj=k,name='a2')


m.addConstrs(S[p,t-1]+a[p-1][t] == l[p-1][t]+S[p,t] for p in products for t in weeks if t>0)
m.addConstrs(S1[p,t-1]+a1[p,t] == l1[p,t]+S1[p,t]-l3[p,t] for p in products for t in weeks if t>0)
m.addConstrs(S2[p,t-1]+a2[p,t] == l2[p,t]+S2[p,t]+l3[p,t] for p in products for t in weeks if t>0)
m.addConstrs((S[p,t] == S1[p,t]+S2[p,t] for p in products for t in weeks ))
m.addConstrs((a[p-1][t] == a1[p,t]+a2[p,t] for p in products for t in weeks ))
m.addConstrs((l[p-1][t] == l1[p,t]+l2[p,t] for p in products for t in weeks ))
m.addConstrs(gp.quicksum(S1[p,t] for p in products) <= L for t in weeks)
m.addConstrs(gp.quicksum(S[p,t] for p in products) <= L + gp.quicksum(S2[p,t] for p in products) for t in weeks)

m.optimize()
m.write("myLP.lp")
print(m)

0

• 

  Jaromił Najman

  • Gurobi Staff

  December 08, 2022 08:51

  Hi Ghada,

  You should try to hard-code the excel database as lists in your code snippet to see what the lists should actually look like. This way you can compare the results of the lists you are currently generating from your excel files and what they actually should look like and then appropriately adjust your code.

  Note that you are writing the data from table \(\texttt{initial stock.xlsx}\) to object \(\texttt{S1}\) which you later override by optimization variables

  S1=m.addVars(products,weeks,name='S1')

  I am not sure what you want to achieve with the data from table \(\texttt{initial stock.xlsx}\) but if you want to use it afterwards, you should save it into a differently named object, e.g., \(\texttt{S1_table_data}\). You can then use it for whatever you need.

  Please note that in your code snippet you are reading 4 excel files but you provide only a screenshot of 2 tables. Moreover, providing screenshots of excel sheets is not helpful, because one cannot copy paste the data. If you want to share any excel table, you should either provide them in a format that can be easily copy pasted or upload the files somewhere as discussed in Posting to the Community Forum.

  Best regards, 
  Jaromił

  0
• 

  ghada ouerfelli

  December 09, 2022 13:55

  • 
  • 
  • 

  hello by Initial stock I wanna say the S1 in my week zero , it is my initial stock , like this but i have a lot of products that is why I need an excel database 

  S1[1,0].lb=1
  S1[1,0].ub=1
  S1[2,0].lb=2
  S1[2,0].ub=2

  0
• 

  Matthias Miltenberger

  • Gurobi Staff

  December 09, 2022 14:44

  Maybe you should try out Gurobi/gurobipy-pandas: Convenience wrapper for building optimization models from pandas data (github.com) to directly translate those tables into Gurobi data structures. Here's the documentation: Welcome to gurobipy-pandas’s documentation! — gurobipy-pandas 1.0.0 documentation (readthedocs-hosted.com)

  In any case, it's often not that easy to translate data tables into a correct mathematical formulation. You may need to iterate the process a few times to find all mistakes.

  Cheers,
  Matthias

  0

Please sign in to leave a comment.