[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.


Variables: Amount produced by crude oil type and product type
Objective function: Total purchase cost-Total sales amount → Maximize
・ 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

dfprd = pd.read_csv(StringIO("""\
Product,Product_Octane number,Lower limit of demand,Demand cap,Selling price

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
crude oil crude oil_Octane number Ownership cost_barrel Product Product_Octane number Lower limit of demand Demand cap Selling price
0 R0 99 782 55.34 F0 88 415 11707 61.97
1 R0 99 782 55.34 F1 94 199 7761 62.04

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)
F0 F1 F2 Total
R0 0 782 0 782
R1 894 0 0 894
R2 465.29 30.08 135.63 631
R3 330.04 0 317.96 648
R4 0 956 0 956
R5 0 621.59 25.41 647
R6 689 0 0 689
R7 0 609 0 609
Total 2378.33 2998.67 479 5856

(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

Recommended Posts

Crude oil refining optimization