Get Max value by category as a new column in R

user_12070334 0
Viewed 335

I understand the questions wording is not clear but its a simple one, hopefully the image will convey more than words. I need a new column (New Col in image) which gets a value from column B corresponding to max value in column N (by group) in this case A.

Preferably tidyverse solution since I am piping a long command.

   df <-  structure(list(A = c("a", "a", "a", "a", "a", "b", "b", "b"), 
        B = c("b", "c", "d", "e", "f", "c", "d", "e"), N = c(1L, 
        2L, 3L, 4L, 5L, 5L, 4L, 3L), New.Col = c("f", "f", "f", "f", 
        "f", "c", "c", "c")), class = "data.frame", row.names = c(NA, 
    -8L))

Output

dplyr tidyverse r
3 Answers

Here is a clean and neat version of my answer without ifelse:

library(dplyr)

df %>% 
  group_by(A) %>% 
  mutate(new_col = B[N==max(N)]) 

  A     B         N New.Col new_col
  <chr> <chr> <int> <chr>   <chr>  
1 a     b         1 f       f      
2 a     c         2 f       f      
3 a     d         3 f       f      
4 a     e         4 f       f      
5 a     f         5 f       f      
6 b     c         5 c       c      
7 b     d         4 c       c      
8 b     e         3 c       c

First answer: We could use max in an ifelse statement:

library(dplyr)
df %>%
  group_by(A) %>% 
  mutate(NewCol = ifelse(N==max(N), B, B)) %>% 
  select(-NewCol)

  A     B         N New.Col
  <chr> <chr> <int> <chr>  
1 a     b         1 f      
2 a     c         2 f      
3 a     d         3 f      
4 a     e         4 f      
5 a     f         5 f      
6 b     c         5 c      
7 b     d         4 c      
8 b     e         3 c
user_133216470

Using data.table

library(data.table)
 setDT(df)[, new_col := B[which.max(N)], A]
> df
   A B N New.Col new_col
1: a b 1       f       f
2: a c 2       f       f
3: a d 3       f       f
4: a e 4       f       f
5: a f 5       f       f
6: b c 5       c       c
7: b d 4       c       c
8: b e 3       c       c
user_37322710

You can just use normal group_by and mutate

library(tidyverse)

df <-  structure(list(A = c("a", "a", "a", "a", "a", "b", "b", "b"), 
                      B = c("b", "c", "d", "e", "f", "c", "d", "e"), N = c(1L, 
                                                                           2L, 3L, 4L, 5L, 5L, 4L, 3L), New.Col = c("f", "f", "f", "f", 
                                                                                                                    "f", "c", "c", "c")), class = "data.frame", row.names = c(NA, 
                                                                                                                                                                              -8L))

df %>% 
  group_by(A) %>% 
  mutate(new_col = B[which.max(N)]) %>% 
  ungroup()
user_66645780
Related