R:数据。表:使用随时间变化的引用进行聚合

使用 dplyr ,我们可以通过一个虚拟变量连接,来创建 time 和 active .定义 inactive 和 av.time 可能不是你想要的,但它应该让你开始。如果你的数据非常大,我同意 data.table 将是更好的处理方式。

library(tidyverse)

time %>% 
  mutate(dummy = TRUE) %>% 
  inner_join({
    active %>% 
      mutate(dummy = TRUE)
    #join by the dummy variable to get the Cartesian product
  }, by = c("dummy" = "dummy")) %>% 
  select(-dummy) %>% 
  #define what makes an id inactive and the time until it becomes active
  mutate(inactive = time < beg | time > end,
         TimeUntilActive = ifelse(beg > time, difftime(beg, time, units = "mins"), NA)) %>% 
  #group by time and summarise
  group_by(time) %>% 
  summarise(inactive = sum(inactive),
            av.time = mean(TimeUntilActive, na.rm = TRUE))

# A tibble: 51 x 3
        time            inactive av.time
        <dttm>            <int>   <dbl>
1 2018-01-01 01:10:00        3      40
2 2018-01-01 01:11:00        3      39
3 2018-01-01 01:12:00        3      38
4 2018-01-01 01:13:00        3      37
5 2018-01-01 01:14:00        3      36
6 2018-01-01 01:15:00        3      35
7 2018-01-01 01:16:00        3      34
8 2018-01-01 01:17:00        3      33
9 2018-01-01 01:18:00        3      32
10 2018-01-01 01:19:00        3      31