Comparing datetimes

General
,
1

0

I am trying to find similarities in timestamps between two data frames in order to match ID numbers to GPS coordinates. One data frame is a master sheet with all GPS coordinates of all id numbers along with a date - timestamp. The second is anytime one ID number contacts with another ID number, these events are also timestamped. There is an individual dataset like this for each id number.

In essence, I need to compare the timestamps from the two datasheets in order to match when contact between two ID numbers happens with the GPS coordinates to find where the ID numbers were at that time.

As the timestamps are in a yyyy/mm/dd/ hh/mm/ss format, the timestamps do not match exactly. Because of this, I believe it will be better to try and compare the date and time down to intervals of 10 minutes, i.e. converting the format to hh/m. However, I am struggling with how to code for this. If there is a way to directly compare the timestamps that would also be helpful.

Any help would be much appreciated! Thanks!

2

Is that 0, 10, 20, 30, 40, 50, 60 past the hour, treating all dates as the same date? That is what

would do. (Aside datetime objects have no format, as such, until they are converted into character strings.)

This is a different problem depending on whether the timestamps are datetime objects or character strings. See the FAQ: How to do a minimal reproducible example reprex for beginners.

For datetime objects

suppressPackageStartupMessages({
  library(lubridate)
})

a_datetime <- now()
a_datetime
#> [1] "2021-05-13 16:21:14 PDT"

round_date(a_datetime,unit = "10 minutes",week_start = getOption("lubridate.week.start", 7))
#> [1] "2021-05-13 16:20:00 PDT"
3

If this helps the data looks similar to this (edited for confidentiality)

This is the format of the first data set:
X name deviceNr timestamp latitude longitude
1 25682 F150 51 2018-07-28 04:56:24 X X

And this is the second:
X deviceNr deviceId timestamp peerId TIME peerId2
1 F150 X 2018-05-28 04:59:09 140 2018-05-28 04:51:09 F140

So I am trying to find the long/lat coordinates for where Device numbers F150 and F140 contacted each other by comparing the two datasheets.

4 
suppressPackageStartupMessages({
  library(dplyr)
})
dat1 <- structure(list(deviceNr = "F150", something = 51, timestamp = structure(1532753784,
  class =
    c("POSIXct", "POSIXt"), tzone = "UTC"
), latitude = "X", longitude = "X"),
class =
  c("spec_tbl_df", "tbl_df", "tbl", "data.frame"),
row.names =
  c(NA, -1L), spec = structure(list(cols = list(name = structure(list(),
  class =
    c("collector_character", "collector")
), deviceNr = structure(list(),
  class =
    c("collector_double", "collector")
), timestamp = structure(list(format = ""),
  class =
    c("collector_datetime", "collector")
), latitude = structure(list(),
  class =
    c("collector_character", "collector")
), longitude = structure(list(),
  class =
    c("collector_character", "collector")
)), default = structure(list(),
  class =
    c("collector_guess", "collector")
), skip = 1L),
class = "col_spec"
)
)

dat2 <- structure(list(deviceNr = "F150", deviceId = "X", timestamp = structure(1527483549,
  class =
    c("POSIXct", "POSIXt"), tzone = "UTC"
), peerId = 140, TIME = structure(1527483069,
  class =
    c("POSIXct", "POSIXt"), tzone = "UTC"
), peerId2 = "F140"),
class =
  c("spec_tbl_df", "tbl_df", "tbl", "data.frame"),
row.names =
  c(NA, -1L), spec = structure(list(cols = list(deviceNr = structure(list(),
  class =
    c("collector_character", "collector")
), deviceId = structure(list(),
  class =
    c("collector_character", "collector")
), timestamp = structure(list(format = ""),
  class =
    c("collector_datetime", "collector")
), peerId = structure(list(),
  class =
    c("collector_double", "collector")
), TIME = structure(list(format = ""),
  class =
    c("collector_datetime", "collector")
), peerId2 = structure(list(),
  class =
    c("collector_character", "collector")
)), default = structure(list(),
  class =
    c("collector_guess", "collector")
), skip = 1L),
class = "col_spec"
)
)

inner_join(dat1, dat2, by = "deviceNr")
#> # A tibble: 1 x 10
#>   deviceNr something timestamp.x         latitude longitude deviceId
#>   <chr>        <dbl> <dttm>              <chr>    <chr>     <chr>   
#> 1 F150            51 2018-07-28 04:56:24 X        X         X       
#> # … with 4 more variables: timestamp.y <dttm>, peerId <dbl>, TIME <dttm>,
#> #   peerId2 <chr>
5

That makes sense! Thank you!

1 Like