Date manipulation in data.table and R

0
votes

I have a data.table of monthly stock returns, each row has a stock id string CUSIP, return RET in numeric and the data time stamp as Date.

For example:

returns =

 CUSIP     RET        Date
"1000"   -0.11  2001-01-15
"1000"    0.05  2001-02-18

I would like to set the day to the end of the month, and subtract one month to the date so that RET is effectively lagged by one month, something like:

returns_modified =

 CUSIP     RET        Date
"1000"   -0.11  2000-12-31
"1000"    0.05  2001-01-31

The reason I need this is so I can merge the lagged returns data with another data.table containing monthly information about the stocks. The way I have been doing is to add two columns, Month and Year containing the month and year of the time stamp. Then use integer arithmetic to shift the columns. So to shift the dat back by x:

Month = (Year*12+(Month-1)-x) %% 12 + 1
Year  = floor(Year*12+(Month-1)-x)

Merging by these two columns works, but it's very inelegant. Is there a simple solution for this?

1
rdatedata.table
Could you show dput(returns)? I'm not sure what class "Date" has. Anyway, normal dates can be rounded: ?round.POSIXt – Frank
library(zoo); as.Date(as.yearmon(date) - x/12, frac = 1) will set the date to the end of xth prior month. You might be able to avoid lagging entirely if data.table rolling joins are applicable here but a full description of the problem would be needed to really know. – G. Grothendieck
Hi, this is the output from dput (note there are around 2: structure(list(CUSIP = "02314110", RET = 0.059701, Date = structure(-2892, class = "Date")), .Names = c("CUSIP", "RET", "Date"), class = c("data.table", "data.frame")) – tanvach
Also just to add that there are around 2.5M rows to be processed, so I've been trying to find a fast and simple solution. – tanvach

1 Answers

1
votes

Is this what you want?

d = as.Date('2001-01-15')
d - as.POSIXlt(d)$mday
#[1] "2000-12-31"