How to aggregate across multiple columns in R using dplyr

Question

I have the following data frame:

data = data.frame(store=c('A','A','A','A','A','A','A','A','A','A','B','B','B','B','B','B','B','B','B','B'),
prod_id=c('p1','p1','p2','p2','p2','p1','p1','p1','p2','p2','p1','p1','p1','p2','p2','p2','p1','p1','p2','p2'),
Date=c('2017-01-01','2017-01-01','2017-01-01','2017-01-01','2017-01-01','2017-01-02','2017-01-02','2017-01-02','2017-01-02','2017-01-02','2017-01-01','2017-01-01','2017-01-01','2017-01-01','2017-01-01','2017-01-01','2017-01-02','2017-01-02','2017-01-02','2017-01-02'),
sales=c('12','13','15','10','12','9','12','8','7','11','10','1','5','9','6','7','1','4','1','5'))

data

     store prod_id  Date    sales
1      A      p1 2017-01-01    12
2      A      p1 2017-01-01    13
3      A      p2 2017-01-01    15
4      A      p2 2017-01-01    10
5      A      p2 2017-01-01    12
6      A      p1 2017-01-02     9
7      A      p1 2017-01-02    12
8      A      p1 2017-01-02     8
9      A      p2 2017-01-02     7
10     A      p2 2017-01-02    11
11     B      p1 2017-01-01    10
12     B      p1 2017-01-01     1
13     B      p1 2017-01-01     5
14     B      p2 2017-01-01     9
15     B      p2 2017-01-01     6
16     B      p2 2017-01-01     7
17     B      p1 2017-01-02     1
18     B      p1 2017-01-02     4
19     B      p2 2017-01-02     1
20     B      p2 2017-01-02     5

I want to aggregate sales across store-prod_id-Date to get the aggregated data as below:

>data_aggregate

   store prod_id   Date     sales
1     A      p1 2017-01-01    15
2     A      p2 2017-01-01    37
3     A      p1 2017-01-02    29
4     A      p2 2017-01-02    18
5     B      p1 2017-01-01    16
6     B      p2 2017-01-01    22
7     B      p1 2017-01-02     5
8     B      p2 2017-01-02     6

How can the above aggregation be achieved using dplyr or any other aggregate method in R?

score 0 · Accepted Answer · answered Sep 16 '20 at 11:49

Something like below using aggregate?

> aggregate(cbind(sales = as.numeric(sales))~.,data,sum)
  store prod_id       Date             sales
1     A      p1 2017-01-01                25
2     B      p1 2017-01-01                16
3     A      p2 2017-01-01                37
4     B      p2 2017-01-01                22
5     A      p1 2017-01-02                29
6     B      p1 2017-01-02                 5
7     A      p2 2017-01-02                18
8     B      p2 2017-01-02                 6

How to aggregate across multiple columns in R using dplyr

1 Answers1