How to use R to solve/pick the best people for a job - with restraints?

Question

I'm fairly new to R and I'm trying to write a script for what I used to do with Solver in Excel. In my data below, I have a list of workers with job types A-E. Each worker has a salary and a production rate. What I want R to do is find the maximum production I can get from 10 workers with a cumulative salary <100,000. The restraints are that I need an exact total of 10 workers and I need 2 from job types A-D, 1 from E, and 1 of any type.

I've searched and searched for a way to do this with optim, IpSolve, etc., but with my limited knowledge I have not had much luck.

Thank you for your help!

Name    Pos Salary  Producton
Joe     A   12001   13.1
Jim     A   17753   23.5
Jill    A   11447   14.8
Brian   A   11447   14.8
Sally   B   2171    1.2
Nancy   B   4537    2.1
Francis B   2840    1.8
Ace     B   2840    1.8
Bill    C   3818    1.6
Ted     C   11447   0.1
Henry   C   2000    1.1
Kyle    C   3818    1.6
Sam     D   11447   0.1
Trevor  D   2000    1.1
John    D   4317    11.7
Jerome  D   2000    1.1
Rebecca E   3818    1.6
Sunny   E   11447   0.1
Britt   E   2000    1.1
Sara    E   4317    11.7

Answer 1

Use lp in the lpSolve package to solve the underlying integer programming problem. The first 5 constraints are on the number of A, B, C, D and E positions respectively, the 6th is on the number of staff to choose and the 7th is on the total salary. Assuming DF is the data frame shown in the question try this:

library(lpSolve)

obj <- DF$Prod
con <- rbind(t(model.matrix(~ Pos + 0, DF)), rep(1, nrow(DF)), DF$Salary)
dir <- c(">=", ">=", ">=", ">=", ">=", "==", "<")
rhs <- c(2, 2, 2, 2, 1, 10, 100000)

result <- lp("max", obj, con, dir, rhs, all.bin = TRUE)

which gives:

> result
Success: the objective function is 84.7 
> DF[result$solution == 1, ]
     Name Pos Salary Producton
2     Jim   A  17753      23.5
3    Jill   A  11447      14.8
4   Brian   A  11447      14.8
6   Nancy   B   4537       2.1
8     Ace   B   2840       1.8
9    Bill   C   3818       1.6
12   Kyle   C   3818       1.6
14 Trevor   D   2000       1.1
15   John   D   4317      11.7
20   Sara   E   4317      11.7

Note that Production is misspelled in the question or perhaps that was intended.

ADDED:

Regarding the second best solution the idea is to add a constraint which makes the best solution infeasible but does not exclude other potential solutions:

con2 <- rbind(con, result$solution)
dir2 <- c(dir, "<=")
rhs2 <- c(rhs, 9)
result2 <- lp("max", obj, con2, dir2, rhs2, all.bin = TRUE)

In this case we get the following which has the same optimum objective value as the best solution so it would be just as good:

> result2
Success: the objective function is 84.7 
> DF[result2$solution == 1, ]
     Name Pos Salary Producton
2     Jim   A  17753      23.5
3    Jill   A  11447      14.8
4   Brian   A  11447      14.8
6   Nancy   B   4537       2.1
8     Ace   B   2840       1.8
9    Bill   C   3818       1.6
12   Kyle   C   3818       1.6
15   John   D   4317      11.7
16 Jerome   D   2000       1.1
20   Sara   E   4317      11.7

There are also arguments to lp which allow it to produce multiple solutions directly; however, the help file mentions some bugs and it may be safer to take the above approach.