Extract a dplyr tbl column as a vector

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.

8 Answers

With dplyr >= 0.7.0, you can use pull() to get a vector from a tbl.

library("dplyr") #> #> Attaching package: 'dplyr' #> The following objects are masked from 'package:stats': #> #> filter, lag #> The following objects are masked from 'package:base': #> #> intersect, setdiff, setequal, union db <- src_sqlite(tempfile(), create = TRUE) iris2 <- copy_to(db, iris) vec <- pull(iris2, Species) head(vec) #> [1] "setosa" "setosa" "setosa" "setosa" "setosa" "setosa" 

As per the comment from @nacnudus, it looks like a pull function was implemented in dplyr 0.6:

iris2 %>% pull(Species) 

For older versions of dplyr, here's a neat function to make pulling out a column a bit nicer (easier to type, and easier to read):

pull <- function(x,y) {x[,if(is.name(substitute(y))) deparse(substitute(y)) else y, drop = FALSE][[1]]} 

This lets you do either of these:

iris2 %>% pull('Species') iris2 %>% pull(Species) iris2 %>% pull(5) 

Resulting in...

 [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7 15.0 21.4 

And it also works fine with data frames:

> mtcars %>% pull(5) [1] 3.90 3.90 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 3.92 3.07 3.07 3.07 2.93 3.00 3.23 4.08 4.93 4.22 3.70 2.76 3.15 3.73 3.08 4.08 4.43 [28] 3.77 4.22 3.62 3.54 4.11 

A nice way to do this in v0.2 of dplyr:

iris2 %>% select(Species) %>% collect %>% .[[5]] 

Or if you prefer:

iris2 %>% select(Species) %>% collect %>% .[["Species"]] 

Or if your table isn't too big, simply...

iris2 %>% collect %>% .[["Species"]] 

You can also use unlist which I find easier to read because you do not need to repeat the name of the column or specify the index.

iris2 %>% select(Species) %>% unlist(use.names = FALSE) 

I would use the extract2 convenience function from magrittr:

library(magrittr) library(dplyr) iris2 %>% select(Species) %>% extract2(1) 

I'd probably write:

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

Since dplyr is designed for working with tbls of data, there's no better way to get a single column of data.

@Luke1018 proposed this solution in one of the comments:

You can also use the magrittr exposition operator (%$%) to pull a vector from a data frame.

For example:

iris2 %>% select(Species) %>% collect() %$% Species 

I thought it deserved its own answer.

If you are used to using square brackets for indexing, another option is to just to wrap the usual indexing approach in a call to deframe(), e.g.:

library(tidyverse) iris2 <- as_tibble(iris) # using column name deframe(iris2[, 'Sepal.Length']) # [1] 5.1 4.9 4.7 4.6 5.0 5.4 # using column number deframe(iris2[, 1]) # [1] 5.1 4.9 4.7 4.6 5.0 5.4 

That and pull() are both pretty good ways of getting a tibble column.

Another faster way to extract a column as a vector is convert dataframe to list using c() function and then:

c(iris)$Species c(iris)$Sepal.Length 

Column to vector using a dplyr approach:

iris %>% select(Sepal.Length) %>% as.matrix() %>% as.vector() 

In case you want All the values of the dataset as a vector you simply do:

# I have this tibble: iris %>% as_tibble() %>% head(3) Sepal.Length Sepal.Width Petal.Length Petal.Width Species <dbl> <dbl> <dbl> <dbl> <fct> 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3 1.4 0.2 setosa 3 4.7 3.2 1.3 0.2 setosa 

Do this for column values order (5.1, 4.9 ,4.7,...):

iris %>% as_tibble() %>% as.matrix %>% as.vector() [1] "5.1" "4.9" "4.7" [4] "4.6" "5.0" "5.4" [7] "4.6" "5.0" "4.4" [10] "4.9" "5.4" "4.8" .... [742] "virginica" "virginica" "virginica" [745] "virginica" "virginica" "virginica" [748] "virginica" "virginica" "virginica" 

And do this for row values order (5.1, 3.5, 1.4,...):

iris %>% as_tibble() %>% as.matrix %>% t() %>% as.vector() [1] "5.1" "3.5" "1.4" [4] "0.2" "setosa" "4.9" [7] "3.0" "1.4" "0.2" [10] "setosa" "4.7" "3.2" [13] "1.3" "0.2" "setosa" .... [739] "2.0" "virginica" "6.2" [742] "3.4" "5.4" "2.3" [745] "virginica" "5.9" "3.0" [748] "5.1" "1.8" "virginica"