Search code examples
rtimedifference

Calculate count of zeros R for specific case

I have data that looks like below

enter image description here

I am trying to calculate when Unit 1 went 0 and what time it became greater than 0. Suppose Unit 1 first drops to zero at 01/04/2019 02:00 and it is zero-till 01/04/2019 03:00 so that should be counted as 1 and then the second time it goes zero at 01/04/2019 04:30 and its zero-till 01/04/2019 05:00 which will be counted as 2 and same calculation for the other units.

Additionally, Iam looking to capture the time difference like the first time unit 1 went 0 for 2 hours and then second time unit went 0 for 1 hour something like this

I am thinking if that can be done using if statement that counts until the value is greater than zero and then a loop gets updated. I am struggling with how to incorporate time with that.

The final result should be

Unit  |            Went Offline|       Came online
Unit 1|     01/04/2019 02:00   |        01/04/2019 03:00
Unit 1|     01/04/2019 04:30   |        01/04/2019 05:00
Solution

  • I prefer some sudo code to start with. But here is an example solution to begin with. 

    # create data frame
date = format(seq(as.POSIXct("2019-04-01 00:00:00", tz="GMT"), 
           length.out=15, by='30 min'), '%Y-%m-%d %H:%M:%S')
unit1 = c(513, 612, 653, 0, 0, 0, 530, 630, 0, 0, 650, 512, 530 , 650, 420)

data = data.frame(date, unit1)

# subset all data that is Zero
data1 = data[data$unit1 != 0,]

# Create lead for from and to
data1$dateTo = lead(data1$date, 1, na.pad = TRUE)

#calculate time diff
data1$timediff = as.numeric(difftime(data1$dateTo,data1$date,units = "mins"))

# subset data that has a time diff more than 30 mins
data2 = subset.data.frame(data1, timediff > 30)