SIYEN SIYEN - 2 months ago 6
R Question

Look up maximum value in a specified range of two values in R

Hi I am fairly new to R and have been trying to find a solution to implement the following:

I have Matrix A (original data set):

df1 <- data.frame(i=1:5, Flow=c(.87,.49,.36,.83,.87))
df1
i Flow
1 1 0.87
2 2 0.49
3 3 0.36
4 4 0.83
5 5 0.87



  1. Matrix B I have created from using Matrix A to show different combinations of i & j and their matching "Flow" values Si & Sj

  2. The column "Max Value" is the desired result I want:



Matrix B - Modified Matrix & Required result - column "Max Value" image

MatrixB<-data.frame(i=c(1,1,1,2,2,3), j=c(3,4,5,4,5,5),
S_i=c(0.87,0.87,0.87,0.49,0.49,0.36),
S_j=c(0.36,0.83,0.87,0.83,0.87,0.87))

i j S_i S_j
1 1 3 0.87 0.36
2 1 4 0.87 0.83
3 1 5 0.87 0.87
4 2 4 0.49 0.83
5 2 5 0.49 0.87
6 3 5 0.36 0.87


Required Result with Max value column

i j S_i S_j Max
1 1 3 0.87 0.36 0.49
2 1 4 0.87 0.83 0.49
3 1 5 0.87 0.87 0.83
4 2 4 0.49 0.83 0.36
5 2 5 0.49 0.87 0.83
6 3 5 0.36 0.87 0.83


4.Using row 2 of Matrix B as an example of what I am trying to do:


  • i = 1 corresponds to Si=0.87

  • j = 4 corresponds to Sj=0.83



For row 2 the 'Max Value' column in Required result shows equal to 0.49
This is because I am looking up the maximum value in column 2 of Matrix A from rows greater than i=1 to rows less than j=4 (i.e max(0.49,0.36))

The issue is the range to look up the maximum value in column 2 of Matrix A changes for every row and is defined by the i & j value in Matrix B.

How would I implement this?
Sorry if this has been answered elsewhere but I did search for solutions and couldn't find a similar query.

Thanks in advance

Answer

There are a few steps involved including the indexed pairs and the max value identification. Lastly we have to arrange everything in an orderly output:

cmb <- combn(df1$i, 2)
ind <- apply(cmb, 2, diff) > 1
fun <- function(x,y) max(df1$Flow[(.x <- x:y)[-c(1, length(.x))]])
values <- mapply(fun, cmb[1,ind], cmb[2,ind])
ij <- sij <- t(cmb[,ind])
sij[] <- df1$Flow[ij]
newdf <- cbind.data.frame(ij, sij, values)
names(newdf) <- c("i", "j", "Si", "Sj", "Max")
newdf
#   i j   Si   Sj  Max
# 1 1 3 0.87 0.36 0.49
# 2 1 4 0.87 0.83 0.49
# 3 1 5 0.87 0.87 0.83
# 4 2 4 0.49 0.83 0.36
# 5 2 5 0.49 0.87 0.83
# 6 3 5 0.36 0.87 0.83
Comments