64

Is it possible to select all unique values from a column of a data.frame using select function in dplyr library? Something like "SELECT DISTINCT field1 FROM table1" in SQL notation.

Thanks!

Robert
  • 5,038
  • 1
  • 25
  • 43
nodm
  • 793
  • 1
  • 6
  • 8

3 Answers3

106

In dplyr 0.3 this can be easily achieved using the distinct() method.

Here is an example:

distinct_df = df %>% distinct(field1)

You can get a vector of the distinct values with:

distinct_vector = distinct_df$field1

You can also select a subset of columns at the same time as you perform the distinct() call, which can be cleaner to look at if you examine the data frame using head/tail/glimpse.:

distinct_df = df %>% distinct(field1) %>% select(field1) distinct_vector = distinct_df$field1

Ron Gejman
  • 6,135
  • 3
  • 25
  • 34
  • 3
    This works if the data frame is already in R, but it doesn't work if you're trying to do the query directly on the database via a db connection (i.e. `src_postgres()`). It reports: `Error: Can't calculate distinct only on specified columns with SQL` – djhocking Jan 15 '15 at 16:28
  • See this question for how to connect the src_postgres() and dplyr http://stackoverflow.com/questions/21592266/i-cannot-connect-postgresql-schema-table-with-dplyr-package – Ron Gejman Mar 08 '15 at 14:43
  • 20
    Note that the way `distinct()` works has changed in dplyr 0.5. By default `distinct()` now only returns the columns that are used as arguments to `distinct()`. If you want to retain the other columns you now have to pass `.keep_all = TRUE` as an additional argument to `distinct()` – RoyalTS Jul 30 '16 at 15:37
  • 2
    Yep, dplyr 0.5 broke my code previously written using 0.3 and distinct. Why the change? The previous default behavior was useful and the natural way to do it. – user1905004 Oct 01 '16 at 00:52
25

Just to add to the other answers, if you would prefer to return a vector rather than a dataframe, you have the following options:

dplyr >= 0.7.0

Use the pull verb:

mtcars %>% distinct(cyl) %>% pull()

dplyr < 0.7.0

Enclose the dplyr functions in a parentheses and combine it with $ syntax:

(mtcars %>% distinct(cyl))$cyl
mhovd
  • 3,724
  • 2
  • 21
  • 47
Josh Gilfillan
  • 4,348
  • 2
  • 24
  • 26
10

The dplyr select function selects specific columns from a data frame. To return unique values in a particular column of data, you can use the group_by function. For example:

library(dplyr)

# Fake data
set.seed(5)
dat = data.frame(x=sample(1:10,100, replace=TRUE))

# Return the distinct values of x
dat %>%
  group_by(x) %>%
  summarise() 

    x
1   1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
10 10

If you want to change the column name you can add the following:

dat %>%
  group_by(x) %>%
  summarise() %>%
  select(unique.x=x)

This both selects column x from among all the columns in the data frame that dplyr returns (and of course there's only one column in this case) and changes its name to unique.x.

You can also get the unique values directly in base R with unique(dat$x).

If you have multiple variables and want all unique combinations that appear in the data, you can generalize the above code as follows:

set.seed(5)
dat = data.frame(x=sample(1:10,100, replace=TRUE), 
                 y=sample(letters[1:5], 100, replace=TRUE))

dat %>% 
  group_by(x,y) %>%
  summarise() %>%
  select(unique.x=x, unique.y=y)
eipi10
  • 91,525
  • 24
  • 209
  • 285