R, delete previous rows based on values in seperate column

user_10570288 0
Viewed 118

I am new to R. I am trying to delete prior rows based on conditions set by another column.

I have found solutions with dplyr and data.table which I believe to be close to what I am looking for, as they do the opposite.

Sample data:

Cust_ID | Date                 | Value
500219  | 2016-04-11 12:00:00  | 0
500219  | 2016-04-12 16:00:00  | A
500219  | 2016-04-14 11:00:00  | A
500219  | 2016-04-15 12:00:00  | B
500219  | 2016-05-23 09:00:00  | B
500219  | 2016-05-02 19:00:00  | C
500220  | 2016-04-11 12:00:00  | C
500220  | 2016-04-14 11:00:00  | C
500220  | 2016-04-15 12:00:00  | A
500220  | 2016-05-23 09:00:00  | A
500220  | 2016-05-02 19:00:00  | A

For each Cust_ID, I would like to retain only the rows after Value == "A" including that row. This should result in the following data frame:

Cust_ID | Date                 | Value
500219  | 2016-04-12 16:00:00  | A
500219  | 2016-04-14 11:00:00  | A
500219  | 2016-04-15 12:00:00  | B
500219  | 2016-05-23 09:00:00  | B
500219  | 2016-05-02 19:00:00  | C
500220  | 2016-04-15 12:00:00  | A
500220  | 2016-05-23 09:00:00  | A
500220  | 2016-05-02 19:00:00  | A

These are the solution I have already found (R delete rows based on values in previous rows)

library(data.table)
setDT(df1)[df1[,  if(any(Value == "A")) .I[seq(max(which(Value == "A")))]
                                 else .I[1:.N] , by = Cust_ID]$V1]


library(dplyr)
df1 %>% 
     group_by(Cust_ID) %>% 
     slice(if(any(Value=="A")) seq(max(which(Value=="A"))) else row_number())
data.table dplyr r
3 Answers

Maybe a base R option with subset + ave could help

subset(df,ave(Value, Cust_ID, FUN = cumsum)>0)

which gives

   Cust_ID                Date Value
3   500219 2016-04-14 11:00:00     1
4   500219 2016-04-15 12:00:00     1
5   500219 2016-05-23 09:00:00     0
6   500219 2016-05-02 19:00:00     0
8   500220 2016-04-14 11:00:00     1
9   500220 2016-04-15 12:00:00     1
10  500220 2016-05-23 09:00:00     0
11  500220 2016-05-02 19:00:00     0
user_121587570

Does this work:

> library(dplyr)
> df %>% group_by(Cust_ID) %>% filter(row_number() >= min(which(Value == 'A')))
# A tibble: 8 x 3
# Groups:   Cust_ID [2]
  Cust_ID Date                Value
    <dbl> <chr>               <chr>
1  500219 2016-04-12 16:00:00 A    
2  500219 2016-04-14 11:00:00 A    
3  500219 2016-04-15 12:00:00 B    
4  500219 2016-05-23 09:00:00 B    
5  500219 2016-05-02 19:00:00 C    
6  500220 2016-04-15 12:00:00 A    
7  500220 2016-05-23 09:00:00 A    
8  500220 2016-05-02 19:00:00 A    
>
user_107227520

Here is another option using non-equi join:

df1[df1[Value=="A", Date[1L], Cust_ID], on=.(Cust_ID, Date>=V1)]

output:

   Cust_ID                Date Value
1:  500219 2016-04-12 16:00:00     A
2:  500219 2016-04-12 16:00:00     A
3:  500219 2016-04-12 16:00:00     B
4:  500219 2016-04-12 16:00:00     B
5:  500219 2016-04-12 16:00:00     C
6:  500220 2016-04-15 12:00:00     A
7:  500220 2016-04-15 12:00:00     A
8:  500220 2016-04-15 12:00:00     A

data:

df1 <- fread("Cust_ID | Date                 | Value
500219  | 2016-04-11 12:00:00  | 0
500219  | 2016-04-12 16:00:00  | A
500219  | 2016-04-14 11:00:00  | A
500219  | 2016-04-15 12:00:00  | B
500219  | 2016-05-23 09:00:00  | B
500219  | 2016-05-02 19:00:00  | C
500220  | 2016-04-11 12:00:00  | C
500220  | 2016-04-14 11:00:00  | C
500220  | 2016-04-15 12:00:00  | A
500220  | 2016-05-23 09:00:00  | A
500220  | 2016-05-02 19:00:00  | A")
df1[, Date := as.POSIXct(Date, format="%Y-%m-%d %T")]
user_19894800
Related