[R] Help with tables in R

David L Carlson dcarlson at tamu.edu
Thu Jul 3 17:03:37 CEST 2014


Generating tables from your sample data like the ones you attached is simple using the ftable() function:

> (CCxT <- ftable(College+Campus~Term, mydata))
          College COBA     COE     COLFA    
          Campus     M O S   M O S     M O S
Term                                        
Fall 2010            1 1 1   0 0 1     0 0 0
Fall 2011            0 2 3   0 0 1     0 1 1
> (TCxCM <- ftable(Term+Campus~College+Major, mydata))
              Term   Fall 2010     Fall 2011    
              Campus         M O S         M O S
College Major                                   
COBA    A                    0 1 0         0 1 0
        B                    1 0 0         0 0 0
        C                    0 0 1         0 1 3
COE     A                    0 0 1         0 0 0
        B                    0 0 0         0 0 0
        C                    0 0 0         0 0 1
COLFA   A                    0 0 0         0 1 0
        B                    0 0 0         0 0 0
        C                    0 0 0         0 0 1

Getting the formatting you want is more difficult. Both ways that I can think of involve using Excel. One approach is to use write.ftable():

> write.ftable(TCxCM, file="TCxCM.txt", quote=FALSE)

Then open the text file in Excel. The default settings in Excel should give you the complete table although you will have to add lines, shading, other formatting.

The second approach is to use package xtable to format the table with html. 

> library(xtable)
> print(xtable(as.matrix(TCxCM)), file="TCxCM.html", type="html")

Then open this file in Excel or an html editor. Word is also a possibility, but usually opening it in Excel and then copying to Word gives better results. The table will have the row/column labels concatenated so that the table appears to be a flat (2 way) table. That happens when converting the ftable object to a matrix since xtable handles a number of different object types, but not ftable.

-------------------------------------
David L Carlson
Department of Anthropology
Texas A&M University
College Station, TX 77840-4352

-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of PIKAL Petr
Sent: Thursday, July 3, 2014 8:42 AM
To: Barry Lambert
Cc: r-help at r-project.org
Subject: Re: [R] Help with tables in R

Hi

Maybe you can find something in Hmisc package from Frank Harrel. Especially summary.formula first example is probably close to what you want.

And also this seems to be quite convenient.

http://cran.r-project.org/web/packages/stargazer/vignettes/stargazer.pdf

Regards
Petr


> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-
> project.org] On Behalf Of Barry Lambert
> Sent: Thursday, July 03, 2014 2:29 PM
> To: Jeff Newmiller
> Cc: r-help at r-project.org
> Subject: Re: [R] Help with tables in R
>
> Thanks for the suggestions Jeff.  I have added some text below that
> will
> hopefully clarify my question and more closely follow the guidelines.
> Barry
> --
>
> Sorry for the confusing first post. I have edited for clarity and
> included
> some sample data.
>
> Clarified summary of problem: I have an excel spreadsheet has a row for
> every student registered at the university for each term since fall
> 2010
> with the following information about each student in columns: Term,
> Campus,
> College, Major, Gender, Ethnicity, Age.
>
> My goal is to be able to generate a print ready report out of R that
> will
> build some output quality tables for printing. I don't care if they are
> PDF, HTML, etc., as long as I can print them and they are somewhat
> attractive. So far, I have imported the spreadsheet into R as a CSV I
> have
> been attempting to do this with the "GridExtra" library with some
> success.
>
> I have 3 problems thus far: 1. If the count for a cell in the table is
> zero, it does not appear in the table; 2. I am unable to understand how
> to
> create more complex tables: for example a table that; 3. I am not able
> to
> create a column and row total.
>
> An example table is shown below:
>
>        ------Campus S-------|---------Campus M-----|-----Campus O------
>        2010    2011    2012   2010    2011    2012  2010    2011
> 2012   Total column
>
> COE
>
>     A
>     B
>     C
>
> COBA
>
>     A
>     B
>     C
>
> Totals -->
>
> Thus far my efforts have been something like this (small sample
> dataset):
>
> Term <- c("Fall 2010", "Fall 2010", "Fall 2011", "Fall 2011", "Fall
> 2011", "Fall 2011", "Fall 2010",
>           "Fall 2010", "Fall 2011", "Fall 2011", "Fall 2011", "Fall
> 2011")
> Campus <- c("S", "M", "O", "O", "S", "S", "O", "S", "S", "O", "S", "S")
> College <- c("COE", "COBA", "COBA", "COLFA", "COE", "COBA", "COBA",
> "COBA", "COBA", "COBA", "COBA", "COLFA")
> Major <- c("A", "B", "C", "A", "C", "C", "A", "C", "C", "A", "C", "C")
> Gender <- c("M", "F", "F", "F", "F", "M", "F", "F", "M", "F", "F", "M")
> Ethnicity <- c("B", "W", "W", "B", "B", "W", "B", "W", "W", "B", "W",
> "W")
> Age <- c(25, 27, 44, 62, 23, 36, 42, 44, 55, 65, 33, 20)
> mydata <- data.frame(Term, Campus, College, Major, Gender, Ethnicity,
> Age)
> mydata
>
> termxcamp.table <- table(mydata$Term, mydata$Campus)
> termxcoll.table <- table(mydata$Term, mydata$College)
>
> library(gridExtra)
> plot.new()
> grid.table(termxcamp.table)
> plot.new()
> grid.table(termxcoll.table)
>
>
>
> On Thu, Jul 3, 2014 at 3:05 AM, Jeff Newmiller
> <jdnewmil at dcn.davis.ca.us>
> wrote:
>
> > Your question is vague. You say you have code that does what you want
> it
> > to, yet do not share an example of it as a starting point, or explain
> what
> > you have not been able to do. It is the nature of the Internet that
> you
> > have to be precise in describing your problems and desired solutions.
> >
> > When I read "populate tables" I think of input processing, yet you
> say you
> > already have code, suggesting that you have successfully read in your
> data
> > to R.
> >
> > Since you attempted to attach Excel files to a posting, I assume you
> have
> > not yet read the Posting Guide mentioned at the bottom of this (or
> any
> > other email from this mailing list) since that document warns against
> > attaching non-text files. Please read it. Also, we tend to "speak" in
> R on
> > this list rather than translating from other programming dialects,
> > especially proprietary ones... that is your responsibility. Sort of
> like
> > foreign language immersion. :-)
> >
> > A word to the wise... it is significantly easier to read in CSV data
> than
> > XLS or XLSX data in R. For getting started in R I highly recommend
> > exporting your XLSX data to CSV.
> >
> > You may find [1] helpful in formulating a reproducible example to get
> the
> > ball rolling more effectively.
> >
> > [1]
> > http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-
> reproducible-example
> >
> > ---------------------------------------------------------------------
> ------
> > Jeff Newmiller                        The     .....       .....  Go
> Live...
> > DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live
> > Go...
> >                                       Live:   OO#.. Dead: OO#..
> Playing
> > Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
> > /Software/Embedded Controllers)               .OO#.       .OO#.
> rocks...1k
> > ---------------------------------------------------------------------
> ------
> > Sent from my phone. Please excuse my brevity.
> >
> > On July 2, 2014 7:33:12 PM PDT, Barry Lambert
> <barrylambert at gmail.com>
> > wrote:
> > >I am a new convert to R (from SAS).  I am a research scientist and
> most
> > >of
> > >my
> > >use of SAS was in data analysis.  Recently, I have been wanting to
> use
> > >R to
> > >create a simple, reliable way to summarize a dataset of student
> > >demographics
> > >for a university.
> > >The spreadsheet has a row for every student registered at the
> > >university for
> > >each term since fall 2010 with the following information about each
> > >student
> > >in columns:
> > >Columns are the following: Term, College, Program, Campus, Gender,
> > >Ethnicity, Age.
> > >
> > >I have created summary tables in Excel using if/then type formulas
> to
> > >select
> > >data and count the number of female students in program A at
> location
> > >3,
> > >etc.
> > >
> > >I have written some R code to create some figures that generally
> meet
> > >my
> > >needs.
> > >I would like to find a way to have R populate some tables with this
> > >type of
> > >information.
> > >
> > >An example of my excel sheets are attached.
> > >
> > >
> > >Any suggestions will be appreciated.
> > >
> > >
> > >--------------------------------------------------------------------
> ----
> > >
> > >______________________________________________
> > >R-help at 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.
> >
> >
>
>
> --
> --
> Barry Lambert
> Mobile/Text: 254-485-5328
>
>       [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at 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.

________________________________
Tento e-mail a jakékoliv k němu připojené dokumenty jsou důvěrné a jsou určeny pouze jeho adresátům.
Jestliže jste obdržel(a) tento e-mail omylem, informujte laskavě neprodleně jeho odesílatele. Obsah tohoto emailu i s přílohami a jeho kopie vymažte ze svého systému.
Nejste-li zamýšleným adresátem tohoto emailu, nejste oprávněni tento email jakkoliv užívat, rozšiřovat, kopírovat či zveřejňovat.
Odesílatel e-mailu neodpovídá za eventuální škodu způsobenou modifikacemi či zpožděním přenosu e-mailu.

V případě, že je tento e-mail součástí obchodního jednání:
- vyhrazuje si odesílatel právo ukončit kdykoliv jednání o uzavření smlouvy, a to z jakéhokoliv důvodu i bez uvedení důvodu.
- a obsahuje-li nabídku, je adresát oprávněn nabídku bezodkladně přijmout; Odesílatel tohoto e-mailu (nabídky) vylučuje přijetí nabídky ze strany příjemce s dodatkem či odchylkou.
- trvá odesílatel na tom, že příslušná smlouva je uzavřena teprve výslovným dosažením shody na všech jejích náležitostech.
- odesílatel tohoto emailu informuje, že není oprávněn uzavírat za společnost žádné smlouvy s výjimkou případů, kdy k tomu byl písemně zmocněn nebo písemně pověřen a takové pověření nebo plná moc byly adresátovi tohoto emailu případně osobě, kterou adresát zastupuje, předloženy nebo jejich existence je adresátovi či osobě jím zastoupené známá.

This e-mail and any documents attached to it may be confidential and are intended only for its intended recipients.
If you received this e-mail by mistake, please immediately inform its sender. Delete the contents of this e-mail with all attachments and its copies from your system.
If you are not the intended recipient of this e-mail, you are not authorized to use, disseminate, copy or disclose this e-mail in any manner.
The sender of this e-mail shall not be liable for any possible damage caused by modifications of the e-mail or by delay with transfer of the email.

In case that this e-mail forms part of business dealings:
- the sender reserves the right to end negotiations about entering into a contract in any time, for any reason, and without stating any reasoning.
- if the e-mail contains an offer, the recipient is entitled to immediately accept such offer; The sender of this e-mail (offer) excludes any acceptance of the offer on the part of the recipient containing any amendment or variation.
- the sender insists on that the respective contract is concluded only upon an express mutual agreement on all its aspects.
- the sender of this e-mail informs that he/she is not authorized to enter into any contracts on behalf of the company except for cases in which he/she is expressly authorized to do so in writing, and such authorization or power of attorney is submitted to the recipient or the person represented by the recipient, or the existence of such authorization is known to the recipient of the person represented by the recipient.
______________________________________________
R-help at 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.


More information about the R-help mailing list