I have 3 columns. C1 and C2 are grouped by C0. Now I want to extract the C3 value where it satisfies when C1 is maximum within each C0 group.
df = data.frame(C0 = c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3),
C1 = c(0,2,3,6,2,0,0,4,9,7,1,2,7,4,2),
C2 = c("A","B", "C", "D", "E","A","B", "C", "D", "E","A","B", "C", "D", "E"))
Now I want to add a new column C4, which is the value of C2 where the corresponding C1 reaches maximum within each C0 group. Now I can only extract the value of maximum C1, something like this
df %>% group_by(C0) %>% mutate (C4 = max(C1))
But this code returns as C4 is the value of the maximum value of C1 whitin each C0 group. I don't know how to extract the corresponding C2 value. Also, I don't want to just extract the row of the maximum value, but to add a new column. Like this (since I am not allowed to attach a figure, I am using the code to explain the idea:
df = data.frame(C0 = c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3),
C1 = c(0,2,3,6,2,0,0,4,9,7,1,2,7,4,2),
C2 = c("A","B", "C", "D", "E","A","B", "C", "D", "E","A","B", "C", "D", "E"),
C4 = c("D","D","D","D","D","D","D","D","D","D","C","C","C","C","C"))
Thank you so much for helping me!
We can use which.max after grouping by 'C0' to get the row index and use that to subset the value of 'C2'
library(dplyr)
df %>%
group_by(C0) %>%
mutate(C4 = C2[which.max(C1)])
# A tibble: 15 x 4
# Groups: C0 [3]
# C0 C1 C2 C4
# <dbl> <dbl> <fct> <fct>
# 1 1 0 A D
# 2 1 2 B D
# 3 1 3 C D
# 4 1 6 D D
# 5 1 2 E D
# 6 2 0 A D
# 7 2 0 B D
# 8 2 4 C D
# 9 2 9 D D
#10 2 7 E D
#11 3 1 A C
#12 3 2 B C
#13 3 7 C C
#14 3 4 D C
#15 3 2 E C