R help - Mar 2013 - Optimization in R similar to MS Excel Solver

Dear all,
I am trying to find the solution for the optimization problem focused on the
finding minimum cost.
I used the solution proposed by excel solver, but there is a restriction in
the number of variables.

My data consists of 300 rows represent cities and 6 columns represent the
centres. It constitutes a cost matrix, where the cost are distances between
each city and each of six centres. 
..+ 1 column contains variables, represents number of firms.
I want to calculate the minimum cost between cities and centres.  Each city
can belong only to one of the centres.

A model example:
costs: distance between municipalities and centres + plus number of firms in
each municipality
"Municipality"	"Centre1"	"Centre2"
"Centre3"	"Centre4"	"Centre5"	"Centre6"
"Firms"				
"Muni1"		            30	    20	            60	              40
66	             90	            15
"Muni2"		            20	    30              	    60	              40
66	             90	            10
"Muni3"		            25	    31	            60	              40
66	             90	              5
"Muni4"		            27	    26	            60	              40
66	             90	             30

The outcome of excel functon Solver is:
cost assigned
"Municipality"	"Centre1"	"Centre2"
"Centre3"	"Centre4"	"Centre5"	"Centre6"
"Solution"				
"Muni1"		            0	            20	               0	               
0
0	                0	            300
"Muni2"		            20	     0              	       0	               
0
0	                0	            200
"Muni3"		            25	     0	                       0	              
0
0	                0	            125
"Muni4"		              0	    26	               0	                0
0	                0	            780

objective : 1405

I used package "lpSolve" but there is a problem with variables
"firms":

s <- as.matrix(read.table("C:/R/OPTIMALIZATION/DATA.TXT", dec =
",",
sep=";",header=TRUE))

      [2] [3] [4] [5] [6]
[1] 30 20 60 40 66 90
[2] 20 30 60 40 66 90
[3] 25 31 60 40 66 90
[4] 27 26 60 40 66 90

row.signs <- rep ("=", 4)
row.rhs <- c(15,10,5,30)
col.signs <- rep ("=", 6)
col.rhs <- c(1,1,1,1,1,1)
lp.transport (costs, "min", row.signs, row.rhs, col.signs, col.rhs,
presolve=0, compute.sens=0)
lp.transport (costs, "min", row.signs, row.rhs, col.signs, col.rhs,
presolve=0, compute.sens=0)$solution

