每年通过销售来计算顶级产品

Question

我有几年和副产品的销售数据，假设是这样的：

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)

我想要按年来识别前2个产品的代码，并将所有其余产品汇总为“其他”类别。

Answer 1

类似于Akrun的解决方案：其他策略很高：

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

Answer 2

我们可以在desc结束订单中按'Year'安排 ，然后在按'Year'

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

---

或另一个 之后根据Row_number更改'模型'的值选项是使用slice_max（with_ties = true默认情况下）

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

Answer 3

另一个可能的解决方案（尽管我不确定OP是否正在寻找输出作为我的答案之一，还是其他答案之一）：

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

Answer 4

您可以从forcats使用fct_lump_n（）来折叠级别，但是首先您需要nocount 您的数据。

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)

输出

#> # 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

（不确定为什么2012年保留4组而不是3组）。