How do you convert all the NAs with 0 in R?

Question

I'm trying to use sqldf

sqldf("update brave set myColumn = 0 where myColumn is null")

Answer 1

My approach:

df <- data.frame(values = c(4, 2, 4, NA, 61, 2, 5, 634, NA, 431, NA, 54352, 323, 45, NA))

df[is.na(df)] <- 0

Changes df from

   values
1       4
2       2
3       4
4      NA
5      61
6       2
7       5
8     634
9      NA
10    431
11     NA
12  54352
13    323
14     45
15     NA

to

   values
1       4
2       2
3       4
4       0
5      61
6       2
7       5
8     634
9       0
10    431
11      0
12  54352
13    323
14     45
15      0

Answer 2

This is a FAQ. See FAQ #8 on https://github.com/ggrothendieck/sqldf . As discussed there update does not return a value. You need to explicitly return one using a select:

library(sqldf)

# test input.  BOD is builtin data frame that comes with R.
BOD$Time[3] <- NA

sqldf(c("update BOD set Time = 0 where Time is null",
        "select * from main.BOD"))

giving

  Time demand
1    1    8.3
2    2   10.3
3    0   19.0
4    4   16.0
5    5   15.6
6    7   19.8

We see the NA was filled in with a zero.

The use of update causes a warning when sqldf is used with the latest version of RSQLite -- version 2.0; howver, it still works correctly so until RSQLite addresses this problem either ignore the warning or use an earlier version of RSQLite.