一个 data.table 的问题

yuanfan

事情是酱紫的，楼主用 Rstudio server 连接数据库，需要一次性导入20个表，刚导入时都是 data.frame，然后需要批量转换为 data.table，并且将列名称改成小写，方便后续做其他数据处理。不过遇到了一个问题，如下所示，就是如果把 setDT 这一步写到前面，结果中就没有成功改成小写，而如果把 setDT 这一步写到后面，就成功了。

转换失败的情况

library(data.table)

data1 <- data.frame(TYPE = c(1:3), VALUE = c(2:4))
data2 <- data.frame(TYPE = c(1:3), VALUE = c(2:4))
data3 <- data.frame(TYPE = c(1:3), VALUE = c(2:4))

tables <- mget(paste0("data", c(1:3)))
tables <- lapply(tables, function(dt) {
  setDT(dt)
  setnames(dt, new = tolower(colnames(dt)))
  return(dt)
})

str(data1)

转换成功的情况

library(data.table)

data1 <- data.frame(TYPE = c(1:3), VALUE = c(2:4))
data2 <- data.frame(TYPE = c(1:3), VALUE = c(2:4))
data3 <- data.frame(TYPE = c(1:3), VALUE = c(2:4))

tables <- mget(paste0("data", c(1:3)))
tables <- lapply(tables, function(dt) {
  setnames(dt, new = tolower(colnames(dt)))
  setDT(dt)
  return(dt)
})

str(data1)

yCue

data.table version 1.17.0,
两种代码转换都能成功。

yuanfan

yCue

你好，我把 data.table 升级到1.17.0以后还是转换失败呀。
第一段执行str(data1)后还是得到下面这样的，字段名称没能转成小写。

Classes ‘data.table’ and 'data.frame':	3 obs. of  2 variables:
 $ TYPE : int  1 2 3
 $ VALUE: int  2 3 4

第二段执行str(data1)后得到的是下面这样的，字段名称才成功转成小写。

Classes ‘data.table’ and 'data.frame':	3 obs. of  2 variables:
 $ type : int  1 2 3
 $ value: int  2 3 4

fenguoerbian

yuanfan

我这里也是一样的结果。搜索了一下发现在函数内对参数调用setDT()的行为有点复杂，能找到不少类似的问题：

在这个类似的github issue的讨论中，最终一个PR被提交了，但是从时间线来看，这个PR一路从1.14被推迟到1.18，还没有被合并进主线。

