Subsetting dataframe where values change

user_15691355 0
Viewed 25

I want to filter rows from a data.frame based on where the values change. Suppose I have:

    id  name  quarter      score      
1.  01  john  q1 2020      80
2.  01  john  q2 2020      80
3.  01  john  q3 2020      85
4.  01  john  q4 2020      75
5.  02  adam  q1 2020      80
6.  02  adam  q2 2020      80
7.  02  adam  q3 2020      85
8.  03  lana  q1 2020      50

Wherever the quarter and score both change, I want to filter out those rows. So the data frame above should become,

    id  name  quarter      score      
1.  01  john  q2 2020      80
2.  01  john  q3 2020      85
3.  01  john  q4 2020      75
4.  02  adam  q2 2020      80
5.  02  adam  q3 2020      85

How do I compare the value of a cell with the value in the previous row in R?

subset dataframe r
2 Answers

Maybe you can try subset + ave like below

subset(
  df,
  !!ave(score,id,FUN = function(x) c(TRUE,diff(x)!=0)&length(x)>1)
)

which gives

  id name quarter score
1  1 john q1_2020    80
3  1 john q3_2020    85
4  1 john q4_2020    75
5  2 adam q1_2020    80
7  2 adam q3_2020    85

data

> dput(df)
structure(list(id = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L), name = c("john", 
"john", "john", "john", "adam", "adam", "adam", "lana"), quarter = c("q1_2020", 
"q2_2020", "q3_2020", "q4_2020", "q1_2020", "q2_2020", "q3_2020", 
"q1_2020"), score = c(80L, 80L, 85L, 75L, 80L, 80L, 85L, 50L)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8"))
user_121587570

You can use lead function from dplyr :

library(dplyr)

result <- df %>% filter(quarter != lead(quarter), score != lead(score))
result
#   id name quarter score
#2.  1 john  q22020    80
#3.  1 john  q32020    85
#4.  1 john  q42020    75
#6.  2 adam  q22020    80
#7.  2 adam  q32020    85

and similarly in data.table:

library(data.table)
setDT(df)[quarter != shift(quarter, type = 'lead') & 
          score != lead(score, type = 'lead')]
user_39629140
Related