Delete rows base on group max value

Question

RStudio:

Suppose I have a set of data:

# Circle  X    Y   velocity
1 A      21    8     0
2 A      32    17    4
3 A      23    32    5
4 B      22    4     0
5 B      43    12    10
6 B      12    0     2
7 C      12    4     0
.....

I wish to filter out all circles that have a max velocity of >9 (e.g. row 5 show circle B at that point having a velocity of >9 (Circle B's max velocity), therefore row 4-6 should be deleted as they all belong to circle B). Does anyone have any idea how to do this in Rstudio?

score 1 · Answer 1 · answered Mar 06 '21 at 13:52

Try this:

 df<- data.frame(Circle = c('A','A','A','B','B','B','C'), 
                    X=c(21,32,23,22,43,12,12),
                    Y =c(8,17,32,4,12,0,4),
                    velocity=c(0,4,5,0,10,2,0))

df %>% group_by(Circle) %>% filter(max(velocity)<=9)

score 0 · Answer 2 · answered Mar 06 '21 at 13:43

One way to do this would be to keep the groups where all the values are less than equal to 9.

library(dplyr)
df %>% group_by(Circle) %>% filter(all(velocity <= 9))
#Also
#df %>% group_by(Circle) %>% filter(!any(velocity > 9))

#  Circle     X     Y velocity
#  <chr>  <int> <int>    <int>
#1 A         21     8        0
#2 A         32    17        4
#3 A         23    32        5
#4 C         12     4        0

Can also be written in base R and data.table

#Base R
subset(df, ave(velocity <= 9, Circle, FUN = all))

#data table
library(data.table)
setDT(df)[, .SD[all(velocity <= 9)], Circle]

Delete rows base on group max value

2 Answers2