Equivalent of Excel pivot tables in R

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Equivalent of Excel pivot tables in R

ppaarrkk
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.

Reply | Threaded
Open this post in threaded view
|

Re: Equivalent of Excel pivot tables in R

Jorge I Velez
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 <[hidden email]> 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.
>
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Equivalent of Excel pivot tables in R

Jim Porzak
In reply to this post by ppaarrkk
See Hadley's reshape package http://had.co.nz/reshape/
and on CRAN.

On Fri, Apr 25, 2008 at 2:54 PM, ppaarrkk <[hidden email]> 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.
>
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Equivalent of Excel pivot tables in R

Henrique Dallazuanna
In reply to this post by ppaarrkk
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 <[hidden email]> 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.
>
> ______________________________________________
> [hidden email] 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]]


______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Equivalent of Excel pivot tables in R

Jorge I Velez
In reply to this post by Jorge I Velez
Hi,

I forgot the first part (I'm sorry):

table(var1,var2)
 var2
var1 x y z
   a 1 0 2
   b 0 1 2

HTH,

Jorge



On Fri, Apr 25, 2008 at 7:25 PM, Jorge Ivan Velez <[hidden email]>
wrote:

>
> 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 <[hidden email]> 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.
>>
>> ______________________________________________
>> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Equivalent of Excel pivot tables in R

Simon Blomberg-4
In reply to this post by ppaarrkk
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: [hidden email] on behalf of ppaarrkk
Sent: Sat 26/04/2008 7:54 AM
To: [hidden email]
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.

______________________________________________
[hidden email] 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]]

______________________________________________
[hidden email] 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.