0

Summing rows by month in R

So I used this post to sum up my data by month in R, but the problem is, my data goes over multiple years. The current solution will treat, say, Jan 2018 and Jan 2019 as the same month. Is there a way to have them separated by month using the aggregate() and month() functions? Or will I have to change my approach?

My data frame looks like this:

           Day           Sales
    1      2019-02-04    219.12        
    2      2019-02-04    60.84
    3      2019-02-05    200.27
    4      2020-02-11    157.17
    5      2020-02-12    12.14

And my function looks like this:

bymonth<-aggregate(sales~month(day,label=TRUE),data=total.orders.by.date,FUN=sum)

Thanks

BalanceLuck
  • 3
  • 3
  • 1
    Is this what you want: `total.orders.by.date %>% mutate(Month=month(Day,label = T),Year=year(Day)) %>% group_by(Year,Month) %>% summarise(Sales=sum(Sales,na.rm=T))`? – Duck Aug 05 '20 at 18:54
  • Yes, this is exactly what I'm looking for! Thank you. – BalanceLuck Aug 05 '20 at 19:53
  • I have added as possible solution. If you think the answer was helpful you could accept it by clicking the tick on the left side of the answer :) – Duck Aug 05 '20 at 19:59

2 Answers2

0

Here is an example using lubridate library

library(tidyverse)
library(lubridate)
result<-data.frame(Date=as.Date(c("18/12/2013", "12/12/2019", "15/8/2017", "09/08/2018"), format = "%d/%m/%Y"), Item = c(8002, 8004, 8001, 8002), result=c(21.45, 5.35, 35.99, 20.83)) 
result_2 <- result %>% 
  mutate(Month_only = month(Date)) %>% 
  group_by(Month_only) %>% 
  filter(between(Month_only, 1, 12)) %>% 
  summarise(total = sum(result, rm.na =TRUE))

#     Month_only total
#         <dbl> <dbl>
# 1          8  56.8
# 2         12  26.8
Tho Vu
  • 1,304
  • 2
  • 8
  • 20
0

You can try this:

library(tidyverse)
library(lubridate)
#Data
total.orders.by.date <- structure(list(Day = structure(c(17931, 17931, 17932, 18303, 
18304), class = "Date"), Sales = c(219.12, 60.84, 200.27, 157.17, 
12.14)), row.names = c("1", "2", "3", "4", "5"), class = "data.frame")

#Code
total.orders.by.date %>% mutate(Month=month(Day,label = T),Year=year(Day)) %>%
  group_by(Year,Month) %>% summarise(Sales=sum(Sales,na.rm=T))

Output:

# A tibble: 2 x 3
# Groups:   Year [2]
   Year Month Sales
  <dbl> <ord> <dbl>
1  2019 Feb    480.
2  2020 Feb    169.
Duck
  • 39,058
  • 13
  • 42
  • 84