There is a behavior that I do not understand when subsetting a dataframe with dates. Here is the example (data at the end):
> df
positif1 positif2 date1 date2
1 0 0 2020-05-02 2020-04-30
2 0 0 2020-05-02 2020-04-21
3 0 0 2020-05-02 2020-04-30
.
.
I naively did want to subset this:
df[df$positif2 == 0 & df$positif1 == 0 & as.numeric(df$date1 - df$date2) <=2,]
and it does not return anything, although there are clearly values meeting the condition:
as.numeric(df[df$positif2 == 0 & df$positif1 == 0,"date1"]-df[df$positif2 == 0 & df$positif1 == 0,"date2"])
[1] 2 11 2 29 12 18 1 22 5 24 5 6 4 25 9 9 13 16 17 35 5 35 22 51 3 17 8 16 12 15 14 21 14
[34] 4
I realized that the proper way is to do:
df[df$positif2 == 0 & df$positif1 == 0 & difftime(df$date1, df$date2,units = "day") <=2,]
and that my ptroblem was that the unit changes when subsetting the data frame or not:
> df$date1 - df$date2
Time differences in secs
[1] 172800 950400 172800 2505600 1036800 1555200 2073600 86400 1900800 432000 2073600 432000
[13] 518400 345600 2160000 777600 777600 0 1123200 1382400 1468800 3024000 0 432000
[25] 3024000 1900800 4406400 0 259200 1468800 691200 1382400 1036800 1296000 1209600 1814400
[37] 1209600 345600
> df[df$positif2 == 0,"date1"] - df[df$positif2 == 0,"date2"]
Time differences in days
[1] 2 11 2 29 12 18 24 1 22 5 24 5 6 4 25 9 9 13 16 17 35 5 35 22 51 3 17 8 16 12 15 14 21
[34] 14 4
It makes no sense to me. Is there something I do wrong ? Is there a reason for such behavior ?
data:
df <- structure(list(positif1 = c(0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0), positif2 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L,
0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), date1 = structure(c(1588377600,
1588377600, 1588377600, 1588377600, 1588377600, 1588377600, 1588377600,
1588377600, 1588377600, 1588377600, 1588377600, 1588377600, 1588377600,
1588377600, 1588377600, 1588377600, 1588377600, 1584057600, 1588377600,
1588377600, 1588377600, 1588377600, 1586563200, 1588377600, 1588377600,
1588377600, 1588377600, 1586908800, 1588377600, 1588377600, 1588377600,
1588377600, 1588377600, 1588377600, 1588377600, 1588377600, 1588377600,
1588377600), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
date2 = structure(c(1588204800, 1587427200, 1588204800, 1585872000,
1587340800, 1586822400, 1586304000, 1588291200, 1586476800,
1587945600, 1586304000, 1587945600, 1587859200, 1588032000,
1586217600, 1587600000, 1587600000, 1584057600, 1587254400,
1586995200, 1586908800, 1585353600, 1586563200, 1587945600,
1585353600, 1586476800, 1583971200, 1586908800, 1588118400,
1586908800, 1587686400, 1586995200, 1587340800, 1587081600,
1587168000, 1586563200, 1587168000, 1588032000), class = c("POSIXct",
"POSIXt"), tzone = "UTC")), row.names = c(NA, -38L), class = "data.frame", index = structure(integer(0), "`__positif1__positif2`" = c(1L,
2L, 3L, 4L, 5L, 6L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L,
17L, 19L, 20L, 21L, 22L, 24L, 25L, 26L, 27L, 29L, 30L, 31L, 32L,
33L, 34L, 35L, 36L, 37L, 38L, 7L, 18L, 23L, 28L)))
From ?difftime
Subtraction of date-time objects gives an object of this class, by calling difftime with units = "auto".
So we know by default units = "auto" when subtracting dates.
Second,
If units = "auto", a suitable set of units is chosen, the largest possible (excluding "weeks") in which all the absolute differences are greater than one.
So when units = "auto" it tries to select a unit which is the largest. So when we do
df$date1 - df$date2
There are certain entries which are same in date1 and date2 making their difference as 0 so here "seconds" is chosen as the unit.
But when you subset the dates for only 0 entries in positif2 (positif2 == 0), the minimum difference is in dates, hence the unit selected is "days" here.
df$date1[df$positif2 == 0] - df$date2[df$positif2 == 0]
The correct way as you have already identified is to use difftime and explicitly specify units argument in it.