R-helper:
Below, code for generating a decile table.
I am using the xtable package, but it is not quite right for the output.
Issue #1. xtable inserts an unwanted column, before the first derived
column DECILE
Issue #2. In the last line "Total" I manually sum all columns, even
though I only want the sums for second and third columns.
If I calculate only second and third columns, the remaining columns
would have NAs.
Either scenario is not desired.
Any suggestions, would be appreciated, for a package that addresses
issue #1,
and has an option for summing the desired two columns.
Lastly, I read that one should rarely use "attach()", but if I
don't the
program will not run.
An explanation of why I need attach() would also be appreciated.
Thanks.
Bruce
****
Response <- data.frame(Response=rbinom(50,1,0.2), yhat=runif(50))
Response <- Response[order(Response$yhat,decreasing=TRUE),]
Response[[2]] <- NULL
cum_R <- cumsum(Response)
sam_size <- nrow(Response)
cum_n <- seq(1:1,sam_size)
wt <- rep(c(1), times=sam_size)
cum_wt <- cumsum(wt)
dec <- (cum_n/sam_size)
decc <- floor((cum_n*10)/(sam_size+1))
dec_mean <- aggregate(Response, by=list(decc), mean)
dd_ <- data.frame(cum_R, sam_size, cum_wt, cum_n, decc)
dd <- cbind(Response, dd_)
names(dd)[2] <- "cum_R"
dec_mean <- aggregate(Response ~ decc, dd, mean)
wt <- rep(c(1), times=sam_size)
cum_wt <- aggregate(wt ~ decc, dd, sum)
cum_R <- aggregate(Response ~ decc, dd, sum)
dec_mean_wt <- cbind(dec_mean, cum_wt)
dec_mean_wt <- dec_mean_wt[-3]
dec_mean_wt_R <- cbind(dec_mean_wt, cum_R)
dec_mean_wt_R <- dec_mean_wt_R[-4]
colnames(dec_mean_wt_R) <- c("Decile", "Resp_Rate",
"No_Inds",
"No_Resp")
dec_mean_wt_R <- dec_mean_wt_R[,c(1,3,4,2)]
cum_n <- dec_mean_wt_R[2]
cum_n <- cumsum(cum_n)
cum_R <- dec_mean_wt_R[3]
cum_R <- cumsum(cum_R)
dec_mean_wt_R_nR <- cbind(dec_mean_wt_R, cum_n, cum_R)
colnames(dec_mean_wt_R_nR) <-
c("Decile", "No_Inds", "No_Resp",
"RespRate",
"Cum_n", "Cum_R")
dec_mean_wt_R_nR
attach(dec_mean_wt_R_nR)
Cum_RespRate <- (Cum_R/Cum_n)*100
options(digits=4)
Decile_RespRate <- (No_Resp/No_Inds)
dec_mean_wt_R_nRD <- cbind(dec_mean_wt_R_nR, Cum_RespRate, Decile_RespRate)
avg_RR <- dec_mean_wt_R_nRD[10,7]
Cum_Lift <- (Cum_RespRate/avg_RR)*100
DECILE <-
c("top","2","3","4","5","6","7","8","9","bot")
dec_mean_wt_R_nRDL <- cbind(DECILE, dec_mean_wt_R_nRD, Cum_Lift)
dec_mean_wt_R_nRDL <- dec_mean_wt_R_nRDL[,c(1,3,4,9,8,10)]
total_line<-cbind(DECILE="Total",
as.data.frame(matrix(colSums(dec_mean_wt_R_nRDL[-1]),nrow=1)))
names(total_line)<-names(dec_mean_wt_R_nRDL)
dec_mean_wt_R_nRDLT<-rbind(dec_mean_wt_R_nRDL,total_line)
decile_table <- dec_mean_wt_R_nRDLT
decile_table
#Install the xtable package: install.packages("xtable")
#Load the xtable package:
library(xtable)
DECILE_TABLE <-xtable(decile_table)
DECILE_TABLE
print.xtable(DECILE_TABLE,
type="html",file="C:/R_Data/DecileTable.html")
****
--
On 20/04/2017 1:09 PM, BR_email wrote:> R-helper: > Below, code for generating a decile table. > I am using the xtable package, but it is not quite right for the output. > Issue #1. xtable inserts an unwanted column, before the first derived > column DECILE > Issue #2. In the last line "Total" I manually sum all columns, even > though I only want the sums for second and third columns. > If I calculate only second and third columns, the remaining columns > would have NAs. > Either scenario is not desired. >I haven't gone through your code yet, but the tables package is generally more flexible (though maybe harder to use) than xtable. I can't look at it now, but will try to remember to do so in a few hours if I don't see a better solution posted first. Duncan Murdoch
Duncan: Thanks. I've exhausted my search for a simple table package that also allows for column sums. If you are not familiar with the decile table, you will find it quite embedded with much insight for predominance of virtually any model. Regards, Bruce ______________ Bruce Ratner PhD The Significant Statistician? (516) 791-3544 Statistical Predictive Analytics -- www.DMSTAT1.com Machine-Learning Data Mining -- www.GenIQ.net> On Apr 20, 2017, at 3:06 PM, Duncan Murdoch <murdoch.duncan at gmail.com> wrote: > >> On 20/04/2017 1:09 PM, BR_email wrote: >> R-helper: >> Below, code for generating a decile table. >> I am using the xtable package, but it is not quite right for the output. >> Issue #1. xtable inserts an unwanted column, before the first derived >> column DECILE >> Issue #2. In the last line "Total" I manually sum all columns, even >> though I only want the sums for second and third columns. >> If I calculate only second and third columns, the remaining columns >> would have NAs. >> Either scenario is not desired. >> > > > I haven't gone through your code yet, but the tables package is generally more flexible (though maybe harder to use) than xtable. > > I can't look at it now, but will try to remember to do so in a few hours if I don't see a better solution posted first. > > Duncan Murdoch > > >
#1 You can remove the rownames by adding the argument include.rownames=FALSE to
print.xtable():
print.xtable(DECILE_TABLE,
type="html",file="DecileTable.html", include.rownames=FALSE)
#2 Prevent data.frame from converting the first column to a factor and use NAs
for the columns where you don't want totals:
dec_mean_wt_R_nRDL <- cbind(DECILE, dec_mean_wt_R_nRD, Cum_Lift,
stringsAsFactors=FALSE)
dec_mean_wt_R_nRDL <- dec_mean_wt_R_nRDL[,c(1,3,4,9,8,10)]
total_line<-cbind(DECILE="Total",
as.data.frame(matrix(c(colSums(dec_mean_wt_R_nRDL[ , 2:3]), rep(NA,
3)),nrow=1)))
Now the table should print without totals in the last three columns and no
rownames.
attach is discouraged since it can lead to confusion when a variable name exists
in the environment and in a data frame (or multiple data frames). It is easy to
forget which version of the variable you are using. More typing, but less
subject to confusion would be to use with(), eg:
Cum_RespRate <- with(dec_mean_wt_R_nR, (Cum_R/Cum_n)*100)
This way it is always clear where Cum_R and Cum_n are coming from. In your code
cum_R = Cum_R and cum_n = Cum_n so you could also use
Cum_RespRate <- cum_R/cum_n)*100
-------------------------------------
David L Carlson
Department of Anthropology
Texas A&M University
College Station, TX 77840-4352
-----Original Message-----
From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of BR_email
Sent: Thursday, April 20, 2017 12:10 PM
To: r-help at r-project.org
Subject: [R] Looking for a package to replace xtable
R-helper:
Below, code for generating a decile table.
I am using the xtable package, but it is not quite right for the output.
Issue #1. xtable inserts an unwanted column, before the first derived
column DECILE
Issue #2. In the last line "Total" I manually sum all columns, even
though I only want the sums for second and third columns.
If I calculate only second and third columns, the remaining columns
would have NAs.
Either scenario is not desired.
Any suggestions, would be appreciated, for a package that addresses
issue #1,
and has an option for summing the desired two columns.
Lastly, I read that one should rarely use "attach()", but if I
don't the
program will not run.
An explanation of why I need attach() would also be appreciated.
Thanks.
Bruce
****
Response <- data.frame(Response=rbinom(50,1,0.2), yhat=runif(50))
Response <- Response[order(Response$yhat,decreasing=TRUE),]
Response[[2]] <- NULL
cum_R <- cumsum(Response)
sam_size <- nrow(Response)
cum_n <- seq(1:1,sam_size)
wt <- rep(c(1), times=sam_size)
cum_wt <- cumsum(wt)
dec <- (cum_n/sam_size)
decc <- floor((cum_n*10)/(sam_size+1))
dec_mean <- aggregate(Response, by=list(decc), mean)
dd_ <- data.frame(cum_R, sam_size, cum_wt, cum_n, decc)
dd <- cbind(Response, dd_)
names(dd)[2] <- "cum_R"
dec_mean <- aggregate(Response ~ decc, dd, mean)
wt <- rep(c(1), times=sam_size)
cum_wt <- aggregate(wt ~ decc, dd, sum)
cum_R <- aggregate(Response ~ decc, dd, sum)
dec_mean_wt <- cbind(dec_mean, cum_wt)
dec_mean_wt <- dec_mean_wt[-3]
dec_mean_wt_R <- cbind(dec_mean_wt, cum_R)
dec_mean_wt_R <- dec_mean_wt_R[-4]
colnames(dec_mean_wt_R) <- c("Decile", "Resp_Rate",
"No_Inds",
"No_Resp")
dec_mean_wt_R <- dec_mean_wt_R[,c(1,3,4,2)]
cum_n <- dec_mean_wt_R[2]
cum_n <- cumsum(cum_n)
cum_R <- dec_mean_wt_R[3]
cum_R <- cumsum(cum_R)
dec_mean_wt_R_nR <- cbind(dec_mean_wt_R, cum_n, cum_R)
colnames(dec_mean_wt_R_nR) <-
c("Decile", "No_Inds", "No_Resp",
"RespRate",
"Cum_n", "Cum_R")
dec_mean_wt_R_nR
attach(dec_mean_wt_R_nR)
Cum_RespRate <- (Cum_R/Cum_n)*100
options(digits=4)
Decile_RespRate <- (No_Resp/No_Inds)
dec_mean_wt_R_nRD <- cbind(dec_mean_wt_R_nR, Cum_RespRate, Decile_RespRate)
avg_RR <- dec_mean_wt_R_nRD[10,7]
Cum_Lift <- (Cum_RespRate/avg_RR)*100
DECILE <-
c("top","2","3","4","5","6","7","8","9","bot")
dec_mean_wt_R_nRDL <- cbind(DECILE, dec_mean_wt_R_nRD, Cum_Lift)
dec_mean_wt_R_nRDL <- dec_mean_wt_R_nRDL[,c(1,3,4,9,8,10)]
total_line<-cbind(DECILE="Total",
as.data.frame(matrix(colSums(dec_mean_wt_R_nRDL[-1]),nrow=1)))
names(total_line)<-names(dec_mean_wt_R_nRDL)
dec_mean_wt_R_nRDLT<-rbind(dec_mean_wt_R_nRDL,total_line)
decile_table <- dec_mean_wt_R_nRDLT
decile_table
#Install the xtable package: install.packages("xtable")
#Load the xtable package:
library(xtable)
DECILE_TABLE <-xtable(decile_table)
DECILE_TABLE
print.xtable(DECILE_TABLE,
type="html",file="C:/R_Data/DecileTable.html")
****
--
______________________________________________
R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
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.
David: Thanks so much. I will recode and let you know how it works out. Bruce ______________ Bruce Ratner PhD The Significant Statistician? (516) 791-3544 Statistical Predictive Analytics -- www.DMSTAT1.com Machine-Learning Data Mining -- www.GenIQ.net> On Apr 20, 2017, at 4:31 PM, David L Carlson <dcarlson at tamu.edu> wrote: > > #1 You can remove the rownames by adding the argument include.rownames=FALSE to print.xtable(): > > print.xtable(DECILE_TABLE, type="html",file="DecileTable.html", include.rownames=FALSE) > > #2 Prevent data.frame from converting the first column to a factor and use NAs for the columns where you don't want totals: > > dec_mean_wt_R_nRDL <- cbind(DECILE, dec_mean_wt_R_nRD, Cum_Lift, stringsAsFactors=FALSE) > dec_mean_wt_R_nRDL <- dec_mean_wt_R_nRDL[,c(1,3,4,9,8,10)] > > total_line<-cbind(DECILE="Total", > as.data.frame(matrix(c(colSums(dec_mean_wt_R_nRDL[ , 2:3]), rep(NA, 3)),nrow=1))) > > Now the table should print without totals in the last three columns and no rownames. > > attach is discouraged since it can lead to confusion when a variable name exists in the environment and in a data frame (or multiple data frames). It is easy to forget which version of the variable you are using. More typing, but less subject to confusion would be to use with(), eg: > > Cum_RespRate <- with(dec_mean_wt_R_nR, (Cum_R/Cum_n)*100) > > This way it is always clear where Cum_R and Cum_n are coming from. In your code cum_R = Cum_R and cum_n = Cum_n so you could also use > > Cum_RespRate <- cum_R/cum_n)*100 > > ------------------------------------- > David L Carlson > Department of Anthropology > Texas A&M University > College Station, TX 77840-4352 > > -----Original Message----- > From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of BR_email > Sent: Thursday, April 20, 2017 12:10 PM > To: r-help at r-project.org > Subject: [R] Looking for a package to replace xtable > > R-helper: > Below, code for generating a decile table. > I am using the xtable package, but it is not quite right for the output. > Issue #1. xtable inserts an unwanted column, before the first derived > column DECILE > Issue #2. In the last line "Total" I manually sum all columns, even > though I only want the sums for second and third columns. > If I calculate only second and third columns, the remaining columns > would have NAs. > Either scenario is not desired. > > Any suggestions, would be appreciated, for a package that addresses > issue #1, > and has an option for summing the desired two columns. > > Lastly, I read that one should rarely use "attach()", but if I don't the > program will not run. > An explanation of why I need attach() would also be appreciated. > Thanks. > Bruce > > **** > Response <- data.frame(Response=rbinom(50,1,0.2), yhat=runif(50)) > Response <- Response[order(Response$yhat,decreasing=TRUE),] > > Response[[2]] <- NULL > > cum_R <- cumsum(Response) > sam_size <- nrow(Response) > > cum_n <- seq(1:1,sam_size) > wt <- rep(c(1), times=sam_size) > cum_wt <- cumsum(wt) > > dec <- (cum_n/sam_size) > decc <- floor((cum_n*10)/(sam_size+1)) > > dec_mean <- aggregate(Response, by=list(decc), mean) > > dd_ <- data.frame(cum_R, sam_size, cum_wt, cum_n, decc) > dd <- cbind(Response, dd_) > names(dd)[2] <- "cum_R" > > dec_mean <- aggregate(Response ~ decc, dd, mean) > > wt <- rep(c(1), times=sam_size) > cum_wt <- aggregate(wt ~ decc, dd, sum) > cum_R <- aggregate(Response ~ decc, dd, sum) > > dec_mean_wt <- cbind(dec_mean, cum_wt) > dec_mean_wt <- dec_mean_wt[-3] > > dec_mean_wt_R <- cbind(dec_mean_wt, cum_R) > dec_mean_wt_R <- dec_mean_wt_R[-4] > > colnames(dec_mean_wt_R) <- c("Decile", "Resp_Rate", "No_Inds", > "No_Resp") > > dec_mean_wt_R <- dec_mean_wt_R[,c(1,3,4,2)] > > cum_n <- dec_mean_wt_R[2] > cum_n <- cumsum(cum_n) > > cum_R <- dec_mean_wt_R[3] > cum_R <- cumsum(cum_R) > > dec_mean_wt_R_nR <- cbind(dec_mean_wt_R, cum_n, cum_R) > > colnames(dec_mean_wt_R_nR) <- > c("Decile", "No_Inds", "No_Resp", "RespRate", > "Cum_n", "Cum_R") > > dec_mean_wt_R_nR > > attach(dec_mean_wt_R_nR) > Cum_RespRate <- (Cum_R/Cum_n)*100 > > options(digits=4) > Decile_RespRate <- (No_Resp/No_Inds) > > dec_mean_wt_R_nRD <- cbind(dec_mean_wt_R_nR, Cum_RespRate, Decile_RespRate) > > avg_RR <- dec_mean_wt_R_nRD[10,7] > Cum_Lift <- (Cum_RespRate/avg_RR)*100 > > DECILE <- c("top","2","3","4","5","6","7","8","9","bot") > dec_mean_wt_R_nRDL <- cbind(DECILE, dec_mean_wt_R_nRD, Cum_Lift) > dec_mean_wt_R_nRDL <- dec_mean_wt_R_nRDL[,c(1,3,4,9,8,10)] > > total_line<-cbind(DECILE="Total", > as.data.frame(matrix(colSums(dec_mean_wt_R_nRDL[-1]),nrow=1))) > > names(total_line)<-names(dec_mean_wt_R_nRDL) > dec_mean_wt_R_nRDLT<-rbind(dec_mean_wt_R_nRDL,total_line) > decile_table <- dec_mean_wt_R_nRDLT > decile_table > > #Install the xtable package: install.packages("xtable") > #Load the xtable package: > library(xtable) > > DECILE_TABLE <-xtable(decile_table) > DECILE_TABLE > > print.xtable(DECILE_TABLE, type="html",file="C:/R_Data/DecileTable.html") > > **** > > -- > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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. >
David: All is perfect, almost - after I ran your corrections. Is there a way I can have more control of the column names, i.e., not be restricted to abbreviations headings, and center-justify? Thanks a lot, nice. Bruce David L Carlson wrote:> #1 You can remove the rownames by adding the argument include.rownames=FALSE to print.xtable(): > > print.xtable(DECILE_TABLE, type="html",file="DecileTable.html", include.rownames=FALSE) > > #2 Prevent data.frame from converting the first column to a factor and use NAs for the columns where you don't want totals: > > dec_mean_wt_R_nRDL <- cbind(DECILE, dec_mean_wt_R_nRD, Cum_Lift, stringsAsFactors=FALSE) > dec_mean_wt_R_nRDL <- dec_mean_wt_R_nRDL[,c(1,3,4,9,8,10)] > > total_line<-cbind(DECILE="Total", > as.data.frame(matrix(c(colSums(dec_mean_wt_R_nRDL[ , 2:3]), rep(NA, 3)),nrow=1))) > > Now the table should print without totals in the last three columns and no rownames. > > attach is discouraged since it can lead to confusion when a variable name exists in the environment and in a data frame (or multiple data frames). It is easy to forget which version of the variable you are using. More typing, but less subject to confusion would be to use with(), eg: > > Cum_RespRate <- with(dec_mean_wt_R_nR, (Cum_R/Cum_n)*100) > > This way it is always clear where Cum_R and Cum_n are coming from. In your code cum_R = Cum_R and cum_n = Cum_n so you could also use > > Cum_RespRate <- cum_R/cum_n)*100 > > ------------------------------------- > David L Carlson > Department of Anthropology > Texas A&M University > College Station, TX 77840-4352 > > -----Original Message----- > From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of BR_email > Sent: Thursday, April 20, 2017 12:10 PM > To: r-help at r-project.org > Subject: [R] Looking for a package to replace xtable > > R-helper: > Below, code for generating a decile table. > I am using the xtable package, but it is not quite right for the output. > Issue #1. xtable inserts an unwanted column, before the first derived > column DECILE > Issue #2. In the last line "Total" I manually sum all columns, even > though I only want the sums for second and third columns. > If I calculate only second and third columns, the remaining columns > would have NAs. > Either scenario is not desired. > > Any suggestions, would be appreciated, for a package that addresses > issue #1, > and has an option for summing the desired two columns. > > Lastly, I read that one should rarely use "attach()", but if I don't the > program will not run. > An explanation of why I need attach() would also be appreciated. > Thanks. > Bruce > > **** > Response <- data.frame(Response=rbinom(50,1,0.2), yhat=runif(50)) > Response <- Response[order(Response$yhat,decreasing=TRUE),] > > Response[[2]] <- NULL > > cum_R <- cumsum(Response) > sam_size <- nrow(Response) > > cum_n <- seq(1:1,sam_size) > wt <- rep(c(1), times=sam_size) > cum_wt <- cumsum(wt) > > dec <- (cum_n/sam_size) > decc <- floor((cum_n*10)/(sam_size+1)) > > dec_mean <- aggregate(Response, by=list(decc), mean) > > dd_ <- data.frame(cum_R, sam_size, cum_wt, cum_n, decc) > dd <- cbind(Response, dd_) > names(dd)[2] <- "cum_R" > > dec_mean <- aggregate(Response ~ decc, dd, mean) > > wt <- rep(c(1), times=sam_size) > cum_wt <- aggregate(wt ~ decc, dd, sum) > cum_R <- aggregate(Response ~ decc, dd, sum) > > dec_mean_wt <- cbind(dec_mean, cum_wt) > dec_mean_wt <- dec_mean_wt[-3] > > dec_mean_wt_R <- cbind(dec_mean_wt, cum_R) > dec_mean_wt_R <- dec_mean_wt_R[-4] > > colnames(dec_mean_wt_R) <- c("Decile", "Resp_Rate", "No_Inds", > "No_Resp") > > dec_mean_wt_R <- dec_mean_wt_R[,c(1,3,4,2)] > > cum_n <- dec_mean_wt_R[2] > cum_n <- cumsum(cum_n) > > cum_R <- dec_mean_wt_R[3] > cum_R <- cumsum(cum_R) > > dec_mean_wt_R_nR <- cbind(dec_mean_wt_R, cum_n, cum_R) > > colnames(dec_mean_wt_R_nR) <- > c("Decile", "No_Inds", "No_Resp", "RespRate", > "Cum_n", "Cum_R") > > dec_mean_wt_R_nR > > attach(dec_mean_wt_R_nR) > Cum_RespRate <- (Cum_R/Cum_n)*100 > > options(digits=4) > Decile_RespRate <- (No_Resp/No_Inds) > > dec_mean_wt_R_nRD <- cbind(dec_mean_wt_R_nR, Cum_RespRate, Decile_RespRate) > > avg_RR <- dec_mean_wt_R_nRD[10,7] > Cum_Lift <- (Cum_RespRate/avg_RR)*100 > > DECILE <- c("top","2","3","4","5","6","7","8","9","bot") > dec_mean_wt_R_nRDL <- cbind(DECILE, dec_mean_wt_R_nRD, Cum_Lift) > dec_mean_wt_R_nRDL <- dec_mean_wt_R_nRDL[,c(1,3,4,9,8,10)] > > total_line<-cbind(DECILE="Total", > as.data.frame(matrix(colSums(dec_mean_wt_R_nRDL[-1]),nrow=1))) > > names(total_line)<-names(dec_mean_wt_R_nRDL) > dec_mean_wt_R_nRDLT<-rbind(dec_mean_wt_R_nRDL,total_line) > decile_table <- dec_mean_wt_R_nRDLT > decile_table > > #Install the xtable package: install.packages("xtable") > #Load the xtable package: > library(xtable) > > DECILE_TABLE <-xtable(decile_table) > DECILE_TABLE > > print.xtable(DECILE_TABLE, type="html",file="C:/R_Data/DecileTable.html") > > **** > > -- > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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. > > >