返回一组列中的第二大值

Question

我有以下数据框:

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>

我已经为 123a_1 到 123a_5 的每一行中的最大列设置了一个列。为此，我能够使用:

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

但是，现在我需要每一行中第二大的。这假定 123a_3 到 123a_5 中可能存在 NA 以外的数据。理想情况下，我想要一个 dplyr 解决方案，这样我就可以将这两个命令通过管道连接在一起，但我会采取任何措施。

Answer 1

使用 dplyr 和 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, .)

这导致:

      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

你可以按如下方式一起做所有事情:

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))

---

基于 R 的解决方案:

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