Delete Rows After a Certain Point in R

user_11427343 0
Viewed 469

I have a data set where I'd like to delete the rows after a certain column value comes up

An example of the data set:

a<-data.frame(Account=c('A','A','A','A','A','A','A','A','A'),
              ProcessDate=seq(as.Date('2020-01-01'),as.Date('2020-09-01'),by='month'),
              Payment=c(1,2,3,4,5,6,7,8,9),
              ColumnCriteria=c(610,610,610,610,610,650,610,610,610))

I would like to delete the rows after the ColumnCriteria = 650.

My problem is that I'm working with a large data set so I cannot just use the filter function with a few lags because where the 650 is in this example, will be different for another Account.

I'm thinking I could potentially write an if_else statement where if the ColumnCriteria is equal to 650 then return the Date and then I could try to fill the column with that date and then filter the rows where the ProcessDate is greater. This seems a little messy so I figured I'd reach out and see if anyone had any suggestions.

Thanks!

row dataset r
4 Answers

Try this base R solution by creating an index with which():

#Data
a<-data.frame(Account=c('A','A','A','A','A','A','A','A','A'),
              ProcessDate=seq(as.Date('2020-01-01'),as.Date('2020-09-01'),by='month'),
              Payment=c(1,2,3,4,5,6,7,8,9),
              ColumnCriteria=c(610,610,610,610,610,650,610,610,610))   
#Index
i <- min(which(a$ColumnCriteria==650))
#Code
a2 <- a[1:i,]

Output:

a2
  Account ProcessDate Payment ColumnCriteria
1       A  2020-01-01       1            610
2       A  2020-02-01       2            610
3       A  2020-03-01       3            610
4       A  2020-04-01       4            610
5       A  2020-05-01       5            610
6       A  2020-06-01       6            650
user_20808480

Consider slice:

library(dplyr)

slice(a, 1:which(ColumnCriteria == 650, arr.ind = TRUE))

  Account ProcessDate Payment ColumnCriteria
1       A  2020-01-01       1            610
2       A  2020-02-01       2            610
3       A  2020-03-01       3            610
4       A  2020-04-01       4            610
5       A  2020-05-01       5            610
6       A  2020-06-01       6            650

Note: If the cutoff value appears multiple times, this expression will only use the first instance of that value (ordered by the row order of a).

user_27999410

From your description it sounds like you need to operate by Account, and that your data is implicitly ordered by Payment, although you didn't supply a complete enough dataset to really demonstrate this. Are you familiar with dplyr::group_by()?

a %>%
  group_by(Account) %>%
  arrange(Payment) %>%
  filter(cumsum(tidyr::replace_na(lag(ColumnCriteria == 650), 0)) == 0)
user_141611890

An option with match and seq

a[seq_len(match(650, a$ColumnCriteria)),]
#  Account ProcessDate Payment ColumnCriteria
#1       A  2020-01-01       1            610
#2       A  2020-02-01       2            610
#3       A  2020-03-01       3            610
#4       A  2020-04-01       4            610
#5       A  2020-05-01       5            610
#6       A  2020-06-01       6            650
user_37322710
Related