### Install specific webinar versions

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Installs the exact versions of gurobipy-pandas, gurobipy, and pandas used in the webinar. This is recommended for reproducibility.

```bash
pip install gurobipy-pandas==1.0.0 gurobipy==10.0.0 pandas==1.5.3
```

--------------------------------

### Install gurobipy-pandas from PyPI

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/installation.md

Install the library using pip. This command also installs pandas and gurobipy as dependencies.

```bash
python -m pip install gurobipy-pandas
```

--------------------------------

### Install gurobipy-pandas

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/README.md

Install the gurobipy-pandas package using pip. Ensure you have Python and pip installed.

```console
pip install gurobipy-pandas
```

--------------------------------

### Embed External Documentation Example

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Embeds an external interactive example from the GurobiPy-Pandas documentation using an IFrame. This allows users to explore a more comprehensive example directly within the current environment.

```python
from IPython.display import IFrame
project_team_url = "https://gurobi-optimization-gurobipy-pandas.readthedocs-hosted.com/en/latest/examples/projects.html"
IFrame(project_team_url, 1000, 500)
```

--------------------------------

### Load example data into pandas DataFrames

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/gppd-in-90-seconds.ipynb

Loads project, team, and project-team profit data from CSV strings into pandas DataFrames, setting appropriate indices.

```python
# Example data
import io
projects_csv = io.StringIO("project,resource\n0,1.1\n1,1.4\n2,1.2\n3,1.1\n4,0.9\n5,1.3\n6,1.0\n7,1.6\n8,1.7\n9,0.9\n10,1.5\n11,1.1\n12,1.2\n13,1.7\n14,0.4\n15,0.4\n16,0.3\n17,1.5\n18,1.5\n19,1.6\n20,1.8\n21,1.5\n22,1.0\n23,1.5\n24,0.5\n25,1.3\n26,0.5\n27,1.7\n28,1.1\n29,0.9\n")
projects = pd.read_csv(projects_csv).assign(project=lambda df: df['project'].apply("p{}".format)).set_index('project')
teams_csv = io.StringIO("team,capacity\n0,2.4\n1,1.8\n2,1.1\n3,1.9\n4,1.4\n")
teams = pd.read_csv(teams_csv).assign(team=lambda df: df['team'].apply("t{}".format)).set_index('team')
project_values_csv = io.StringIO("project,team,profit\n0,4,0.4\n1,4,1.3\n2,0,1.7\n2,1,1.7\n2,2,1.7\n2,3,1.7\n2,4,1.7\n3,4,1.3\n4,0,1.3\n4,1,1.3\n4,2,1.3\n4,3,1.3\n4,4,1.3\n5,0,1.8\n5,1,1.8\n5,2,1.8\n5,3,1.8\n5,4,1.8\n6,0,1.2\n6,1,1.2\n6,2,1.2\n6,3,1.2\n6,4,1.2\n7,3,0.9\n7,4,0.9\n8,3,1.0\n8,4,1.0\n9,4,1.2\n10,0,0.8\n10,1,0.8\n10,2,0.8\n10,3,0.8\n10,4,0.8\n11,0,1.3\n11,1,1.3\n11,2,1.3\n11,3,1.3\n11,4,1.3\n12,3,0.8\n12,4,0.8\n13,0,1.5\n13,1,1.5\n13,2,1.5\n13,3,1.5\n13,4,1.5\n14,3,1.7\n14,4,1.7\n15,4,1.3\n16,4,0.3\n17,0,1.2\n17,1,1.2\n17,2,1.2\n17,3,1.2\n17,4,1.2\n18,3,1.3\n18,4,1.3\n19,3,1.8\n19,4,1.8\n20,3,1.6\n20,4,1.6\n21,3,1.1\n21,4,1.1\n22,4,0.4\n23,4,1.0\n24,4,0.3\n25,0,1.0\n25,1,1.0\n25,2,1.0\n25,3,1.0\n25,4,1.0\n26,4,1.8\n27,3,0.8\n27,4,0.8\n28,0,1.0\n28,1,1.0\n28,2,1.0\n28,3,1.0\n28,4,1.0\n29,3,1.3\n29,4,1.3\n")
project_values = pd.read_csv(project_values_csv).assign(
    team=lambda df: df['team'].apply("t{}".format),
    project=lambda df: df['project'].apply("p{}".format),
).set_index(["project", "team"])
```

--------------------------------

### Add Startup Constraints (Rolling Window)

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/unit-commitment.md

Models the relationship between active generators and startups using a rolling-window constraint. This captures the number of generators started between adjacent time periods.

```python
# Constrain the relationship between active generators and startups.

def startup_constraints(group):
    group = group.sort_index()
    return gppd.add_constrs(
        model,
        group["num_startup"].iloc[1:],
        GRB.GREATER_EQUAL,
        group["num_active"].diff().dropna(),
        name="startup",
        index_formatter=short_time,
    )

startup = generators.groupby("generator_class").apply(startup_constraints).droplevel(0)
```

--------------------------------

### Create Databricks Job

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Create a Databricks job using the provided workflow.json file. This command initiates the job setup process.

```bash
databricks jobs create --json @workflow.json
```

--------------------------------

### Gurobi Model Definition Example

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Example of defining variables, setting the objective, and adding constraints within a Gurobi model using Python.

