<jupyter_start><jupyter_text>This notebook implements the solution to the **Project Portfolio Management** mini-case. It assumes you are familiar with the case and the model. ____
 Basic Setup

Import useful modules, read the data and store it in data frames, and set up some useful Python lists. You may want to expand this section and make sure you understand how the data is organized, and also read the last part where the Python lists are created, as these may be very useful when you build your model.<jupyter_code>#@markdown We first import some useful modules. 

# Python ≥3.5 is required
import sys
assert sys.version_info >= (3, 5)

# import numpy
import numpy as np
import urllib.request  # for file downloading

# Import pandas for data-frames
import pandas as pd
pd.options.display.max_rows = 15
pd.options.display.float_format = "{:,.2f}".format

from IPython.display import display

# Make sure Matplotlib runs inline, for nice figures
%matplotlib inline
import matplotlib as mpl
import matplotlib.pyplot as plt
mpl.rc('axes', labelsize=14)
mpl.rc('xtick', labelsize=12)
mpl.rc('ytick', labelsize=12)
import matplotlib.ticker as ticker 

# install Gurobi (our linear optimization solver)
!pip install -i https://pypi.gurobi.com gurobipy
from gurobipy import *

# some modules to create local directories for CBC (to avoid issues with solving multiple models)
import os
def new_local_directory(name):
    full_path = os.path.join(".", name)
    os.makedirs(full_path, exist_ok=True)
    return full_path

# install the latest version of seaborn for nicer graphics
#!pip install --prefix {sys.prefix} seaborn==0.11.0  &> /dev/null
import seaborn as sns

# Ignore useless some warnings
import warnings
warnings.simplefilter(action="ignore")

print("Completed successfully!")<jupyter_output>Looking in indexes: https://pypi.gurobi.com
Requirement already satisfied: gurobipy in /usr/local/lib/python3.7/dist-packages (9.1.1)
Completed successfully!<jupyter_text>Load the case data into Pandas data frames We first download an Excel file with all the data from Github.<jupyter_code>#@markdown Download the entire data as an Excel file from Github

url_Excel = 'https://github.com/dan-a-iancu/airm/blob/master/Project_Portfolio_Management/Project_Data.xlsx?raw=true'
local_file = "Portfolio_Project_Data.xlsx"   # name of local file where you want to store the downloaded file
urllib.request.urlretrieve(url_Excel, local_file)    # download from website and save it locally<jupyter_output><empty_output><jupyter_text>Read in and store the data in suitable dataframes.<jupyter_code>#@markdown Create a dataframe with the information on projects

# Read in the information about the available projects
projectData = pd.read_excel("Portfolio_Project_Data.xlsx", sheet_name = "Projects", index_col = 0)

# Have a look:
display(projectData)

#@markdown Create a dataframe with the information on available resources (this is useful in **Q5**)

# Read in the information about the available projects
resourceData = pd.read_excel("Portfolio_Project_Data.xlsx", sheet_name = "Resources", index_col = 0)

# Have a look:
display(resourceData)<jupyter_output><empty_output><jupyter_text>Also set up any other problem data/parameters, such as the initial capital available.<jupyter_code>initialCapital = 25000<jupyter_output><empty_output><jupyter_text>Create Python lists based on the data-frames

__NOTE__: Make sure you understand what the __lists__ created here are! These will be very helpful when creating the model.<jupyter_code>#@markdown Some useful lists for building all the models

# the list with project names (A,B, ...)
allProjects = list(projectData.index)
print("This is the list of all the project names:")
print(allProjects)

# the unique locations / continents
allLocations = list(projectData["Location"].unique())
print("\nThis is the list of unique locations:")
print(allLocations)
#@markdown The following lists will be useful in **Q5**

# the list with periods when the projects could be scheduled
allPeriods = list(resourceData.columns)
print("These are periods when the projects could be scheduled:")
print(allPeriods)

# the types of resources needed
allResources = list(resourceData.index)
print("\nThese are the unique resources needed to execute the projects:")
print(allResources)<jupyter_output>These are periods when the projects could be scheduled:
['January-March', 'April-June', 'July-September', 'October-December']

