Home > OS >  Mutate a column based on the presence/absence of values from two data frames in R
Mutate a column based on the presence/absence of values from two data frames in R

Time:11-11

I wish to mutate a new column called ClassType based on values in my Sales column.

My data looks as follows:

enter image description here

df <- data.frame(Group = c("A","A","A","A","A","B","B","B"),Sales = c("Bread","Bread","Milk","Milk","Bread","Cheese","Egg","Egg"), ClassType = c("Class1","Class1","Class1","Class1","Class1","Class2","Class2","Class2"))

I have two character vectors called x and y

x <- c("Bread","Milk") 
y <- c("Egg","Cheese","Rice")

My objective:

Within groups, if any value of Sales contains ANY value from x AND none of the values from y then Class1, if any value of Sales contains ANY value from x AND any value from y then Class2.

I've tried case_when and if_else statements with any() and data.table::%in% functions but I can not get the mutate to work

z <- df %>% group_by(Group) %>% mutate(ClassType = case_when(Sales == any(x) ~ "Class1",TRUE ~ "Class2"))

Any help would be greatly appreciated.

CodePudding user response:

library(dplyr)

df %>% 
  group_by(Group) %>% 
  mutate(ClassType = case_when(
    any(Sales %in% x) & !any(Sales %in% y) ~ "Case 1",
    any(Sales %in% x) & any(Sales %in% y) ~ "Case 2",
    T ~ "Case 3"
  ))

Output

# A tibble: 8 x 3
# Groups:   Group [2]
  Group Sales  ClassType
  <chr> <chr>  <chr>    
1 A     Bread  Case 2   
2 A     Bread  Case 2   
3 A     Cheese Case 2   
4 A     Milk   Case 2   
5 B     Bread  Case 2   
6 B     Cheese Case 2   
7 B     Egg    Case 2   
8 B     Egg    Case 2   
  • Related