Disparate Logical returns working with datetimes created with as.POSIXct()

Question

Rephrasing original question to add more clarity and reproducible code:

The code below is what I am using to label various time segments of my hydrograph. When I run it no errors are returned.

library(tidyverse)
library(magrittr)
library(lubridate)
library(zoo)
library(trend)

## Original Data

test_df = data.frame(test_date=c("8/1/2023","8/1/2023","8/1/2023", "7/31/2023","7/31/2023","8/1/2023"),
                      T1=c("16:18:00","17:41:00","18:58:00","23:09:00","23:47:00","0:29:00"),
                      T2=c("17:34:00","18:52:00","19:56:00","23:44:00","0:28:00","1:21:00"),
                      mid_time=c("16:56:00","18:16:30","19:27:00","23:26:30","0:07:30","0:55:00"))

# data processing
test_df = test_df %>%
  mutate(DateTime1=as.POSIXct(with(test_df, as.Date(test_date,"%m/%d/%y%y")+hms(T1)))) %>%
  mutate(DateTime2=as.POSIXct(with(test_df, as.Date(test_date,"%m/%d/%y%y")+hms(T2)))) %>%
  mutate(midtime_date=as.POSIXct(with(test_df, as.Date(test_date,"%m/%d/%y%y")+hms(mid_time)))) 

# original attempt
test_df = test_df %>%

  mutate(Tide=ifelse(midtime_date>="2023-07-31 18:14:00 UTC" & midtime_date<"2023-07-31 23:22:00 UTC","EBB",
                     ifelse(midtime_date>="2023-07-31 23:22:00 UTC" & midtime_date<="2023-07-31 23:59:00 UTC","Flood",
                     ifelse(midtime_date>="2023-08-01 00:00:00 UTC" & midtime_date<"2023-08-01 04:08:00 UTC","Flood",
                     ifelse(midtime_date>="2023-08-01 04:08:00 UTC" & midtime_date<"2023-08-01 11:30:00 UTC","Ebb",
                     ifelse(midtime_date>="2023-08-01 11:30:00 UTC" & midtime_date<"2023-08-01 18:52:00 UTC","Flood",
                     ifelse(midtime_date>="2023-08-01 18:52:00 UTC" & midtime_date<"2023-08-02 00:00:00 UTC","Ebb",
                                                        "NA" )))))))
#logical: these two statements should be equal but aren't
test_df$midtime_date[6]>"2023-08-01 00:00:00 UTC"
"2023-08-01 00:55:00 UTC">"2023-08-01 00:00:00 UTC"

# What I want
test_df = test_df %>%

  mutate(Tide=ifelse(test_date=="2023-07-31",
                     ifelse(hms(T1)>= hms("18:14:00") & hms(T1)<hms("23:22:00"), "Ebb", "Flood" ),
                     
                     ifelse(hms(T1)>= hms("00:00:00") & hms(T1)<hms("04:08:00"), "Flood",
                     ifelse(hms(T1)>= hms("04:08:00") & hms(T1)<hms("11:30:00"), "Ebb",
                     ifelse(hms(T1)>= hms("11:30:00") & hms(T1)<hms("18:52:00"), "Flood",
                     ifelse(hms(T1)>= hms("18:52:00") & hms(T1)<hms("23:59:00"), "Ebb", "NA"))))))

I first thought it was an interaction around the change of date and time so I split ifelse() statements at midnight to not have the ifelse() span 2 dates (first 2 lines of code). Then I re-ran the code to recreate the dataframe. However that didn't seem to work.

Next, I realized that in the original data for midnight hour there was only one zero in the hours place. I added a second zero to the hours place but it still produced the same result:

## Add leading zero, still get same result
test_df2 = data.frame(test_date=c("8/1/2023","8/1/2023","8/1/2023", "7/31/2023","7/31/2023","8/1/2023"),
                     T1=c("16:18:00","17:41:00","18:58:00","23:09:00","23:47:00","00:29:00"),
                     T2=c("17:34:00","18:52:00","19:56:00","23:44:00","00:28:00","01:21:00"),
                     mid_time=c("16:56:00","18:16:30","19:27:00","23:26:30","00:07:30","00:55:00"))

