How do I code each row based on the columns in groups?

Question

I have the following dataset:

name <- c("Mary", "John", "Paul", "Sally", "Ronald", "Adam", "Nancy", "Jane", "Peter", "Linda")

score <- c(1, 1, 1, 1, 0,
           0, 1, 0, 1, 0)

vote <- c(1, 0, 0, 1, 1,
          1, 1, 1, 0, 1)

group <- c("A", "A", "A", "A", "A",
           "B", "B", "B", "B", "B")

df <- data.frame(name, group, score, vote)

     name group score vote
1    Mary     A     1    1
2    John     A     1    0
3    Paul     A     1    0
4   Sally     A     1    1
5  Ronald     A     0    1
6    Adam     B     0    1
7   Nancy     B     1    1
8    Jane     B     0    1
9   Peter     B     1    0
10  Linda     B     0    1

Group A contains Mary, John, Paul, Sally, and Ronald. Mary, John, Paul, and Sally all have a score of 1 while Ronald has a score of 0. Within Group A, Mary, John, Paul, and Sally tend to be together in one group, while Ronald is in a group of his own.

When asked to vote on a topic, Mary, Sally, and Ronald vote together (1) while John and Paul vote together (0). I want to be able to note in the out column that Mary, Sally, and Ronald are in a "mixed" group (Mary and Sally have a score of 1 and Ronald has a score of 0 but voted 1) (1), and John and Paul (both with scores of 1 and votes of 0) (0).

Same idea with Group B. Adam, Jane, and Linda have scores of (0) and Nancy and Peter have scores of (1). Adam, Nancy, Jane, and Linda voted 1 while Peter voted 0. Since Peter has the "lone" vote, I want to mark him as (2) in the "out" column.

I want an output that looks like the following:

     name group score vote out
1    Mary     A     1    1   1
2    John     A     1    0   0
3    Paul     A     1    0   0
4   Sally     A     1    1   1
5  Ronald     A     0    1   1
6    Adam     B     0    1   1
7   Nancy     B     1    1   1
8    Jane     B     0    1   1
9   Peter     B     1    0   2
10  Linda     B     0    1   1

I am thinking of something with this structure:

df2 <- df %>%
  group_by(group, score) %>%
  mutate(
    out = ifelse(???)
  )

Any help would be appreciated!

user_8245406 · Answer

Instead of grouping by group and score, group by group and vote. Then use a case_when statement in mutate.

suppressPackageStartupMessages(library(dplyr))

name <- c("Mary", "John", "Paul", "Sally", "Ronald", "Adam", "Nancy", "Jane", "Peter", "Linda")
score <- c(1, 1, 1, 1, 0,
           0, 1, 0, 1, 0)
vote <- c(1, 0, 0, 1, 1,
          1, 1, 1, 0, 1)
group <- c("A", "A", "A", "A", "A",
           "B", "B", "B", "B", "B")
df <- data.frame(name, group, score, vote)

df %>%
  group_by(group, vote) %>%
  mutate(
    out = case_when(
      n() > 1 & vote == 0 ~ 0L,
      n() > 1 & vote == 1 ~ 1L,
      n() == 1 ~ 2L,
      TRUE ~ NA_integer_
    )
  )
#> # A tibble: 10 x 5
#> # Groups:   group, vote [4]
#>    name   group score  vote   out
#>         
#>  1 Mary   A         1     1     1
#>  2 John   A         1     0     0
#>  3 Paul   A         1     0     0
#>  4 Sally  A         1     1     1
#>  5 Ronald A         0     1     1
#>  6 Adam   B         0     1     1
#>  7 Nancy  B         1     1     1
#>  8 Jane   B         0     1     1
#>  9 Peter  B         1     0     2
#> 10 Linda  B         0     1     1

^{Created on 2022-03-23 by the reprex package (v2.0.1)}

Edit

much simpler is ifelse on the group size n().

df %>%
  group_by(group, vote) %>%
  mutate(out = ifelse(n() > 1, vote, 2))
#> # A tibble: 10 x 5
#> # Groups:   group, vote [4]
#>    name   group score  vote   out
#>         
#>  1 Mary   A         1     1     1
#>  2 John   A         1     0     0
#>  3 Paul   A         1     0     0
#>  4 Sally  A         1     1     1
#>  5 Ronald A         0     1     1
#>  6 Adam   B         0     1     1
#>  7 Nancy  B         1     1     1
#>  8 Jane   B         0     1     1
#>  9 Peter  B         1     0     2
#> 10 Linda  B         0     1     1

^{Created on 2022-03-23 by the reprex package (v2.0.1)}

How do I code each row based on the columns in groups?

1 Answers

Edit