trjhtr

I have the following code to create a table for overall percentages and the past 30 day percentages. I would like to add this to a Shiny app I'm making, but I feel there is a simpler or shorter way to create this code/table than what I have, which would work better in Shiny. I'm not asking for the Shiny code, just a way to reduce the code I have.

a$FAIL<-ifelse(a$OPERATION_STATUS %in% "FAIL", 1, 0)
cc<-as.data.frame((table(a$CRIT_CODE))) #Dataframe with frequency of each crit code
cf<-aggregate(FAIL~CRIT_CODE,a,sum) #Total number of fails based on crit codes
cc<-cbind(cc[,],cf[,2])
names(cc)<-c("CRIT_CODE", "Freq", "FAIL") #Change column names
cc<-transform(cc, Percent=FAIL/Freq*100)

last30<-a[which(a$DATE>=(Sys.Date()-30)),]
last<-as.data.frame((table(last30$CRIT_CODE))) #Dataframe with frequency ofeach crit code
lastfails<-aggregate(FAIL~CRIT_CODE,last30,sum) #Total number of fails based on crit codes
last<-cbind(last[,],lastfails[,2])
names(last)<-c("CRIT_CODE", "Freq", "FAIL") #Change column names
last<-transform(last, Percent=FAIL/Freq*100)

rates<-merge(cc[,c(1,4)], last[,c(1,4)], by="CRIT_CODE")
rates$Percent.x<-round(rates$Percent.x, 2)
rates$Percent.y<-round(rates$Percent.y, 2)
library(gridExtra)
grid.table(rates, rows=NULL, cols=c("Area", "Overall Percent", "30 Day Percent"))

Sample Data

Data <- data.frame(
 DATE=sample(seq.Date(as.Date("2018-01-01"), as.Date("2018-06-15"), "days"), 15),
 OPERATION_STATUS=sample(c("PASS","FAIL"), 15, replace=TRUE),
 CRIT_CODE=sample(c("A", "B", "C"), 15, replace=TRUE)
)

Changed a little bit input data:

n <- 60
set.seed(21)
a <- data.frame(
 DATE = 
 rev(seq.Date(as.Date("2018-01-01"), as.Date("2018-06-15"), "days"))[1:n],
 OPERATION_STATUS = sample(c("PASS","FAIL"), n, replace = TRUE),
 CRIT_CODE = sample(c("A", "B", "C"), n, replace = TRUE)
)

Using data.table I would do it like this:

require(data.table)
setDT(a) 
a[, group_i := DATE >= (Sys.Date() - 30)]
rates3 <- a[, .(
 Percent1 = round(sum(OPERATION_STATUS == 'FAIL') / .N * 100, 2),
 Percent2 = round(sum(OPERATION_STATUS[group_i] == 'FAIL') / sum(group_i) * 100, 2)
 ),
 keyby = CRIT_CODE]

rates3
# CRIT_CODE Percent1 Percent2
# 1: A 71.43 75.00
# 2: B 63.16 66.67
# 3: C 59.26 45.45

Presumably for large data one of the fastest approaches.

P.S. results changes depending on Sys.Date()

Names can be added directly:

rates3 <- a[, .(
 'Overall Percent' = round(sum(OPERATION_STATUS == 'FAIL') / .N * 100, 2),
 '30 Day Percent' = round(sum(OPERATION_STATUS[group_i] == 'FAIL') / sum(group_i) * 100, 2)
 ),
 keyby = .(Area = CRIT_CODE)]