CODEWITHSUNDEEP
rdatedatetimedata.tableaggregate
user45415631
user45415631

Reputation: 175

Grouping dates by Periods in R

I a have a table:

   Date     Value1   Value2   ...
09/01/2008        
10/01/2008
11/01/2008
12/01/2008
01/01/2009
02/01/2009
03/01/2008
04/01/2009
05/01/2009
06/01/2009
07/01/2009
08/01/2008

I need to group dates next way: 09/01/2008 10/01/2008 11/01/2008 make equal to "2008" and the rest dates make equal to "2008.2009"

Is it possible to do something like that:

Date[Date>=09/01/2008 & Date<=11/01/2008] <- 2008
Date[Date>=12/01/2008 & Date<=08/01/2009] <- 2008.2009

The output should be in the following format :

Date        Value1   Value2  ...
2008        
2008
2008
2008.2009
2008.2009
2008.2009
2008.2009
2008.2009
2008.2009
2008.2009
2008.2009
2008.2009

Thank you!

Upvotes: 0

Views: 104

Answers (2)

KFB
KFB

Reputation: 3501

If the dates in your dataset range only from 2008/09/01 to 2009/08/01 and only requires of 2 year-groups, you could try to do below using data.table.

# Use @BondedDust toy data
library(data.table)
setDT(dat)  # convert to data table
dat[, new_col := ifelse(dt %between% c("2008-09-01", "2008-11-01"),
                        "2008", "2008.2009")]
dat

# you get
          Date         dt   new_col
 1: 09/01/2008 2008-09-01      2008
 2: 10/01/2008 2008-10-01      2008
 3: 11/01/2008 2008-11-01      2008
 4: 12/01/2008 2008-12-01 2008.2009
 5: 01/01/2009 2009-01-01 2008.2009
 6: 02/01/2009 2009-02-01 2008.2009
 7: 03/01/2009 2009-03-01 2008.2009
 8: 04/01/2009 2009-04-01 2008.2009
 9: 05/01/2009 2009-05-01 2008.2009
10: 06/01/2009 2009-06-01 2008.2009
11: 07/01/2009 2009-07-01 2008.2009
12: 08/01/2009 2009-08-01 2008.2009

Upvotes: 2

IRTFM
IRTFM

Reputation: 263301

dat <-read.table(text="Date  
09/01/2008        
10/01/2008
11/01/2008
12/01/2008
01/01/2009
02/01/2009
03/01/2009
04/01/2009
05/01/2009
06/01/2009
07/01/2009
08/01/2009",head=T)

dat$dt <- with( dat, as.Date(Date,format="%m/%d/%Y"))

with(dat, c("2008", "2008.2009", "NA")[ findInterval( dt,
                                            c( as.Date("2008/09/01") , 
                                               as.Date("2008/11/02") , 
                                               as.Date("2009/08/02") )
                                                    )
                                       ] )

# [1] "2008"      "2008"      "2008"      "2008.2009" "2008.2009" "2008.2009"
# [7] "2008.2009" "2008.2009" "2008.2009" "2008.2009" "2008.2009" "2008.2009"

You need to advance some of the dates by 1 day so that findInterval can abide by your somewhat irregular use of ">=" and "<="

Upvotes: 2

Related Questions