I have the following data: x <- as.factor(c(1,1,1,2,2,2,3,3,3)) y <- as.factor(c(10,10,10,20,20,20,30,30,30)) z <- c(100,100,NA,200,200,200,300,300,300) I could create the cross tab of x and y with Sum of z as its elements using the xtabs function as follows: # X Vs. Y with Sum Z xtabs(z ~ x + y) y x 10 20 30 1 200 0 0 2 0 600 0 3 0 0 900 How do I replace the sum with average and count so that I can get the following outputs?? # X Vs. Y with Average of Z y x 10 20 30 1 100 0 0 2 0 200 0 3 0 0 300 # X Vs. Y with Count Z y x 10 20 30 1 2 0 0 2 0 3 0 3 0 0 3 Would appreciate any help on these? Thank you. Ravi -- View this message in context: http://r.789695.n4.nabble.com/Crosstab-with-Average-and-Count-tp4637180.html Sent from the R help mailing list archive at Nabble.com.
Hi, Try this: dat1<-data.frame(x,y,z) ?xtabs(z~x+y,aggregate(z~x+y,dat1,mean)) ?? y x??? 10? 20? 30 ? 1 100?? 0?? 0 ? 2?? 0 200?? 0 ? 3?? 0?? 0 300 table(dat1$z,dat1$y) ???? ????? 10 20 30 ? 100? 2? 0? 0 ? 200? 0? 3? 0 ? 300? 0? 0? 3 or, table(dat1$x,dat1$z) ?? ??? 100 200 300 ? 1?? 2?? 0?? 0 ? 2?? 0?? 3?? 0 ? 3?? 0?? 0?? 3 A.K. ----- Original Message ----- From: vioravis <vioravis at gmail.com> To: r-help at r-project.org Cc: Sent: Friday, July 20, 2012 6:30 AM Subject: [R] Crosstab with Average and Count I have the following data: x <- as.factor(c(1,1,1,2,2,2,3,3,3)) y <- as.factor(c(10,10,10,20,20,20,30,30,30)) z <- c(100,100,NA,200,200,200,300,300,300) I could create the cross tab of x and y with Sum of z as its elements using the xtabs function as follows: # X Vs. Y with Sum Z xtabs(z ~ x + y) ? y x? ? 10? 20? 30 ? 1 200? 0? 0 ? 2? 0 600? 0 ? 3? 0? 0 900 How do I replace the sum with average and count so that I can get the following outputs?? # X Vs. Y with Average of Z ? y x? ? ? 10? 20? 30 ? 1? ? 100 0? 0 ? 2? ? 0? 200 0 ? 3? ? 0? 0? 300 # X Vs. Y with Count Z ? y x? ? 10? 20? 30 1? ? 2? 0? 0 2? ? 0? 3? 0 3? ? 0? 0? 3 Would appreciate any help on these? Thank you. Ravi -- View this message in context: http://r.789695.n4.nabble.com/Crosstab-with-Average-and-Count-tp4637180.html Sent from the R help mailing list archive at Nabble.com. ______________________________________________ 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.
Not quite what you asked for but would this do?
library(plyr)
x <- as.factor(c(1,1,1,2,2,2,3,3,3))
y <- as.factor(c(10,10,10,20,20,20,30,30,30))
z <- c(100,100,NA,200,200,200,300,300,300)
df1 <- data.frame(x, y, z)
(dsum <- ddply(df1, .(x, y), summarise, sum = sum(z, na.rm = TRUE),
mean = mean(z, na.rm = TRUE), count = length(z)))
John Kane
Kingston ON Canada
> -----Original Message-----
> From: vioravis at gmail.com
> Sent: Fri, 20 Jul 2012 03:30:16 -0700 (PDT)
> To: r-help at r-project.org
> Subject: [R] Crosstab with Average and Count
>
> I have the following data:
>
> x <- as.factor(c(1,1,1,2,2,2,3,3,3))
> y <- as.factor(c(10,10,10,20,20,20,30,30,30))
> z <- c(100,100,NA,200,200,200,300,300,300)
>
> I could create the cross tab of x and y with Sum of z as its elements
> using
> the xtabs function as follows:
>
> # X Vs. Y with Sum Z
>
> xtabs(z ~ x + y)
>
> y
> x 10 20 30
> 1 200 0 0
> 2 0 600 0
> 3 0 0 900
>
> How do I replace the sum with average and count so that I can get the
> following outputs??
>
> # X Vs. Y with Average of Z
> y
> x 10 20 30
> 1 100 0 0
> 2 0 200 0
> 3 0 0 300
>
> # X Vs. Y with Count Z
> y
> x 10 20 30
> 1 2 0 0
> 2 0 3 0
> 3 0 0 3
>
> Would appreciate any help on these? Thank you.
>
> Ravi
>
>
>
>
>
> --
> View this message in context:
>
http://r.789695.n4.nabble.com/Crosstab-with-Average-and-Count-tp4637180.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> 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.
____________________________________________________________
GET FREE SMILEYS FOR YOUR IM & EMAIL - Learn more at
http://www.inbox.com/smileys
Works with AIM?, MSN? Messenger, Yahoo!? Messenger, ICQ?, Google Talk? and most
webmails
On Jul 20, 2012, at 5:30 AM, vioravis wrote:> I have the following data: > > x <- as.factor(c(1,1,1,2,2,2,3,3,3)) > y <- as.factor(c(10,10,10,20,20,20,30,30,30)) > z <- c(100,100,NA,200,200,200,300,300,300) > > I could create the cross tab of x and y with Sum of z as its elements using > the xtabs function as follows: > > # X Vs. Y with Sum Z > > xtabs(z ~ x + y) > > y > x 10 20 30 > 1 200 0 0 > 2 0 600 0 > 3 0 0 900 > > How do I replace the sum with average and count so that I can get the > following outputs?? > > # X Vs. Y with Average of Z > y > x 10 20 30 > 1 100 0 0 > 2 0 200 0 > 3 0 0 300 > > # X Vs. Y with Count Z > y > x 10 20 30 > 1 2 0 0 > 2 0 3 0 > 3 0 0 3 > > Would appreciate any help on these? Thank you. > > RaviYou can use ?tapply, albeit you will get NA's rather than 0's:> tapply(z, list(x, y), mean, na.rm = TRUE)10 20 30 1 100 NA NA 2 NA 200 NA 3 NA NA 300> tapply(z, list(x, y), function(x) sum(!is.na(x)))10 20 30 1 2 NA NA 2 NA 3 NA 3 NA NA 3 Regards, Marc Schwartz