Calculating dates with days, not seconds
I have 2 columns:
date1 date2
2015-01-01 2015-01-31
2015-01-02 2015-01-31
2015-01-03 2015-01-31
2015-10-27 2015-08-31
I want to create a new column diff
based on date1
and date2
.
data[,diff := pmax(0, date2 - date1)]
As a result, I want:
date1 date2 diff
2015-01-01 2015-01-31 30
2015-01-02 2015-01-31 29
2015-01-03 2015-01-31 28
2015-10-27 2015-08-31 -57
However, I got the following:
date1 date2 diff
2015-01-01 2015-01-31 2592000
2015-01-02 2015-01-31 2505600
2015-01-03 2015-01-31 2419200
2015-10-27 2015-08-31 0
Date calculation uses seconds . But I want days .
Any help?
source to share
Assuming we need to change negative values to 0 based on usage max
, then replace max
withpmax
library(data.table)
setDT(data)[, diff := pmax(0, date2-date1)]
data
# date1 date2 diff
#1: 2015-01-01 2015-01-31 30
#2: 2015-01-02 2015-01-31 29
#3: 2015-01-03 2015-01-31 28
#4: 2015-10-27 2015-08-31 0
and if we keep an eye on the expected output
setDT(data)[, diff := as.integer(date2-date1)]
data
# date1 date2 diff
#1: 2015-01-01 2015-01-31 30
#2: 2015-01-02 2015-01-31 29
#3: 2015-01-03 2015-01-31 28
#4: 2015-10-27 2015-08-31 -57
Update
Based on the OP's output file, we think it might be based on the original dataset, one parameter difftime
, where the argument unit
causes the output to be "day".
setDT(data)[, diff := pmax(0, difftime(date2,date1, units = 'day'))]
and for the second case
setDT(data)[, diff := as.integer(difftime(date2,date1, units = 'day'))]
OP may get output due to dates with less difference, which changes it to sec
instead ofday
Update2
OP confirmed that the 'date' columns were POSIXct
. So we can also convert it to Date
and get the difference with-
data[, diff := as.integer(as.Date(date2) - as.Date(date1))]
data
data <- structure(list(date1 = structure(c(16436, 16437, 16438, 16735
), class = "Date"), date2 = structure(c(16466, 16466, 16466,
16678), class = "Date")), .Names = c("date1", "date2"),
row.names = c(NA, -4L), class = "data.frame")
source to share
The difference will make you seconds, because you probably have objects representing non-dates in your data.
If your data will indeed ever deal with dates, that is, whole days, use the provided class Date
. Example:
data = data.frame(
date1=as.Date(c("2015-01-01","2015-01-02","2015-10-27")),
date2 = as.Date(c("2015-01-31","2015-01-31","2015-08-31")))
Then:
> data$diff = data$date2 - data$date1
> data
date1 date2 diff
1 2015-01-01 2015-01-31 30 days
2 2015-01-02 2015-01-31 29 days
3 2015-10-27 2015-08-31 -57 days
>
source to share