How to select row with maximum value if all values are NA in R

April 27, 2022

I have a dataframe that looks like this:

tt1 <- structure(list(sjlid = c("SJL1527107", "SJL1527107", "SJL1527107", 
"SJL1527107", "SJL1527107"), condition = c("Abnormal_glucose_metabolism", 
"Abnormal_glucose_metabolism", "Abnormal_glucose_metabolism", 
"Abnormal_glucose_metabolism", "Abnormal_glucose_metabolism"), 
    grade = c(NA, NA, NA, NA, NA), ageevent = c(58.8352421588442, 
    62.1366120218579, 64.4872969533648, 68.9694887341867, 70.9612695561045
    )), row.names = 72:76, class = "data.frame")

I need to run this code:

library(dplyr)
tt1 %>% group_by(condition) %>% top_n(1, grade) %>% top_n(1, ageevent)

This code works when there are multiple rows (like tt1), but if there are only one row (like tt2 below), it just fails to give that particular row. For example, if I have my dataframe like this:

tt2 <- structure(list(sjlid = "SJL1527107", condition = "Abnormal_glucose_metabolism", 
    grade = NA, ageevent = 58.8352421588442), row.names = 72L, class = "data.frame")

tt2 %>% group_by(condition) %>% top_n(1, grade) %>% top_n(1, ageevent) returns nothing

# A tibble: 0 x 4
# Groups:   condition [0]
# ... with 4 variables: sjlid <chr>, condition <chr>, grade <lgl>, ageevent <dbl>

Instead, I want it to return this row because it’s the only row there.

        sjlid                   condition grade         ageevent
72 SJL1527107 Abnormal_glucose_metabolism    NA 58.8352421588442

>Solution :

slice_max can be used –top_n is kind of deprecated in favor of slice

library(dplyr)
tt1 %>% 
  group_by(condition) %>%
  slice_max(n = 1, order_by = ageevent) %>%
  ungroup

-output

# A tibble: 1 × 4
  sjlid      condition                   grade ageevent
  <chr>      <chr>                       <lgl>    <dbl>
1 SJL1527107 Abnormal_glucose_metabolism NA        71.0

It also works with tt2 (if both columns needs to be considered)

tt2 %>%
  group_by(condition) %>% 
  slice_max(n = 1, order_by = pmax(ageevent, grade, na.rm = TRUE) ) %>%
  ungroup
# A tibble: 1 × 4
  sjlid      condition                   grade ageevent
  <chr>      <chr>                       <lgl>    <dbl>
1 SJL1527107 Abnormal_glucose_metabolism NA        58.8

For the top_n, we can use

tt2 %>%
   group_by(condition) %>%
   top_n(pmax(grade, ageevent, na.rm = TRUE)) %>%
   ungroup
Selecting by ageevent
# A tibble: 1 × 4
  sjlid      condition                   grade ageevent
  <chr>      <chr>                       <lgl>    <dbl>
1 SJL1527107 Abnormal_glucose_metabolism NA        58.8