CODEWITHSUNDEEP
rdateperiod
olive
olive

Reputation: 189

Aggregate dates into periods

We have data for 2 months. The date is in the following format: mm/dd/yyyy. We want to have 4 periods (of each 2 weeks):

Period1: 06/01/15 - 06/15/15
Period2: 06/16/15 - 06/30/15
Period3: 07/01/15 - 07/15/15
Period4: 07/16/15 - 07/31/15

In this way we would like to add 4 extra dummy columns to our dataset, namely Period1, Period2, etc.

Output example: enter image description here

Upvotes: 0

Views: 376

Answers (3)

J_F
J_F

Reputation: 10372

Another possibility is to use lubridate:

 library(lubridate)

 Period1 <- interval(start = mdy("06/01/15"), end = mdy("06/15/15"))
 Period2 <- interval(start = mdy("06/16/15"), end = mdy("06/30/15"))
 Period3 <- interval(start = mdy("07/01/15"), end = mdy("07/15/15"))
 Period4 <- interval(start = mdy("07/16/15"), end = mdy("07/31/15"))

 Period <- list(Period1, Period2, Period3, Period4)

 TestData <- mdy(c("06/15/15", "06/13/15", "06/20/15", "07/17/15"))

 sapply(1:length(TestData), function(x){
   as.numeric(TestData %within% Period[[x]])
 })

Upvotes: 0

G5W
G5W

Reputation: 37661

You will need to convert the strings into some form of date. I use POSIXct. After that, you can use cut to break dates into groups. From the groups you can use model.matrix to create the dummy variables. I added a few more test dates to better illustrate the result.

Breaks = as.POSIXct(c("06/01/15", "06/16/15", "07/01/15",
    "07/16/15", "08/01/15"), format="%m/%d/%y")

TestData = c("06/15/15", "06/13/15", "06/20/15", "07/17/15")
Periods  = cut(as.POSIXct(TestData, format="%m/%d/%y"), breaks=Breaks)
as.numeric(Periods)
[1] 1 1 2 4

Dummies = model.matrix(~ Periods - 1)
  Periods2015-06-01 Periods2015-06-16 Periods2015-07-01 Periods2015-07-16
1                 1                 0                 0                 0
2                 1                 0                 0                 0
3                 0                 1                 0                 0
4                 0                 0                 0                 1

Result = data.frame(TestData, Dummies)
names(Result) = c("Date", "Period1", "Period2", "Period3", "Period4")
Result
      Date Period1 Period2 Period3 Period4
1 06/15/15       1       0       0       0
2 06/13/15       1       0       0       0
3 06/20/15       0       1       0       0
4 07/17/15       0       0       0       1

Upvotes: 0

Julien Colomb
Julien Colomb

Reputation: 563

look into strptime to transform your mm/dd/yyyy date into numbers and then split() should be helpful, check this Split time-series weekly in R for a start..

z <- strptime(Date, "%m/%d/%y")

Upvotes: 0

Related Questions