Eliminate NAs within each group

user_1553411 0
Viewed 48

I have ended up with rows that have NAs that correspond to a column that was deleted and I want to get rid of those NAs. This would turn the example df below from three rows to one row and leave me one set of variables for each group. 

df <- data.frame(group = c("a", "a", "a"), var1 = c(1, NA, NA), var2 = c(NA, 4, NA), var3 = c(NA, NA, 
2), var4 = c(1, NA, NA), var5 = c(NA, 4, NA), var6 = c(NA, NA, 2), var7 = c(1, NA, NA), 
var8 = c(NA, 4, NA), var3 = c(NA, NA, 2))

I know how to get rid of whole rows with NAs or columns with NAs, but am not sure how to collapse this.

dplyr r
2 Answers

Based on the example, it can be done with summarise_all, assuming that there is only single non-NA element for each column per 'group'

library(dplyr)
df %>%
   group_by(group) %>%
   summarise_all(na.omit)
# A tibble: 1 x 10
#  group  var1  var2  var3  var4  var5  var6  var7  var8 var3.1
#  <fct> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
#1 a         1     4     2     1     4     2     1     4      2
user_37322710

One base R option could be:

aggregate(. ~ group, 
          FUN = function(x) sum(x, na.rm = TRUE), 
          na.action = na.pass, 
          data = df)

  group var1 var2 var3 var4 var5 var6 var7 var8 var3.1
1     a    1    4    2    1    4    2    1    4      2

It also makes the assumption that there is only a single non-NA value per variable per group.

user_59645570
Related