These are the unique resources needed to execute the projects:
['Engineers', 'Field_Workers', 'Support']<jupyter_text>_____ **Q1** **Create an empty model**<jupyter_code>#@title Create the model
ProjectSelectionModel = Model("Funding Projects")<jupyter_output>Restricted license - for non-production use only - expires 2022-01-13<jupyter_text>**Define the Decision Variables**We have a **binary** decision for whether to select each project or not. We can add one such variable for each project using Gurobi's ``addVars`` method. Note that  specify the **type** of the decision using `vtype`.<jupyter_code>#@title Add the decision variables
fund_project = ProjectSelectionModel.addVars(allProjects, vtype = GRB.BINARY, name="Fund")<jupyter_output><empty_output><jupyter_text>**Calculate and add the objective function**
The objective corresponds to the total impact achieved on all the continents.<jupyter_code>#@title Add the objective
ProjectSelectionModel.setObjective( 
    quicksum(fund_project[p]*projectData["Impact"][p] for p in allProjects), GRB.MAXIMIZE )<jupyter_output><empty_output><jupyter_text>**Add the Constraints**

The only constraint here is that the capital used should not exceed the initial capital available.<jupyter_code>#@title Add the constraints
# we only have one constraint, that "capital used <= capital available"
ProjectSelectionModel.addConstr( 
    quicksum(fund_project[p]*projectData["Capital_Required"][p] for p in allProjects) 
    <= initialCapital, name = "Initial_capital_avail" )<jupyter_output><empty_output><jupyter_text>**Solve the model**<jupyter_code>#@markdown Select whether to run the [Gurobi](https://www.gurobi.com/) optimization algorithms silently (no output details)
run_silently = False #@param {type:"boolean"}

if run_silently:
    ProjectSelectionModel.setParam('OutputFlag',0)
else:
    ProjectSelectionModel.setParam('OutputFlag',1)

ProjectSelectionModel.optimize()
print('\nSolved the optimization problem...')<jupyter_output>Parameter OutputFlag unchanged
   Value: 1  Min: 0  Max: 1  Default: 1
Gurobi Optimizer version 9.1.1 build v9.1.1rc0 (linux64)
Thread count: 1 physical cores, 2 logical processors, using up to 2 threads
Optimize a model with 1 rows, 10 columns and 10 nonzeros
Model fingerprint: 0xfa4d6032
Variable types: 0 continuous, 10 integer (10 binary)
Coefficient statistics:
  Matrix range     [2e+03, 2e+04]
  Objective range  [1e+00, 1e+01]
  Bounds range     [1e+00, 1e+00]
  RHS range        [2e+04, 2e+04]
Found heuristic solution: objective 30.0000000
Presolve removed 1 rows and 10 columns
Presolve time: 0.00s
Presolve: All rows and columns removed

Explored 0 nodes (0 simplex iterations) in 0.01 seconds
Thread count was 1 (of 2 available processors)

Solution count 2: 32 

Optimal solution found (tolerance 1.00e-04)
Best objective 3.200000000000e+01, best bound 3.200000000000e+01, gap 0.0000%

Solved the optimization problem...<jupyter_text>**Print the optimal objective and optimal solution**<jupyter_code>#@title Print the solution
print("Projects Funded: ")
for p in allProjects:
    if fund_project[p].X == 1:
        print("Project ",p)

print("Optimal total impact: {:.2f}".format(ProjectSelectionModel.objVal))<jupyter_output>Projects Funded: 
Project  A
Project  B
Project  C
Project  E
Project  I
Optimal total impact: 32.00<jupyter_text>**Calculate and print some additional information about the solution**

We calculate the "bang-for-the-buck" for each project, and display it together with the solution as a dataframe. We also calculate the impact achieved on each continent.<jupyter_code>#@markdown Calculate and print additional information

# change the precision on pandas dataframes
pd.options.display.float_format = "{:,.5f}".format

# create a dataframe with the optimal decisions for each project
df_results = pd.DataFrame({"Fund_Project?" : \
                           [np.int(fund_project[p].X) for p in allProjects], \
                            "Bang-for-buck" : \
                           [ projectData["Impact"][p]/projectData["Capital_Required"][p] for p in allProjects]}, \
                          index = allProjects)
display(df_results)

