Filter data by last 12 Months of the total data available in R

user_13184941 0
Viewed 1667

R:

I have a data-set with N Products sales value from some yyyy-mm-dd to some yyyy-mm-dd, I just want to filter the data for the last 12 months for each product in the data-set.

Eg: Say, I have values from 2016-01-01 to 2020-02-01 So now I want to filter the sales values for the last 12 months that is from 2019-02-01 to 2020-02-01 I just cannot simply mention a "filter(Month >= as.Date("2019-04-01") & Month <= as.Date("2020-04-01"))" because the end date keeps changing for my case as every months passes by so I need to automate the case.

r date
4 Answers

You can use : 

library(dplyr)
library(lubridate)
data %>%
  group_by(Product) %>%
  filter(between(date, max(date) - years(1), max(date)))
  #filter(date >= (max(date) - years(1)) & date <= max(date))
user_39629140

You can test whether the date is bigger equal the maximal date per product minus 365 days:

library(dplyr)
df %>% 
  group_by(Products) %>% 
  filter(Date >= max(Date)-365)

# A tibble: 6 x 2
# Groups:   Products [3]
  Products Date      
     <dbl> <date>    
1        1 2002-01-21
2        1 2002-02-10
3        2 2002-02-24
4        2 2002-02-10
5        2 2001-07-01
6        3 2005-03-10

Data

df <- data.frame(
  Products = c(1,1,1,1,2,2,2,3,3,3), 
  Date = as.Date(c("2000-02-01", "2002-01-21", "2002-02-10", 
                   "2000-06-01", "2002-02-24", "2002-02-10",
                   "2001-07-01", "2003-01-02", "2005-03-10",
                   "2002-05-01")))
user_62421320

If your aim is to just capture entries from today back to the same day last year, then:

The function Sys.Date() returns the current date as an object of type Date. You can then convert that to POSIXlc form to adjust the year to get the start date. For example:

end.date <- Sys.Date()
end.date.lt <- asPOSIXlt(end.date)
start.date.lt <- end.date.lt
start.date.lt$year <- start.date.lt$year - 1
start.date <- asPOSIXct(start.date.lt)

Now this does have one potential fail-state: if today is February 29th. One way to deal with that would be to write a "today.last.year" function to do the above conversion, but give an explicit treatment for leap years - possibly including an option to count "today last year" as either February 28th or March 1st, depending on which gives you the desired behaviour.

Alternatively, if you wanted to filter based on a start-of-month date, you can make your function also set start.date.lt$day = 1, and so forth if you need to adjust in different ways.

user_62334180

Input:

   product       date
 1:       a 2017-01-01
 2:       b 2017-04-01
 3:       a 2017-07-01
 4:       b 2017-10-01
 5:       a 2018-01-01
 6:       b 2018-04-01
 7:       a 2018-07-01
 8:       b 2018-10-01
 9:       a 2019-01-01
10:       b 2019-04-01
11:       a 2019-07-01
12:       b 2019-10-01

Code:

library(lubridate)
library(data.table)

DT <- data.table(
  product = rep(c("a", "b"), 6),
  date = seq(as.Date("2017-01-01"), as.Date("2019-12-31"), by = "quarter")
)

yearBefore <- function(x){
  year(x) <- year(x) - 1
  x
}

date_DT <- DT[, .(last_date = last(date)), by = product]
date_DT[, year_before := yearBefore(last_date)]
result <- DT[, date_DT[DT, on = .(product, year_before <= date), nomatch=0]]
result[, last_date := NULL]
setnames(result, "year_before", "date")

Output:

   product       date
1:       a 2018-07-01
2:       b 2018-10-01
3:       a 2019-01-01
4:       b 2019-04-01
5:       a 2019-07-01
6:       b 2019-10-01

Is this what you are looking for?

user_121484020
Related