CODEWITHSUNDEEP
rdataframedplyr
thehand0
thehand0

Reputation: 1163

obtain corresponding value of one column based on minimum value of another in R

I have several pairs of variables e.g. X1 and Y1, X2 and Y2...Xn and Yn etc). I know hoe to obtain the minimum value of some of the columns e.g (X1, X2...Xn), but I would like to find the Y value that corresponds to the minimum X value. I was thinking potentially some sort of key/value pair... would do the trick, but cant quite figure out how to implement it. If someone could help with any form of a solution, that would be fantastic.

# Make some dummy data
X1 = c(1,20,3,40,5)
Y1 = c(20,32,60,82,100)
X2 = c(10,2,30,4,50)
Y2= c(2,30,6,80,10)
df = data.frame(X1,Y1,X2,Y2)

 # This is what the dataframe looks like 
  X1  Y1 X2 Y2
1  1  20 10  2
2 20  32  2 30
3  3  60 30  6
4 40  82  4 80
5  5 100 50 10

# create column of minimum X values
df$minX = c(pmin( df[,1], df[,3]))

I want to create another column with the Y value corresponding to the minimum value of X, but cant quite figure out how to do it. In the example above, the resultant dataframe should look something like the following. NOTE : The corresponding Y value is not necessarily max or min).

  X1  Y1 X2 Y2 minX correspondingY
1  1  20 10  2    1             20
2 20  32  2 30    2             30
3  3  60 30  6    3             60
4 40  82  4 80    4             80
5  5 100 50 10    5            100

Any help would be appreciated. Thanks in advance.

Upvotes: 1

Views: 1280

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388797

In tidyverse you can do :

library(dplyr)

df1 <- df %>% mutate(row = row_number()) 

df1 %>%
  inner_join(
df1 %>%
  tidyr::pivot_longer(cols = -row,
               names_to = c('.value'), 
               names_pattern = '([A-Z])') %>%
  group_by(row) %>%
  slice(which.min(X)),  by = 'row')

#  X1  Y1 X2 Y2 row X   Y
#1  1  20 10  2   1 1  20
#2 20  32  2 30   2 2  30
#3  3  60 30  6   3 3  60
#4 40  82  4 80   4 4  80
#5  5 100 50 10   5 5 100

You can remove the row column if it is not needed.

Upvotes: 1

ThomasIsCoding
ThomasIsCoding

Reputation: 101034

Maybe you can try the code below

X <- df[startsWith(names(df), "X")]
df$minX <- do.call(pmin, X)
df$correspondingY <- df[startsWith(names(df), "Y")][cbind(seq(nrow(X)), max.col(-X))]

which gives

> df
  X1  Y1 X2 Y2 minX correspondingY
1  1  20 10  2    1             20
2 20  32  2 30    2             30
3  3  60 30  6    3             60
4 40  82  4 80    4             80
5  5 100 50 10    5            100

Upvotes: 1

Related Questions