1

I have this optimization problem where I am trying to maximize column z based on a unique value from column X, but also within a constraint that each of the unique values picked of X added up column of Y most be less than (in this example) 23.

For example, I have this sample data:

d=data.frame(x=c(1,1,1,2,2,2,3,3,3),y=c(9,7,5,9,7,5,9,7,5),z=c(25,20,5,20,10,5,10,5,3))

Which looks like this:

  X  Y  Z 
1 1  9  25     
2 1  7  20   
3 1  5  5    
4 2  9  20    
5 2  7  10     
6 2  5  5    
7 3  9  10     
8 3  7  5               
9 3  5  5

The result should look like this:

  X  Y  Z 
1 1  9  25  
4 2  9  20     
9 3  5  5

How do I set this problem up in the lpSolve::lp function?

CooperBuckeye05
  • 149
  • 2
  • 14
  • And what have you tried? Did you already thought about LP vs. IP? – sascha Oct 16 '17 at 22:06
  • so essentially I have done this in excel using the solver, and looking to move it over into R. I am not quite following you on LP or IP (what they mean). I found other examples in stack that show how to move this over, but nothing that includes how to use a sum of the picks constraint. – CooperBuckeye05 Oct 16 '17 at 22:17
  • You (probably) can't solve it as a pure Linear Program, you will need Mixed-integer programming (which is supported by lpsolve). Read the docs of the wrapper you want to use, check out the standard-form it needs and try something on your own as this reads very broad for the moment. The nature of *picking* will be formulated as binary-variables: x0 = 1 iff x0 was picked. Picking only one of the three x-candidates would be: x0 + x1 + x2 <= 1 (and >= 1 if you need it). – sascha Oct 16 '17 at 22:19
  • Not sure how it seems very broad at the moment, I think it is a pretty straight forward ask. Maybe you could help me understand how it is broad? – CooperBuckeye05 Oct 16 '17 at 22:29
  • You ask how to formulate a MIP without any own idea/approach shown (while indicating missing knowledge about the internals of this optimizer too). – sascha Oct 16 '17 at 22:30
  • Your table does not equal the R-code (z column) which makes josliber's solution and your example-solution different. – sascha Oct 16 '17 at 22:34

1 Answers1

5

You are trying to maximize the sum of the z values of the selected options subject to two types of constraints:

  • The sum of the y values for the selected options does not exceed 23
  • You select exactly one value for each unique x value

You can create a binary variable for each option and then solve with lpSolve:

d=data.frame(x=c(1,1,1,2,2,2,3,3,3),y=c(9,7,5,9,7,5,9,7,5),z=c(25,20,5,20,10,5,10,5,3))
library(lpSolve)
all.x <- unique(d$x)
d[lp(direction = "max",
     objective.in = d$z,
     const.mat = rbind(outer(all.x, d$x, "=="), d$y),
     const.dir = rep(c("==", "<="), c(length(all.x), 1)),
     const.rhs = rep(c(1, 23), c(length(all.x), 1)),
     all.bin = TRUE)$solution == 1,]
#   x y  z
# 1 1 9 25
# 4 2 9 20
# 9 3 5  3
josliber
  • 43,891
  • 12
  • 98
  • 133
  • This works great, thank you! I appreciate your help as I while I am still just learning this package, I learn by taking code and playing around with it, and this is a perfect example of giving some direction and help (as opposed to other previous commenters). Thanks again! – CooperBuckeye05 Oct 16 '17 at 23:10
  • This may require a new thread, but if I wanted change an all.x to look like 1,1,2,3 (where there are more than one 1,s (ie. not unique)), how do I go about changing the const.mat line of the function? Would I need to change other parts of the lp function? – CooperBuckeye05 Oct 16 '17 at 23:38
  • @CooperBuckeye05 right now the constraint limits to exactly one from each group. You would just change `const.rhs` to be something other than all 1's if you wanted to allow more than one for a particular value. – josliber Oct 16 '17 at 23:39