Blake Rayvid - https://github.com/brayvid
Make the most of the ingredients you have. Maximize total magnitude (essentially in-game value) with integer linear programming in scipy.
import numpy as np
import pandas as pd
from scipy.optimize import milp, Bounds, LinearConstraint
Uses local files "ingredients_have.csv" and "recipes_can_make.csv"
I made my CSVs using this helpful spreadsheet: https://docs.google.com/spreadsheets/d/1010C6ltqv7apuBoNYuFIFSBZER4YI03Y54kIsoKs5RI/edit?usp=sharing
# Ingredients we have with quantity on hand
ingredients = pd.read_csv('ingredients_have.csv');ingredients
Ingredient | Quantity | |
---|---|---|
0 | Blisterwort | 4 |
1 | Blue Butterfly Wing | 4 |
2 | Blue Dartwing | 1 |
3 | Blue Mountain Flower | 24 |
4 | Bone Meal | 5 |
5 | Butterfly Wing | 6 |
6 | Canis Root | 2 |
7 | Creep Cluster | 1 |
8 | Deathbell | 6 |
9 | Dragons Tongue | 5 |
10 | Ectoplasm | 5 |
11 | Elves Ear | 10 |
12 | Fire Salts | 1 |
13 | Fly Amanita | 1 |
14 | Frost Mirriam | 3 |
15 | Garlic | 7 |
16 | Giant Lichen | 2 |
17 | Glow Dust | 2 |
18 | Hagraven Feathers | 2 |
19 | Histcarp | 2 |
20 | Honeycomb | 4 |
21 | Ice Wraith Teeth | 2 |
22 | Imp Stool | 2 |
23 | Lavender | 17 |
24 | Luna Moth Wing | 4 |
25 | Mora Tapinella | 2 |
26 | Mudcrab Chitin | 2 |
27 | Nightshade | 7 |
28 | Nirnroot | 3 |
29 | Nordic Barnacle | 2 |
30 | Orange Dartwing | 2 |
31 | Purple Mountain Flower | 15 |
32 | Red Mountain Flower | 1 |
33 | River Betty | 2 |
34 | Rock Warbler Egg | 3 |
35 | Salt Pile | 14 |
36 | Scaly Pholiota | 1 |
37 | Skeever Tail | 2 |
38 | Slaughterfish Scales | 3 |
39 | Snowberries | 6 |
40 | Spider Egg | 8 |
41 | Spriggan Sap | 3 |
42 | Swamp Fungal Pod | 2 |
43 | Taproot | 2 |
44 | Thistle Branch | 2 |
45 | Torchbug Thorax | 3 |
46 | Troll Fat | 3 |
47 | Tundra Cotton | 7 |
48 | Vampire Dust | 1 |
49 | Void Salts | 1 |
50 | White Cap | 6 |
# Potions list with magnitude and ingredient names (1,2 + optional 3rd)
recipes = pd.read_csv('recipes_can_make.csv')
recipes = recipes[recipes['Magnitude'] > 0];
recipes.head(20)
Magnitude | Type | Ingredient 1 | Ingredient 2 | Ingredient 3 | Effects | Effect 1 | Effect 2 | Effect 3 | Effect 4 | Effect 5 | MyPotionID | Can Make | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 159 | Mixed | Blue Dartwing | Blue Mountain Flower | Glow Dust | 3 | Restore Health | Damage Magicka Regen | Resist Shock | NaN | NaN | 3028 | True |
1 | 156 | Mixed | Blue Dartwing | Blue Mountain Flower | Nightshade | 2 | Restore Health | Damage Magicka Regen | NaN | NaN | NaN | 3037 | True |
2 | 156 | Mixed | Blue Dartwing | Blue Mountain Flower | Spider Egg | 2 | Restore Health | Damage Magicka Regen | NaN | NaN | NaN | 3045 | True |
3 | 156 | Mixed | Blue Dartwing | Blue Mountain Flower | Spriggan Sap | 2 | Restore Health | Damage Magicka Regen | NaN | NaN | NaN | 3046 | True |
4 | 113 | Mixed | Blisterwort | Blue Butterfly Wing | Blue Mountain Flower | 4 | Damage Stamina | Restore Health | Fortify Conjuration | Damage Magicka Regen | NaN | 2130 | True |
5 | 113 | Mixed | Blue Butterfly Wing | Blue Mountain Flower | Rock Warbler Egg | 4 | Fortify Conjuration | Damage Magicka Regen | Restore Health | Damage Stamina | NaN | 2680 | True |
6 | 112 | Mixed | Frost Mirriam | Histcarp | Purple Mountain Flower | 4 | Damage Stamina Regen | Restore Stamina | Fortify Sneak | Resist Frost | NaN | 10371 | True |
7 | 110 | Mixed | Blue Butterfly Wing | Blue Mountain Flower | Butterfly Wing | 3 | Fortify Conjuration | Damage Magicka Regen | Restore Health | NaN | NaN | 2666 | True |
8 | 110 | Mixed | Blue Butterfly Wing | Blue Mountain Flower | Imp Stool | 3 | Fortify Conjuration | Damage Magicka Regen | Restore Health | NaN | NaN | 2677 | True |
9 | 110 | Mixed | Blue Butterfly Wing | Blue Mountain Flower | Swamp Fungal Pod | 3 | Fortify Conjuration | Damage Magicka Regen | Restore Health | NaN | NaN | 2684 | True |
10 | 110 | Mixed | Glow Dust | Nightshade | River Betty | 3 | Damage Magicka Regen | Fortify Destruction | Damage Health | NaN | NaN | 11628 | True |
11 | 109 | Mixed | Blisterwort | Blue Mountain Flower | Spriggan Sap | 3 | Restore Health | Damage Magicka Regen | Fortify Smithing | NaN | NaN | 2210 | True |
12 | 109 | Mixed | Blue Butterfly Wing | Bone Meal | Spriggan Sap | 4 | Damage Stamina | Fortify Conjuration | Damage Magicka Regen | Fortify Enchanting | NaN | 2703 | True |
13 | 109 | Mixed | Butterfly Wing | Glow Dust | Nightshade | 4 | Damage Magicka | Damage Magicka Regen | Lingering Damage Stamina | Fortify Destruction | NaN | 4738 | True |
14 | 109 | Mixed | Creep Cluster | Ectoplasm | Histcarp | 3 | Restore Magicka | Fortify Magicka | Damage Stamina Regen | NaN | NaN | 6302 | True |
15 | 109 | Mixed | Creep Cluster | Histcarp | Red Mountain Flower | 3 | Damage Stamina Regen | Restore Magicka | Fortify Magicka | NaN | NaN | 6550 | True |
16 | 109 | Mixed | Creep Cluster | River Betty | Skeever Tail | 3 | Fortify Carry Weight | Damage Stamina Regen | Damage Health | NaN | NaN | 6725 | True |
17 | 109 | Mixed | Nightshade | River Betty | Spriggan Sap | 3 | Damage Health | Damage Magicka Regen | Fortify Alteration | NaN | NaN | 14461 | True |
18 | 108 | Mixed | Blisterwort | Blue Butterfly Wing | Spriggan Sap | 4 | Damage Stamina | Damage Magicka Regen | Fortify Enchanting | Fortify Smithing | NaN | 2154 | True |
19 | 108 | Mixed | Blisterwort | Blue Mountain Flower | Spider Egg | 3 | Restore Health | Damage Stamina | Damage Magicka Regen | NaN | NaN | 2209 | True |
One row for each ingredient, one column for each potion. "1" indicates the ingredient is used in the potion.
# Boolean matrix A says what ingredients are in what recipes
A = pd.DataFrame(0, index=range(len(ingredients)),columns=range(len(recipes)))
for i in range(len(recipes)):
if ingredients.iloc[ingredients["Ingredient"].str.find(recipes.loc[i, "Ingredient 1"]).idxmax()]["Quantity"] > 0:
A.iloc[ingredients["Ingredient"].str.find(recipes.loc[i, "Ingredient 1"]).idxmax(), i] = 1
if ingredients.iloc[ingredients["Ingredient"].str.find(recipes.loc[i, "Ingredient 2"]).idxmax()]["Quantity"] > 0:
A.iloc[ingredients["Ingredient"].str.find(recipes.loc[i, "Ingredient 2"]).idxmax(), i] = 1
if not pd.isnull(recipes.loc[i, "Ingredient 3"]):
if ingredients.iloc[ingredients["Ingredient"].str.find(recipes.loc[i, "Ingredient 3"]).idxmax()]["Quantity"] > 0:
A.iloc[ingredients["Ingredient"].str.find(recipes.loc[i, "Ingredient 3"]).idxmax(), i] = 1
A.head(20)
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 2375 | 2376 | 2377 | 2378 | 2379 | 2380 | 2381 | 2382 | 2383 | 2384 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
3 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
13 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
14 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
16 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
17 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
19 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
20 rows × 2385 columns
find x to minimize f.x with Ax <= b, x >= lb
f = -1 * magnitude, b = qty of each ingredient on hand
# Objective function f.x to minimize
f = np.array(-1 * recipes['Magnitude'],dtype=int); # f = -1*value so that minimizing f.x maximizes total value
# Bounds
b_max = np.array(ingredients['Quantity'],dtype=int) # Cannot use more than we have on hand
x_lb = np.zeros(shape=len(recipes)) # Cannot use less than 0
# milp parameters
bounds = Bounds(lb=x_lb)
constraint = LinearConstraint(A, ub=b_max)
integrality = np.ones(shape=len(recipes),dtype=int) # All x should be integers
# Perform optimization
res = milp(c=f, integrality=integrality, bounds=bounds, constraints=constraint)
# Display the potions we should make to maximize magnitude where the last column is quantity to make
total_magnitude = int(-res.fun)
num_potions = int(sum(res.x))
indices_to_make = np.nonzero(res.x > 0)
to_make_df = recipes.iloc[indices_to_make].copy()
to_make_df.loc[:,'QtyToMake'] = res.x[indices_to_make].astype(int)
to_make_df.head(len(to_make_df))
Magnitude | Type | Ingredient 1 | Ingredient 2 | Ingredient 3 | Effects | Effect 1 | Effect 2 | Effect 3 | Effect 4 | Effect 5 | MyPotionID | Can Make | QtyToMake | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
6 | 112 | Mixed | Frost Mirriam | Histcarp | Purple Mountain Flower | 4 | Damage Stamina Regen | Restore Stamina | Fortify Sneak | Resist Frost | NaN | 10371 | True | 2 |
7 | 110 | Mixed | Blue Butterfly Wing | Blue Mountain Flower | Butterfly Wing | 3 | Fortify Conjuration | Damage Magicka Regen | Restore Health | NaN | NaN | 2666 | True | 4 |
11 | 109 | Mixed | Blisterwort | Blue Mountain Flower | Spriggan Sap | 3 | Restore Health | Damage Magicka Regen | Fortify Smithing | NaN | NaN | 2210 | True | 3 |
19 | 108 | Mixed | Blisterwort | Blue Mountain Flower | Spider Egg | 3 | Restore Health | Damage Stamina | Damage Magicka Regen | NaN | NaN | 2209 | True | 1 |
31 | 108 | Mixed | Blue Mountain Flower | Bone Meal | Spider Egg | 3 | Fortify Conjuration | Damage Stamina | Damage Magicka Regen | NaN | NaN | 3416 | True | 4 |
33 | 108 | Mixed | Blue Mountain Flower | Glow Dust | Hagraven Feathers | 3 | Damage Magicka Regen | Damage Magicka | Fortify Conjuration | NaN | NaN | 3628 | True | 1 |
34 | 108 | Mixed | Blue Mountain Flower | Glow Dust | Swamp Fungal Pod | 3 | Damage Magicka Regen | Resist Shock | Restore Health | NaN | NaN | 3643 | True | 1 |
35 | 108 | Mixed | Blue Mountain Flower | Rock Warbler Egg | Spider Egg | 3 | Restore Health | Damage Stamina | Damage Magicka Regen | NaN | NaN | 3780 | True | 1 |
45 | 107 | Mixed | Creep Cluster | Ectoplasm | Skeever Tail | 3 | Restore Magicka | Damage Stamina Regen | Damage Health | NaN | NaN | 6318 | True | 1 |
57 | 107 | Mixed | Frost Mirriam | Purple Mountain Flower | Skeever Tail | 3 | Resist Frost | Fortify Sneak | Damage Stamina Regen | NaN | NaN | 10519 | True | 1 |
103 | 105 | Mixed | Blue Mountain Flower | Lavender | Nightshade | 2 | Fortify Conjuration | Damage Magicka Regen | NaN | NaN | NaN | 3749 | True | 7 |
104 | 105 | Mixed | Blue Mountain Flower | Lavender | Spider Egg | 2 | Fortify Conjuration | Damage Magicka Regen | NaN | NaN | NaN | 3755 | True | 2 |
303 | 59 | Potion | Blue Dartwing | Swamp Fungal Pod | NaN | 2 | Resist Shock | Restore Health | NaN | NaN | NaN | 3388 | True | 1 |
334 | 57 | Mixed | Deathbell | Salt Pile | Taproot | 3 | Slow | Weakness to Magic | Regenerate Magicka | NaN | NaN | 8001 | True | 1 |
361 | 55 | Poison | River Betty | Salt Pile | Troll Fat | 2 | Slow | Damage Health | NaN | NaN | NaN | 15006 | True | 2 |
367 | 53 | Poison | Deathbell | Nirnroot | Salt Pile | 2 | Damage Health | Slow | NaN | NaN | NaN | 7937 | True | 2 |
370 | 53 | Poison | Deathbell | Salt Pile | Troll Fat | 2 | Slow | Damage Health | NaN | NaN | NaN | 8004 | True | 1 |
379 | 50 | Poison | Deathbell | Salt Pile | NaN | 1 | Slow | NaN | NaN | NaN | NaN | 8006 | True | 2 |
384 | 17 | Mixed | Elves Ear | Fire Salts | Salt Pile | 4 | Restore Magicka | Weakness to Frost | Resist Fire | Regenerate Magicka | NaN | 8930 | True | 1 |
391 | 16 | Potion | Dragons Tongue | Fly Amanita | Scaly Pholiota | 4 | Resist Fire | Fortify Two-handed | Fortify Illusion | Regenerate Stamina | NaN | 8094 | True | 1 |
397 | 15 | Potion | Garlic | Taproot | Vampire Dust | 3 | Regenerate Magicka | Restore Magicka | Regenerate Health | NaN | NaN | 11060 | True | 1 |
400 | 14 | Mixed | Ectoplasm | Giant Lichen | Void Salts | 4 | Restore Magicka | Weakness to Shock | Damage Health | Fortify Magicka | NaN | 8576 | True | 1 |
443 | 12 | Mixed | Canis Root | Imp Stool | Rock Warbler Egg | 4 | Paralysis | Restore Health | Fortify One-Handed | Damage Stamina | NaN | 5088 | True | 2 |
461 | 12 | Mixed | Luna Moth Wing | Nordic Barnacle | Orange Dartwing | 3 | Damage Magicka | Regenerate Health | Fortify Pickpocket | NaN | NaN | 14094 | True | 1 |
465 | 12 | Potion | Dragons Tongue | Elves Ear | Mora Tapinella | 3 | Resist Fire | Restore Magicka | Fortify Illusion | NaN | NaN | 8058 | True | 2 |
468 | 12 | Potion | Honeycomb | Purple Mountain Flower | Slaughterfish Scales | 3 | Restore Stamina | Resist Frost | Fortify Block | NaN | NaN | 12905 | True | 1 |
469 | 12 | Potion | Mudcrab Chitin | Purple Mountain Flower | Thistle Branch | 3 | Restore Stamina | Resist Frost | Resist Poison | NaN | NaN | 14343 | True | 2 |
470 | 11 | Mixed | Ectoplasm | Red Mountain Flower | NaN | 3 | Restore Magicka | Fortify Magicka | Damage Health | NaN | NaN | 8873 | True | 1 |
493 | 11 | Mixed | Dragons Tongue | Elves Ear | White Cap | 3 | Resist Fire | Weakness to Frost | Restore Magicka | NaN | NaN | 8067 | True | 2 |
516 | 11 | Mixed | Elves Ear | Snowberries | White Cap | 3 | Resist Fire | Weakness to Frost | Restore Magicka | NaN | NaN | 9130 | True | 2 |
582 | 10 | Mixed | Elves Ear | Ice Wraith Teeth | White Cap | 3 | Weakness to Frost | Fortify Heavy Armor | Restore Magicka | NaN | NaN | 9037 | True | 2 |
658 | 9 | Mixed | Bone Meal | Lavender | Nirnroot | 3 | Fortify Conjuration | Damage Stamina | Resist Magic | NaN | NaN | 4095 | True | 1 |
704 | 9 | Mixed | Purple Mountain Flower | Snowberries | Torchbug Thorax | 3 | Resist Frost | Restore Stamina | Lingering Damage Magicka | NaN | NaN | 14933 | True | 3 |
760 | 9 | Potion | Ectoplasm | Elves Ear | Tundra Cotton | 2 | Restore Magicka | Fortify Magicka | NaN | NaN | NaN | 8459 | True | 1 |
765 | 9 | Potion | Ectoplasm | Giant Lichen | Tundra Cotton | 2 | Restore Magicka | Fortify Magicka | NaN | NaN | NaN | 8575 | True | 1 |
804 | 9 | Potion | Garlic | Lavender | Luna Moth Wing | 2 | Fortify Stamina | Regenerate Health | NaN | NaN | NaN | 10953 | True | 1 |
806 | 9 | Potion | Garlic | Lavender | Salt Pile | 2 | Fortify Stamina | Regenerate Magicka | NaN | NaN | NaN | 10961 | True | 5 |
855 | 9 | Potion | Honeycomb | Purple Mountain Flower | Tundra Cotton | 2 | Restore Stamina | Fortify Block | NaN | NaN | NaN | 12911 | True | 3 |
879 | 8 | Mixed | Luna Moth Wing | Nordic Barnacle | NaN | 2 | Damage Magicka | Regenerate Health | NaN | NaN | NaN | 14098 | True | 1 |
1017 | 8 | Mixed | Hagraven Feathers | Lavender | Luna Moth Wing | 2 | Fortify Conjuration | Damage Magicka | NaN | NaN | NaN | 12101 | True | 1 |
1144 | 8 | Potion | Orange Dartwing | Purple Mountain Flower | Snowberries | 2 | Restore Stamina | Resist Frost | NaN | NaN | NaN | 14612 | True | 1 |
1443 | 7 | Potion | Purple Mountain Flower | Slaughterfish Scales | Tundra Cotton | 2 | Resist Frost | Fortify Block | NaN | NaN | NaN | 14922 | True | 2 |
print(f"To maximize magnitude and therefore value, create {num_potions} potions of the {len(to_make_df)} unique types listed above for a total magnitude of {total_magnitude}.")
To maximize magnitude and therefore value, create 76 potions of the 42 unique types listed above for a total magnitude of 3905.