计算条件累积时间 [英] Calculating conditional cumulative time

问题描述

遵循这个问题。

我想计算 all 的累积时间Cat ，请考虑它们各自的最后切换状态。

I'd like to calculate the cumulative time for all the Cats, by considering their respective last toggle status.

编辑：
我还想检查 Cat 的第一个切换状态是否为关闭，如果是这样，对于该特定的猫，从午夜开始的时间 00:00:00 直到第一个首次关闭时间应加到其有条件的累积正常运行时间中。

I'd also want to check if the FIRST Toggle status of a Cat is Off and if it is so, for that specific cat, the time from midnight 00:00:00 till this first FIRST Off time should be added to its total conditional cumulative ontime.

样本数据：

       Time Cat Toggle
1  05:12:09  36 On
2  05:12:12 26R Off # First Toggle of this Cat happens to be Off, Condition met
3  05:12:15 26R On
4  05:12:16 26R Off
5  05:12:18  99 Off # Condition met
6  05:12:18  99 On
7  05:12:24  36 Off
8  05:12:26  36 On
9  05:12:29  80 Off # Condition met
10 05:12:30  99 Off
11 05:12:31  95 Off # Condition met
12 05:12:32  36 Off

所需的样本输出：

  Cat Time(Secs)
1 36  21
2 26R 18733 # (=1+18732), 18732 secs to be added = total Sec from midnight till 05:12:12
3 99  18750 # (=12+18738), 18738 secs to be added = total Sec from midnight till 05:12:18
4 ..  ..

任何帮助都是值得的。

推荐答案

使用 data.table ：

# load the 'data.table'-package, convert 'df' to a 'data.table'
# and 'Time'-column to a time-format
library(data.table)
setDT(df)[, Time := as.ITime(Time)]

# calculate the time-difference
df[, .(time.diff = sum((shift(Time, type = 'lead') - Time) * (Toggle == 'On'), na.rm = TRUE))
   , by = Cat]

它给出：

   Cat time.diff
1:  36        21
2: 26R         1
3:  99        12
4:  80         0
5:  95         0

---

您的评论中的问题，您可以执行以下操作：

---

In respons to your question in the comments, you could do:

# create a new data.table with midnigth times for the categories where
# the first 'Toggle' is on "Off"
df0 <- df[, .I[first(Toggle) == "Off"], by = Cat
          ][, .(Time = as.ITime("00:00:00"), Cat = unique(Cat), Toggle = "On")]

# bind that to the original data.table; order on 'Cat' and 'Time'
# and then do the same calculation
rbind(df, df0)[order(Cat, Time)
               ][, .(time.diff = sum((shift(Time, type = 'lead') - Time) * (Toggle == 'On'), na.rm = TRUE))
                                 , by = Cat]

它给出：

   Cat time.diff
1: 26R     18733
2:  36        21
3:  80     18749
4:  95     18751
5:  99     18750

---

基数R（仅原始问题）：

---

An alternative with base R (only original question):

df$Time <- as.POSIXct(df$Time, format = "%H:%M:%S")

stack(sapply(split(df, df$Cat),
             function(x) sum(diff(x[["Time"]]) * (head(x[["Toggle"]],-1) == 'On'))))

给出：

  values ind
1      1 26R
2     21  36
3      0  80
4      0  95
5     12  99

---

或带有 tidyverse （仅原始问题）：

library(dplyr)
library(lubridate)

df %>% 
  mutate(Time = lubridate::hms(Time)) %>% 
  group_by(Cat) %>% 
  summarise(time.diff = sum(diff(Time) * (head(Toggle, -1) == 'On'),
                            na.rm = TRUE))

这篇关于计算条件累积时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！