If NA exists in column, replace with value in another column - sqldf

Question

I have a dataframe below:

 df

 ColA    ColB    ColC
   NA      BN       6
   JH      NA       8
   NA    rewr       9
   NA      NA      10

Expected Output:

 newdf

 ColA    ColB    ColC   New_Col
   NA      BN       6        BN
   JH      NA       8        JH
   NA    rewr       9      rewr
   NA      NA      10        NA

How do I do this using sqldf?

This was my attempt but it did not get the output I was looking for:

newdf<- sqldf("SELECT *, replace([ColA], NULL, [ColB]) [New_Col] from df")

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

Using coalesce

library(sqldf)
sqldf("SELECT ColA, ColB, ColC, coalesce(ColA, ColB) as New_Col from df")
#   ColA ColB ColC New_Col
#1 <NA>   BN    6      BN
#2   JH <NA>    8      JH
#3 <NA> rewr    9    rewr
#4 <NA> <NA>   10     <NA>

Or with tidyverse

library(dplyr)
df %>%
   mutate(New_Col = coalesce(ColA, ColB))
#   ColA ColB ColC New_Col
#1 <NA>   BN    6      BN
#2   JH <NA>    8      JH
#3 <NA> rewr    9    rewr
#4 <NA> <NA>   10    <NA>

data

df <- structure(list(ColA = c(NA, "JH", NA, NA), ColB = c("BN", NA, 
 "rewr", NA), ColC = c(6L, 8L, 9L, 10L)), class = "data.frame", row.names = c(NA, 
 -4L))

If NA exists in column, replace with value in another column - sqldf

1 Answers1

data