Calculate Top N products by sales with in each year

user_15330237 0
Viewed 64

I have the data about sales by years and by-products, let's say like this:

Year <- c(2010,2010,2010,2010,2010,2011,2011,2011,2011,2011,2012,2012,2012,2012,2012)
Model <- c("a","b","c","d","e","a","b","c","d","e","a","b","c","d","e")
Sale <- c("30","45","23","33","24","11","56","19","45","56","33","32","89","33","12")
df <- data.frame(Year, Model, Sale)

I want the code which identifies the TOP 2 products by years and summarises all the rest products as category "other".

calculated-columns dataframe r loops
4 Answers

We could arrange by 'Year' and 'Sale' in descending order and then change the values of 'Model' based on the row_number after grouping by 'Year'

library(dplyr)
df %>% 
 arrange(Year, desc(Sale)) %>% 
 group_by(Year) %>%
 mutate(Model = case_when(row_number() > 2~ 'other', TRUE ~ Model)) %>%
 ungroup

Or another option is to use slice_max (with_ties = TRUE by default)

df %>%
  group_by(Year) %>% 
  mutate(Model =case_when(Model %in% {cur_data() %>% 
     slice_max(n = 2, order_by = Sale) %>%
     pull(Model)} ~ Model, TRUE ~ "other" )
)
user_37322710

Similar to akrun's solution: Slighlty other strategy:

library(dplyr)

df %>% 
  type.convert(as.is = TRUE) %>% 
  group_by(Year) %>% 
  arrange(desc(Sale), .by_group = TRUE) %>% 
  mutate(Model = ifelse(Model == first(Model, 2), Model, "Other"))
 

# Groups:   Year [3]
    Year Model  Sale
   <int> <chr> <int>
 1  2010 b        45
 2  2010 d        33
 3  2010 Other    30
 4  2010 Other    24
 5  2010 Other    23
 6  2011 b        56
 7  2011 e        56
 8  2011 Other    45
 9  2011 Other    19
10  2011 Other    11
11  2012 c        89
12  2012 a        33
13  2012 Other    33
14  2012 Other    32
15  2012 Other    12
user_133216470

Another possible solution (although I am not sure whether the OP is looking for an output as the one of my answer or as the one of the other answers):

library(tidyverse)

df %>% 
  group_by(Year) %>% 
  mutate(top = dense_rank(desc(Sale)) %>% as.character,
         top = if_else(top %in% c("1", "2"), top, "Other")) %>% 
  ungroup

#> # A tibble: 15 × 4
#>     Year Model Sale  top  
#>    <dbl> <chr> <chr> <chr>
#>  1  2010 a     30    Other
#>  2  2010 b     45    1    
#>  3  2010 c     23    Other
#>  4  2010 d     33    2    
#>  5  2010 e     24    Other
#>  6  2011 a     11    Other
#>  7  2011 b     56    1    
#>  8  2011 c     19    Other
#>  9  2011 d     45    2    
#> 10  2011 e     56    1    
#> 11  2012 a     33    2    
#> 12  2012 b     32    Other
#> 13  2012 c     89    1    
#> 14  2012 d     33    2    
#> 15  2012 e     12    Other
user_115644870

You can use fct_lump_n() from forcats to collapse levels, but first you need to uncount your data.

library(dplyr)
library(tidyr)
library(forcats)
df |> 
  mutate(Sale = as.integer(Sale)) |>
  uncount(Sale) |> 
  group_by(Year) |> 
  mutate(Model = fct_lump_n(Model, n = 2)) |>
  count(Model)

Output

#> # A tibble: 10 x 3
#> # Groups:   Year [3]
#>     Year Model     n
#>    <dbl> <fct> <int>
#>  1  2010 b        45
#>  2  2010 d        33
#>  3  2010 Other    77
#>  4  2011 b        56
#>  5  2011 Other    75
#>  6  2011 e        56
#>  7  2012 d        33
#>  8  2012 Other    44
#>  9  2012 a        33
#> 10  2012 c        89

(Not sure why 2012 keeps 4 groups rather than 3).

user_139682220
Related