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
Constraints
・ Upper limit of crude oil purchase
・ Upper and lower limits of demand for each product
・ Octane number of product
```

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

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

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