CODEWITHSUNDEEP
rdplyr
indy anahh
indy anahh

Reputation: 33

In R, find adjacent rows given a particular column

I have a dataframe where I have a list of Clients and Categories, Products and the Department that has conducted outreach:

Client    |  Category   |  Product      |  Department
Mike S.   |  Home Goods |  Carpet       |  Sales
Mike S.   |  Outdoor    |  Shovel       |  Sales
Mike S.   |  Outdoor    |  Garden Hose  |  Marketing
Bill T.   |  Outdoor    |  Garden Hose  |  Marketing
Bill T.   |  Outdoor    |  Garden Hose  |  Sales
Bill T.   |  Outdoor    |  Leaf Blower  |  Sales
Bill T.   |  Home Goods |  Recliner     |  Marketing

I'm looking to provide a marketing email list where Sales has conducted outreach at the Category level (not product) but Marketing has NOT.

Here is the desired output:

Client    |  Category   |  Product      |  Department          
Mike S.   |  Home Goods |  Carpet       |  Sales                   
Bill T.   |  Outdoor    |  Leaf Blower  |  Sales

Upvotes: 0

Views: 77

Answers (2)

TimTeaFan
TimTeaFan

Reputation: 18541

Another option is to use all inside a grouped filter. all(Department != "Marketing") would do the job:

library(dplyr)

my_data %>%
  group_by(Client, Category) %>%
  filter(all(Department != "Marketing")) 

#> # A tibble: 1 x 4
#> # Groups:   Client, Category [1]
#>   Client  Category   Product Department
#>   <chr>   <chr>      <chr>   <chr>     
#> 1 Mike S. Home Goods Carpet  Sales

# data used
# my_data <- data.frame(
#      stringsAsFactors = FALSE,
#      Client = c("Mike S.","Mike S.",
#                 "Mike S.","Bill T.","Bill T.","Bill T.","Bill T."),
#      Category = c("Home Goods","Outdoor",
#                   "Outdoor","Outdoor","Outdoor","Outdoor","Home Goods"),
#          Product = c("Carpet","Shovel",
#                  "Garden Hose","Garden Hose","Garden Hose","Leaf Blower",
#                  "Recliner"),
#     Department = c("Sales","Sales","Marketing",
#                     "Marketing","Sales","Sales","Marketing")
#    )

Created on 2021-04-14 by the reprex package (v0.3.0)

Upvotes: 1

Jon Spring
Jon Spring

Reputation: 66415

Here's an approach that adds the number of Marketing and Sales outreaches within each Client-Category group, then filters for ones where Sales contacted but Marketing did not.

library(dplyr)
my_data %>%
  group_by(Client, Category) %>%
  mutate(Sales = sum(Department == "Sales"),
         Mktg  = sum(Department == "Marketing")) %>%
  ungroup() %>%
  filter(Sales >= 1, Mktg == 0)

Result

# A tibble: 1 x 6
  Client  Category   Product Department Sales  Mktg
  <chr>   <chr>      <chr>   <chr>      <int> <int>
1 Mike S. Home Goods Carpet  Sales          1     0

Data in loadable format, after pasting into a spreadsheet, deleting |s, and then using the datapasta package:

my_data <- data.frame(
  stringsAsFactors = FALSE,
             Client = c("Mike S.","Mike S.",
                        "Mike S.","Bill T.","Bill T.","Bill T.","Bill T."),
           Category = c("Home Goods","Outdoor",
                        "Outdoor","Outdoor","Outdoor","Outdoor","Home Goods"),
            Product = c("Carpet","Shovel",
                        "Garden Hose","Garden Hose","Garden Hose","Leaf Blower",
                        "Recliner"),
         Department = c("Sales","Sales","Marketing",
                        "Marketing","Sales","Sales","Marketing")
 )

Upvotes: 1

Related Questions