Can somebody tell me how to do the equivalent of a pivot table in R ?
For example, if I have :
var1 var2 var3
a x 10
b y 20
a z 10
b z 20
a z 10
b z 20
I could have :
x y z
a 1 0 2
b 0 1 2
where entries in the table are counts of var3.
x y z
a 10 0 20
b 0 20 40
where entries are sums of var3.
I would expect it to be tapply(), but I can't see how it would be done.
Any suggestions please.
--
View this message in context:
http://www.nabble.com/Equivalent-of-Excel-pivot-tables-in-R-tp16906289p16906289.html
Sent from the R help mailing list archive at Nabble.com.
Hi, Try this: x="var1 var2 var3 a x 10 b y 20 a z 10 b z 20 a z 10 b z 20" yourdata=read.table(textConnection(x),header=TRUE) attach(yourdata) res=tapply(var3,yourdata[,-3],sum) # With tapply! res[is.na(res)]<-0 res var2 var1 x y z a 10 0 20 b 0 20 40 See also ?tapply. HTH, Jorge On Fri, Apr 25, 2008 at 5:54 PM, ppaarrkk <simon_ecc@yahoo.co.uk> wrote:> > Can somebody tell me how to do the equivalent of a pivot table in R ? > > > For example, if I have : > > var1 var2 var3 > a x 10 > b y 20 > a z 10 > b z 20 > a z 10 > b z 20 > > I could have : > > x y z > a 1 0 2 > b 0 1 2 > > where entries in the table are counts of var3. > > x y z > a 10 0 20 > b 0 20 40 > > where entries are sums of var3. > > > > I would expect it to be tapply(), but I can't see how it would be done. > > > Any suggestions please. > > > -- > View this message in context: > http://www.nabble.com/Equivalent-of-Excel-pivot-tables-in-R-tp16906289p16906289.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >[[alternative HTML version deleted]]
See Hadley's reshape package http://had.co.nz/reshape/ and on CRAN. On Fri, Apr 25, 2008 at 2:54 PM, ppaarrkk <simon_ecc@yahoo.co.uk> wrote:> > Can somebody tell me how to do the equivalent of a pivot table in R ? > > > For example, if I have : > > var1 var2 var3 > a x 10 > b y 20 > a z 10 > b z 20 > a z 10 > b z 20 > > I could have : > > x y z > a 1 0 2 > b 0 1 2 > > where entries in the table are counts of var3. > > x y z > a 10 0 20 > b 0 20 40 > > where entries are sums of var3. > > > > I would expect it to be tapply(), but I can't see how it would be done. > > > Any suggestions please. > > > -- > View this message in context: > http://www.nabble.com/Equivalent-of-Excel-pivot-tables-in-R-tp16906289p16906289.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >-- HTH, Jim Porzak Responsys, Inc. San Francisco, CA http://www.linkedin.com/in/jimporzak [[alternative HTML version deleted]]
Try: with(x, tapply(var3, list(var1, var2), length)) with(x, tapply(var3, list(var1, var2), sum)) Or with xtabs xtabs(as.logical(var3) ~ var1 + var2, data = x) xtabs(var3 ~ var1 + var2, data = x) On 4/25/08, ppaarrkk <simon_ecc@yahoo.co.uk> wrote:> > > Can somebody tell me how to do the equivalent of a pivot table in R ? > > > For example, if I have : > > var1 var2 var3 > a x 10 > b y 20 > a z 10 > b z 20 > a z 10 > b z 20 > > I could have : > > x y z > a 1 0 2 > b 0 1 2 > > where entries in the table are counts of var3. > > x y z > a 10 0 20 > b 0 20 40 > > where entries are sums of var3. > > > > I would expect it to be tapply(), but I can't see how it would be done. > > > Any suggestions please. > > > -- > View this message in context: > http://www.nabble.com/Equivalent-of-Excel-pivot-tables-in-R-tp16906289p16906289.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >-- Henrique Dallazuanna Curitiba-Paraná-Brasil 25° 25' 40" S 49° 16' 22" O [[alternative HTML version deleted]]
See also the reshape package.
Cheers,
Simon.
Simon Blomberg, BSc (Hons), PhD, MAppStat.
Lecturer and Consultant Statistician
Faculty of Biological and Chemical Sciences
The University of Queensland
St. Lucia Queensland 4072
Australia
T: +61 7 3365 2506
email: S.Blomberg1_at_uq.edu.au
Policies:
1. I will NOT analyse your data for you.
2. Your deadline is your problem.
The combination of some data and an aching desire for
an answer does not ensure that a reasonable answer can
be extracted from a given body of data. - John Tukey.
-----Original Message-----
From: r-help-bounces@r-project.org on behalf of ppaarrkk
Sent: Sat 26/04/2008 7:54 AM
To: r-help@r-project.org
Subject: [R] Equivalent of Excel pivot tables in R
Can somebody tell me how to do the equivalent of a pivot table in R ?
For example, if I have :
var1 var2 var3
a x 10
b y 20
a z 10
b z 20
a z 10
b z 20
I could have :
x y z
a 1 0 2
b 0 1 2
where entries in the table are counts of var3.
x y z
a 10 0 20
b 0 20 40
where entries are sums of var3.
I would expect it to be tapply(), but I can't see how it would be done.
Any suggestions please.
--
View this message in context:
http://www.nabble.com/Equivalent-of-Excel-pivot-tables-in-R-tp16906289p16906289.html
Sent from the R help mailing list archive at Nabble.com.
______________________________________________
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.
[[alternative HTML version deleted]]