# impact by continent
print('\n\n{:<50}\nImpact achieved on each continent:'.format("="*80))
for loc in allLocations:
    print("   {:<15} : {:,.2f}".format(loc, sum(projectData["Impact"][p]*fund_project[p].X \
                                                for p in allProjects if projectData["Location"][p]==loc)) )<jupyter_output><empty_output><jupyter_text>Store the optimal impact obtained in **Q1**.<jupyter_code>impact_Q1 = ProjectSelectionModel.objVal  # optimal value Q1<jupyter_output><empty_output><jupyter_text>Create a few useful functions

To help with subsequent parts of the problem, we also add all the steps above inside a **function** that creates and returns a generic model like the one we created in **Q1**, together with all the decision variables and constraints.<jupyter_code>#@title A function that generates a model like the one in **Q1**
def create_model_like_in_Q1():
    # create the model
    ProjectSelectionModel = Model("Funding Projects")

    #@markdown Decision variables
    # one binary decision for each project
    fund_project = ProjectSelectionModel.addVars(allProjects, vtype = GRB.BINARY, name="Fund")

    #@markdown Objective
    # calculate the net impact objective and add it to the model
    ProjectSelectionModel.setObjective( 
      quicksum(fund_project[p]*projectData["Impact"][p] for p in allProjects), GRB.MAXIMIZE )

    #@markdown Constraints
    # add constraint that "capital used <= capital available"
    constraints = ProjectSelectionModel.addConstr( 
        quicksum(fund_project[p]*projectData["Capital_Required"][p] for p in allProjects) 
        <= initialCapital, name = "Initial_capital_avail" )

    # return the model, the decision variables and the constraints
    return ProjectSelectionModel,  fund_project, constraints
#@title A function that prints a solution and some useful information
def print_solution(fund_project):
      # change the precision on pandas dataframes
      pd.options.display.float_format = "{:,.5f}".format

      # create a dataframe with the optimal decisions for each project
      df_results = pd.DataFrame({"Fund_Project?" : \
                                [np.int(fund_project[p].X) for p in allProjects]}, \
                                index = allProjects)
      display(df_results)

      # impact by continent
      print('\n\n{:<50}\nImpact achieved on each continent:'.format("="*80))
      for loc in allLocations:
          print("   {:<15} : {:,.2f}".format(loc, sum(projectData["Impact"][p]*fund_project[p].X \
                                                      for p in allProjects if projectData["Location"][p]==loc)) )<jupyter_output><empty_output><jupyter_text>______
 **Q2**
Before running this section, make sure you have run all the previous sections of the Colab file. Re-recreate an identical model to the one from **Q1** and store the model, the decision variables and the constraints.<jupyter_code>#@title Create a model like the one in Q1
ProjectSelectionModel, fund_project, budget_constraint = \
       create_model_like_in_Q1()<jupyter_output><empty_output><jupyter_text>Add a new decision variable **Z** to the model, meant to capture the minimum impact on either continent.<jupyter_code>#@title Add a new decision variable **Z**
Z = ProjectSelectionModel.addVar(name="min_impact")<jupyter_output><empty_output><jupyter_text>Set the objective to maximize the new variable **Z**.<jupyter_code>#@title Set the objective to maximize **Z**
ProjectSelectionModel.setObjective(Z, GRB.MAXIMIZE)<jupyter_output><empty_output><jupyter_text>Add constraints that **Z** should be less than or equal to the impact on each continent.<jupyter_code>#@title Add constraints that **Z** cannot exceed the impact on each continent
for loc in allLocations:
    ProjectSelectionModel.addConstr( Z <= quicksum(projectData["Impact"][p]*fund_project[p] \
                                                for p in allProjects if projectData["Location"][p]==loc))<jupyter_output><empty_output><jupyter_text>Solve the new model.<jupyter_code>#@title Solve the model 
#@markdown Select whether to run the [Gurobi](https://www.gurobi.com/) optimization algorithms silently (no output details)
run_silently = True #@param {type:"boolean"}

if run_silently:
    ProjectSelectionModel.setParam('OutputFlag',0)
else:
    ProjectSelectionModel.setParam('OutputFlag',1)

ProjectSelectionModel.optimize()
print('\nSolved the optimization problem...')<jupyter_output>Solved the optimization problem...<jupyter_text>Print information about the solution.<jupyter_code>#@markdown Print the solution
# the objective in this problem
print("The largest impact that can be simultaneously achieved on each continent: {}".\
      format(ProjectSelectionModel.objVal))

