aggregation by sum of month divided by groups in R

Question

here mydata

mydata=structure(list(doc_date = structure(c(7L, 9L, 4L, 10L, 2L, 5L, 
8L, 1L, 3L, 6L), .Label = c("01.06.2018", "06.04.2018", "08.07.2018", 
"14.03.2018", "20.04.2018", "21.09.2018", "24.01.2018", "25.05.2018", 
"28.02.2018", "28.03.2018"), class = "factor"), shop_id = c(67885L, 
67885L, 67885L, 67885L, 67885L, 67885L, 67885L, 67885L, 67885L, 
67885L), shop_code = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L), .Label = "02293НСК", class = "factor"), product_id = c(11622L, 
11622L, 11622L, 11622L, 11622L, 11622L, 11622L, 11622L, 11622L, 
11622L), product_group_id = c(5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L), city_id = c(9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L), 
    fin_centre_id = c(15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
    15L, 15L), return_count = c(2L, 3L, 1L, 1L, 1L, 1L, 3L, 1L, 
    3L, 2L)), .Names = c("doc_date", "shop_id", "shop_code", 
"product_id", "product_group_id", "city_id", "fin_centre_id", 
"return_count"), class = "data.frame", row.names = c(NA, -10L
))

How can i aggregate column return_count for groups shop_code+product_id for each month, by sum in pivot format.

I.E. output

  jan feb march apr may jun jul aug sept oct nov dec
1   2   3     2   2   3   1   3   0    2   0   0   0

this topic is not dublicate Aggregate / summarize multiple variables per group (e.g. sum, mean) cause i need pivot format

Possible duplicate of [Aggregate / summarize multiple variables per group (e.g. sum, mean)](https://stackoverflow.com/questions/9723208/aggregate-summarize-multiple-variables-per-group-e-g-sum-mean) — NelsonGon, Feb 27 '19 at 13:37

rove · Answer 1 · 2019-02-27T14:14:59.837

Here is a data.table approach:

EDITED to include months with 0 counts in results

library(data.table)
library(lubridate)
setDT(mydata)

# First make a variable storing the month 
mydata[, month := lubridate::month(as.Date(doc_date, format = "%d.%m.%y"), label = TRUE)]

# Then sum return_count by the product id, group id and month. Keep only rows that are unique by month
mydata <- unique(mydata[, sum := sum(return_count), by = .(product_id, product_group_id, month), ], by = "month")

# Now we need to make sure any months with 0 counts are included
all_months <- data.table(month = lubridate::month(1:12, label = TRUE) )
mydata <- merge(mydata[, .(month, sum)], all_months, by = "month", all.y = TRUE)
mydata[is.na(sum), sum := 0]

## output
 month sum
 1:   Jan   2
 2:   Feb   3
 3:   Mar   2
 4:   Apr   2
 5:   May   3
 6:   Jun   1
 7:   Jul   3
 8:   Aug   0
 9:   Sep   2
10:   Oct   0
11:   Nov   0
12:   Dec   0

score 1 · Answer 2 · answered Feb 27 '19 at 13:11

> require(tidyverse)
> mydata$months <- months(dmy(mydata$doc_date))
> my <- mydata %>% group_by( months) %>% summarise(re_count = sum(return_count,na.rm = T))
> my

# A tibble: 8 x 2
  months    re_count
  <chr>        <int>
1 April            2
2 Februar          3
3 Januar           2
4 Juli             3
5 Juni             1
6 Mai              3
7 März             2
8 September        2
>

would be the solution I would come up with using the tidyverse approach. (Sorry, for whatever reason my months are in German).

My months are also in German. You can try `Sys.setlocale("LC_TIME", "English")` — Cettt, Feb 27 '19 at 13:48

score 1 · Answer 3 · answered Feb 27 '19 at 13:23

The problem is you have different dates (that too as factor) of same months, so first we will summarise at month level and we can pivot up. Try this :

 mydata$new_date <- dmy(mydata$doc_date) # convert to date format)
 mydata$month <- month(mydata$new_date) # extract month from date

 mydata <- mydata %>% group_by(shop_code,product_id,month) %>% summarise(return_count= sum(return_count)) # group at your required level

 mydata_1 <- dcast(setDT(mydata), shop_code + product_id  ~ month , fun.aggregate = sum, value.var = c("return_count")) # Pivot up using dcast

aggregation by sum of month divided by groups in R

3 Answers3