The computer is not smarter than people, but it does have more eyes, that is why these cases with many variables are better left to the computer. The tool we will use will be mathematical programming, where we will use it to decide which day each collaborator goes to work, according to their preferences and the needs of the company.<\/p>\n
In this case, we are going to define that the people who must meet in the office are those from the same team, we will make each person belong to one day per week, everyone on a team will have to go to the office and thus carry out team activities.<\/p>\n
Model Formulation<\/h3>\nLoad Datasets<\/h4>\n
To consider each collaborator’s preferences, we will ask each one to assign a score to each day as follows:
\n![\"file\"](\"https:\/\/i0.wp.com\/www.geekosas.com\/wp-content\/uploads\/2021\/10\/image-1635109694128.png?ssl=1\")
<\/p>\n
We will start by loading the data into python and preparing the data:<\/p>\n
>>> import pulp\n>>> import itertools\n>>> import pandas as pd\n>>> pref = pd.read_excel("data_con_pref.xlsx","pref")\n>>> pref = pref.fillna(0)\n>>> print(pref)\n lun mar mie jue vie\ncolab \nc1 1 4 -5 5 -5\nc2 -3 5 -3 5 -4\nc3 3 3 -5 4 -5\nc4 -2 1 8 1 -8\nc5 -4 -6 1 7 2\nc6 3 3 3 1 -10\nc7 2 2 2 2 2\nc8 -8 3 3 -2 4\nc9 -2 -8 4 2 4\nc10 4 3 3 -5 -5\nc11 0 5 2 3 -10\nc12 1 -3 -7 8 1\nc13 5 5 -3 -3 -4\nc14 -2 5 5 -2 -6\nc15 2 -3 -4 -3 8<\/code><\/pre>\nThe preferences will be used to calculate the benefit of following them for the model, but obviously forcing a person to go to work on a day that is not their preference is quite annoying, so after scaling the scores, we will multiply the negative values by 2.<\/p>\n
>>> #scaled to 1 and double weighting of negatives\n>>>\n>>> for c in pref.index:\n... pref.loc[c,:] = pref.loc[c,:]\/pref.loc[c,:].abs().sum()\n... pref.loc[c,:] = [x if x > 0 else 2*x for x in pref.loc[c,:]]\n...\n>>> print(pref)\n lun mar mie jue vie\ncolab \nc1 0.05 0.20 -0.50 0.25 -0.50\nc2 -0.30 0.25 -0.30 0.25 -0.40\nc3 0.15 0.15 -0.50 0.20 -0.50\nc4 -0.20 0.05 0.40 0.05 -0.80\nc5 -0.40 -0.60 0.05 0.35 0.10\nc6 0.15 0.15 0.15 0.05 -1.00\nc7 0.20 0.20 0.20 0.20 0.20\nc8 -0.80 0.15 0.15 -0.20 0.20\nc9 -0.20 -0.80 0.20 0.10 0.20\nc10 0.20 0.15 0.15 -0.50 -0.50\nc11 0.00 0.25 0.10 0.15 -1.00\nc12 0.05 -0.30 -0.70 0.40 0.05\nc13 0.25 0.25 -0.30 -0.30 -0.40\nc14 -0.20 0.25 0.25 -0.20 -0.60\nc15 0.10 -0.30 -0.40 -0.30 0.40<\/code><\/pre>\nin addition to a table with the membership of each team:
\n![\"file\"](\"https:\/\/i0.wp.com\/www.geekosas.com\/wp-content\/uploads\/2021\/10\/image-1635109747576.png?ssl=1\")
<\/p>\n
>>> team = pd.read_excel("data_con_pref.xlsx","team")\n>>> team = team.set_index('colab')\n>>> print(team)\n team\ncolab \nc1 t1\nc2 t1\nc3 t1\nc4 t1\nc5 t2\nc6 t2\nc7 t2\nc8 t2\nc9 t2\nc10 t3\nc11 t3\nc12 t3\nc13 t3\nc14 t3\nc15 t3<\/code><\/pre>\nDimensions<\/h4>\n
They are like the coordinates of the data<\/p>\n
\n- colab<\/strong>: collaborators<\/li>\n
- dias<\/strong>: day of the week<\/li>\n
- team<\/strong>: team<\/li>\n<\/ul>\n
we extract them from the data itself:<\/p>\n
# indices\ndias = pref.columns\ncolab = pref.index\nteams = set(team["team"])<\/code><\/pre>\nDecision Variables<\/h4>\n
Which day each collaborator goes: 1 if they go on a day, 0 if not:<\/p>\n
CD = {e:{d:pulp.LpVariable(cat ='Binary', name ='{} el {}'.format(e, d)) for d in dias} for e in emp}<\/code><\/pre>\nWhich day each team will meet<\/p>\n
TD = {t:{d:pulp.LpVariable(cat ='Binary', name ='{} el {}'.format(t, d)) for d in dias} for t in teams}<\/code><\/pre>\nObjective Function<\/h4>\n
The objective function is what allows us to compare different shift combinations with a number.<\/p>\n
The objective function is debatable, there could be many, for example: maximize the worst-off collaborator with respect to their preferences or maximize the overall benefit. In this case we will build the second case, where there is a benefit for going on preferred days and for not going on days without preference or with a negative score:<\/p>\n
obj = \\sum_{c,d}{CD_{c,d} * pref_{c,d} - (1-CD_{c,d}) * pref_{c,d} }<\/code><\/p>\nprob = pulp.LpProblem("DIAS", pulp.LpMaximize)\nprob += pulp.lpSum([ CD[e][d] * pref.loc[e, d] - (1 - CD[e][d]) * pref.loc[e, d] for e, d in itertools.product(emp, dias)])<\/code><\/pre>\nConstraints<\/h4>\n
Finally we will define the interaction of the variables as constraints that reflect the conditions that make a combination of shifts valid:<\/p>\n
Everyone goes 2 times a week<\/h5>\n
\\forall e \\in colab \\sum_{d \\in dias} CD_{c,d} = 2<\/code><\/p>\nfor e in emp:\n prob += pulp.lpSum([CD[e][d] for d in dias]) == 2<\/code><\/pre>\nEach team has its day<\/h5>\n
\\forall t \\in team \\sum_{d \\in dias} TD_{t,d} = 1<\/code><\/p>\nfor t in teams:\n prob += pulp.lpSum([TD[t][d] for d in dias]) == 1<\/code><\/pre>\nEveryone goes on their team’s day<\/h5>\nfor e, t, d in itertools.product(colab,teams,dias):\n if team.loc[e,'team'] == t:\n prob += CD[e][d] >= TD[t][d]<\/code><\/pre>\nMaximum capacity of 10 people per day in the office<\/h5>\n
\\forall d \\in dias \\sum_{e \\in colab} CD_{e,d} <= 10<\/code><\/p>\nfor d in dias:\n prob += pulp.lpSum([CD[e][d] for e in emp]) <= 10<\/code><\/pre>\nWe solve the problem and look at the results:<\/h4>\nsolver = pulp.getSolver('PULP_CBC_CMD')\nprob.solve(solver)<\/code><\/pre>\nWe look at the solution<\/h4>\n
We see that in this case only 2 collaborators will have to go on days that are not their preference<\/p>\n
>>> for e,d in itertools.product(colab,dias):\n... if(pulp.value(CD[e][d]) > 0.9):\n... print('{} goes on {} and is {}'.format(e,d,'ok' if pref.loc[e,d] > 0 else 'not ok'))\n...\nc1 goes on tue and is ok\nc1 goes on thu and is ok\nc2 goes on tue and is ok\nc2 goes on thu and is ok\nc3 goes on tue and is ok\nc3 goes on thu and is ok\nc4 goes on wed and is ok\nc4 goes on thu and is ok\nc5 goes on wed and is ok\nc5 goes on thu and is ok\nc6 goes on mon and is ok\nc6 goes on wed and is ok\nc7 goes on tue and is ok\nc7 goes on wed and is ok\nc8 goes on wed and is ok\nc8 goes on fri and is ok\nc9 goes on wed and is ok\nc9 goes on fri and is ok\nc10 goes on mon and is ok\nc10 goes on wed and is ok\nc11 goes on mon and is not ok\nc11 goes on tue and is ok\nc12 goes on mon and is ok\nc12 goes on thu and is ok\nc13 goes on mon and is ok\nc13 goes on tue and is ok\nc14 goes on mon and is not ok\nc14 goes on wed and is ok\nc15 goes on mon and is ok\nc15 goes on fri and is ok<\/code><\/pre>\nobtaining a score of 17.4<\/p>\n
>>>print("Objective Value")\n>>>print(pulp.value(prob.objective))\n17.400000000000006<\/code><\/pre>\nif you want to see the code you can download it from my github: https:\/\/github.com\/danielfm123\/turnos_back_to_office<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"