How to compare two data.frames and return rows based on conditions?

Question

I have two tables, the first a customer transaction list, the second is a product mapping table.

Customer Transaction List:

Cust_list <- data.frame(
stringsAsFactors = FALSE,
Customer = c("Mike S.","Tim P."),
Product_ID = c(233,6546)
)

Product Table:

Product_Table <- data.frame(
stringsAsFactors = FALSE,
Product_ID = c(233,256,296,8536,6546,8946),
Type = c("Shoes","Shoes","Shoes", "Socks","Socks","Socks"),
Category = c("A","B","B","A","B","B")
)

I have merged the two tables below (Right-Outer Join), and below is the combined output:

df <- merge(x=Cust_list, y=Product_Table, by="Product_ID", all.y=TRUE)

  Product_ID Customer  Type   Category
  233          Mike S.  Shoes    A
  256          <NA>     Shoes    B
  296          <NA>     Shoes    B
  6546         Tim P.   Socks    B
  8536         <NA>     Socks    A
  8946         <NA>     Socks    B

I am looking to find out only those customers -

• who have not purchased any category B product, and
• For these customers I only want to know what similar type of product of category B they have not purchased.
• since Tim P. purchased a category B Product for Type Socks, so he/she should be excluded from the output
• But since Mike S. has not purchased any B product and only purchased a product of A category, I just want to know which similar type (i.e. Shoes) of category B he/she hasn't purchased.

Based on the two tables above here is the desired output:

Customer  |  Product ID  | Type  | Category
Mike S.   | 256          | Shoes | B
Mike S.   | 296          | Shoes | B

Answer 1

Do this

Product_Table %>% filter(Category == "B") %>%
  right_join(
    Cust_list %>% left_join(Product_Table, by = "Product_ID") %>%
      group_by(Customer) %>%
      filter(!sum(Category == "B") > 0) %>%
      select(-c(Category, Product_ID)),
    by = "Type"
  )
  Product_ID  Type Category Customer
1        256 Shoes        B  Mike S.
2        296 Shoes        B  Mike S.

To check let's suppose customer table is like

> Cust_list
  Customer Product_ID
1  Mike S.        233
2   Tim P.       6546
3  Mike S.       8536
4      XYZ        296
5      ABC       8536
6      XYZ        233

Now the above code will give following output

  Product_ID  Type Category Customer
1        256 Shoes        B  Mike S.
2        296 Shoes        B  Mike S.
3       6546 Socks        B  Mike S.
4       6546 Socks        B      ABC
5       8946 Socks        B  Mike S.
6       8946 Socks        B      ABC

which should meet the expectation.