Diet model with Google Sheets#

Description: Diet model using Google Sheets

Tags: amplpy, google-sheets, example

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

Model author: N/A

References: N/A

This notebook uses the snippet from https://colab.research.google.com/notebooks/snippets/sheets.ipynb in order to load data from the Goolge Sheet at https://docs.google.com/spreadsheets/d/1sTyJdgnMCrmuZDtUjs-cOpRLoKgByM8U-lHieNBNaRY/edit?usp=sharing

Autheticate in order to use Google Sheets#

from google.colab import auth

auth.authenticate_user()

# Install dependencies
%pip install -q amplpy gspread --upgrade

# Google Colab & Kaggle integration
from amplpy import AMPL, ampl_notebook

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

Use `%%ampl_eval` to evaluate AMPL commands#

%%ampl_eval
option version;

option version 'AMPL Version 20220219 (Linux-5.4.0-1069-azure, 64-bit)\
Demo license with maintenance expiring 20240131.\
Using license file "/content/ampl.linux-intel64/ampl.lic".\
';

Define the model#

%%ampl_eval
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];

Instatiate gspread client#

import gspread
from google.auth import default

creds, _ = default()
gclient = gspread.authorize(creds)


def open_spreedsheet(name):
    if name.startswith("https://"):
        return gclient.open_by_url(name)
    return gclient.open(name)

Open speedsheet using name or URL#

# spreedsheet = open_spreedsheet('DietModelSheet')
spreedsheet = open_spreedsheet(
    "https://docs.google.com/spreadsheets/d/1sTyJdgnMCrmuZDtUjs-cOpRLoKgByM8U-lHieNBNaRY/edit?usp=sharing"
)


def get_worksheet_values(name):
    return spreedsheet.worksheet(name).get_values(
        value_render_option="UNFORMATTED_VALUE"
    )

Define auxiliar functions to convert data from worksheets into dataframes#

import pandas as pd


def table_to_dataframe(rows):
    return pd.DataFrame(rows[1:], columns=rows[0]).set_index(rows[0][0])


def matrix_to_dataframe(rows, tr=False):
    col_labels = rows[0][1:]
    row_labels = [row[0] for row in rows[1:]]

    def label(pair):
        return pair if not tr else (pair[1], pair[0])

    data = {
        label((rlabel, clabel)): rows[i + 1][j + 1]
        for i, rlabel in enumerate(row_labels)
        for j, clabel in enumerate(col_labels)
    }
    df = pd.Series(data).reset_index()
    df.columns = ["index1", "index2", rows[0][0]]
    return df.set_index(["index1", "index2"])

Load data from the first worksheet#

rows = get_worksheet_values("FOOD")
df = table_to_dataframe(rows)
ampl.set_data(df, set_name="FOOD")  # send the data to AMPL
df

	cost	f_min	f_max
FOOD
BEEF	3.19	0	100
CHK	2.59	0	100
FISH	2.29	0	100
HAM	2.89	0	100
MCH	1.89	0	100
MTL	1.99	0	100
SPG	1.99	0	100
TUR	2.49	0	100

Load the data from the second worksheet#

rows = get_worksheet_values("NUTR")
df = table_to_dataframe(rows)
ampl.set_data(df, set_name="NUTR")  # Send the data to AMPL
df

	n_min	n_max
NUTR
A	700	10000
C	700	10000
B1	700	10000
B2	700	10000

Load the data from the third worksheet#

rows = get_worksheet_values("amt")
df = matrix_to_dataframe(rows, tr=True)
ampl.set_data(df)  # Send the data to AMPL
df

		amt
index1	index2
A	BEEF	60
C	BEEF	20
B1	BEEF	10
B2	BEEF	15
A	CHK	8
C	CHK	0
B1	CHK	20
B2	CHK	20
A	FISH	8
C	FISH	10
B1	FISH	15
B2	FISH	10
A	HAM	40
C	HAM	40
B1	HAM	35
B2	HAM	10
A	MCH	15
C	MCH	35
B1	MCH	15
B2	MCH	15
A	MTL	70
C	MTL	30
B1	MTL	15
B2	MTL	15
A	SPG	25
C	SPG	50
B1	SPG	25
B2	SPG	15
A	TUR	60
C	TUR	20
B1	TUR	15
B2	TUR	10

Use `%%ampl_eval` to solve the model with cbc#

%%ampl_eval
option solver cbc;
solve;
display Buy;

CBC 2.10.5: CBC 2.10.5 optimal, objective 88.2
1 iterations
Buy [*] :=
BEEF   0
 CHK   0
FISH   0
 HAM   0
 MCH  46.6667
 MTL   0
 SPG   0
 TUR   0
;

Retrieve the solution as a pandas dataframe#

ampl.var["Buy"].to_pandas()

	Buy.val
BEEF	0.000000
CHK	0.000000
FISH	0.000000
HAM	0.000000
MCH	46.666667
MTL	0.000000
SPG	0.000000
TUR	0.000000