Filter grouped data to the rows where grouping changes

user_2778289 0
Viewed 34

With these data (below with dput()), where IndIDII is a grouping column, within which there are multiple observations of MigStratFact for each Year.

> head(Dat)
  IndIDII Year MigStratFact
1 BHS_376 2015      MidDist
2 BHS_376 2016      MidDist
3 BHS_376 2017      MidDist
4 BHS_376 2018    ShortDist
5 BHS_378 2015      MidDist
6 BHS_378 2016    ShortDist

I would like to filter Dat to the rows where the lead() value of MigStratFact does not match the current value, AND also retain the current field.

With the code below, for each IndIDII, I can filter to the rows where lead(MigStratFact) != MigStratFact but am not sure how to also keep the reference (i.e. current) row. 

Dat %>%
  group_by(IndIDII) %>% 
  filter(lead(MigStratFact) != MigStratFact) %>% 
  as.data.frame()

The desired solution would filter to rows 3,4,5,6,8,9,11,12,15,16.

Many thanks in advance

Dat <- structure(list(IndIDII = c("BHS_376", "BHS_376", "BHS_376", "BHS_376", 
    "BHS_378", "BHS_378", "BHS_378", "BHS_391", "BHS_391", "BHS_394", 
    "BHS_394", "BHS_394", "BHS_395", "BHS_395", "BHS_395", "BHS_395"
    ), Year = c("2015", "2016", "2017", "2018", "2015", "2016", "2017", 
    "2015", "2016", "2016", "2017", "2018", "2015", "2016", "2017", 
    "2018"), MigStratFact = structure(c(3L, 3L, 3L, 2L, 3L, 2L, 2L, 
    2L, 3L, 3L, 3L, 2L, 3L, 3L, 3L, 2L), .Label = c("Resident", "ShortDist", 
    "MidDist", "LongDist"), class = "factor")), class = "data.frame", row.names = c(NA, 
    -16L))
dplyr r
2 Answers

Try changing to 

Dat %>%
  group_by(IndIDII) %>% 
  filter(lead(MigStratFact) != MigStratFact | lag(MigStratFact) != MigStratFact)
#    IndIDII Year MigStratFact
# 1  BHS_376 2017      MidDist
# 2  BHS_376 2018    ShortDist
# 3  BHS_378 2015      MidDist
# 4  BHS_378 2016    ShortDist
# 5  BHS_391 2015    ShortDist
# 6  BHS_391 2016      MidDist
# 7  BHS_394 2017      MidDist
# 8  BHS_394 2018    ShortDist
# 9  BHS_395 2017      MidDist
# 10 BHS_395 2018    ShortDist
user_36914280

@konvas answer is hard to top, but here another solution. I took the challenge of filtering by index instead of by logical, but I admit it's a bit hard to read. 

Dat %>%
  group_by(IndIDII) %>% 
  filter(row_number() %in% c(a <-  which(lead(MigStratFact) != MigStratFact), a + 1))

# A tibble: 10 x 3
# Groups:   IndIDII [5]
   IndIDII Year  MigStratFact
   <chr>   <chr> <fct>       
 1 BHS_376 2017  MidDist     
 2 BHS_376 2018  ShortDist   
 3 BHS_378 2015  MidDist     
 4 BHS_378 2016  ShortDist   
 5 BHS_391 2015  ShortDist   
 6 BHS_391 2016  MidDist     
 7 BHS_394 2017  MidDist     
 8 BHS_394 2018  ShortDist   
 9 BHS_395 2017  MidDist     
10 BHS_395 2018  ShortDist
user_79411880
Related