LAB3_Nurse_Scheduling_Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# The Nurse Assignment Problem\n",
    "\n",
    "This tutorial includes everything you need to set up IBM Decision Optimization CPLEX Modeling for Python (DOcplex), build a Mathematical Programming model, and get its solution by solving the model on the cloud with IBM ILOG CPLEX Optimizer.\n",
    "\n",
    "When you finish this tutorial, you'll have a foundational knowledge of _Prescriptive Analytics_.\n",
    "\n",
    ">This notebook is part of [Prescriptive Analytics for Python](https://rawgit.com/IBMDecisionOptimization/docplex-doc/master/docs/index.html).\n",
    "\n",
    ">It requires a valid subscription to **Decision Optimization on Cloud** or a **local installation of CPLEX Optimizers**. \n",
    "Discover us [here](https://developer.ibm.com/docloud).\n",
    "\n",
    "\n",
    "Table of contents:\n",
    "\n",
    "-  [Describe the business problem](#Describe-the-business-problem)\n",
    "*  [How decision optimization (prescriptive analytics) can help](#How--decision-optimization-can-help)\n",
    "*  [Use decision optimization](#Use-decision-optimization)\n",
    "    *  [Step 1: Download the library](#Step-1:-Download-the-library)\n",
    "    *  [Step 2: Set up the engines](#Step-2:-Set-up-the-prescriptive-engine)\n",
    "    -  [Step 3: Model the data](#Step-3:-Model-the-data)\n",
    "    *  [Step 4: Prepare the data](#Step-4:-Prepare-the-data)\n",
    "    -  [Step 5: Set up the prescriptive model](#Step-5:-Set-up-the-prescriptive-model)\n",
    "        * [Define the decision variables](#Define-the-decision-variables)\n",
    "        * [Express the business constraints](#Express-the-business-constraints)\n",
    "        * [Express the objective](#Express-the-objective)\n",
    "        * [Solve with the Decision Optimization solve service](#Solve-with-the-Decision-Optimization-solve-service)\n",
    "    *  [Step 6: Investigate the solution and run an example analysis](#Step-6:-Investigate-the-solution-and-then-run-an-example-analysis)\n",
    "*  [Summary](#Summary)\n",
    "\n",
    "****"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Describe the business problem\n",
    "\n",
    "This notebook describes how to use CPLEX Modeling for Python together with *pandas* to\n",
    "manage the assignment of nurses to shifts in a hospital.\n",
    "\n",
    "Nurses must be assigned to hospital shifts in accordance with various skill and staffing constraints.\n",
    "\n",
    "The goal of the model is to find an efficient balance between the different objectives:\n",
    "\n",
    "* minimize the overall cost of the plan and\n",
    "* assign shifts as fairly as possible.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## How  decision optimization can help\n",
    "\n",
    "* Prescriptive analytics (decision optimization) technology recommends actions that are based on desired outcomes.  It takes into account specific scenarios, resources, and knowledge of past and current events. With this insight, your organization can make better decisions and have greater control of business outcomes.  \n",
    "\n",
    "* Prescriptive analytics is the next step on the path to insight-based actions. It creates value through synergy with predictive analytics, which analyzes data to predict future outcomes.  \n",
    "\n",
    "* Prescriptive analytics takes that insight to the next level by suggesting the optimal way to handle that future situation. Organizations that can act fast in dynamic conditions and make superior decisions in uncertain environments gain a strong competitive advantage.  \n",
    "<br/>\n",
    "\n",
    "<u>With prescriptive analytics, you can:</u> \n",
    "\n",
    "* Automate the complex decisions and trade-offs to better manage your limited resources.\n",
    "* Take advantage of a future opportunity or mitigate a future risk.\n",
    "* Proactively update recommendations based on changing events.\n",
    "* Meet operational goals, increase customer loyalty, prevent threats and fraud, and optimize business processes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Checking minimum requirements\n",
    "This notebook uses some features of pandas that are available in version 0.17.1 or above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pip\n",
    "REQUIRED_MINIMUM_PANDAS_VERSION = '0.17.1'\n",
    "try:\n",
    "    import pandas as pd\n",
    "    assert pd.__version__ >= REQUIRED_MINIMUM_PANDAS_VERSION\n",
    "except:\n",
    "    raise Exception(\"Version %s or above of Pandas is required to run this notebook\" % REQUIRED_MINIMUM_PANDAS_VERSION)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Use decision optimization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Step 1: Download the library\n",
    "\n",
    "Run the following code to install the Decision Optimization CPLEX Modeling library.  The *DOcplex* library contains the two modeling packages, Mathematical Programming (docplex.mp) and Constraint Programming (docplex.cp)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "try:\n",
    "    import docplex.mp\n",
    "except:\n",
    "    if hasattr(sys, 'real_prefix'):\n",
    "        #we are in a virtual env.\n",
    "        !pip install docplex \n",
    "    else:\n",
    "        !pip install --user docplex      "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Step 2: Set up the prescriptive engine\n",
    "\n",
    "* Subscribe to our private cloud offer or Decision Optimization on Cloud solve service [here](https://developer.ibm.com/docloud) if you do not want to use a local solver.\n",
    "* Get the service URL and your personal API key and enter your credentials here if accurate:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "url = None\n",
    "key = None\n",
    "# Initialize IBM Decision Optimization credentials\n",
    "url =  \"https://api-oaas.docloud.ibmcloud.com/job_manager/rest/v1\"\n",
    "key =  \"bm_api_ext_760d77c9-8f08-4964-803e-c0ddf4c2f757\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Step 3: Model the data\n",
    "\n",
    "The input data consists of several tables:\n",
    "\n",
    "* The Departments table lists all departments in the scope of the assignment.\n",
    "* The Skills table list all skills.\n",
    "* The Shifts table lists all shifts to be staffed. A shift contains a department, a day in the week, plus the start and end times.\n",
    "* The Nurses table lists all nurses, identified by their names.\n",
    "* The NurseSkills table gives the skills of each nurse.\n",
    "* The SkillRequirements table lists the minimum number of persons required for a given department and skill.\n",
    "* The NurseVacations table lists days off for each nurse.\n",
    "* The NurseAssociations table lists pairs of nurses who wish to work together.\n",
    "* The NurseIncompatibilities table lists pairs of nurses who do not want to work together."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Loading data from Excel with pandas\n",
    "\n",
    "We load the data from an Excel file using *pandas*.\n",
    "Each sheet is read into a separate *pandas* DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "CSS = \"\"\"\n",
    "body {\n",
    "    margin: 0;\n",
    "    font-family: Helvetica;\n",
    "}\n",
    "table.dataframe {\n",
    "    border-collapse: collapse;\n",
    "    border: none;\n",
    "}\n",
    "table.dataframe tr {\n",
    "    border: none;\n",
    "}\n",
    "table.dataframe td, table.dataframe th {\n",
    "    margin: 0;\n",
    "    border: 1px solid white;\n",
    "    padding-left: 0.25em;\n",
    "    padding-right: 0.25em;\n",
    "}\n",
    "table.dataframe th:not(:empty) {\n",
    "    background-color: #fec;\n",
    "    text-align: left;\n",
    "    font-weight: normal;\n",
    "}\n",
    "table.dataframe tr:nth-child(2) th:empty {\n",
    "    border-left: none;\n",
    "    border-right: 1px dashed #888;\n",
    "}\n",
    "table.dataframe td {\n",
    "    border: 2px solid #ccf;\n",
    "    background-color: #f4f4ff;\n",
    "}\n",
    "    table.dataframe thead th:first-child {\n",
    "        display: none;\n",
    "    }\n",
    "    table.dataframe tbody th {\n",
    "        display: none;\n",
    "    }\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "from IPython.core.display import HTML\n",
    "HTML('<style>{}</style>'.format(CSS))\n",
    "\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    from StringIO import StringIO\n",
    "except ImportError:\n",
    "    from io import StringIO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    from urllib2 import urlopen\n",
    "except ImportError:\n",
    "    from urllib.request import urlopen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#nurses = 32\n",
      "#shifts = 41\n",
      "#vacations = 59\n"
     ]
    }
   ],
   "source": [
    "# This notebook requires pandas to work\n",
    "import pandas as pd\n",
    "from pandas import DataFrame\n",
    "\n",
    "# Make sure that xlrd package, which is a pandas optional dependency, is installed\n",
    "# This package is required for Excel I/O\n",
    "try:\n",
    "    import xlrd\n",
    "except:\n",
    "    if hasattr(sys, 'real_prefix'):\n",
    "        #we are in a virtual env.\n",
    "        !pip install xlrd \n",
    "    else:\n",
    "        !pip install --user xlrd      \n",
    "\n",
    "# Use pandas to read the file, one tab for each table.\n",
    "data_url = \"https://github.com/IBMDecisionOptimization/docplex-examples/blob/master/examples/mp/jupyter/nurses_data.xls?raw=true\"\n",
    "nurse_xls_file = pd.ExcelFile(urlopen(data_url))\n",
    "\n",
    "df_skills = nurse_xls_file.parse('Skills')\n",
    "df_depts  = nurse_xls_file.parse('Departments')\n",
    "df_shifts = nurse_xls_file.parse('Shifts')\n",
    "# Rename df_shifts index\n",
    "df_shifts.index.name = 'shiftId'\n",
    "\n",
    "# Index is column 0: name\n",
    "df_nurses = nurse_xls_file.parse('Nurses', header=0, index_col=0)\n",
    "df_nurse_skilles = nurse_xls_file.parse('NurseSkills')\n",
    "df_vacations = nurse_xls_file.parse('NurseVacations')\n",
    "df_associations = nurse_xls_file.parse('NurseAssociations')\n",
    "df_incompatibilities = nurse_xls_file.parse('NurseIncompatibilities')\n",
    "\n",
    "# Display the nurses dataframe\n",
    "print(\"#nurses = {}\".format(len(df_nurses)))\n",
    "print(\"#shifts = {}\".format(len(df_shifts)))\n",
    "print(\"#vacations = {}\".format(len(df_vacations)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "In addition, we introduce some extra global data:\n",
    "\n",
    "* The maximum work time for each nurse.\n",
    "* The maximum and minimum number of shifts worked by a nurse in a week."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# maximum work time (in hours)\n",
    "max_work_time = 40\n",
    "\n",
    "# maximum number of shifts worked in a week.\n",
    "max_nb_shifts = 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Shifts are stored in a separate DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department</th>\n",
       "      <th>day</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>min_req</th>\n",
       "      <th>max_req</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shiftId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Friday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Friday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Geriatrics</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           department        day  start_time  end_time  min_req  max_req\n",
       "shiftId                                                                 \n",
       "0           Emergency     Monday           2         8        3        5\n",
       "1           Emergency     Monday           8        12        4        7\n",
       "2           Emergency     Monday          12        18        2        5\n",
       "3           Emergency     Monday          18         2        3        7\n",
       "4        Consultation     Monday           8        12       10       13\n",
       "5        Consultation     Monday          12        18        8       12\n",
       "6        Cardiac Care     Monday           8        12       10       13\n",
       "7        Cardiac Care     Monday          12        18        8       12\n",
       "8           Emergency    Tuesday           8        12        4        7\n",
       "9           Emergency    Tuesday          12        18        2        5\n",
       "10          Emergency    Tuesday          18         2        3        7\n",
       "11       Consultation    Tuesday           8        12       10       13\n",
       "12       Consultation    Tuesday          12        18        8       12\n",
       "13       Cardiac Care    Tuesday           8        12        4        7\n",
       "14       Cardiac Care    Tuesday          12        18        2        5\n",
       "15       Cardiac Care    Tuesday          18         2        3        7\n",
       "16          Emergency  Wednesday           2         8        3        5\n",
       "17          Emergency  Wednesday           8        12        4        7\n",
       "18          Emergency  Wednesday          12        18        2        5\n",
       "19          Emergency  Wednesday          18         2        3        7\n",
       "20       Consultation  Wednesday           8        12       10       13\n",
       "21       Consultation  Wednesday          12        18        8       12\n",
       "22          Emergency   Thursday           2         8        3        5\n",
       "23          Emergency   Thursday           8        12        4        7\n",
       "24          Emergency   Thursday          12        18        2        5\n",
       "25          Emergency   Thursday          18         2        3        7\n",
       "26       Consultation   Thursday           8        12       10       13\n",
       "27       Consultation   Thursday          12        18        8       12\n",
       "28          Emergency     Friday           2         8        3        5\n",
       "29          Emergency     Friday           8        12        4        7\n",
       "30          Emergency     Friday          12        18        2        5\n",
       "31          Emergency     Friday          18         2        3        7\n",
       "32       Consultation     Friday           8        12       10       13\n",
       "33       Consultation     Friday          12        18        8       12\n",
       "34          Emergency   Saturday           2        12        5        7\n",
       "35          Emergency   Saturday          12        20        7        9\n",
       "36          Emergency   Saturday          20         2       12       12\n",
       "37          Emergency     Sunday           2        12        5        7\n",
       "38          Emergency     Sunday          12        20        7        9\n",
       "39          Emergency     Sunday          20         2        8       12\n",
       "40         Geriatrics     Sunday           8        10        2        5"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_shifts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Step 4: Prepare the data\n",
    "\n",
    "We need to precompute additional data for shifts. \n",
    "For each shift, we need the start time and end time expressed in hours, counting from the beginning of the week: Monday 8am is converted to 8, Tuesday 8am is converted to 24+8 = 32, and so on.\n",
    "\n",
    "#### Sub-step #1\n",
    "We start by adding an extra column `dow` (day of week) which converts the string \"day\" into an integer in 0..6 (Monday is 0, Sunday is 6)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department</th>\n",
       "      <th>day</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>min_req</th>\n",
       "      <th>max_req</th>\n",
       "      <th>dow</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shiftId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Friday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Friday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Geriatrics</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           department        day  start_time  end_time  min_req  max_req  dow\n",
       "shiftId                                                                      \n",
       "0           Emergency     Monday           2         8        3        5    0\n",
       "1           Emergency     Monday           8        12        4        7    0\n",
       "2           Emergency     Monday          12        18        2        5    0\n",
       "3           Emergency     Monday          18         2        3        7    0\n",
       "4        Consultation     Monday           8        12       10       13    0\n",
       "5        Consultation     Monday          12        18        8       12    0\n",
       "6        Cardiac Care     Monday           8        12       10       13    0\n",
       "7        Cardiac Care     Monday          12        18        8       12    0\n",
       "8           Emergency    Tuesday           8        12        4        7    1\n",
       "9           Emergency    Tuesday          12        18        2        5    1\n",
       "10          Emergency    Tuesday          18         2        3        7    1\n",
       "11       Consultation    Tuesday           8        12       10       13    1\n",
       "12       Consultation    Tuesday          12        18        8       12    1\n",
       "13       Cardiac Care    Tuesday           8        12        4        7    1\n",
       "14       Cardiac Care    Tuesday          12        18        2        5    1\n",
       "15       Cardiac Care    Tuesday          18         2        3        7    1\n",
       "16          Emergency  Wednesday           2         8        3        5    2\n",
       "17          Emergency  Wednesday           8        12        4        7    2\n",
       "18          Emergency  Wednesday          12        18        2        5    2\n",
       "19          Emergency  Wednesday          18         2        3        7    2\n",
       "20       Consultation  Wednesday           8        12       10       13    2\n",
       "21       Consultation  Wednesday          12        18        8       12    2\n",
       "22          Emergency   Thursday           2         8        3        5    3\n",
       "23          Emergency   Thursday           8        12        4        7    3\n",
       "24          Emergency   Thursday          12        18        2        5    3\n",
       "25          Emergency   Thursday          18         2        3        7    3\n",
       "26       Consultation   Thursday           8        12       10       13    3\n",
       "27       Consultation   Thursday          12        18        8       12    3\n",
       "28          Emergency     Friday           2         8        3        5    4\n",
       "29          Emergency     Friday           8        12        4        7    4\n",
       "30          Emergency     Friday          12        18        2        5    4\n",
       "31          Emergency     Friday          18         2        3        7    4\n",
       "32       Consultation     Friday           8        12       10       13    4\n",
       "33       Consultation     Friday          12        18        8       12    4\n",
       "34          Emergency   Saturday           2        12        5        7    5\n",
       "35          Emergency   Saturday          12        20        7        9    5\n",
       "36          Emergency   Saturday          20         2       12       12    5\n",
       "37          Emergency     Sunday           2        12        5        7    6\n",
       "38          Emergency     Sunday          12        20        7        9    6\n",
       "39          Emergency     Sunday          20         2        8       12    6\n",
       "40         Geriatrics     Sunday           8        10        2        5    6"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "days = [\"monday\", \"tuesday\", \"wednesday\", \"thursday\", \"friday\", \"saturday\", \"sunday\"]\n",
    "day_of_weeks = dict(zip(days, range(7)))\n",
    "\n",
    "# utility to convert a day string e.g. \"Monday\" to an integer in 0..6\n",
    "def day_to_day_of_week(day):\n",
    "    return day_of_weeks[day.strip().lower()]\n",
    "\n",
    "# for each day name, we normalize it by stripping whitespace and converting it to lowercase\n",
    "# \" Monday\" -> \"monday\"\n",
    "df_shifts[\"dow\"] = df_shifts.day.apply(day_to_day_of_week)\n",
    "df_shifts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Sub-step #2 : Compute the absolute start time of each shift.\n",
    "\n",
    "Computing the start time in the week is easy: just add `24*dow` to column `start_time`. The result is stored in a new column `wstart`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df_shifts[\"wstart\"] = df_shifts.start_time + 24 * df_shifts.dow"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Sub-Step #3 : Compute the absolute end time of each shift.\n",
    "\n",
    "Computing the absolute end time is a little more complicated as certain shifts span across midnight. For example, Shift #3 starts on Monday at 18:00 and ends Tuesday at 2:00 AM. The absolute end time of Shift #3 is 26, not 2.\n",
    "The general rule for computing absolute end time is:\n",
    "\n",
    "`abs_end_time = end_time + 24 * dow + (start_time>= end_time ? 24 : 0)`\n",
    "\n",
    "Again, we use *pandas* to add a new calculated column `wend`. This is done by using the *pandas* `apply` method with an anonymous `lambda` function over rows. The `raw=True` parameter prevents the creation of a *pandas* Series for each row, which improves the performance significantly on large data sets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# an auxiliary function to calculate absolute end time of a shift\n",
    "def calculate_absolute_endtime(start, end, dow):\n",
    "    return 24*dow + end + (24 if start>=end else 0)\n",
    "\n",
    "# store the results in a new column\n",
    "df_shifts[\"wend\"] = df_shifts.apply(lambda row: calculate_absolute_endtime(\n",
    "        row.start_time, row.end_time, row.dow), axis=1, raw=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Sub-step #4 : Compute the duration of each shift.\n",
    "\n",
    "Computing the duration of each shift is now a straightforward difference of columns. The result is stored in column `duration`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df_shifts[\"duration\"] = df_shifts.wend - df_shifts.wstart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Sub-step #5 : Compute the minimum demand for each shift.\n",
    "\n",
    "Minimum demand is the product of duration (in hours) by the minimum required number of nurses. Thus, in number of \n",
    "nurse-hours, this demand is stored in another new column `min_demand`.\n",
    "\n",
    "Finally, we display the updated shifts DataFrame with all calculated columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department</th>\n",
       "      <th>day</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>min_req</th>\n",
       "      <th>max_req</th>\n",
       "      <th>dow</th>\n",
       "      <th>wstart</th>\n",
       "      <th>wend</th>\n",
       "      <th>duration</th>\n",
       "      <th>min_demand</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shiftId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Monday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>26</td>\n",
       "      <td>8</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Monday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>36</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>42</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "      <td>50</td>\n",
       "      <td>8</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>36</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>42</td>\n",
       "      <td>6</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>36</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>42</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Cardiac Care</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "      <td>50</td>\n",
       "      <td>8</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>56</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>56</td>\n",
       "      <td>60</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "      <td>66</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>66</td>\n",
       "      <td>74</td>\n",
       "      <td>8</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>56</td>\n",
       "      <td>60</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "      <td>66</td>\n",
       "      <td>6</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>74</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>84</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>84</td>\n",
       "      <td>90</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>90</td>\n",
       "      <td>98</td>\n",
       "      <td>8</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "      <td>84</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>84</td>\n",
       "      <td>90</td>\n",
       "      <td>6</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>98</td>\n",
       "      <td>104</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>104</td>\n",
       "      <td>108</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>108</td>\n",
       "      <td>114</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Friday</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>114</td>\n",
       "      <td>122</td>\n",
       "      <td>8</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Friday</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>104</td>\n",
       "      <td>108</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Consultation</td>\n",
       "      <td>Friday</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>108</td>\n",
       "      <td>114</td>\n",
       "      <td>6</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>122</td>\n",
       "      <td>132</td>\n",
       "      <td>10</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>132</td>\n",
       "      <td>140</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>140</td>\n",
       "      <td>146</td>\n",
       "      <td>6</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>146</td>\n",
       "      <td>156</td>\n",
       "      <td>10</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>12</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>156</td>\n",
       "      <td>164</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Emergency</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>164</td>\n",
       "      <td>170</td>\n",
       "      <td>6</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Geriatrics</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>152</td>\n",
       "      <td>154</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           department        day  start_time  end_time  min_req  max_req  dow  \\\n",
       "shiftId                                                                         \n",
       "0           Emergency     Monday           2         8        3        5    0   \n",
       "1           Emergency     Monday           8        12        4        7    0   \n",
       "2           Emergency     Monday          12        18        2        5    0   \n",
       "3           Emergency     Monday          18         2        3        7    0   \n",
       "4        Consultation     Monday           8        12       10       13    0   \n",
       "5        Consultation     Monday          12        18        8       12    0   \n",
       "6        Cardiac Care     Monday           8        12       10       13    0   \n",
       "7        Cardiac Care     Monday          12        18        8       12    0   \n",
       "8           Emergency    Tuesday           8        12        4        7    1   \n",
       "9           Emergency    Tuesday          12        18        2        5    1   \n",
       "10          Emergency    Tuesday          18         2        3        7    1   \n",
       "11       Consultation    Tuesday           8        12       10       13    1   \n",
       "12       Consultation    Tuesday          12        18        8       12    1   \n",
       "13       Cardiac Care    Tuesday           8        12        4        7    1   \n",
       "14       Cardiac Care    Tuesday          12        18        2        5    1   \n",
       "15       Cardiac Care    Tuesday          18         2        3        7    1   \n",
       "16          Emergency  Wednesday           2         8        3        5    2   \n",
       "17          Emergency  Wednesday           8        12        4        7    2   \n",
       "18          Emergency  Wednesday          12        18        2        5    2   \n",
       "19          Emergency  Wednesday          18         2        3        7    2   \n",
       "20       Consultation  Wednesday           8        12       10       13    2   \n",
       "21       Consultation  Wednesday          12        18        8       12    2   \n",
       "22          Emergency   Thursday           2         8        3        5    3   \n",
       "23          Emergency   Thursday           8        12        4        7    3   \n",
       "24          Emergency   Thursday          12        18        2        5    3   \n",
       "25          Emergency   Thursday          18         2        3        7    3   \n",
       "26       Consultation   Thursday           8        12       10       13    3   \n",
       "27       Consultation   Thursday          12        18        8       12    3   \n",
       "28          Emergency     Friday           2         8        3        5    4   \n",
       "29          Emergency     Friday           8        12        4        7    4   \n",
       "30          Emergency     Friday          12        18        2        5    4   \n",
       "31          Emergency     Friday          18         2        3        7    4   \n",
       "32       Consultation     Friday           8        12       10       13    4   \n",
       "33       Consultation     Friday          12        18        8       12    4   \n",
       "34          Emergency   Saturday           2        12        5        7    5   \n",
       "35          Emergency   Saturday          12        20        7        9    5   \n",
       "36          Emergency   Saturday          20         2       12       12    5   \n",
       "37          Emergency     Sunday           2        12        5        7    6   \n",
       "38          Emergency     Sunday          12        20        7        9    6   \n",
       "39          Emergency     Sunday          20         2        8       12    6   \n",
       "40         Geriatrics     Sunday           8        10        2        5    6   \n",
       "\n",
       "         wstart  wend  duration  min_demand  \n",
       "shiftId                                      \n",
       "0             2     8         6          18  \n",
       "1             8    12         4          16  \n",
       "2            12    18         6          12  \n",
       "3            18    26         8          24  \n",
       "4             8    12         4          40  \n",
       "5            12    18         6          48  \n",
       "6             8    12         4          40  \n",
       "7            12    18         6          48  \n",
       "8            32    36         4          16  \n",
       "9            36    42         6          12  \n",
       "10           42    50         8          24  \n",
       "11           32    36         4          40  \n",
       "12           36    42         6          48  \n",
       "13           32    36         4          16  \n",
       "14           36    42         6          12  \n",
       "15           42    50         8          24  \n",
       "16           50    56         6          18  \n",
       "17           56    60         4          16  \n",
       "18           60    66         6          12  \n",
       "19           66    74         8          24  \n",
       "20           56    60         4          40  \n",
       "21           60    66         6          48  \n",
       "22           74    80         6          18  \n",
       "23           80    84         4          16  \n",
       "24           84    90         6          12  \n",
       "25           90    98         8          24  \n",
       "26           80    84         4          40  \n",
       "27           84    90         6          48  \n",
       "28           98   104         6          18  \n",
       "29          104   108         4          16  \n",
       "30          108   114         6          12  \n",
       "31          114   122         8          24  \n",
       "32          104   108         4          40  \n",
       "33          108   114         6          48  \n",
       "34          122   132        10          50  \n",
       "35          132   140         8          56  \n",
       "36          140   146         6          72  \n",
       "37          146   156        10          50  \n",
       "38          156   164         8          56  \n",
       "39          164   170         6          48  \n",
       "40          152   154         2           4  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# also compute minimum demand in nurse-hours\n",
    "df_shifts[\"min_demand\"] = df_shifts.min_req * df_shifts.duration\n",
    "\n",
    "# finally check the modified shifts dataframe\n",
    "df_shifts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Step 5: Set up the prescriptive model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "* system is: Linux 64bit\n",
      "* Python is present, version is 2.7.13\n",
      "* docplex is present, version is (2, 5, 92)\n",
      "* CPLEX wrapper is present, version is 12.8.0.0, located at: /user-home/_global_/python-2.7\n"
     ]
    }
   ],
   "source": [
    "from docplex.mp.environment import Environment\n",
    "env = Environment()\n",
    "env.print_information()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Create the DOcplex model\n",
    "The model contains all the business constraints and defines the objective.\n",
    "\n",
    "We now use CPLEX Modeling for Python to build a Mixed Integer Programming (MIP) model for this problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "from docplex.mp.model import Model\n",
    "mdl = Model(name=\"nurses\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Define the decision variables\n",
    "\n",
    "For each (nurse, shift) pair, we create one binary variable that is equal to 1 when the nurse is assigned to the shift.\n",
    "\n",
    "We use the `binary_var_matrix` method of class `Model`, as each binary variable is indexed by _two_ objects: one nurse and one shift."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# first global collections to iterate upon\n",
    "all_nurses = df_nurses.index.values\n",
    "all_shifts = df_shifts.index.values\n",
    "\n",
    "# the assignment variables.\n",
    "assigned = mdl.binary_var_matrix(keys1=all_nurses, keys2=all_shifts, name=\"assign_%s_%s\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Express the business constraints\n",
    "\n",
    "#####  Overlapping shifts\n",
    "\n",
    "Some shifts overlap in time, and thus cannot be assigned to the same nurse.\n",
    "To check whether two shifts overlap in time, we start by ordering all shifts with respect to their *wstart* and *duration* properties. Then, for each shift, we iterate over the subsequent shifts in this ordered list to easily compute the subset of overlapping shifts.\n",
    "\n",
    "We use *pandas* operations to implement this algorithm. But first, we organize all decision variables in a DataFrame.\n",
    "\n",
    "For convenience, we also organize the decision variables in a pivot table with *nurses* as row index and *shifts* as columns. The *pandas* *unstack* operation does this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"21\" halign=\"left\">assigned</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>all_shifts</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>31</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>40</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>all_nurses</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Anne</th>\n",
       "      <td>assign_Anne_0</td>\n",
       "      <td>assign_Anne_1</td>\n",
       "      <td>assign_Anne_2</td>\n",
       "      <td>assign_Anne_3</td>\n",
       "      <td>assign_Anne_4</td>\n",
       "      <td>assign_Anne_5</td>\n",
       "      <td>assign_Anne_6</td>\n",
       "      <td>assign_Anne_7</td>\n",
       "      <td>assign_Anne_8</td>\n",
       "      <td>assign_Anne_9</td>\n",
       "      <td>...</td>\n",
       "      <td>assign_Anne_31</td>\n",
       "      <td>assign_Anne_32</td>\n",
       "      <td>assign_Anne_33</td>\n",
       "      <td>assign_Anne_34</td>\n",
       "      <td>assign_Anne_35</td>\n",
       "      <td>assign_Anne_36</td>\n",
       "      <td>assign_Anne_37</td>\n",
       "      <td>assign_Anne_38</td>\n",
       "      <td>assign_Anne_39</td>\n",
       "      <td>assign_Anne_40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bethanie</th>\n",
       "      <td>assign_Bethanie_0</td>\n",
       "      <td>assign_Bethanie_1</td>\n",
       "      <td>assign_Bethanie_2</td>\n",
       "      <td>assign_Bethanie_3</td>\n",
       "      <td>assign_Bethanie_4</td>\n",
       "      <td>assign_Bethanie_5</td>\n",
       "      <td>assign_Bethanie_6</td>\n",
       "      <td>assign_Bethanie_7</td>\n",
       "      <td>assign_Bethanie_8</td>\n",
       "      <td>assign_Bethanie_9</td>\n",
       "      <td>...</td>\n",
       "      <td>assign_Bethanie_31</td>\n",
       "      <td>assign_Bethanie_32</td>\n",
       "      <td>assign_Bethanie_33</td>\n",
       "      <td>assign_Bethanie_34</td>\n",
       "      <td>assign_Bethanie_35</td>\n",
       "      <td>assign_Bethanie_36</td>\n",
       "      <td>assign_Bethanie_37</td>\n",
       "      <td>assign_Bethanie_38</td>\n",
       "      <td>assign_Bethanie_39</td>\n",
       "      <td>assign_Bethanie_40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Betsy</th>\n",
       "      <td>assign_Betsy_0</td>\n",
       "      <td>assign_Betsy_1</td>\n",
       "      <td>assign_Betsy_2</td>\n",
       "      <td>assign_Betsy_3</td>\n",
       "      <td>assign_Betsy_4</td>\n",
       "      <td>assign_Betsy_5</td>\n",
       "      <td>assign_Betsy_6</td>\n",
       "      <td>assign_Betsy_7</td>\n",
       "      <td>assign_Betsy_8</td>\n",
       "      <td>assign_Betsy_9</td>\n",
       "      <td>...</td>\n",
       "      <td>assign_Betsy_31</td>\n",
       "      <td>assign_Betsy_32</td>\n",
       "      <td>assign_Betsy_33</td>\n",
       "      <td>assign_Betsy_34</td>\n",
       "      <td>assign_Betsy_35</td>\n",
       "      <td>assign_Betsy_36</td>\n",
       "      <td>assign_Betsy_37</td>\n",
       "      <td>assign_Betsy_38</td>\n",
       "      <td>assign_Betsy_39</td>\n",
       "      <td>assign_Betsy_40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cathy</th>\n",
       "      <td>assign_Cathy_0</td>\n",
       "      <td>assign_Cathy_1</td>\n",
       "      <td>assign_Cathy_2</td>\n",
       "      <td>assign_Cathy_3</td>\n",
       "      <td>assign_Cathy_4</td>\n",
       "      <td>assign_Cathy_5</td>\n",
       "      <td>assign_Cathy_6</td>\n",
       "      <td>assign_Cathy_7</td>\n",
       "      <td>assign_Cathy_8</td>\n",
       "      <td>assign_Cathy_9</td>\n",
       "      <td>...</td>\n",
       "      <td>assign_Cathy_31</td>\n",
       "      <td>assign_Cathy_32</td>\n",
       "      <td>assign_Cathy_33</td>\n",
       "      <td>assign_Cathy_34</td>\n",
       "      <td>assign_Cathy_35</td>\n",
       "      <td>assign_Cathy_36</td>\n",
       "      <td>assign_Cathy_37</td>\n",
       "      <td>assign_Cathy_38</td>\n",
       "      <td>assign_Cathy_39</td>\n",
       "      <td>assign_Cathy_40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cecilia</th>\n",
       "      <td>assign_Cecilia_0</td>\n",
       "      <td>assign_Cecilia_1</td>\n",
       "      <td>assign_Cecilia_2</td>\n",
       "      <td>assign_Cecilia_3</td>\n",
       "      <td>assign_Cecilia_4</td>\n",
       "      <td>assign_Cecilia_5</td>\n",
       "      <td>assign_Cecilia_6</td>\n",
       "      <td>assign_Cecilia_7</td>\n",
       "      <td>assign_Cecilia_8</td>\n",
       "      <td>assign_Cecilia_9</td>\n",
       "      <td>...</td>\n",
       "      <td>assign_Cecilia_31</td>\n",
       "      <td>assign_Cecilia_32</td>\n",
       "      <td>assign_Cecilia_33</td>\n",
       "      <td>assign_Cecilia_34</td>\n",
       "      <td>assign_Cecilia_35</td>\n",
       "      <td>assign_Cecilia_36</td>\n",
       "      <td>assign_Cecilia_37</td>\n",
       "      <td>assign_Cecilia_38</td>\n",
       "      <td>assign_Cecilia_39</td>\n",
       "      <td>assign_Cecilia_40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     assigned                                        \\\n",
       "all_shifts                 0                  1                  2    \n",
       "all_nurses                                                            \n",
       "Anne            assign_Anne_0      assign_Anne_1      assign_Anne_2   \n",
       "Bethanie    assign_Bethanie_0  assign_Bethanie_1  assign_Bethanie_2   \n",
       "Betsy          assign_Betsy_0     assign_Betsy_1     assign_Betsy_2   \n",
       "Cathy          assign_Cathy_0     assign_Cathy_1     assign_Cathy_2   \n",
       "Cecilia      assign_Cecilia_0   assign_Cecilia_1   assign_Cecilia_2   \n",
       "\n",
       "                                                                     \\\n",
       "all_shifts                 3                  4                  5    \n",
       "all_nurses                                                            \n",
       "Anne            assign_Anne_3      assign_Anne_4      assign_Anne_5   \n",
       "Bethanie    assign_Bethanie_3  assign_Bethanie_4  assign_Bethanie_5   \n",
       "Betsy          assign_Betsy_3     assign_Betsy_4     assign_Betsy_5   \n",
       "Cathy          assign_Cathy_3     assign_Cathy_4     assign_Cathy_5   \n",
       "Cecilia      assign_Cecilia_3   assign_Cecilia_4   assign_Cecilia_5   \n",
       "\n",
       "                                                                     \\\n",
       "all_shifts                 6                  7                  8    \n",
       "all_nurses                                                            \n",
       "Anne            assign_Anne_6      assign_Anne_7      assign_Anne_8   \n",
       "Bethanie    assign_Bethanie_6  assign_Bethanie_7  assign_Bethanie_8   \n",
       "Betsy          assign_Betsy_6     assign_Betsy_7     assign_Betsy_8   \n",
       "Cathy          assign_Cathy_6     assign_Cathy_7     assign_Cathy_8   \n",
       "Cecilia      assign_Cecilia_6   assign_Cecilia_7   assign_Cecilia_8   \n",
       "\n",
       "                                      ...                              \\\n",
       "all_shifts                 9          ...                          31   \n",
       "all_nurses                            ...                               \n",
       "Anne            assign_Anne_9         ...              assign_Anne_31   \n",
       "Bethanie    assign_Bethanie_9         ...          assign_Bethanie_31   \n",
       "Betsy          assign_Betsy_9         ...             assign_Betsy_31   \n",
       "Cathy          assign_Cathy_9         ...             assign_Cathy_31   \n",
       "Cecilia      assign_Cecilia_9         ...           assign_Cecilia_31   \n",
       "\n",
       "                                                                        \\\n",
       "all_shifts                  32                  33                  34   \n",
       "all_nurses                                                               \n",
       "Anne            assign_Anne_32      assign_Anne_33      assign_Anne_34   \n",
       "Bethanie    assign_Bethanie_32  assign_Bethanie_33  assign_Bethanie_34   \n",
       "Betsy          assign_Betsy_32     assign_Betsy_33     assign_Betsy_34   \n",
       "Cathy          assign_Cathy_32     assign_Cathy_33     assign_Cathy_34   \n",
       "Cecilia      assign_Cecilia_32   assign_Cecilia_33   assign_Cecilia_34   \n",
       "\n",
       "                                                                        \\\n",
       "all_shifts                  35                  36                  37   \n",
       "all_nurses                                                               \n",
       "Anne            assign_Anne_35      assign_Anne_36      assign_Anne_37   \n",
       "Bethanie    assign_Bethanie_35  assign_Bethanie_36  assign_Bethanie_37   \n",
       "Betsy          assign_Betsy_35     assign_Betsy_36     assign_Betsy_37   \n",
       "Cathy          assign_Cathy_35     assign_Cathy_36     assign_Cathy_37   \n",
       "Cecilia      assign_Cecilia_35   assign_Cecilia_36   assign_Cecilia_37   \n",
       "\n",
       "                                                                        \n",
       "all_shifts                  38                  39                  40  \n",
       "all_nurses                                                              \n",
       "Anne            assign_Anne_38      assign_Anne_39      assign_Anne_40  \n",
       "Bethanie    assign_Bethanie_38  assign_Bethanie_39  assign_Bethanie_40  \n",
       "Betsy          assign_Betsy_38     assign_Betsy_39     assign_Betsy_40  \n",
       "Cathy          assign_Cathy_38     assign_Cathy_39     assign_Cathy_40  \n",
       "Cecilia      assign_Cecilia_38   assign_Cecilia_39   assign_Cecilia_40  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Organize decision variables in a DataFrame\n",
    "df_assigned = DataFrame({'assigned': assigned})\n",
    "df_assigned.index.names=['all_nurses', 'all_shifts']\n",
    "\n",
    "# Re-organize the Data Frame as a pivot table with nurses as row index and shifts as columns:\n",
    "df_assigned_pivot = df_assigned.unstack(level='all_shifts')\n",
    "\n",
    "# Create a pivot using nurses and shifts index as dimensions\n",
    "#df_assigned_pivot = df_assigned.reset_index().pivot(index='all_nurses', columns='all_shifts', values='assigned')\n",
    "\n",
    "# Display first rows of the pivot table\n",
    "df_assigned_pivot.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We create a DataFrame representing a list of shifts sorted by *\"wstart\"* and *\"duration\"*.\n",
    "This sorted list will be used to easily detect overlapping shifts.\n",
    "\n",
    "Note that indices are reset after sorting so that the DataFrame can be indexed with respect to\n",
    "the index in the sorted list and not the original unsorted list. This is the purpose of the *reset_index()*\n",
    "operation which also adds a new column named *\"shiftId\"* with the original index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>shiftId</th>\n",
       "      <th>wstart</th>\n",
       "      <th>wend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   shiftId  wstart  wend\n",
       "0        0       2     8\n",
       "1        1       8    12\n",
       "2        4       8    12\n",
       "3        6       8    12\n",
       "4        2      12    18"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a Data Frame representing a list of shifts sorted by wstart and duration.\n",
    "# One keeps only the three relevant columns: 'shiftId', 'wstart' and 'wend' in the resulting Data Frame \n",
    "df_sorted_shifts = df_shifts.sort_values(['wstart','duration']).reset_index()[['shiftId', 'wstart', 'wend']]\n",
    "\n",
    "# Display the first rows of the newly created Data Frame\n",
    "df_sorted_shifts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Next, we state that for any pair of shifts that overlap in time, a nurse can be assigned to only one of the two."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#incompatible shift constraints: 640\n"
     ]
    }
   ],
   "source": [
    "number_of_incompatible_shift_constraints = 0\n",
    "for shift in df_sorted_shifts.itertuples():\n",
    "    # Iterate over following shifts\n",
    "    # 'shift[0]' contains the index of the current shift in the df_sorted_shifts Data Frame\n",
    "    for shift_2 in df_sorted_shifts.iloc[shift[0] + 1:].itertuples():\n",
    "        if (shift_2.wstart < shift.wend):\n",
    "            # Iterate over all nurses to force incompatible assignment for the current pair of overlapping shifts\n",
    "            for nurse_assignments in df_assigned_pivot.iloc[:, [shift.shiftId, shift_2.shiftId]].itertuples():\n",
    "                # this is actually a logical OR\n",
    "                mdl.add_constraint(nurse_assignments[1] + nurse_assignments[2] <= 1)\n",
    "                number_of_incompatible_shift_constraints += 1\n",
    "        else:\n",
    "            # No need to test overlap with following shifts\n",
    "            break\n",
    "print(\"#incompatible shift constraints: {}\".format(number_of_incompatible_shift_constraints))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "##### Vacations\n",
    "\n",
    "When the nurse is on vacation, he cannot be assigned to any shift starting that day.\n",
    "\n",
    "We use the *pandas* *merge* operation to create a join between the *\"df_vacations\"*, *\"df_shifts\"*, and *\"df_assigned\"* DataFrames. Each row of the resulting DataFrame contains the assignment decision variable corresponding to the matching (nurse, shift) pair."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nurse</th>\n",
       "      <th>day</th>\n",
       "      <th>dow</th>\n",
       "      <th>shiftId</th>\n",
       "      <th>all_nurses</th>\n",
       "      <th>all_shifts</th>\n",
       "      <th>assigned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anne</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>Anne</td>\n",
       "      <td>28</td>\n",
       "      <td>assign_Anne_28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anne</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>Anne</td>\n",
       "      <td>29</td>\n",
       "      <td>assign_Anne_29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anne</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4</td>\n",
       "      <td>30</td>\n",
       "      <td>Anne</td>\n",
       "      <td>30</td>\n",
       "      <td>assign_Anne_30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anne</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>Anne</td>\n",
       "      <td>31</td>\n",
       "      <td>assign_Anne_31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Anne</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>Anne</td>\n",
       "      <td>32</td>\n",
       "      <td>assign_Anne_32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  nurse     day  dow  shiftId all_nurses  all_shifts        assigned\n",
       "0  Anne  Friday    4       28       Anne          28  assign_Anne_28\n",
       "1  Anne  Friday    4       29       Anne          29  assign_Anne_29\n",
       "2  Anne  Friday    4       30       Anne          30  assign_Anne_30\n",
       "3  Anne  Friday    4       31       Anne          31  assign_Anne_31\n",
       "4  Anne  Friday    4       32       Anne          32  assign_Anne_32"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add 'day of week' column to vacations Data Frame\n",
    "df_vacations['dow'] = df_vacations.day.apply(day_to_day_of_week)\n",
    "\n",
    "# Join 'df_vacations', 'df_shifts' and 'df_assigned' Data Frames to create the list of 'forbidden' assigments.\n",
    "# The 'reset_index()' function is invoked to move 'shiftId' index as a column in 'df_shifts' Data Frame, and\n",
    "# to move the index pair ('all_nurses', 'all_shifts') as columns in 'df_assigned' Data Frame.\n",
    "# 'reset_index()' is invoked so that a join can be performed between Data Frame, based on column names.\n",
    "df_assigned_reindexed = df_assigned.reset_index()\n",
    "df_vacation_forbidden_assignments = df_vacations.merge(df_shifts.reset_index()[['dow', 'shiftId']]).merge(\n",
    "    df_assigned_reindexed, left_on=['nurse', 'shiftId'], right_on=['all_nurses', 'all_shifts'])\n",
    "\n",
    "# Here are the first few rows of the resulting Data Frames joins\n",
    "df_vacation_forbidden_assignments.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# vacation forbids: 342 assignments\n"
     ]
    }
   ],
   "source": [
    "for forbidden_assignment in df_vacation_forbidden_assignments.itertuples():\n",
    "    # to forbid an assignment just set the variable to zero.\n",
    "    mdl.add_constraint(forbidden_assignment.assigned == 0)\n",
    "print(\"# vacation forbids: {} assignments\".format(len(df_vacation_forbidden_assignments)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "##### Associations\n",
    "\n",
    "Some pairs of nurses get along particularly well, so we wish to assign them together as a team. In other words, for every such couple and for each shift, both assignment variables should always be equal.\n",
    "Either both nurses work the shift, or both do not.\n",
    "\n",
    "In the same way we modeled *vacations*, we use the *pandas* merge operation to create a DataFrame for which each row contains the pair of nurse-shift assignment decision variables matching each association."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nurse1</th>\n",
       "      <th>nurse2</th>\n",
       "      <th>all_nurses_1</th>\n",
       "      <th>all_shifts</th>\n",
       "      <th>assigned_1</th>\n",
       "      <th>all_nurses_2</th>\n",
       "      <th>assigned_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Isabelle</td>\n",
       "      <td>Dee</td>\n",
       "      <td>Isabelle</td>\n",
       "      <td>0</td>\n",
       "      <td>assign_Isabelle_0</td>\n",
       "      <td>Dee</td>\n",
       "      <td>assign_Dee_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Isabelle</td>\n",
       "      <td>Dee</td>\n",
       "      <td>Isabelle</td>\n",
       "      <td>1</td>\n",
       "      <td>assign_Isabelle_1</td>\n",
       "      <td>Dee</td>\n",
       "      <td>assign_Dee_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Isabelle</td>\n",
       "      <td>Dee</td>\n",
       "      <td>Isabelle</td>\n",
       "      <td>2</td>\n",
       "      <td>assign_Isabelle_2</td>\n",
       "      <td>Dee</td>\n",
       "      <td>assign_Dee_2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Isabelle</td>\n",
       "      <td>Dee</td>\n",
       "      <td>Isabelle</td>\n",
       "      <td>3</td>\n",
       "      <td>assign_Isabelle_3</td>\n",
       "      <td>Dee</td>\n",
       "      <td>assign_Dee_3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Isabelle</td>\n",
       "      <td>Dee</td>\n",
       "      <td>Isabelle</td>\n",
       "      <td>4</td>\n",
       "      <td>assign_Isabelle_4</td>\n",
       "      <td>Dee</td>\n",
       "      <td>assign_Dee_4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     nurse1 nurse2 all_nurses_1  all_shifts         assigned_1 all_nurses_2  \\\n",
       "0  Isabelle    Dee     Isabelle           0  assign_Isabelle_0          Dee   \n",
       "1  Isabelle    Dee     Isabelle           1  assign_Isabelle_1          Dee   \n",
       "2  Isabelle    Dee     Isabelle           2  assign_Isabelle_2          Dee   \n",
       "3  Isabelle    Dee     Isabelle           3  assign_Isabelle_3          Dee   \n",
       "4  Isabelle    Dee     Isabelle           4  assign_Isabelle_4          Dee   \n",
       "\n",
       "     assigned_2  \n",
       "0  assign_Dee_0  \n",
       "1  assign_Dee_1  \n",
       "2  assign_Dee_2  \n",
       "3  assign_Dee_3  \n",
       "4  assign_Dee_4  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join 'df_assignment' Data Frame twice, based on associations to get corresponding decision variables pairs for all shifts\n",
    "# The 'suffixes' parameter in the second merge indicates our preference for updating the name of columns that occur both\n",
    "# in the first and second argument Data Frames (in our case, these columns are 'all_nurses' and 'assigned').\n",
    "df_preferred_assign = df_associations.merge(\n",
    "    df_assigned_reindexed, left_on='nurse1', right_on='all_nurses').merge(\n",
    "    df_assigned_reindexed, left_on=['nurse2', 'all_shifts'], right_on=['all_nurses', 'all_shifts'], suffixes=('_1','_2'))\n",
    "\n",
    "# Here are the first few rows of the resulting Data Frames joins\n",
    "df_preferred_assign.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "The associations constraint can now easily be formulated by iterating on the rows of the *\"df_preferred_assign\"* DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "for preferred_assign in df_preferred_assign.itertuples():\n",
    "    mdl.add_constraint(preferred_assign.assigned_1 == preferred_assign.assigned_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "##### Incompatibilities\n",
    "\n",
    "Similarly, certain pairs of nurses do not get along well, and we want to avoid having them together on a shift.\n",
    "In other terms, for each shift, both nurses of an incompatible pair cannot be assigned together to the sift. Again, we state a logical OR between the two assignments: at most one nurse from the pair can work the shift.\n",
    "\n",
    "We first create a DataFrame whose rows contain pairs of invalid assignment decision variables, using the same *pandas* `merge` operations as in the previous step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nurse1</th>\n",
       "      <th>nurse2</th>\n",
       "      <th>all_nurses_1</th>\n",
       "      <th>all_shifts</th>\n",
       "      <th>assigned_1</th>\n",
       "      <th>all_nurses_2</th>\n",
       "      <th>assigned_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Patricia</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>Patricia</td>\n",
       "      <td>0</td>\n",
       "      <td>assign_Patricia_0</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>assign_Patrick_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Patricia</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>Patricia</td>\n",
       "      <td>1</td>\n",
       "      <td>assign_Patricia_1</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>assign_Patrick_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Patricia</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>Patricia</td>\n",
       "      <td>2</td>\n",
       "      <td>assign_Patricia_2</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>assign_Patrick_2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Patricia</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>Patricia</td>\n",
       "      <td>3</td>\n",
       "      <td>assign_Patricia_3</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>assign_Patrick_3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Patricia</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>Patricia</td>\n",
       "      <td>4</td>\n",
       "      <td>assign_Patricia_4</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>assign_Patrick_4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     nurse1   nurse2 all_nurses_1  all_shifts         assigned_1 all_nurses_2  \\\n",
       "0  Patricia  Patrick     Patricia           0  assign_Patricia_0      Patrick   \n",
       "1  Patricia  Patrick     Patricia           1  assign_Patricia_1      Patrick   \n",
       "2  Patricia  Patrick     Patricia           2  assign_Patricia_2      Patrick   \n",
       "3  Patricia  Patrick     Patricia           3  assign_Patricia_3      Patrick   \n",
       "4  Patricia  Patrick     Patricia           4  assign_Patricia_4      Patrick   \n",
       "\n",
       "         assigned_2  \n",
       "0  assign_Patrick_0  \n",
       "1  assign_Patrick_1  \n",
       "2  assign_Patrick_2  \n",
       "3  assign_Patrick_3  \n",
       "4  assign_Patrick_4  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join assignment Data Frame twice, based on incompatibilities Data Frame to get corresponding decision variables pairs\n",
    "#  for all shifts\n",
    "df_incompatible_assign = df_incompatibilities.merge(\n",
    "    df_assigned_reindexed, left_on='nurse1', right_on='all_nurses').merge(\n",
    "    df_assigned_reindexed, left_on=['nurse2', 'all_shifts'], right_on=['all_nurses', 'all_shifts'], suffixes=('_1','_2'))\n",
    "\n",
    "# Here are the first few rows of the resulting Data Frames joins\n",
    "df_incompatible_assign.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "The incompatibilities constraint can now easily be formulated, by iterating on the rows of the *\"df_incompatible_assign\"* DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "for incompatible_assign in df_incompatible_assign.itertuples():\n",
    "    mdl.add_constraint(incompatible_assign.assigned_1 + incompatible_assign.assigned_2 <= 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "##### Constraints on work time\n",
    "\n",
    "Regulations force constraints on the total work time over a week;\n",
    "and we compute this total work time in a new variable. We store the variable in an extra column in the nurse DataFrame.\n",
    "\n",
    "The variable is declared as _continuous_ though it contains only integer values. This is done to avoid adding unnecessary integer variables for the _branch and bound_ algorithm. \n",
    "These variables are not true decision variables; they are used to express work constraints.\n",
    "\n",
    "From a *pandas* perspective, we apply a function over the rows of the nurse DataFrame to create this variable and store it into a new column of the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seniority</th>\n",
       "      <th>qualification</th>\n",
       "      <th>pay_rate</th>\n",
       "      <th>worktime</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Anne</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>worktime_Anne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bethanie</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "      <td>worktime_Bethanie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Betsy</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>worktime_Betsy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cathy</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>worktime_Cathy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cecilia</th>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>38</td>\n",
       "      <td>worktime_Cecilia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chris</th>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>38</td>\n",
       "      <td>worktime_Chris</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cindy</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "      <td>worktime_Cindy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>David</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>worktime_David</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Debbie</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>24</td>\n",
       "      <td>worktime_Debbie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dee</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>worktime_Dee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gloria</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>worktime_Gloria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Isabelle</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>worktime_Isabelle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jane</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>23</td>\n",
       "      <td>worktime_Jane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Janelle</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>worktime_Janelle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Janice</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>worktime_Janice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jemma</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>22</td>\n",
       "      <td>worktime_Jemma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Joan</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>24</td>\n",
       "      <td>worktime_Joan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Joyce</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "      <td>worktime_Joyce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jude</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>worktime_Jude</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Julie</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>22</td>\n",
       "      <td>worktime_Julie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Juliet</th>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>worktime_Juliet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kate</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>24</td>\n",
       "      <td>worktime_Kate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nancy</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>worktime_Nancy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nathalie</th>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>38</td>\n",
       "      <td>worktime_Nathalie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nicole</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>worktime_Nicole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Patricia</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>worktime_Patricia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Patrick</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>worktime_Patrick</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roberta</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>26</td>\n",
       "      <td>worktime_Roberta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Suzanne</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>worktime_Suzanne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vickie</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>worktime_Vickie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendie</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "      <td>worktime_Wendie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zoe</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "      <td>worktime_Zoe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          seniority  qualification  pay_rate           worktime\n",
       "name                                                           \n",
       "Anne             11              1        25      worktime_Anne\n",
       "Bethanie          4              5        28  worktime_Bethanie\n",
       "Betsy             2              2        17     worktime_Betsy\n",
       "Cathy             2              2        17     worktime_Cathy\n",
       "Cecilia           9              5        38   worktime_Cecilia\n",
       "Chris            11              4        38     worktime_Chris\n",
       "Cindy             5              2        21     worktime_Cindy\n",
       "David             1              2        15     worktime_David\n",
       "Debbie            7              2        24    worktime_Debbie\n",
       "Dee               3              3        21       worktime_Dee\n",
       "Gloria            8              2        25    worktime_Gloria\n",
       "Isabelle          3              1        16  worktime_Isabelle\n",
       "Jane              3              4        23      worktime_Jane\n",
       "Janelle           4              3        22   worktime_Janelle\n",
       "Janice            2              2        17    worktime_Janice\n",
       "Jemma             2              4        22     worktime_Jemma\n",
       "Joan              5              3        24      worktime_Joan\n",
       "Joyce             8              3        29     worktime_Joyce\n",
       "Jude              4              3        22      worktime_Jude\n",
       "Julie             6              2        22     worktime_Julie\n",
       "Juliet            7              4        31    worktime_Juliet\n",
       "Kate              5              3        24      worktime_Kate\n",
       "Nancy             8              4        32     worktime_Nancy\n",
       "Nathalie          9              5        38  worktime_Nathalie\n",
       "Nicole            0              2        14    worktime_Nicole\n",
       "Patricia          1              1        13  worktime_Patricia\n",
       "Patrick           6              1        19   worktime_Patrick\n",
       "Roberta           3              5        26   worktime_Roberta\n",
       "Suzanne           5              1        18   worktime_Suzanne\n",
       "Vickie            7              1        20    worktime_Vickie\n",
       "Wendie            5              2        21    worktime_Wendie\n",
       "Zoe               8              3        29       worktime_Zoe"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# auxiliary function to create worktime variable from a row\n",
    "def make_var(row, varname_fmt):\n",
    "    return mdl.continuous_var(name=varname_fmt % row.name, lb=0)\n",
    "\n",
    "# apply the function over nurse rows and store result in a new column\n",
    "df_nurses[\"worktime\"] = df_nurses.apply(lambda r: make_var(r, \"worktime_%s\"), axis=1)\n",
    "\n",
    "# display nurse dataframe\n",
    "df_nurses"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "###### Define total work time\n",
    "\n",
    "Work time variables must be constrained to be equal to the sum of hours actually worked.\n",
    "\n",
    "We use the *pandas* *groupby* operation to collect all assignment decision variables for each nurse in a separate series. Then, we iterate over nurses to post a constraint calculating the actual worktime for each nurse as the dot product of the series of nurse-shift assignments with the series of shift durations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model: nurses\n",
      " - number of variables: 1344\n",
      "   - binary=1312, integer=0, continuous=32\n",
      " - number of constraints: 1547\n",
      "   - linear=1547\n",
      " - parameters: defaults\n"
     ]
    }
   ],
   "source": [
    "# Use pandas' groupby operation to enforce constraint calculating worktime for each nurse as the sum of all assigned\n",
    "#  shifts times the duration of each shift\n",
    "for nurse, nurse_assignments in df_assigned.groupby(level='all_nurses'):\n",
    "    mdl.add_constraint(df_nurses.worktime[nurse] == mdl.dot(nurse_assignments.assigned, df_shifts.duration))\n",
    "                       \n",
    "# print model information and check we now have 32 extra continuous variables\n",
    "mdl.print_information()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "###### Maximum work time\n",
    "\n",
    "For each nurse, we add a constraint to enforce the maximum work time for a week.\n",
    "Again we use the `apply` method, this time with an anonymous lambda function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name\n",
       "Anne            worktime_Anne <= 40\n",
       "Bethanie    worktime_Bethanie <= 40\n",
       "Betsy          worktime_Betsy <= 40\n",
       "Cathy          worktime_Cathy <= 40\n",
       "Cecilia      worktime_Cecilia <= 40\n",
       "Chris          worktime_Chris <= 40\n",
       "Cindy          worktime_Cindy <= 40\n",
       "David          worktime_David <= 40\n",
       "Debbie        worktime_Debbie <= 40\n",
       "Dee              worktime_Dee <= 40\n",
       "Gloria        worktime_Gloria <= 40\n",
       "Isabelle    worktime_Isabelle <= 40\n",
       "Jane            worktime_Jane <= 40\n",
       "Janelle      worktime_Janelle <= 40\n",
       "Janice        worktime_Janice <= 40\n",
       "Jemma          worktime_Jemma <= 40\n",
       "Joan            worktime_Joan <= 40\n",
       "Joyce          worktime_Joyce <= 40\n",
       "Jude            worktime_Jude <= 40\n",
       "Julie          worktime_Julie <= 40\n",
       "Juliet        worktime_Juliet <= 40\n",
       "Kate            worktime_Kate <= 40\n",
       "Nancy          worktime_Nancy <= 40\n",
       "Nathalie    worktime_Nathalie <= 40\n",
       "Nicole        worktime_Nicole <= 40\n",
       "Patricia    worktime_Patricia <= 40\n",
       "Patrick      worktime_Patrick <= 40\n",
       "Roberta      worktime_Roberta <= 40\n",
       "Suzanne      worktime_Suzanne <= 40\n",
       "Vickie        worktime_Vickie <= 40\n",
       "Wendie        worktime_Wendie <= 40\n",
       "Zoe              worktime_Zoe <= 40\n",
       "Name: worktime, dtype: object"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we use pandas' apply() method to set an upper bound on all worktime variables.\n",
    "def set_max_work_time(v):\n",
    "    v.ub = max_work_time\n",
    "    # Optionally: return a string for fancy display of the constraint in the Output cell\n",
    "    return str(v) + ' <= ' + str(v.ub)\n",
    "\n",
    "df_nurses[\"worktime\"].apply(convert_dtype=False, func=set_max_work_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "##### Minimum requirement for shifts\n",
    "\n",
    "Each shift requires a minimum number of nurses. \n",
    "For each shift, the sum over all nurses of assignments to this shift\n",
    "must be greater than the minimum requirement.\n",
    "\n",
    "The *pandas* *groupby* operation is invoked to collect all assignment decision variables for each shift in a separate series. Then, we iterate over shifts to post the constraint enforcing the minimum number of nurse assignments for each shift."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Use pandas' groupby operation to enforce minimum requirement constraint for each shift\n",
    "for shift, shift_nurses in df_assigned.groupby(level='all_shifts'):\n",
    "    mdl.add_constraint(mdl.sum(shift_nurses.assigned) >= df_shifts.min_req[shift])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Express the objective\n",
    "\n",
    "The objective mixes different (and contradictory) KPIs. \n",
    "\n",
    "The first KPI is the total salary cost, computed as the sum of work times over all nurses, weighted by pay rate.\n",
    "\n",
    "We compute this KPI as an expression from the variables we previously defined by using the panda summation over the DOcplex objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DecisionKPI(name=Total salary cost,expr=25worktime_Anne+28worktime_Bethanie+17worktime_Betsy+17worktime_..)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# again leverage pandas to create a series of expressions: costs of each nurse\n",
    "total_salary_series = df_nurses.worktime * df_nurses.pay_rate\n",
    "\n",
    "# compute global salary cost using pandas sum()\n",
    "# Note that the result is a DOcplex expression: DOcplex if fully compatible with pandas\n",
    "total_salary_cost = total_salary_series.sum()\n",
    "mdl.add_kpi(total_salary_cost, \"Total salary cost\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "##### Minimizing salary cost\n",
    "\n",
    "In a preliminary version of the model, we minimize the total salary cost. This is accomplished\n",
    "using the `Model.minimize()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model: nurses\n",
      " - number of variables: 1344\n",
      "   - binary=1312, integer=0, continuous=32\n",
      " - number of constraints: 1588\n",
      "   - linear=1588\n",
      " - parameters: defaults\n"
     ]
    }
   ],
   "source": [
    "mdl.minimize(total_salary_cost)\n",
    "mdl.print_information()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Solve with the Decision Optimization solve service\n",
    "\n",
    "Now we have everything we need to solve the model, using `Model.solve()`. The following cell solves using your local CPLEX (if any, and provided you have added it to your `PYTHONPATH` variable). \n",
    "If you do not have CPLEX installed, please enter your DOcplexcloud credentials below in the `key` and `url` fields in order to solve on DOcplexcloud."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2018-05-03T19:03:29Z, INFO] CPLEX version 12070100\n",
      "[2018-05-03T19:03:29Z, INFO] Parameter file:\n",
      "[2018-05-03T19:03:29Z, INFO] # -- This content is generated by DOcplex\n",
      "[2018-05-03T19:03:29Z, INFO] CPLEX Parameter File Version 12.8.0.0\n",
      "[2018-05-03T19:03:29Z, INFO] CPX_PARAM_EPGAP                  0.00001000000000\n",
      "[2018-05-03T19:03:29Z, INFO] # --- end of generated prm data ---\n",
      "[2018-05-03T19:03:29Z, WARN] Warning:  CPLEX version found in file python_nurses.prm (12.8.0.0) does not match\n",
      "[2018-05-03T19:03:29Z, WARN]           this CPLEX version (12.7.1.0).\n",
      "[2018-05-03T19:03:29Z, WARN] Changed parameter CPX_PARAM_THREADS from 0 to 10\n",
      "[2018-05-03T19:03:29Z, INFO] Param[1,067] = 10\n",
      "[2018-05-03T19:03:29Z, INFO] Param[1,130] = utf-8\n",
      "[2018-05-03T19:03:29Z, INFO] Param[1,132] = -1\n",
      "[2018-05-03T19:03:29Z, INFO] Param[2,009] = 0.000010000000000000000818030539140313095458623138256371021270751953125\n",
      "[2018-05-03T19:03:29Z, INFO] CPXPARAM_WorkDir                                 \"/var/job_processor/work/tmp/java/job8644755491768751521.tmp\"\n",
      "[2018-05-03T19:03:29Z, INFO] CPXPARAM_Threads                                 10\n",
      "[2018-05-03T19:03:29Z, INFO] CPXPARAM_Read_APIEncoding                        \"utf-8\"\n",
      "[2018-05-03T19:03:29Z, INFO] CPXPARAM_Output_CloneLog                         -1\n",
      "[2018-05-03T19:03:29Z, INFO] CPXPARAM_MIP_Tolerances_MIPGap                   1.0000000000000001e-05\n",
      "[2018-05-03T19:03:29Z, INFO] CPXPARAM_MIP_Strategy_CallbackReducedLP          0\n",
      "[2018-05-03T19:03:29Z, INFO] CPXPARAM_Output_IntSolFilePrefix                 \"/var/job_processor/work/tmp/java/job8644755491768751521.tmp/sol\"\n",
      "[2018-05-03T19:03:29Z, INFO] Tried aggregator 2 times.\n",
      "[2018-05-03T19:03:29Z, INFO] MIP Presolve eliminated 997 rows and 379 columns.\n",
      "[2018-05-03T19:03:29Z, INFO] MIP Presolve modified 90 coefficients.\n",
      "[2018-05-03T19:03:29Z, INFO] Aggregator did 41 substitutions.\n",
      "[2018-05-03T19:03:29Z, INFO] Reduced MIP has 550 rows, 922 columns, and 2862 nonzeros.\n",
      "[2018-05-03T19:03:29Z, INFO] Reduced MIP has 892 binaries, 0 generals, 0 SOSs, and 0 indicators.\n",
      "[2018-05-03T19:03:29Z, INFO] Presolve time = 0.01 sec. (3.45 ticks)\n",
      "[2018-05-03T19:03:30Z, INFO] Probing time = 0.00 sec. (0.50 ticks)\n",
      "[2018-05-03T19:03:30Z, INFO] Tried aggregator 1 time.\n",
      "[2018-05-03T19:03:30Z, INFO] Reduced MIP has 550 rows, 922 columns, and 2862 nonzeros.\n",
      "[2018-05-03T19:03:30Z, INFO] Reduced MIP has 892 binaries, 30 generals, 0 SOSs, and 0 indicators.\n",
      "[2018-05-03T19:03:30Z, INFO] Presolve time = 0.00 sec. (1.92 ticks)\n",
      "[2018-05-03T19:03:30Z, INFO] Probing time = 0.00 sec. (0.50 ticks)\n",
      "[2018-05-03T19:03:30Z, INFO] Clique table members: 479.\n",
      "[2018-05-03T19:03:30Z, INFO] MIP emphasis: balance optimality and feasibility.\n",
      "[2018-05-03T19:03:30Z, INFO] MIP search method: dynamic search.\n",
      "[2018-05-03T19:03:30Z, INFO] Parallel mode: deterministic, using up to 10 threads.\n",
      "[2018-05-03T19:03:30Z, INFO] Root relaxation solution time = 0.01 sec. (4.01 ticks)\n",
      "[2018-05-03T19:03:30Z, INFO] \n",
      "[2018-05-03T19:03:30Z, INFO]         Nodes                                         Cuts/\n",
      "[2018-05-03T19:03:30Z, INFO]    Node  Left     Objective  IInf  Best Integer    Best Bound    ItCnt     Gap\n",
      "[2018-05-03T19:03:30Z, INFO] \n",
      "[2018-05-03T19:03:30Z, INFO]       0     0    28824.0000    28                  28824.0000      467\n",
      "[2018-05-03T19:03:30Z, INFO]       0     0    28824.0000    46                    Cuts: 73      615\n",
      "[2018-05-03T19:03:30Z, INFO]       0     0    28824.0000    22                    Cuts: 32      658\n",
      "[2018-05-03T19:03:30Z, INFO]       0     0    28824.0000    38                    Cuts: 47      738\n",
      "[2018-05-03T19:03:30Z, INFO] *     0+    0                        29330.0000    28824.0000             1.73%\n",
      "[2018-05-03T19:03:30Z, INFO] *     0+    0                        29160.0000    28824.0000             1.15%\n",
      "[2018-05-03T19:03:30Z, INFO] *     0+    0                        29064.0000    28824.0000             0.83%\n",
      "[2018-05-03T19:03:30Z, INFO] *     0+    0                        28966.0000    28824.0000             0.49%\n",
      "[2018-05-03T19:03:30Z, INFO]       0     2    28824.0000    10    28966.0000    28824.0000      738    0.49%\n",
      "[2018-05-03T19:03:30Z, INFO] Elapsed time = 0.46 sec. (114.79 ticks, tree = 0.01 MB, solutions = 4)\n",
      "[2018-05-03T19:03:30Z, INFO] *    10+    1                        28936.0000    28824.0000             0.39%\n",
      "[2018-05-03T19:03:30Z, INFO] *    10+    2                        28934.0000    28824.0000             0.38%\n",
      "[2018-05-03T19:03:30Z, INFO] *    11+    2                        28902.0000    28824.0000             0.27%\n",
      "[2018-05-03T19:03:30Z, INFO] *    29+    5                        28856.0000    28824.0000             0.11%\n",
      "[2018-05-03T19:03:30Z, INFO] *  2050+ 1644                        28824.0000    28824.0000             0.00%\n",
      "[2018-05-03T19:03:31Z, INFO] \n",
      "[2018-05-03T19:03:31Z, INFO] GUB cover cuts applied:  18\n",
      "[2018-05-03T19:03:31Z, INFO] Cover cuts applied:  80\n",
      "[2018-05-03T19:03:31Z, INFO] Flow cuts applied:  9\n",
      "[2018-05-03T19:03:31Z, INFO] Mixed integer rounding cuts applied:  13\n",
      "[2018-05-03T19:03:31Z, INFO] Zero-half cuts applied:  12\n",
      "[2018-05-03T19:03:31Z, INFO] Lift and project cuts applied:  5\n",
      "[2018-05-03T19:03:31Z, INFO] Gomory fractional cuts applied:  2\n",
      "[2018-05-03T19:03:31Z, INFO] \n",
      "[2018-05-03T19:03:31Z, INFO] Root node processing (before b&c):\n",
      "[2018-05-03T19:03:31Z, INFO]   Real time             =    0.44 sec. (114.64 ticks)\n",
      "[2018-05-03T19:03:31Z, INFO] Parallel b&c, 10 threads:\n",
      "[2018-05-03T19:03:31Z, INFO]   Real time             =    0.60 sec. (172.06 ticks)\n",
      "[2018-05-03T19:03:31Z, INFO]   Sync time (average)   =    0.24 sec.\n",
      "[2018-05-03T19:03:31Z, INFO]   Wait time (average)   =    0.01 sec.\n",
      "[2018-05-03T19:03:31Z, INFO]                           ------------\n",
      "[2018-05-03T19:03:31Z, INFO] Total (root+branch&cut) =    1.04 sec. (286.69 ticks)\n",
      "[2018-05-03T19:03:31Z, INFO] Incumbent solution:\n",
      "[2018-05-03T19:03:31Z, INFO] MILP objective                                 2.8824000000e+04\n",
      "[2018-05-03T19:03:31Z, INFO] MILP solution norm |x| (Total, Max)            1.46800e+03  4.00000e+01\n",
      "[2018-05-03T19:03:31Z, INFO] MILP solution error (Ax=b) (Total, Max)        0.00000e+00  0.00000e+00\n",
      "[2018-05-03T19:03:31Z, INFO] MILP x bound error (Total, Max)                0.00000e+00  0.00000e+00\n",
      "[2018-05-03T19:03:31Z, INFO] MILP x integrality error (Total, Max)          0.00000e+00  0.00000e+00\n",
      "[2018-05-03T19:03:31Z, INFO] MILP slack bound error (Total, Max)            0.00000e+00  0.00000e+00\n",
      "[2018-05-03T19:03:31Z, INFO] integer optimal solution (101)\n",
      "* model nurses solved with objective = 28824.000\n",
      "*  KPI: Total salary cost = 28824.000\n"
     ]
    }
   ],
   "source": [
    "# Set Cplex mipgap to 1e-5 to enforce precision to be of the order of a unit (objective value magnitude is ~1e+5).\n",
    "mdl.parameters.mip.tolerances.mipgap = 1e-5\n",
    "\n",
    "s = mdl.solve(url=url, key=key, log_output=True)\n",
    "assert s, \"solve failed\"\n",
    "mdl.report()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Step 6: Investigate the solution and then run an example analysis\n",
    "\n",
    "We take advantage of *pandas* to analyze the results. First we store the solution values of the assignment variables into a new *pandas* Series.\n",
    "\n",
    "Calling `solution_value` on a DOcplex variable returns its value in the solution (provided the model has been successfully solved)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>all_shifts</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>31</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>40</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>all_nurses</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Anne</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bethanie</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Betsy</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cathy</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cecilia</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "all_shifts   0    1    2    3    4    5    6    7    8    9  ...    31   32  \\\n",
       "all_nurses                                                   ...              \n",
       "Anne        0.0  0.0  0.0  0.0  1.0  0.0  0.0  1.0  0.0  0.0 ...   0.0  0.0   \n",
       "Bethanie    1.0  0.0  0.0  0.0  1.0  0.0  0.0  1.0  0.0  0.0 ...   1.0  0.0   \n",
       "Betsy       0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  1.0  0.0 ...   0.0  1.0   \n",
       "Cathy       0.0  0.0  0.0  1.0  1.0  0.0  0.0  1.0  0.0  0.0 ...   0.0  1.0   \n",
       "Cecilia     0.0  0.0  0.0  0.0  0.0  0.0  1.0  0.0  0.0  0.0 ...   0.0  0.0   \n",
       "\n",
       "all_shifts   33   34   35   36   37   38   39   40  \n",
       "all_nurses                                          \n",
       "Anne        0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  \n",
       "Bethanie    1.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  \n",
       "Betsy       0.0  0.0  1.0  0.0  0.0  0.0  0.0  0.0  \n",
       "Cathy       1.0  0.0  0.0  1.0  0.0  0.0  0.0  1.0  \n",
       "Cecilia     0.0  0.0  1.0  0.0  0.0  0.0  0.0  0.0  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a pandas Series containing actual shift assignment decision variables value\n",
    "s_assigned = df_assigned.assigned.apply(lambda v: v.solution_value)\n",
    "\n",
    "# Create a pivot table by (nurses, shifts), using pandas' \"unstack\" method to transform the 'all_shifts' row index\n",
    "#  into columns\n",
    "df_res = s_assigned.unstack(level='all_shifts')\n",
    "\n",
    "# Display the first few rows of the resulting pivot table\n",
    "df_res.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Analyzing how worktime is distributed\n",
    "\n",
    "Let's analyze how worktime is distributed among nurses. \n",
    "\n",
    "First, we compute the global average work time as the total minimum requirement in hours, divided by number of nurses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "* theoretical average work time is 39 h\n"
     ]
    }
   ],
   "source": [
    "s_demand  = df_shifts.min_req * df_shifts.duration\n",
    "total_demand = s_demand.sum()\n",
    "avg_worktime = total_demand / float(len(all_nurses))\n",
    "print(\"* theoretical average work time is {0:g} h\".format(avg_worktime))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Let's analyze the series of deviations to the average, stored in a *pandas* Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "* the sum of absolute deviations from mean is 58.0\n"
     ]
    }
   ],
   "source": [
    "# a pandas series of worktimes solution values\n",
    "s_worktime = df_nurses.worktime.apply(lambda v: v.solution_value)\n",
    "\n",
    "# returns a new series computed as deviation from average\n",
    "s_to_mean = s_worktime - avg_worktime\n",
    "\n",
    "# take the absolute value\n",
    "s_abs_to_mean = s_to_mean.apply(abs)\n",
    "\n",
    "\n",
    "total_to_mean = s_abs_to_mean.sum()\n",
    "print(\"* the sum of absolute deviations from mean is {}\".format(total_to_mean))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "To see how work time is distributed among nurses, print a histogram of work time values.\n",
    "Note that, as all time data are integers, work times in the solution can take only integer values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.text.Text at 0x7faf20409310>"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAEKCAYAAAAfGVI8AAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAE1dJREFUeJzt3X2QZXV95/H3hwcVRQVq2smsYEZdQ0K5OmBDZZdljbqm\niEkEqYQNZcyYUE4eDUTXFdmUun+kCh9JspWkMgYqkwTNoqIQF5MMhghmETKDICCwuHHcAAMzGl0g\nGgjDd/+4p/Vm6IfbA+ee7v69X1Vdfc65D+czPXP6M+fh/k6qCklSuw4aOoAkaVgWgSQ1ziKQpMZZ\nBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxh/T1xkmeBlwDPLVbz8eq6l1JjgL+B7AR2AWcWVXf\nWOy91q1bVxs3buwrqiStSTt37vxaVc0s9bz0NcREkgDPqKqHkhwKfA44BzgD+IequiDJecCRVfX2\nxd5rdna2duzY0UtOSVqrkuysqtmlntfboaEaeaibPbT7KuA0YFu3fBtwel8ZJElL6/UcQZKDk9wE\n7AG2V9X1wPqq2t095T5gfZ8ZJEmL67UIqmpfVW0CjgZOSvLi/R4vRnsJj5NkS5IdSXbs3bu3z5iS\n1LSpXDVUVd8ErgZOBe5PsgGg+75ngddsrarZqpqdmVnyXIck6QD1VgRJZpIc0U0fBrwauAO4Atjc\nPW0zcHlfGSRJS+vt8lFgA7AtycGMCufSqvpUkuuAS5OcDXwVOLPHDJKkJfRWBFX1ReD4eZZ/HXhV\nX+uVJC2PnyyWpMZZBJLUuD7PEUjSmnDZnbuXflJPzjh2Q+/rcI9AkhpnEUhS4ywCSWqcRSBJjbMI\nJKlxFoEkNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CS\nGmcRSFLjLAJJapxFIEmNswgkqXEWgSQ1ziKQpMZZBJLUuN6KIMkxSa5O8qUktyU5p1v+7iT3JLmp\n+3pNXxkkSUs7pMf3fhR4a1XdmOSZwM4k27vHLqyq9/e4bknShHorgqraDezuph9Mcjvw3L7WJ0k6\nMFM5R5BkI3A8cH236M1Jvpjk4iRHLvCaLUl2JNmxd+/eacSUpCb1XgRJDgc+DpxbVQ8Avwe8ANjE\naI/hA/O9rqq2VtVsVc3OzMz0HVOSmtVrESQ5lFEJXFJVlwFU1f1Vta+qHgM+BJzUZwZJ0uL6vGoo\nwEXA7VX1wbHlG8ae9jrg1r4ySJKW1udVQycDbwBuSXJTt+x84Kwkm4ACdgE/32MGSdIS+rxq6HNA\n5nnoyr7WKUlaPj9ZLEmNswgkqXEWgSQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlx\nFoEkNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcR\nSFLjLAJJapxFIEmNswgkqXG9FUGSY5JcneRLSW5Lck63/Kgk25Pc1X0/sq8MkqSl9blH8Cjw1qo6\nDvhB4JeTHAecB3ymql4EfKablyQNpLciqKrdVXVjN/0gcDvwXOA0YFv3tG3A6X1lkCQtbSrnCJJs\nBI4HrgfWV9Xu7qH7gPXTyCBJml/vRZDkcODjwLlV9cD4Y1VVQC3wui1JdiTZsXfv3r5jSlKzei2C\nJIcyKoFLquqybvH9STZ0j28A9sz32qraWlWzVTU7MzPTZ0xJalqfVw0FuAi4vao+OPbQFcDmbnoz\ncHlfGSRJSzukx/c+GXgDcEuSm7pl5wMXAJcmORv4KnBmjxkkSUvorQiq6nNAFnj4VX2tV5K0PH6y\nWJIaZxFIUuMsAklqnEUgSY2zCCSpcRMVQZJ/03cQSdIwJt0j+N0kNyT5pSTP7jWRJGmqJiqCqjoF\neD1wDLAzyYeTvLrXZJKkqZj4HEFV3QX8OvB24OXAbye5I8kZfYWTJPVv0nMEL0lyIaN7CrwS+PGq\n+oFu+sIe80mSejbpEBP/HfgD4Pyq+vbcwqq6N8mv95JMkjQVkxbBjwLfrqp9AEkOAp5WVd+qqj/u\nLZ0kqXeTniO4CjhsbP7p3TJJ0io3aRE8raoempvppp/eTyRJ0jRNWgT/mOSEuZkkLwO+vcjzJUmr\nxKTnCM4FPprkXkb3GPge4D/1lkqSNDUTFUFV/W2S7weO7RbdWVX/3F8sSdK0LOcOZScCG7vXnJCE\nqvqjXlJJkqZmoiJI8sfAC4GbgH3d4gIsAkla5SbdI5gFjquq6jOMJGn6Jr1q6FZGJ4glSWvMpHsE\n64AvJbkBeHhuYVW9tpdUkqSpmbQI3t1nCEnScCa9fPSzSb4XeFFVXZXk6cDB/UaTJE3DpMNQvwn4\nGPD73aLnAp/sK5QkaXomPVn8y8DJwAPwnZvUPKevUJKk6Zm0CB6uqkfmZpIcwuhzBJKkVW7SIvhs\nkvOBw7p7FX8U+LPFXpDk4iR7ktw6tuzdSe5JclP39ZoDjy5JejJMWgTnAXuBW4CfB65kdP/ixfwh\ncOo8yy+sqk3d15WTBpUk9WPSq4YeAz7UfU2kqq5JsvHAYkmSpmXSsYa+wjznBKrqBQewzjcn+Rlg\nB/DWqvrGAbyHJOlJMumhoVlGo4+eCJwC/DbwJwewvt8DXgBsAnYDH1joiUm2JNmRZMfevXsPYFWS\npElMVARV9fWxr3uq6jcZ3dB+Warq/qraN3ao6aRFnru1qmaranZmZma5q5IkTWjSQ0MnjM0exGgP\nYTn3Mph7nw1VtbubfR2jwewkSQOa9Jf5+CGcR4FdwJmLvSDJR4AfAtYluRt4F/BDSTYxOt+wi9EV\nSJKkAU161dArlvvGVXXWPIsvWu77SJL6Nemhobcs9nhVffDJiSNJmrbl3KHsROCKbv7HgRuAu/oI\nJUmankmL4GjghKp6EEZDRQD/s6p+uq9gkqTpmPRzBOuBR8bmH+mWSZJWuUn3CP4IuCHJJ7r504Ft\n/USSJE3TpFcN/UaSTzP6VDHAz1bVF/qLJUmalkkPDQE8HXigqn4LuDvJ83vKJEmaoklvVfku4O3A\nO7pFh3JgYw1JklaYSfcIXge8FvhHgKq6F3hmX6EkSdMzaRE8UlVFNxR1kmf0F0mSNE2TFsGlSX4f\nOCLJm4CrWMZNaiRJK9ekVw29v7tX8QPAscA7q2p7r8kkSVOxZBEkORi4qht4zl/+krTGLHloqKr2\nAY8lefYU8kiSpmzSTxY/BNySZDvdlUMAVfWrvaSSJE3NpEVwWfclSVpjFi2CJM+rqv9bVY4rJElr\n1FLnCD45N5Hk4z1nkSQNYKkiyNj0C/oMIkkaxlJFUAtMS5LWiKVOFr80yQOM9gwO66bp5quqntVr\nOklS7xYtgqo6eFpBJEnDWM79CCRJa5BFIEmNswgkqXEWgSQ1ziKQpMb1VgRJLk6yJ8mtY8uOSrI9\nyV3d9yP7Wr8kaTJ97hH8IXDqfsvOAz5TVS8CPtPNS5IG1FsRVNU1wD/st/g0YG4Au23A6X2tX5I0\nmWmfI1hfVbu76fuA9Qs9McmWJDuS7Ni7d+900klSgwY7WVxVxSLjF1XV1qqararZmZmZKSaTpLZM\nuwjuT7IBoPu+Z8rrlyTtZ9pFcAWwuZveDFw+5fVLkvbT5+WjHwGuA45NcneSs4ELgFcnuQv4j928\nJGlAk96zeNmq6qwFHnpVX+uUJC2fnyyWpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEk\nNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLj\nLAJJapxFIEmNswgkqXEWgSQ1ziKQpMZZBJLUuEOGWGmSXcCDwD7g0aqaHSKHJGmgIui8oqq+NuD6\nJUl4aEiSmjdUERRwVZKdSbYMlEGSxHCHhv59Vd2T5DnA9iR3VNU140/oCmILwPOe97whMkpSEwbZ\nI6iqe7rve4BPACfN85ytVTVbVbMzMzPTjihJzZh6ESR5RpJnzk0DPwzcOu0ckqSRIQ4NrQc+kWRu\n/R+uqj8fIIckiQGKoKr+DnjptNcrSZqfl49KUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4\ni0CSGmcRSFLjLAJJapxFIEmNswgkqXEWgSQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMI\nJKlxFoEkNc4ikKTGWQSS1DiLQJIad8jQAfp22Z27h44wdWccu2HoCM0Y8t+Xf896srhHIEmNswgk\nqXGDFEGSU5PcmeTLSc4bIoMkaWTqRZDkYOB3gB8BjgPOSnLctHNIkkaG2CM4CfhyVf1dVT0C/Clw\n2gA5JEkMUwTPBf5+bP7ubpkkaQAr9vLRJFuALd3sQ0nuPMC3Wgd87clJNRWrKe9qygqrK+9qygqr\nK+9qygpPLO/3TvKkIYrgHuCYsfmju2X/QlVtBbY+0ZUl2VFVs0/0faZlNeVdTVlhdeVdTVlhdeVd\nTVlhOnmHODT0t8CLkjw/yVOAnwKuGCCHJIkB9giq6tEkvwL8BXAwcHFV3TbtHJKkkUHOEVTVlcCV\nU1rdEz68NGWrKe9qygqrK+9qygqrK+9qygpTyJuq6nsdkqQVzCEmJKlxa6oIkhyT5OokX0pyW5Jz\nxh57c5I7uuXvHTJnl2ferEk2Jfl8kpuS7Ehy0grI+rQkNyS5ucv637rlRyXZnuSu7vuRQ2eFRfO+\nr/s38MUkn0hyxErNOvb4W5NUknVDZRy3WN4VuI0t9O9gxW1j45IcnOQLST7Vzfe/nVXVmvkCNgAn\ndNPPBP43o2EsXgFcBTy1e+w5KzjrXwI/0i1/DfDXKyBrgMO76UOB64EfBN4LnNctPw94z9BZl8j7\nw8Ah3fL3rIS8C2Xt5o9hdFHFV4F1Q2dd4me7ErexhbKuuG1sv9xvAT4MfKqb7307W1N7BFW1u6pu\n7KYfBG5n9KnlXwQuqKqHu8f2DJdyZJGsBTyre9qzgXuHSfhdNfJQN3to91WMhgbZ1i3fBpw+QLzH\nWShvVf1lVT3aLf88o8+wDGqRny3AhcB/GZsf3CJ5V+I2tlDWFbeNzUlyNPCjwB+MLe59O1tTRTAu\nyUbgeEb/C/g+4JQk1yf5bJITh8y2v/2yngu8L8nfA+8H3jFcsu/qdldvAvYA26vqemB9Vc3dmeU+\nYP1gAfezQN5xPwd8evrJHm++rElOA+6pqpsHjvc4C/xsV+Q2tkDWFbmNdX6TUfk/Nras9+1sTRZB\nksOBjwPnVtUDjC6TPYrRbuHbgEuTZMCI3zFP1l8Efq2qjgF+DbhoyHxzqmpfVW1i9L/ok5K8eL/H\n5/6ntSIsljfJfwUeBS4ZKt+4ebK+BDgfeOewyea3wM92RW5jC2RdkdtYkh8D9lTVzoWe09d2tuaK\nIMmhjH6xXlJVl3WL7wYu63YVb2DUtoOffFsg62ZgbvqjjEZrXTGq6pvA1cCpwP1JNgB03wc/HLC/\n/fKS5I3AjwGv7zaqFWMs62nA84Gbk+xi9EvsxiTfM2C8x9nvZ7sit7E5+2VdqdvYycBru7/zPwVe\nmeRPmMJ2tqaKoPsfyEXA7VX1wbGHPsnoZBZJvg94CgMPOrVI1nuBl3fTrwTumna2/SWZmbvCJslh\nwKuBOxgNDbK5e9pm4PJhEv5LC+VNciqj3e7XVtW3hsw4Z4GsX6iq51TVxqrayOiX7AlVdd+AUYFF\n/y2sxG1soawrbhsDqKp3VNXR3d/5TwF/VVU/zRS2sxU7+ugBOhl4A3BLd1wQRrvYFwMXJ7kVeATY\nvAL+N7hQ1jcBv5XkEOCf+O4IrEPaAGzL6KZCBwGXVtWnklzH6BDA2YyubDlzyJBjFsr7ZeCpwPbu\nqMXnq+oXBswJC2QdONNiFvrZPoWVt40tlPWbrLxtbDEX0PN25ieLJalxa+rQkCRp+SwCSWqcRSBJ\njbMIJKlxFoEkNc4ikJYhya79RwJNckSSXxqb/1dJPjb9dNKBsQikCXXXo8/nCOA7RVBV91bVT0wn\nlfTEWQRqQpK3JfnVbvrCJH/VTb8yySVJzkpyS5Jbk7xn7HUPJflAkpuBfzu2/LAkn07yJkYf+Hlh\nN779+5Js7D5YRZI3JvlkN478riS/kuQtGY03//kkR3XPe2GSP0+yM8m1Sb5/ij8eNc4iUCuuBU7p\npmeBw7uxnk5hdC+I9zAabmATcGKSuaF+nwFcX1UvrarPdcsOB/4M+EhVfYjRGPH/p6o2VdXb5ln3\ni4EzgBOB3wC+VVXHA9cBP9M9Zyvw5qp6GfCfgd99kv7c0pLW2hAT0kJ2Ai9L8izgYeBGRoVwCqNf\n6n9dVXsBklwC/AdG4+fsYzQw4LjLgfdW1aSjl17d3XPiwST/r1sfwC3AS7oRaP8d8NGxATufuvw/\nonRgLAI1oar+OclXgDcC/wv4IqNB0v41sAt42QIv/aeq2rffsr8BTk3y4QnH03l4bPqxsfnHGG2D\nBwHf7IZLlqbOQ0NqybWMDrtc003/AvAF4Abg5UnWdSeEzwI+u8j7vBP4BvA73fyDjG43ekC6+1B8\nJclPwmhk2iQvPdD3k5bLIlBLrmU0IuV1VXU/o5Enr+3u/nQeo/HqbwZ2VtVSQ/2eAxyW5L1V9XXg\nb7oTze87wGyvB87uTkrfxuieBNJUOPqoJDXOPQJJapxFIEmNswgkqXEWgSQ1ziKQpMZZBJLUOItA\nkhpnEUhS4/4/2S2Zin9So1QAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7faf2069ed10>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "# we can also plot as a histogram the distribution of worktimes\n",
    "s_worktime.plot.hist(color='LightBlue')\n",
    "plt.xlabel(\"worktime\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### How shifts are distributed\n",
    "\n",
    "Let's now analyze the solution from the _number of shifts_ perspective.\n",
    "How many shifts does each nurse work? Are these shifts fairly distributed amongst nurses?\n",
    "\n",
    "We compute a new column in our result DataFrame for the number of shifts worked,\n",
    "by summing rows (the *\"axis=1\"* argument in the *sum()* call indicates to *pandas* that each sum is performed by row instead of column):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.text.Text at 0x7faf16a53890>"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYUAAAD8CAYAAACYebj1AAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAEahJREFUeJzt3X2QXXV9x/H3hwSqPAk0K1VgDTqaFBkdnKWD4AMPSimi\noQ4qTHEQGdOximgtFNHKtH8x1aHqtGMJEKEV49iIioo8iEp0SsHwoAQSi4MYgkCCVGGoA1K+/ePe\nnCxLdvdudu89m837NbOz9/zuOef3nTMsn5xzfud3UlVIkgSwU9sFSJJmD0NBktQwFCRJDUNBktQw\nFCRJDUNBktQwFCRJDUNBktQwFCRJjfltF9CLBQsW1MKFC9suQ5K2K7feeusjVTU0lW22i1BYuHAh\nq1evbrsMSdquJPnlVLfx8pEkqWEoSJIahoIkqWEoSJIahoIkqdG3UEiyPMnGJGvGtJ+ZZF2Su5L8\nY7/6lyRNXT/PFC4DjhvdkOQoYAnw6qp6JfDpPvYvSZqivoVCVa0CHh3T/H7ggqp6srvOxn71L0ma\nukHfU3gF8PokNye5McmhA+5fkjSBQT/RPB/YBzgMOBT4SpKXVlWNXTHJUmApwPDw8ECLlDSBdelt\nvcXP+bPWdmDQZwobgCur4xbgGWDB1lasqmVVNVJVI0NDU5q6Q5K0jQYdCl8HjgJI8gpgF+CRAdcg\nSRpH3y4fJVkBHAksSLIBOB9YDizvDlN9Cjhta5eOJEnt6FsoVNUp43x1ar/6lCRNj080S5IahoIk\nqWEoSJIahoIkqWEoSJIahoIkqWEoSJIahoIkqWEoSJIahoIkqWEoSJIahoIkqWEoSJIahoIkqWEo\nSJIahoIkqWEoSJIafQuFJMuTbOy+enPsdx9NUkkW9Kt/SdLU9fNM4TLguLGNSQ4AjgXW97FvSdI2\n6FsoVNUq4NGtfPVPwDlA9atvSdK2Geg9hSRLgAeq6ieD7FeS1Jv5g+ooya7AeXQuHfWy/lJgKcDw\n8HAfK5MkbTbIM4WXAQcCP0lyH7A/cFuSP9raylW1rKpGqmpkaGhogGVK0o5rYGcKVXUn8MLNy91g\nGKmqRwZVgyRpYv0ckroCuAlYlGRDkjP61ZckaWb07Uyhqk6Z5PuF/epbkrRtfKJZktQwFCRJDUNB\nktQwFCRJDUNBktQwFCRJDUNBktQwFCRJDUNBktQwFCRJDUNBktQwFCRJDUNBktQwFCRJDUNBktQw\nFCRJDUNBktTo5+s4lyfZmGTNqLZPJVmX5KdJvpZkr371L0maun6eKVwGHDem7Xrg4Kp6FfDfwMf6\n2L8kaYr6FgpVtQp4dEzbdVX1dHfxv4D9+9W/JGnq2ryn8F7gO+N9mWRpktVJVm/atGmAZUnSjquV\nUEjyceBp4Irx1qmqZVU1UlUjQ0NDgytOknZg8wfdYZL3ACcAx1RVDbp/SdL4BhoKSY4DzgHeWFX/\nO8i+JUmT6+eQ1BXATcCiJBuSnAH8M7AHcH2SO5L8a7/6lyRNXd/OFKrqlK00X9qv/iRJ0+cTzZKk\nhqEgSWoYCpKkhqEgSWoYCpKkxrijj5K8faINq+rKmS9HktSmiYakvrX7+4XA4cD3ustHAf8JGAqS\nNMeMGwpVdTpAkuuAg6rqwe7yi+hMiy1JmmN6uadwwOZA6HoYGO5TPZKkFvXyRPMNSa4FVnSX3wV8\nt38lSZLaMmkoVNUHk/w58IZu07Kq+lp/y5IktaHXuY9uAx6vqu8m2TXJHlX1eD8LkyQN3qT3FJK8\nD1gJXNRt2g/4ej+LkiS1o5cbzR8AjgAeA6iqe+gMU5UkzTG9hMKTVfXU5oUk8wHfmCZJc1AvoXBj\nkvOA5yd5M/AfwDf7W5YkqQ29hMK5wCbgTuAvgaur6uN9rUqS1IpeQuGQqrq4qt5RVSdV1cVJTphs\noyTLk2xMsmZU2z5Jrk9yT/f33tOqXpI0o3oJhYuTHLx5IckpwN/1sN1lwHFj2s4FbqiqlwM3dJcl\nSbNEL6FwEvBvSRZ3h6f+FXDsZBtV1Srg0THNS4DLu58vB06cQq2SpD7r5Ynme5OcTOfZhPXAsVX1\nu23sb99R8yg9BOw73opJlgJLAYaHnWpJkgZhovcp3Mmzh57uA8wDbk5CVb1qOh1XVSUZd2hrVS0D\nlgGMjIw4BFaSBmCiM4VJbyZvg4eTvKiqHuxOwb2xD31IkrbRRO9T+GWSecBdVbV4hvq7CjgNuKD7\n+xsztF9J0gyY8EZzVf0f8LMkU76on2QFcBOwKMmGJGfQCYM3J7kHeFN3WZI0S/QyS+rewF1JbgGe\n2NxYVW+baKOqOmWcr47pvTxJ0iD1Egq9PJMgSZoDehmSemOSfYFDu023VJU3iCVpDurlfQrvBG4B\n3gG8k86Q1JP6XZgkafB6uXz0ceDQzWcHSYbovKN5ZT8LkyQNXi/TXOw05nLRr3vcTpK0nenlTOGa\nJNcCK7rL7wKu7l9JkqS29HKj+ewkbwde121aVlVf629ZkqQ2TBoK3YfOVlXVlQOoR5LUol4uHw0D\nFyVZCNwKrAJ+WFV39LEuSVILJr1hXFXnV9XRwCuBHwJn0wkHSdIc08vlo08ARwC7A7cDf0MnHCRJ\nc0wvl4/eDjwNfBu4Ebipqp7sa1WSpFb0cvnoNXRmNL0FeDNwZ5If9bswSdLg9XL56GDg9cAbgRHg\nfrx8JElzUi+Xjy6gM+Loc8CPq+r3/S1JktSWXh5e68drOSVJs5BzGEmSGq2EQpKPJLkryZokK5I8\nr406JEnPNqVQSLJTkj2n02GS/YAPASNVdTAwDzh5OvuUJM2MXl6y86UkeybZDVgD3J3k7Gn2Ox94\nfpL5wK7Ar6a5P0nSDOhl9NFBVfVYkr8AvgOcS2eai09tS4dV9UCSTwPrgd8B11XVdWPXS7IUWAow\nPDy8LV1JmkvWpbf1Fld/65jjerl8tHOSnYETgaumOyQ1yd7AEuBA4MXAbklOHbteVS2rqpGqGhka\nGppOl5KkHvUSChcB9wG7AauSvAT47TT6fBPwi6ra1A2YK4HDp7E/SdIM6SUUvllV+1XV8VVVdC77\nvHcafa4HDkuya5IAxwBrp7E/SdIM6SUUvjp6oRsMX97WDqvqZmAlcBtwZ7eGZdu6P0nSzBn3RnOS\nxXTeofCC7us4N9sTmNZzBVV1PnD+dPYhSZp5E40+WgScAOwFvHVU++PA+/pZlCSpHROFwuFVdXqS\nT1bVPwysIklSaya6p3B890bwiYMqRpLUronOFK4B/gfYPcljo9pD537ztKa7kCTNPuOeKVTV2VW1\nF/Dtqtpz1M8eBoIkzU29vI5zySAKkSS1b9xQ2Pwe5iSPJ3ls7O/BlShJGpRx7ylU1eu6v/cYXDmS\npDb1MksqSeYB+45ev6rW96soSVI7Jg2FJGfSefr4YeCZbnMBr+pjXZKkFvRypnAWsKiqft3vYiRJ\n7eplQrz7md5U2ZKk7cREE+L9dffjvcAPknwbeHLz91V1YZ9rkyQN2ESXjzaPOlrf/dml+yNJmqMm\nGpL692PbkuwE7F5VPqcgSXPQpPcUknwpyZ5JdgPWAHcnObv/pUmSBq2XG80Hdc8MTgS+AxwIvHs6\nnSbZK8nKJOuSrE3y2unsT5I0M3oJhZ2T7EwnFK6qqt/TeU5hOj4LXFNVi4FX4zuaJWlW6CUULgLu\nA3YDViV5CbDN9xSSvAB4A3ApQFU9VVW/2db9SZJmTi+zpH6uqvarquOrquiMRDpqGn0eCGwCvpDk\n9iSXdO9XSJJa1suN5k+M+vwH1fH0NPqcD7wG+HxVHQI8AZy7lX6XJlmdZPWmTZum0Z0kqVcTTZ39\nt90bwCeNar5pBvrcAGyoqpu7yyvphMSzVNWyqhqpqpGhoaEZ6FaSNJmJzhTWAe8AXprkh0kuBv4w\nyaLpdFhVDwH3j9rPMcDd09mnJGlmTPRE82+A84Ajuz9/DBwLnJtkUVUdPo1+zwSuSLILnWk0Tp/G\nviRJM2SiUPhT4JPAy4ALgZ8CT1TVtP8HXlV3ACPT3Y8kaWaNe/moqs6rqmPoDEf9d2AeMJTkR0m+\nOaD6JEkD1Mv7FK6tqtXA6iTvr6rXJVnQ78IkSYPXy3MK54xafE+37ZF+FSRJak8vTzQ3quon/SpE\nktS+KYWCJGluMxQkSQ1DQZLUMBQkSQ1DQZLUMBQkSQ1DQZLUMBQkSQ1DQZLUMBQkSQ1DQZLUMBQk\nSQ1DQZLUaC0UksxLcnuSb7VVgyTp2do8UzgLWNti/5KkMVoJhST7A28BLmmjf0nS1rV1pvAZ4Bzg\nmZb6lyRtRS/vaJ5RSU4ANlbVrUmOnGC9pcBSgOHh4QFVJ03RuvS23uJqp99+9K05rY0zhSOAtyW5\nD/gycHSSL45dqaqWVdVIVY0MDQ0NukZJ2iENPBSq6mNVtX9VLQROBr5XVacOug5J0nP5nIIkqTHw\newqjVdUPgB+0WYMkaQvPFCRJDUNBktQwFCRJDUNBktQwFCRJDUNBktQwFCRJDUNBktQwFCRJDUNB\nktQwFCRJDUNBktQwFCRJDUNBktQwFCRJDUNBktQwFCRJjYGHQpIDknw/yd1J7kpy1qBrkCRtXRuv\n43wa+GhV3ZZkD+DWJNdX1d0t1CJJGmXgZwpV9WBV3db9/DiwFthv0HVIkp6r1XsKSRYChwA3t1mH\nJKmjtVBIsjvwVeDDVfXYVr5fmmR1ktWbNm0afIGStANqJRSS7EwnEK6oqiu3tk5VLauqkaoaGRoa\nGmyBkrSDamP0UYBLgbVVdeGg+5ckja+NM4UjgHcDRye5o/tzfAt1SJLGGPiQ1Kr6EZBB9ytJmpxP\nNEuSGoaCJKlhKEiSGoaCJKlhKEiSGoaCJKlhKEiSGoaCJKlhKEiSGoaCJKlhKEiSGoaCJKlhKEiS\nGoaCJKlhKEiSGoaCJKlhKEiSGq2EQpLjkvwsyc+TnNtGDZKk5xp4KCSZB/wL8GfAQcApSQ4adB2S\npOdq40zhT4CfV9W9VfUU8GVgSQt1SJLGaCMU9gPuH7W8odsmSWrZ/LYLGE+SpcDS7uKTSda0Wc8s\nsgB4pO0iZont6Fik3x1McCz63vc4Wut3O/rvou8WTXWDNkLhAeCAUcv7d9uepaqWAcsAkqyuqpHB\nlDe7eSy28Fhs4bHYwmOxRZLVU92mjctHPwZenuTAJLsAJwNXtVCHJGmMgZ8pVNXTST4IXAvMA5ZX\n1V2DrkOS9Fyt3FOoqquBq6ewybJ+1bId8lhs4bHYwmOxhcdiiykfi1RVPwqRJG2HnOZCktSY1aHg\ndBgdSQ5I8v0kdye5K8lZbdfUtiTzktye5Ftt19KmJHslWZlkXZK1SV7bdk1tSfKR7t/HmiQrkjyv\n7ZoGKcnyJBtHD99Psk+S65Pc0/2992T7mbWh4HQYz/I08NGqOgg4DPjADnwsNjsLWNt2EbPAZ4Fr\nqmox8Gp20GOSZD/gQ8BIVR1MZxDLye1WNXCXAceNaTsXuKGqXg7c0F2e0KwNBZwOo1FVD1bVbd3P\nj9P5w99hnwJPsj/wFuCStmtpU5IXAG8ALgWoqqeq6jftVtWq+cDzk8wHdgV+1XI9A1VVq4BHxzQv\nAS7vfr4cOHGy/czmUHA6jK1IshA4BLi53Upa9RngHOCZtgtp2YHAJuAL3UtplyTZre2i2lBVDwCf\nBtYDDwK/rarr2q1qVti3qh7sfn4I2HeyDWZzKGiMJLsDXwU+XFWPtV1PG5KcAGysqlvbrmUWmA+8\nBvh8VR0CPEEPlwfmou618iV0gvLFwG5JTm23qtmlOkNNJx1uOptDoafpMHYUSXamEwhXVNWVbdfT\noiOAtyW5j84lxaOTfLHdklqzAdhQVZvPGlfSCYkd0ZuAX1TVpqr6PXAlcHjLNc0GDyd5EUD398bJ\nNpjNoeB0GF1JQue68dqqurDtetpUVR+rqv2raiGd/ya+V1U75L8Iq+oh4P4kmyc9Owa4u8WS2rQe\nOCzJrt2/l2PYQW+6j3EVcFr382nANybbYNbOkup0GM9yBPBu4M4kd3Tbzus+Ga4d25nAFd1/ON0L\nnN5yPa2oqpuTrARuozNa73Z2sCebk6wAjgQWJNkAnA9cAHwlyRnAL4F3Trofn2iWJG02my8fSZIG\nzFCQJDUMBUlSw1CQJDUMBUlSw1CQJDUMBUlSw1CQJDX+H0rPp9vYogkaAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7faf202f5ad0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# a pandas series of #shifts worked\n",
    "df_worked = df_res[all_shifts].sum(axis=1)\n",
    "df_res[\"worked\"] = df_worked\n",
    "\n",
    "df_worked.plot.hist(color=\"gold\", xlim=(0,10))\n",
    "plt.ylabel(\"#shifts worked\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We see that one nurse works significantly fewer shifts than others do. What is the average number of shifts worked by a nurse? This is equal to the total demand divided by the number of nurses.\n",
    "\n",
    "Of course, this yields a fractional number of shifts that is not practical, but nonetheless will help us quantify\n",
    "the _fairness_ in shift distribution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-- expected avg #shifts worked is 6.875\n",
      "-- total absolute deviation to mean #shifts is 22.0\n"
     ]
    }
   ],
   "source": [
    "avg_worked = df_shifts[\"min_req\"].sum() / float(len(all_nurses))\n",
    "print(\"-- expected avg #shifts worked is {}\".format(avg_worked))\n",
    "\n",
    "worked_to_avg = df_res[\"worked\"] - avg_worked\n",
    "total_to_mean = worked_to_avg.apply(abs).sum()\n",
    "print(\"-- total absolute deviation to mean #shifts is {}\".format(total_to_mean))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Introducing a fairness goal\n",
    "\n",
    "As the above diagram suggests, the distribution of shifts could be improved.\n",
    "We implement this by adding one extra objective, _fairness_, which balances\n",
    "the shifts assigned over nurses.\n",
    "\n",
    "Note that we can edit the model, that  is add (or remove) constraints, even after it has been solved. \n",
    "\n",
    "### Step #1 : Introduce three new variables per nurse to model the \n",
    "number of shifts worked and positive and negative deviations to the average."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# add two extra variables per nurse: deviations above and below average\n",
    "df_nurses[\"worked\"]      = df_nurses.apply(lambda r: make_var(r, \"worked%s\"), axis=1)\n",
    "df_nurses[\"overworked\"]  = df_nurses.apply(lambda r: make_var(r, \"overw_%s\"), axis=1)\n",
    "df_nurses[\"underworked\"] = df_nurses.apply(lambda r: make_var(r, \"underw_%s\"), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Step #2 : Post the constraint that links these variables together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Use the pandas groupby operation to enforce the constraint calculating number of worked shifts for each nurse\n",
    "for nurse, nurse_assignments in df_assigned.groupby(level='all_nurses'):\n",
    "    # nb of worked shifts is sum of assigned shifts\n",
    "    mdl.add_constraint(df_nurses.worked[nurse] == mdl.sum(nurse_assignments.assigned))\n",
    "\n",
    "for nurse in df_nurses.itertuples():\n",
    "    # nb worked is average + over - under\n",
    "    mdl.add_constraint(nurse.worked == avg_worked + nurse.overworked - nurse.underworked)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Step #3 : Define KPIs to measure the result after solve."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DecisionKPI(name=Total under-worked,expr=underw_Anne+underw_Bethanie+underw_Betsy+underw_Cathy+underw_Cec..)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# finally, define kpis for over and under average quantities\n",
    "total_overw = mdl.sum(df_nurses[\"overworked\"])\n",
    "mdl.add_kpi(total_overw, \"Total over-worked\")\n",
    "total_underw = mdl.sum(df_nurses[\"underworked\"])\n",
    "mdl.add_kpi(total_underw, \"Total under-worked\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Finally, let's modify the objective by adding the sum of `over_worked and under_worked` to the previous objective.\n",
    "\n",
    "**Note:** The definitions of `over_worked` and `under_worked` as described above are not sufficient to give them an unambiguous value. However, as all these variables are minimized, CPLEX ensures that these variables take the minimum possible values in the solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "mdl.minimize(total_salary_cost + total_overw + total_underw)  # incorporate over_worked and under_worked in objective"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Our modified model is ready to solve. Again, enter your DOcplexcloud `url` and `api_key` in the appropriate fields below to solve on DOcplexcloud if you do not have a local CPLEX installation.\n",
    "\n",
    "The `log_output=True` parameter tells CPLEX to print the log on the standard output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2018-05-03T19:03:39Z, INFO] CPLEX version 12070100\n",
      "[2018-05-03T19:03:39Z, INFO] Parameter file:\n",
      "[2018-05-03T19:03:39Z, INFO] # -- This content is generated by DOcplex\n",
      "[2018-05-03T19:03:39Z, INFO] CPLEX Parameter File Version 12.8.0.0\n",
      "[2018-05-03T19:03:39Z, INFO] CPX_PARAM_EPGAP                  0.00001000000000\n",
      "[2018-05-03T19:03:39Z, INFO] # --- end of generated prm data ---\n",
      "[2018-05-03T19:03:39Z, WARN] Warning:  CPLEX version found in file python_nurses.prm (12.8.0.0) does not match\n",
      "[2018-05-03T19:03:39Z, WARN]           this CPLEX version (12.7.1.0).\n",
      "[2018-05-03T19:03:39Z, WARN] Changed parameter CPX_PARAM_THREADS from 0 to 10\n",
      "[2018-05-03T19:03:39Z, INFO] Param[1,067] = 10\n",
      "[2018-05-03T19:03:39Z, INFO] Param[1,130] = utf-8\n",
      "[2018-05-03T19:03:39Z, INFO] Param[1,132] = -1\n",
      "[2018-05-03T19:03:39Z, INFO] Param[2,009] = 0.000010000000000000000818030539140313095458623138256371021270751953125\n",
      "[2018-05-03T19:03:39Z, INFO] CPXPARAM_WorkDir                                 \"/var/job_processor/work/tmp/java/job523843052845228505.tmp\"\n",
      "[2018-05-03T19:03:39Z, INFO] CPXPARAM_Threads                                 10\n",
      "[2018-05-03T19:03:39Z, INFO] CPXPARAM_Read_APIEncoding                        \"utf-8\"\n",
      "[2018-05-03T19:03:39Z, INFO] CPXPARAM_Output_CloneLog                         -1\n",
      "[2018-05-03T19:03:39Z, INFO] CPXPARAM_MIP_Tolerances_MIPGap                   1.0000000000000001e-05\n",
      "[2018-05-03T19:03:39Z, INFO] CPXPARAM_MIP_Strategy_CallbackReducedLP          0\n",
      "[2018-05-03T19:03:39Z, INFO] CPXPARAM_Output_IntSolFilePrefix                 \"/var/job_processor/work/tmp/java/job523843052845228505.tmp/sol\"\n",
      "[2018-05-03T19:03:39Z, INFO] Tried aggregator 2 times.\n",
      "[2018-05-03T19:03:39Z, INFO] MIP Presolve eliminated 997 rows and 379 columns.\n",
      "[2018-05-03T19:03:39Z, INFO] MIP Presolve modified 90 coefficients.\n",
      "[2018-05-03T19:03:39Z, INFO] Aggregator did 73 substitutions.\n",
      "[2018-05-03T19:03:39Z, INFO] Reduced MIP has 582 rows, 986 columns, and 3859 nonzeros.\n",
      "[2018-05-03T19:03:39Z, INFO] Reduced MIP has 892 binaries, 0 generals, 0 SOSs, and 0 indicators.\n",
      "[2018-05-03T19:03:39Z, INFO] Presolve time = 0.01 sec. (4.07 ticks)\n",
      "[2018-05-03T19:03:39Z, INFO] Probing time = 0.00 sec. (0.59 ticks)\n",
      "[2018-05-03T19:03:39Z, INFO] Tried aggregator 1 time.\n",
      "[2018-05-03T19:03:39Z, INFO] MIP Presolve eliminated 2 rows and 4 columns.\n",
      "[2018-05-03T19:03:39Z, INFO] Reduced MIP has 580 rows, 982 columns, and 3814 nonzeros.\n",
      "[2018-05-03T19:03:39Z, INFO] Reduced MIP has 892 binaries, 30 generals, 0 SOSs, and 0 indicators.\n",
      "[2018-05-03T19:03:39Z, INFO] Presolve time = 0.00 sec. (2.29 ticks)\n",
      "[2018-05-03T19:03:39Z, INFO] Probing time = 0.00 sec. (0.58 ticks)\n",
      "[2018-05-03T19:03:39Z, INFO] Clique table members: 479.\n",
      "[2018-05-03T19:03:39Z, INFO] MIP emphasis: balance optimality and feasibility.\n",
      "[2018-05-03T19:03:39Z, INFO] MIP search method: dynamic search.\n",
      "[2018-05-03T19:03:39Z, INFO] Parallel mode: deterministic, using up to 10 threads.\n",
      "[2018-05-03T19:03:39Z, INFO] Root relaxation solution time = 0.02 sec. (10.38 ticks)\n",
      "[2018-05-03T19:03:39Z, INFO] \n",
      "[2018-05-03T19:03:39Z, INFO]         Nodes                                         Cuts/\n",
      "[2018-05-03T19:03:39Z, INFO]    Node  Left     Objective  IInf  Best Integer    Best Bound    ItCnt     Gap\n",
      "[2018-05-03T19:03:39Z, INFO] \n",
      "[2018-05-03T19:03:39Z, INFO]       0     0    28827.9167    84                  28827.9167      840\n",
      "[2018-05-03T19:03:39Z, INFO]       0     0    28829.0661    76                    Cuts: 82     1018\n",
      "[2018-05-03T19:03:39Z, INFO]       0     0    28830.3437    37                   Cuts: 124     1188\n",
      "[2018-05-03T19:03:39Z, INFO]       0     0    28831.0000    27                    Cuts: 39     1258\n",
      "[2018-05-03T19:03:39Z, INFO]       0     0    28831.0000    27                    Cuts: 30     1324\n",
      "[2018-05-03T19:03:39Z, INFO] *     0+    0                        28977.0000    28831.0000             0.50%\n",
      "[2018-05-03T19:03:40Z, INFO]       0     0    28831.0000     6    28977.0000       Cuts: 9     1352    0.50%\n",
      "[2018-05-03T19:03:40Z, INFO]       0     0    28831.0000     6    28977.0000      Cuts: 14     1380    0.50%\n",
      "[2018-05-03T19:03:40Z, INFO] *     0+    0                        28919.0000    28831.0000             0.30%\n",
      "[2018-05-03T19:03:40Z, INFO] *     0+    0                        28831.0000    28831.0000             0.00%\n",
      "[2018-05-03T19:03:40Z, INFO]       0     0        cutoff          28831.0000    28831.0000     1380    0.00%\n",
      "[2018-05-03T19:03:40Z, INFO] Elapsed time = 0.53 sec. (142.24 ticks, tree = 0.01 MB, solutions = 3)\n",
      "[2018-05-03T19:03:40Z, INFO] \n",
      "[2018-05-03T19:03:40Z, INFO] GUB cover cuts applied:  32\n",
      "[2018-05-03T19:03:40Z, INFO] Cover cuts applied:  2\n",
      "[2018-05-03T19:03:40Z, INFO] Flow cuts applied:  19\n",
      "[2018-05-03T19:03:40Z, INFO] Mixed integer rounding cuts applied:  64\n",
      "[2018-05-03T19:03:40Z, INFO] Zero-half cuts applied:  29\n",
      "[2018-05-03T19:03:40Z, INFO] Lift and project cuts applied:  4\n",
      "[2018-05-03T19:03:40Z, INFO] Gomory fractional cuts applied:  7\n",
      "[2018-05-03T19:03:40Z, INFO] \n",
      "[2018-05-03T19:03:40Z, INFO] Root node processing (before b&c):\n",
      "[2018-05-03T19:03:40Z, INFO]   Real time             =    0.53 sec. (142.34 ticks)\n",
      "[2018-05-03T19:03:40Z, INFO] Parallel b&c, 10 threads:\n",
      "[2018-05-03T19:03:40Z, INFO]   Real time             =    0.00 sec. (0.00 ticks)\n",
      "[2018-05-03T19:03:40Z, INFO]   Sync time (average)   =    0.00 sec.\n",
      "[2018-05-03T19:03:40Z, INFO]   Wait time (average)   =    0.00 sec.\n",
      "[2018-05-03T19:03:40Z, INFO]                           ------------\n",
      "[2018-05-03T19:03:40Z, INFO] Total (root+branch&cut) =    0.53 sec. (142.34 ticks)\n",
      "[2018-05-03T19:03:40Z, INFO] Incumbent solution:\n",
      "[2018-05-03T19:03:40Z, INFO] MILP objective                                 2.8831000000e+04\n",
      "[2018-05-03T19:03:40Z, INFO] MILP solution norm |x| (Total, Max)            1.69500e+03  4.00000e+01\n",
      "[2018-05-03T19:03:40Z, INFO] MILP solution error (Ax=b) (Total, Max)        0.00000e+00  0.00000e+00\n",
      "[2018-05-03T19:03:40Z, INFO] MILP x bound error (Total, Max)                0.00000e+00  0.00000e+00\n",
      "[2018-05-03T19:03:40Z, INFO] MILP x integrality error (Total, Max)          0.00000e+00  0.00000e+00\n",
      "[2018-05-03T19:03:40Z, INFO] MILP slack bound error (Total, Max)            0.00000e+00  0.00000e+00\n",
      "[2018-05-03T19:03:40Z, INFO] integer optimal solution (101)\n",
      "* model nurses solved with objective = 28831.000\n",
      "*  KPI: Total salary cost  = 28824.000\n",
      "*  KPI: Total over-worked  = 3.500\n",
      "*  KPI: Total under-worked = 3.500\n"
     ]
    }
   ],
   "source": [
    "sol2 = mdl.solve(url=url, key=key, log_output=True)  # solve again and get a new solution\n",
    "assert sol2, \"Solve failed\"\n",
    "mdl.report()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Analyzing new results\n",
    "\n",
    "Let's recompute the new total deviation from average on this new solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-- total absolute deviation to mean #shifts is now 7.0 down from 22.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>all_shifts</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>34</th>\n",
       "      <th>35</th>\n",
       "      <th>36</th>\n",
       "      <th>37</th>\n",
       "      <th>38</th>\n",
       "      <th>39</th>\n",
       "      <th>40</th>\n",
       "      <th>worked</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>all_nurses</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Anne</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bethanie</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Betsy</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cathy</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cecilia</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "all_shifts    0    1    2    3    4    5    6    7    8    9   ...     32  \\\n",
       "all_nurses                                                     ...          \n",
       "Anne        0.0  0.0  1.0  0.0  0.0  0.0  1.0  0.0  0.0  0.0   ...    0.0   \n",
       "Bethanie    1.0  0.0  0.0  0.0  0.0  1.0  1.0  0.0  0.0  0.0   ...    0.0   \n",
       "Betsy       0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0   ...    0.0   \n",
       "Cathy       0.0  0.0  0.0  0.0  1.0  0.0  0.0  1.0  0.0  0.0   ...    0.0   \n",
       "Cecilia     0.0  0.0  0.0  0.0  0.0  1.0  1.0  0.0  1.0  0.0   ...    0.0   \n",
       "\n",
       "all_shifts   33   34   35   36   37   38   39   40  worked  \n",
       "all_nurses                                                  \n",
       "Anne        0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0     7.0  \n",
       "Bethanie    1.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0     7.0  \n",
       "Betsy       1.0  0.0  0.0  1.0  0.0  0.0  0.0  0.0     7.0  \n",
       "Cathy       0.0  0.0  1.0  1.0  0.0  0.0  1.0  0.0     7.0  \n",
       "Cecilia     0.0  0.0  0.0  0.0  0.0  0.0  1.0  0.0     6.0  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a pandas Series containing actual shift assignment decision variables value\n",
    "s_assigned2 = df_assigned.assigned.apply(lambda v: v.solution_value)\n",
    "\n",
    "# Create a pivot table by (nurses, shifts), using pandas' \"unstack\" method to transform the 'all_shifts' row index\n",
    "#  into columns\n",
    "df_res2 = s_assigned2.unstack(level='all_shifts')\n",
    "\n",
    "# Add a new column to the pivot table containing the #shifts worked by summing over each row\n",
    "df_res2[\"worked\"] = df_res2[all_shifts].sum(axis=1)\n",
    "\n",
    "# total absolute deviation from average is directly read on expressions\n",
    "new_total_to_mean = total_overw.solution_value + total_underw.solution_value\n",
    "print(\"-- total absolute deviation to mean #shifts is now {0} down from {1}\".format(new_total_to_mean, total_to_mean))\n",
    "\n",
    "# Display the first few rows of the result Data Frame\n",
    "df_res2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Let's print the new histogram of shifts worked."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7faf169667d0>"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAD8CAYAAAB6paOMAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAADslJREFUeJzt3W2sJnV9xvHvBUvDg6gYjlvKQxYagyVtRXqktqhVEQM+\noDaplVRDjHFNaq1Uk4rEVPuiiS9UbJvWuAIVFTGIoLSltgs1WpMW3MWtLA8Gq6gsK3usaRcsEVl+\nfXHPcY9k95xZYGbO7v/7Se6cmblndq5MTs6185yqQpLUroOmDiBJmpZFIEmNswgkqXEWgSQ1ziKQ\npMZZBJLUOItAkhpnEUhS4ywCSWrcmqkD9HH00UfXunXrpo4hSfuVzZs3/7Cq5laab78ognXr1rFp\n06apY0jSfiXJd/vM56EhSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklq3H5x\nZ7Ek/Zw7s/fvnlnj5ThAuEcgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxFIEmN\nswgkqXEWgSQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjRusCJIcn+RLSW5PcluSt3fT35dk\nW5It3edlQ2WQJK1syFdVPgy8s6puSXIksDnJxu67i6vqAwOuW5LU02BFUFXbge3d8P1J7gCOHWp9\nkqTHZpRzBEnWAc8GbuomvS3JN5JcluSoMTJIkvZs8CJI8iTgc8AFVbUT+AhwEnAqsz2GD+5lufVJ\nNiXZtLCwMHRMSWrWoEWQ5BBmJXBFVV0DUFX3VdWuqnoE+Bhw+p6WraoNVTVfVfNzc3NDxpSkpg15\n1VCAS4E7qupDS6Yfs2S21wBbh8ogSVrZkFcNnQG8Abg1yZZu2kXAeUlOBQq4G3jLgBkkSSsY8qqh\nrwLZw1fXD7VOSdK+885iSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklqnEUg\nSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxFIEmNswgkqXEWgSQ1ziKQpMZZBJLU\nOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNW6wIkhyfJIvJbk9yW1J3t5Nf1qSjUnu6n4eNVQG\nSdLKhtwjeBh4Z1WdAjwXeGuSU4ALgRur6hnAjd24JGkigxVBVW2vqlu64fuBO4BjgVcBl3ezXQ68\neqgMkqSVjXKOIMk64NnATcDaqtreffUDYO0YGSRJezZ4ESR5EvA54IKq2rn0u6oqoPay3Pokm5Js\nWlhYGDqmJDVr0CJIcgizEriiqq7pJt+X5Jju+2OAHXtatqo2VNV8Vc3Pzc0NGVOSmjbkVUMBLgXu\nqKoPLfnqOuD8bvh84AtDZZAkrWzNgP/2GcAbgFuTbOmmXQS8H7gqyZuA7wKvHTCDJGkFgxVBVX0V\nyF6+PnOo9UqS9o13FktS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklq\nXK8iSPJrQweRJE2j7x7B3ya5OckfJnnKoIkkSaPqVQRV9XzgD4Djgc1JPp3krEGTSZJG0fscQVXd\nBbwHeBfwO8BfJbkzye8OFU6SNLy+5wh+PcnFzF5A/2LglVX1K93wxQPmkyQNrO/7CP4auAS4qKoe\nXJxYVfcmec8gySRJo+hbBC8HHqyqXQBJDgIOrar/q6pPDpZOkjS4vucIbgAOWzJ+eDdNkrSf61sE\nh1bVA4sj3fDhw0SSJI2pbxH8OMlpiyNJfgN4cJn5JUn7ib7nCC4APpvkXmYvpP9F4PcHSyVJGk2v\nIqiqryV5JnByN+mbVfXT4WJJksbSd48A4DnAum6Z05JQVZ8YJJUkaTS9iiDJJ4FfBrYAu7rJBVgE\nkrSf67tHMA+cUlU1ZBhJ0vj6XjW0ldkJYknSAabvHsHRwO1JbgZ+sjixqs4dJJUkaTR9i+B9Q4aQ\nJE2n7/sIvgzcDRzSDX8NuGW5ZZJclmRHkq1Lpr0vybYkW7rPyx5HdknSE6DvY6jfDFwNfLSbdCzw\n+RUW+zhw9h6mX1xVp3af6/sGlSQNo+/J4rcCZwA74WcvqXn6cgtU1VeAHz2udJKkwfUtgp9U1UOL\nI0nWMLuP4LF4W5JvdIeOjnqM/4Yk6QnStwi+nOQi4LDuXcWfBf7+MazvI8BJwKnAduCDe5sxyfok\nm5JsWlhYeAyrkiT10bcILgQWgFuBtwDXM3t/8T6pqvuqaldVPQJ8DDh9mXk3VNV8Vc3Pzc3t66ok\nST31fejc4h/ujz2elSU5pqq2d6OvYXajmiRpQn2fNfQd9nBOoKpOWmaZK4EXAkcnuQd4L/DCJKd2\n/9bdzPYuJEkT2pdnDS06FPg94GnLLVBV5+1h8qU91ydJGknfG8r+e8lnW1V9mNkL7SVJ+7m+h4ZO\nWzJ6ELM9hH15l4EkaZXq+8d86WWeDzM7vv/aJzyNJGl0fa8aetHQQSRJ0+h7aOgdy31fVR96YuJI\nksa2L1cNPQe4rht/JXAzcNcQoSRJ4+lbBMcBp1XV/TB7nDTwj1X1+qGCSZLG0fcRE2uBh5aMP9RN\nkyTt5/ruEXwCuDnJtd34q4HLh4kkSRpT36uG/iLJPwHP7ya9saq+PlwsSdJY+h4aAjgc2FlVfwnc\nk+TEgTJJkkbU91WV7wXeBby7m3QI8KmhQkmSxtN3j+A1wLnAjwGq6l7gyKFCSZLG07cIHqqqonsU\ndZIjhoskSRpT3yK4KslHgacmeTNwA4/zJTWSpNWh71VDH+jeVbwTOBn4s6raOGgySdIoViyCJAcD\nN3QPnvOPvyQdYFY8NFRVu4BHkjxlhDySpJH1vbP4AeDWJBvprhwCqKo/HiSVJGk0fYvgmu4jSTrA\nLFsESU6oqu9Vlc8VkqQD1ErnCD6/OJDkcwNnkSRNYKUiyJLhk4YMIkmaxkpFUHsZliQdIFY6Wfys\nJDuZ7Rkc1g3TjVdVPXnQdJKkwS1bBFV18FhBJEnT2Jf3EUiSDkAWgSQ1brAiSHJZkh1Jti6Z9rQk\nG5Pc1f08aqj1S5L6GXKP4OPA2Y+adiFwY1U9A7ixG5ckTWiwIqiqrwA/etTkVwGLdylfDrx6qPVL\nkvoZ+xzB2qra3g3/AFg78volSY8y2cnipa++3JMk65NsSrJpYWFhxGSS1Jaxi+C+JMcAdD937G3G\nqtpQVfNVNT83NzdaQElqzdhFcB1wfjd8PvCFkdcvSXqUIS8fvRL4d+DkJPckeRPwfuCsJHcBL+nG\nJUkT6vtimn1WVeft5aszh1qnJGnfeWexJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxFIEmNswgk\nqXEWgSQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS1DiLQJIa\nZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGrdmipUmuRu4H9gFPFxV81PkkCRN\nVASdF1XVDydcvyQJDw1JUvOmKoICbkiyOcn6iTJIkpju0NDzqmpbkqcDG5PcWVVfWTpDVxDrAU44\n4YQpMkpSEybZI6iqbd3PHcC1wOl7mGdDVc1X1fzc3NzYESWpGaMXQZIjkhy5OAy8FNg6dg5J0swU\nh4bWAtcmWVz/p6vqixPkkCQxQRFU1beBZ429XknSnnn5qCQ1ziKQpMZNeWexpCfKndn7d8+s8XJo\nv+QegSQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS1DiLQJIa\nZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxFIEmNswgkqXEW\ngSQ1bpIiSHJ2km8m+VaSC6fIIEmaGb0IkhwM/A1wDnAKcF6SU8bOIUmamWKP4HTgW1X17ap6CPgM\n8KoJckiSmKYIjgW+v2T8nm6aJGkCa6YOsDdJ1gPru9GfJNk6ZZ5V5Gjgh1OHWCXcFrstsy0yapDp\nxd+L3U7uM9MURbANOH7J+HHdtJ9TVRuADQBJNlXV/DjxVje3xW5ui93cFru5LXZLsqnPfFMcGvoa\n8IwkJyb5BeB1wHUT5JAkMcEeQVU9nOSPgH8GDgYuq6rbxs4hSZqZ5BxBVV0PXL8Pi2wYKst+yG2x\nm9tiN7fFbm6L3Xpti1TV0EEkSauYj5iQpMat2iJIcmiSm5P8Z5Lbkvz51JmmluTgJF9P8g9TZ5lS\nkruT3JpkS9+rIg5USZ6a5Ookdya5I8lvTZ1pCklO7n4fFj87k1wwda6pJPmT7u/m1iRXJjl02flX\n66GhJAGOqKoHkhwCfBV4e1X9x8TRJpPkHcA88OSqesXUeaaS5G5gvqqav1Y8yeXAv1XVJd1VeIdX\n1f9MnWtK3WNstgG/WVXfnTrP2JIcy+zv5SlV9WCSq4Drq+rje1tm1e4R1MwD3egh3Wd1ttYIkhwH\nvBy4ZOosWh2SPAV4AXApQFU91HoJdM4E/qvFElhiDXBYkjXA4cC9y828aosAfnYoZAuwA9hYVTdN\nnWlCHwb+FHhk6iCrQAE3JNnc3YHeqhOBBeDvukOGlyQ5YupQq8DrgCunDjGVqtoGfAD4HrAd+N+q\n+pflllnVRVBVu6rqVGZ3H5+e5FenzjSFJK8AdlTV5qmzrBLP634vzgHemuQFUweayBrgNOAjVfVs\n4MdA04917w6PnQt8duosU0lyFLMHeZ4I/BJwRJLXL7fMqi6CRd3u7peAs6fOMpEzgHO7Y+OfAV6c\n5FPTRppO9z8eqmoHcC2zJ9q26B7gniV7ylczK4aWnQPcUlX3TR1kQi8BvlNVC1X1U+Aa4LeXW2DV\nFkGSuSRP7YYPA84C7pw21TSq6t1VdVxVrWO22/uvVbVswx+okhyR5MjFYeClQJMPJKyqHwDfT7L4\nYLEzgdsnjLQanEfDh4U63wOem+Tw7qKbM4E7lltg1T59FDgGuLy7AuAg4KqqavqySQGwFrh29vvN\nGuDTVfXFaSNN6m3AFd0hkW8Db5w4z2S6/xicBbxl6ixTqqqbklwN3AI8DHydFe4wXrWXj0qSxrFq\nDw1JksZhEUhS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS1Lj/B+FDm8//GvOpAAAAAElFTkSu\nQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7faf16966d10>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_res2[\"worked\"].plot(kind=\"hist\", color=\"gold\", xlim=(3,8))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "##### The breakdown of shifts over nurses is much closer to the average than it was in the previous version."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### But what would be the minimal fairness level?\n",
    "\n",
    "But what is the absolute minimum for the deviation to the ideal average number of shifts?\n",
    "CPLEX can tell us: simply minimize only the the total deviation from average, ignoring the salary cost.\n",
    "Of course this is unrealistic, but it will help us quantify how far our fairness result is to the\n",
    "absolute optimal fairness.\n",
    "\n",
    "We modify the objective and solve for the third time (using the usual necessary update for DOcplexcloud credentials)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "* model nurses solved with objective = 4.000\n",
      "*  KPI: Total salary cost  = 29594.000\n",
      "*  KPI: Total over-worked  = 4.000\n",
      "*  KPI: Total under-worked = 0.000\n"
     ]
    }
   ],
   "source": [
    "mdl.minimize(total_overw + total_underw)\n",
    "assert mdl.solve(url=url, key=key), \"solve failed\"\n",
    "mdl.report()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "In the fairness-optimal solution, we have zero under-average shifts and 4 over-average.\n",
    "Salary cost is now higher than the previous value of 28884 but this was expected as salary cost was not part of the objective.\n",
    "\n",
    "To summarize, the absolute minimum for this measure of fairness is 4, and we have found a balance with fairness=7.\n",
    "\n",
    "Finally, we display the histogram for this optimal-fairness solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7faf2009f790>"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAD8CAYAAAB6paOMAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAD6NJREFUeJzt3X+MJ3V9x/HnizsafogCYXu9Au1BQ2iJLQfdUluttSIG\n1Ao2qZVUS43xbEutVJOKpqn6Rxv/QLE/jadQzx9gEEGpJbYHJVoTC+7hKccPg0VU4ODWXwWMAYF3\n/9hZb8W73dnz5jt793k+km++M/Od2XllstnXzo/vTKoKSVK7Dhg7gCRpXBaBJDXOIpCkxlkEktQ4\ni0CSGmcRSFLjLAJJapxFIEmNswgkqXGrxw7Qx1FHHVXr1q0bO4Yk7VO2bNnyzaqaWmq+faII1q1b\nx8zMzNgxJGmfkuRrfebz0JAkNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDVu\nn/hmsST1ddemu3Y5/fjzjp9wkn2HewSS1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCk\nxg1WBEkOSnJTki8muTXJ27rpRybZnOTO7v2IoTJIkpY25B7BI8Bzq+pkYD1wZpJnABcC11fVCcD1\n3bgkaSSDFUHNebgbPbB7FXA2sKmbvgk4Z6gMkqSlDXqOIMmqJFuBHcDmqroRWFNV27tZ7gfWDJlB\nkrS4QYugqh6vqvXAMcBpSZ7+pM+Lub2EH5NkQ5KZJDOzs7NDxpSkpk3kqqGq+i5wA3Am8ECStQDd\n+47dLLOxqqaranpqamoSMSWpSUNeNTSV5PBu+GDgDOAO4BrgvG6284BPDJVBkrS0IZ9HsBbYlGQV\nc4VzRVV9MsnngCuSvAr4GvDSATNIkpYwWBFU1ZeAU3Yx/VvA6UOtV5K0PH6zWJIaZxFIUuMsAklq\nnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxFIEmNswgkqXEWgSQ1ziKQpMZZ\nBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTGDVYESY5NckOS25LcmuR13fS3Jrk3\nydbu9YKhMkiSlrZ6wJ/9GPCGqro5yWHAliSbu88urqqLBly3JKmnwYqgqrYD27vhh5LcDhw91Pok\nSXtmIucIkqwDTgFu7Ca9NsmXklya5IhJZJAk7drgRZDkKcDHgAuq6kHg3cDxwHrm9hjesZvlNiSZ\nSTIzOzs7dExJatagRZDkQOZK4MNVdRVAVT1QVY9X1RPAe4HTdrVsVW2squmqmp6amhoypiQ1bcir\nhgJcAtxeVe9cMH3tgtleAmwbKoMkaWlDXjX0TOAVwC1JtnbT3gycm2Q9UMDdwGsGzCBJWsKQVw19\nFsguPrp2qHVKkpbPbxZLUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJ\napxFIEmNswgkqXEWgSQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTG\nWQSS1DiLQJIa16sIkvzycn9wkmOT3JDktiS3JnldN/3IJJuT3Nm9H7Hcny1J2nv67hH8S5KbkvxZ\nkqf1XOYx4A1VdRLwDOD8JCcBFwLXV9UJwPXduCRpJL2KoKp+C/hD4FhgS5LLkpyxxDLbq+rmbvgh\n4HbgaOBsYFM32ybgnD3MLknaC3qfI6iqO4G/Bt4I/DbwD0nuSPJ7Sy2bZB1wCnAjsKaqtncf3Q+s\n2c0yG5LMJJmZnZ3tG1OStEx9zxH8SpKLmfuv/rnA71bVL3XDFy+x7FOAjwEXVNWDCz+rqgJqV8tV\n1caqmq6q6ampqT4xJUl7oO8ewT8CNwMnV9X5Cw753MfcXsIuJTmQuRL4cFVd1U1+IMna7vO1wI49\nDS9J+sn1LYIXApdV1fcBkhyQ5BCAqvrgrhZIEuAS4PaqeueCj64BzuuGzwM+sSfBJUl7R98iuA44\neMH4Id20xTwTeAXw3CRbu9cLgLcDZyS5E3heNy5JGsnqnvMdVFUPz49U1cPzewS7U1WfBbKbj0/v\nuV5J0sD67hF8L8mp8yNJfhX4/jCRJEmT1HeP4ALgo0nuY+6//J8B/mCwVJKkielVBFX1+SS/CJzY\nTfpyVf1guFiSpEnpu0cA8GvAum6ZU5NQVR8YJJUkaWJ6FUGSDwK/AGwFHu8mF2ARSNI+ru8ewTRw\nUvdNYEnSfqTvVUPbmDtBLEnaz/TdIzgKuC3JTcAj8xOr6sWDpJIkTUzfInjrkCEkSePpe/nop5P8\nPHBCVV3Xfat41bDRJEmT0Pc21K8GrgTe0006Gvj4UKEkSZPT92Tx+czdRO5B+OFDan56qFCSpMnp\nWwSPVNWj8yNJVrObB8pIkvYtfYvg00neDBzcPav4o8C/DRdLkjQpfYvgQmAWuAV4DXAtizyZTJK0\n7+h71dATwHu7lyRpP9L3XkNfZRfnBKrq+L2eSJI0Ucu519C8g4DfB47c+3EkSZPW6xxBVX1rweve\nqnoXcw+0lyTt4/oeGjp1wegBzO0hLOdZBpKkFarvH/N3LBh+DLgbeOleTyNJmri+Vw39ztBBJEnj\n6Hto6PWLfV5V79w7cSRJk9b3C2XTwJ8yd7O5o4E/AU4FDutePybJpUl2JNm2YNpbk9ybZGv3esFP\nFl+S9JPqe47gGODUqnoI5v6gA/9eVS9fZJn3A//Ejz/X+OKqumiZOSVJA+m7R7AGeHTB+KPdtN2q\nqs8A397DXJKkCem7R/AB4KYkV3fj5wCb9nCdr03yR8AM8Iaq+s4e/hxJ0l7Q9wtlfwu8EvhO93pl\nVf3dHqzv3cDxwHpgOz96WeqPSLIhyUySmdnZ2T1YlSSpj76HhgAOAR6sqr8H7kly3HJXVlUPVNXj\nC25id9oi826squmqmp6amlruqiRJPfV9VOVbgDcCb+omHQh8aLkrS7J2wehLgG27m1eSNBl9zxG8\nBDgFuBmgqu5LssvLRucluRx4DnBUknuAtwDPSbKeuTuZ3s3csw0kSSPqWwSPVlUlKYAkhy61QFWd\nu4vJlywnnCRpeH3PEVyR5D3A4UleDVyHD6mRpP1C33sNXdQ9q/hB4ETgb6pq86DJJEkTsWQRJFkF\nXNfdeM4//pK0n1ny0FBVPQ48keRpE8gjSZqwvieLHwZuSbIZ+N78xKr6i0FSSZImpm8RXNW9JEn7\nmUWLIMnPVdXXq2pP7yskSVrhljpH8PH5gSQfGziLJGkESxVBFgwfP2QQSdI4liqC2s2wJGk/sdTJ\n4pOTPMjcnsHB3TDdeFXVUwdNJ0ka3KJFUFWrJhVEkjSO5TyPQJK0H7IIJKlxFoEkNc4ikKTGWQSS\n1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDVusCJIcmmSHUm2LZh2ZJLNSe7s3o8Yav2SpH6G\n3CN4P3Dmk6ZdCFxfVScA13fjkqQRDVYEVfUZ4NtPmnw2MP/Yy03AOUOtX5LUz6TPEaypqu3d8P3A\nmgmvX5L0JKOdLK6qYpGnniXZkGQmyczs7OwEk0lSWyZdBA8kWQvQve/Y3YxVtbGqpqtqempqamIB\nJak1ky6Ca4DzuuHzgE9MeP2SpCcZ8vLRy4HPAScmuSfJq4C3A2ckuRN4XjcuSRrRUg+v32NVde5u\nPjp9qHVKkpbPbxZLUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxF\nIEmNswgkqXEWgSQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS\n1DiLQJIat3qMlSa5G3gIeBx4rKqmx8ghSRqpCDq/U1XfHHH9kiQ8NCRJzRurCAq4LsmWJBt2NUOS\nDUlmkszMzs5OOJ4ktWOsInhWVa0HzgLOT/LsJ89QVRurarqqpqempiafUJIaMUoRVNW93fsO4Grg\ntDFySJJGKIIkhyY5bH4YeD6wbdI5JElzxrhqaA1wdZL59V9WVZ8aIYckiRGKoKruAk6e9HolSbvm\n5aOS1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxF\nIEmNswgkqXEWgSQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNW6UIkhyZpIv\nJ/lKkgvHyCBJmjPxIkiyCvhn4CzgJODcJCdNOockac4YewSnAV+pqruq6lHgI8DZI+SQJDFOERwN\nfGPB+D3dNEnSCFaPHWB3kmwANnSjjyTZNmaeFeQo4Jtjh1gh3BY7uS122vW2+OOJ51gJTuwz0xhF\ncC9w7ILxY7ppP6KqNgIbAZLMVNX0ZOKtbG6LndwWO7ktdnJb7JRkps98Yxwa+jxwQpLjkvwU8DLg\nmhFySJIYYY+gqh5L8ufAfwCrgEur6tZJ55AkzRnlHEFVXQtcu4xFNg6VZR/kttjJbbGT22Int8VO\nvbZFqmroIJKkFcxbTEhS41ZsESQ5KMlNSb6Y5NYkbxs709iSrEryhSSfHDvLmJLcneSWJFv7XhWx\nv0pyeJIrk9yR5PYkvzF2pjEkObH7fZh/PZjkgrFzjSXJX3Z/N7cluTzJQYvOv1IPDSUJcGhVPZzk\nQOCzwOuq6n9GjjaaJK8HpoGnVtWLxs4zliR3A9NV1fx180k2Af9dVe/rrsI7pKq+O3auMXW3sbkX\n+PWq+trYeSYtydHM/b08qaq+n+QK4Nqqev/ullmxewQ15+Fu9MDutTJbawKSHAO8EHjf2Fm0MiR5\nGvBs4BKAqnq09RLonA78b4slsMBq4OAkq4FDgPsWm3nFFgH88FDIVmAHsLmqbhw704jeBfwV8MTY\nQVaAAq5LsqX7BnqrjgNmgX/tDhm+L8mhY4daAV4GXD52iLFU1b3ARcDXge3A/1XVfy62zIougqp6\nvKrWM/ft49OSPH3sTGNI8iJgR1VtGTvLCvGs7vfiLOD8JM8eO9BIVgOnAu+uqlOA7wFN39a9Ozz2\nYuCjY2cZS5IjmLuR53HAzwKHJnn5Ysus6CKY1+3u3gCcOXaWkTwTeHF3bPwjwHOTfGjcSOPp/uOh\nqnYAVzN3R9sW3QPcs2BP+UrmiqFlZwE3V9UDYwcZ0fOAr1bVbFX9ALgK+M3FFlixRZBkKsnh3fDB\nwBnAHeOmGkdVvamqjqmqdczt9v5XVS3a8PurJIcmOWx+GHg+0OQNCavqfuAbSeZvLHY6cNuIkVaC\nc2n4sFDn68AzkhzSXXRzOnD7Ygus2LuPAmuBTd0VAAcAV1RV05dNCoA1wNVzv9+sBi6rqk+NG2lU\nrwU+3B0SuQt45ch5RtP9Y3AG8Jqxs4ypqm5MciVwM/AY8AWW+Ibxir18VJI0GSv20JAkaTIsAklq\nnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGvf/qUHsh3VellgAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7faf201c0110>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create a pandas Series containing actual shift assignment decision variables value\n",
    "s_assigned_fair = df_assigned.assigned.apply(lambda v: v.solution_value)\n",
    "\n",
    "# Create a pivot table by (nurses, shifts), using pandas' \"unstack\" method to transform the 'all_shifts' row index\n",
    "#  into columns\n",
    "df_res_fair = s_assigned_fair.unstack(level='all_shifts')\n",
    "\n",
    "# Add a new column to the pivot table containing the #shifts worked by summing over each row\n",
    "df_res_fair[\"solution_value_fair\"] = df_res_fair[all_shifts].sum(axis=1)\n",
    "df_res_fair[\"worked\"] = df_res_fair[all_shifts].sum(axis=1)\n",
    "df_res_fair[\"worked\"].plot.hist(color=\"plum\", xlim=(3,8))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "In the above figure, all nurses but one are assigned the average of 7 shifts, which is what we expected.\n",
    "\n",
    "## Summary\n",
    "\n",
    "You learned how to set up and use IBM Decision Optimization CPLEX Modeling for Python to formulate a Mathematical Programming model and solve it with IBM Decision Optimization on Cloud."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## References\n",
    "* [CPLEX Modeling for Python documentation](https://rawgit.com/IBMDecisionOptimization/docplex-doc/master/docs/index.html)\n",
    "* [Decision Optimization on Cloud](https://developer.ibm.com/docloud/)\n",
    "* Need help with DOcplex or to report a bug? Please go [here](https://developer.ibm.com/answers/smartspace/docloud).\n",
    "* Contact us at dofeedback@wwpdl.vnet.ibm.com."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Copyright © 2017 IBM. IPLA licensed Sample Materials."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "%%javascript\n",
    "Jupyter.notebook.session.delete();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python2.7 with DSX Spark 2.0.2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}