Interfacing with Gurobi: A Diet Selection Optimiser

You can see this app running online at: Diet Selection Optimiser

What this app does:

  • Solves a diet selection optimisation problem

This app aims to demonstrate:

Gurobi worked example

  • The code that solves the optimisation problem within this app is taken from an online Gurobi example and is used with permission.

Setup Instructions

Before you can run this app, you will need to install Gurobi, and obtain a trial license. Details can be found on the Gurobi website

Next, use the app name 'tropofy_gurobi_diet_model' to quickstart as in Running and Debugging Tropofy Apps

Full Source

"""
Authors: www.tropofy.com and www.gurobi.com

Copyright 2015 Tropofy Pty Ltd, all rights reserved.
Copyright 2013, Gurobi Optimization, Inc.

This source file (where not indicated as under the copyright of Gurobi)
is part of Tropofy and governed by the Tropofy terms of service
available at: http://www.tropofy.com/terms_of_service.html

Parts of the formulation provided by Gurobi have been modified.
The original example is in the Gurobi installation in the example file dietmodel.py

Used with permission.

This source file is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. See the license files for details.
"""

import gurobipy # Note eclipse has problems importing quicksum so we don't import selectively
import pkg_resources
from sqlalchemy.types import Text, Float
from sqlalchemy.schema import Column, ForeignKeyConstraint, UniqueConstraint
from sqlalchemy.orm import relationship

from tropofy.app import AppWithDataSets, Step, StepGroup
from tropofy.widgets import ExecuteFunction, SimpleGrid, Chart
from tropofy.database.tropofy_orm import DataSetMixin
from tropofy.file_io import read_write_xl


class Food(DataSetMixin):
    name = Column(Text, nullable=False)
    cost = Column(Float, nullable=False)

    @classmethod
    def get_table_args(cls):
        return (UniqueConstraint('data_set_id', 'name'),)


class NutritionCategory(DataSetMixin):
    name = Column(Text, nullable=False)
    min_amount = Column(Float, nullable=False)
    max_amount = Column(Float, nullable=False)

    @classmethod
    def get_table_args(cls):
        return (UniqueConstraint('data_set_id', 'name'),)


class FoodContents(DataSetMixin):
    food_name = Column(Text, nullable=False)
    nutrition_category_name = Column(Text, nullable=False)
    amount = Column(Float, nullable=False)

    @classmethod
    def get_table_args(cls):
        return (
            UniqueConstraint('data_set_id', 'food_name', 'nutrition_category_name'),
            ForeignKeyConstraint(['food_name', 'data_set_id'], ['food.name', 'food.data_set_id'], ondelete='CASCADE', onupdate='CASCADE'),
            ForeignKeyConstraint(['nutrition_category_name', 'data_set_id'], ['nutritioncategory.name', 'nutritioncategory.data_set_id'], ondelete='CASCADE', onupdate='CASCADE')
        )


class FoodServing(DataSetMixin):
    food_name = Column(Text, nullable=False)
    amount = Column(Float, nullable=False)

    food = relationship(Food)

    @classmethod
    def get_table_args(cls): 
        return (ForeignKeyConstraint(['food_name', 'data_set_id'], ['food.name', 'food.data_set_id'], ondelete='CASCADE', onupdate='CASCADE'),)


class ExecuteGurobiSolver(ExecuteFunction):

    def get_button_text(self, app_session):
        return "Solve Diet Selection Problem"

    def execute_function(self, app_session):
        formulate_and_solve_diet_selection_problem(app_session)


class BudgetPieChart(Chart):
    def get_chart_type(self, app_session):
        return Chart.PIECHART

    def get_table_schema(self, app_session):
        return {"food": ("string", "food"), "spend": ("number", "Spend")}

    def get_table_data(self, app_session):
        return [{"food": fs.food_name, "spend": fs.amount * fs.food.cost} for fs in app_session.data_set.query(FoodServing).all()]

    def get_column_ordering(self, app_session):
        return ["food", "spend"]

    def get_order_by_column(self, app_session):
        return "food"

    def get_chart_options(self, app_session):
        return {'title': 'Diet Cost Breakdown'}


class NutritionColumnChart(Chart):
    def get_chart_type(self, app_session):
        return Chart.COLUMNCHART

    def get_table_schema(self, app_session):
        return {"nutrition_category": ("string", "Category"), "amount": ("number", "Amount"), "diff_to_maximum": ("number", "Difference to maximum")}

    def get_table_data(self, app_session):
        nutritionValues = dict(
            (f, dict((c, app_session.data_set.query(FoodContents).filter(FoodContents.food_name == f.name).filter(FoodContents.nutrition_category_name == c.name).one().amount)
                                     for c in app_session.data_set.query(NutritionCategory).all())) for f in app_session.data_set.query(Food).all())
        category = dict((c, sum([nutritionValues[fs.food][c] * fs.amount for fs in app_session.data_set.query(FoodServing).all()])) for c in app_session.data_set.query(NutritionCategory).all())
        return [{"nutrition_category": c.name, "amount": category[c], "diff_to_maximum": c.max_amount - category[c]} for c in app_session.data_set.query(NutritionCategory).all()]

    def get_column_ordering(self, app_session):
        return ["nutrition_category", "amount", "diff_to_maximum"]

    def get_order_by_column(self, app_session):
        return "nutrition_category"

    def get_chart_options(self, app_session):
        return {
            'isStacked': 'true',
            'title': 'Nutrition categories with difference to maximum',
            'series': [{'color': '#FFB82C'}, {'color': '#006FB9'}]
        }


