I wanted to subtract one column from the other in R and this turned out more complicated than I thought.
Suppose this is my data (columns a and b) and column c is what I want, namely a - b but keeping a when b==NA and vice versa:
a b c
1 2 1 1
2 2 NA 2
3 NA 3 3
4 NA NA NA
Now I tried different things but most of the time it returned NA when at least one column was NA. For example:
matrixStats::rowDiffs(data, na.rm=T) # only works for matrix-format, and returns NA's
dat$c <- dat$a - dat$b + ifelse(is.na(dat$b),dat$a,0) + ifelse(is.na(dat$a),dat$b,0) # seems like a desparately basic solution, but not even this does the trick as it also returns NA's
apply(dat[,(1:2)], MARGIN = 1,FUN = diff, na.rm=T) # returns NA's
dat$b<-dat$b*(-1)
dat$c<-rowSums(dat,na.rm=T) # this kind of works but it's a really ugly workaround
Also, if you can think of a dplyr solution, please share your knowledge. I didn't even know what to try.
Will delete this question if you think it's a duplicate of an existing one, though none of the existing threads were particularly helpful.
Try this (Base R Solution):
If df$b is NA then simply take the value of df$a else if df$a is NA then simply take the value of df$b else do df$a-df$b
df$c=ifelse(is.na(df$b),df$a,ifelse(is.na(df$a),df$b,df$a-df$b))
Output:
df
a b c
1 2 1 1
2 2 NA 2
3 NA 3 3
4 NA NA NA