R help - Jan 2008 - How to do more advanced cross tabulation in R?

Hi,

I am trying to reproduce some functionalities of Excel pivot table in R,
sadly, I couldn't figure out how to do it. I am wondering if this is even
possible in R. Does anyone know?

Here is an example:

year=rep(2003,16)
quarter=rep(1:4,each=4)
sales=1:16
company=rep(c("a","b","c","d"),4)
df=data.frame(year,quarter,sales,company) #this is the database

I would like to construct a cross tabulation table like this:
  2003           #a row of year(s)
   1   2   3   4 #a row of quarters of each year
a 1   2   3   4
b 5   6   7   8
c 9  10  11 12
c 13 14 15 16
                     #maybe add a row of subtotal for each quarter at the
end.

I tried xtabs, but all I could produce is this, unfortunately:
 xtabs(sales~year+quarter, data=df)
      quarter
year    1  2  3  4
  2003 10 26 42 58

or this:


 xtabs(sales~year+quarter+company, data=df)
, , company = a

      quarter
year    1  2  3  4
  2003  1  5  9 13

, , company = b

      quarter
year    1  2  3  4
  2003  2  6 10 14

, , company = c

      quarter
year    1  2  3  4
  2003  3  7 11 15

, , company = d

      quarter
year    1  2  3  4
  2003  4  8 12 16
Thanks!

-- 
Tom

	[[alternative HTML version deleted]]

I think you are looking for ftable:

xt <- xtabs(sales ~ company + year + quarter, data = DF)
ftable(quarter ~ year + company, xt)

You also might want to look at the reshape package and
the rpad package.  Here is a demo of a pivot table in R using
rpad for the user interface and reshape for the data manipulation
that includes drag and drop:
http://www.rpad.org/Rpad/DataExplorer.Rpad

On Jan 22, 2008 8:19 PM, tom soyer <tom.soyer at gmail.com>
wrote:
> Hi,
>
> I am trying to reproduce some functionalities of Excel pivot table in R,
> sadly, I couldn't figure out how to do it. I am wondering if this is
even
> possible in R. Does anyone know?
>
> Here is an example:
>
> year=rep(2003,16)
> quarter=rep(1:4,each=4)
> sales=1:16
> company=rep(c("a","b","c","d"),4)
> df=data.frame(year,quarter,sales,company) #this is the database
>
> I would like to construct a cross tabulation table like this:
>  2003           #a row of year(s)
>   1   2   3   4 #a row of quarters of each year
> a 1   2   3   4
> b 5   6   7   8
> c 9  10  11 12
> c 13 14 15 16
>                     #maybe add a row of subtotal for each quarter at the
> end.
>
> I tried xtabs, but all I could produce is this, unfortunately:
>  xtabs(sales~year+quarter, data=df)
>      quarter
> year    1  2  3  4
>  2003 10 26 42 58
>
> or this:
>
>
>  xtabs(sales~year+quarter+company, data=df)
> , , company = a
>
>      quarter
> year    1  2  3  4
>  2003  1  5  9 13
>
> , , company = b
>
>      quarter
> year    1  2  3  4
>  2003  2  6 10 14
>
> , , company = c
>
>      quarter
> year    1  2  3  4
>  2003  3  7 11 15
>
> , , company = d
>
>      quarter
> year    1  2  3  4
>  2003  4  8 12 16
> Thanks!
>
> --
> Tom
>
>        [[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.
>

On Tue, 22 Jan 2008, tom soyer wrote:
> Hi,
>
> I am trying to reproduce some functionalities of Excel pivot table in R,
> sadly, I couldn't figure out how to do it. I am wondering if this is
even
> possible in R. Does anyone know?
>
Using 'ftable()', I can match your format, but the cell values differ:
>  ftable(xtabs(sales~year+quarter+company, data=df),row.vars=3)
         year    2003
         quarter    1  2  3  4
company
a                  1  5  9 13
b                  2  6 10 14
c                  3  7 11 15
d                  4  8 12 16
>
Are you sure you got everything right in your example?

HTH,

Chuck
> Here is an example:
>
> year=rep(2003,16)
> quarter=rep(1:4,each=4)
> sales=1:16
> company=rep(c("a","b","c","d"),4)
> df=data.frame(year,quarter,sales,company) #this is the database
>
> I would like to construct a cross tabulation table like this:
>  2003           #a row of year(s)
>   1   2   3   4 #a row of quarters of each year
> a 1   2   3   4
> b 5   6   7   8
> c 9  10  11 12
> c 13 14 15 16
>                     #maybe add a row of subtotal for each quarter at the
> end.
>
> I tried xtabs, but all I could produce is this, unfortunately:
> xtabs(sales~year+quarter, data=df)
>      quarter
> year    1  2  3  4
>  2003 10 26 42 58
>
> or this:
>
>
> xtabs(sales~year+quarter+company, data=df)
> , , company = a
>
>      quarter
> year    1  2  3  4
>  2003  1  5  9 13
>
> , , company = b
>
>      quarter
> year    1  2  3  4
>  2003  2  6 10 14
>
> , , company = c
>
>      quarter
> year    1  2  3  4
>  2003  3  7 11 15
>
> , , company = d
>
>      quarter
> year    1  2  3  4
>  2003  4  8 12 16
> Thanks!
>
> -- 
> Tom
>
> 	[[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.
>
Charles C. Berry                            (858) 534-2098
                                             Dept of Family/Preventive Medicine
E mailto:cberry at tajo.ucsd.edu	            UC San Diego
http://famprevmed.ucsd.edu/faculty/cberry/  La Jolla, San Diego 92093-0901

You could have a look at the reshape package.

Ciao,
domenico

tom soyer wrote:
> Hi,
>
> I am trying to reproduce some functionalities of Excel pivot table in R,
> sadly, I couldn't figure out how to do it. I am wondering if this is
even
> possible in R. Does anyone know?
>
> Here is an example:
>
> year=rep(2003,16)
> quarter=rep(1:4,each=4)
> sales=1:16
> company=rep(c("a","b","c","d"),4)
> df=data.frame(year,quarter,sales,company) #this is the database
>
> I would like to construct a cross tabulation table like this:
>   2003           #a row of year(s)
>    1   2   3   4 #a row of quarters of each year
> a 1   2   3   4
> b 5   6   7   8
> c 9  10  11 12
> c 13 14 15 16
>                      #maybe add a row of subtotal for each quarter at the
> end.
>
> I tried xtabs, but all I could produce is this, unfortunately:
>  xtabs(sales~year+quarter, data=df)
>       quarter
> year    1  2  3  4
>   2003 10 26 42 58
>
> or this:
>
>
>  xtabs(sales~year+quarter+company, data=df)
> , , company = a
>
>       quarter
> year    1  2  3  4
>   2003  1  5  9 13
>
> , , company = b
>
>       quarter
> year    1  2  3  4
>   2003  2  6 10 14
>
> , , company = c
>
>       quarter
> year    1  2  3  4
>   2003  3  7 11 15
>
> , , company = d
>
>       quarter
> year    1  2  3  4
>   2003  4  8 12 16
> Thanks!
>
>