Using dplyr summarise_if() with a predicate

Question

I want to calculate mean of x1 and x2 on days where the ratio of sum(is.NA) and all observations is >= 0.5 or else NA.

Data:

library(lubridate)
library(dplyr)

x = seq(length.out= 10)
x[seq(1,11,5)] <- NA
data = data.frame(
    tseq = seq(from = Sys.time(), length.out = 11, by = "12 hours"),
    x1 = x,
    x2 = x
    )

means = data %>% group_by(tseq=floor_date(tseq, "days")) %>%
    summarise_all(list( mean = ~ mean(., na.rm = TRUE)))

ratio = data %>% group_by(tseq=floor_date(tseq, "days")) %>%
    summarise_all(list( ratio = ~ sum(is.na(.)) / n()))

> ratio
  tseq                x1_ratio x2_ratio
1 2019-08-26 00:00:00      1        1  
2 2019-08-27 00:00:00      0        0  
3 2019-08-28 00:00:00      0        0  
4 2019-08-29 00:00:00      0.5      0.5
5 2019-08-30 00:00:00      0        0  
6 2019-08-31 00:00:00      0.5      0.5

So here 2019-08-26, 2019-08-29, 2019-08-31 dates would get means. In a vector I could accomplish this by function

isEnough = function(x){
    # is there enough values to calculate mean
    if (sum(is.na(x)) / length(x) < 0.5){
        return(FALSE)
    }
    else return(TRUE)
}

For a data frame I can't find a solution. So far I have tried

data %>% group_by(tseq=floor_date(tseq, "days")) %>%
    summarise_if(.predicate =  isEnough(~ sum(is.na(.)), ~n()),
    .funs = list( mean = ~ mean(., na.rm = TRUE)))
Error in naCount/xLength : non-numeric argument to binary operator

data %>% group_by(tseq=floor_date(tseq, "days")) %>%
    summarise_if(.predicate = list( ~ sum(is.na(.)) / n() > 0.5),
    .func = list( mean = ~ mean(., na.rm = TRUE)))
Error: n() should only be called in a data context

data %>% group_by(tseq=floor_date(tseq, "days")) %>%
    summarise_if(.predicate = (~ sum(is.na(.)) / ~n() > 0.5),
    .func = list( mean = ~ mean(., na.rm = TRUE)))
Error in sum(is.na(.))/~n() > 0.5 : 
  non-numeric argument to binary operator

I think you need to also create a condition for numeric columns `summarise_if(~is.numeric(.) &&)` — akrun, Aug 26 '19 at 13:11
For complex predicates I always find it easier to create a boolean column, filter by that and then use the normal summarize. It's longer, but much more interpretable in 6 months. — Amit Kohli, Aug 26 '19 at 13:14
@AmitKohli thanks for suggestion! I created a boolean data frame, but how do I filter by that? ratio[-1] = ratio[-1] >= 0.5 — tRash, Aug 26 '19 at 14:09
you would use the `dplyr::filter()` verb... so in your case, you would do `data %>% filter(ratio >= 0.5)` or something like that, I'm not sure your condition :) — Amit Kohli, Aug 26 '19 at 15:04

score 2 · Accepted Answer · answered Aug 26 '19 at 13:24

summarise_if is used to select columns. See it as a derivative of summarise_at, where you specify which columns to use some functions on.

As it appears you want to calculate the mean of x1 and x2 separately, but under same conditions, I start off by gathering the two columns into one, using tidyr's gather:

library(tidyr)
data %>% gather(x, val, x1, x2) %>% 
  group_by(tseqs=floor_date(tseq, "days"), x) %>% 
  summarise(
    ratio=sum(is.na(val))/n(), 
    mean=mean(val, na.rm=TRUE)*ifelse(ratio >= 0.5, NA, 1)
  )
# A tibble: 12 x 4
# Groups:   tseqs [?]
   tseqs               x     ratio  mean
   <dttm>              <chr> <dbl> <dbl>
 1 2019-08-26 00:00:00 x1      1   NaN  
 2 2019-08-26 00:00:00 x2      1   NaN  
 3 2019-08-27 00:00:00 x1      0     2.5
 4 2019-08-27 00:00:00 x2      0     2.5
 5 2019-08-28 00:00:00 x1      0     4.5
 6 2019-08-28 00:00:00 x2      0     4.5
 7 2019-08-29 00:00:00 x1      0.5  NA  
 8 2019-08-29 00:00:00 x2      0.5  NA  
 9 2019-08-30 00:00:00 x1      0     8.5
10 2019-08-30 00:00:00 x2      0     8.5
11 2019-08-31 00:00:00 x1      0.5  NA  
12 2019-08-31 00:00:00 x2      0.5  NA

Last step is to clean it up, and pack it back into format:

data %>% gather(x, val, x1, x2) %>% 
  group_by(tseqs=floor_date(tseq, "days"), x) %>% 
  summarise(
    ratio=sum(is.na(val))/n(), 
    mean=mean(val, na.rm=TRUE)*ifelse(ratio >= 0.5, NA, 1)
  ) %>%
  select(tseqs, x, mean) %>%
  tidyr::spread(x, mean)
# A tibble: 6 x 3
# Groups:   tseqs [6]
  tseqs                  x1    x2
  <dttm>              <dbl> <dbl>
1 2019-08-26 00:00:00 NaN   NaN  
2 2019-08-27 00:00:00   2.5   2.5
3 2019-08-28 00:00:00   4.5   4.5
4 2019-08-29 00:00:00  NA    NA  
5 2019-08-30 00:00:00   8.5   8.5
6 2019-08-31 00:00:00  NA    NA

Using dplyr summarise_if() with a predicate

1 Answers1