Merging columns while ignoring NAs

user_5933306 0
Viewed 52

I would like to merge multiple columns. Here is what my sample dataset looks like.

df <- data.frame(
  id = c(1,2,3,4,5),
  cat.1 = c(3,4,NA,4,2),
  cat.2 = c(3,NA,1,4,NA),
  cat.3 = c(3,4,1,4,2))

> df
  id cat.1 cat.2 cat.3
1  1     3     3     3
2  2     4    NA     4
3  3    NA     1     1
4  4     4     4     4
5  5     2    NA     2

I am trying to merge columns cat.1 cat.2 and cat.3. It is a little complicated for me since there are NAs.

I need to have only one cat variable and even some columns have NA, I need to ignore them. The desired output is below:

> df
  id   cat 
1  1     3   
2  2     4  
3  3     1  
4  4     4 
5  5     2

Any thoughts?

dataframe r merge
5 Answers

Another variation of Gregor's answer using dplyr::transmute:

library(dplyr)
df %>% 
  transmute(id = id, cat = coalesce(cat.1, cat.2, cat.3))

 #>   id cat
 #> 1  1   3
 #> 2  2   4
 #> 3  3   1
 #> 4  4   4
 #> 5  5   2
user_64614620

With dplyr:

library(dplyr)
df %>%
  mutate(cat = coalesce(cat.1, cat.2, cat.3)) %>%
  select(-cat.1, -cat.2, -cat.3)
user_9030610

An option with fcoalesce from data.table

library(data.table)
setDT(df)[, .(id, cat = do.call(fcoalesce,  .SD)), .SDcols = patterns('^cat')]

-output

#    id cat
#1:  1   3
#2:  2   4
#3:  3   1
#4:  4   4
#5:  5   2
user_37322710

Does this work:

> library(dplyr)
> df %>% rowwise() %>% mutate(cat = mean(c(cat.1, cat.2, cat.3), na.rm = T)) %>% select(-(2:4))
# A tibble: 5 x 2
# Rowwise: 
     id   cat
  <dbl> <dbl>
1     1     3
2     2     4
3     3     1
4     4     4
5     5     2

Since values across rows are unique, mean of the rows will return the same unique value, can also go with max or min.

user_107227520

Here is a base R solution which uses apply:

df$cat <- apply(df, 1, function(x) unique(x[!is.na(x)][-1]))
user_54368770
Related