使用sqldf处理NA值

Question

如果我试图使用SQL中的AVG函数计算c(NA, NA, 3, 4, 5, 6, 7, 8, 9, 10)的平均值，得到的结果是5.2，而不是期望的6.5。

# prepare data and write to file
write.table(data.frame(col1 = c(NA, NA, 3:10)),
        "my.na.txt", row.names = FALSE)

mean(c(NA, NA, 3:10), na.rm = TRUE) # 6.5

my.na <- read.csv.sql("my.na.txt", sep = " ",
        sql = "SELECT AVG(col1) FROM file") # 5.2

# this is identical to
sum(3:10)/10

unlink("my.na.txt") # remove file

这让我相信sql（df）将NA值视为零。在SQL调用中是否可能忽略（排除）NA值，就像在R中使用na.rm参数一样？

- Roman Luštrik

---

“?sqldf” 说：“sqldf 使用的 dbWriteTable/sqliteImportFile 程序来将文件传输到数据库中，这些程序用于加速，但灵活性不如 read.table。”虽然可能不是最理想的解决方案，但可以使用 read.csv.sql 的 filter 参数在将行读入 SQLite 之前过滤掉 NA 行。 在“?sqldf”和 sqldf 主页上有一些关于 filter 参数的示例：http://sqldf.googlecode.com。 - G. Grothendieck

Answer 1

修改查询以忽略 NA 值：

SELECT AVG(col1)
FROM file
WHERE col1 IS NOT \"NA\"

- mathematical.coffee

---

哼，我正在使用 SELECT AVG(col1) FROM file WHERE col1 IS NOT NULL，但仍然得到5.2。 - Roman Luštrik

数据库中的值是N/A还是NULL？（从文件中选择col1 where col1不为null）[NA会显示吗？] 或者（选择Col1 from file where col1 <> 'NA' [NA会显示吗？]）。 - xQbert

使用 SELECT AVG(col1) FROM file WHERE col1 IS NOT \"NA\" 是完全正常的。诀窍在于NAs被引用，你需要转义它们。感谢你们的提示。 - Roman Luštrik

Answer 2

问题在于read.csv.sql函数无法识别缺失值，并将其转换为零，而不是NULL。如果您先将数据加载到data.frame中，然后再调用sqldf，则不会发生这种情况。

d <- read.csv("my.na.txt")
sqldf("SELECT AVG(col1) FROM d") # 6.5

- Vincent Zoonekynd

---

在我的情况下，将数据加载到数据框中是不可能的。 - Roman Luštrik

Answer 3

供日后参考。在研究这个问题时，我发现了这个帖子，解决方案可以在NA的定义中找到。当转移到SQLite时，NAs会被转换为NULL，然后您可以使用IS NULL或IS NOT NULL来排除它们。

- Fredrik Karlsson