Outcome:
Error in lp.transport(costs, "min", row.signs, row.rhs, col.signs,
col.rhs,
: 
  Error: We have 6 signs, but 7 columns

Does anyone know where could the problem ? 
Does there exist any other possibility how to perform that analysis in R ?
I am bit confused here about how can I treat with the variables
"firms".

Thanks 
Pavel




--
View this message in context:
http://r.789695.n4.nabble.com/Optimization-in-R-similar-to-MS-Excel-Solver-tp4660997.html
Sent from the R help mailing list archive at Nabble.com.

On 11-03-2013, at 23:31, Pavel_K <kuk064 at vsb.cz> wrote:
> Dear all,
> I am trying to find the solution for the optimization problem focused on
the
> finding minimum cost.
> I used the solution proposed by excel solver, but there is a restriction in
> the number of variables.
> 
> My data consists of 300 rows represent cities and 6 columns represent the
> centres. It constitutes a cost matrix, where the cost are distances between
> each city and each of six centres. 
> ..+ 1 column contains variables, represents number of firms.
> I want to calculate the minimum cost between cities and centres.  Each city
> can belong only to one of the centres.
> 
> A model example:
> costs: distance between municipalities and centres + plus number of firms
in
> each municipality
> "Municipality"	"Centre1"	"Centre2"
"Centre3"	"Centre4"	"Centre5"	"Centre6"
> "Firms"				
> "Muni1"		            30	    20	            60	              40
> 66	             90	            15
> "Muni2"		            20	    30              	    60	             
40
> 66	             90	            10
> "Muni3"		            25	    31	            60	              40
> 66	             90	              5
> "Muni4"		            27	    26	            60	              40
> 66	             90	             30
> 
> The outcome of excel functon Solver is:
> cost assigned
> "Municipality"	"Centre1"	"Centre2"
"Centre3"	"Centre4"	"Centre5"	"Centre6"
> "Solution"				
> "Muni1"		            0	            20	               0	          
0
> 0	                0	            300
> "Muni2"		            20	     0              	       0	           
0
> 0	                0	            200
> "Muni3"		            25	     0	                       0	         
0
> 0	                0	            125
> "Muni4"		              0	    26	               0	               
0
> 0	                0	            780
> 
> objective : 1405
> 
> I used package "lpSolve" but there is a problem with variables
"firms":
> 
> s <- as.matrix(read.table("C:/R/OPTIMALIZATION/DATA.TXT", dec
= ",",
> sep=";",header=TRUE))
> 
>      [2] [3] [4] [5] [6]
> [1] 30 20 60 40 66 90
> [2] 20 30 60 40 66 90
> [3] 25 31 60 40 66 90
> [4] 27 26 60 40 66 90
> 
> row.signs <- rep ("=", 4)
> row.rhs <- c(15,10,5,30)
> col.signs <- rep ("=", 6)
> col.rhs <- c(1,1,1,1,1,1)
> lp.transport (costs, "min", row.signs, row.rhs, col.signs,
col.rhs,
> presolve=0, compute.sens=0)
> lp.transport (costs, "min", row.signs, row.rhs, col.signs,
col.rhs,
> presolve=0, compute.sens=0)$solution
> 
> Outcome:
> Error in lp.transport(costs, "min", row.signs, row.rhs,
col.signs, col.rhs,
> : 
>  Error: We have 6 signs, but 7 columns
> 
> Does anyone know where could the problem ? 
> Does there exist any other possibility how to perform that analysis in R ?
> I am bit confused here about how can I treat with the variables
"firms".

Please provide a reproducible example including the necessary library()
statements.

In the call of lp.transport you are using a variable "costs" but where
is it defined?
You read a file with read.table into a variable "s".
Use dput.

Berend

Dear Mr Hasselman,
for a better understanding I have attached an example solved in excel by
using the tool Solver.

I want to assign for each municipality one of the centres and apply it for
calculating the minimum cost as you can see in an example.
I used package lpsolve, but it does not work. I am not sure how to treat
with this part of statement, I think I made mistake in it:
row.rhs <- c(15,10,5,30) and
col.rhs <- c(1,1,1,1,1,1)

The example in R:

library(lpSolve)
costs <- as.matrix(read.table("C:/R/OPTIMIZATION/DATA.TXT", dec =
",",
sep=";",header=TRUE)) 
row.signs <- rep ("=", 4) 
row.rhs <- c(15,10,5,30) 
col.signs <- rep ("=", 6) 
col.rhs <- c(1,1,1,1,1,1) 
lp.transport (costs, "min", row.signs, row.rhs, col.signs, col.rhs,
presolve=0, compute.sens=0) 
lp.transport (costs, "min", row.signs, row.rhs, col.signs, col.rhs,
presolve=0, compute.sens=0)$solution 

Outcome: 
Error in lp.transport(costs, "min", row.signs, row.rhs, col.signs,
col.rhs,
: 
Error: We have 6 signs, but 7 columns 
Hope the example solved in excel will help you to understand my problem.

Thank you
Pavel
example.xls <http://r.789695.n4.nabble.com/file/n4661019/example.xls>  



--
View this message in context:
http://r.789695.n4.nabble.com/Optimization-in-R-similar-to-MS-Excel-Solver-tp4660997p4661019.html
Sent from the R help mailing list archive at Nabble.com.

Pavel_K <kuk064 <at> vsb.cz> writes:
> 
> Dear all,
> I am trying to find the solution for the optimization problem focused on
> the finding minimum cost.
> I used the solution proposed by excel solver, but there is a restriction
> in the number of variables.
> 
> My data consists of 300 rows represent cities and 6 columns represent the
> centres. It constitutes a cost matrix, where the cost are distances between
> each city and each of six centres. 
> ..+ 1 column contains variables, represents number of firms.
> I want to calculate the minimum cost between cities and centres.  Each city
> can belong only to one of the centres.
(1) The solution you say the Excel Solver returns does not appear to be 
    correct: The column sum in columns 3 to 5 is not (greater or) equal
    to 1 as you request.

(2) lpSolve does not return an error, but says "no feasible solution
found",
    which seems to be correct: The equality constraints are too strict.

(3) If you relieve these constraints to inequalities, lpSolves does find
    a solution:

    costs <- matrix(c(
    30, 20, 60, 40, 66, 90,
    20, 30, 60, 40, 66, 90,
    25, 31, 60, 40, 66, 90,
    27, 26, 60, 40, 66, 90), 4, 6, byrow = TRUE)

    firms <- c(15, 10, 5, 30)

    row.signs <- rep (">=", 4)
    row.rhs   <- firms
    col.signs <- rep (">=", 6)
    col.rhs   <- c(1,1,1,1,1,1)

    require("lpSolve")
    T <- lp.transport (costs, "min", row.signs, row.rhs, col.signs,
col.rhs,
                       presolve = 0, compute.sens = 0)
    T$solution
    sum(T$solution * costs)     # 1557

Of course, I don't know which constraints you really want to impose.
Hans Werner
> A model example:
> costs: distance between municipalities and centres + plus number of firms
> in each municipality
> "Municipality" "Centre1" "Centre2"
"Centre3" "Centre4" "Centre5"
> "Centre6"
> "Firms"    
> "Muni1" 30 20 60 40 66 90 15
> "Muni2" 20 30 60 40 66 90 10
> "Muni3" 25 31 60 40 66 90 5
> "Muni4" 27 26 60 40 66 90 30
> 
> The outcome of excel functon Solver is:
> cost assigned
> "Municipality" "Centre1" "Centre2"
"Centre3" "Centre4" "Centre5" "Centre6"
> "Solution"    
> "Muni1"  0 20 0 0 0 0 300
> "Muni2" 20  0 0 0 0 0 200
> "Muni3" 25  0 0 0 0 0 125
> "Muni4"  0 26 0 0 0 0 780
> 
> objective : 1405
> 
> I used package "lpSolve" but there is a problem with variables
"firms":
> 
> s <- as.matrix(read.table("C:/R/OPTIMALIZATION/DATA.TXT", dec
= ",",
> sep=";",header=TRUE))
> 
>       [2] [3] [4] [5] [6]
> [1] 30 20 60 40 66 90
> [2] 20 30 60 40 66 90
> [3] 25 31 60 40 66 90
> [4] 27 26 60 40 66 90
> 
> row.signs <- rep ("=", 4)
> row.rhs <- c(15,10,5,30)
> col.signs <- rep ("=", 6)
> col.rhs <- c(1,1,1,1,1,1)
> lp.transport (costs, "min", row.signs, row.rhs, col.signs,
col.rhs,
> presolve=0, compute.sens=0)
> lp.transport (costs, "min", row.signs, row.rhs, col.signs,
col.rhs,
> presolve=0, compute.sens=0)$solution
> 
> Outcome:
> Error in lp.transport(costs, ...):
>   Error: We have 6 signs, but 7 columns
> 
> Does anyone know where could the problem ? 
> Does there exist any other possibility how to perform that analysis in R ?
> I am bit confused here about how can I treat with the variables
"firms".
> 
> Thanks 
> Pavel
>