```python
x = m.addVar(vtype=GRB.BINARY, name="x")
...
m.setObjective(x + y + 2 * z, GRB.MAXIMIZE)
m.addConstr(x + 2 * y + 3 * z <= 4, "c0")
...
```

--------------------------------

### Optimize Model and Get Solution Values

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Optimize the Gurobi model and retrieve the solution values for the 'x' variables as a pandas Series. This is the standard way to get the results of an optimization.

```python
model.optimize()
x.gppd.X.head()
```

--------------------------------

### Get Filename

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2407-data-first/netflow.ipynb

Calls the GUI file selector function to obtain the path to the data file.

```python
filename = gui_fname()
```

--------------------------------

### Extract Constraint Slack Values

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Determines the slack in inequality constraints using the .gppd.Slack accessor on a constraint series. This example shows the spare capacity of each team in the current assignment.

```python
capacity_constraints.gppd.Slack
```

--------------------------------

### Add Initial Startup Constraints

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/unit-commitment.md

Captures the startups at the very first time period. This constraint relates the initial number of active generators to the initial state and the number of startups.

```python
# Separately capture the startups at time period 0.

time_period_1 = generators.sort_index().groupby("generator_class").first()
initial_startup = gppd.add_constrs(
    model,
    time_period_1["num_startup"],
    GRB.GREATER_EQUAL,
    time_period_1["num_active"] - generator_data["state0"],
    name="initial_startup",
    index_formatter=short_time,
)
```

--------------------------------

### Create a GurobiPy Model

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Initializes a GurobiPy model. This is the entry point for creating optimization models and interacting with the solver.

```python
# Create a gurobipy model
model = gp.Model()
```

--------------------------------

### Initialize MathJax and Reveal.js

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/gppd-in-90-seconds.slides.html

Initializes MathJax for rendering mathematical equations and Reveal.js for presentation, with a delay to ensure proper loading.

```javascript
require( { // it makes sense to wait a little bit when you are loading // reveal from a cdn in a slow connection environment waitSeconds: 15 }, [ "https://unpkg.com/reveal.js@4.0.2/dist/reveal.js", "https://unpkg.com/reveal.js@4.0.2/plugin/notes/notes.js" ], function(Reveal, RevealNotes){ // Full list of configuration options available here: https://github.com/hakimel/reveal.js#configuration Reveal.initialize({ controls: true, progress: true, history: true, transition: "slide", slideNumber: "", plugins: [RevealNotes] }); var update = function(event){ if(MathJax.Hub.getAllJax(Reveal.getCurrentSlide())){ MathJax.Hub.Rerender(Reveal.getCurrentSlide()); } }; Reveal.addEventListener('slidechanged', update); function setScrollingSlide() { var scroll = false if (scroll === true) { var h = $('.reveal').height() * 0.95; $('section.present').find('section') .filter(function() { return $(this).height() > h; }) .css('height', 'calc(95vh)') .css('overflow-y', 'scroll') .css('margin-top', '20px'); } } // check and set the scrolling slide every time the slide change Reveal.addEventListener('slidechanged', setScrollingSlide); } );
```

--------------------------------

### Display Sample Assignments

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/2-optimize.ipynb

Displays a sample of 5 assignments from the optimized results. This is useful for a quick inspection of the solution.

```python
assignments.sample(5)
```

--------------------------------

### Get Model Objective

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Retrieve the linear expression of the model's objective function. This is useful for inspecting the objective after variables have been defined with coefficients.

```python
model.getObjective()
```

--------------------------------

### Update Model and Get Objective

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Updates the GurobiPy model after setting the objective and then retrieves the objective function. An update is necessary to reflect changes made to the model.

```python
model.update()
model.getObjective()
```

--------------------------------

### Set up interactive mode and Gurobi parameters

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/gppd-in-90-seconds.ipynb

Enables interactive mode for gurobipy-pandas and sets Gurobi to suppress output. Handy for live coding.

```python
import pandas as pd
import gurobipy as gp
from gurobipy import GRB
import gurobipy_pandas as gppd

# Handy trick for live coding, not for production
gppd.set_interactive()

# Quiet please
gp.setParam('OutputFlag', 0)
```

--------------------------------

### Display Project Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/gppd-in-90-seconds.slides.html

Displays the first few rows of project data, showing resource requirements for each project.

```python
projects.head(3)  # w_i
```

--------------------------------

### Generate Roster Table from Solution

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/workforce.md

Transforms the solution into a roster table format by unstacking the data and replacing numerical assignments with 'Y' for assigned and '-' for unassigned. This provides a clear overview of the schedule.

```python
shift_table = solution.unstack(0).fillna("-").replace({0.0: "-", 1.0: "Y"})
pd.options.display.max_rows = 15
shift_table
```

--------------------------------

### Performance Documentation Link

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Provides a link to the dedicated performance page in the GurobiPy-Pandas documentation. This is useful for understanding optimization performance tips and best practices, especially regarding data sparsity and preparation.

```python
performance_url = "https://gurobi-optimization-gurobipy-pandas.readthedocs-hosted.com/en/latest/performance.html"
```

```python
IFrame(performance_url, 1000, 300)
```

--------------------------------

### Create SQLAlchemy Engine

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/1b-staff-required.ipynb

