How to merge rows by grouping and only keep highest value in R

user_11217595 0
Viewed 716

Suppose the following data frame:

dfX <- data.frame('a' = c('A', 'A', 'B', 'B', 'B', 'C', 'C', 'D', 'D', 'D'),
              'b' = c('c2', 'c2', 'c8', 'c8', 'c4', 'c7', 'c7', 'c9', 'c9','c9'),
              'c' = c('f34', 'f34', 'm92', 'm92', 'm92', 'g22', 'g22', 'i41', 'i41', 'i41'),
              'd' = c('Check', 'Check', 'Check', 'Check', 'UnCheck', 'Check', 'Check', 'Check', 'Check','Check'),
              'val1' = c(54, '', 37, '', '', 51, '', 74, '', ''),
              'val2' = c('', 59, '', 87, 84, '', 62, '', 27, 85))

dfX
    a   b   c    d       val1  val2
1   A1  c2  f34  Check   54 
2   A1  c2  f34  Check         59
3   A2  c8  m92  Check   37 
4   A2  c8  m92  Check         87
5   A2  c4  m92  UnCheck       84
6   A3  c7  g22  Check   51 
7   A3  c7  g22  Check         62
8   A4  c9  i41  Check   74 
9   A4  c9  i41  Check         27
10  A4  c9  i41  Check         85

I would like to merge columns val1 and val2 grouping them by the equivalent values of columns a, b, c, BUT if column d says 'Uncheck', then the row should be erased from the resulting data frame. On top of that, if there is more than one value in a column per group (like in case of rows A4), only the highest value of the repeated column should be kept

So far I was aiming with dplyr something like this: (This post as source)

FuncX <- function(x) x[x != '']

dfY <- dfX %>%
  group_by(a,b,c) %>%
  summarise_each(funs(FuncX))

But adding the d column and the said condition that if this column marks 'Unchecked', then it should be erased, I couldn't find a way around it. Also failed at having as output only the max() value of the A4 rows

The desired output should be like so:

dfY
    a   b   c    d       val1  val2
1   A   c2  f34  Check   54    59
2   B   c8  m92  Check   37    87
3   C   c7  g22  Check   51    62
4   D   c9  i41  Check   74    85
dplyr grouping r
2 Answers

We need to convert to numeric to get the max. The 'val1', 'val2' are character class (NOTE: We are using R 4.0.0 where stringsAsFactors = FALSE by default in data.frame construct. If the R version is < 4.0, then it would be stringsAFactors = TRUE by default and then the as.numeric(.) in the below should be changed to as.numeric(as.character(.)))

library(dplyr)
dfX %>%
    filter(d == 'Check') %>% 
    group_by(a, b, c, d) %>%
    summarise_at(vars(starts_with('val')), ~ max(as.numeric(.), na.rm = TRUE))
# A tibble: 4 x 6
# Groups:   a, b, c [4]
#  a     b     c     d      val1  val2
#  <chr> <chr> <chr> <chr> <dbl> <dbl>
#1 A     c2    f34   Check    54    59
#2 B     c8    m92   Check    37    87
#3 C     c7    g22   Check    51    62
#4 D     c9    i41   Check    74    85

Or with summarise/across in the new version of dplyr

dfX %>%
 filter(d == 'Check') %>% 
 group_by(a, b, c, d) %>% 
 summarise(across(starts_with('val'), ~ max(as.numeric(.), na.rm = TRUE)))

In base R, we can use aggregate

dfX[c('val1', 'val2')] <- lapply(dfX[c('val1', 'val2')], as.numeric)
aggregate(. ~ a + b + c+ d, dfX,subset = d == 'Check', max,
      na.rm = TRUE, na.action = NULL)

Or as @Onyambu suggested, the . can also be on the rhs of ~ to select the rest of the columns as grouping columns

aggregate(cbind(val1, val2) ~ ., dfX,subset = d == 'Check', max,
      na.rm = TRUE, na.action = NULL)
user_37322710

As it stands, your data.frame has val1 and val2 as factors, we can do this:

dfX %>% 
mutate_at(c("val1","val2"),~replace(as.character(.x),.x=="",NA)) %>% 
filter(d=="Check") %>% 
group_by(a,b,c,d) %>% 
summarize_all(~max(as.numeric(.x),na.rm=TRUE))

# A tibble: 4 x 6
# Groups:   a, b, c [4]
  a     b     c     d     val1  val2 
  <fct> <fct> <fct> <fct> <chr> <chr>
1 A     c2    f34   Check 54    59   
2 B     c8    m92   Check 37    87   
3 C     c7    g22   Check 51    62   
4 D     c9    i41   Check 74    85
user_122584590
Related