Returning group maximum and NA using dplyr-CodePudding

I need a function I can use that returns both the group maximum and any NA values. Here is toy data:

df <- data.frame(id = rep(1:5, 
                          each = 3),
                 score = rnorm(15))

df$score[c(3,7,10,14)] <- NA

#    id      score
# 1   1 -1.4666164
# 2   1  0.4392647
# 3   1         NA
# 4   2 -0.6010311
# 5   2  1.9845774
# 6   2  0.1749082
# 7   3         NA
# 8   3 -0.3089731
# 9   3  0.4427471
# 10  4         NA
# 11  4  1.7156319
# 12  4 -0.2354253
# 13  5  1.1781350
# 14  5         NA
# 15  5  0.0642082

I can use slice_max to get the maximum in each group:

df %>%
  group_by(id) %>%
    slice_max(score)

#      id score
#   <int> <dbl>
# 1     1 0.439
# 2     2 1.98 
# 3     3 0.443
# 4     4 1.72 
# 5     5 1.18

But how do I get the maximum plus any NAs returned?

CodePudding user response：

We can group_by the id column, then use summarize to output the summaries with max. Here, two max are used, with one of them has na.rm = T and the other doesn't. union() is used to combine output that is present in both max.

library(dplyr)

df %>% 
  group_by(id) %>% 
  summarize(score = union(max(score, na.rm = T), max(score)))

UPDATE: The above code only works if you have at most one NA per ID. Thanks @KU99 for the reminder.

If you have more than one NA per ID, you need to combine the result of max with the records of NA found by is.na().

df %>% 
  group_by(id) %>% 
  summarize(score = c(max(score, na.rm = T), score[is.na(score)]))

Result

# A tibble: 9 × 2
# Groups:   id [5]
     id  score
  <int>  <dbl>
1     1  0.735
2     1 NA    
3     2  0.314
4     3  0.994
5     3 NA    
6     4  0.847
7     4 NA    
8     5  1.95 
9     5 NA

Data

df <- structure(list(id = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 
4L, 4L, 5L, 5L, 5L), score = c(-1.05089006245306, 0.734652105895187, 
NA, -1.31427279695036, -0.250038722057874, 0.314204596436828, 
NA, 0.994420599790523, 0.855768431757766, NA, 0.834325037545013, 
0.846790152407738, 1.95410525460771, NA, 0.971120269710021)), row.names = c(NA, 
-15L), class = "data.frame")

CodePudding user response：

One option would be to use slice and | to create a logical condition with is.na to return the NA rows and the max rows.

library(dplyr)

df %>%
  group_by(id) %>% 
  slice(which(score == max(score, na.rm = T)|is.na(score)))

Another option would be to use slice.max as you did but then to use bind_rows to add the NA values back to the dataframe.

library(dplyr)

df %>% 
  group_by(id) %>%
  slice_max(score) %>% 
  bind_rows(df %>% filter(is.na(score))) %>% 
  arrange(id)

Output

     id   score
  <int>   <dbl>
1     1 -0.161 
2     1 NA     
3     2  1.49  
4     3 -0.451 
5     3 NA     
6     4  0.878 
7     4 NA     
8     5 -0.0652
9     5 NA

Data

df <- structure(list(id = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 
4L, 4L, 5L, 5L, 5L), score = c(-0.161217942983375, -0.456571996252207, 
NA, 0.540071362460494, 1.49325799630099, -0.17985218510166, NA, 
-0.451301758592, -0.839100876399644, NA, -0.0432130218441599, 
0.87779273806634, -0.339260854059069, NA, -0.065177224102029)), row.names = c(NA, 
-15L), class = "data.frame")

CodePudding user response：

Using a custom function you could do:

library(dplyr)

set.seed(123)

slice_max_na <- function(.data, order_by, ..., n, prop, with_ties = TRUE) {
  bind_rows(
    slice_max(.data, order_by = {{order_by}}, ..., n = n, prop = prop, with_ties = with_ties),
    filter(.data, is.na({{order_by}})),
  )
}

df %>%
  group_by(id) %>%
  slice_max_na(score)
#> # A tibble: 9 × 2
#> # Groups:   id [5]
#>      id  score
#>   <int>  <dbl>
#> 1     1 -0.230
#> 2     2  1.72 
#> 3     3 -0.687
#> 4     4  1.22 
#> 5     5  0.401
#> 6     1 NA    
#> 7     3 NA    
#> 8     4 NA    
#> 9     5 NA

CodePudding user response：

Here is dplyr version more using rank:

library(dplyr)

df %>%
  group_by(id) %>%
  mutate(rank = rank(-score, ties.method = "random")) %>% 
  filter(rank == 1 | is.na(score)) %>% 
  select(-rank)

     id  score
  <int>  <dbl>
1     1  0.505
2     1 NA    
3     2 -0.109
4     3 NA    
5     3  1.45 
6     4 NA    
7     4  0.355
8     5 NA    
9     5 -0.298