Summing NAs across all columns by group in dplyr

Muhammad Kamil 0
Viewed 32

I have a grouped data frame with some NA values in all columns.

id <- rep(c("a", "b", "c"), 3)
x1 <- c(1, NA, NA, 2, 2, NA, 0, NA, 0)
x2 <- c(1, 2, 3, NA, 12, NA, NA, 4, NA)
df <- cbind.data.frame(id, x1, x2)

I want to group by ID and then summarize the number of NAs across all numeric columns. The resulting data frame should have 3 rows (1 for each ID) and 2 columns (x1 and x2) and should contain the sums of NAs in both columns by ID.

dplyr r
2 Answers
library(dplyr)
df %>% 
  group_by(id) %>% 
  summarise(across(c(x1, x2), ~ sum(is.na(.x))))

or, with aggregate:

aggregate(list(x1 = df$x1, x2 = df$x2), by = list(id = df$id), function(x) sum(is.na(x)))

output

  id       x1    x2
  <chr> <int> <int>
1 a         0     2
2 b         2     0
3 c         2     2
Maël0

Using rowsum in base R

rowsum(+(is.na(df[-1])), df$id, na.rm = TRUE)
  x1 x2
a  0  2
b  2  0
c  2  2
akrun0
Related