Key error(0,0)
回答済み
We tried to get data from excel by making the code to read it from excel file that attached to mail. Because of the knowledge we have for defining lp parameters into python we could not get rid of from nested inventory which can be seen from every parameter we created( example: d_il={0:{.......},1:{.......}}). It would be very helpful if we can set every parameter like variables.
import numpy
import numpy as np
import pandas as pd
import openpyxl
import gurobipy as grb
from gurobipy import GRB
from gurobipy import quicksum
import xlrd
# Read Data
df = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name='Sheet1')
C_j = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name='C_j', header=None)
C1_lj = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="C1_lj", header=None)
I_ij = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="I_ij", header=None)
I1_lj = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="I1_lj", header=None)
I0_i = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="I0_i", header=None)
a_ik = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="a_ik", header=None)
b_km = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="b_km", header=None)
c_ml = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="c_ml", header=None)
d_il = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="d_il", header=None)
e_ij = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="e_ij", header=None)
MaxS_ij = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="MaxS_ij", header=None)
MinS_ij = pd.read_excel('D:\Dersler\Senior Design_2\modelv2.xlsx', sheet_name="MinS_ij", header=None)
# Defining Data Column
j = df['Store']
l = df['Product Category']
m = df['Product Model']
color = df['Color']
size = df['Size']
i = m + ' ' + color + ' ' + size
k = m + ' ' + color
df['SKU'] = i
df['Option'] = k
df = df.drop(['Size', 'Color'], axis=1)
i = i.drop_duplicates()
k = k.drop_duplicates()
l = l.drop_duplicates()
m = m.drop_duplicates()
j = j.drop_duplicates()
i = i.reset_index(drop=True)
j = j.reset_index(drop=True)
k = k.reset_index(drop=True)
l = l.reset_index(drop=True)
m = m.reset_index(drop=True)
set_i = range(0, len(i) ) # +1 konulacak mı emin değilim
set_j = range(0, len(j) )
set_k = range(0, len(k))
set_l = range(0, len(l))
set_m = range(0, len(m) )
set_bin = range(0,1)
MinW = 5000
MaxW = 15000
h=5
p=15
rep_model = grb.Model(name="Replenishment Model")
C= {}
for j in set_j:
C[j]=C_j
C1 = {}
for l in set_l:
for j in set_j:
C1[l, j] = C1_lj
I = {}
for i in set_i:
for j in set_j:
I[i, j] = I_ij
I0 = {}
for i in set_i:
I0[i] = I0_i
# d = {}
# for i in set_i:
# for l in set_l:
# d[i, l] = d_il
e = {}
for i in set_i:
for j in set_j:
e[i,j] = e_ij
# FL = {}
# for i in set_i:
# for j in set_j:
# FL[i,j]= FL_ij
#
# FU = {}
# for i in set_i:
# for j in set_j:
# FU[i, j] = FU_ij
#
# y = {}
# for i in set_i:
# for j in set_j:
# y[i,j]= y_ij
C_j=C_j.to_dict()
C1_lj=C1_lj.to_dict()
I_ij=I_ij.to_dict()
I0_i=I0_i.to_dict()
# d_il=d_il.to_dict()
e_ij=e_ij.to_dict()
#Variables
q = {(i, j): rep_model.addVar(vtype=GRB.CONTINUOUS) for i in set_i for j in set_j}
y = {(i, j): rep_model.addVar(vtype=GRB.BINARY) for i in set_i for j in set_j}
x = {():rep_model.addVar(vtype=GRB.BINARY)}
o_ij= {(i, j): rep_model.addVar(vtype=GRB.CONTINUOUS) for i in set_i for j in set_j }
t_ij= {(i, j): rep_model.addVar(vtype=GRB.CONTINUOUS) for i in set_i for j in set_j }
#Constraints
constraint1 = rep_model.addConstrs(grb.quicksum(q[i, j] for j in set_j) <= I0_i[0][i] for i in set_i)
constraint2 = rep_model.addConstrs(grb.quicksum(d_il[i,l]*q[i,j] for i in set_i ) <= C1_lj[l,j] for l in set_l for j in set_j)
# constraint3 = rep_model.addConstrs(grb.quicksum(I_ij[j-1][i-1] +q[i,j] for i in set_i)<=C_j[0][j-1] for j in set_j)
# constraint4 = rep_model.addConstrs(grb.quicksum(q[i,j] for i in set_i for j in set_j) <= 1500*x)
# constraint5 = rep_model.addConstrs(grb.quicksum(q[i,j] for i in set_i for j in set_j) >= 500*x)
# constraint6 = rep_model.addConstrs((I_ij[j-1][i-1]+q[i,j] for i in set_i for j in set_j)>= min(FL_ij[j-1][i-1],I0_i[0][i-1])*y_ij[j-1][i-1])
# constraint7 = rep_model.addConstrs((q[i,j] for i in set_i for j in set_j)>=MinS_ij[j-1][i-1]*y_ij[j-1][i-1]
# constraint8 = rep_model.addConstrs((q[i,j] for i in set_i for j in set_j)<=e_ij[j-1][i-1]*min(FU_ij[j-1][i-1],MaxS_ij[j-1][i-1])*y_[j-1][i-1])
#Objective Function
# objective = grb.quicksum(p*o_ij[i,j]+h*t_ij[i,j]
# for i in set_i
# for j in set_j)
#
# rep_model.ModelSense = grb.GRB.MINIMIZE
# rep_model.setObjective(objective)
# rep_model.optimize()
-
正式なコメント
-
Gurobi Staff
This post is more than three 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 why not try our AI Gurobot?. -
-
-
Hi Ece,
It seems this is rather a general Python question and not a Gurobi-specific issue. Your approach using pandas to read in the Excel sheets appears very reasonable - I don't see your problem or question, to be honest. It is always recommended to share a minimal reproducible example, so please try formulating a shorter example data set and let us know where you are stuck.
Best regards,
Matthias0 -
-
This annoying error means that Pandas can not find your column name in your dataframe. Before doing anything with the data frame, use print(df.columns) to see dataframe column exist or not.
print(df.columns)
I was getting a similar kind of error in one of my codes. Turns out, that particular index was missing from my data frame as I had dropped the empty dataframe 2 rows. If this is the case, you can do df.reset_index(inplace=True) and the error should be resolved.
0 -
投稿コメントは受け付けていません。
コメント
3件のコメント