Conditional slicing|filtering top and bottom n rows from grouped data

Question

I have come to an issue that filtering or slicing the top and bottom n number of rows at the same time from the grouped data.

So it is different than this Select first and last row from grouped data

What I need to do that if sub_gr==a then filter|slice top three rows if sub_gr==b then filter|slice bottom two rows that's it!

my data something like this

  df <- data.frame(gr=rep(seq(1,2),each=10),sub_gr=rep(rep(c("a","b"),each=5),2),
                   y = rep(c(sort(runif(5,0,0.5),decreasing=TRUE), sort(runif(5,0,0.5),,decreasing=TRUE)),2),
                   x = rep(c(seq(0.1,0.5,0.1),rev(seq(-0.5,-0.1,0.1))),2))

gr sub_gr          y      x
1   1      a 0.37851909  0.1
2   1      a 0.33305165  0.2
3   1      a 0.22478005  0.3
4   1      a 0.09677654  0.4
5   1      a 0.07060651  0.5
6   1      b 0.41999445 -0.1
7   1      b 0.35356301 -0.2
8   1      b 0.33274398 -0.3
9   1      b 0.20451400 -0.4
10  1      b 0.03714828 -0.5
11  2      a 0.37851909  0.1
12  2      a 0.33305165  0.2
13  2      a 0.22478005  0.3
14  2      a 0.09677654  0.4
15  2      a 0.07060651  0.5
16  2      b 0.41999445 -0.1
17  2      b 0.35356301 -0.2
18  2      b 0.33274398 -0.3
19  2      b 0.20451400 -0.4
20  2      b 0.03714828 -0.5

library(dplyr)

Here is what I tried,

 df%>%
    group_by(gr, sub_gr)%>%
    slice(if(any(sub_gr=="a")) {row_number()==1:3} else {row_number()==4:n()})

Warning messages:
1: In 1:5 == 1:3 :
  longer object length is not a multiple of shorter object length
2: In 1:5 == 4:5L :
  longer object length is not a multiple of shorter object length
3: In 1:5 == 1:3 :
  longer object length is not a multiple of shorter object length
4: In 1:5 == 4:5L :
  longer object length is not a multiple of shorter object length

thanks for your help in advance!

www · Accepted Answer · 2017-11-20T23:22:39.590

There are probably more elegant solutions, but I think the following works. I set seed for reproducibility.

set.seed(123)

df <- data.frame(gr=rep(seq(1,2),each=10),sub_gr=rep(rep(c("a","b"),each=5),2),
                 y = rep(c(sort(runif(5,0,0.5),decreasing=TRUE), sort(runif(5,0,0.5),,decreasing=TRUE)),2),
                 x = rep(c(seq(0.1,0.5,0.1),rev(seq(-0.5,-0.1,0.1))),2))

df %>%
  group_by(gr, sub_gr) %>%
  filter((sub_gr %in% "a" & row_number() %in% 1:3) |
           (sub_gr %in% "b" & row_number() %in% (n() - 1):n())) %>%
  ungroup()

#  # A tibble: 10 x 4
#       gr sub_gr          y     x
#    <int> <fctr>      <dbl> <dbl>
#  1     1      a 0.47023364   0.1
#  2     1      a 0.44150870   0.2
#  3     1      a 0.39415257   0.3
#  4     1      b 0.22830737  -0.4
#  5     1      b 0.02277825  -0.5
#  6     2      a 0.47023364   0.1
#  7     2      a 0.44150870   0.2
#  8     2      a 0.39415257   0.3
#  9     2      b 0.22830737  -0.4
# 10     2      b 0.02277825  -0.5

score 1 · Answer 2 · answered Nov 20 '17 at 23:32

library(tidyverse)
# create a custom function to take the head or tail based on your rule
cond_slice <- function(x) {
  if (unique(x$sub_gr) == "a") {
    head(x, 3)
  } else {
    tail(x, 2)
  }
}
# create a column to split by and then map across the subsets
result <- x %>%
  unite(split_by, gr, sub_gr, remove = F) %>%
  split(.$split_by) %>%
  map(cond_slice) %>% 
  bind_rows() %>%
  select(-split_by)

Conditional slicing|filtering top and bottom n rows from grouped data

2 Answers2