Ifelse with conditional on grouped data-CodePudding

This has got to be simple, but I'm stuck. I want to mutate some grouped data using a where statement within an ifelse statement. Here's an example that works:

example <- tibble::tribble(
  ~Group, ~Code, ~Value,
  "1", "A", 1,
  "1", "B", 1,
  "1", "C", 5,
  "2", "A", 1,
  "2", "B", 5,
  "2", "C", 2
)

example %>% group_by(Group) %>%
  mutate(GroupStatus=ifelse(Value[Code=="C"]==5, 1, 0))

This gives the desired result:

  Group Code  Value GroupStatus
  <chr> <chr> <dbl>       <dbl>
1 1     A         1           1
2 1     B         1           1
3 1     C         5           1
4 2     A         1           0
5 2     B         5           0
6 2     C         2           0

The problem is when one of the groups is missing Code C, as below:

example2 <- tibble::tribble(
  ~Group, ~Code, ~Value,
  "1", "A", 1,
  "1", "B", 1,
  "1", "C", 5,
  "2", "A", 1,
  "2", "B", 5
)

example2 %>% group_by(Group) %>%
  mutate(GroupStatus=ifelse(Value[Code=="C"]==5, 1, 0))

This gives me an error: Error: Problem with mutate() column GroupStatus. i GroupStatus = ifelse(Value[Code == "C"] == 5, 1, 0). i GroupStatus must be size 2 or 1, not 0. i The error occurred in group 2: Group = "2".

What I'd like is for "GroupStatus" in any group that is missing Code C to just be set to zero. Is that possible?

CodePudding user response：

You really only have a single condition to check per group, so we can simplify to an any() instead of ifelse():

example2 %>% 
  group_by(Group) %>%
  mutate(GroupStatus = as.integer(any(Value == 5 & Code == "C")))
# # A tibble: 5 × 4
# # Groups:   Group [2]
#   Group Code  Value GroupStatus
#   <chr> <chr> <dbl>       <dbl>
# 1 1     A         1           1
# 2 1     B         1           1
# 3 1     C         5           1
# 4 2     A         1           0
# 5 2     B         5           0

CodePudding user response：

Another possible solution, based on a nested ifelse:

library(dplyr)

example2 <- tibble::tribble(
  ~Group, ~Code, ~Value,
  "1", "A", 1,
  "1", "B", 1,
  "1", "C", 5,
  "2", "A", 1,
  "2", "B", 5
)

example2 %>% 
  group_by(Group) %>% 
  mutate(GroupStatus = ifelse("C" %in% Code, 
     ifelse(Value[Code == "C"] == 5, 1, 0), 0)) %>% 
  ungroup

#> # A tibble: 5 × 4
#>   Group Code  Value GroupStatus
#>   <chr> <chr> <dbl>       <dbl>
#> 1 1     A         1           1
#> 2 1     B         1           1
#> 3 1     C         5           1
#> 4 2     A         1           0
#> 5 2     B         5           0