[PYTHON] Crude oil refining optimization

what is this

This is an article resolving "Optimization learned with OR-Tools [Linear programming: Let's refine oil]" with a model using pandas.

Formulation

Variables: Amount produced by crude oil type and product type
Objective function: Total purchase cost-Total sales amount → Maximize
Constraints
・ Upper limit of crude oil purchase
・ Upper and lower limits of demand for each product
・ Octane number of product

Solve with Python

Data settings

Create a table of crude oil and products

from io import StringIO
from ortoolpy import pd, model_max, lpDot, lpSum

dfoil = pd.read_csv(StringIO("""\
crude oil,crude oil_Octane number,Ownership,cost_barrel
R0,99,782,55.34
R1,94,894,54.12
R2,84,631,53.68
R3,92,648,57.03
R4,87,956,54.81
R5,97,647,56.25
R6,81,689,57.55
R7,96,609,58.21"""))

dfprd = pd.read_csv(StringIO("""\
Product,Product_Octane number,Lower limit of demand,Demand cap,Selling price
F0,88,415,11707,61.97
F1,94,199,7761,62.04
F2,90,479,12596,61.99"""))

Variable table creation

Variable table is created by direct product of crude oil and product

df = pd.DataFrame(
    [
        [*row1, *row2]
        for row1 in dfoil.itertuples(False)
        for row2 in dfprd.itertuples(False)
    ],
    columns=dfoil.columns.tolist() + dfprd.columns.tolist(),
)
df[:2]  #First two lines

Modeling & result display

m = model_max(df=df)
for _, dfs in df.groupby("crude oil"):
    m += lpSum(dfs.Var) <= dfs.Ownership.iloc[0]
for _, dfs in df.groupby("Product"):
    m += lpSum(dfs.Var) >= dfs.Lower limit of demand.iloc[0]
    m += lpSum(dfs.Var) <= dfs.Demand cap.iloc[0]
    m += (lpDot(dfs.crude oil_Octane number, dfs.Var) ==
          lpDot(dfs.Product_Octane number, dfs.Var))
m.solve(objs=["-cost_barrel", "Selling price"])

dfr = df.pivot_table("Val", "crude oil", "Product").round(2)
dfr = pd.concat([dfr, dfr.sum().to_frame("Total").T])
dfr["Total"] = dfr.sum(1)
dfr

(For some reason, it is different from the original article of OR-Tools)

Modeling points

--It is possible with 0.2.36 or later of ortoolpy. --Create a maximized model with model_max and a minimized model with model_min. --When creating a model, if you specify DataFrame in the argument below, a Var column will be created as a variable. --df: Non-negative continuous variable --dfb: 0-1 variable --dfi: Non-negative integer variable --If you specify a list of column names with ʻobjs at solve, that column will be used as the coefficient of the objective function. --If the column name has " -", it will be multiplied by -1. --If a DataFrame is attached to the model, the result will be added as aVal` column after solve.

that's all