Quick Start using Pandas dataframes

pandasdiet.ipynb Open In Colab Kaggle Gradient Open In SageMaker Studio Lab Hits

Description: Quick Start using Pandas dataframes to load and retrieve data

Tags: amplpy, quick-start, pandas, highlights

Notebook author: Filipe Brandão <fdabrandao@gmail.com>

# Install dependencies
%pip install -q amplpy pandas numpy
# Google Colab & Kaggle integration
from amplpy import AMPL, ampl_notebook

ampl = ampl_notebook(
    modules=["highs"],  # modules to install
    license_uuid="default",  # license to use
)  # instantiate AMPL object and register magics

Diet Model

Consider the problem of choosing prepared foods to meet certain nutritional requirements.

Sets:

  • NUTR: set of nutrients to consider

  • FOOD: set of food to consider

Parameters:

  • cost {FOOD}: cost of each food

  • f_min {FOOD}: minimum amount of food to buy

  • f_max {FOOD}: maximum amount of food to buy

  • n_min {NUTR}: minimum amount required of each nutrient

  • n_max {NUTR}: maximum amount allowed of each nutrient

  • amt {NUTR, FOOD}: amount of each nutrient in each food

Variables:

  • Buy {FOOD}: amount of food to buy

Objective:

  • Total_Cost: total cost of the diet

Constraints:

  • Diet {NUTR}: ensure that the nutritional requirements are satisfied by the diet.

The problem is then modeled as follows:

%%ampl_eval
reset;
set NUTR;
set FOOD;

param cost {FOOD} > 0;
param f_min {FOOD} >= 0;
param f_max {j in FOOD} >= f_min[j];

param n_min {NUTR} >= 0;
param n_max {i in NUTR} >= n_min[i];

param amt {NUTR,FOOD} >= 0;

var Buy {j in FOOD} >= f_min[j], <= f_max[j];

minimize Total_Cost:  sum {j in FOOD} cost[j] * Buy[j];

subject to Diet {i in NUTR}:
n_min[i] <= sum {j in FOOD} amt[i,j] * Buy[j] <= n_max[i];

Prepare pandas.DataFrame objects will all the data

import pandas as pd
import numpy as np

food_df = pd.DataFrame(
    [
        ("BEEF", 3.59, 2, 10),
        ("CHK", 2.59, 2, 10),
        ("FISH", 2.29, 2, 10),
        ("HAM", 2.89, 2, 10),
        ("MCH", 1.89, 2, 10),
        ("MTL", 1.99, 2, 10),
        ("SPG", 1.99, 2, 10),
        ("TUR", 2.49, 2, 10),
    ],
    columns=["FOOD", "cost", "f_min", "f_max"],
).set_index("FOOD")

# Create a pandas.DataFrame with data for n_min, n_max
nutr_df = pd.DataFrame(
    [
        ("A", 700, 20000),
        ("C", 700, 20000),
        ("B1", 700, 20000),
        ("B2", 700, 20000),
        ("NA", 0, 50000),
        ("CAL", 16000, 24000),
    ],
    columns=["NUTR", "n_min", "n_max"],
).set_index("NUTR")

amt_df = pd.DataFrame(
    np.matrix(
        [
            [60, 8, 8, 40, 15, 70, 25, 60],
            [20, 0, 10, 40, 35, 30, 50, 20],
            [10, 20, 15, 35, 15, 15, 25, 15],
            [15, 20, 10, 10, 15, 15, 15, 10],
            [928, 2180, 945, 278, 1182, 896, 1329, 1397],
            [295, 770, 440, 430, 315, 400, 379, 450],
        ]
    ),
    columns=food_df.index.tolist(),
    index=nutr_df.index.tolist(),
)

Pandas dataframes

Cost, minimum, and maximum amount of each food:

food_df
cost f_min f_max
FOOD
BEEF 3.59 2 10
CHK 2.59 2 10
FISH 2.29 2 10
HAM 2.89 2 10
MCH 1.89 2 10
MTL 1.99 2 10
SPG 1.99 2 10
TUR 2.49 2 10