Initializes a SQLAlchemy engine to connect to the SQLite database.

```python
import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine(f"sqlite:///{db_path}")
```

--------------------------------

### Configure Gurobi Cloud License

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/2-optimize.ipynb

Fetches Gurobi cloud license parameters from Databricks secrets. If secrets are not available, it defaults to using Gurobi's size-limited license.

```python
# If running in databricks, fetch Gurobi cloud license key from secrets.
# If unavailable, use Gurobi's free size-limited license to run the job.

try:
    gurobi_params = {
        "CloudAccessID": dbutils.secrets.get("grbcloud", "CloudAccessID"),
        "CloudSecretKey": dbutils.secrets.get("grbcloud", "CloudSecretKey"),
        "LicenseID": int(dbutils.secrets.get("grbcloud", "LicenseID")),
        "CSAppName": dbutils.secrets.get("grbcloud", "CSAppName"),
        "CloudPool": dbutils.secrets.get("grbcloud", "CloudPool"),
    }
except Exception as e:
    print("Databricks secrets are not set up.")
    print(f"Error message: {e}")
    print("Using Gurobi size-limited license.")
    gurobi_params = {}
```

--------------------------------

### Create Gurobi Model

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/regression.md

Initializes an empty Gurobi model instance.

```python
model = gp.Model()
```

--------------------------------

### Run Databricks Job

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Launch the Databricks job. This can be done via the Databricks UI or using the command line.

```bash
databricks jobs run-now
```

--------------------------------

### Project and Team Data Summary

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Prints the total number of projects and teams, total resource required by projects, and total resource available from teams.

```python
print(f"There are {len(projects)} projects and {len(teams)} teams.")
print(f"Total project resource required: {projects.resource.sum():.1f}")
print(f"Total team resource available: {teams.capacity.sum():.1f}")
```

--------------------------------

### Create Gurobi Model

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2407-data-first/netflow.ipynb

Initializes a Gurobi model with a specified name.

```python
m = grb.Model("netflow")
```

--------------------------------

### Create Gurobi model and add assignment variables

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/gppd-in-90-seconds.ipynb

Initializes a Gurobi model for maximization and adds binary variables representing project assignments using gurobipy-pandas.

```python
# gurobipy-pandas creates variables as a series
model = gp.Model()
model.ModelSense = GRB.MAXIMIZE
assignments = project_values.gppd.add_vars(
    model, vtype=GRB.BINARY, obj="profit", name="x"
)
```

--------------------------------

### Import Libraries and Configure Pandas

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/workforce.md

Imports necessary libraries including gurobipy, pandas, and gurobipy_pandas. Configures pandas display options and sets gurobipy_pandas to interactive mode.

```python
import gurobipy as gp
from gurobipy import GRB
import pandas as pd
import gurobipy_pandas as gppd

pd.options.display.max_rows = 8
gppd.set_interactive()
```

--------------------------------

### Set Gurobi Instant Cloud License ID

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Store your Gurobi Instant Cloud license ID as a secret within the 'grbcloud' scope. You will be prompted to enter the license ID.

```bash
databricks secrets put-secret grbcloud licenseid
```

--------------------------------

### Import Optimization Libraries

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/2-optimize.ipynb

Imports necessary libraries for optimization, including GurobiPy, GurobiPy-Pandas, and Pandas.

```python
import gurobipy as gp
from gurobipy import GRB
import pandas as pd
import gurobipy_pandas as gppd
```

--------------------------------

### Standard Imports for GurobiPy-Pandas

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Import necessary libraries including pandas, gurobipy, and gurobipy_pandas. Ensure gurobipy_pandas is set to interactive mode.

```python
import pandas as pd
import gurobipy as gp
from gurobipy import GRB
import gurobipy_pandas as gppd

gppd.set_interactive()
```

--------------------------------

### GUI File Selector

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2407-data-first/netflow.ipynb

Defines a function to open a graphical file dialog for selecting a data file. Requires PyQt5.

```python
%gui qt

from PyQt5.QtWidgets import QFileDialog

def gui_fname(dir=None):
    """Select a file via a dialog and return the file name."""
    if dir is None: dir ='./'
    fname = QFileDialog.getOpenFileName(None, "Select data file...", 
                dir, filter="All files (*);; SM Files (*.sm)")
    return fname[0]
```

--------------------------------

### Define and Execute SQL Query

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/1a-feasible-assignments.ipynb

Defines a SQL query to find feasible staff-shift assignments based on qualifications and availability. Executes the query using SQLAlchemy and Pandas, then saves the results to a feather file.

```python
query = """
SELECT
    shifts.id as shift_id,
    staff_qualifications.staff_id as staff_id
FROM shifts
INNER JOIN staff_qualifications ON (
    staff_qualifications.qualification_id = shifts.qualification_id
)
INNER JOIN availability ON (
    availability.staff_id == staff_qualifications.staff_id
    AND shifts.start_time >= availability.from_time
    AND shifts.end_time <= availability.to_time
)
INNER JOIN staff on (
    staff_qualifications.staff_id == staff.id
)
WHERE staff.active == 1
"""

with engine.connect() as connection:
    feasible_assignments = pd.read_sql(text(query), connection)

feasible_assignments.to_feather(model_data_path / "feasible_assignments.feather")
```

