How to use R to decide / choose the best people to work with restrictions?

Question

I am new to R and I am trying to write a script for what I used with Solver in Excel. In my data below, I have a list of employees with job types AE. Each employee has a salary and a production rate. What I want to do R is to find the maximum production that I can get from 10 workers with a total salary and $ 100,000. The limitations are that I need a total of 10 workers, and I need 2 of the types of AD jobs, 1 of E and 1 of any type.

I searched and searched for a way to do this with optim, IpSolve, etc., but with my limited knowledge I was out of 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 main integer programming problem. The first 5 restrictions relate to the number of positions A, B, C, D and E respectively, the 6th number depends on the number of employees, and the 7th depends on the total wage. 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 

Please note that the release is spelled incorrectly, or perhaps it was intended.

ADDED:

In relation to the second best solution, the idea is to add a constraint that makes the optimal solution impossible, 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 optimal 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 lp arguments that allow it to create multiple solutions at once; however, some errors are mentioned in the help file, and it may be safer to use the above approach.