在时间段内按时间戳加入

Question

我有两个桌子，我想在日期加入。如果一个实例属于颜色期，我希望该实例将上述颜色作为变量接收（请参阅加入）：

instance <- read.table(header=TRUE, text="
ID      UTC_DateTime            Value
1       2022-06-15 15:00:00     8
2       2022-06-15 15:05:00     3
3       2022-06-15 15:10:00     2
4       2022-06-15 15:15:00     4
")

periods <- read.table(header=TRUE, text="
COLOR           UTC_DateTime          
Start_Green     2022-06-15 14:56:22
End_Green       2022-06-15 15:03:08
Start_Red       2022-06-15 15:03:11
End_Red         2022-06-15 15:58:48
")

joined <- read.table(header=TRUE, text="
ID      UTC_DateTime            Value     Color
1       2022-06-15 15:00:00     8         Green
2       2022-06-15 15:05:00     3         Green
3       2022-06-15 15:10:00     2         Red
4       2022-06-15 15:15:00     4         Red
")

另外，如果您可以以某种方式合并一个将ercepers to to stripss_wide的过程。很棒，请参阅：

periods_wide <- read.table(header=TRUE, text="
COLOR           UTC_DateTime_Start      UTC_DateTime_End
Green           2022-06-15 14:56:22     2022-06-15 15:03:08
Red             2022-06-15 15:03:11     2022-06-15 15:58:48
")

请注意，可能还有更多实例，还有更多的时期（相同或其他颜色）

Answer 1

基于powerjoin :: Power_inner_join和lubridate的可能解决方案。请注意，read.table没有按预期读取数据，因此，我使用read.csv：

library(tidyverse)
library(powerjoin)
library(lubridate)

instance <- read.csv(header=TRUE, text="
ID,      UTC_DateTime,            Value
1,       2022-06-15 15:00:00,     8
2,       2022-06-15 15:05:00,     3
3,       2022-06-15 15:10:00,     2
4,       2022-06-15 15:15:00,     4
", strip.white = T)

periods <- read.csv(header=TRUE, text="
COLOR,           UTC_DateTime          
Start_Green,     2022-06-15 14:56:22
End_Green,       2022-06-15 15:03:08
Start_Red,       2022-06-15 15:03:11
End_Red,         2022-06-15 15:58:48
", strip.white = T)

periods %>% 
  mutate(COLOR = str_remove(COLOR, "Start_|End_"), 
         name = rep(c("d1", "d2"), 2)) %>% 
  pivot_wider(COLOR, values_from = UTC_DateTime) %>% 
  power_inner_join(instance,., 
                   by = c(~ ymd_hms(.x$UTC_DateTime) <= ymd_hms(.y$d2),  
                          ~ ymd_hms(.x$UTC_DateTime) >= ymd_hms(.y$d1))) %>% 
  select(-(d1:d2))

#>   ID        UTC_DateTime Value COLOR
#> 1  1 2022-06-15 15:00:00     8 Green
#> 2  2 2022-06-15 15:05:00     3   Red
#> 3  3 2022-06-15 15:10:00     2   Red
#> 4  4 2022-06-15 15:15:00     4   Red