CODEWITHSUNDEEP
rdplyrna
sachinv
sachinv

Reputation: 502

Add a column with count of NAs and Mean

I have a data frame and I need to add another column to it which shows the count of NAs in all the other columns for that row and also the mean of the non-NA values. I think it can be done in dplyr.

> df1 <- data.frame(a = 1:5, b = c(1,2,NA,4,NA), c = c(NA,2,3,NA,NA))
> df1
  a  b  c
1 1  1 NA
2 2  2  2
3 3 NA  3
4 4  4 NA
5 5 NA NA

I want to mutate another column which counts the number of NAs in that row and another column which shows the mean of all the NON-NA values in that row.

Upvotes: 23

Views: 11588

Answers (4)

Shannon Pileggi
Shannon Pileggi

Reputation: 101

I recently faced a variation on this question where I needed to compute the percent of complete values, but for specific variables (not all variables). Here is an approach that worked for me.

df1 %>% 
  # create dummy variables representing if the observation is missing ----
  # can modify here for specific variables ----
  mutate_all(list(dummy = is.na)) %>% 
  # compute a row wise sum of missing ----
  rowwise() %>% 
  mutate(
    # number of missing observations ----
    n_miss = sum(c_across(matches("_dummy"))),
    # percent of observations that are complete (non-missing) ----
    pct_complete = 1 - mean(c_across(matches("_dummy")))
  ) %>% 
  # remove grouping from rowwise ---- 
  ungroup() %>% 
  # remove dummy variables ----
  dplyr::select(-matches("dummy"))

Upvotes: 1

maloneypatr
maloneypatr

Reputation: 3622

library(dplyr)

count_na <- function(x) sum(is.na(x))    

df1 %>%
  mutate(means = rowMeans(., na.rm = T),
         count_na = apply(., 1, count_na))

#### ANSWER FOR RADEK ####
elected_cols <- c('b', 'c')

df1 %>%
  mutate(means = rowMeans(.[elected_cols], na.rm = T),
         count_na = apply(.[elected_cols], 1, count_na))

Upvotes: 24

Jerry T
Jerry T

Reputation: 1690

As mentioned here https://stackoverflow.com/a/37732069/2292993

df1 <- data.frame(a = 1:5, b = c(1,2,NA,4,NA), c = c(NA,2,3,NA,NA))

df1 %>%
  mutate(means = rowMeans(., na.rm = T),
         count_na = rowSums(is.na(.)))

to work on selected cols (the example here is for col a and col c):

df1 %>%
  mutate(means = rowMeans(., na.rm = T),
       count_na = rowSums(is.na(select(.,one_of(c('a','c'))))))

Upvotes: 14

windrunn3r.1990
windrunn3r.1990

Reputation: 414

You can try this:

#Find the row mean and add it to a new column in the dataframe
df1$Mean <- rowMeans(df1, na.rm = TRUE)

#Find the count of NA and add it to a new column in the dataframe
df1$CountNa <- rowSums(apply(is.na(df1), 2, as.numeric))

Upvotes: 8

Related Questions