Remove NAs after pivot_wider to match up rows

Question

I spread a column using pivot_wider so I could compare two groups (var1 vs var2) using an xy plot. But I can't compare them because there is a corresponding NA in the column.

Here is an example dataframe:

 df <- data.frame(group = c("a", "a", "b", "b", "c", "c"), var1 = c(3, NA, 1, NA, 2, NA), 
            var2 = c(NA, 2, NA, 4, NA, 8))

I would like it to look like:

df2 <- data.frame(group = c("a", "b", "c"), var1 = c(3, 1, 2), 
            var2 = c( 2,  4, 8))

I don't know why this needs to be solved with `pivot_wider`, as shown in the answers this just looks like group by sum. Possible duplicate https://stackoverflow.com/questions/9723208/aggregate-summarize-multiple-variables-per-group-e-g-sum-mean — Ronak Shah, Nov 13 '19 at 23:47

score 2 · Accepted Answer · answered Nov 13 '19 at 23:00

You can use summarize. But this treats the symptom not the cause. You may have a column in id_cols which is one-to-one with your variable in values_from.

library(dplyr)

df %>%
  group_by(group) %>%
  summarize_all(sum, na.rm = T)

# A tibble: 3 x 3
  group  var1  var2
  <fct> <dbl> <dbl>
1 a         3     2
2 b         1     4
3 c         2     8

score 2 · Answer 2 · edited Aug 24 '21 at 12:08

This solution is a bit more robust, with a slightly more general data.frame to begin with:

df <- data.frame(col_1 = c("A", "A", "A", "A", "A", "A", "B", "B", "B"), 
                 col_2 = c(1, 3, NA, NA, NA, NA, 4, NA, NA),
                 col_3 = c(NA, NA, 2, 5, NA, NA, NA, 5, NA),
                 col_4 = c(NA, NA, NA, NA, 5, 6, NA, NA, 7))

df %>% dplyr::group_by(col_1) %>% 
  dplyr::summarise_all(purrr::discard, is.na)

score 0 · Answer 3 · answered Nov 13 '19 at 23:00

Here is a way to do it, assuming you only have two rows by group and one row with NA

library(dplyr)
df %>% group_by(group) %>% 
       summarise(var1=max(var1,na.rm=TRUE),
                 var2=max(var2,na.rm=TRUE))

The na.rm=TRUE will not count the NAs and get the max on only one value (the one which is not NA)

Remove NAs after pivot_wider to match up rows

3 Answers3