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

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"""))

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
``````
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)
dfr
``````
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 a`Val` column after solve.

that's all

Recommended Posts