Cant create dataframe

Specific Domains Optimization (Mathematical)
,
1

Hello guys. Newbie here

I have this array

getvalue.(W)
1-dimensional DenseAxisArray{Float64,1,…} with index sets:
Dimension 1, 0:6
And data, a 7-element Array{Float64,1}:
80.0
65.0
65.0
65.0
65.0
65.0
65.0

and i need to create a dataframe with the 6 last indexes but i get this error

df=DataFrame(W=getvalue.(W)[1:6])
KeyError: key 1:6 not found

can you please advise?
thanks

2

Please, provide a MWE (Minimal Working Example). What is W? I have tried to reproduce your error and had no success, so I do not know what is your problem, or how to fix it.

2 Likes
3

its an aggregate planning exercise. i have uploaded a chunk of it here
https://codeshare.io/GbbbPv
ive been trying to debug line 88 which is the one that gives me errors

Basically, The W variable gets created here
@variable(m, W[0:nPeriods] >= 0,Int) #number of employees

and then used to create a dataframe

decisionDF=DataFrame(W=getvalue(W)[1:nPeriods])

which yields this error

JuMP.value is not defined for collections of JuMP types. Use Julia’s broadcast syntax instead: JuMP.value.(x).

4

Did you try the suggestion in the error?

1 Like
5

decisionDF=DataFrame(W=getvalue.(W)[1:nPeriods])

now yields

KeyError: key 1:6 not found

6

It sounds like you are acting as though getvalue.(W) returns a vector. However it does not return a vector, so you can’t index into it with [1:nPeriods]

7

how can i then get a vector with indexes 1 to 6?

8

it’s hard to say. doing vec(getvalue.(W)). maybe that will work.

9

same result
vec(getvalue.(W))[0:6]

KeyError: key 0:6 not found

10

Please post a MWE and post full error messages, it’s tough to help without more information

2 Likes
11

Here it is
error occurs when im trying to run decisionDF function

###################Load Julia packages##########################################
using JuMP #package containing modeling features for optimization
using DataFrames #package facilitating input and output using dataframes
using CSV
using Cbc #load Cbc solver

###################Input data and initialize parameters##########################
forecastAndPrice=DataFrame!(CSV.File("RedTomatoForecastAndPrice_OffPeakDisc.csv"))
#forecastAndPrice=readtable("RedTomatoForecastAndPrice_OffPeakDisc.csv") #read forecast and price from file
demand = forecastAndPrice[:,2] #extract demand column of forecastAndPrice data frame
price  = forecastAndPrice[:,3] #extract price column of forecastAndPrice data frame

#Specify cost parameters
c_materials = 10 #Materials cost per unit
c_holding = 2 #Holding cost per unit/month
c_stockout = 5 #Stockout cost per unit/month
c_hiring = 300 #Hiring cost per worker
c_layoff = 500 #Layoff cost per worker
c_regular = 4 #Regular time wage per hour
c_overtime = 6 #Over time wage per hour
c_subcontract = 30 #Subcontracting per unit

#Specify other planning parameters
labReq = 4 #Labor hours per unit
hrsPerDay = 8 #hours per day
daysPerMonth = 20 #Days per month
maxOTPerMonth= 10 #Maximum over time per month
startingWorkForce= 80 #Maximum over time per month
endingWorkForce= 0 #ending workforce level
startingBackorder= 0 #starting backorder
endingBackorder= 0 #ending backorder
startInv= 1000 #starting inventory
endInv= 500 #ending inventory

########################Formulate optimization model####################################
nPeriods=size(demand,1) #obtain number of planning periods from forecast
#m = Model() #create Julia optimization model object
m = Model(with_optimizer(Cbc.Optimizer, logLevel=3))

#Create decision variables
@variable(m, W[0:nPeriods] >= 0,Int) #number of employees
@variable(m, H[1:nPeriods] >= 0, Int) #number of employees hired
@variable(m, L[1:nPeriods] >= 0, Int) #number of employess laid off
@variable(m, P[1:nPeriods] >= 0, Int) #number of units produced
@variable(m, Inv[0:nPeriods] >= 0) #number of units in inventory
@variable(m, S[0:nPeriods] >= 0) #number of units backordered
@variable(m, C[1:nPeriods] >= 0, Int) #number of units subcontracted
@variable(m, O[1:nPeriods] >= 0) #number of overtime hours

