0

I have pulled some data via sql into a dataframe. I am now trying to subset such data and have had no luck.

I wish to loop through each row and identify the previous hour, after which I wish to select a subset of the DF where date == previous hour. (I understand there are other ways of doing this however i wish to understand why this isn't working). When I do this it returns an empty df. However If i directly paste the value of previous hour as a string I get the result I desire.

Both variables are POSIXCT and any attempt to convert to character fails. Can someone please tell me what on earth is going on? :S

My code:

for(row in 1:3){

  PreviousHour <- as.POSIXct(Data$mydate[row] - hours(1), tz = "UTC")
  Date <- Data$mydate[row]

  print(c(Data$mydate[row],PreviousHour))
  #"2019-11-20 23:00:00 GMT" "2019-11-20 22:00:00 GMT"

  print(Data$mydate[row] == PreviousHour)
  #FALSE

  print(subset(Data,Data$mydate == PreviousHour))
  # A tibble 0x5

  print(subset(Data,Data$mydate == "2019-11-20 22:00:00 GMT"))
  # A tibble 1x5

}

Code if I manually create the df (This works):

mydate <- c(as.POSIXct("2019-11-20 22:00:00", tz = "UTC"),as.POSIXct("2019-11-20 21:00:00", tz = "UTC"))
Data <- data.frame(mydate)


for(row in 1:1){

  PreviousHour <- as.POSIXct(Data$mydate[row] - hours(1), tz = "UTC")
  Date <- Data$mydate[row]
  print(c(Data$mydate[row],PreviousHour))
  #"2019-11-20 22:00:00 GMT" "2019-11-20 21:00:00 GMT"

  print(Data$mydate[row] == PreviousHour)
  #FALSE

  print(subset(Data,Data$mydate == PreviousHour))
  # A tibble 1x1

}
Tolki
  • 63
  • 1
  • 1
  • 9
  • 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. – MrFlick Feb 17 '20 at 16:54
  • Like I said I pull the data from an sql server, I could create a df however i dont know if it will have the same result – Tolki Feb 17 '20 at 16:56
  • 2
    Well, if the `df` you create doesn't have the same result, then that will tell us a lot. Then we would be interested to see how exactly you are pulling in the data. But chances are it will have the same problem hence why creating a reproducible example is so helpful. – MrFlick Feb 17 '20 at 16:58
  • I'm not replicating your results. You show that `print(Data$mydate[row] == PreviousHour)` is TRUE, but it isn't true as you show in the previous line (they are an hour apart). When I run this code, the line `print(Data$mydate[row] == PreviousHour)` prints FALSE, as it should do. – Allan Cameron Feb 17 '20 at 17:00
  • Sorry Allan you are right that was a typo on my part ill edit now. Adding the code with the manual creation of the df, and it works – Tolki Feb 17 '20 at 17:06

0 Answers0