Joining datasets in range of time.

user124578 · January 21, 2019, 11:42pm

I have two datasets that I would like to join based on the time range. for instance Event name total render need to be joined based in the time range in df2. Is this possible?

tibble::tribble(
                  ~timestamp,                                ~hostname,      ~eventName, ~eventType,                                            ~jobId,                                ~taskId,
  "2018-11-08T07:41:45.459Z", "04dc4e9647154250beeee51b866b0715000000",   "TotalRender",    "START", "1024-lvl12-7e026be3-5fd0-48ee-b7d1-abd61f747705", "00390eee-c26c-41da-a02d-556bb7fcac67",
  "2018-11-08T07:41:45.459Z", "04dc4e9647154250beeee51b866b0715000000", "Saving Config",    "START", "1024-lvl12-7e026be3-5fd0-48ee-b7d1-abd61f747705", "00390eee-c26c-41da-a02d-556bb7fcac67",
  "2018-11-08T07:41:32.461Z", "04dc4e9647154250beeee51b866b0715000000",        "Render",    "START", "1024-lvl12-7e026be3-5fd0-48ee-b7d1-abd61f747705", "00390eee-c26c-41da-a02d-556bb7fcac67",
  "2018-11-08T07:41:32.461Z", "04dc4e9647154250beeee51b866b0715000000", "Saving Config",     "STOP", "1024-lvl12-7e026be3-5fd0-48ee-b7d1-abd61f747705", "00390eee-c26c-41da-a02d-556bb7fcac67",
  "2018-11-08T07:42:09.344Z", "04dc4e9647154250beeee51b866b0715000000",        "Render",     "STOP", "1024-lvl12-7e026be3-5fd0-48ee-b7d1-abd61f747705", "00390eee-c26c-41da-a02d-556bb7fcac67"
  )

DF2

tibble::tribble(
                  ~timestamp,                                ~hostname,   ~gpuSerial,                                   ~gpuUUID, ~powerDrawWatt, ~gpuTempC, ~gpuUtilPerc, ~gpuMemUtilPerc,
  "2018-11-08T07:41:44.242Z", "04dc4e9647154250beeee51b866b0715000000", 323217056165, "GPU-a1119ee9-9cd1-919f-a479-b902142c717d",          25.94,       32L,           0L,              0L,
  "2018-11-08T07:41:47.259Z", "04dc4e9647154250beeee51b866b0715000000", 323217056165, "GPU-a1119ee9-9cd1-919f-a479-b902142c717d",          25.84,       32L,           0L,              0L,
  "2018-11-08T07:41:31.285Z", "04dc4e9647154250beeee51b866b0715000000", 323217056165, "GPU-a1119ee9-9cd1-919f-a479-b902142c717d",          25.84,       32L,           0L,              0L,
  "2018-11-08T07:41:33.301Z", "04dc4e9647154250beeee51b866b0715000000", 323217056165, "GPU-a1119ee9-9cd1-919f-a479-b902142c717d",          25.94,       32L,           0L,              0L,
  "2018-11-08T07:41:35.322Z", "04dc4e9647154250beeee51b866b0715000000", 323217056165, "GPU-a1119ee9-9cd1-919f-a479-b902142c717d",          25.84,       32L,           0L,              0L
  )

andresrcs · January 22, 2019, 1:14am

It's not clear with that sample data, because you have not calculated the time range between start and stop off any given event, but apparently they overlap each other, so this will depend on what conclusion are you trying to get from these analysis.

mfherman · January 22, 2019, 1:48am

As @andresrcs says, it's not entirely clear what your criteria for joining are, but you can accomplish this type of join using the fuzzyjoin package. Take a look at this question and answer for an example of a join based on time intervals:

Tidy way to range join tables, on an interval of dates tidyverse

You can do it with the fuzzyjoin package, which implements various not quite exact matching joins in dplyr syntax. library(tidyverse) library(fuzzyjoin) df1 <- tibble::tribble( ~id, ~category, ~date, 1L, "a", "7/1/2000", 2L, "b", "11/1/2000", 3L, "c", "7/1/2002" ) %>% mutate(date = as.Date(date, format = "%m/%d/%Y")) df2 <- tibble::tribble( ~category, ~other_info, ~start, ~end, "a", "x", "1/1/2000", "12/31/2000", "b", "y", "1/1/2001", "12/31/2001", "c", "z", "1/1/2002", "12/31/2002" ) %>% mutate_at(vars(start, end), as.Date, format = "%m/%d/%Y") fuzzy_left_join( df1, df2, by = c( "category" = "c…

JohnMount · January 22, 2019, 2:41am

data.table has a very nice function for this: foverlaps https://rdrr.io/cran/data.table/man/foverlaps.html

user124578 · January 22, 2019, 9:30am

@mfherman I have tried the fussy join but didn't get the results I am after. It gave the same out put as the left_join.

user124578 · January 22, 2019, 9:38am

I initially tried to calculate the time difference between each event based on the stop and start value and then joined based on the hostname and timestamp, but the data dimensions was two small and my analysis wasn't accurate. In df1 the measurement is recorded every 5 seconds where in df2ly it is every 2 second.

user124578 · January 22, 2019, 10:22am

Thanks for pointing this out. I'n struggling to apply this into my data. Please can you indicate whta i need to do?

foverlaps(x, y, by.x = if (!is.null(key(x))) key(x) else key(y),
    by.y = key(y), maxgap = 0L, minoverlap = 1L,
    type = c("any", "within", "start", "end", "equal"),
    mult = c("all", "first", "last"),
    nomatch = getOption("datatable.nomatch"),
    which = FALSE, verbose = getOption("datatable.verbose"))

user124578 · January 22, 2019, 7:05pm

When trying the fuzzy join i am getting a messge regarding memory: Error: cannot allocate vector of size 3.7 Gb
Is there anyway round this please?

taras · January 22, 2019, 8:04pm

I feel like you could use some of lubridate's interval functions (see https://lubridate.tidyverse.org/reference/lubridate-package.html#intervals for more), but without a clear deliverable, I'm not sure what would you need exactly.

Another way to do this is to perform a cartesian join / cross join (e.g. merge(df1, df2, all=TRUE), and then pair it up with filter call, where your conditions are <, == or > on the respective timestamps.

user124578 · January 22, 2019, 11:28pm

This is my lastest code. It states that: Error in is.data.table(y) : object 'y' not found

Any idea what I am missing?

require(data.table)
simple example:
  x = data.table(df2,start=c(timestamp),end=c(taskId))
y = data.table(df1,start=c(timestamp),end=c(gpuMemUtilPerc))
setkey(y,start,end)
foverlaps(x, y, by.x = c("timestamp","hostname"), type="within", nomatch = 0L)

EconomiCurtis · January 23, 2019, 3:50am

This discussion seems to have moved here Joining datasets using foverlaps