--------------------------------

### Sample Staffing Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/1b-staff-required.ipynb

Displays a random sample of 4 rows from the staff_required DataFrame.

```python
staff_required.sample(4)
```

--------------------------------

### Create Linear Expression with Scalar Arithmetic

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Demonstrates creating a linear expression by applying scalar arithmetic to a Series of GurobiPy variables. This operation results in a new Series where each element is a linear expression.

```python
i = pd.RangeIndex(5, name="i")
x = gppd.add_vars(model, i, name="x")
2*x + 5
```

--------------------------------

### Format Solution for Readability

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Transforms the solution values into a more readable format by filtering for assignments with a value of 0.9 or higher, resetting the index, and grouping by team to list allocated projects.

```python
(
    x.gppd.X.to_frame()
    .query("x >= 0.9").reset_index()
    .groupby("team").agg({"project": list})
)
```

--------------------------------

### Load Project Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Reads project data from a CSV file into a pandas DataFrame, setting the 'project' column as the index.

```python
projects = pd.read_csv("data/projects.csv", index_col="project")
projects.head()
```

--------------------------------

### Load and Prepare Diabetes Dataset

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/regression.md

Loads the diabetes regression dataset, scales the features using StandardScaler, adds a constant column for the intercept, and splits the data into training and testing sets.

```python
diabetes_X, diabetes_y = datasets.load_diabetes(return_X_y=True, as_frame=True)
scaler = StandardScaler()
diabetes_X_scaled = pd.DataFrame(
    data=scaler.fit_transform(diabetes_X),
    columns=diabetes_X.columns,
    index=diabetes_X.index,
)
diabetes_X_scaled["const"] = 1.0
X_train, X_test, y_train, y_test = train_test_split(
    diabetes_X_scaled, diabetes_y, random_state=42
)
```

--------------------------------

### Set Gurobi Instant Cloud API Key

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Store your Gurobi Instant Cloud API key as a secret within the 'grbcloud' scope. You will be prompted to enter the key.

```bash
databricks secrets put-secret grbcloud cloudaccessid
```

--------------------------------

### Load Project Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Loads project data from a CSV string into a Pandas DataFrame, setting the 'project' column as the index. This DataFrame represents project resource requirements.

```python
projects_csv = io.StringIO("project,resource\n0,1.1\n1,1.4\n2,1.2\n3,1.1\n4,0.9\n5,1.3\n6,1.0\n7,1.6\n8,1.7\n9,0.9\n10,1.5\n11,1.1\n12,1.2\n13,1.7\n14,0.4\n15,0.4\n16,0.3\n17,1.5\n18,1.5\n19,1.6\n20,1.8\n21,1.5\n22,1.0\n23,1.5\n24,0.5\n25,1.3\n26,0.5\n27,1.1\n28,1.1\n29,0.9\n")
projects = pd.read_csv(projects_csv, index_col="project")
projects.head(3)  # w_i
```

--------------------------------

### Create Gurobi Instant Cloud Secrets Scope

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Create a secrets scope named 'grbcloud' in Databricks to store Gurobi Instant Cloud credentials. This is the first step in configuring remote optimization.

```bash
databricks secrets create-scope grbcloud
```

--------------------------------

### Set Gurobi Instant Cloud Secret Key

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Store your Gurobi Instant Cloud secret key as a secret within the 'grbcloud' scope. You will be prompted to enter the secret key.

```bash
databricks secrets put-secret grbcloud cloudsecretkey
```

--------------------------------

### Add Constraints with String Syntax

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/usage.md

Adds constraints concisely using a string syntax similar to pandas' eval method. Ensures all data is correctly aligned and filled.

```python
import gurobipy_pandas as gppd
import pandas as pd

data = pd.DataFrame({
    'x': [1, 2, 3],
    'limit': [10, 20, 30]
}, index=['a', 'b', 'c'])

model_df = data.gppd.add_constraints('x <= limit')

```

--------------------------------

### Project Difficulty Distribution

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Displays the count of projects for each difficulty level.

```python
projects.difficulty.value_counts()
```

--------------------------------

### Extract and Save Staffing Requirements

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/1b-staff-required.ipynb

Queries the database for shift staffing counts within a specified date range, loads the results into a Pandas DataFrame, and saves it to a feather file.

```python
query = """
SELECT id as shift_id, staff_count
FROM shifts
WHERE start_time BETWEEN '2023-04-01T00:00:00Z' and '2023-05-30T00:00:00Z'
"""

with engine.connect() as connection:
    staff_required = pd.read_sql(text(query), connection, index_col="shift_id")

staff_required.to_feather(model_data_path / "staff_required.feather")
```

--------------------------------

### Build Summation Expressions with groupby and agg

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/usage.md

Builds summation expressions across different index levels using pandas groupby and aggregate. Use .agg(gp.quicksum) for performance with many modeling objects.

```python
import gurobipy_pandas as gppd
import pandas as pd
import gurobipy as gp

variables = pd.Series(name='x', index=[('a', 1), ('a', 2), ('b', 1)])

# Example: Sum of variables grouped by the first level of the index
expression = variables.gppd.groupby(level=0).agg(gp.quicksum)
```

--------------------------------

