CODEWITHSUNDEEP
rmachine-learning
Hku
Hku

Reputation: 241

Group data and generate new column

I have data as follows:

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 using R is to generate a new column, which would have a value of 1 if at least one of the three values (which all belong together) in the second column have a 1. So, my new column would be like:

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

As each 3 rows belong together, I was not able to figure out how to accomplish this. Could anyone help me with this?

Upvotes: 4

Views: 65

Answers (2)

akrun
akrun

Reputation: 887168

We can use ave from base R

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

Or using table

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

Or using 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

data

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))

Upvotes: 1

Sotos
Sotos

Reputation: 51592

You can use dplyr and group_by the first column (V1 in my case), and then use any to check if any of the values equals to 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

Upvotes: 2

Related Questions