Time differences between rows with given sequential pattern

Question

I have a data table describing the times of ON and OFF states for two different devices. ON is represented by -1 and 1, OFF is represented by 0.

myData <- data.frame(
   date = as.POSIXct(c(
      '2017-06-12 19:35:51','2017-06-12 19:36:49','2017-06-12 19:38:41','2017-06-12 19:39:50','2017-06-12 19:39:18','2017-06-12 19:39:35',
      '2017-06-12 19:41:53','2017-06-12 19:42:56','2017-06-12 19:42:01','2017-06-12 19:42:41','2017-06-12 19:44:56','2017-06-12 19:45:09')),
   device1 = c(1,NA,0,1,NA,NA,0,1,NA,NA,0,1),
   device2 = c(NA,-1,NA,NA,0,-1,NA,NA,0,-1,NA,NA)
)

> myData
                  date device1 device2
1  2017-06-12 19:35:51       1      NA
2  2017-06-12 19:36:49      NA      -1
3  2017-06-12 19:38:41       0      NA
4  2017-06-12 19:39:50       1      NA
5  2017-06-12 19:39:18      NA       0
6  2017-06-12 19:39:35      NA      -1
7  2017-06-12 19:41:53       0      NA
8  2017-06-12 19:42:56       1      NA
9  2017-06-12 19:42:01      NA       0
10 2017-06-12 19:42:41      NA      -1
11 2017-06-12 19:44:56       0      NA
12 2017-06-12 19:45:09       1      NA

For each device ON/OFF states, I want to calculate the time differences when the states alternate as:

-1 or 1 followed by 0 (ON to OFF)
0 followed by -1 or 1 (OFF to ON)

So far, I only found a way to get differences between subsequent occurrence of the same state, e.g.

device1_OFF_to_OFF_diff <- diff.difftime(myData$date[(is.na(myData$device1) == FALSE) & myData$device1 == '0' ])
device1_ON_to_ON_diff <- diff.difftime(myData$date[(is.na(myData$device1) == FALSE) & myData$device1 == '1' ])

> device1_OFF_to_OFF_diff
Time differences in 
[1] 3.20 3.05

> device1_ON_to_ON_diff
Time differences in 
[1] 3.983333 3.100000 2.216667

However the goal is to get differences when specific pattern exists, giving tables like device1_ON_to_OFF_diff and device1_OFF_to_ON_diff (hope you get the idea). Are there any convenient ways to achieve this?

more or less `myData[!is.na(device1), .(date[.N] - date[1]), cumsum(device1 == 0)]` — eddi, Jun 26 '17 at 21:37
Assuming you meant `setDT(myData)`, awesome method, the only problem is mixed units of time, e.g.: `> setDT(myData)[!is.na(device1), .(date[.N] - date[1]), cumsum(device1 == 0)] cumsum V1 1: 0 0.00 secs 2: 1 1.15 secs 3: 2 1.05 secs 4: 3 13.00 secs` The last row is indeed in seconds, but the middle two are in minutes. — DianaLog, Jun 27 '17 at 07:55
OK, just got it: setDT(myData)[!is.na(device1), .( difftime( date[.N], date[1], units = "sec") ), cumsum(device1 == 0)], thanks! — DianaLog, Jun 27 '17 at 08:09

Matt Jewett · Accepted Answer · 2017-06-26T18:15:56.867

Here is a for loop that may work for you

myData <- data.frame(
  date = as.POSIXct(c(
    '2017-06-12 19:35:51','2017-06-12 19:36:49','2017-06-12 19:38:41','2017-06-12 19:39:50','2017-06-12 19:39:18','2017-06-12 19:39:35',
    '2017-06-12 19:41:53','2017-06-12 19:42:56','2017-06-12 19:42:01','2017-06-12 19:42:41','2017-06-12 19:44:56','2017-06-12 19:45:09')),
  device1 = c(1,NA,0,1,NA,NA,0,1,NA,NA,0,1),
  device2 = c(NA,-1,NA,NA,0,-1,NA,NA,0,-1,NA,NA)
)

devices <- colnames(myData)[substr(colnames(myData),1,6) == "device"]

for(d in devices){
  last.on <- NA
  last.off <- NA

  for(i in 1:nrow(myData)){
    cur.val <- myData[i,d]
    cur.ts <- myData[i,"date"]

    if(!is.na(cur.val) & cur.val %in% c(1,-1)){
      last.on <- cur.ts
      if(is.na(last.off)){
        myData[i,paste0(d,"_OFF_to_ON")] <- 0
      } else {
        myData[i,paste0(d,"_OFF_to_ON")] <- round(difftime(cur.ts, last.off, units = "mins"),2)
      }
    } else if(!is.na(cur.val) & cur.val == 0){
      last.off <- cur.ts
      if(is.na(last.on)){
        myData[i,paste0(d,"_ON_to_OFF")] <- 0
      } else{
        myData[i,paste0(d,"_ON_to_OFF")] <- round(difftime(cur.ts, last.on, units = "mins"),2)
      }
    } else {
      myData[i,paste0(d,"_OFF_to_ON")] <- NA
      myData[i,paste0(d,"_ON_to_OFF")] <- NA
    }
  }
}


# Change column order to keep device information together
myData <- myData[,sort(colnames(myData))]

that's also great, although longer than @eddi 's data.table solution, +1 for preserving the table format — DianaLog, Jun 27 '17 at 09:07

Time differences between rows with given sequential pattern

1 Answers1