### Set Gurobi Instant Cloud Application Name

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Store your Gurobi Instant Cloud application name as a secret within the 'grbcloud' scope. You will be prompted to enter the application name.

```bash
databricks secrets put-secret grbcloud csappname
```

--------------------------------

### Formulate and Solve Optimization Model

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/2-optimize.ipynb

Defines and solves an optimization model using GurobiPy-Pandas. It sets the model sense to maximize, adds binary assignment variables, staffing constraints, and conflict constraints, then optimizes the model.

```python
with gp.Env(params=gurobi_params) as env, gp.Model(env=env) as model:

    model.ModelSense = GRB.MAXIMIZE
    assign = gppd.add_vars(
        model,
        feasible_assignments.set_index(["staff_id", "shift_id"]),
        obj=1.0,
        vtype=GRB.BINARY,
        name="assign",
    )
    
    constr_requires = gppd.add_constrs(
        model,
        assign.groupby("shift_id").sum(),
        GRB.LESS_EQUAL,
        staff_required["staff_count"],
        name="staffing",
    )

    df_conflict_vars = (
        shift_conflicts
        .join(assign.rename("assign1"), on=["staff_id", "shift1_id"])
        .join(assign.rename("assign2"), on=["staff_id", "shift2_id"])
        .dropna()
    )

    constr_conflicts = df_conflict_vars.gppd.add_constrs(
        model,
        "assign1 + assign2 <= 1",
        name="conflict",
    )

    model.optimize()

    assignments = assign.gppd.X
    assignments = assignments[assignments == 1.0].reset_index().drop(columns=["assign"])

assignments.to_feather(model_data_path / "assignments.feather")
```

--------------------------------

### Create Pandas DataFrame

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Creates a pandas DataFrame with a RangeIndex and random data, demonstrating a common data structure used with gurobipy-pandas.

```python
import pandas as pd
import numpy as np
np.random.seed(0)

df = pd.DataFrame(
    index=pd.RangeIndex(4, name="i"),
    columns=["a", "b"],
    data=np.random.random((4, 2)).round(2)
)
df
```

--------------------------------

### Enable Interactive Mode

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/usage.md

Enables interactive mode for easier model inspection during development. Remove for production applications where performance is critical.

```python
import gurobipy_pandas as gppd
gppd.interactive()
```

--------------------------------

### Import Libraries

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2407-data-first/netflow.ipynb

Imports necessary libraries for data manipulation, Gurobi optimization, and enhanced display of pandas Series.

```python
from IPython.display import display
import pandas as pd
import gurobipy as grb
import gurobipy_pandas as gppd
```

--------------------------------

### Create a Pandas Series for Coefficients

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Creates a Pandas Series to represent coefficients for an optimization model. The Series is indexed by 'j' and contains corresponding data values.

```python
c = pd.Series(index=pd.RangeIndex(3, name='j'),
              data=[1.0, 2.0, 3.0], name="c")
c
```

--------------------------------

### Load Project Values Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Loads project-team profit data from a CSV string into a Pandas DataFrame, using both 'project' and 'team' as a multi-index. This DataFrame represents the profit p_ij for team j completing project i.

```python
project_values_csv = io.StringIO("project,team,profit\n0,4,0.4\n1,4,1.3\n2,0,1.7\n2,1,1.7\n2,2,1.7\n2,3,1.7\n2,4,1.7\n3,4,1.3\n4,0,1.3\n4,1,1.3\n4,2,1.3\n4,3,1.3\n4,4,1.3\n5,0,1.8\n5,1,1.8\n5,2,1.8\n5,3,1.8\n5,4,1.8\n6,0,1.2\n6,1,1.2\n6,2,1.2\n6,3,1.2\n6,4,1.2\n7,3,0.9\n7,4,0.9\n8,3,1.0\n8,4,1.0\n9,4,1.2\n10,0,0.8\n10,1,0.8\n10,2,0.8\n10,3,0.8\n10,4,0.8\n11,0,1.3\n11,1,1.3\n11,2,1.3\n11,3,1.3\n11,4,1.3\n12,3,0.8\n12,4,0.8\n13,0,1.5\n13,1,1.5\n13,2,1.5\n13,3,1.5\n13,4,1.5\n14,3,1.7\n14,4,1.7\n15,4,1.3\n16,4,0.3\n17,0,1.2\n17,1,1.2\n17,2,1.2\n17,3,1.2\n17,4,1.2\n18,3,1.3\n18,4,1.3\n19,3,1.8\n19,4,1.8\n20,3,1.6\n20,4,1.6\n21,3,1.1\n21,4,1.1\n22,4,0.4\n23,4,1.0\n24,4,0.3\n25,0,1.0\n25,1,1.0\n25,2,1.0\n25,3,1.0\n25,4,1.0\n26,4,1.8\n27,3,0.8\n27,4,0.8\n28,0,1.0\n28,1,1.0\n28,2,1.0\n28,3,1.0\n28,4,1.0\n29,3,1.3\n29,4,1.3\n")
project_values = pd.read_csv(project_values_csv, index_col=["project", "team"])
project_values  # p_ij
```

--------------------------------

### Add Capacity Constraints

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2407-data-first/netflow.ipynb

Adds capacity constraints to the model. It groups flow by 'From' and 'To' nodes, aggregates using quicksum, and compares against 'capacity' data.

