CODEWITHSUNDEEP
rdplyrtidyr
Sol
Sol

Reputation: 754

How to write new column conditional on grouped rows in R?

I have a data frame where each Item has three categories (a, b,c) and a numeric Answer for each category is recorded (either 0 or 1). I would like to create a new column contingent on the rows in the Answer column. This is how my data frame looks like:

Item     <- rep(c(1:3), each=3)
Option   <- rep(c('a','b','c'), times=3)
Answer   <- c(1,1,0,1,0,1,1,1,1)
df       <- data.frame(Item, Option, Answer)


  Item Option Answer
1    1      a      1
2    1      b      1
3    1      c      0
4    2      a      0
5    2      b      0
6    2      c      1
7    3      a      1
8    3      b      1
9    3      c      1

What is needed: whenever the three categories in the Option column are 1, the New column should receive a 1. In any other case, the column should have a 0. The desired output should look like this:

  Item Option Answer New
1    1      a      1   0
2    1      b      1   0
3    1      c      0   0
4    2      a      0   0
5    2      b      0   0
6    2      c      1   0
7    3      a      1   1
8    3      b      1   1
9    3      c      1   1

I tried to achieve this without using a loop, but I got stuck because I don't know how to make a new column contingent on a group of rows, not just a single one. I have tried this solution but it doesn't work if the rows are not grouped in pairs.

Do you have any suggestions? Thanks a bunch!

Upvotes: 2

Views: 170

Answers (3)

akrun
akrun

Reputation: 886938

Or using only base R

df$Index <- with(df, +(ave(!!Answer, Item, FUN = all)))
df$Index
#[1] 0 0 0 0 0 0 1 1 1

Upvotes: 2

user5249203
user5249203

Reputation: 4638

using data.table 

DT <- data.table(Item, Option, Answer)
DT[, Index := as.numeric(all(as.logical(Answer))), by= Item]
DT

    Item Option Answer Index
1:    1      a      1     0
2:    1      b      1     0
3:    1      c      0     0
4:    2      a      1     0
5:    2      b      0     0
6:    2      c      1     0
7:    3      a      1     1
8:    3      b      1     1
9:    3      c      1     1

Upvotes: 3

C_Z_
C_Z_

Reputation: 7796

This should work:

df %>%
  group_by(Item)%>%
  mutate(New = as.numeric(all(as.logical(Answer))))

Upvotes: 4

Related Questions