Comparing consecutive rows over specific conditions by group

Question

For each participant and each trial I need to check that for all the consecutive rows in CURRENT_ID, the first row has a value of 0 in the column A, and the last row has a value of 0 in the column B. If both conditions are fulfil, I would like to have a value of 0 in the new column C, if they are not I would like to have a value of 1.

head(mydf, 10)

#> # A tibble: 10 x 6
#>        A     B participant trial CURRENT_ID     C
#>    <dbl> <dbl> <chr>       <dbl>      <dbl> <dbl>
#>  1     0     1 ppt01          45          3     0
#>  2     1     0 ppt01          45          4     0
#>  3     0     1 ppt01          45         10     0
#>  4     0     0 ppt01          45         11     0
#>  5     1     0 ppt01          45         12     0
#>  6     0     1 ppt01          87          2     0
#>  7     1     0 ppt01          87          3     0
#>  8     1     1 ppt01          87          4     1
#>  9     1     1 ppt01          87          5     1
#> 10     0     1 ppt01          34          6     0

I need to consider every pair of consecutive rows (consecutive based on the values of CURRENT_ID) for each participant and trial. In the example above, rows 8 and 9 get a value of 1 in the new column C because row 8 has a 1 (instead of 0) in column A, and row 9 has a 1 (instead of 0) in column B.

Here an example how the rows should be compared, with participant ppt01 and trial 87

    A   B    participant   trial   CURRENT_ID      C

    0   1    ppt01         87      2               0
    1   0    ppt01         87      3               0

    1   0    ppt01         87      3               0
    1   1    ppt01         87      4               1

    1   1    ppt01         87      4               1
    1   1    ppt01         87      5               1

Data:

mydf <- structure(list(A = c(0, 1, 0, 0, 1, 0, 1, 1, 1, 0, 0, 0, 0, 0, 
1, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 0, 
1, 1), B = c(1, 0, 1, 0, 0, 1, 0, 1, 1, 1, 0, 0, 0, 0, 0, 1, 
0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 1, 0, 1
), participant = c("ppt01", "ppt01", "ppt01", "ppt01", "ppt01", 
"ppt01", "ppt01", "ppt01", "ppt01", "ppt01", "ppt01", "ppt01", 
"ppt01", "ppt01", "ppt01", "ppt01", "ppt01", "ppt01", "ppt01", 
"ppt01", "ppt01", "ppt02", "ppt02", "ppt02", "ppt02", "ppt02", 
"ppt02", "ppt02", "ppt02", "ppt02", "ppt02", "ppt02", "ppt02", 
"ppt02", "ppt02", "ppt02", "ppt02"), trial = c(45, 45, 45, 45, 
45, 87, 87, 87, 87, 34, 34, 34, 34, 34, 34, 8, 8, 8, 8, 8, 8, 
87, 87, 87, 87, 55, 55, 55, 55, 55, 55, 22, 22, 22, 22, 22, 22
), CURRENT_ID = c(3, 4, 10, 11, 12, 2, 3, 4, 5, 6, 7, 8, 9, 10, 
11, 5, 6, 9, 10, 11, 12, 2, 3, 4, 5, 5, 6, 9, 10, 11, 12, 2, 
3, 4, 10, 11, 12), C = c(0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 
1, 0, 1, 1)), class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -37L), spec = structure(list(cols = list(
    A = structure(list(), class = c("collector_double", "collector"
    )), B = structure(list(), class = c("collector_double", "collector"
    )), participant = structure(list(), class = c("collector_character", 
    "collector")), trial = structure(list(), class = c("collector_double", 
    "collector")), CURRENT_ID = structure(list(), class = c("collector_double", 
    "collector")), C = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), skip = 1), class = "col_spec"))

are you trying to do this by `participant` and/or `trial`? also are you trying to do this for consecutive values of `CURRENT_ID`? — akash87, Jun 14 '17 at 20:26
is the provided column C in the data correct - why would row 8,9 get a 1 - doesnt match the description for me!? — Tonio Liebrand, Jun 14 '17 at 20:31
@akash87 I need to consider consecutive values of CURRENT_ID for each participant and trial. — dede, Jun 15 '17 at 09:39
@BigDataScientist I have added an edit to better explain why rows 8 and 9 get a value of 1 in C. — dede, Jun 15 '17 at 09:40
Then why does trial 22 have `C=1` when the line above `A = 0` and `B=0` in the current line. — akash87, Jun 15 '17 at 12:49
@akash87 because they are from different trials (trial 8 and trial 87), and I am interested in consecutive rows for each trial and each participant. — dede, Jun 15 '17 at 14:19
I have revised my answer to accommodate your new explanations as I understand them. — ssp3nc3r, Jun 15 '17 at 14:22

