# data.table way this stuff feels faster than dplyr but isn't very FP when using := methods
# alternatively, use the .() aka list() feature and create a new table. Still faster than dplyr or plyr
#
https://mran.microsoft.com/web/packages/data.table/vignettes/datatable-intro.html
library(data.table) # for fread and other data.table functions
library(tidyverse) # for as_tibble to feed into ggplot
library(lubridate) # for round_date
library(fasttime) # for fastPOSIXct
dtCLE01=fread("c:/kewoo/eai/CLE.Identity.d20171204.csv")
AESTDiff <- 36000
interval.length <- "1 seconds"
# exploratory
str(dtCLE01)
nrow(dtCLE01)
names(dtCLE01)
dtCLE01[,.(TIME_STAMP, APPLICATIONID)]
# end exploration
tb01.tx.times.all <-dtCLE01[, list(transactionid,
componentname,
startPct = round_date(fastPOSIXct(start)-AESTDiff, interval.length),
endtPct = round_date(fastPOSIXct(endt)-AESTDiff, interval.length))
]
tb01.expandedIntervals <- tb01.tx.times.all[, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
][, list(txCount = .N), by = intervals]
ggplot() +
geom_line(data=tb01.expandedIntervals, aes(x=intervals,y=txCount), color='blue')
# FOR FILTERED TIME RANGE
start.AEST <- fastPOSIXct("2017-12-04 21:17:00")-36000
end.AEST <- fastPOSIXct("2017-12-04 21:32:00")-36000
tb01.tx.times.filtered <- tb01.tx.times.all[startPct > start.AEST & endtPct < end.AEST]
tb01.expandedIntervals <- tb01.tx.times.filtered[, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
][, list(txCount = .N), by = intervals]
ggplot() +
geom_line(data=tb01.expandedIntervals, aes(x=intervals,y=txCount), color='blue')
dtER=fread("c:/kewoo/eai/EXCEPTIONREC.identity.d20171204.csv")
tb02.tx.times.all <-dtER[, list(transactionid,
COMPONENTNAME,
endtPct = round_date(fastPOSIXct(TIME_STAMP)-AESTDiff, interval.length))]
tb02.txCounts <- tb02.tx.times.all[, list(txCount = .N), by = endtPct]
ggplot() +
geom_line(data=tb01.expandedIntervals, aes(x=intervals,y=txCount), color='blue') +
geom_line(data=tb02.txCounts, aes(x=endtPct,y=txCount), color='red')
# 20171215: The reason there's a drop in txCount during a service interruption
# is because startPct == endPct caused by the group by in the original extracting SQL
# Solution is to extract actual endPct from EXCEPTIONREC joining via transactionid
# first, take outer join
dtOJ <- tb02.tx.times.all[tb01.tx.times.all, on = "transactionid"]
# second, populate blank (NA) startPct values with i.startPct
dtOJ[is.na(endtPct), endtPct := i.endtPct]
dtOJ.expandedIntervals <- dtOJ[, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
][, list(txCount = .N), by = intervals]
ggplot() +
geom_line(data=dtOJ.expandedIntervals, aes(x=intervals,y=txCount), color='blue')