# other useful information about the solution
print_solution(fund_project)<jupyter_output>The largest impact that can be simultaneously achieved on each continent: 5.0<jupyter_text>Store the optimal value of the problem --- this is useful subsequently, e.g., in **Q3**.<jupyter_code>#@markdown Store the optimal value for later use (in **Q3**)
# store the optimal value
opt_value_Q2 = ProjectSelectionModel.objVal<jupyter_output><empty_output><jupyter_text>______
 **Q3**
Before running this section, make sure you have run all the previous sections of the Colab file. Re-recreate an identical model to the one from **Q1** and store the model, the decision variables and the constraints.<jupyter_code>#@title Create a model like the one in Q1
ProjectSelectionModel, fund_project, budget_constraint = \
       create_model_like_in_Q1()<jupyter_output><empty_output><jupyter_text>Add constraints that the impact in each location should be greater than or equal to the (optimal) minimum impact calculated in **Q2**.<jupyter_code>#@title Add constraints that the impact on each continent is at least the minimum calculated in **Q2**
for loc in allLocations:
    ProjectSelectionModel.addConstr( quicksum(projectData["Impact"][p]*fund_project[p] \
                                              for p in allProjects if projectData["Location"][p]==loc) \
                                    >= opt_value_Q2)<jupyter_output><empty_output><jupyter_text>Solve the new model.<jupyter_code>#@markdown Select whether to run the [Gurobi](https://www.gurobi.com/) optimization algorithms silently (no output details)
run_silently = True #@param {type:"boolean"}

if run_silently:
    ProjectSelectionModel.setParam('OutputFlag',0)
else:
    ProjectSelectionModel.setParam('OutputFlag',1)

ProjectSelectionModel.optimize()
print('\nSolved the optimization problem...')<jupyter_output>Solved the optimization problem...<jupyter_text>Print information about the solution.<jupyter_code>#@markdown Print the solution
# the objective in this problem
print("The largest total cumulative impact that can be achieved while ensuring that each continent has impact of at least {} is : {}".\
      format(opt_value_Q2, ProjectSelectionModel.objVal))

# other useful information about the solution
print_solution(fund_project)<jupyter_output>The largest total cumulative impact that can be achieved while ensuring that each continent has impact of at least 5.0 is : 23.0<jupyter_text>______
 **Q4**
Before running this section, make sure you have run all the previous sections of the Colab file.<jupyter_code>#@markdown Store the max risk score in a parameter
max_risk = 5.5<jupyter_output><empty_output><jupyter_text>Re-recreate an identical model to the one from **Q1** and store the model, the decision variables and the constraints.<jupyter_code>#@title Create a model like the one in Q1
ProjectSelectionModel, fund_project, budget_constraint = \
       create_model_like_in_Q1()<jupyter_output><empty_output><jupyter_text>Add a constraint that the average risk score should not exceed the maximum allowed risk. Note that this constraint is of the form
>  $\frac{\sum_{p} R_p \cdot X_p}{\sum_p X_p} \leq M$

where $R_p$ is the risk score for project $p$, and $X_p \in \{0,1\}$ is the binary variable indicating whether project $p$ is selected. This is a nonlinear constraint, but it can be re-formulated in a linear way as:
>  $\sum_{p} R_p \cdot X_p \leq M \cdot \sum_p X_p$

We formulate it in this linear way below. (Formulating the first non-linear constraint above would result in an error from Gurobi.)<jupyter_code>#@title Add a constraint that the average risk does not exceed max allowed
ProjectSelectionModel.addConstr( quicksum(projectData["Risks"][p]*fund_project[p] \
                                          for p in allProjects) <= \
                                max_risk*quicksum(fund_project[p] for p in allProjects))<jupyter_output><empty_output><jupyter_text>Solve the new model.<jupyter_code>#@markdown Select whether to run the [Gurobi](https://www.gurobi.com/) optimization algorithms silently (no output details)
run_silently = True #@param {type:"boolean"}

if run_silently:
    ProjectSelectionModel.setParam('OutputFlag',0)
else:
    ProjectSelectionModel.setParam('OutputFlag',1)

