I am trying to add a new column to my dataframe that displays either a red, yellow, or green "status" based upon the existence of a value within each group.
library(dplyr)
a <- c("A", "A", "B", "B", "C", "C")
b <- c(90,80,90,90,90,70)
df <- data.frame("Group" = a, "Value" = b)
> df
Group Value
1 A 90
2 A 80
3 B 90
4 B 90
5 C 90
6 C 70
If any value in a group is less than 80 I want each row of RYG to be red; if any value in a group is between 80 and 90 I want it to be yellow, and anything 90 or greater is green. I am trying to use any() to accomplish this but it seems to be overriding the group_by:
df_2 <- df %>%
group_by(Group) %>%
mutate(RYG = if_else(any(Value, na.rm = TRUE) < 90 & any(Value, na.rm = TRUE) >= 80, "YELLOW", if_else(any(Value, na.rm = TRUE) < 80, "RED", "GREEN")))
Warning messages:
1: In any(Value, na.rm = TRUE) :
coercing argument of type 'double' to logical
> df_2
# A tibble: 6 x 3
# Groups: Group [3]
Group Value RYG
<fct> <dbl> <chr>
1 A 90 RED
2 A 80 RED
3 B 90 RED
4 B 90 RED
5 C 90 RED
6 C 70 RED
Expected outcome is:
> df_2
# A tibble: 6 x 3
# Groups: Group [3]
Group Value RYG
<fct> <dbl> <chr>
1 A 90 YELLOW
2 A 80 YELLOW
3 B 90 GREEN
4 B 90 GREEN
5 C 90 RED
6 C 70 RED