Group data and create a new column

Question

I have the following data:

1 0 1234
1 0 1235
1 0 5434
2 1 31212
2 1 3212
2 0 1211
3 0 2212
3 0 2212
3 1 1212

What I would like to accomplish with R is to create a new column that will have a value of 1 if at least one of the three values ​​(which all belong together) in the second column has 1. So, my new column will look like this way:

1 0 1234 0
1 0 1235 0 
1 0 5434 0
2 1 31212 1
2 1 3212 1
2 0 1211 1
3 0 2212 1
3 0 2212 1
3 1 1212 1

Since each 3 lines belong to each other, I could not figure out how to do this. Can anyone help me with this?

Answer 1

You can use dplyrand group_bythe first column (V1 in my case), and then use anyto check as to whether the one of the values 1.

library(dplyr)
df %>% 
   group_by(V1) %>% 
   mutate(new = ifelse(any(V2) == 1, 1, 0))

#Source: local data frame [9 x 4]
#Groups: V1 [3]

#     V1    V2    V3   new
#  <int> <int> <int> <dbl>
#1     1     0  1234     0
#2     1     0  1235     0
#3     1     0  5434     0
#4     2     1 31212     1
#5     2     1  3212     1
#6     2     0  1211     1
#7     3     0  2212     1
#8     3     0  2212     1
#9     3     1  1212     1

Answer 2

ave base R

df1$new <- with(df1, ave(V2, V1, FUN = any))
df1$new
#[1] 0 0 0 1 1 1 1 1 1

table

as.integer(rowSums(table(df1[1:2])!=0)==2)[df1$V1]
#[1] 0 0 0 1 1 1 1 1 1

---

data.table

library(data.table)
setDT(df1)[, new := as.integer(any(V2)), by = V1]
df1
#   V1 V2    V3 new
#1:  1  0  1234   0
#2:  1  0  1235   0
#3:  1  0  5434   0
#4:  2  1 31212   1
#5:  2  1  3212   1
#6:  2  0  1211   1
#7:  3  0  2212   1
#8:  3  0  2212   1
#9:  3  1  1212   1

df1 <- structure(list(V1 = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L), V2 = c(0L, 
0L, 0L, 1L, 1L, 0L, 0L, 0L, 1L), V3 = c(1234L, 1235L, 5434L, 
31212L, 3212L, 1211L, 2212L, 2212L, 1212L)), .Names = c("V1", 
"V2", "V3"), class = "data.frame", row.names = c(NA, -9L))