#Create objective function components
@expression(m, regTimeCost[t=1:nPeriods],  c_regular*hrsPerDay*daysPerMonth*W[t]) #regular time cost
@expression(m, OTCost[t=1:nPeriods],  c_overtime*O[t]) #over time cost
@expression(m, hiringCost[t=1:nPeriods],  c_hiring*H[t]) #hiring cost
@expression(m, layoffCost[t=1:nPeriods],  c_layoff*L[t]) #layoff cost
@expression(m, prodCost[t=1:nPeriods],  c_materials*P[t]) #production cost
@expression(m, invCost[t=1:nPeriods],  c_holding*Inv[t]) #inventory holding cost
@expression(m, stockOutCost[t=1:nPeriods],  c_stockout*S[t]) #stockout cost
@expression(m, subContCost[t=1:nPeriods], c_subcontract*C[t]) #subcontracting cost

#Add objective function components together and form "minimization" objective function
@objective(m, Min, sum(regTimeCost[t] + OTCost[t] + hiringCost[t] 
+ layoffCost[t]  + prodCost[t] + invCost[t] + stockOutCost[t] + subContCost[t] for t=1:nPeriods))

#Create constraints
@constraints(m, begin
    const_initWorkforce, W[0]==startingWorkForce #set W[0] to initial workforce
    const_startInv, Inv[0] == startInv #set I[0] to initial inventory
    const_startBackorder,S[0]==startingBackorder #set S[0] to initial backorder
    
    const_workforceFlow[t=1:nPeriods], W[t] == W[t-1] + H[t] - L[t] #balance equations for workforce level
    const_invBal[t=1:nPeriods], Inv[t] == Inv[t-1] + P[t] + C[t] + S[t] - demand[t] - S[t-1] #balance equations for inventory
    
    const_prodCap[t=1:nPeriods], labReq*P[t] <= hrsPerDay*daysPerMonth*W[t] + O[t] #production time constraint
    const_overTime[t=1:nPeriods], O[t] <= maxOTPerMonth*W[t] #maximum overtime constraint
    
    const_endWorkforce, W[nPeriods] >= endingWorkForce #minimum ending workforce constraint
    const_endInv, Inv[nPeriods] >= endInv #minimum ending inventory constraint
    const_endBackorders, S[nPeriods] == endingBackorder #ending backorder constraint    
end)

println("****Formulation****\n",m,"\n") #print optimization model

########################Solve model and output solution####################################
#status = solve(m) #solve optimization model
status = optimize!(m) #solve model

println("****Solve status and results****")
println("Optimization status: ", status,"\n") #print solver status
# println("Workforce: ", getvalue(W))

#Create data frame with decision variables
decisionDF=DataFrame(W=getvalue(W)[1:nPeriods],H=getvalue(H),L=getvalue(L), P=getvalue(P),C=getvalue(C),S=getvalue(S)[1:nPeriods],Inv=getvalue(Inv)[1:nPeriods])

#Create data frame with costs per period
costPerPeriodDF = DataFrame(regTimeCost=getvalue(regTimeCost),OTCost=getvalue(OTCost),
hiringCost=getvalue(hiringCost),layoffCost=getvalue(layoffCost),invCost=getvalue(invCost),
stockOutCost=getvalue(stockOutCost),prodCost=getvalue(prodCost),subContCost=getvalue(subContCost))

#Create data frame containing profit information
Revenue=dot(price,demand) #sum_{t = 1 to nperiods} price(t)*demand(t)
Cost=getobjectivevalue(m)
Profit=Revenue-Cost
profitDF=DataFrame(Revenue=Revenue,Cost=Cost,Profit=Profit)

#Process measures
Flowrate=mean(demand) #average demand satisfied by the aggregate plan
Inventory= (0.5*(getvalue(Inv)[0] + getvalue(Inv)[nPeriods]) + sum(getvalue(Inv)[1:nPeriods-1]))/nPeriods #average inventory 
Flowtime=Inventory/Flowrate #flow time via little's law
processDF = DataFrame(Flowrate=Flowrate,Inventory=Inventory,Flowtime=Flowtime) #create data frame with process flow measures

println("****Optimal solution****\n",decisionDF,"\n") #print data frame containing decision variables created above
println(profitDF,"\n") #print profit
println("****Cost per period****\n",costPerPeriodDF,"\n") #print cost data frame created above
println("****Process flow measures****\n",processDF) #print data frame containing process flow measures

writetable("DecVar.csv", decisionDF) #write decision variable values to a csv file
writetable("profitSummary.csv", profitDF) #write profit information from data frame to a csv file
writetable("CostPerPeriod.csv", costPerPeriodDF) #write cost data frame to a csv file
writetable("ProcessFlowMeas.csv", processDF) #write process flow measures to a csv file

