CODEWITHSUNDEEP
rdatetimepattern-matchingdata.tabledifference
DianaLog
DianaLog

Reputation: 336

Time differences between rows with given sequential pattern

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:

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?

Upvotes: 0

Views: 44

Answers (1)

Matt Jewett
Matt Jewett

Reputation: 3369

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))]

Upvotes: 1

Related Questions