Home > front end >  How do I drop all the rows within a group when one of the observations meets a certain condition?
How do I drop all the rows within a group when one of the observations meets a certain condition?

Time:10-23

I need to know how to drop all the rows prior to a certain point in a data.frame (DF1)

I want to drop all the rows before the row with the lowest number (grouped by ID and arranged by Date)

DF1
ID     Date       Value
1      2/11/2021  0    
1      2/12/2021  2         
1      2/13/2021  1            
1      2/14/2021  0            
1      2/15/2021  1            
1      2/16/2021  -37            
1      2/17/2021  0           
1      2/18/2021  1            
1      2/19/2021  -2

The data frame I'm trying to get to (DF2):

DF2
ID     Date       Value       
1      2/16/2021  -37            
1      2/17/2021  0           
1      2/18/2021  1            
1      2/19/2021  -2

Thanks!

CodePudding user response:

You can use -

library(dplyr)

df %>% 
  mutate(Date = as.Date(Date, '%m/%d/%Y')) %>%
  arrange(ID, Date) %>%
  group_by(ID) %>% 
  slice(which.min(Value):n()) %>%
  ungroup


#     ID Date       Value
#  <int> <date>     <int>
#1     1 2021-02-16   -37
#2     1 2021-02-17     0
#3     1 2021-02-18     1
#4     1 2021-02-19    -2

CodePudding user response:

We may do

library(dplyr)
library(lubridate)
DF1 %>%
    arrange(ID, mdy(Date)) %>%
    group_by(ID) %>% 
    slice(match(min(Value), Value):n()) %>% 
    ungroup
# A tibble: 4 × 3
     ID Date      Value
  <int> <chr>     <int>
1     1 2/16/2021   -37
2     1 2/17/2021     0
3     1 2/18/2021     1
4     1 2/19/2021    -2

data

DF1 <- structure(list(ID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), Date = c("2/11/2021", 
"2/12/2021", "2/13/2021", "2/14/2021", "2/15/2021", "2/16/2021", 
"2/17/2021", "2/18/2021", "2/19/2021"), Value = c(0L, 2L, 1L, 
0L, 1L, -37L, 0L, 1L, -2L)), class = "data.frame", row.names = c(NA, 
-9L))
  • Related