Return second largest value in a group of columns

Question

I have the following dataframe:

dff <- structure(list(`MCI ID` = c("070405344", "230349820", "260386435","370390587", "380406805", "391169282", "440377986", "750391394","890373764", "910367024"), `123a_1` = structure(c(16672, 16372,16730, 16688, 16700, 16783, 16709, 17033, 16786, 16675), class = "Date"),`123a_2` = structure(c(17029, 16422, 17088, 17036, 17057,17140, 17072, 17043, 17141, 17038), class = "Date"), `123a_3` = structure(c(NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, NA_real_, NA_real_), class = "Date"), `123a_4` = structure(c(NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, NA_real_, NA_real_), class = "Date"), `123a_5` = structure(c(NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, NA_real_, NA_real_), class = "Date"), max123a = structure(c(17029,16422, 17088, 17036, 17057, 17140, 17072, 17043, 17141, 17038), class = "Date")), .Names = c("MCI ID", "123a_1", "123a_2","123a_3", "123a_4", "123a_5", "max123a"), row.nam... <truncated>

I already have a column for the largest of each row for 123a_1 through 123a_5. For this I was able to use:

dff <- mutate(dff, max123a = pmax(`123a_1`, `123a_2`, `123a_3`, `123a_4`, `123a_5`, na.rm = T))

However, now I need the second largest of each row. This assumes that there might be data other than NA in 123a_3 through 123a_5. Ideally, I'd like a dplyr solution, so that I can pipe the two commands together, but I'll take anything.

http://stackoverflow.com/questions/2453326/fastest-way-to-find-second-third-highest-lowest-value-in-vector-or-column — Raad, Feb 06 '17 at 16:29
@d.b, this works with the slight modification of `apply(dff, 1, function(x) max(x[x != max(x)]))` however, it only returns the `NAs`, is there a way to pass an `na.rm= T` argument? — Jeremy R. Johnson, Feb 06 '17 at 16:57

h3rm4n · Accepted Answer · 2017-02-06T17:53:11.300

With dplyr and tidyr:

library(dplyr)
library(tidyr)
dff %>% 
  gather(var, val, 2:6) %>% 
  group_by(`MCI ID`) %>% 
  summarise(max2 = max(val[val != max(val, na.rm = TRUE)], na.rm = TRUE)) %>% 
  left_join(dff, .)

This results in:

      MCI ID     123a_1     123a_2 123a_3 123a_4 123a_5    max123a       max2
1  070405344 2015-08-25 2016-08-16   <NA>   <NA>   <NA> 2016-08-16 2015-08-25
2  230349820 2014-10-29 2014-12-18   <NA>   <NA>   <NA> 2014-12-18 2014-10-29
3  260386435 2015-10-22 2016-10-14   <NA>   <NA>   <NA> 2016-10-14 2015-10-22
4  370390587 2015-09-10 2016-08-23   <NA>   <NA>   <NA> 2016-08-23 2015-09-10
5  380406805 2015-09-22 2016-09-13   <NA>   <NA>   <NA> 2016-09-13 2015-09-22
6  391169282 2015-12-14 2016-12-05   <NA>   <NA>   <NA> 2016-12-05 2015-12-14
7  440377986 2015-10-01 2016-09-28   <NA>   <NA>   <NA> 2016-09-28 2015-10-01
8  750391394 2016-08-20 2016-08-30   <NA>   <NA>   <NA> 2016-08-30 2016-08-20
9  890373764 2015-12-17 2016-12-06   <NA>   <NA>   <NA> 2016-12-06 2015-12-17
10 910367024 2015-08-28 2016-08-25   <NA>   <NA>   <NA> 2016-08-25 2015-08-28

You could do everything together as follows:

dff %>% 
  gather(var, val, 2:6) %>% 
  group_by(`MCI ID`) %>% 
  summarise(max2 = max(val[val != max(val, na.rm = TRUE)], na.rm = TRUE)) %>% 
  left_join(dff,.) %>% 
  mutate(max123a = pmax(`123a_1`, `123a_2`, `123a_3`, `123a_4`, `123a_5`, na.rm = TRUE))

A solution in base R:

dff$max2 <- apply(dff[2:6], 1, function(x) rev(sort(x))[2])

score 2 · Answer 2 · answered Feb 06 '17 at 17:02

2

We can use tidyverse

library(tidyverse)
dff %>%
      summarise_each(funs(rev(sort(.))[2]))

answered Feb 06 '17 at 17:02

akrun

874,273
37
540
662

Return second largest value in a group of columns

2 Answers2