Data.table: increase IDateTime timeout on hourly hourly data

Question

How to increase data.table idate and itime by one hour?

I want to do this to pack my data as described here from Christoph_J.

My data looks like

 > dt idate itime windgeschwindigkeit 1: 1958-02-01 00:00:00 -0.9049475 2: 1958-02-01 01:00:00 -0.9049475 3: 1958-02-01 02:00:00 -0.9049475 4: 1958-02-01 03:00:00 -1.0049475 5: 1958-02-01 04:00:00 -2.0049475 --- 498020: 2014-11-24 19:00:00 -1.0852256 498021: 2014-11-24 20:00:00 -0.7852256 498022: 2014-11-24 21:00:00 -0.8852256 498023: 2014-11-24 22:00:00 -1.0852256 498024: 2014-11-24 23:00:00 -1.3852256 

I tried to defer it using the code from the SO answer mentioned above, as follows:

 setkeyv(dt, c("idate","itime")) m_col = "windgeschwindigkeit" pm_col = parse(text="windgeschwindigkeit") lagg = 1 dt[, paste0(m_col,"_",lagg) := dt[list(idate,itime+lagg*3600), eval(pm_col), roll=-1]] 

which leads to the expected result:
One new column that is one hour behind. BUT (see below)

 > dt idate itime windgeschwindigkeit windgeschwindigkeit_1 1: 1958-02-01 00:00:00 -0.9049475 -0.9049475 2: 1958-02-01 01:00:00 -0.9049475 -0.9049475 3: 1958-02-01 02:00:00 -0.9049475 -1.0049475 4: 1958-02-01 03:00:00 -1.0049475 -2.0049475 5: 1958-02-01 04:00:00 -2.0049475 -2.0049475 --- 498020: 2014-11-24 19:00:00 -1.0852256 -0.7852256 498021: 2014-11-24 20:00:00 -0.7852256 -0.8852256 498022: 2014-11-24 21:00:00 -0.8852256 -1.0852256 498023: 2014-11-24 22:00:00 -1.0852256 -1.3852256 498024: 2014-11-24 23:00:00 -1.3852256 NA 

But all rows that are multiples of 24, now NA now as list(idate,itime+lagg*3600) increases the itime hour from 0:23 to 1:24, and the data table cannot correspond to itime hour 24 for any results.

 > dt[c(24,48)] idate itime windgeschwindigkeit windgeschwindigkeit_1 1: 1958-02-01 23:00:00 0.5950525 NA 2: 1958-02-02 23:00:00 4.0939842 NA 

Any ideas how to fix this, such as increasing idate and itime by 1 hour? Any help is greatly appreciated.

I managed to do this with the following "workaround" using as.POSIXct , but it is not very efficient:

 setkeyv(dt, c("idate","itime")) m_col = "windgeschwindigkeit" pm_col = parse(text="windgeschwindigkeit") lagg = 1 new_time <- dt[,IDateTime(as.POSIXct(idate)+itime+lagg*3600)] dt[, paste0(m_col,"_",lagg) := dt[new_time, eval(pm_col), roll=-1]] 

dput header of my data:

 structure(list(idate = structure(c(-4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4352L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L, -4351L), class = c("IDate", "Date")), itime = structure(c(0L, 3600L, 7200L, 10800L, 14400L, 18000L, 21600L, 25200L, 28800L, 32400L, 36000L, 39600L, 43200L, 46800L, 50400L, 54000L, 57600L, 61200L, 64800L, 68400L, 72000L, 75600L, 79200L, 82800L, 0L, 3600L, 7200L, 10800L, 14400L, 18000L, 21600L, 25200L, 28800L, 32400L, 36000L, 39600L, 43200L, 46800L, 50400L, 54000L, 57600L, 61200L, 64800L, 68400L, 72000L, 75600L, 79200L, 82800L), class = "ITime"), windgeschwindigkeit = c(-0.904947510665982, -0.904947510665982, -0.904947510665982, -1.00494751066598, -2.00494751066598, -2.00494751066598, -2.90494751066598, -2.50494751066598, -2.50494751066598, -1.40494751066598, -1.50494751066598, -1.30494751066598, -1.00494751066598, -0.704947510665983, -0.504947510665983, -0.504947510665983, -0.204947510665982, -0.104947510665983, 0.0950524893340177, 1.09505248933402, 0.195052489334017, -0.204947510665982, 0.0950524893340177, 0.595052489334018, 1.79398421777773, 2.99398421777773, 3.39398421777773, 3.29398421777773, 2.99398421777773, 2.89398421777773, 1.89398421777773, 0.593984217777727, 0.293984217777727, -0.706015782222273, -0.706015782222273, -0.806015782222273, -0.406015782222273, 0.893984217777727, -0.206015782222273, -0.606015782222273, -0.00601578222227328, 0.693984217777727, 1.29398421777773, 2.49398421777773, 3.79398421777773, 4.29398421777773, 3.99398421777773, 4.09398421777773)), .Names = c("idate", "itime", "windgeschwindigkeit"), row.names = c(NA, -48L), class = c("data.table", "data.frame"), sorted = c("idate", "itime")) 

Answer 1

I simply clicked on the shift() function, which is capable of generating input / delay vectors of several periods. It always returns a list. See this question . Although to use it you will need v1.9.5 , which is the current version of the development - Installation Instructions.

With this, IIUC, what you would like to do can be done as follows:

 require(data.table) ## v1.9.5+ dt[, lead_1 := shift(windgeschwindigkeit, 1L, type="lead"), by=.(idate)] 

The itime column corresponding to idate is idate to be in the correct order. If not, you can do:

 dt[order(idate, itime), lead_1 := shift(windgeschwindigkeit, 1L, type="lead"), by=.(idate)]