dbplyr 选择不重复列

dbplyr select not duplicate columns

提问人:stats_noob 提问时间:12/11/2021 最后编辑:Simon.S.A.stats_noob 更新时间:12/13/2021 访问量:292

问:

我正在使用 R 编程语言。我有位于服务器上的下表:

age=18:29
height=c(76.1,77,78.1,78.2,78.8,79.7,79.9,81.1,81.2,81.8,82.8,83.5)
gender=c("M","F","M","M","F","F","M","M","F","M","F","M")
testframe = data.frame(age=age,height=height,height2=height,gender=gender,gender2=gender)

head(testframe)

  age height height2 gender gender2
1  18   76.1    76.1      M       M
2  19   77.0    77.0      F       F
3  20   78.1    78.1      M       M
4  21   78.2    78.2      M       M
5  22   78.8    78.8      F       F
6  23   79.7    79.7      F       F

我的目标是从上面的表中删除相同的列,即使它们具有不同的名称。因此,最终产品将如下所示:

  age height gender
1  18   76.1      M
2  19   77.0      F
3  20   78.1      M
4  21   78.2      M
5  22   78.8      F
6  23   79.7      F

我的问题:我想直接在服务器上从表中删除这些重复的列。我一直在阅读有关 R 中的“dbplyr”库的信息,我不确定这是否能够做到这一点。我尝试了以下两种方法:

方法1:

library(RODBC)
library(sqldf)
library(dplyr)
library(dbplyr)
library(odbc)

con = odbcConnect("some name", uid = "some id", pwd = "abc")

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, testframe)



final_1 = testframe %>%
  dplyr::select(., which(as.list(.) %>%
                           duplicated %>%
                           `!`))

方法2:

mtcars2 <- tbl(con, "testframe")


final_2 = mtcars2 %>%
    dplyr::select(., which(as.list(.) %>%
                               duplicated %>%
                               `!`))

在上面的示例中,数据(“testframe”)在我的本地环境中,但我尝试使用该语句来尝试查看如果数据在服务器上,这些语句是否有效。copy_con

我的问题:上面示例中的代码似乎有效 - 但我不确定上面的代码是否只能对本地环境中的数据执行这些语句 - 或者它也可以对位于服务器上的数据执行这些相同的语句。有人可以对此发表评论吗?

谢谢!

R 数据库 DPLYR 数据操作 DBPLYR

评论

2赞 Ben 12/12/2021
如果我理解正确,您不能使用动词(作为 的一部分)来更改远程数据库数据。我相信这是设计使然,保持源不可变。其他选项是像您所做的那样操作数据,然后使用 或 保存生成的 data.frame ,或者使用不同的自定义 SQL 语句,例如 or(取决于您的确切需求)或类似的东西。dplyrdbplyrcopy_todbWriteTableALTERUPDATE
2赞 Ben 12/12/2021
此外,正如您在其他示例中所做的那样,您可以查看 SQL 语句并查看正在执行的操作。with 和 不会更改任何数据库数据,而只会检索带有别名的列数据。这就是它被设计的目的。show_querySELECTASFROM
0赞 stats_noob 12/13/2021
@Ben:非常感谢您的回复!这是有道理的——你不能直接覆盖原来的表——但你可以做一个表的副本,然后使用“dplyr verbs”直接操作这个表?
0赞 stats_noob 12/13/2021
stackoverflow.com/questions/70231544/......我想知道有没有办法在里面做“模糊连接”,以便它们可以以相同的方式运行?

答:

1赞 Simon.S.A. 12/13/2021 #1

dbplyr 的工作原理是将 dplyr 命令转换为相应的数据库语言,然后再将这些命令传递到数据库。我强烈怀疑它会很难翻译,因为你要求它翻译这些指令的含义,而不是字面上的命令对命令的翻译。which(as.list(.) %>% duplicated %>% '!')

您可以使用模拟数据库连接来测试 dbplyr 转换。例如:

library(dplyr)
library(dbplyr)

age=18:29
height=c(76.1,77,78.1,78.2,78.8,79.7,79.9,81.1,81.2,81.8,82.8,83.5)
gender=c("M","F","M","M","F","F","M","M","F","M","F","M")
testframe = data.frame(age=age,height=height,height2=height,gender=gender,gender2=gender)

remote_table = tbl_lazy(testframe , con = simulate_mssql()) # the simulation

remote_table %>%
  mutate(height3 = height2) %>%
  select(height, height2, height3) %>%
  show_query()

如果这生成了有效的数据库代码,那么您就知道您的 dplyr 命令正在按预期工作。但是,如果这会产生意外的数据库命令(或者翻译中出现 R 命令),则 dbplyr 无法将您的指令翻译成数据库语言。

对于这种类型的问题,我可能会采取如下方法:

all_column_names = colnames(remotetable)
num_cols = length(all_column_names)
is_dupe = rep(0, num_cols)

for(ii in 1:(num_cols - 1)){
  for(jj in 2:num_cols) {
    colA = all_column_names[ii]
    colB = all_column_names[jj]

    this_check = remotetable %>%
      mutate(compare = ifelse(!!sym(colA) == !!sym(colB), 1, 0)) %>%
      ungroup() %>%
      summarise(num = n(), matches = sum(compare)) %>%
      collect()

    if(this_check$num == this_check$matches)
      is_dupe[jj] = 1
  }
}

这个想法是遍历所有列对,并使用基本的 dplyr 命令检查每个值是否相等。如果相等值的数等于值的总数,则将该列作为重复列进行匹配。然后,您可以使用输出来选择非重复列。

笔记

  • !!sym(colA)获取存储在该变量中的文本,并将其转换为 DBPlyr 可以处理的列名。colA
  • collect()是将数据从数据库拉取到本地 R 内存的命令。只有在您确信数据适合本地内存时才执行此操作。

评论

0赞 stats_noob 12/13/2021
@Simon S.A.:非常感谢您的回答!在你编写的第二段代码中 - 当你启动“this_check”时,“this_check”是否在服务器上运行?你提到“collect()”命令会将数据拉取到本地 R 内存中。有没有办法改变这一点,以便可以将“this_check”创建为数据库上的表......一旦你对结果感到满意,那么你可以使用 collect() 语句吗?为此,您是否只需删除“collect()”语句...然后“check_this”将在服务器上创建为新表?非常感谢!
1赞 Simon.S.A. 12/14/2021
数据库表的可编辑方式与 R 内存中的表不同。一种选择是编写一个全新的数据库表(请参阅答案)。但这不是必需的,您可以在调用之前与之交互,它的行为将类似于表(数据库查询的输出是一个表,即使它是动态生成的而不是保存到磁盘)。this_checkcollect()
0赞 stats_noob 12/14/2021
@Simon S.A. : 非常感谢您的回复!我正在研究另一个相关问题(赏金)——如果你有时间,你能看看吗?stackoverflow.com/questions/70231544/......非常感谢您的帮助!