summarise by group returns 0 instead of NA if all values are NA-CodePudding

library(dplyr)

dat <- 
data.frame(id = rep(c(1,2,3,4), each = 3),
           value = c(NA, NA, NA, 0, 1, 2, 0, 1, NA, 1, 2,3))

dat %>%
  dplyr::group_by(id) %>%
  dplyr::summarise(value_sum = sum(value, na.rm = T))

# A tibble: 4 x 2
id value_sum
 1         0
 2         3
 3         1
 4         6

Is there any way I can return NA if all the entries in a group are NA. For e.g. id 1 has all the entries as NA so I want the value_sum to be NA as well.

  # A tibble: 4 x 2
  id value_sum
  1         NA
  2         3
  3         1
  4         6

CodePudding user response：

One way is to use an if/else statement: If all is Na return NA else return sum():

dat %>%
  dplyr::group_by(id) %>%
  #dplyr::summarise(value_sum = sum(value, na.rm = F)) %>% 
  summarise(number = if(all(is.na(value))) NA_real_ else sum(value, na.rm = TRUE))

    id number
  <dbl>  <dbl>
1     1     NA
2     2      3
3     3      1
4     4      6

CodePudding user response：

We could use fsum

library(collapse)
fsum(dat$value, g = dat$id)
 1  2  3  4 
NA  3  1  6

Or with dplyr

library(dplyr)
 dat %>%
   group_by(id) %>% 
   summarise(number = fsum(value))
# A tibble: 4 × 2
     id number
  <dbl>  <dbl>
1     1     NA
2     2      3
3     3      1
4     4      6