test_df2 = test_df2 %>%
  mutate(DateTime1=as.POSIXct(with(test_df2, as.Date(test_date,"%m/%d/%y%y")+hms(T1)))) %>%
  mutate(DateTime2=as.POSIXct(with(test_df2, as.Date(test_date,"%m/%d/%y%y")+hms(T2)))) %>%
  mutate(midtime_date=as.POSIXct(with(test_df2, as.Date(test_date,"%m/%d/%y%y")+hms(mid_time)))) 

test_df2 = test_df2 %>%
  
  mutate(Tide=ifelse(midtime_date>="2023-07-31 18:14:00 UTC" & midtime_date<"2023-07-31 23:22:00 UTC","EBB",
                     ifelse(midtime_date>="2023-07-31 23:22:00 UTC" & midtime_date<="2023-07-31 23:59:00 UTC","Flood",
                     ifelse(midtime_date>="2023-08-01 00:00:00 UTC" & midtime_date<"2023-08-01 04:08:00 UTC","Flood",
                     ifelse(midtime_date>="2023-08-01 04:08:00 UTC" & midtime_date<"2023-08-01 11:30:00 UTC","Ebb",
                     ifelse(midtime_date>="2023-08-01 11:30:00 UTC" & midtime_date<"2023-08-01 18:52:00 UTC","Flood",
                     ifelse(midtime_date>="2023-08-01 18:52:00 UTC" & midtime_date<"2023-08-02 00:00:00 UTC","Ebb",
                                                        "NA" )))))))
# What I want
test_df2 = test_df2 %>%
  
  mutate(Tide=ifelse(test_date=="2023-07-31",
                     ifelse(hms(T1)>= hms("18:14:00") & hms(T1)<hms("23:22:00"), "Ebb", "Flood" ), 
                     
                     ifelse(hms(T1)>= hms("00:00:00") & hms(T1)<hms("04:08:00"), "Flood",
                     ifelse(hms(T1)>= hms("04:08:00") & hms(T1)<hms("11:30:00"), "Ebb",
                     ifelse(hms(T1)>= hms("11:30:00") & hms(T1)<hms("18:52:00"), "Flood",
                     ifelse(hms(T1)>= hms("18:52:00") & hms(T1)<hms("23:59:00"), "Ebb", "NA"))))))

Now I'm not sure what to try next given that it worked for all values except these 3. As you can see I figured out a viable work around that will be fine but I would really like to understand what is going on with the dates and time in the logical statements and why it worked in some cases and not others.

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Please [do not post code or data in images](https://meta.stackoverflow.com/q/285551/2372064) What time zone are you in? — MrFlick, Aug 30 '23 at 19:47
@MrFlick - I updated the post to provide additional clarity and to add data and code that reproduces the problem and solution. — HydroSci, Aug 30 '23 at 23:35

score 0 · Accepted Answer · answered Aug 31 '23 at 05:38

0

It's quite hard to make heads or tails of your code. While I wouln't say it's spaghetti code, your question could do with a description, to tell us what you are trying to do, or, if nothing else, at least a desired output. I'm guessing the last test_df2 is the desired output, but I'm still unsure if that is actually the case.

ebb_flood <- function(v) {
  case_when(
    v <= hms("04:08:00") ~ "Flood",
    v <= hms("11:30:00") ~ "Ebb",
    v <= hms("18:52:00") ~ "Flood",
    v <= hms("23:59:00") ~ "Ebb",
    TRUE ~ NA_character_
  )
}

test_df |> mutate(Tide = hms(T1) |> ebb_flood())

answered Aug 31 '23 at 05:38

Mark

7,785
2
14
34

I'm definitely not the most elegant coder. I pretty much only have a hammer in my coding toolbox. Still fairly new to creating functions so this response helped me learn a lot and eliminates a lot of the code I used to accomplish the same thing. I wasn't aware of case_when() and that is a game changer. Since I found an inelegant solution to produce what I wanted my real question, which got lost in all the text soup, was : #logical: these two statements should be equal but aren't test_df$midtime_date[6]>"2023-08-01 00:00:00 UTC" "2023-08-01 00:55:00 UTC">"2023-08-01 00:00:00 UTC" – HydroSci Aug 31 '23 at 19:47

Disparate Logical returns working with datetimes created with as.POSIXct()

1 Answers1