SIYEN - 1 year ago 32

R Question

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

- Matrix B I have created from using Matrix A to show different combinations of i & j and their matching "Flow" values Si & Sj
- 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 Source

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
```