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

user_4701887 0
Viewed 37

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
r
1 Answers

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

If we need to prioritize, an option is also with arrange

tt2 %>%
   arrange(condition, desc(ageevent), desc(grade)) %>% 
   distinct(condition, .keep_all = TRUE)

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
user_37322710
Related