ProjectSelectionModel.optimize()
print('\nSolved the optimization problem...')<jupyter_output>Solved the optimization problem...<jupyter_text>Print information about the solution.<jupyter_code>#@markdown Print the solution
# the objective in this problem
print("The largest total cumulative impact achieved without exceeding an average risk of {} is : {}".\
      format(max_risk, ProjectSelectionModel.objVal))

# other useful information about the solution
print_solution(fund_project)<jupyter_output>The largest total cumulative impact achieved without exceeding an average risk of 5.5 is : 27.0<jupyter_text>______
 **Q5**
Before running this section, make sure you have run all the previous sections of the Colab file. Re-recreate an identical model to the one from **Q1** and store the model, the decision variables and the constraints.<jupyter_code>#@title Create a model like the one in Q1
ProjectSelectionModel, fund_project, budget_constraint = \
       create_model_like_in_Q1()<jupyter_output><empty_output><jupyter_text>Add a new set of decision variables for whether to schedule each project in a particular period. Here, we want to have one binary decision for every project and for every potential period, so we use `addVars` to define these.<jupyter_code>#@title Add binary decision variables for whether to schedule the projects in a given period
schedule_project = ProjectSelectionModel.addVars(allProjects, allPeriods, name="schedule")<jupyter_output><empty_output><jupyter_text>Add constraints to connect the decisions on whether to fund projects with the decisions on whether to schedule projects. If we let $X_p$ denote the binary decision of whether to fund project $p$, and $Y_{p,t}$ the binary variable for whether project $p$ should be scheduled in period $t$, then the constraints we need to add here are:
> $\sum_{t=1}^T Y_{p,t} = X_p$,  for every project $p$.

In other words, if $X_p=1$ (so project $p$ is funded), it must be scheduled in one of the periods, so the sum on the left must be equal to 1. If $X_p=0$, project $p$ is not funded and it should also not be scheduled.<jupyter_code>#@title Constraints that a project must be scheduled if and only if it is funded.
for p in allProjects:
    ProjectSelectionModel.addConstr( fund_project[p] == \
                                    quicksum(schedule_project[p,t] for t in allPeriods))<jupyter_output><empty_output><jupyter_text>Add constraints on the available resources. For each resource, the usage should not exceed what is available.<jupyter_code>#@title Constraints on available resources
for t in allPeriods:
    for r in allResources:
        ProjectSelectionModel.addConstr( quicksum(schedule_project[p,t]*projectData[r][p] \
                                                  for p in allProjects) <= \
                                        resourceData[t][r] )<jupyter_output><empty_output><jupyter_text>Solve the new model.<jupyter_code>#@markdown Select whether to run the [Gurobi](https://www.gurobi.com/) optimization algorithms silently (no output details)
run_silently = True #@param {type:"boolean"}

if run_silently:
    ProjectSelectionModel.setParam('OutputFlag',0)
else:
    ProjectSelectionModel.setParam('OutputFlag',1)

ProjectSelectionModel.optimize()
print('\nSolved the optimization problem...')<jupyter_output>Solved the optimization problem...<jupyter_text>Print information about the solution.<jupyter_code>#@markdown Print the solution
# the objective in this problem
print("The largest impact that can be simultaneously achieved on each continent: {}".\
      format(ProjectSelectionModel.objVal))

# create a small dataframe to store the decisions to fund as well as the decisions to schedule
dict = {"Fund_Project" : [np.int(fund_project[p].X) for p in allProjects]}
for t in allPeriods:
   dict["Schedule "+t] = [np.int(schedule_project[p,t].X) for p in allProjects]

all_decisions_Q5 = pd.DataFrame(dict, index=allProjects)

# other useful information about the solution
display(all_decisions_Q5)<jupyter_output>The largest impact that can be simultaneously achieved on each continent: 32.0
{'fraction_non_alphanumeric': 0.0889455937300647, 'fraction_numerical': 0.011254898386949786, 'mean_word_length': 4.624551512045105, 'pattern_counts': {'":': 0, '<': 143, '<?xml version=': 0, '>': 138, 'https://': 8, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 20, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '36', 'dir': 'jupyter-structured-clean-dedup', 'id': '154667', 'license': 'MIT', 'n_tokens_mistral': 6537, 'n_tokens_neox': 6239, 'n_words': 2210, 'path': 'Project_Portfolio_Management/Project_Portfolio_Management.ipynb', 'repo_name': 'dan-a-iancu/airm', 'size': '21946'}