total-packed-product-cost.py

"""
Based on https://stackoverflow.com/questions/76647505
"""

import pandas as pd
import pulp

data = {
    'Product': ('Product 1', 'Product 2', 'Product 3', 'Product 4',
                'Product 1', 'Product 2', 'Product 3', 'Product 4',
                'Product 1', 'Product 2', 'Product 3', 'Product 4',
                'Product 1', 'Product 2', 'Product 3', 'Product 4',
                'Product 1', 'Product 2', 'Product 3', 'Product 4',
                'Product 1', 'Product 2', 'Product 3', 'Product 4',
                'Product 1', 'Product 2', 'Product 3', 'Product 4',
                'Product 1', 'Product 2', 'Product 3', 'Product 4'),
    'Pack Size': (1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4,
                  5, 5, 5, 5, 6, 6, 6, 6, 7, 7, 7, 7, 8, 8, 8, 8),
    'Total Cost': ( 2773.47,  2665.23, 23421.24, 17666.58,  1592.09,  1678.04,
                   12798.46,  9425.8 ,  1246.3 ,  1396.98,  9377.7 ,  6621.21,
                    1109.4 ,  1292.45,  7491.82,  5275.17,  1056.07,  1258.53,
                    6482.69,  4517.04,  1044.51,  1259.92,  5845.94,  4035.62,
                    1056.82,  1281.48,  5411.69,  3712.32,  1084.06,  1315.65,
                    5104.  ,  3487.85),
}

def make_assign(row: pd.Series) -> pulp.LpVariable:
    product, pack = row.name
    return pulp.LpVariable(name=f'asn_{product}_{pack}', cat=pulp.LpBinary)

def make_pack_assign(row: pd.Series) -> pulp.LpVariable:
    return pulp.LpVariable(name=f'asn_pack_{row.name}', cat=pulp.LpBinary)

combos = pd.DataFrame(data).set_index(['Product', 'Pack Size'])
combos['assign'] = combos.apply(make_assign, axis=1)
assign_by_pack = combos['assign'].unstack(level='Product')
pack_assigns = assign_by_pack.apply(make_pack_assign, axis=1)

prob = pulp.LpProblem(name='product_packing', sense=pulp.LpMinimize)
prob.objective = combos['assign'].dot(combos['Total Cost'])
prob.addConstraint(name='packs_assigned', constraint=pack_assigns.sum() == 2)

# Each product must be assigned exactly once
for product, total in assign_by_pack.sum(axis=0).items():
    prob.addConstraint(name='excl_' + product, constraint=total == 1)

# Product-pack pairs cannot be assigned if their pack is not assigned
for pack_name, pack in pack_assigns.items():
    for product_name, product in assign_by_pack.loc[pack_name, :].items():
        prob.addConstraint(
            name=f'group_{product_name}_{pack_name}',
            constraint=product <= pack,
        )

print(prob)
prob.solve()
assert prob.status == pulp.LpStatusOptimal
print(f'Total cost: ${prob.objective.value():.2f}')
print('Pack sizes:', pack_assigns.index[pack_assigns.apply(pulp.LpVariable.value) > 0.5].values)
print('Product-pack pair assignments and their cost:')
print(combos.loc[combos['assign'].apply(pulp.LpVariable.value) > 0.5, 'Total Cost'].to_string())

'''
product_packing:
MINIMIZE
2773.47*asn_Product_1_1 + 1592.09*asn_Product_1_2 + ... + 3487.85*asn_Product_4_8 + 0.0
SUBJECT TO
packs_assigned: asn_pack_1 + asn_pack_2 + asn_pack_3 + asn_pack_4 + asn_pack_5
 + asn_pack_6 + asn_pack_7 + asn_pack_8 = 2

excl_Product_1: asn_Product_1_1 + asn_Product_1_2 + asn_Product_1_3
 + asn_Product_1_4 + asn_Product_1_5 + asn_Product_1_6 + asn_Product_1_7
 + asn_Product_1_8 = 1

excl_Product_2: asn_Product_2_1 + asn_Product_2_2 + asn_Product_2_3
 + asn_Product_2_4 + asn_Product_2_5 + asn_Product_2_6 + asn_Product_2_7
 + asn_Product_2_8 = 1

excl_Product_3: asn_Product_3_1 + asn_Product_3_2 + asn_Product_3_3
 + asn_Product_3_4 + asn_Product_3_5 + asn_Product_3_6 + asn_Product_3_7
 + asn_Product_3_8 = 1

excl_Product_4: asn_Product_4_1 + asn_Product_4_2 + asn_Product_4_3
 + asn_Product_4_4 + asn_Product_4_5 + asn_Product_4_6 + asn_Product_4_7
 + asn_Product_4_8 = 1

group_Product_1_1: asn_Product_1_1 - asn_pack_1 <= 0

group_Product_2_1: asn_Product_2_1 - asn_pack_1 <= 0
...
group_Product_3_8: asn_Product_3_8 - asn_pack_8 <= 0

group_Product_4_8: asn_Product_4_8 - asn_pack_8 <= 0

VARIABLES
0 <= asn_Product_1_1 <= 1 Integer
0 <= asn_Product_1_2 <= 1 Integer
...
0 <= asn_Product_4_7 <= 1 Integer
0 <= asn_Product_4_8 <= 1 Integer
0 <= asn_pack_1 <= 1 Integer
0 <= asn_pack_2 <= 1 Integer
0 <= asn_pack_3 <= 1 Integer
0 <= asn_pack_4 <= 1 Integer
0 <= asn_pack_5 <= 1 Integer
0 <= asn_pack_6 <= 1 Integer
0 <= asn_pack_7 <= 1 Integer
0 <= asn_pack_8 <= 1 Integer

Welcome to the CBC MILP Solver 
Version: 2.10.3 
Build Date: Dec 15 2019 

At line 2 NAME          MODEL
At line 3 ROWS
At line 42 COLUMNS
At line 259 RHS
At line 297 BOUNDS
At line 338 ENDATA
Problem MODEL has 37 rows, 40 columns and 104 elements
...
Result - Optimal solution found
Objective value:                10896.28000000

Total cost: $10896.28
Pack sizes: [6 8]
Product-pack pair assignments and their cost:
Product    Pack Size
Product 1  6            1044.51
Product 2  6            1259.92
Product 3  8            5104.00
Product 4  8            3487.85
'''