omit NAs when tallying using dplyr summarise

dplyr, r

Solution

Try

df %>%
    summarise_each(funs(total.count=sum(!is.na(.)), positive.count=sum(.,na.rm=T),positive.pctg=sum(.,na.rm=T)*100/sum(!is.na(.))))%>%
    gather(key,fxn,x1_total.count:x5_positive.pctg) %>%
    separate(key,c("col","funcn"),sep="\\_") %>%
    spread(funcn,fxn)

Problem

My question involves summarising a dataframe with multiple columns(50 columns) using the `summarise_each` function in dplyr. The data entries in the columns are binary(0=negative, 1=positive) and I aim to get the colsums and percentage positives. The issue is that some columns have NAs and I wish to exclude these in the calculations of totals and percentages. Below is a minimal example: ``` library(dplyr) library(tidyr) df=data.frame( x1=c(1,0,0,NA,0,1,1,NA,0,1), x2=c(1,1,NA,1,1,0,NA,NA,0,1), x3=c(0,1,0,1,1,0,NA,NA,0,1), x4=c(1,0,NA,1,0,0,NA,0,0,1), x5=c(1,1,NA,1,1,1,NA,1,0,1)) > df x1 x2 x3 x4 x5 1 1 1 0 1 1 2 0 1 1 0 1 3 0 NA 0 NA NA 4 NA 1 1 1 1 5 0 1 1 0 1 6 1 0 0 0 1 7 1 NA NA NA NA 8 NA NA NA 0 1 9 0 0 0 0 0 10 1 1 1 1 1 df %>% summarise_each(funs(total.count=n(), positive.count=sum(.,na.rm=T),positive.pctg=sum(.,na.rm=T)*100/n())) %>% gather(key,fxn,x1_total.count:x5_positive.pctg) %>% separate(key,c("col","funcn"),sep="\\_") %>% spread(funcn,fxn) col positive.count positive.pctg total.count 1 x1 4 40 10 2 x2 5 50 10 3 x3 4 40 10 4 x4 3 30 10 5 x5 7 70 10 ``` What I was hoping to get in the table above is for example, the total(total.count) for x1 as: ``` length(df$x1[!is.na(df$x1)]) [1] 8 ``` Instead I get an equivalent of the following, which includes the NAs: ``` length(df$x1) [1] 10 ``` and I also want the percentage(positive.pctg) for x1 as: ``` sum(df$x1,na.rm=T)/length(df$x1[!is.na(df$x1)]) [1] 0.5 ``` Instead I get an equivalent of the following, which includes the NAs: ``` sum(df$x1,na.rm=T)/length(df$x1) [1] 0.4 ``` How can I do the the count in dplyr ommiting NAs? it seems the functions `n()` or `length()` do not take any arguments like `na.omit/na.rm/complete.cases`. Any assistance would be greatly appreciated.

Original source