4
\$\begingroup\$

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)
)
\$\endgroup\$
  • 1
    \$\begingroup\$ can you add example data set? \$\endgroup\$ – minem Jun 21 '18 at 14:25
  • \$\begingroup\$ @minem I uploaded code for a sample data set \$\endgroup\$ – Lost Jun 21 '18 at 15:15
1
\$\begingroup\$

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)]
\$\endgroup\$
  • 1
    \$\begingroup\$ Thank you so much! It works exactly like I needed it to! \$\endgroup\$ – Lost Jun 22 '18 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.