class GurobiDietSelectionApp(AppWithDataSets):

    def get_name(self):
        return 'Gurobi Diet Selection Optimiser'

    def get_examples(self):
        return {"Demo data set from Gurobi": load_gurobi_data}

    def get_static_content_path(self, app_session):
        return pkg_resources.resource_filename('te_gurobi_diet_model', 'static')

    def get_gui(self):
        step_group1 = StepGroup(name='Food and nutrition data')
        step_group1.add_step(Step(name='Enter your food selection', widgets=[SimpleGrid(Food)]))
        step_group1.add_step(Step(name='Enter your nutrition categories', widgets=[SimpleGrid(NutritionCategory)]))
        step_group1.add_step(Step(name='Enter food contents', widgets=[SimpleGrid(FoodContents)]))

        step_group2 = StepGroup(name='Solve diet selection problem')
        step_group2.add_step(Step(name='Solve diet selection problem using Gurobi', widgets=[ExecuteGurobiSolver()]))

        step_group3 = StepGroup(name='Food servings')
        step_group3.add_step(Step(
            name='Enjoy your meal',
            widgets=[
                {"widget": BudgetPieChart(), "cols": 6},
                {"widget": NutritionColumnChart(), "cols": 6},
                {"widget": SimpleGrid(FoodServing), "cols": 12}
            ])
        )

        return [step_group1, step_group2, step_group3]

    def get_icon_url(self):
        return "/{}/static/{}/gurobi_diet_tool.png".format(
            self.url_name,
            self.get_app_version(),
        )


def load_gurobi_data(app_session):
    read_write_xl.ExcelReader.load_data_from_excel_file_on_disk(
        app_session,
        pkg_resources.resource_filename('te_gurobi_diet_model', 'gurobi_diet_data.xlsx')
    )


def formulate_and_solve_diet_selection_problem(app_session):
    # Copyright 2013, Gurobi Optimization, Inc.
    # Adapted by Tropofy Pty Ltd to integrate with the Tropofy Platform
    # Solve the classic diet model

    app_session.data_set.query(FoodServing).delete()  # delete the previous solution

    # This data structure is different to the gurobi example
    nutritionValues = dict((f, dict((c, app_session.data_set.query(FoodContents).filter(FoodContents.food_name == f.name).filter(FoodContents.nutrition_category_name == c.name).one().amount)
                                     for c in app_session.data_set.query(NutritionCategory).all())) for f in app_session.data_set.query(Food).all())

    # Model
    m = gurobipy.Model("diet")

    # Create decision variables for the nutrition information, which we limit via bounds
    nutrition = dict((c, m.addVar(lb=c.min_amount, ub=c.max_amount, name=c.name.encode('ascii', 'ignore'))) for c in app_session.data_set.query(NutritionCategory).all())

    # Create decision variables for the foods to buy
    buy = dict((f, m.addVar(obj=f.cost, name=f.name.encode('ascii', 'ignore'))) for f in app_session.data_set.query(Food).all())

    # The objective is to minimize the costs
    m.modelSense = gurobipy.GRB.MINIMIZE

    # Update model to integrate new variables
    m.update()

    # Nutrition constraints
    for c in app_session.data_set.query(NutritionCategory).all():
        m.addConstr(gurobipy.quicksum(nutritionValues[f][c] * buy[f] for f in app_session.data_set.query(Food).all()) == nutrition[c], c.name.encode('ascii', 'ignore'))

    def printSolution(app_session):
        if m.status == gurobipy.GRB.status.OPTIMAL:
            app_session.task_manager.send_progress_message("Cost = %s" % m.objVal)
            app_session.task_manager.send_progress_message("<br>Buy:")
            for f in app_session.data_set.query(Food).all():
                if buy[f].x > 0.0001:
                    app_session.task_manager.send_progress_message(f.name + " " + str(buy[f].x))
                    app_session.data_set.add(FoodServing(food_name=f.name, amount=buy[f].x))

            app_session.task_manager.send_progress_message("<br>Nutrition:")
            for c in app_session.data_set.query(NutritionCategory).all():
                app_session.task_manager.send_progress_message(c.name + " " + str(nutrition[c].x))
        else:
            app_session.task_manager.send_progress_message('No solution')

    # Solve
    m.optimize()
    printSolution(app_session)