Knapsack: Exercise 1.4 - Loading Data from Excel

In the knapsack example shown in the tutorial slides, the data is hardcoded at the top of the file. Instead of hard-coding the data, we can use Python to load the data from a different source.

Learning Objectives:

• Learn to read external data sources (Excel files)

• Understand data integration with Pyomo models

• Practice using pandas for data manipulation

• See how to make models more flexible and reusable

import pandas as pd
import pyomo.environ as pyo
import os
import openpyxl
path = os.path.abspath('knapsack_data.xlsx')
df_items = pd.read_excel(path, sheet_name='data', header=0, index_col=0)
W_max = 14

A = df_items.index.tolist()
b = df_items['Benefit'].to_dict()
w = df_items['Weight'].to_dict()

model = pyo.ConcreteModel()
model.x = pyo.Var( A, within=pyo.Binary )

model.obj = pyo.Objective(
    expr = sum( b[i]*model.x[i] for i in A ), 
    sense = pyo.maximize )

model.weight_con = pyo.Constraint(
    expr = sum( w[i]*model.x[i] for i in A ) <= W_max )

opt = pyo.SolverFactory('cbc')
opt_success = opt.solve(model)

total_weight = sum( w[i]*pyo.value(model.x[i]) for i in A )
print('Total Weight:', total_weight)
print('Total Benefit:', pyo.value(model.obj))

print('%12s %12s' % ('Item', 'Selected'))
print('=========================')
for i in A:
    acquired = 'No'
    if pyo.value(model.x[i]) >= 0.5:
        acquired = 'Yes'
    print('%12s %12s' % (i, acquired))
print('-------------------------')

Total Weight: 12.0
Total Benefit: 25.0
        Item     Selected
=========================
      hammer          Yes
      wrench           No
 screwdriver          Yes
       towel          Yes
-------------------------