r - Filter timestamp rows in one table based on start time & End time given in another table

Question

I have one table that contains downtime data which looks like this

| Machine No | Start Time       | End Time         |
|------------|------------------|------------------|
| H18        | 01-01-2021 12:05 | 01-01-2021 12:15 |
| H19        | 02-01-2021 11:15 | 02-01-2021 13:15 |
| H20        | 01-01-2021 11:15 | 01-01-2021 13:15 |
| H21        | 02-01-2021 09:15 | 02-01-2021 13:55 |
| H22        | 02-01-2021 10:25 | 02-01-2021 10:35 |

And I have a value stream data which looks like this, which is basically appended for all machines together

| Machine No | timestamp        | Value |
|------------|------------------|-------|
| H18        | 01-01-2021 12:00 | 34    |
| H18        | 01-01-2021 12:01 | 74    |
| H18        | 01-01-2021 12:02 | 43    |
| H18        | 01-01-2021 12:03 | 60    |
| H18        | 01-01-2021 12:04 | 68    |
| H18        | 01-01-2021 12:05 | 17    |
| H18        | 01-01-2021 12:06 | 38    |
| H18        | 01-01-2021 12:07 | 91    |
| H18        | 01-01-2021 12:08 | 65    |
| H18        | 01-01-2021 12:09 | 80    |
| H18        | 01-01-2021 12:10 | 67    |
| H18        | 01-01-2021 12:11 | 78    |
| H18        | 01-01-2021 12:12 | 43    |
| H18        | 01-01-2021 12:13 | 53    |
| H18        | 01-01-2021 12:14 | 92    |
| H18        | 01-01-2021 12:15 | 11    |
| H18        | 01-01-2021 12:16 | 75    |
| H18        | 01-01-2021 12:17 | 61    |
| H18        | 01-01-2021 12:18 | 82    |
| H18        | 01-01-2021 12:19 | 50    |
| H18        | 01-01-2021 12:20 | 65    |
| H18        | 01-01-2021 12:21 | 23    |
| H18        | 01-01-2021 12:22 | 80    |
| H18        | 01-01-2021 12:23 | 55    |
| H18        | 01-01-2021 12:24 | 61    |
| H18        | 01-01-2021 12:25 | 11    |
| H18        | 01-01-2021 12:26 | 98    |

I want to remove the rows containing data from the value stream table that is in between the start time and end time mentioned in the downtime data table. How do I achieve this in R?

question from:https://stackoverflow.com/questions/65661925/filter-timestamp-rows-in-one-table-based-on-start-time-end-time-given-in-anoth

Answer 1

You can join df1 and df2 by Machine.No, convert the columns to POSIXct format and keep only rows which are outside of Start.Time and End.Time.

library(dplyr)

df1 %>%
  inner_join(df2, by = 'Machine.No') %>%
  mutate(across(c(Start.Time,  End.Time,timestamp), lubridate::dmy_hm)) %>%
  filter(!(timestamp >= Start.Time & timestamp <= End.Time))

Or in base R :

res <- merge(df1, df2, by = 'Machine.No')
res[2:4] <- lapply(res[2:4], as.POSIXct, format = '%d-%m-%Y %H:%M', tz = 'UTC')
subset(res, !(timestamp >= Start.Time & timestamp <= End.Time))