Add a column with count of NAs and Mean

Question

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.

Lengel

This generally isn't a forum to ask us to write your code for you. What have you tried? Why do you want to use dplyr? FWIW, this can be done in base R quite easily any number of ways. One is: df1$na <- apply(is.na(df1), 1, sum) – Nowak

The dplyr way is described here: #21818681 – Oldfangled

Answer 1

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))

Cloudburst

How would you modify this solution to work only on elected columns? For instance b & c? – Seek

@radek see my answer on this page – Tyrontyrone

@radek - I updated the solution to answer your question. – Cloudburst

Answer 2

As mentioned here https://mcmap.net/q/584929/-count-number-of-values-in-row-using-dplyr

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'))))))

Tyrontyrone

Answer 3

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))

Multivalent

Answer 4

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"))

Ungrudging