如何使用r在远程PostgreSQL数据库中使用从同一表的另一列中提取的数值来修改新列

Question

我是R的新手，我需要找到一种方法来做以下事情:我可以访问一个巨大的远程PostgreSQL数据库。在这个数据库中，

• 我有一个名为occurrence的表，这个表有一个名为uri的列。
• 它包含一个URI列表(指向网页的链接)。
:abc://abc- def-ghi-abc/12345.
• 列中唯一改变的是数字12345，文本部分(URI)在整个列中保持不变。

我的问题是，我如何才能在同一个表中创建(改变)一个新列，这个新列将被命名为uri_id，并且必须只包含从上面提到的uri列中提取的数字部分。

的例子:

|id|sub_id|uri||
|---|---|---|---|
|3654|5741|abc://abc- def-ghi-abc/12345|
|9784|5742|abc://abc- def-ghi-abc/45789|
|9751|5743|abc://abc- def-ghi-abc/97856|
|9794|5746|abc://abc- def-ghi-abc/69785|
|||||

结果应该如下所示:

|id|sub_id|uri|uri_id|
|---|---|---|---|
|3654|5741|abc://abc-de-fgh.abc/12345|12345|
|9784|5742|abc://abc-de-fgh.abc/45789|45789|
|9751|5743|abc://abc-de-fgh.abc/97856|97856|
|9794|5746|abc://abc-de-fgh.abc/69785|69785|

首先我定义了包含这个列的表:

library(tidyverse)
library(dbplyr)
occurrence <- tbl(db_name, in_schema("metadata", "occurrence"))
print(occurrence) 

正常返回表。然后我试了这个

str_replace(occurrence$uri, "abc://abc- def-ghi-abc/", "")

返回character (0)。打印它或导出它将给出NULL和一个空表。我还试了这个:

uri_id <- mutate(uri_id = as.numeric(str_extract(occurrence$uri, "[0-9]+")))

返回如下错误:

UseMethod("mutate")错误:没有适用于'mutate'的方法应用于类"c('double'， 'numeric')">

我尝试像这样替换文本元素:

uri_id <- mutate(uri_id = as.numeric(gsub(".*?([0-9]+).*", "\1", occurrence$uri)))
print(uri_id)

返回同样的错误!我尝试了extract和extract_:

occurrence$uri %>% extract_(occurrence$uri, "abc://abc- def-ghi-abc/") 

返回错误:

UseMethod("extract_")错误:没有适用于'extract_'的方法应用于类"NULL"的对象

我将非常感谢你帮助我选择正确的方法来完成这项任务。

Answer 1

@AR4891，欢迎来到Stack Overflow!我认为你很好地解释了这些步骤，所以不确定所有的反对票。但我认为这将有助于向我们展示更多的东西，特别是一个最小的，可复制的例子。

我想我认为你依赖tidyverse和dbplyr是对的，所以我添加了这些。我想你应该再读一遍函数描述和例子。让我给你一些例子:

让我们首先创建一个样本数据集。我忽略了dbplyr部分，假设只是一个标题结构。

library(tidyverse)
occurrence <- tibble(
id = c(3654, 9784, 9751, 9794),
sub_id = c(5741, 5742, 5743, 5746),
uri = c(
"abc://abc- def-ghi-abc/12345",
"abc://abc- def-ghi-abc/45789",
"abc://abc- def-ghi-abc/97856",
"abc://abc- def-ghi-abc/69785"
)
)

• str_replace(occurrence$uri, "abc://abc- def-ghi-abc/", "")—>你似乎没有把它存储在任何地方。
• uri_id <- mutate(uri_id = as.numeric(str_extract(occurrence$uri, "[0-9]+")))—>一旦进入mutate，你也不需要通过occurrence$引用数据帧。
• 以上也不是使用tidyr::extract的正确方法。

如果您想采用gsub或str_replace的方法，

occurrence %>%
mutate(uri_id = gsub("abc://abc- def-ghi-abc/", "", uri))

如果您想采用str_extract与regex的方法，

occurrence %>%
mutate(uri_id = as.numeric(str_extract(uri, "[0-9]+")))

让我们看看这两种方法是否有效。