How to identify which columns are not “NA” per row in a dataframe?

Question

This is the same question as How to identify which columns are not "NA" per row in a matrix? but for a dataframe and not a matrix.

I have a dataframe with 205 rows and 62 columns. I would like to identify the column names for each row that do not have NA as their value. E.g.:

        col1 col2 col3 col4 col5 col6
row1    NA   NA   NA   NA   fact fact
row2    num  num  NA   NA   NA   NA
row3    num  num  NA   NA   int  int    
row4    NA   NA   NA   NA   fact fact
row5    NA   NA   int  int  NA   NA
row6    NA   NA   int  NA   NA   NA

The result should then be:

row1    col5;col6
row2    col1;col2
row3    col1;col2;col5;col6
row4    col5;col6
row5    col3;col4
row6    col3

Ideally the result should be added to the dataframe as an extra column.

`df$non_nas <- apply(!is.na(df), 1, function(x) paste(colnames(df)[x], collapse = ";"))` — Bas, Nov 03 '20 at 08:37
An option using `which` and `tapply` : `idx <- which(!is.na(dat), arr.ind = TRUE); stack(tapply(row.names(idx), names(dat)[idx[, 'col']], toString))` — markus, Nov 03 '20 at 08:47

score 2 · Accepted Answer · answered Nov 03 '20 at 08:37

2

Loop through rows, get column names with for non-na columns, then paste:

d$myCol <- apply(d, 1, function(i) paste(colnames(d)[ !is.na(i) ], collapse = ","))

answered Nov 03 '20 at 08:37

zx8754

52,746
12
114
209

score 1 · Answer 2 · answered Nov 03 '20 at 09:15

Similar logic to @zx8754 using dplyr you could do :

library(dplyr)

df %>%
  tibble::rownames_to_column('row') %>%
  rowwise() %>%
  mutate(res = toString(names(df)[!is.na(c_across(starts_with('col')))])) %>%
  select(row, res)

#   row   res                   
#  <chr> <chr>                 
#1 row1  col5, col6            
#2 row2  col1, col2            
#3 row3  col1, col2, col5, col6
#4 row4  col5, col6            
#5 row5  col3, col4            
#6 row6  col3

How to identify which columns are not “NA” per row in a dataframe?

2 Answers2

Linked