How to group variables that falls within a range of numbers

I a df like this

my_df <- data.frame(
b1 = c(2, 6, 3, 6, 4, 2, 1, 9, NA), 
b2 = c(100, 4, 106, 102, 6, 6, 1, 1, 7), 
b3 = c(75, 79, 8, 0, 2, 3, 9, 5, 80), 
b4 = c(NA, 6, NA, 10, 12, 8, 3, 6, 2),
b5 = c(2, 12, 1, 7, 8, 5, 5, 6, NA),
b6 = c(9, 2, 4, 6, 7, 6, 6, 7, 9),
b7 = c(1, 3, 7, 7, 4, 2, 2, 9, 5),
b8 = c(NA, 8, 4, 5, 1, 4, 1, 3, 6),
b9 = c(4, 5, 7, 9, 5, 1, 1, 2, 12))

I wanted to create a new column (NEW) based on the following assumptions.

If b9 is <= 2 write yellow.
If b9 is between 4 and 7 write white.
If b9 is >= 9 write green

The idea is to create something like this.

my_df1 <- data.frame(
b1 = c(2, 6, 3, 6, 4, 2, 1, 9, NA), 
b2 = c(100, 4, 106, 102, 6, 6, 1, 1, 7), 
b3 = c(75, 79, 8, 0, 2, 3, 9, 5, 80), 
b4 = c(NA, 6, NA, 10, 12, 8, 3, 6, 2),
b5 = c(2, 12, 1, 7, 8, 5, 5, 6, NA),
b6 = c(9, 2, 4, 6, 7, 6, 6, 7, 9),
b7 = c(1, 3, 7, 7, 4, 2, 2, 9, 5),
b8 = c(NA, 8, 4, 5, 1, 4, 1, 3, 6),
b9 = c(4, 5, 7, 9, 5, 1, 1, 2, 12),
NEW = c("white", "white", "white", "green", "white", "yellow", "yellow", "yellow", "green"))

I thought something like this will do it, but it didn't.

greater_threshold <- 2
greater_threshold1 <- 4
greater_threshold2 <- 7
greater_threshold3 <- 9
my_df1 <- my_df %>%
mutate(NEW = case_when(b9 <= greater_threshold ~ "yellow", b9 >= greater_threshold1 | b9 <= greater_threshold2 ~ "white", b9 >= greater_threshold3 ~ "green"))

Any help will be appreciated.

Your setup has some issues, for example what say you want would leave no label when b9 equals 3 or 8:

> If b9 is <= 2 write yellow. If b9 is between 4 and 7 write white. If
> b9 is >= 9 write green

I'm going to change the "white" condition to label b9 between 3 and 7, and "green" to >= 8, to maintain sanity. Then this should work:

greater_threshold1 <- 2
greater_threshold2 <- 7
my_df <- mutate(my_df,
NEW = case_when(
b9 > greater_threshold2 ~ 'green',
b9 > greater_threshold1 ~ 'white',
TRUE ~ 'yellow'
)) %>% print()

Output:

  b1  b2 b3 b4 b5 b6 b7 b8 b9    NEW
1  2 100 75 NA  2  9  1 NA  4  white
2  6   4 79  6 12  2  3  8  5  white
3  3 106  8 NA  1  4  7  4  7  white
4  6 102  0 10  7  6  7  5  9  green
5  4   6  2 12  8  7  4  1  5  white
6  2   6  3  8  5  6  2  4  1 yellow
7  1   1  9  3  5  6  2  1  1 yellow
8  9   1  5  6  6  7  9  3  2 yellow
9 NA   7 80  2 NA  9  5  6 12  green

case_when works best if you start with the narrowest condition, and then move wider as you go down the list of conditions - it executes the assignment operation as soon as it finds the condition that matches your variable, so you don't have to worry about the fact that, for example, b9=12 would match both of the first two conditions - case_when won't even get past the first condition. Then you don't even have to define your most permissive condition, just use TRUE which basically means, if you've gotten this far, here's the assignment you are left with.

Your code goes backwards, so e.g. b9=12 gets assigned "white" because it satisfies the second condition, b9 >= greater_threshold1 | b9 <= greater_threshold2 ~ "white", and the assignment function never gets to the one you really want, b9 >= greater_threshold3 ~ "green". Although you can also get what you want, I think, if you change the OR operator in the second condition to AND, i.e. b9 >= greater_threshold1 & b9 <= greater_threshold2 ~ "white". If you use the narrow to wider condition approach that I describe, though, you don't need to mess with &, plus you get cleaner code.

We can use cut() for this:

library(dplyr)
my_df %>%
mutate(NEW = cut(b9, 
breaks = c(-Inf, 2, 4, 7, Inf),
labels = c("yellow", "white", "white", "green"),
include.lowest = TRUE))
b1  b2 b3 b4 b5 b6 b7 b8 b9    NEW
1  2 100 75 NA  2  9  1 NA  4  white
2  6   4 79  6 12  2  3  8  5  white
3  3 106  8 NA  1  4  7  4  7  white
4  6 102  0 10  7  6  7  5  9  green
5  4   6  2 12  8  7  4  1  5  white
6  2   6  3  8  5  6  2  4  1 yellow
7  1   1  9  3  5  6  2  1  1 yellow
8  9   1  5  6  6  7  9  3  2 yellow
9 NA   7 80  2 NA  9  5  6 12  green

You can use between from dplyr:

my_df %>%
mutate(NEW = case_when(
b9 <= 2 ~ "Yellow",
between(b9, 4, 7) ~ "white",
b9 >= 9 ~ "green"
))

Output:

  b1  b2 b3 b4 b5 b6 b7 b8 b9    NEW
1  2 100 75 NA  2  9  1 NA  4  white
2  6   4 79  6 12  2  3  8  5  white
3  3 106  8 NA  1  4  7  4  7  white
4  6 102  0 10  7  6  7  5  9  green
5  4   6  2 12  8  7  4  1  5  white
6  2   6  3  8  5  6  2  4  1 Yellow
7  1   1  9  3  5  6  2  1  1 Yellow
8  9   1  5  6  6  7  9  3  2 Yellow
9 NA   7 80  2 NA  9  5  6 12  green

Those not falling within the conditions (ie, 8) will be NA