Filter dataframe within datetime range

tidyverse
1

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.
image
So where did I go wrong?

I did some further analysis, and find it strange.

image
image1604×640 37.3 KB

test1=Test
filtered = test1 %>% dplyr::mutate(check1=( DateTime >=DateTime_start),check2=(DateTime <=DateTime_end),check=check1 & check2)

How can the highlighted be TRUE?

2

Advice in seeking help, and sharing your work. it would be better to post text, formatted with triple backticks (to look nicer) rather than an image. Please try to preserve posting images for the case of reporting Rstudio bugs, or to show plots, otherwise a text representation is generally much preferred.

3

image
image1120×209 99.3 KB

It is a mixture of timezone that gets the whole thing screwed up. It is solved now, thanks.

1 Like
4

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.