R Create a column containing value of another column based on min or max of reference column

Question

I feel like this has been asked before but I can't find a repex that describes what I'm trying to do.

Suppose I have this dataframe:

df <- data.frame(ID = c(1, 1, 1, 2, 2, 2, 3, 3, 3),
                 B = c(34, 54, 17, 2, 25, 89, 23, 93, 90),
                 C = c(1, 2, 3, 1, 2, 3, 1, 2, 3))

> df
  ID  B C
1  1 34 1
2  1 54 2
3  1 17 3
4  2  2 1
5  2 25 2
6  2 89 3
7  3 23 1
8  3 93 2
9  3 90 3

I would like to create/mutate columns D and E that lists the value of column B matching the minimum and maximum of column C. So the output would be:

> df
  ID  B C  D  E
1  1 34 1 34 17
2  1 54 2 34 17
3  1 17 3 34 17
4  2  2 1  2 89
5  2 25 2  2 89
6  2 89 3  2 89
7  3 23 1 23 90
8  3 93 2 23 90
9  3 90 3 23 90

ID is a grouping variable; however, I would want an ungrouped output.

Answer 1

We can group by 'ID', create the D by subsetting the 'B' based on the min (or first element of C if it is already ordered) and similarly the 'E' by the max index of 'C' (or last element of 'C')

library(dplyr)
df %>%
   group_by(ID) %>%
   mutate(D = B[min(C)], E = B[max(C)]) %>%
   ungroup

-output

# A tibble: 9 x 5
#    ID     B     C     D     E
#  <dbl> <dbl> <dbl> <dbl> <dbl>
#1     1    34     1    34    17
#2     1    54     2    34    17
#3     1    17     3    34    17
#4     2     2     1     2    89
#5     2    25     2     2    89
#6     2    89     3     2    89
#7     3    23     1    23    90
#8     3    93     2    23    90
#9     3    90     3    23    90