Aggregate multiple columns on different functions in R using groupby on multiple keys: Named aggregation in R

Question

df

     date       storeid itemid sales  biscuits   jelly        
1    2013-01-01     1    1      13       2        5
2    2013-01-01     1    2      11       3        2  
3    2013-01-02     2    1      14       4        3
4    2013-01-02     1    1      13       6        1
 ..........................    
1000 2015-12-05     1    1    10          1       12   and so on

I have this dataframe. I need something like this:

    Year-month   storeid   itemid   sum_sales  count_biscuits  mean_jelly
      2013-01      1         1        12           13               20
      2013-01      1         1        13           10               30

and so on.

So basically I wish to aggregate or groupby on 3 keys: year-month, store id and itemid. After that I want to calculate sum of sales, count of biscuits and mean of jelly.

df$Year-month <- format(as.Date(df$date), "%Y-%m")
group1<-df %>% group_by(storeid, itemid, Year-month ) %>% summarise(sum_sales = sum(sales))

How to add count of biscuits and mean of jelly. New to R so dont know how to do named aggregations here

Ronak Shah · Accepted Answer · 2020-04-10 04:43:27Z

2

After extracting the year-month aggregation you could group_by storeid, itemid and year_mon and perform different aggregation on different columns.

library(dplyr)

df %>%
  mutate(date = as.Date(date), 
        year_mon = format(date, "%Y-%m")) %>%
   group_by(storeid, itemid, year_mon) %>%
   summarise(sum_sales = sum(sales), 
             count_biscuits = sum(!is.na(biscuits)), 
             #If no `NA` values we can just count number of rows in group.
             #count_biscuits = n(),
             mean_jelly = mean(jelly))

edited Apr 10, 2020 at 4:43

answered Apr 10, 2020 at 4:36

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

noob Over a year ago

you mean count(biscuits)?

Ronak Shah Over a year ago

If you don't want sum of biscuits what do you want to count for each group? number of biscuit values or number of unique biscuit values?

noob Over a year ago

number of biscuit values

Ronak Shah Over a year ago

so that is just number of rows in each group, right? If you have NA values then use sum(!is.na(biscuits))

Collectives™ on Stack Overflow

Aggregate multiple columns on different functions in R using groupby on multiple keys: Named aggregation in R

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related