[R] additions to r pivot table

[arun]

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" <bcrombie at 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