For each row in x I am taking the num, the id1, and id2; first checking for each row in y if the x$num and y$num are equal and if they are taking the y value for that num from the column in y corresponding to the id1 and id2 in x, then placing the min of these two columns in y back into a new column in x.
The num can be repeated in x, but is unique in y. The id1 and id2 are values in x, and column headers in y.
So essentially I extract the id1 and id2 from x, and look in y (given the nums for the row in x and y are the same) at the columns with the same name as id1 and id2.
x <- data.frame(num = c(1,2,2,4), id1 = c("a","b","c","d"), id2 = c("b","d","a","b"));
y <- data.frame(num= c(1,2,3,4,5,6), a = c(1,1,3,2,1,2), b = c(2,3,2,4,5,2), c = c(1,1,3,2,5,2), d = c(3,2,1,3,4,2) );
x$id1 = as.character(x$id1);
x$id2 = as.character(x$id2);
for(i in 1:length(x[,1])){
for(j in 1:length(y[,1])){
if(x[i,1] == y[j,1]){
x$min[i] = min( y[j,x[i,2]], y[j,x[i,3]] )
}
}
}
(Note: I get an error when trying to execute that code. Will fix the posted y matrix and let the OP address his error on his own.)
The output I am trying to achieve is the min of the two corresponding columns for each row of x added to the min column to x.
The above code I have works, but is very inefficient given that my x and y data frames are very big and I was hoping there was some sort of apply method I could use, but the fact that I only needed to reference certain rows in the y matrix given the row in the x matrix was throwing me off when I tried to solve using apply.
Input:
x
num id1 id2
1 a b
2 b d
2 c a
4 d b
y
num a b c d
1 1 1 2 1 3
2 2 1 3 1 2
3 3 3 2 3 1
4 4 2 4 2 3
5 5 1 5 5 4
6 6 2 2 2 2
Output:
x
num id1 id2 min
1 a b 1
2 b d 2
2 c a 1
4 d b 3
Using match to look up the appropriate columns and the matrix method to use two-column indexing for "[" will give you a loop-less solution (which I will undelete when the OP fixes his misleading question that has the wrong answer.)
x$min <- pmin( y[cbind(x$num, match( as.character(x$id2), names(y) ) ) ],
y[cbind(x$num, match( as.character(x$id1), names(y) ) ) ] )
x
#----------------------
num id1 id2 min
1 1 a b 1
2 2 b d 2
3 2 c a 1
4 4 d b 3