How to assign value based on one unique key but evaluating two rows?-CodePudding

If for every unique id, one of the two value columns read 0 then I want the status to be single if not mixed. Any ideas on how to achieve this?

I do not want a solution with spread and gather. I can't figure out how to to do in dplyr

set.seed(111)
id <- c(1,1,2,2,3,3,4,4)
level <- c("high","low","high","low","high","low","high","low")
val <- c(9,0,2,4,1,0,0,2)
df <- data.frame(level, val,id)
df$level <- as.factor(as.character(df$level))

    level val id status
1  high   9  1  single
2   low   0  1  single
3  high   2  2  mixed
4   low   4  2  mixed
5  high   1  3  single
6   low   0  3  single
7  high   0  4  single
8   low   2  4  single

CodePudding user response：

Here's one way.

library(dplyr)

df %>%
  group_by(id) %>%
  mutate(status = ifelse(any(val == 0), 'single', 'mixed')) %>%
  ungroup()

#> # A tibble: 8 × 4
#>   level   val    id status
#>   <fct> <dbl> <dbl> <chr> 
#> 1 high      9     1 single
#> 2 low       0     1 single
#> 3 high      2     2 mixed 
#> 4 low       4     2 mixed 
#> 5 high      1     3 single
#> 6 low       0     3 single
#> 7 high      0     4 single
#> 8 low       2     4 single

CodePudding user response：

case_when option:

library(dplyr)
df %>%
  group_by(id) %>%
  mutate(status = case_when(any(val == 0) ~ "single",
                            TRUE ~ "mixed")) %>%
  ungroup()

Output:

# A tibble: 8 × 4
  level   val    id status
  <fct> <dbl> <dbl> <chr> 
1 high      9     1 single
2 low       0     1 single
3 high      2     2 mixed 
4 low       4     2 mixed 
5 high      1     3 single
6 low       0     3 single
7 high      0     4 single
8 low       2     4 single