Create a binary column indicating the first success

Question

Fish_ID	Instance	Success	First_Success
1	0	0	0
1	1	0	0
1	2	1	1
1	3	0	0
1	4	1	0
1	5	0	0

I have a data frame with column "Fish_ID","Instance" and"Success" and I want to create a column "First_Success", containing a 1 only at first success and otherwise 0 for each fish_ID group. In the actual data I have more than one Fish_ID. Here is an example. I came up with this code:

mydata %>%
  group_by(fish_ID) %>%
  mutate(Fist_Success = ifelse(Success == 1, 1, 0))

But with this code, it counts for all success values that equals to 1 and I just want to it only takes for the first success as 1 for the "First_Success" column and the rest is 0 within the same fish_ID group. I would appreciate any suggestions. Thanks a lot!

Darren Tsai · Accepted Answer · 2023-04-25T03:43:20.260

You can use cumsum():

library(dplyr)

df %>%
  mutate(First_Success = +(Success & cumsum(Success) == 1),
         .by = Fish_ID)

#   Fish_ID Instance Success First_Success
# 1       1        0       0             0
# 2       1        1       0             0
# 3       1        2       1             1
# 4       1        3       0             0
# 5       1        4       1             0
# 6       1        5       0             0

It has a variant suggested by @langtang:

df %>%
  mutate(First_Success = (cumsum(Success) == 1) * Success,
         .by = Fish_ID)

Data

df <- read.table(text =
"Fish_ID    Instance    Success
1   0   0
1   1   0
1   2   1
1   3   0
1   4   1
1   5   0", header = TRUE)

i like this better than mine. Also, you can just multiply the cumsum directly like `Success*(cumsum(Success)==1)` — langtang, Apr 23 '23 at 14:17

score 2 · Answer 2 · answered Apr 23 '23 at 13:46

There are quite a number of ways to do this.. In this example below, I subtract 1 from the value of Instance in the row where Success is first equal to 1

df %>%
  group_by(fish_ID) %>%
  mutate(First_Success = +(Instance == min(which(Success==1))-1))

Another approach is to use which.max(), like this:

group_by(df, fish_ID) %>%
  mutate(First_Success = +(row_number()== which.max(Success)))

Output:

  fish_ID Instance Success First_Success
    <int>    <int>   <int>         <int>
1       1        0       0             0
2       1        1       0             0
3       1        2       1             1
4       1        3       0             0
5       1        4       1             0
6       1        5       0             0

Create a binary column indicating the first success

2 Answers2

Data