Using R, for each year, I need to sum the sales in different years between same two dates

user_5157636 0
Viewed 604

For two different years, for each year, I need to sum all the sales that occurred from January 3 through March 3. I would prefer a dplyr solution.

All the possible solutions I looked at in stackoverflow used SQL, not R. If someone knows of a solution I missed, please let me know.

In R, I know how to work with groups and to use a variety of dplyr functions, but I need help doing what this post is about.

I would like the output to look like this:

Year   Total Sales
2020   138 
2021   196

Input

df <- data.frame(date=c(20200102, 20200107, 20200210, 20200215, 20200216, 20200302, 20200305, 20210101, 20210104, 20210209, 20210211, 20210215, 20210317, 20210322),
                  sales=c(9,14,27,30,33,34,36,44,45,47,51,53,56,58))
dplyr r
4 Answers

One row less than my master akrun's solution :)

  1. With ymd function of lubridate package transform character type to date.
  2. With DayMonth function consider only month and day for the desired interval by month and day
  3. group by year
  4. filter the interval
  5. summarise
library(lubridate)
df %>% 
    mutate(date = ymd(date)) %>% 
    mutate(DayMonth = format(as.Date(date), "%m-%d")) %>% 
    group_by(Year=year(date)) %>% 
    filter(DayMonth>"01-03" & DayMonth<"03-03") %>% 
    summarise(Total_Sales = sum(sales))

Output:

   Year Total_Sales
  <int>       <dbl>
1  2020         138
2  2021         196
user_133216470

You can also use the following solution for your purpose:

library(dplyr)
library(lubridate)

df %>%
  mutate(date = ymd(date)) %>%
  group_by(year = year(date)) %>%
  filter(date %within% interval(ymd(paste(first(year), "01-03", sep = "-")), 
                                ymd(paste(first(year), "03-03", sep = "-")))) %>%
  summarise(sale = sum(sales))

# A tibble: 2 x 2
   year  sale
  <dbl> <dbl>
1  2020   138
2  2021   196
user_143145200

We can use tidyverse. Convert the 'date' to Date class (with ymd from lubridate), get the month and day from the 'date', create a new date with ISOdate with year standardized to a single year (here we chose 2021 - it can be any year though), then we filter the 'newdate' using between and specify the left and right arguments as the custom date range, then do a group by 'year' and get the sum of 'sales' in summarise

library(dplyr)
library(lubridate)
df %>%
  mutate(date = ymd(date), year = year(date),
   month = month(date), day = day(date), 
   newdate = as.Date(ISOdate(2021, month, day))) %>% 
  filter(between(newdate, as.Date("2021-01-03"), 
        as.Date("2021-03-03"))) %>% 
  group_by(year) %>%
  summarise(sales = sum(sales))

-output

# A tibble: 2 x 2
#   year sales
#  <dbl> <dbl>
#1  2020   138
#2  2021   196

Or using aggregate from base R. Create the 'newdate' by subsitution i.e. remove the first 4 characters (.{4}) from the start (^) of the 'date', replace with '2021', convert to Date class, do a subset with relational operators. Then use the filtered dataset in aggregate to get the sum of 'sales' by the year part i.e. first 4 characters

subdf <- subset(transform(df, newdate = as.Date(sub("^.{4}", "2021", 
         date), '%Y%m%d')),
     newdate >= as.Date('2021-01-03') & newdate <= as.Date('2021-03-03'))
aggregate(sales ~ cbind(Year = substr(date, 1, 4)), subdf, FUN = sum)
#  Year sales
#1 2020   138
#2 2021   196
user_37322710

A simple solution using only integer/modulo division, %% & %/%, i.e. without using any date type library (lubridate, etc.)

  • Since your date variable follow most logical (and most suited for aplhabetical sorting of dates) format, the job here is to check condition/grouping over first four digits, filtering over last four digits and summarise. So
  • group_by on Year which is obtained by integer division i.e. %/% date by 10000 which will give you first four digits always (in case of YYYYMMDD format)
  • No need to create this column first and then group_by
  • Thereafter filter in the rows using modulo division %% of date by 10000 obtaining last four digits and check your condition
  • summarise lastly
  • In case your date column is of character type wrap it with as.numeric in all the steps
library(dplyr)

df %>% 
  group_by(Year = date %/% 10000) %>%
  filter(date %% 10000 > 103, date %% 10000 < 303) %>%
  summarise(Total_sales = sum(sales))

#> # A tibble: 2 x 2
#>    Year Total_sales
#>   <dbl>       <dbl>
#> 1  2020         138
#> 2  2021         196

Created on 2021-05-30 by the reprex package (v2.0.0)

equivalent baseR syntax

aggregate(sales ~ cbind(Year = date %/% 10000), 
          subset(df, date %% 10000 > 103 & date %% 10000 < 303), 
          FUN = sum)
  Year sales
1 2020   138
2 2021   196
user_28848590
Related