Create new column based on values from three other columns in R

user_12208050 0
Viewed 187

I have a dataframe:

df <- data.frame('a'=c(1,NA,3,NA,NA), 'b'=c(NA,NA,NA,4,50), 'c'=c(NA,5,NA,NA,NA))
df
   a  b  c
1  1 NA NA
2 NA NA  5
3  3 NA NA
4 NA  4 NA
5 NA 50 NA

I need to create a new column d that combines only the values without the NAs:

  a  b  c  d
1  1 NA NA 1
2 NA NA  5 5
3  3 NA NA 3
4 NA  4 NA 4
5 NA 50 NA 50
r
4 Answers

Additional to the solution by @r2evans in the comment section:

We could use coalesce from dplyr package:

df %>% 
  mutate(d = coalesce(a, b, c))

   a  b  c  d
1  1 NA NA  1
2 NA NA  5  5
3  3 NA NA  3
4 NA  4 NA  4
5 NA 50 NA 50

OR

We could use unite from tidyr package with na.rm argument:

library(tidyr)
library(dplyr)

df %>% 
  unite(d, a:c, na.rm = TRUE, remove = FALSE)

   d  a  b  c
1  1  1 NA NA
2  5 NA NA  5
3  3  3 NA NA
4  4 NA  4 NA
5 50 NA 50 NA
user_133216470

You can simply do the following without using any libraries:

df$d <- apply(df, MARGIN=1, function(x) x[!is.na(x)])

   a  b  c  d
1  1 NA NA  1
2 NA NA  5  5
3  3 NA NA  3
4 NA  4 NA  4
5 NA 50 NA 50

It works as: The function, function(x) x[!is.na(x)], which takes a vector as input and return the non-na element from it, is applied to every row (margin=1).

user_78181490

A vectorized option in base R is max.col to get the column index of non-NA, cbind with row sequence and extract the element

df$d <- df[cbind(seq_len(nrow(df)), max.col(!is.na(df)))]

-output

> df
   a  b  c  d
1  1 NA NA  1
2 NA NA  5  5
3  3 NA NA  3
4 NA  4 NA  4
5 NA 50 NA 50
user_37322710

Another option is to use fcoalesce from data.table, which takes a dataframe and allows you to forgo having to add the column names.

library(data.table)

df$d <- fcoalesce(df)

Output

   a  b  c  d
1  1 NA NA  1
2 NA NA  5  5
3  3 NA NA  3
4 NA  4 NA  4
5 NA 50 NA 50

Or we could use do.call and pmax:

df$d <- do.call(pmax, c(df, list(na.rm=TRUE)))
user_152931910
Related