Help in pandas-gurobipy

any help please

Hello, the problem in my model is how to convert the database S1 and S2 of week zero into S1[1,0].ub and S1[2,0].lb , the same for S2 , I sent the excel files by email

S1 and S2 are variables for weeks 1 , 2,3 , and 4 but for week 0 is a data

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],]



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')

#Read initial Local stock from Excel
df=pd.read_excel("S1.xlsx")
b=df.drop(columns='Article',axis=1)
S3=b.values.tolist()
#print(S3)
for i in range(len(products)):
     S1[i,0].ub=S3[i]
     S1[i, 0].ub +=1
     S1[i,0].lb=S3[i]
     S1[i,0].lb+=1
#print(S1)

#Read initial Logis stock from Excel
df=pd.read_excel("S2.xlsx")
b=df.drop(columns='Article',axis=1)
S4=b.values.tolist()
#print(S4)
for i in range(len(products)):
     S2[i,0].ub=S4[i]
     S2[i, 0].ub +=1
     S2[i,0].lb=S4[i]
     S2[i,0].lb+=1
print(S2)
#S1[1,0].lb=1
#S1[1,0].ub=1
#S1[2,0].lb=2
#S1[2,0].ub=2

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



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>1)
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>1)
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)

Purchase order

consommation

Official comment

Simranjit Kaur

Gurobi Staff

November 13, 2026 01:51

This post is more than two years old. Some information may not be up to date. For current information, please check the Gurobi Documentation or Knowledge Base. If you need more help, please create a new post in the community forum, or try Gurobot, our chatbot interface offering instant, expert-level support.