当列为因子变量时，R data.table将“NULL”替换为`NA`

Question

我通过ODBC从SQL数据库中提取了一些数据，并且列被自动设置为factor。它看起来像下面这样：

library(RODBC)
library(data.table)
data <- data.table(sqlQuery(channel, query))

我的数据看起来像这样，只是有更多的列：

data <- data.table("C1"=as.factor(c(letters[1:4], "NULL", letters[5])),
                   "C2"=as.factor(c(rnorm(3), "NULL", rnorm(2))),
                   "C3"=as.factor(c(letters[1], "NULL", letters[2:4], "NULL")))
> data
     C1                 C2   C3
1:    a -0.190200079604691    a
2:    b  0.310548914832963 NULL
3:    c 0.0153099116493453    b
4:    d               NULL    c
5: NULL  0.157187027626419    d
6:    e  0.118537540781528 NULL
> str(data)
Classes ‘data.table’ and 'data.frame':  6 obs. of  3 variables:
 $ C1: Factor w/ 6 levels "a","b","c","d",..: 1 2 3 4 6 5
 $ C2: Factor w/ 6 levels "-0.190200079604691",..: 1 5 2 6 4 3
 $ C3: Factor w/ 5 levels "a","b","c","d",..: 1 5 2 3 4 5
 - attr(*, ".internal.selfref")=<externalptr> 

我该如何将 "NULL" 替换为 NA？在这里，我希望 R 将这些 SQL "NULL" 字符串视为缺失值 NA。我尝试了以下方法，但似乎 NA 会导致问题。

for (col in names(data)) {
  set(data, which(data[[col]]=="NULL"), col, NA)
}

> Error in set(data, which(data[[col]] == "NULL"), col, NA) : 
  Can't assign to column 'C1' (type 'factor') a value of type 'logical' (not character, factor, integer or numeric)

RODBC解决方案

感谢@user20650的建议，您可以通过执行data <- data.table(sqlQuery(channel, query, na.strings=c("NA", "NULL")))来控制sqlQuery中的缺失值。然而，如果您的数据源格式不正确，仍有可能出现此问题，因此这不是该帖子的通用解决方案。

- Boxuan

---

请注意，“NULL”不是NULL。 - IRTFM

@BondedDust 是的，在我的数据中，“NULL”是从SQL中提取的字符串。我希望R将它们替换为缺失值。 - Boxuan

在R中，因素可以变得异常/打破语言和人类直觉的规则。所以，这可能不是一个错误。 - Frank

你能不能在sqlQuery中不使用na.strings = c("NA", "NULL")或类似的方法，以便在读取时将值设置为缺失？ - user20650

@user20650 那是个好点子。我会尝试一下的。 - Boxuan

Answer 1

这样做可以达到预期的效果，而且更加紧凑：

is.na(data) <- data == "NULL"

关于评论Q的说明：函数is.na与is.na<-非常不同。后者是将NA值分配给定义在赋值运算符右侧逻辑表达式上的项目。虽然有一个is.na.data.frame方法，但没有is.na[<-.dataframe方法。因此，我不确定这是否是纯粹的按引用策略，因为它没有使用[.data.frame语法实现。它可能正在使用“is.na<-.default”。

我认为，在进一步探索后，“is.na<-.default”（这只是{x[value] <- NA; x}）所以最终会将此调用分派到[<-.data.table，因此可能会通过引用进行处理。

- IRTFM

---

有人能解释一下如何读取它吗？解释一下它的含义？ 我的理解是is.na(data)返回一个类似于T或F值的结构，具体取决于is.na()。data == '"NULL"'似乎做了同样的事情，所以不清楚这个赋值语句的作用是什么。 - Chris

将“data”中等于字符串“NULL”的任何位置设置为NA_character值。请记住，在R中，“NULL”与NULL不同。 - IRTFM

Answer 2

这是一种方法：

data[,names(data):=lapply(.SD,function(x){
  z <- levels(x)
  z[z=="NULL"] <- NA
  `levels<-`(x,z)
})]

要了解发生了什么，请查看lapply(data,levels)，您会发现"NULL"已经消失了。

---

（感谢@akrun :) 使用car包可以获得更简洁和直观的变体：）

library(car)
data[,names(data):=lapply(.SD, recode, '"NULL"=NA')]

---

在 data.table 的世界里，通常可以通过引用进行修改。在这种情况下，看起来像是...

for (j in names(data)) setattr(data[[j]],"levels",{
  z <- levels(data[[j]])
  z[z=="NULL"] <- NA
  z
})

这样做避免了复制整个向量，就像`levels<-`一样。

- Frank

---

有人能提醒我为什么没有这样做吗：is.na(data) <- data == "NULL" - IRTFM

我在上面贴了类似的东西@BondedDust；我认为问题在于NULL仍然是一个级别，所以需要使用droplevels或类似的函数。 - user20650

@BondedDust，我之前没有想到过这个问题，也没有在更高的评论中看到它。我只使用因子进行表格处理，所以对此并不十分了解。看起来这是一个不错的方法。如果向量很大或者其他原因，通过引用修改级别可能会更好一些，但如果\is.na<-``能够工作，那么这是最清晰明了的方法。 - Frank

另一个选项是 library(car);data[, lapply(.SD, recode, '"NULL"=NA')]，它会自动删除水平并保持真实的 NA。 - akrun

我没有任何修改建议，但在阅读您的代码后想知道是否可以在SQL数据库和R之间插入fread功能，以神奇地完成列类型转换。 - IRTFM