remove NA values and combine non NA values into a single column

Question

I have a data set which has numeric and NA values in all columns. I would like to create a new column with all non NA values and preserve the row names

    v1  v2  v3  v4  v5
a   1   NA  NA  NA  NA 
b   NA  2   NA  NA  NA
c   NA  NA  3   NA  NA
d   NA  NA  NA  4   NA
e   NA  NA  NA  NA  5

I have tried using the coalesce function from dplyr

digital_metrics_FB <- fb_all_data %>%
                      mutate(fb_metrics = coalesce("v1", 
                                                   "v2", 
                                                   "v3", 
                                                   "v4",
                                                   "v5"))

and also tried an apply function

df2 <- sapply(fb_all_data,function(x) x[!is.na(x)])

still cannot get it to work.

I am looking for the final result to be where all non NA values come together in the final column and the row names are preserved

any help would be much appreciated

Possible [duplicate](https://stackoverflow.com/questions/14563531/combine-column-to-remove-nas). — Rui Barradas, May 03 '19 at 18:48
the solution to a similar question was not working for me so I had to ask — NMB, May 03 '19 at 19:25

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

We can use pmax

do.call(pmax, c(fb_all_data , na.rm = TRUE))

If there are more than one non-NA element and want to combine as a string, a simple base R option would be

data.frame(final = apply(fb_all_data, 1, function(x) toString(x[!is.na(x)])))

Or using coalesce

library(dplyr)
library(tibble)
fb_all_data  %>%
   rownames_to_column('rn') %>%
   transmute(rn, final = coalesce(v1, v2, v3, v4, v5)) %>%
   column_to_rownames('rn')
#   final
#a     1
#b     2
#c     3
#d     4
#e     5

Or using tidyverse, for multiple non-NA elements

fb_all_data %>%
      rownames_to_column('rn') %>%
      transmute(rn, final = pmap_chr(.[-1], ~ c(...) %>% 
                                 na.omit %>%
                                 toString)) %>%
      column_to_rownames('rn')

NOTE: Here we are showing data that the OP showed as example and not some other dataset

data

fb_all_data <- structure(list(v1 = c(1L, NA, NA, NA, NA), v2 = c(NA, 2L, NA, 
 NA, NA), v3 = c(NA, NA, 3L, NA, NA), v4 = c(NA, NA, NA, 4L, NA
  ), v5 = c(NA, NA, NA, NA, 5L)), class = "data.frame",
  row.names = c("a", 
 "b", "c", "d", "e"))

Couldn't we simplify your second approach to `data.frame(final = do.call(coalesce, fb_all_data), row.names = rownames(fb_all_data))` — markus, May 03 '19 at 18:05

score 1 · Answer 2 · answered May 03 '19 at 18:02

With tidyverse, you can do:

df %>%
 rownames_to_column() %>%
 gather(var, val, -1, na.rm = TRUE) %>%
 group_by(rowname) %>%
 summarise(val = paste(val, collapse = ", "))

  rowname val  
  <chr>   <chr>
1 a       1    
2 b       2, 3 
3 c       3    
4 d       4    
5 e       5

Sample data to have a row with more than one non-NA value:

df <- read.table(text = "    v1  v2  v3  v4  v5
a   1   NA  NA  NA  NA 
                 b   NA  2   3  NA  NA
                 c   NA  NA  3   NA  NA
                 d   NA  NA  NA  4   NA
                 e   NA  NA  NA  NA  5", header = TRUE)

remove NA values and combine non NA values into a single column

2 Answers2

data