Create new column by two conditions-CodePudding

I want to create new diff column by differencing as diff= value1 -value3 for non missing values of value3 or diff= value1 -value2 if only value3 is NA.

data<-data.frame(id=c(1,2,3,4,5,6,7,8),
                value1=c(10,12,11,15,16,14,14,11),
                value2=c(9,5,6,10,8,10,6,8),
                value3=c(4,8,7,NA,9,NA,5,NA))

Desired output:

 id value1 value2 value3 diff
   1    10    9    4    6
   2    12    5    8    4
   3    11    6    7    4
   4    15   10    NA   5
   5    16   8     9    7
   6    14   10    NA   4
   7    14   6     5    9
   8    11   8     NA   3

CodePudding user response：

In dplyr you can use coalesce to get non-NA value between value3 and value2.

library(dplyr)

data %>% mutate(diff = value1 - coalesce(value3, value2))

#  id value1 value2 value3 diff
#1  1     10      9      4    6
#2  2     12      5      8    4
#3  3     11      6      7    4
#4  4     15     10     NA    5
#5  5     16      8      9    7
#6  6     14     10     NA    4
#7  7     14      6      5    9
#8  8     11      8     NA    3

In base R with ifelse -

transform(data, diff = value1 - ifelse(is.na(value3), value2, value3))

and data.table has fcoalesce.

library(data.table)
setDT(data)[, diff := value1 - fcoalesce(value3, value2)]