ssp3nc3r · Answer 1 · 2017-06-15T14:33:35.523

If you would like to group pairs of AB within groups of participant-trial, this should work:

d %>% group_by(participant, trial) %>% mutate(AB = ceiling(1:n()/2)) %>% group_by(participant, trial, AB) %>% mutate(newC = ifelse(length(A) == 1 | (A[1] == 0 & B[2] == 0), 0, 1))

I've left the new columns in so you can see how this was done.

Output:

# A tibble: 15 x 8
       A     B participant trial CURRENT_ID     C    AB  newC
   <int> <int>       <chr> <int>      <int> <int> <dbl> <dbl>
 1     0     1       ppt01    45          3     0     1     0
 2     1     0       ppt01    45          4     0     1     0
 3     0     1       ppt01    45         10     0     2     0
 4     0     0       ppt01    45         11     0     2     0
 5     1     0       ppt01    45         12     0     3     0
 6     0     1       ppt01    87          2     0     1     0
 7     1     0       ppt01    87          3     0     1     0
 8     1     1       ppt01    87          4     1     2     1
 9     1     1       ppt01    87          5     1     2     1
10     0     1       ppt01    34          6     0     1     0
11     0     0       ppt01    34          7     0     1     0
12     0     0       ppt01    34          8     0     2     0
13     0     0       ppt01    34          9     0     2     0
14     0     0       ppt01    34         10     0     3     0
15     1     0       ppt01    34         11     0     3     0

Otherwise, as was originally described:

require(dplyr)
d %>% group_by(participant, trial) %>% mutate(newC = ifelse(A[1] == 0 & B[n()] == 0, 0, 1))

Output:

Source: local data frame [15 x 7]
Groups: participant, trial [3]

# A tibble: 15 x 7
       A     B participant trial CURRENT_ID     C  newC
   <int> <int>       <chr> <int>      <int> <int> <dbl>
 1     0     1       ppt01    45          3     0     0
 2     1     0       ppt01    45          4     0     0
 3     0     1       ppt01    45         10     0     0
 4     0     0       ppt01    45         11     0     0
 5     1     0       ppt01    45         12     0     0
 6     0     1       ppt01    87          2     0     1
 7     1     0       ppt01    87          3     0     1
 8     1     1       ppt01    87          4     1     1
 9     1     1       ppt01    87          5     1     1
10     0     1       ppt01    34          6     0     0
11     0     0       ppt01    34          7     0     0
12     0     0       ppt01    34          8     0     0
13     0     0       ppt01    34          9     0     0
14     0     0       ppt01    34         10     0     0
15     1     0       ppt01    34         11     0     0

I used a subset of your data using dput():

d <- structure(
  list(
    A = c(0L, 1L, 0L, 0L, 1L, 0L, 1L, 1L, 1L, 0L,
          0L, 0L, 0L, 0L, 1L),
    B = c(1L, 0L, 1L, 0L, 0L, 1L, 0L, 1L, 1L,
          1L, 0L, 0L, 0L, 0L, 0L),
    participant = c(
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01",
      "ppt01"
    ),
    trial = c(
      45L,
      45L,
      45L,
      45L,
      45L,
      87L,
      87L,
      87L,
      87L,
      34L,
      34L,
      34L,
      34L,
      34L,
      34L
    ),
    CURRENT_ID = c(3L, 4L, 10L, 11L, 12L, 2L, 3L, 4L, 5L, 6L,
                   7L, 8L, 9L, 10L, 11L),
    C = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L,
          1L, 0L, 0L, 0L, 0L, 0L, 0L)
  ),
  .Names = c("A", "B", "participant",
             "trial", "CURRENT_ID", "C"),
  class = "data.frame",
  row.names = c(NA,-15L)
)