```python
capct = pd.concat(
    [flow.groupby(["From", "To"]).agg(grb.quicksum), data["capacity"]], axis=1
).gppd.add_constrs(m, "flow <= capacity", name="cap")
m.update()
capct
```

--------------------------------

### Define SQL Queries and Load Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/3-postprocess.ipynb

Defines SQL queries for assignments and uncovered shifts, then loads and processes data using pandas and SQLAlchemy. Existing tables are replaced if they exist.

```python
query_assignments = """
SELECT
  name, start_time, end_time
FROM assignments
INNER JOIN staff on staff.id == assignments.staff_id
INNER JOIN shifts on shifts.id == assignments.shift_id
ORDER BY name, start_time
"""

query_uncovered = """
SELECT
  id, start_time, end_time, IfNull(assigned, 0) as assigned, staff_count as required, qualification_id
FROM shifts
LEFT JOIN (
    SELECT
    shift_id, count(staff_id) as assigned
    FROM assignments
    GROUP BY shift_id
) on shifts.id == shift_id
WHERE start_time BETWEEN '2023-04-01T00:00:00Z' and '2023-05-30T00:00:00Z'
"""

with engine.connect() as connection:
    assignments = pd.read_feather(model_data_path / "assignments.feather")
    assignments.to_sql("assignments", connection, if_exists="replace", index_label="id")
    assignments = pd.read_sql(query_assignments, connection, parse_dates=["start_time", "end_time"])
    uncovered = pd.read_sql(query_uncovered, connection, parse_dates=["start_time", "end_time"])
    uncovered = uncovered.query("assigned < required")
```

--------------------------------

### Add project allocation constraints

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/gppd-in-90-seconds.ipynb

Adds constraints to ensure each project is allocated to at most one team. Uses gurobipy-pandas for grouped constraint addition.

```python
allocate_once = gppd.add_constrs(
    model, assignments['x'].groupby('project').sum(),
    GRB.LESS_EQUAL, 1.0, name="allocate_once",
)
```

--------------------------------

### Group and Sum Product by Index

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Groups the result of a product operation (coefficients times variables) by index 'i' and sums them. This is a common step in constructing optimization constraints.

```python
(c * y).groupby("i").sum()
```

--------------------------------

### Add Node Balance Constraints

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2407-data-first/netflow.ipynb

Adds node balance constraints for flow. It calculates outgoing and incoming flow for each product and node, then applies the net flow constraint.

```python
flowct = pd.concat(
    [
        flow.rename_axis(index={"From": "Node"})
        .groupby(["Product", "Node"])
        .agg(grb.quicksum)
        .rename("flowout"),
        flow.rename_axis(index={"To": "Node"})
        .groupby(["Product", "Node"])
        .agg(grb.quicksum)
        .rename("flowin"),
        inflow,
    ],
    axis=1,
).fillna(0).gppd.add_constrs(m, "flowout - flowin == net", name="node")
m.update()
flowct
```

--------------------------------

### Clone Repository into Databricks Workspace

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Use this command to clone the Gurobi gurobipy-pandas repository into your Databricks workspace. Ensure Databricks CLI is configured.

```bash
databricks repos create \
    https://github.com/Gurobi/gurobipy-pandas.git \
    --path /Shared/gurobipy-pandas
```

--------------------------------

### Instantiate DataFrame from Pre-existing GurobiPy Variables

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Convert a GurobiPy tupledict of variables into a pandas Series. This is useful for existing models where you want to use GurobiPy-Pandas accessors to extract solutions.

```python
x = model.addVars(...)   # x is a tupledict
xs = pd.Series(x)        # done!
xs.gppd.X                # use pandas accessors to retrieve solutions
```

--------------------------------

### Sample Feasible Assignments

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/1a-feasible-assignments.ipynb

Displays a random sample of 4 feasible assignments from the generated DataFrame. Useful for quick inspection of the extracted data.

```python
feasible_assignments.sample(4)
```

--------------------------------

### Optimize Workforce Scheduling Model

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/workforce.md

Solves the formulated Gurobi model to find the optimal worker assignments that maximize total preference scores while meeting shift requirements.

```python
m.optimize()
```

--------------------------------

### Generate Allowed Project-Team Pairs

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Creates a DataFrame of allowed project-team assignments by performing a cross join and filtering based on team skill and project difficulty. This exploits model sparsity by only considering valid assignments.

```python
# Pandas does not have a conditional join, but we can use a
# cross join + query to create the list of allowed pairs.
# For larger datasets, this data filtering might be better
# done before loading into python/pandas (e.g. in SQL, which
# will handle this more efficiently).

allowed_pairs = (
    pd.merge(
        projects.reset_index(),
        teams.reset_index(),
        how='cross',
    )
    .query("difficulty <= skill")
    .set_index(["project", "team"])
)

print(
    f"Model will have {len(allowed_pairs)} variables"
    f" (out of a possible {len(projects) * len(teams)})"
)

allowed_pairs
```

--------------------------------

### Extract Solution Values using Series Accessor

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Optimizes the model and then extracts the solution values for variable 'y' using the `.gppd.X` Series accessor. This efficiently retrieves variable attributes from the solved model.

```python
model.optimize()
y.gppd.X  # Series accessor
```

--------------------------------

### Set Gurobi Instant Cloud Pool Name

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/readme.md

