SIYEN - 1 year ago 55
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.

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download