Extract a dplyr tbl column as a vector

Question

Is there a more succinct way to get one column of a dplyr tbl as a vector, from a tbl with database back-end (i.e. the data frame/table can't be subset directly)?

require(dplyr)

db <- src_sqlite(tempfile(), create = TRUE)

iris2 <- copy_to(db, iris)

iris2$Species

# NULL

That would have been too easy, so

collect(select(iris2, Species))[, 1]

# [1] "setosa"     "setosa"     "setosa"     "setosa"  etc.

But it seems a bit clumsy.

Answer 1

To extract a column as a vector, you can use the pull function from the dplyr package.

The basic syntax is given below:

pull(.data, var =)

.data:- A table of data

 

var :-A variable specified as:

 

• A literal variable name

 

• A positive integer, giving the position counting from the left

 

• A negative integer, giving the position counting from the right.

 

In your case:

library("dplyr")

db <- src_sqlite(tempfile(), create = TRUE)

iris2 <- copy_to(db, iris)

vec <- pull(iris2, Species)

head(vec)

Output:

[1] "setosa" "setosa" "setosa" "setosa" "setosa" "setosa"