Using `rle` function along with `dplyr` `group_by` command to mapping grouping variable

user_11962863 0
Viewed 317

I have a dataframe with three columns that has information similar to the data frame given below. Now I wish to extract information search pattern based on the information in column a.

Based on the support from few developers (@thelatemail and @David T), I was able to identify the pattern with rle function, please see here - using rle function to identify pattern. Now, I wish to move ahead and add grouping information to the extracted pattern. I tried with dplyr do function - refer to the code below. However, this does not work.

The example data and desired output is given as well for your reference. 

##mycode that produces error - needs to be fixed
test <- data%>%
  group_by(b, c)%>%
  do(.,  data.frame(from = rle(.$a)$values), to = lead(rle(.$a)$values))

##code to create the data frame
a <- c( "a", "b", "b", "b", "a", "c", "a", "b", "d", "d", "d", "e", "f", "f", "e", "e")
b <- c(rep("experiment", times = 8), rep("control", times = 8))
c <- c(rep("A01", times = 4), rep("A02", times = 4), rep("A03", times = 4), rep("A04", times = 4))
data <- data.frame(c,b,a)


## desired output

    c      b         from  to    fromCount toCount
                    <chr> <chr>     <int>   <int>
 1 A01 experimental  a     b             1       3
 2 A02 experimental  a     c             1       1
 3 A02 experimental  c     a             1       1
 4 A02 experimental  a     b             1       1
 5 A03 control       d     e             3       1
 6 A04 control       f     e             2       2

Compared to the earlier post here, the information gets compressed since we apply grouping to the a column.

dplyr r
2 Answers

We could use rleid from data.table

library(data.table)
library(dplyr)
data %>% 
  group_by(b, c, grp = rleid(a)) %>%
  summarise(from = first(a), fromCount = n()) %>% 
  mutate(to = lead(from), toCount = lead(fromCount)) %>%
  ungroup %>%
  select(-grp) %>% 
  filter(!is.na(to)) %>%
  arrange(c)
# A tibble: 6 x 6
#  b          c     from  fromCount to    toCount
#  <chr>      <chr> <chr>     <int> <chr>   <int>
#1 experiment A01   a             1 b           3
#2 experiment A02   a             1 c           1
#3 experiment A02   c             1 a           1
#4 experiment A02   a             1 b           1
#5 control    A03   d             3 e           1
#6 control    A04   f             2 e           2

Or using rle, after grouping by 'b', 'c', summarise with rle to create a list column, then extract the 'values' and 'lengths' from column in summarise, create the 'to', 'toCount' on the lead of the 'from', 'fromCount' column filter out the NA elements and arrange the rows based on the 'c' column

data %>% 
    group_by(b, c) %>%
    summarise(rl = list(rle(a)), 
              from = rl[[1]]$values, 
              fromCount = rl[[1]]$lengths) %>% 
    mutate(to = lead(from), 
           toCount = lead(fromCount)) %>%
    ungroup %>% 
    select(-rl) %>% 
    filter(!is.na(to)) %>% 
    arrange(c)
# A tibble: 6 x 6
#  b          c     from  fromCount to    toCount
#  <chr>      <chr> <chr>     <int> <chr>   <int>
#1 experiment A01   a             1 b           3
#2 experiment A02   a             1 c           1
#3 experiment A02   c             1 a           1
#4 experiment A02   a             1 b           1
#5 control    A03   d             3 e           1
#6 control    A04   f             2 e           2

We could also loop over the rle list column ('rl') with map, extract the components, and take the lead of the lengths, values in a tibble, use unnest_wider to create the columns and unnest the list structure, filter out the NA elements and arrange

library(tidyr)
library(purrr)
data %>% 
     group_by(b, c) %>%
     summarise(rl = list(rle(a))) %>%
     ungroup %>%
     mutate(out = map(rl, 
          ~ tibble(from = .x$values,
                   fromCount = .x$lengths,
                   to = lead(from), 
                   toCount = lead(fromCount)))) %>%
     unnest_wider(c(out)) %>% 
     unnest(from:toCount) %>%
     filter(!is.na(to)) %>% 
     arrange(c) %>% 
     select(-rl)
user_37322710

Or in the tidyverse, create a function that does the rle for the Tracking for a single subject

rleSlice <- function(Tracking) {

  rlTrack <- rle(as.character(Tracking))  # Strip the levels from the factor, they interfere
  tibble(from = rlTrack$values, to = lead(rlTrack$values),
                 fromCount = rlTrack$lengths, toCount = lead(rlTrack$lengths)) %>% 
    filter(!is.na(to)) %>% 
    list()
}

Make sure it's behaving

[[1]]
rleSlice(c("a", "b", "b", "b", "c"))

A tibble: 2 x 4
  from  to    fromCount toCount
  <chr> <chr>     <int>   <int>
1 a     b             1       3
2 b     c             3       1

Now we'll group and get the rle for each participant

data %>% 
  as_tibble() %>% 
    # This is easier to track than all these a,b,c's
  rename(Subject = c, Test = b, Tracking = a) %>% 
  group_by(Subject, Test) %>% 
  summarise(Slice = rleSlice(Tracking)) %>% 
  unnest(col = "Slice") %>% 
  ungroup()

# A tibble: 6 x 6
  Subject Test       from  to    fromCount toCount
  <fct>   <fct>      <chr> <chr>     <int>   <int>
1 A01     experiment a     b             1       3
2 A02     experiment a     c             1       1
3 A02     experiment c     a             1       1
4 A02     experiment a     b             1       1
5 A03     control    d     e             3       1
6 A04     control    f     e             2       2
user_60040160
Related