所以目前来看，比较稳定的做法就是不在函数调用中对参数使用setDT()(我不确定这是否包括了不在管道运算符中使用setDT()），而是尽量先提前准备好data.table对象。

yuanfan

fenguoerbian

关于这个问题，我也试过不放到函数里，而是单独对数据执行，这种情况下 setnames()和setDT()谁先谁后都可以正常起作用。当然把setDT()换成as.data.table()也是一样的。不过我倒是从没想到过在管道里转换数据对象的类型。

另外，我记得坛子里以前有个大佬对 data.table 很在行，可是我只记得大名叫谭显英，用户名是 sh 开头，全称是撒死都想不起来。如果风过耳边你记得的话，可以帮忙艾特大佬来帮俺瞅瞅。

yihui

fenguoerbian 我之前的回复里提到的那个PR，不仅一直被推迟了，而且看起来PR的原作者对R的使用也已经减少了，所以不确定到底什么时候能合并到主线了。

我代表组织去问了一下这个 PR 被合并的可能性，他们的答复如下： https://github.com/Rdatatable/data.table/issues/4783#issuecomment-2792415640 由于这里面的东西超出了我的知识范围，要是你懂他在讲什么就劳烦你回复一下吧。我感觉他们大概的意思也就是你开头说的：

fenguoerbian 所以目前来看，比较稳定的做法就是不在函数调用中对参数使用setDT()(我不确定这是否包括了不在管道运算符中使用setDT()），而是尽量先提前准备好data.table对象。

fenguoerbian

yuanfan

as.data.table() 和 copy()在这方面应该效果一致，都是明确要求进行一次完整的复制，所以行为会更可预期。但是也是因为这样的操作，所以会牺牲性能。

我之前的回复里提到的那个PR，不仅一直被推迟了，而且看起来PR的原作者对R的使用也已经减少了，所以不确定到底什么时候能合并到主线了。

至于大佬，稍微用中文名搜索一下就可以找到，@shrektan

fenguoerbian

我对代码做了一些改动，并增加了一些对内存地址的打印。setDT在处理非data.table的变量的时候会进行shallow copy。从后面打印的地址可以看到，在setDT(dt)之后，dt的地址变了，但是dt内包含的变量，如TYPE，其地址是不变的。而names(dt)的结果，会跟着dt进行变化。

所以第一个版本（先setDT再setnames）这个变量名的修改并没有能够传递到全局环境里的data1，但是对TYPE这一列的修改，却又体现到了全局环境的data1里。

而对第二个版本（先setnames再setDT）这个变量名的修改就传递到了全局变量的data1，但在setDT之后，实际上函数内的dt已经指向了一个新的地址。不过对type的修改还是可以传递到data1，因为他们背后是同一个地址。

另外，因为对tables做了赋值（tables <- lapply(xxx)），所以tables也不是原来的tables了，tabels$data1也不一定是原来的tables$data1或者data1了

第一个版本

library(data.table)

data1 <- data.frame(TYPE = c(1:3), VALUE = c(2:4))
tables <- mget(paste0("data", c(1)))

{
  print(paste0("address of `data1`: ", address(data1)))
  print(paste0("address of `data1$TYPE`: ", address(data1$TYPE)))
  print(paste0("address of `data1$type`: ", address(data1$type)))
  print(paste0("address of `names(data1)`: ", address(names(data1))))
  print(paste0("address of `tables`: ", address(tables)))
  print(paste0("address of `tables$data1`: ", address(tables$data1)))
  print(paste0("address of `tables$data1$TYPE`: ", address(tables$data1$TYPE)))
  print(paste0("address of `tables$data1$type`: ", address(tables$data1$type)))
  print(paste0("address of `names(table$data1)`: ", address(names(tables$data1))))
}
#> [1] "address of `data1`: 000001acdf8be768"
#> [1] "address of `data1$TYPE`: 000001acdf8c1968"
#> [1] "address of `data1$type`: 000001acd6e4df30"
#> [1] "address of `names(data1)`: 000001acdf8beb68"
#> [1] "address of `tables`: 000001acdfe38500"
#> [1] "address of `tables$data1`: 000001acdf8be768"
#> [1] "address of `tables$data1$TYPE`: 000001acdf8c1968"
#> [1] "address of `tables$data1$type`: 000001acd6e4df30"
#> [1] "address of `names(table$data1)`: 000001acdf8beb68"


tables <- lapply(tables, function(dt) {
  print("At the beginning: ")
  print(paste0("address of dt: ", address(dt)))
  print(paste0("address of dt$TYPE: ", address(dt$TYPE)))
  print(paste0("address of names(dt): ", address(names(dt))))
  print(" ")
  
  
  setDT(dt)
  print("After `setDT`")
  print(paste0("address of dt: ", address(dt)))
  print(paste0("address of dt$TYPE: ", address(dt$TYPE)))
  print(paste0("address of names(dt): ", address(names(dt))))
  print(" ")
  
  dt[, TYPE := 11]
  dt[, type := 22]
  print("After `setDT` + `:=`, but before `setnames`: ")
  print(paste0("address of dt: ", address(dt)))
  print(paste0("address of dt$TYPE: ", address(dt$TYPE)))
  print(paste0("address of dt$type: ", address(dt$type)))
  print(paste0("address of names(dt): ", address(names(dt))))
  print(" ")
  
  setnames(dt, new = tolower(colnames(dt)))
  print("After `setnames`")
  print(paste0("address of dt: ", address(dt)))
  print(paste0("address of dt$TYPE: ", address(dt$TYPE)))
  print(paste0("address of dt$type: ", address(dt$type)))
  print(paste0("address of names(dt): ", address(names(dt))))
  print(" ")
  
  
  return(dt)
})
#> [1] "At the beginning: "
#> [1] "address of dt: 000001acdf8be768"
#> [1] "address of dt$TYPE: 000001acdf8c1968"
#> [1] "address of names(dt): 000001acdf8beb68"
#> [1] " "
#> [1] "After `setDT`"
#> [1] "address of dt: 000001acdc1f0e00"
#> [1] "address of dt$TYPE: 000001acdf8c1968"
#> [1] "address of names(dt): 000001acdadfb270"
#> [1] " "
#> [1] "After `setDT` + `:=`, but before `setnames`: "
#> [1] "address of dt: 000001acdc1f0e00"
#> [1] "address of dt$TYPE: 000001acdf8c1968"
#> [1] "address of dt$type: 000001ace20940e8"
#> [1] "address of names(dt): 000001acdadfb270"
#> [1] " "
#> [1] "After `setnames`"
#> [1] "address of dt: 000001acdc1f0e00"
#> [1] "address of dt$TYPE: 000001acd6e4df30"
#> [1] "address of dt$type: 000001acdf8c1968"
#> [1] "address of names(dt): 000001acdadfb270"
#> [1] " "

str(data1)
#> Classes 'data.table' and 'data.frame':   3 obs. of  2 variables:
#>  $ TYPE : int  11 11 11
#>  $ VALUE: int  2 3 4

{
  print(paste0("address of `data1`: ", address(data1)))
  print(paste0("address of `data1$TYPE`: ", address(data1$TYPE)))
  print(paste0("address of `data1$type`: ", address(data1$type)))
  print(paste0("address of `names(data1)`: ", address(names(data1))))
  print(paste0("address of `tables`: ", address(tables)))
  print(paste0("address of `tables$data1`: ", address(tables$data1)))
  print(paste0("address of `tables$data1$TYPE`: ", address(tables$data1$TYPE)))
  print(paste0("address of `tables$data1$type`: ", address(tables$data1$type)))
  print(paste0("address of `names(table$data1)`: ", address(names(tables$data1))))
}
#> [1] "address of `data1`: 000001acdf8be768"
#> [1] "address of `data1$TYPE`: 000001acdf8c1968"
#> [1] "address of `data1$type`: 000001acd6e4df30"
#> [1] "address of `names(data1)`: 000001acdf8beb68"
#> [1] "address of `tables`: 000001acdff04f48"
#> [1] "address of `tables$data1`: 000001acdc1f0e00"
#> [1] "address of `tables$data1$TYPE`: 000001acd6e4df30"
#> [1] "address of `tables$data1$type`: 000001acdf8c1968"
#> [1] "address of `names(table$data1)`: 000001acdadfb270"

第二个版本

data1 <- data.frame(TYPE = c(1:3), VALUE = c(2:4))
tables <- mget(paste0("data", c(1)))

{
  print(paste0("address of `data1`: ", address(data1)))
  print(paste0("address of `data1$TYPE`: ", address(data1$TYPE)))
  print(paste0("address of `data1$type`: ", address(data1$type)))
  print(paste0("address of `names(data1)`: ", address(names(data1))))
  print(paste0("address of `tables`: ", address(tables)))
  print(paste0("address of `tables$data1`: ", address(tables$data1)))
  print(paste0("address of `tables$data1$TYPE`: ", address(tables$data1$TYPE)))
  print(paste0("address of `tables$data1$type`: ", address(tables$data1$type)))
  print(paste0("address of `names(table$data1)`: ", address(names(tables$data1))))
}
#> [1] "address of `data1`: 000001ace29192e8"
#> [1] "address of `data1$TYPE`: 000001ace2919ba8"
#> [1] "address of `data1$type`: 000001acd6e4df30"
#> [1] "address of `names(data1)`: 000001ace29191e8"
#> [1] "address of `tables`: 000001ace2958178"
#> [1] "address of `tables$data1`: 000001ace29192e8"
#> [1] "address of `tables$data1$TYPE`: 000001ace2919ba8"
#> [1] "address of `tables$data1$type`: 000001acd6e4df30"
#> [1] "address of `names(table$data1)`: 000001ace29191e8"

tables <- lapply(tables, function(dt) {
  print("At the beginning: ")
  print(paste0("address of dt: ", address(dt)))
  print(paste0("address of dt$TYPE: ", address(dt$TYPE)))
  print(paste0("address of names(dt): ", address(names(dt))))
  print(" ")
  
  setnames(dt, new = tolower(colnames(dt)))
  print("After `setnames`")
  print(paste0("address of dt: ", address(dt)))
  print(paste0("address of dt$TYPE: ", address(dt$TYPE)))
  print(paste0("address of dt$type: ", address(dt$type)))
  print(paste0("address of names(dt): ", address(names(dt))))
  print(" ")
  
  setDT(dt)
  print("After `setDT`")
  print(paste0("address of dt: ", address(dt)))
  print(paste0("address of dt$TYPE: ", address(dt$TYPE)))
  print(paste0("address of dt$type: ", address(dt$type)))
  print(paste0("address of names(dt): ", address(names(dt))))
  print(" ")
  
  dt[, TYPE := 11]
  dt[, type := 22]
  print("After `setDT` + `:=`: ")
  print(paste0("address of dt: ", address(dt)))
  print(paste0("address of dt$TYPE: ", address(dt$TYPE)))
  print(paste0("address of dt$type: ", address(dt$type)))
  print(paste0("address of names(dt): ", address(names(dt))))
  print(" ")
  
  return(dt)
})
#> [1] "At the beginning: "
#> [1] "address of dt: 000001ace29192e8"
#> [1] "address of dt$TYPE: 000001ace2919ba8"
#> [1] "address of names(dt): 000001ace29191e8"
#> [1] " "
#> [1] "After `setnames`"
#> [1] "address of dt: 000001ace29192e8"
#> [1] "address of dt$TYPE: 000001acd6e4df30"
#> [1] "address of dt$type: 000001ace2919ba8"
#> [1] "address of names(dt): 000001ace29191e8"
#> [1] " "
#> [1] "After `setDT`"
#> [1] "address of dt: 000001acdacedb60"
#> [1] "address of dt$TYPE: 000001acd6e4df30"
#> [1] "address of dt$type: 000001ace2919ba8"
#> [1] "address of names(dt): 000001acdf5c5040"
#> [1] " "
#> [1] "After `setDT` + `:=`: "
#> [1] "address of dt: 000001acdacedb60"
#> [1] "address of dt$TYPE: 000001ace3a1ccc8"
#> [1] "address of dt$type: 000001ace2919ba8"
#> [1] "address of names(dt): 000001acdf5c5040"
#> [1] " "

str(data1)
#> Classes 'data.table' and 'data.frame':   3 obs. of  2 variables:
#>  $ type : int  22 22 22
#>  $ value: int  2 3 4

{
  print(paste0("address of `data1`: ", address(data1)))
  print(paste0("address of `data1$TYPE`: ", address(data1$TYPE)))
  print(paste0("address of `data1$type`: ", address(data1$type)))
  print(paste0("address of `names(data1)`: ", address(names(data1))))
  print(paste0("address of `tables`: ", address(tables)))
  print(paste0("address of `tables$data1`: ", address(tables$data1)))
  print(paste0("address of `tables$data1$TYPE`: ", address(tables$data1$TYPE)))
  print(paste0("address of `tables$data1$type`: ", address(tables$data1$type)))
  print(paste0("address of `names(table$data1)`: ", address(names(tables$data1))))
}
#> [1] "address of `data1`: 000001ace29192e8"
#> [1] "address of `data1$TYPE`: 000001acd6e4df30"
#> [1] "address of `data1$type`: 000001ace2919ba8"
#> [1] "address of `names(data1)`: 000001ace29191e8"
#> [1] "address of `tables`: 000001ace2a2b3c0"
#> [1] "address of `tables$data1`: 000001acdacedb60"
#> [1] "address of `tables$data1$TYPE`: 000001ace3a1ccc8"
#> [1] "address of `tables$data1$type`: 000001ace2919ba8"
#> [1] "address of `names(table$data1)`: 000001acdf5c5040"

<sup>Created on 2025-04-08 with reprex v2.1.1</sup>

yuanfan

fenguoerbian

你这个验证的思路，我学到了。虽然经常print，但没想过这么干，谢谢你。

nan.xiao

这是一个很经典的 data.table 工程实践问题。既然你的函数使用了 data.table，那么函数的输入参数应该可以接受 data.frame、data.table 或 tibble 等任何声明了自己也是 data.frame 的对象。

有了这个预期，为了避免以上提到的那些烧脑的复杂情况，你的函数在处理这些输入时的最佳实践就是大不了做一次硬拷贝，以后完全在拷贝上操作，最后保证返回的是编辑过的那个拷贝，从而在语法层面上完全规避按引用去编辑原始输入对象的可能性，也让你的函数完全符合对 R 中函数输出输出语义的典型期待。

想起我们使用 data.table 驱动的软件里还写了不少专门的冒烟测试，就是为了专门防止未来发生这种问题。