Select the top N values with conditions

Question

I have a data set as this one:

data <- tribble(
  ~shop_name,  ~category,       ~NumberOfProducts,
  "A",         "Game",            50,
  "A",         "Book",            40,
  "A",         "Electronic",      30,
  "B",         "Home",            90, 
  "B",         "Game",           100,
  "B",         "Electronic",      50,
  "C",         "Home",            60, 
  "C",         "Book",            30, 
  "A",         "Garden",          15,
  "B",         "Garden",          10,
)

But now, I want to create a new data frame something like this:

newdata <- tribble(
  ~shop_name,  ~top_category,
   "A",        "Game, Book, Electronic",  
   "B",        "Game, Home, Electronic",
   "C",        "Home, Book"
)

That means I want to group my data based on the "shop_name", and then I want to create a new variable (top_category) that shows the top three categories based on the ´NumberOfProducts`.

I've already tried to code it. But I got only top three when I coded like this:

data %>% top_n(3, NumberOfProducts)

Would someone help me to get the new data that shows the top three categories?

score 2 · Accepted Answer · answered Oct 29 '19 at 14:31

You where on the right track with ''group my data''. You can use group_by to apply the top_n function by shop. To transform them to one row you can then use summarize with toString

data %>% 
  group_by(shop_name) %>% 
  top_n(3, NumberOfProducts) %>% 
  summarize(top_category = toString(category))

# A tibble: 3 x 2
# shop_name top_category          
# <chr>     <chr>                 
# 1 A         Game, Book, Electronic
# 2 B         Home, Game, Electronic
# 3 C         Home, Book

score 1 · Answer 2 · answered Oct 29 '19 at 14:30

you can group with respect to shop_name and then use summarise and paste the top categories:

data %>% group_by(shop_name) %>% 
 top_n(3, NumberOfProducts) %>%
 arrange(-NumberOfProducts) %>%
 summarise(top_category = paste(category, collapse = ", "))

# A tibble: 3 x 2
  shop_name top_category          
  <chr>     <chr>                 
1 A         Game, Book, Electronic
2 B         Game, Home, Electronic
3 C         Home, Book

Select the top N values with conditions

2 Answers2