Background
I have real data from a PLC, that I prepare for conversion to an event-log for use with the package bupaR.
The data below are limited and simplified but contain information concerning resource, timestamp, state-type, and event_ID.
I achieved the desired transformation documented below with loops. My question is, can this be done without loops, in a "vectorised" way?
Goal:
From this, I want to
- detect when an error occurs and I want to track it till it ends. It ends when the `State_type`` is anything but "Error", "Comlink Down", "Not Active".
- assign an error number to all rows of the same "error-trace" (assign to "Error_ID")
- have the start time of the error (timestamp of 1st error row) (assign to "Error_startTS")
- have the end time of the error (timestamp of the 1st row after the error, in other words the timestamp of the event that ends the error) ((assign to "Error_endTS"))
- assign a Life_cycle_ID to the rows of the error, either "Start" or "Ongoing". (In a later stage, the "Complete" Life_cycle_id will be inserted after the last row of "ongoing" of each "error-trace"; one problem at the time ;-)
My data
my_df <-
structure(list(Resource = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L),
.Label = c("L54", "L60", "L66", "L68", "L70", "L76",
"L78", "L95", "L96", "L97", "L98", "L99"),
class = "factor"),
Datetime_local = structure(c(1535952594, 1535952618, 1535952643, 1535952651,
1535952787, 1535952835, 1535952840, 1535952846,
1535952890, 1535952949, 1535952952, 1535952958,
1535953066),
class = c("POSIXct", "POSIXt"), tzone = ""),
State_type = structure(c(6L, 4L, 8L, 4L, 8L, 4L, 12L, 4L, 8L, 4L, 12L, 4L, 12L),
.Label = c("Comlink Down", "Comlink Up", "Counter",
"Error", "Message", "No part in", "No part out",
"Not active", "Part changing", "Part in", "Part out",
"Producing", "Waiting"),
class = "factor"),
event_ID = c("e00000000000072160", "e00000000000072270", "e00000000000072400",
"e00000000000072430", "e00000000000072810", "e00000000000073110",
"e00000000000073150", "e00000000000073170", "e00000000000073300",
"e00000000000073520", "e00000000000073540", "e00000000000073570",
"e00000000000074040")),
.Names = c("Resource", "Datetime_local", "State_type", "event_ID"),
row.names = 160:172, class = "data.frame")
... looking like this
Resource Datetime_local State_type event_ID
160 L60 2018-09-03 07:29:54 No part in e00000000000072160
161 L60 2018-09-03 07:30:18 Error e00000000000072270
162 L60 2018-09-03 07:30:43 Not active e00000000000072400
163 L60 2018-09-03 07:30:51 Error e00000000000072430
164 L60 2018-09-03 07:33:07 Not active e00000000000072810
165 L60 2018-09-03 07:33:55 Error e00000000000073110
166 L60 2018-09-03 07:34:00 Producing e00000000000073150
167 L60 2018-09-03 07:34:06 Error e00000000000073170
168 L60 2018-09-03 07:34:50 Not active e00000000000073300
169 L60 2018-09-03 07:35:49 Error e00000000000073520
170 L60 2018-09-03 07:35:52 Producing e00000000000073540
171 L60 2018-09-03 07:35:58 Error e00000000000073570
172 L60 2018-09-03 07:37:46 Producing e00000000000074040
My UDF:
AssignErrorNumber <- function(df) {
# set start values
require(dplyr)
errorNumber <- 0
i <- 1
j <- 0
df$Error_ID <- 0
df$Error_startTS <- NA
df$Error_endTS <- NA
df$Lifecycle_ID <- NA
# loop through all rows
while (i <= nrow(df)) {
## find the first row with an error raised
if ( df$State_type[i] == "Error") {
# for the first row for this error,
# increase error counter and get startTS
# save them for this row
errorNumber <- errorNumber + 1
startTS <- df$Datetime_local[i]
df$Error_ID[i] <- errorNumber
df$Error_startTS[i] <- startTS
df$Lifecycle_ID[i] <- "Start"
# do the following for each following row
# until state_type goes to non-error state
# save error_number and startTS for this row
i <- i+1
j <- 1 # counter for the loop
while (df$State_type[i] %in% c("Error", "Comlink Down", "Not active")) {
df$Error_ID[i] <- errorNumber
df$Error_startTS[i] <- startTS
df$Lifecycle_ID[i] <- "Ongoing"
i <- i+1
j <- j+1
}
# we saw the last row for this error, mark as "ongoing" AND add a row later on with "complete"
# alternatively we could mark this as "completed", but this
# mixes things up: the time when an error is finished is not this Datetime_local!
if (j!=1){ # if not first line, this should remain "start"
df$Lifecycle_ID[i-1] <- "Ongoing"
}
}
# before going to the next row,
# get TS from the row following the last error-row (if not end of file)
# go back and set endTS for this error_number
if (i <= nrow(df)) {
endTS <- df$Datetime_local[i]
}
else {
endTS <- df$Datetime_local[i-1] # last row: endTS = startTS of last row of error
}
while (j >= 1) {
df$Error_endTS[i-j] <- endTS
j <- j-1
}
# to go to next row
i <- i+1
}
# transform TS's to Date and time POSIXct
df$Error_startTS <- as.POSIXct(df$Error_startTS, origin = "1970-01-01")
df$Error_endTS <- as.POSIXct(df$Error_endTS, origin = "1970-01-01")
return(df)
}
Call to the UDF
AssignErrorNumber(my_df)
Wanted output // output of my looping function
Resource Datetime_local State_type event_ID Error_ID Error_startTS Error_endTS Lifecycle_ID
160 L60 2018-09-03 07:29:54 No part in e00000000000072160 0 <NA> <NA> <NA>
161 L60 2018-09-03 07:30:18 Error e00000000000072270 1 2018-09-03 07:30:18 2018-09-03 07:34:00 Start
162 L60 2018-09-03 07:30:43 Not active e00000000000072400 1 2018-09-03 07:30:18 2018-09-03 07:34:00 Ongoing
163 L60 2018-09-03 07:30:51 Error e00000000000072430 1 2018-09-03 07:30:18 2018-09-03 07:34:00 Ongoing
164 L60 2018-09-03 07:33:07 Not active e00000000000072810 1 2018-09-03 07:30:18 2018-09-03 07:34:00 Ongoing
165 L60 2018-09-03 07:33:55 Error e00000000000073110 1 2018-09-03 07:30:18 2018-09-03 07:34:00 Ongoing
166 L60 2018-09-03 07:34:00 Producing e00000000000073150 0 <NA> <NA> <NA>
167 L60 2018-09-03 07:34:06 Error e00000000000073170 2 2018-09-03 07:34:06 2018-09-03 07:35:52 Start
168 L60 2018-09-03 07:34:50 Not active e00000000000073300 2 2018-09-03 07:34:06 2018-09-03 07:35:52 Ongoing
169 L60 2018-09-03 07:35:49 Error e00000000000073520 2 2018-09-03 07:34:06 2018-09-03 07:35:52 Ongoing
170 L60 2018-09-03 07:35:52 Producing e00000000000073540 0 <NA> <NA> <NA>
171 L60 2018-09-03 07:35:58 Error e00000000000073570 3 2018-09-03 07:35:58 2018-09-03 07:37:46 Start
172 L60 2018-09-03 07:37:46 Producing e00000000000074040 0 <NA> <NA> <NA>
My sincere gratitude to anyone who has read through this long question. And I repeat my question: "Can this set of problems be vectorised?"