In my understanding row 6-9 shouldnt get a 1 for row `newC`. Do you additionally group for `trial` or am i missing sthg? — Tonio Liebrand, Jun 14 '17 at 21:17
Yes, he said he wants both conditions met. If he intends something else, he'll have to clarify. — ssp3nc3r, Jun 14 '17 at 21:21
@ssp3nc3r Thanks for your answer. However, rows 6 and 7 should have values of 0 because row 6 has a value of 0 in column A and row 7 has a value of 0 in column B. Rows 8 and 9 are correct. — dede, Jun 15 '17 at 09:42
According to your explanation, a unique paring of participant and trial create a group, and within that group, you want A in the first row to have a 0, and B in the last row to have a 0. If that is not what you intended as your explanation, you'll need to revise what you mean. — ssp3nc3r, Jun 15 '17 at 13:54
@ssp3nc3r I have added EDIT2 to give a better explanation of how to consider the consecutive rows. — dede, Jun 15 '17 at 14:24

tjebo · Answer 2 · 2020-05-16T10:57:00.623

An old question but still relevant and not yet with accepted answer!

The conditions are not exactly super clear from the question, but I find two ways of interpreting it.

1) TRUE if B == 1 and lag(A) == 1

2) TRUE if A == 1 & B == 1 in at least two consecutive rows.

I am trying to provide a solution for both ways to interpret the OP.

library(tidyverse)

 mydf2 <- mydf %>%
  group_by(participant, trial) %>%
  mutate(consec_rows = cumsum(c(1, diff(CURRENT_ID) != 1))) %>%
  group_by(participant, trial, consec_rows) %>%
  mutate(cond_consec = B & lag(A))

 as.data.frame(mydf2)
#>    A B participant trial CURRENT_ID C consec_rows cond_consec
#> 1  0 1       ppt01    45          3 0           1          NA
#> 2  1 0       ppt01    45          4 0           1       FALSE
#> 3  0 1       ppt01    45         10 0           2          NA
#> 4  0 0       ppt01    45         11 0           2       FALSE
#> 5  1 0       ppt01    45         12 0           2       FALSE
#> 6  0 1       ppt01    87          2 0           1          NA
#> 7  1 0       ppt01    87          3 0           1       FALSE
#> 8  1 1       ppt01    87          4 1           1        TRUE
#> 9  1 1       ppt01    87          5 1           1        TRUE
#> 10 0 1       ppt01    34          6 0           1          NA
#> 11 0 0       ppt01    34          7 0           1       FALSE
#> 12 0 0       ppt01    34          8 0           1       FALSE
#> 13 0 0       ppt01    34          9 0           1       FALSE
#> 14 0 0       ppt01    34         10 0           1       FALSE
#> 15 1 0       ppt01    34         11 0           1       FALSE
#> 16 0 1       ppt01     8          5 0           1          NA
#> 17 1 0       ppt01     8          6 0           1       FALSE
#> 18 0 1       ppt01     8          9 0           2          NA
#> 19 0 0       ppt01     8         10 0           2       FALSE
#> 20 0 0       ppt01     8         11 0           2       FALSE
#> 21 1 0       ppt01     8         12 0           2       FALSE
#> 22 0 1       ppt02    87          2 0           1          NA
#> 23 0 0       ppt02    87          3 0           1       FALSE
#> 24 0 0       ppt02    87          4 0           1       FALSE
#> 25 1 0       ppt02    87          5 0           1       FALSE
#> 26 0 1       ppt02    55          5 0           1          NA
#> 27 1 0       ppt02    55          6 0           1       FALSE
#> 28 0 1       ppt02    55          9 0           2          NA
#> 29 1 0       ppt02    55         10 0           2       FALSE
#> 30 0 1       ppt02    55         11 1           2        TRUE
#> 31 1 0       ppt02    55         12 0           2       FALSE
#> 32 0 1       ppt02    22          2 0           1          NA
#> 33 1 0       ppt02    22          3 0           1       FALSE
#> 34 0 1       ppt02    22          4 1           1        TRUE
#> 35 0 1       ppt02    22         10 0           2          NA
#> 36 1 0       ppt02    22         11 1           2       FALSE
#> 37 1 1       ppt02    22         12 1           2        TRUE

As far as I understand the OP, I believe that the desired C == 1 in row 36 should actually be a zero.

^{Created on 2020-05-16 by the reprex package (v0.3.0)}

