Hi Burnette,
Try:
indx <- grep("\\bMW\\b|\\bOT\\b|\\bposter\\b|\\brecords\\b", rownames(res3))
indx1 <- grep("Grand Total",rownames(res3))
res3R
<-
as.data.frame(matrix(NA,ncol=ncol(res3),nrow=nrow(res3),dimnames=
list(rownames(res3),c("rate of Count of Case ID", "rate of Sum of
BW_Due", "rate of Sum of BW_ATP", "rate of Sum of EEs_Rep"))))
res3R[c(indx,indx1),] <- t(t(res3[c(indx,indx1),])/unlist(res3[indx1,]))
indx2 <- as.vector(table(gsub("\\_.*","",rownames(res3[-c(indx,indx1),]))))
#or
#indx2 <- res3[indx,1]
res3R[-c(indx,indx1),] <- res3[-c(indx,indx1),]/unlist(res3[rep(indx,indx2),])
res4 <- cbind(res3,res3R)[,rep(seq(1,8,by=4),4) + rep(seq(0,3),each=2)]
head(res4,2)
# Count of Case ID rate of Count of Case ID Sum of BW_Due
#MW 2 0.4 168.11
#MW_tip 1 0.5 55.76
# rate of Sum of BW_Due Sum of BW_ATP rate of Sum of BW_ATP Sum of EEs_Rep
#MW 0.7202348 155.86 0.7953664 29
#MW_tip 0.3316876 55.76 0.3577570 9
# rate of Sum of EEs_Rep
#MW 0.5370370
#MW_tip 0.3103448
A.K.
On Wednesday, February 26, 2014 12:57 PM, "Crombie, Burnette N"
<bcrom...@utk.edu> wrote:
A.K., I’m struggling with adding columns that calculate rate in the dataset you
helped me with earlier:
Is this something I should resubmit to Rhelp as a new question? Thanks!
FLSAdata <- data.frame(CaseID = c(1881, 1882, 1883, 1884, 1885),
ViolationDesc = c("records", "poster", "MW", "OT", "MW"),
ReasonDesc = c("", "", "tip", "bonus", "uniform"),
BW_Due = c(0, 0, 55.76, 65.30, 112.35),
BW_ATP = c(0, 0, 55.76, 40.10, 100.10),
EEs_Rep = c(4, 11, 9, 10, 20))
FLSAdata$CaseID <- factor(FLSAdata$CaseID) FLSAdata [FLSAdata ==""] <- NA
library(reshape2)
FLSAdata_melt
<- melt(FLSAdata,id=c("CaseID", "ViolationDesc",
"ReasonDesc"),measure=c("BW_Due", "BW_ATP", "EEs_Rep"), na.rm=FALSE)
res1 <- acast(FLSAdata_melt, ViolationDesc + ReasonDesc ~ variable, sum,
margins=TRUE)[,-4]
res2
<- within(as.data.frame(res1),`Count of Case ID` <-
dcast(FLSAdata_melt, ViolationDesc + ReasonDesc ~ variable, length,
margins=TRUE)[,3])[,c(4,1:3)] colnames(res2)[2:4] <- paste("Sum
of",colnames(res2)[2:4]) rownames(res2)[length(rownames(res2))] <-
"Grand Total"
indx <- grepl("all",rownames(res2))
ord1
<-
unlist(tapply(seq_len(nrow(res2)),list(cumsum(c(TRUE,diff(indx)<0))),FUN=function(x)
c(tail(x,1),head(x,-1)) ),use.names=FALSE)
res3 <- res2[ord1,]
rownames(res3) <- gsub("\\_\\(all\\)","",rownames(res3))
res3
Burnette Crombie
Construction Industry Research & Policy Center (CIRPC)
219 Stokely Management Center
The University of Tennessee, Knoxville 37996
ph 865.974.4422
fax 865.545.4193
http://cirpc.bus.utk.edu
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
