R: How to create a new binary column out of next row values?

Question

Here's the issue I need to solve:

Given a dataframe like this:

Att.team Def.team    Attack  Defense   Type          Goal
A        B           1       1         Organized     1
B        A           1       1         Organized     0
A        B           2       2         Organized     0
B        A           2       2         Fastbreak     1
A        B           3       3         Organized     0
A        B           3       3         Organized     0
A        B           3       3         Organized     0
B        A           3       3         Fastbreak     1
C        D           1       1         Organized     1
D        C           1       1         Organized     0
C        D           2       2         Fastbreak     0
D        C           2       2         Organized     1
C        D           3       3         Organized     0
D        C           3       3         Fastbreak     1

I would like to add two columns:

• One to keep track of which successful defenses (to be successful Goal column must be 0) do lead to a fastbreak (so in next row Type column must be "Fastbreak"). Let's call this new column Def.fb

• Another one to keep track of which successful defenses do lead to a fastbreak that ends in goal (thus, in next row Type column must be "Fastbreak" and Goal column must be "1"). Let's call it Def.fb.goal

In other words, I would like the dataframe to look like this:

Att.team Def.team    Attack  Defense   Type      Goal Def.fb Def.fb.goal
A        B           1       1         Organized 1    0      0
B        A           1       1         Organized 0    0      0
A        B           2       2         Organized 0    1      1
B        A           2       2         Fastbreak 1    0      0
A        B           3       3         Organized 0    1      1
A        B           3       3         Organized 0    1      1
A        B           3       3         Organized 0    1      1
B        A           3       3         Fastbreak 1    0      0
C        D           1       1         Organized 1    0      0
D        C           1       1         Organized 0    1      0
C        D           2       2         Fastbreak 0    0      0
D        C           2       2         Organized 1    0      0
C        D           3       3         Organized 0    1      1
D        C           3       3         Fastbreak 1    0      0

I'm trying with lag and lead functions but honestly I'm not familiar with these functions therefore I'm not being able to get any outcome.

Answer 1

Could try:

library(dplyr)

df %>%
  mutate(
    Def.fb = +(Goal == 0 & lead(Type) == 'Fastbreak'),
    Def.fb.goal = +(lead(Goal) == 1 & lead(Type) == 'Fastbreak')
  ) %>%
  group_by_at(vars(1:4)) %>%
  mutate_at(vars(starts_with("Def.fb")), ~ coalesce(+(any(. == 1)), 0L))

Output:

# A tibble: 14 x 8
# Groups:   Att.team, Def.team, Attack, Defense [12]
   Att.team Def.team Attack Defense Type       Goal Def.fb Def.fb.goal
   <fct>    <fct>     <int>   <int> <fct>     <int>  <int>       <int>
 1 A        B             1       1 Organized     1      0           0
 2 B        A             1       1 Organized     0      0           0
 3 A        B             2       2 Organized     0      1           1
 4 B        A             2       2 Fastbreak     1      0           0
 5 A        B             3       3 Organized     0      1           1
 6 A        B             3       3 Organized     0      1           1
 7 A        B             3       3 Organized     0      1           1
 8 B        A             3       3 Fastbreak     1      0           0
 9 C        D             1       1 Organized     1      0           0
10 D        C             1       1 Organized     0      1           0
11 C        D             2       2 Fastbreak     0      0           0
12 D        C             2       2 Organized     1      0           0
13 C        D             3       3 Organized     0      1           1
14 D        C             3       3 Fastbreak     1      0           0