JuMP.value is not defined for collections of JuMP types. Use Julia’s broadcast syntax instead: JuMP.value.(x).

Stacktrace:
[1] error(::String) at .\error.jl:33
[2] value(::JuMP.Containers.DenseAxisArray{VariableRef,1,Tuple{UnitRange{Int64}},Tuple{Dict{Int64,Int64}}}) at C:\Users\andre.julia\packages\JuMP\qhoVb\src\variables.jl:1001
[3] getvalue(::JuMP.Containers.DenseAxisArray{VariableRef,1,Tuple{UnitRange{Int64}},Tuple{Dict{Int64,Int64}}}) at .\deprecated.jl:48
[4] top-level scope at In[197]:97
[5] include_string(::Function, ::Module, ::String, ::String) at .\loading.jl:1091

12 
ERROR: LoadError: ArgumentError: "RedTomatoForecastAndPrice_OffPeakDisc.csv" is not a valid file

It is not yet reproducible, as the CSV is not given.

1 Like
13

here is the csv

14

Without us having to run all your code, can you post the output of

@show getvalue.(W)

?

1 Like
15 
getvalue.(W) = 1-dimensional DenseAxisArray{Float64,1,...} with index sets:
    Dimension 1, 0:6
And data, a 7-element Array{Float64,1}:
 80.0
 65.0
 65.0
 65.0
 65.0
 65.0
 65.0

1-dimensional DenseAxisArray{Float64,1,...} with index sets:
    Dimension 1, 0:6
And data, a 7-element Array{Float64,1}:
 80.0
 65.0
 65.0
 65.0
 65.0
 65.0
 65.0
16

I think your main problem is that you start from zero in W, S, and Inv. If you do this JuMP will not use an Vector, but their own special container to deal with this case, and these container keeps being used when you call getvalue.(W).

W = 1-dimensional DenseAxisArray{VariableRef,1,...} with index sets:
    Dimension 1, 0:6
And data, a 7-element Array{VariableRef,1}:
 W[0]
 W[1]
 W[2]
 W[3]
 W[4]
 W[5]
 W[6]
typeof(W) = JuMP.Containers.DenseAxisArray{VariableRef,1,Tuple{UnitRange{Int64}},Tuple{Dict{Int64,Int64}}}
┌ Warning: `getvalue` is deprecated, use `JuMP.value` instead.
│   caller = _broadcast_getindex_evalf at broadcast.jl:631 [inlined]
└ @ Core ./broadcast.jl:631
getvalue.(W) = 1-dimensional DenseAxisArray{Float64,1,...} with index sets:
    Dimension 1, 0:6
And data, a 7-element Array{Float64,1}:
 80.0
 65.0
 65.0
 65.0
 65.0
 65.0
 65.0
typeof(getvalue.(W)) = JuMP.Containers.DenseAxisArray{Float64,1,Tuple{UnitRange{Int64}},Tuple{Dict{Int64,Int64}}}

This container is not a vector and do not allow the same slicing syntax. I changed your dataframe creation to:

@show W
@show typeof(W)
@show getvalue.(W)
@show typeof(getvalue.(W))
W_=getindex.((getvalue.(W),), 1:nPeriods)
@show W_
@show typeof(W_)
H_=getvalue.(H)
L_=getvalue.(L)
P_=getvalue.(P)
C_=getvalue.(C)
S_=getindex.((getvalue.(S),), 1:nPeriods)
@show S_
@show typeof(S_)
Inv_=getindex.((getvalue.(Inv),), 1:nPeriods)
@show Inv_
@show typeof(Inv_)

#Create data frame with decision variables
decisionDF=DataFrame(W = W_, H = H_, L = L_, P = P_, C = C_, S = S_, Inv = Inv_)

Note, however, that the W_, S_, and Inv_, are normal vectors from 1:6 (they do not have the concept of zero-index). It is not a problem here but it may trip you if start doing more thing with them than just saving them to a CSV file.

I also had to fix getindex calls missing the dot in many other places (basically all calls in the file) before the whole code could run without errors. And, I had to add an using LinearAlgebra at the start of the code because dot lives in LinearAlgebra now (it comes with Julia, no extra package needed) not in the Core anymore. The same for using Statistics and the mean function. And I also had to change other line because the same problem above:

Inventory= 0.5*(getvalue.(Inv)[0] + getvalue.(Inv)[nPeriods]) + sum(getindex.((getvalue.(Inv),), 1:(nPeriods-1)))/nPeriods #average inventory

Finally, it is CSV.write not writetable.

The final code is in: Fixed JuMP Code - Pastebin.com

4 Likes
17

thank you so much for all your help