Store your Gurobi Instant Cloud pool name as a secret within the 'grbcloud' scope. You will be prompted to enter the pool name.

```bash
databricks secrets put-secret grbcloud cloudpool
```

--------------------------------

### Display Team Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/gppd-in-90-seconds.slides.html

Displays the first few rows of team data, showing the capacity of each team.

```python
teams.head(3)  # c_j
```

--------------------------------

### Display Unstacked Preferences

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/workforce.md

Unstacks the preference data to visualize its sparse nature, showing that not all worker-shift combinations are possible. This is useful for understanding data structure before model construction.

```python
preferences.unstack(0).head()
```

--------------------------------

### Team Skill Distribution

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Displays the count of teams for each skill level.

```python
teams.skill.value_counts()
```

--------------------------------

### Compute Net Flow (Inflow)

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2407-data-first/netflow.ipynb

Calculates the net flow for each product at each node by summing supply and subtracting demand. Transshipment nodes have a net flow of 0.

```python
inflow = pd.concat(
    [
        data["supply"].rename("net"),
        data["demand"].rename("net") * -1,
        pd.Series(
            0,
            index=pd.MultiIndex.from_product(
                [commodities, nodes], names=["Product", "Node"]
            ),
            name="net",
        ),
    ]
).groupby(["Product", "Node"]).sum()
inflow
```

--------------------------------

### Load Model Input DataFrames

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/2-optimize.ipynb

Reads input dataframes from feather files. These dataframes contain feasible assignments, staff requirements, and shift conflicts.

```python
feasible_assignments = pd.read_feather(model_data_path / "feasible_assignments.feather")
staff_required = pd.read_feather(model_data_path / "staff_required.feather")
shift_conflicts = pd.read_feather(model_data_path / "shift_conflicts.feather")
```

--------------------------------

### Extract Variable Values from Solution

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/usage.md

Extracts the values of variables from the optimal solution using the Series accessor.

```python
import gurobipy_pandas as gppd
import pandas as pd

# Assuming 'model' is a solved gurobipy.Model object
# and 'variables' is a pandas Series of gurobipy variables

solution_values = variables.gppd.get_values(model)

```

--------------------------------

### Calculate Total Resource Requirements per Team

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Groups binary assignment variables by 'team' and sums their resource requirements, using pandas groupby and aggregate operations. This creates expressions for the total resource allocated to each team.

```python
total_resource = (
    (projects["resource"] * x)
    .groupby("team").sum()
)
total_resource
```

--------------------------------

### Create Variables with DataFrame Accessor

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/usage.md

Uses the DataFrame accessor to create variables and append them as new columns, facilitating method chaining.

```python
import gurobipy_pandas as gppd
import pandas as pd

data = pd.DataFrame({
    'upper_bound': [10, 20, 30]
}, index=['a', 'b', 'c'])

model_df = data.gppd.add_vars(name='x', lb=0, ub=data['upper_bound'])
```

--------------------------------

### Extract Unit Commitment Solution Variables

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/unit-commitment.md

Extracts the optimized values for 'output', 'num_active', and 'num_startup' for each generator into a pandas DataFrame. This is typically done after the model has been solved and before closing the model.

```python
# Extract all variable values
solution = pd.DataFrame(
    dict(
        output=generators["output"].gppd.X,
        num_active=generators["num_active"].gppd.X,
        num_startup=generators["num_startup"].gppd.X,
    )
)
```

--------------------------------

### Retrieve Dual Values

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Access the dual values for a series of constraints using the .gppd.Pi accessor. This requires the constraints to be created using gppd.add_constrs.

```python
c.gppd.Pi
```

--------------------------------

### Plot Unit Commitment Results

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/unit-commitment.md

Generates a line plot visualizing the extracted results, including demand, minimum active capacity, active capacity, and excess capacity over time. This snippet is used for analyzing the performance and feasibility of the unit commitment solution.

```python
%matplotlib inline
%config InlineBackend.figure_formats = ['svg']

import matplotlib
import matplotlib.pyplot as plt
import seaborn as sns

sns.set_palette(sns.color_palette("deep"))

plt.figure(figsize=(8, 4))
results.plot.line(ax=plt.gca())
plt.xlabel("Time")
plt.ylabel("MWh")
plt.ylim([0, 50000])
plt.legend(loc=2);
```

--------------------------------

### Import Libraries for L1 Regression

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/regression.md

Imports necessary libraries including gurobipy, gurobipy_pandas, pandas, and scikit-learn modules for data handling, model building, and validation.

```python
import gurobipy as gp
from gurobipy import GRB
import gurobipy_pandas as gppd
import pandas as pd

from sklearn import datasets
from sklearn.preprocessing import StandardScaler
from sklearn.model_selection import train_test_split
```

--------------------------------

### Inspect Constraint Rows

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Retrieves and displays the row expressions for the constraints named 'c1' using the `.apply(model.getRow)` method. This is useful for verifying constraint formulations.

```python
vars_and_constrs["c1"].apply(model.getRow)
```

--------------------------------

### Load Preference Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/workforce.md

Reads worker preference data from a CSV file. The data includes shift dates, worker names, and preference values. Only preferenced shifts are considered valid.

