R library(sqldf) cannot change the contain of a column with na( null)

I have a column where I want to replace the null value( the column only contain na) with a value, when I use

library(sqldf)
options(sqldf.driver = "SQLite")
var1<-sqldf("select col1, case when col2 is \"NA\" then 'sth' else 'sth' end as col2 from table1")

It is not changing anything, i have also tried

 var1<-sqldf("select col1, case when col2 is null then 'sth' else 'sth' end as col2 from table1")

and simply try to replace all ( the whole column is with null):

var1<-sqldf("select col1, 'sth'as col2 from table1")

Not of these work, in order to get it work I have to create a new column like

var1<-sqldf("select col1, 'sth'as col2_sth from table1") 

but I need to keep the column with the new value, what can I do in this case? Is it a bug in sqldf?

1 answer

  • answered 2018-07-12 08:06 Juan Antonio Roldán Díaz

    If all your column is NA, probably your problem is that the column is numeric and you can not enter characters without changing the class, see the example:

    library(sqldf)
    options(sqldf.driver = "SQLite")
    table1 <- data.frame(col1 = 1:6, col2 = rep(NA, 6))
    
    sqldf("select col1, case when col2 is NULL then 'sth' else 'sth' end as col2 from table1")
      col1 col2
    1    1   NA
    2    2   NA
    3    3   NA
    4    4   NA
    5    5   NA
    6    6   NA
    Warning message:
    In asfn(rs[[i]]) : NAs introduced by coercion
    

    Changing the class of col2 this works without problems.

    table1$col2 <- as.character(table1$col2)
    sqldf("select col1, case when col2 is NULL then 'sth' else 'sth' end as col2 from table1")
      col1 col2
    1    1  sth
    2    2  sth
    3    3  sth
    4    4  sth
    5    5  sth
    6    6  sth