Replace sets of rows in a tibble with a single row

user_3680420 0
Viewed 88

There is a similar question for data.table at Replace sets of rows in a data.table with a single row but I am looking for an equivalent solution in tidyverse. So, I have a tibble like:

DT <- tibble (
  id=c(1,1,1,1,2,2,2,2),
  location = c("a","b","c","d","a","b","d","e"),
  seq = c(1,2,3,4,1,2,3,4))

For every id, I want to look for the sequence b,c,d and if there is such a thing, I want to replace the rows with b and c with a single row, let's say z. The values for the other variables should retain the values of the previous b (in this case id and seq)

So in this case, the new tibble should be

DT.Tobe <- tibble (
  id=c(1,1,1,2,2,2,2),
  place = c("a","z","d","a","b","d","e"),
  seq = c(1,2,4,1,2,3,4))

I was not able to find even a starting point for this...

tidyverse r replace
2 Answers
library(dplyr)
# library(zoo) # rollapply
DT %>%
  group_by(id) %>%
  mutate(
    isseq = zoo::rollapply(location, 3, FUN = function(z) identical(z, c("b", "c", "d")), align = "left", partial = TRUE), 
    isseq = isseq | lag(isseq, default = FALSE)
  ) %>%
  group_by(id, isseq) %>%
  summarize(
    across(everything(), ~ {
      if (cur_group()$isseq) { 
        if (cur_column() == "location") "z" else first(.)
      }  else .
    })
  ) %>%
  ungroup() %>%
  select(-isseq)
# # A tibble: 7 x 3
#      id location   seq
#   <dbl> <chr>    <dbl>
# 1     1 a            1
# 2     1 d            4
# 3     1 z            2
# 4     2 a            1
# 5     2 b            2
# 6     2 d            3
# 7     2 e            4

The order is changed because the group_by(isseq) tends to keep "like" together. This should be easy to either re-order (assuming "seq" is meaningful) or pre-add an order variable and using it later.

If it is possible for a single id to have multiple of such sequences (if so, say something), then run-length encoding will be needed here as well (to differentiate between different b-c-d sequences in the same id).

user_33582720

One possible option would be to use a for-loop. Here is my pseudocode.

for (i in nrows(DT)){ # Repeat this if statement for each row in your DT

    if (place[i] == "b & place[i+1] == "c"){ # if the first item is B and the second item is C

        DT <- DT %>%
              dplyr::replace(place[i] == "z") # Replaces item B with the z character
        
        DT[-(i+1)] # Deletes item C's row
    }

}

The Dplyr cheat sheet has some useful functions that may help with finding the right tools for the if-statement part of this pseudocode.

What are your thoughts?

user_139018680
Related