Return list of column names with missing (NA) data for each row of a data frame in R

user_12893372 0
Viewed 1061

I am trying to create a list of data with NA values for each ID# in a dataframe so I can keep track of missing data. I have a dataframe where each row is an ID# and each column is a variable name. Each ID# may have different missing data so I want to condense this information down into a two column table. Currently, my table looks something like this:

ID  Var1 Var2 Var3 Var4 Var5
1   10   T    NA   2    NA
2   15   F    50   2    NA
3   12   NA   41   2    NA
4   NA   NA   NA   1    NA
5   NA   F    NA   NA   NA
...

I am hoping to get output that looks something like this:

ID   Missing Variables
1    Var3, Var5
2    Var5
3    Var2, Var5
4    Var1, Var2, Var3, Var5
5    Var1, Var3, Var4, Var5
...

I'm confused how I would return to column name for each missing data entry. I know you can probably do something like this with a for loop but beyond that I get a little lost. Any help is much appreciated!

dplyr na r
4 Answers

Here is a tidyverse solution.

df <- read_table("
ID  Var1 Var2 Var3 Var4 Var5
1   10   T    NA   2    NA
2   15   F    50   2    NA
3   12   NA   41   2    NA
4   NA   NA   NA   1    NA
5   NA   F    NA   NA   NA", col_names = TRUE)

library(dplyr)
library(tidyr)
df %>%
  mutate(across(starts_with("var"), is.na)) %>%  # replace all NA with TRUE and else FALSE
  pivot_longer(-ID, names_to = "var") %>%  # pivot longer
  filter(value) %>%   # remove the FALSE rows
  group_by(ID) %>%    # group by the ID
  summarise(`Missing Variables` = toString(var)) # convert the variable names to a string column

`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 5 x 2
     ID `Missing Variables`   
  <dbl> <chr>                 
1     1 Var3, Var5            
2     2 Var5                  
3     3 Var2, Var5            
4     4 Var1, Var2, Var3, Var5
5     5 Var1, Var3, Var4, Var5
user_129294470

Here is one possible base R approach, which returns a vector:

result <- apply(
  X = is.na(my_df),
  MARGIN = 1,
  FUN = function(x) paste(colnames(my_df)[x], collapse = ", ")
)

> result
[1] "Var3, Var5"             "Var5"                   "Var2, Var5"             "Var1, Var2, Var3, Var5" "Var1, Var3, Var4, Var5"

It looks like you are requesting a data.frame object, and you can get there easily:

data.frame(ID = my_df$ID, `Missing Variables` = result, check.names = FALSE)

# Note that the data.frame specification does not consider variable names
# containing spaces to be syntactically valid, so you have to disable the
# check if you want the variable name you have specified. This may cause
# other problems 'down the line'.

  ID      Missing Variables
1  1             Var3, Var5
2  2                   Var5
3  3             Var2, Var5
4  4 Var1, Var2, Var3, Var5
5  5 Var1, Var3, Var4, Var5

However you also said that you are looking for a list - if so:

> setNames(as.list(result), test$ID)

$`1`
[1] "Var3, Var5"

$`2`
[1] "Var5"

$`3`
[1] "Var2, Var5"

$`4`
[1] "Var1, Var2, Var3, Var5"

$`5`
[1] "Var1, Var3, Var4, Var5"
user_141611890

Does this work:

> library(dplyr)
> df
# A tibble: 5 x 6
     ID  Var1 Var2   Var3  Var4 Var5 
  <dbl> <dbl> <lgl> <dbl> <dbl> <lgl>
1     1    10 TRUE     NA     2 NA   
2     2    15 FALSE    50     2 NA   
3     3    12 NA       41     2 NA   
4     4    NA NA       NA     1 NA   
5     5    NA FALSE    NA    NA NA   
> df$reps <- sapply(apply(df[2:6], 1, function(x) which(is.na(x))), names)
> df %>% unnest(reps) %>% group_by(ID) %>% summarise(`Missing Variables` = paste0(reps, collapse = ', '))
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 5 x 2
     ID `Missing Variables`   
  <dbl> <chr>                 
1     1 Var3, Var5            
2     2 Var5                  
3     3 Var2, Var5            
4     4 Var1, Var2, Var3, Var5
5     5 Var1, Var3, Var4, Var5
user_107227520

A dplyr way of solving it

df <- read.table(text = 'ID  Var1 Var2 Var3 Var4 Var5
1   10   T    NA   2    NA
2   15   F    50   2    NA
3   12   NA   41   2    NA
4   NA   NA   NA   1    NA
5   NA   F    NA   NA   NA', header = T)
library(dplyr)

df %>%
  rowwise() %>%
  summarise(ID, missing = toString(names(.)[-1][seq_along(c_across(starts_with('Var'))) * is.na(c_across(starts_with('Var')))]),
            .groups = 'drop')

#> # A tibble: 5 x 2
#>      ID missing               
#>   <int> <chr>                 
#> 1     1 Var3, Var5            
#> 2     2 Var5                  
#> 3     3 Var2, Var5            
#> 4     4 Var1, Var2, Var3, Var5
#> 5     5 Var1, Var3, Var4, Var5

Created on 2021-05-15 by the reprex package (v2.0.0)

user_28848590
Related