Or, if the condition is meant to be A == 1 & B == 1 in consecutive rows:

mydf %>%
  group_by(participant, trial, consec = cumsum(c(1, diff(CURRENT_ID) != 1))) %>%
  mutate(cond_consec = 
           rep(rle(A & B)$values & rle(A & B)$lengths >= 2, rle(A & B)$lengths))

#> # A tibble: 37 x 8
#> # Groups:   participant, trial, consec [11]
#>        A     B participant trial CURRENT_ID     C consec cond_consec
#>    <dbl> <dbl> <chr>       <dbl>      <dbl> <dbl>  <dbl> <lgl>      
#>  1     0     1 ppt01          45          3     0      1 FALSE      
#>  2     1     0 ppt01          45          4     0      1 FALSE      
#>  3     0     1 ppt01          45         10     0      2 FALSE      
#>  4     0     0 ppt01          45         11     0      2 FALSE      
#>  5     1     0 ppt01          45         12     0      2 FALSE      
#>  6     0     1 ppt01          87          2     0      3 FALSE      
#>  7     1     0 ppt01          87          3     0      3 FALSE      
#>  8     1     1 ppt01          87          4     1      3 TRUE       
#>  9     1     1 ppt01          87          5     1      3 TRUE       
#> 10     0     1 ppt01          34          6     0      3 FALSE

Explanation part 1

grouping by "groups" of consecutive numbers
0 and 1 are interpreted as TRUE and FALSE, therefore we can omit the == operators.
I.e., B & lag(A) is equivalent to B == 1 & lag(A) == 1
the reference is B and one lag of A - allowing for comparison of values across rows.

Part 2 (if A == 1 & B == 1 in consecutive rows)

using rle of conditional statements and check if rle$length of TRUE >= 2
repeating the resulting vector in order to use it with mutate

I think the fastest you can get without writing compiled code will be using [`rleidv`](https://rdatatable.gitlab.io/data.table/library/data.table/html/rleid.html). — jangorecki, May 16 '20 at 10:14

hello_friend · Answer 3 · 2020-05-16T10:58:35.257

2

Base R Solution:

mydf$grouping_vec <- with(mydf, paste(participant, trial, 
                           ave(CURRENT_ID, participant, trial, FUN = function(x){
    cumsum(c(1, ifelse(diff(x) > 1, 1, 0))) 
  }), sep = " - "))

data.frame(do.call("rbind", 
  lapply(split(mydf, mydf$grouping_vec),
    function(x) {
      data.frame(cbind(x[, names(x) != "C"], 
                      C = rep(if(x$A[1] == 0 & x$B[nrow(x)] == 0){0}else{1}, nrow(x))))}
  )),
  row.names = NULL)

Tidyverse Solution:

library(tidyverse)

mydf %>% 
  mutate(grouping_vec = str_c(participant, 
                              trial, 
                              cumsum(c(1, diff(CURRENT_ID) != 1))),
                              sep = " - ")) %>% 
  group_by(grouping_vec) %>% 
  mutate(C = if_else(first(A) == 0 & last(B) == 0, 0, 1)) %>% 
  ungroup() %>% 
  select(-grouping_vec)

edited May 16 '20 at 10:58

answered May 16 '20 at 07:37

hello_friend

5,682
1
11
15

thanks for contributing! Unfortunately both solutions do not give the desired output shown in column C. Admittedly, I am not sure I fully understand the condition either - and also I believe there may be an error in column C (row 36) – tjebo May 16 '20 at 10:09
1

@Tjebo this question is terribly worded. I think OP means that they want to consider cumulative differences in both A & B but honestly who knows. – hello_friend May 16 '20 at 10:12
1

But please keep this answer here - I like the use of first and last and it definitely answers for *one* way of interpreting the answer. I've seen you've updated your tidyverse solution. You could make the grouping statement a bit simpler by using just the conditon without ifelse (such as I used in my post) `cumsum(c(1, diff(CURRENT_ID) != 1)` – tjebo May 16 '20 at 10:31
1

@Tjebo True will edit accordingly ! Thanks for your help and also for the words of encouragement. +1 to your solution too, your solution seems to be the only one matching OPs sample. – hello_friend May 16 '20 at 10:53

Comparing consecutive rows over specific conditions by group

3 Answers3