Minimum and maximum amounts of each nutrient:

nutr_df
n_min n_max
NUTR
A 700 20000
C 700 20000
B1 700 20000
B2 700 20000
NA 0 50000
CAL 16000 24000

Amount of each nutrient in each food:

amt_df
A C B1 B2 NA CAL
BEEF 60 20 10 15 928 295
CHK 8 0 20 20 2180 770
FISH 8 10 15 10 945 440
HAM 40 40 35 10 278 430
MCH 15 35 15 15 1182 315
MTL 70 30 15 15 896 400
SPG 25 50 25 15 1329 379
TUR 60 20 15 10 1397 450

Load the data from the pandas.DataFrame objects

# 1. Send the data from "amt_df" to AMPL and initialize the indexing set "FOOD"
ampl.set_data(food_df, "FOOD")
# 2. Send the data from "nutr_df" to AMPL and initialize the indexing set "NUTR"
ampl.set_data(nutr_df, "NUTR")
# 3. Set the values for the parameter "amt" using "amt_df"
ampl.get_parameter("amt").set_values(amt_df)

Solve with HiGHS

# Specify the solver to use (e.g., HiGHS)
ampl.option["solver"] = "highs"
# Solve
ampl.solve()
# Stop if the model was not solved
assert ampl.get_value("solve_result") == "solved"
# Get objective entity by AMPL name
totalcost = ampl.get_objective("Total_Cost")
# Print it
print("Objective is:", totalcost.value())
HiGHS 1.4.0:HiGHS 1.4.0: optimal solution; objective 119.9897589
4 simplex iterations
0 barrier iterations
absmipgap=119.99, relmipgap=inf
Objective is: 119.98975893599335

Reassign data for specific instances and resolve

# Reassign data - specific instances
cost = ampl.get_parameter("cost")
cost.set_values({"BEEF": 5.01, "HAM": 4.55})
print("Increased costs of beef and ham.")

# Resolve and display objective
ampl.solve()
# Stop if the model was not solved
assert ampl.get_value("solve_result") == "solved"
print("New objective value:", totalcost.value())
Increased costs of beef and ham.
HiGHS 1.4.0:HiGHS 1.4.0: optimal solution; objective 144.0120033
0 simplex iterations
0 barrier iterations
absmipgap=144.012, relmipgap=inf
New objective value: 144.01200332502077

Reassign data for all instances and resolve

# Reassign data - all instances
cost.set_values(
    {
        "BEEF": 3,
        "CHK": 5,
        "FISH": 5,
        "HAM": 6,
        "MCH": 1,
        "MTL": 2,
        "SPG": 5.01,
        "TUR": 4.55,
    }
)
print("Updated all costs.")

# Resolve and display objective
ampl.solve()
# Stop if the model was not solved
assert ampl.get_value("solve_result") == "solved"
print("New objective value:", totalcost.value())
Updated all costs.
HiGHS 1.4.0:HiGHS 1.4.0: optimal solution; objective 164.10625
4 simplex iterations
0 barrier iterations
absmipgap=164.106, relmipgap=inf
New objective value: 164.10625

Get the values of the variable Buy in a pandas.DataFrame object

df = ampl.get_variable("Buy").to_pandas()
df
Buy.val
BEEF 10.000000
CHK 2.000000
FISH 2.000000
HAM 6.851974
MCH 10.000000
MTL 10.000000
SPG 6.765351
TUR 2.000000

Get the values of an expression into a pandas.DataFrame object

df = ampl.get_data("{j in FOOD} 100*Buy[j]/Buy[j].ub").to_pandas()
df
100*Buy[j]/(Buy[j].ub)
BEEF 100.000000
CHK 20.000000
FISH 20.000000
HAM 68.519737
MCH 100.000000
MTL 100.000000
SPG 67.653509
TUR 20.000000