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. -------------- next part -------------- A non-text attachment was scrubbed... Name: example table.pdf Type: application/pdf Size: 37441 bytes Desc: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20140702/fd5597cb/attachment.pdf>
Hi Hm. Cooperation of R with excel can be probably done by Rexcel package. Without more detailed information how do you want to use R in connection with Excel you probably do not get more precise answer. For summaries use ?aggregate or maybe ?sapply For counting item occurance ?table, ?xtabs or sum(something == "something") can be used. 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 4:33 AM > To: r-help at r-project.org > Subject: [R] Help with tables in R > > 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.________________________________ 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.
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.
On Wed, 2 Jul 2014 09:33:12 PM Barry Lambert wrote:> I am a new convert to R (from SAS). I am a research scientist andmost of> my > use of SAS was in data analysis. Recently, I have been wanting touse R to> create a simple, reliable way to summarize a dataset of studentdemographics> for a university. > The spreadsheet has a row for every student registered at theuniversity for> each term since fall 2010 with the following information about eachstudent> in columns: > Columns are the following: Term, College, Program, Campus,Gender,> Ethnicity, Age. > > I have created summary tables in Excel using if/then type formulasto select> data and count the number of female students in program A atlocation 3,> etc. > > I have written some R code to create some figures that generallymeet my> needs. > I would like to find a way to have R populate some tables with thistype of> information. > > An example of my excel sheets are attached. > > > Any suggestions will be appreciated.Hi Barry, If I understand the above properly, you are calculating counts of female students in different programs and locations. I would do this using the "by" function with "length" as the function called for each group defined by program and location. This will produce an array of counts, and your problem is then to add a table of values to the plot. Here is an example: bldat<-data.frame(sex=sample(c("M","F"),1000,TRUE), year=rep(2010:2013,each=250), program=sample(c("Arts","Math","Science"),1000,TRUE), location=sample(c("New York","London","Paris"),1000,TRUE)) females<-bldat$sex=="F" femsumm<-by(bldat$sex,bldat[,c("program","location")], FUN=length) rownames(femsumm)<-c("Arts","Math","Science") barplot(femsumm,beside=TRUE,ylim=c(0,150)) library(plotrix) addtable2plot(4,120,femsumm,display.rownames=TRUE) Jim
On 02/07/2014, 10:33 PM, Barry Lambert 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. >The "tables" package has formulas that are like PROC TABULATE formulas (except better :-). Your samples would be something like tabular( term ~ campus * college ) tabular( college * program ~ campus * term ) The second one will need some extra work to remove all the empty cells (e.g. College 1 Program 3 will show up, even though it doesn't really exist). See the vignette for a couple of approaches to handling this. The package is mainly aimed at LaTeX output, but it can produce HTML, or just put the numbers in a CSV file for pasting into some spreadsheet for manual formatting. Duncan
I see that others have answered the question much better than I could. However assumng you are getting the data in the layout you want you may also want to look at the xtable package in R as a very handy alternative to stargazer if you want to produce some good-loking tables in some flavour of LaTeX. Odfweave seems to produce good tables in OpenOffice/LibreOffice. John Kane Kingston ON Canada> -----Original Message----- > From: barrylambert at gmail.com > Sent: Wed, 2 Jul 2014 21:33:12 -0500 > To: r-help at r-project.org > Subject: [R] Help with tables in R > > 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.____________________________________________________________ FREE 3D MARINE AQUARIUM SCREENSAVER - Watch dolphins, sharks & orcas on your desktop!