dplyr filter: Get rows with minimum of variable, but only the first if multiple minima

I want to make a grouped filter using dplyr, in a way that within each group only that row is returned which has the minimum value of variable x.

My problem is: As expected, in the case of multiple minima all rows with the minimum value are returned. But in my case, I only want the first row if multiple minima are present.

Here's an example:

df <- data.frame(
A=c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
x=c(1, 1, 2, 2, 3, 4, 5, 5, 5),
y=rnorm(9)
)

library(dplyr)
df.g <- group_by(df, A)
filter(df.g, x == min(x))

As expected, all minima are returned:

Source: local data frame [6 x 3]
Groups: A

  A x           y
1 A 1 -1.04584335
2 A 1  0.97949399
3 B 2  0.79600971
4 C 5 -0.08655151
5 C 5  0.16649962
6 C 5 -0.05948012

With ddply, I would have approach the task that way:

library(plyr)
ddply(df, .(A), function(z) {
	z[z$x == min(z$x), ][1, ]
})

... which works:

  A x           y
1 A 1 -1.04584335
2 B 2  0.79600971
3 C 5 -0.08655151

Q: Is there a way to approach this in dplyr? (For speed reasons)

Update

With dplyr >= 0.3 you can use the slice function in combination with which.min, which would be my favorite approach for this task:

df %>% group_by(A) %>% slice(which.min(x))
#Source: local data frame [3 x 3]
#Groups: A
#
#  A x          y
#1 A 1  0.2979772
#2 B 2 -1.1265265
#3 C 5 -1.1952004

---

Original answer

For the sample data, it is also possible to use two filter after each other:

group_by(df, A) %>% 
  filter(x == min(x)) %>% 
  filter(1:n() == 1)

Just for completeness: Here's the final dplyr solution, derived from the comments of @hadley and @Arun:

library(dplyr)
df.g <- group_by(df, A)
filter(df.g, rank(x, ties.method="first")==1)

For what it's worth, here's a data.table solution, to those who may be interested:

# approach with setting keys
dt <- as.data.table(df)
setkey(dt, A,x)
dt[J(unique(A)), mult="first"]

# without using keys
dt <- as.data.table(df)
dt[dt[, .I[which.min(x)], by=A]$V1]

This can be accomplished by using row_number combined with group_by. row_number handles ties by assigning a rank not only by the value but also by the relative order within the vector. To get the first row of each group with the minimum value of x:

df.g <- group_by(df, A)
filter(df.g, row_number(x) == 1)

For more information see the dplyr vignette on window functions.

dplyr offers slice_min function, wich do the job with the argument with_ties = FALSE

library(dplyr)

df %>% 
  group_by(A) %>% 
  slice_min(x, with_ties = FALSE)

Output :

# A tibble: 3 x 3
# Groups:   A [3]
A         x      y
<fct> <dbl>  <dbl>
1 A         1  0.273
2 B         2 -0.462
3 C         5  1.08 

Another way to do it:

set.seed(1)
x <- data.frame(a = rep(1:2, each = 10), b = rnorm(20))
x <- dplyr::arrange(x, a, b)
dplyr::filter(x, !duplicated(a))

Result:

  a          b
1 1 -0.8356286
2 2 -2.2146999

Could also be easily adapted for getting the row in each group with maximum value.

I like sqldf for its simplicity..

sqldf("select A,min(X),y from 'df.g' group by A")

Output:

A min(X)          y

1 A      1 -1.4836989

2 B      2  0.3755771

3 C      5  0.9284441

In case you are looking to filter the minima of x and then the minima of y. An intuitive way of do it is just using filtering functions:

> df
  A x            y
1 A 1  1.856368296
2 A 1 -0.298284187
3 A 2  0.800047796
4 B 2  0.107289719
5 B 3  0.641819999
6 B 4  0.650542284
7 C 5  0.422465687
8 C 5  0.009819306
9 C 5 -0.482082635  

df %>% group_by(A) %>% 
       filter(x == min(x), y == min(y))
 
# A tibble: 3 x 3
# Groups:   A [3]
  A         x      y
  <chr> <dbl>  <dbl>
1 A         1 -0.298
2 B         2  0.107
3 C         5 -0.482 

This code will filter the minima of x and y.

Also you can do a double filter that looks even more readable:

df %>% group_by(A) %>% 
  filter(x == min(x)) %>%
  filter(y == min(y))

# A tibble: 3 x 3
# Groups:   A [3]
  A         x      y
  <chr> <dbl>  <dbl>
1 A         1 -0.298
2 B         2  0.107
3 C         5 -0.482

For the sake of completeness, here's the base R answer:

df[with(df, ave(x, A, FUN = \(x) rank(x, ties.method = "first")) == 1), ]

#  A x          y
#1 A 1  0.1076158
#4 B 2 -1.3909084
#7 C 5  0.3511618