```python
preferences = pd.read_csv(
    "data/preferences.csv",
    parse_dates=["Shift"],
    index_col=['Worker', 'Shift']
)
preferences
```

--------------------------------

### Extract Solution Assignments

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/workforce.md

Extracts the optimal assignments from the solved model using the .gppd.X accessor, returning the solution as a pandas Series. This allows for easy filtering and analysis of the assignments.

```python
solution = df['assign'].gppd.X
solution
```

--------------------------------

### Add Capacity Constraints

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Adds capacity constraints to the model by aligning calculated total resource requirements with team capacities. Uses the gppd.add_constrs function for efficient constraint creation.

```python
capacity_constraints = gppd.add_constrs(
    model, total_resource, GRB.LESS_EQUAL, teams["capacity"],
    name='capacity',
)
capacity_constraints.head()
```

--------------------------------

### Query Shift Conflicts and Save to Feather

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2409-gppd-etl/1c-shift-conflicts.ipynb

Executes a SQL query to find overlapping shifts for staff members and saves the results to a feather file. This involves joining the shifts and staff tables and filtering for conflicts.

```python
query = """
SELECT
    s1.id as shift1_id,
    s2.id as shift2_id,
    staff.id as staff_id
FROM shifts s1
INNER JOIN shifts s2
INNER JOIN staff
ON s2.start_time >= s1.start_time
AND s2.start_time <= s1.end_time
AND s1.id < s2.id
"""

with engine.connect() as connection:
    shift_conflicts = pd.read_sql(text(query), connection)

shift_conflicts.to_feather(model_data_path / "shift_conflicts.feather")
```

--------------------------------

### Customizing Index Formatting with a Dictionary

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/naming.md

Apply custom formatting functions to specific named index levels using a dictionary for `index_formatter`. This allows for tailored names while preserving the original index structure.

```python
model.addVars(df.index, name='x', index_formatter={'date': lambda d: d.strftime('%Y%m%d'), 'location': str.upper})
model.addConstrs(df.index, name='c', index_formatter={'date': lambda d: d.strftime('%Y%m%d'), 'location': str.upper})
```

--------------------------------

### Define Binary Variables with Objective

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Define binary variables for each valid project-team pairing and assign linear objective coefficients upfront using attributes. This is useful for assignment or allocation problems.

```python
model = gp.Model()
model.ModelSense = GRB.MAXIMIZE
x = gppd.add_vars(model, project_values, vtype=GRB.BINARY, obj="profit", name="x")
x.head()
```

--------------------------------

### Add Project Allocation Constraints

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Ensures each project is allocated to at most one team. This is achieved by summing the assignment variables for each project and constraining the sum to be less than or equal to 1.

```python
allocate_once = gppd.add_constrs(
    model, x.groupby('project').sum(),
    GRB.LESS_EQUAL, 1.0, name="allocate_once",
)
allocate_once.head()
```

--------------------------------

### Add Variables and Constraints using DataFrame Accessors

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Adds variables ('y', 'z') and constraints ('c1') to the model using the `.gppd.add_vars` and `.gppd.add_constrs` DataFrame accessors. This method allows for concise, chained operations.

```python
vars_and_constrs = (
    data
    .gppd.add_vars(model, name="y")
    .gppd.add_vars(model, name="z")
    .gppd.add_constrs(model, "y + z <= 1", name="c1")
)
vars_and_constrs
```

--------------------------------

### Optimize the Model

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/regression.md

Solves the formulated L1 regression problem using the Gurobi Optimizer.

```python
model.optimize()
```

--------------------------------

### Add Variables with Free Function

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/webinar.ipynb

Creates a pandas Series of GurobiPy variables using a free function. Each entry in the provided index corresponds to a variable. Variable names are derived from index values, and attributes like type can be specified.

```python
i = pd.RangeIndex(5, name="i")
x = gppd.add_vars(model, i, name="x", vtype="B")
x
```

--------------------------------

### Customizing Index Formatting with a Callable

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/naming.md

Provide a single callable function to `index_formatter` to apply the same formatting logic to all index levels. This is useful for consistent naming across a multi-index.

```python
model.addVars(df.index, name='x', index_formatter=lambda i: str(i).replace(' ', '_'))
model.addConstrs(df.index, name='c', index_formatter=lambda i: str(i).replace(' ', '_'))
```

--------------------------------

### Extract and Filter Solution

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/webinar/2407-data-first/netflow.ipynb

Retrieves the solution values for the flow variables and filters for arcs with positive flow, then sorts the results.

```python
soln = flow.gppd.X
soln.to_frame().query("flow > 0").sort_index()
```

--------------------------------

### Build Linear Expressions with Arithmetic Operations

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/usage.md

Builds linear expressions using standard pandas arithmetic operations across rows.

```python
import gurobipy_pandas as gppd
import pandas as pd

variables = pd.Series(name='x', index=['a', 'b', 'c'])

# Example: x_a + x_b
expression = variables.loc[['a', 'b']] + variables.loc[['a', 'b']]
```

--------------------------------

### Load Team Data

Source: https://github.com/gurobi/gurobipy-pandas/blob/main/docs/source/examples/projects.md

Reads team data from a CSV file into a pandas DataFrame, setting the 'team' column as the index.

```python
teams = pd.read_csv("data/teams.csv", index_col="team")
teams.head()
```