r - Find Minimum of a column and corresponding row of the minimum with condition on another column

Question

Key        Value Value2      Min Value Min Value2
1xA         1      2            1         2
2xA         2      3            2         3
3xB         3      1            2         2
1xB         1      1            1         1
1xA         5      5            1         2
2xB         2      6            2         6
3xB         2      2            2         2
2xA         4      1            2         3

In correspondence to the question I raised in this: Find minimum of a column with condition on another, for every observation in R

The Column Min. Value is the minimum Value corresponding to every Key How can I also fill the Min Value2 which will be the Value2corresponding to the Min. Valueobtained for every Key?

Answer 1

You can first calculate min for the group and look for row number of row containing min value which can be used to get Value2.

A solution using dplyr:

library(dplyr)

df %>% group_by(Key) %>%
  mutate(MinValue = min(Value)) %>%
  mutate(MinValue2 = Value2[which(Value==MinValue)[1]]) %>%
  as.data.frame()

#   Key Value Value2 MinValue MinValue2
# 1 1xA     1      2        1         2
# 2 2xA     2      3        2         3
# 3 3xB     3      1        2         2
# 4 1xB     1      1        1         1
# 5 1xA     5      5        1         2
# 6 2xB     2      6        2         6
# 7 3xB     2      2        2         2
# 8 2xA     4      1        2         3

Data:

df <- read.table(text = 
"Key        Value Value2      
1xA         1      2         
2xA         2      3         
3xB         3      1         
1xB         1      1         
1xA         5      5         
2xB         2      6         
3xB         2      2         
2xA         4      1",
header = TRUE, stringsAsFactors = FALSE)