按销售和年来计算顶级产品

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)

数年的产品：

  a = 30+11+33 = 74
B = 45+56+32 = 133
C = 23+19+89 = 131
D = 33+45+33 = 111
E = 12+56+24 = 92
 

根据这三年内的总销售额进行排名：

1 2 3 4 5 
b c d e a

我希望按年来确定前2个产品（根据这3年内的总销售额）的代码，并将所有其余产品汇总为“其他”类别。因此，输出应该是这样：

 年度销售
2010 B 45
2010 C 23
2010其他30+33+24 = 92
2011 B 56
2011 C 19
2011其他11+45+56 = 112
2012 B 32
2012 C 89
2012其他33+33+12 = 78
 

Answer 1

一个 tidyverse 解决方案。您的销售数据似乎存储为字符，这意味着我们必须在求和之前使用as.numeric。

library(tidyverse)

df %>% 
  group_by(Model) %>% 
  mutate(
    Sale = as.numeric(Sale),
    total_sale = sum(Sale)
  ) %>% 
  ungroup %>% 
  mutate(
    model_condensed = ifelse(total_sale %in% rev(sort(unique(total_sale)))[1:2], Model, 'other')
  ) %>% 
  group_by(Year, model_condensed) %>% 
  summarize(Sale = sum(Sale))

   Year model_condensed  Sale
  <dbl> <chr>           <dbl>
1  2010 b                  45
2  2010 c                  23
3  2010 other              87
4  2011 b                  56
5  2011 c                  19
6  2011 other             112
7  2012 b                  32
8  2012 c                  89
9  2012 other              78

以上解决方案通过匹配sale中的值来创建“其他”类别。如果这些值具有小数位置，这可能会导致问题（请参见这个问题）。取而代之的是，我们可以使用两步过程来按名称识别前两个模型，并使用它来为总数据创建分组：

totals <- df %>% 
  group_by(Model) %>% 
  summarize(total_sale = sum(as.numeric(Sale))) %>% 
  arrange(desc(total_sale)) %>% 
  slice_head(n = 2)

df %>% 
  group_by(Year, model_condensed = ifelse(Model %in% totals$Model, Model, 'other')) %>% 
  summarize(Sale = sum(as.numeric(Sale)))