I'm trying to create a df that has col #, row number and cell value based on the lowest value according to a linear model with constraints. column is the col with the lowest value according to the lm output. row is the corresponding row with lowest value, now I want the actual value and i'm having a hard time. if your curious the time value is gmapsdistance output in seconds.
What i'm getting
> h column row time.1 time.2 time.3 time.4 time.5 1 1 1 8262 8262 8262 66357 66357 2 1 2 21386 21386 21386 73307 73307 3 1 3 30698 30698 30698 52547 52547 4 2 4 32711 32711 32711 53006 53006 5 2 5 66156 66156 66156 65205 65205
What I want is one "time" column with the minimum time corresponding to column and row within aa.
> h column row time 1 1 1 8262 2 1 2 21386 3 1 3 30698 4 2 4 53006 5 2 5 65205
Here is a reproducible example:
library(lpSolve) aa <- matrix(c(8262, 21386, 30698, 32711, 66156, 66357, 73307, 52547, 53006, 65205), nrow=5, ncol=2) aa #Run aa through a Linear model with lower constraint of 2 and upper constraint of 8 gwide <- aa k <- ncol(gwide) n <- nrow(gwide) dir <- "min" objective.in <- c(gwide) A <- t(rep(1, k)) %x% diag(n) B <- diag(k) %x% t(rep(1, n)) const.mat <- rbind(A, B, B) const.dir <- c(rep("==", n), rep(">=", k), rep("<=", k)) const.rhs <- c(rep(1, n), rep(2, k), rep(8, k)) res <- lp(dir, objective.in, const.mat, const.dir, const.rhs, all.bin = TRUE) res #create a matrix from LM soln <- matrix(res$solution, n, k) soln column <- apply(soln, 1, which.max) h <- as.data.frame(column) h$row = 1:nrow(h) h$time <- aa[h$row,c(h$column)] #this seems to be where the problem is h I thought h$time <- aa[h$row,c(h$column)] would return a new column named "time" with the value from aa based on the row and column from h but that didn't work out so well. I've been racking my brain for hours and have come up with nothing. Any thoughts?
