I have DF as below:
> str(Test)
'data.frame': 15206574 obs. of 3 variables:
$ DateTime : POSIXct, format: "2020-06-09 08:20:00" "2020-06-09 08:20:00" "2020-06-09 08:20:00" "2020-06-09 08:20:00" ...
$ DateTime_start: POSIXct, format: "2020-06-09 08:19:35" "2020-06-09 11:01:57" "2020-06-09 16:56:52" "2020-06-09 21:52:07" ...
$ DateTime_end : POSIXct, format: "2020-06-09 09:37:01" "2020-06-09 14:55:20" "2020-06-09 21:43:11" "2020-06-10 06:32:09" ...
> head(Test)
DateTime DateTime_start DateTime_end
1 2020-06-09 08:20:00 2020-06-09 08:19:35 2020-06-09 09:37:01
2 2020-06-09 08:20:00 2020-06-09 11:01:57 2020-06-09 14:55:20
3 2020-06-09 08:20:00 2020-06-09 16:56:52 2020-06-09 21:43:11
4 2020-06-09 08:20:00 2020-06-09 21:52:07 2020-06-10 06:32:09
5 2020-06-09 08:20:00 2020-06-10 09:20:06 2020-06-10 10:12:53
6 2020-06-09 08:20:00 2020-06-10 11:31:41 2020-06-10 17:10:36
> tail(Test)
DateTime DateTime_start DateTime_end
15206569 2020-07-30 09:03:00 2020-07-28 10:26:00 2020-07-28 11:07:50
15206570 2020-07-30 09:03:00 2020-07-28 22:25:30 2020-07-29 03:41:35
15206571 2020-07-30 09:03:00 2020-07-29 11:26:15 2020-07-29 16:41:55
15206572 2020-07-30 09:03:00 2020-07-29 17:58:50 2020-07-29 23:16:45
15206573 2020-07-30 09:03:00 2020-07-29 23:28:45 2020-07-30 03:03:30
15206574 2020-07-30 09:03:00 2020-07-30 04:33:10 2020-07-30 08:33:50
My objective is to filter the DF such that DateTime is within the range of DateTime_start AND DateTime_end.
I have thought with this code, it should meet its objective.
TestFiltered <- Test %>% dplyr::filter(DateTime >= DateTime_start & DateTime <= DateTime_end)
But its output is wrong.
So where did I go wrong?
I did some further analysis, and find it strange.
test1=Test
filtered = test1 %>% dplyr::mutate(check1=( DateTime >=DateTime_start),check2=(DateTime <=DateTime_end),check=check1 & check2)
How can the highlighted be TRUE?