CODEWITHSUNDEEP
rcountaggregatena
user1491868
user1491868

Reputation: 326

How can I count the number of NAs per group?

Could someone please explain why I get different answers using the aggregate function to count missing values by group? Also, is there a better way to count missing values by group using a native R function?

DF <- data.frame(YEAR=c(2000,2000,2000,2001,2001,2001,2001,2002,2002,2002), X=c(1,NA,3,NA,NA,NA,7,8,9,10))
DF

aggregate(X ~ YEAR, data=DF, function(x) { sum(is.na(x)) })
with(DF, aggregate(X, list(YEAR), function(x) { sum(is.na(x)) }))

aggregate(X ~ YEAR, data=DF, function(x) { sum(! is.na(x)) })
with(DF, aggregate(X, list(YEAR), function(x) { sum(! is.na(x)) }))

Upvotes: 15

Views: 23012

Answers (3)

user438383
user438383

Reputation: 6206

An option using dplyr

library(dplyr)

DF %>% 
    group_by(YEAR) %>% 
    summarise(sum_na = sum(is.na(x)))

Or if you want to use across to dynamically select columns:

DF %>% 
    group_by(YEAR) %>% 
    summarise(across(X,  ~sum(is.na(.))))

# A tibble: 3 × 2
   YEAR sum_na
  <dbl>  <int>
1  2000      1
2  2001      3
3  2002      0

Upvotes: 4

Pranay Aryal
Pranay Aryal

Reputation: 5396

library(dplyr)
library(tidyr)

#say you want to get missing values from group 1
dataframe %>% filter(group = 1 & is.na(another_column))

#missing values from group 2
dataframe %>% filter(group = 2 & is.na(another_column))

Upvotes: -3

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193507

The help page at ?aggregate points out that the formula method has an argument na.action which is set by default to na.omit.

na.action: a function which indicates what should happen when the data contain NA values. The default is to ignore missing values in the given variables.

Change that argument to NULL or na.pass instead to get the results you are probably expecting:

# aggregate(X ~ YEAR, data=DF, function(x) {sum(is.na(x))}, na.action = na.pass)
aggregate(X ~ YEAR, data=DF, function(x) {sum(is.na(x))}, na.action = NULL)
#   YEAR X
# 1 2000 1
# 2 2001 3
# 3 2002 0

Upvotes: 21

Related Questions