Hello, I'm struggling with an elementary problem with R. I have a simple data frame such as this one giving the number of accidents subdivided by sex, age and region. sex age region no_of_accidents F young north 10 F young south 12 F old north 5 F old south 7 M young north 24 M young south 30 M old north 12 M old south 17 and I would like to build a pivot table, e.g. obtaining the sum of the number of accidents for each sex: sex age region no_of_accidents F (any) (any) 34 M (any) (any) 83 but I can't seem to obtain this result simply in R, except by embarking in complicated "for" loops. I have checked the documentation on such functions as "table()" and the documentation on "An introduction to R" but couldn't solve the problem. Could you please help me with this? Cheers Pete PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS E-MAIL For Aon’s standard conditions associated with this e-mail please visit http://www.aon.com/uk/en/email-footer/aon-limited.jsp Aon Limited Registered Office: 8 Devonshire Square, London EC2M 4PL Registered in London No. 210725 . VAT Registration No. 480 8401 48 Aon Limited is authorised and regulated by the Financial Services Authority in respect of insurance mediation activities only. [[alternative HTML version deleted]]
Hi Pietro, Depending on the actual structure you want in the output, you can use some of the functions in the apply family, particularly tapply, aggregate, or by. Something like : R> tapply( d[["no_of_accidents" ]], d[[ "sex" ]], sum ) F M 34 83 R> aggregate( d["no_of_accidents" ], d[ "sex" ], sum ) sex no_of_accidents 1 F 34 2 M 83 R> by( d[["no_of_accidents" ]], d[[ "sex" ]], sum ) INDICES: F [1] 34 ------------------------------------------------------------ INDICES: M [1] 83 Cheers, Romain -- Mango Solutions data analysis that delivers R for finance training course :: 3 days :: London (27-29/02/2008) http://www.mango-solutions.com/services/rtraining/r_finance.html pietro.parodi at aon.co.uk wrote:> Hello, > > I'm struggling with an elementary problem with R. I have a simple data > frame such as this one giving the number of accidents subdivided by sex, > age and region. > > sex age region no_of_accidents > > F young north 10 > F young south 12 > F old north 5 > F old south 7 > M young north 24 > M young south 30 > M old north 12 > M old south 17 > > and I would like to build a pivot table, e.g. obtaining the sum of the > number of accidents for each sex: > > sex age region no_of_accidents > > F (any) (any) 34 > M (any) (any) 83 > > but I can't seem to obtain this result simply in R, except by embarking in > complicated "for" loops. > > I have checked the documentation on such functions as "table()" and the > documentation on "An introduction to R" but couldn't solve the problem. > Could you please help me with this? > > Cheers > > Pete > > > PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS E-MAIL > > For Aon?s standard conditions associated with this e-mail please visit http://www.aon.com/uk/en/email-footer/aon-limited.jsp > Aon Limited > Registered Office: 8 Devonshire Square, London EC2M 4PL > Registered in London No. 210725 . VAT Registration No. 480 8401 48 > > Aon Limited is authorised and regulated by the Financial Services Authority in respect of insurance mediation activities only. > > [[alternative HTML version deleted]] > >
A = read.table("clipboard", header=TRUE)> A
sex age region no_of_accidents
1 F young north 10
2 F young south 12
3 F old north 5
4 F old south 7
5 M young north 24
6 M young south 30
7 M old north 12
8 M old south 17
sum(A$no_of_accidents[which( A$sex=="F")])
should give you the required sum.
You have to remember that sex, age and region are Factors with their
respective levels and no_of_accidents is a numeric vector.
Do str( A) to see their type.
Hope that helps.
Mama
-----
Mama Attiglah, PhD
Advanced Research Center
Quantitative Research Analyst
State Street Bank
+44(0)20 7698 6290 (Direct Line)
+44 (0)207 004 2968 (Direct Fax)
Please visit our Web site at
www.ssga.com
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On Behalf Of pietro.parodi at aon.co.uk
Sent: 29 January 2008 12:05
To: r-help at r-project.org
Subject: [R] pivot table in R
Hello,
I'm struggling with an elementary problem with R. I have a simple data
frame such as this one giving the number of accidents subdivided by sex,
age and region.
sex age region no_of_accidents
F young north 10
F young south 12
F old north 5
F old south 7
M young north 24
M young south 30
M old north 12
M old south 17
and I would like to build a pivot table, e.g. obtaining the sum of the
number of accidents for each sex:
sex age region no_of_accidents
F (any) (any) 34
M (any) (any) 83
but I can't seem to obtain this result simply in R, except by embarking
in
complicated "for" loops.
I have checked the documentation on such functions as "table()" and
the
documentation on "An introduction to R" but couldn't solve the
problem.
Could you please help me with this?
Cheers
Pete
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS E-MAIL
For Aon's standard conditions associated with this e-mail please visit
http://www.aon.com/uk/en/email-footer/aon-limited.jsp
Aon Limited
Registered Office: 8 Devonshire Square, London EC2M 4PL
Registered in London No. 210725 . VAT Registration No. 480 8401 48
Aon Limited is authorised and regulated by the Financial Services
Authority in respect of insurance mediation activities only.
[[alternative HTML version deleted]]
pietro.parodi at aon.co.uk wrote in news:OF9469393D.467B36A7-ON802573DF.00412BB7-802573DF.0042657E at aon.co.u k: Three or four solutions have already been offered. Here is (yet) another:> Atxt <- "+ sex age region no_of_accidents + 1 F young north 10 + 2 F young south 12 + 3 F old north 5 + 4 F old south 7 + 5 M young north 24 + 6 M young south 30 + 7 M old north 12 + 8 M old south 17"> > A <- read.table(textConnection(Atxt), header=TRUE)> Asex <- xtabs(no_of_accidents ~ sex, data=A) > Asexsex F M 34 83 xtabs() returns an object of class = contingency table. This may have added advantage if you are using statistical function which expect such an object. Using a formula based function also lets you quickly expand the analysis.> Asexreg <- xtabs(no_of_accidents ~ sex+region, data=A) > Asexregregion sex north south F 15 19 M 36 47 -- David Winsemius> Hello, > > I'm struggling with an elementary problem with R. I have a simple > data frame such as this one giving the number of accidents > subdivided by sex, age and region. > > sex age region no_of_accidents > > F young north 10 > F young south 12 > F old north 5 > F old south 7 > M young north 24 > M young south 30 > M old north 12 > M old south 17 > > and I would like to build a pivot table, e.g. obtaining the sum of > the number of accidents for each sex: > > sex age region no_of_accidents > > F (any) (any) 34 > M (any) (any) 83
> I'm struggling with an elementary problem with R. I have a simple data > frame such as this one giving the number of accidents subdivided by sex, > age and region.If you're trying to do pivot tables in R, I'd recommend looking at the reshape package, which was designed specifically to tackle these types of problems. http://had.co.nz/reshape provides some links to get started Hadley -- http://had.co.nz/
Have a look at the reshape package. With your data as
data.frame xx :
library(reshape)
dd <- melt(xx, id=c("sex", "age",
"region"),measured=c(no_of_accidents)); dd
cast(dd, sex~variable, sum)
--- pietro.parodi at aon.co.uk wrote:
> Hello,
>
> I'm struggling with an elementary problem with R. I
> have a simple data
> frame such as this one giving the number of
> accidents subdivided by sex,
> age and region.
>
> sex age region no_of_accidents
>
> F young north 10
> F young south 12
> F old north 5
> F old south 7
> M young north 24
> M young south 30
> M old north 12
> M old south 17
>
> and I would like to build a pivot table, e.g.
> obtaining the sum of the
> number of accidents for each sex:
>
> sex age region no_of_accidents
>
> F (any) (any) 34
> M (any) (any) 83
>
> but I can't seem to obtain this result simply in R,
> except by embarking in
> complicated "for" loops.
>
> I have checked the documentation on such functions
> as "table()" and the
> documentation on "An introduction to R" but couldn't
> solve the problem.
> Could you please help me with this?
>
> Cheers
>
> Pete
>
>
> PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS
> E-MAIL
>
> For Aon?s standard conditions associated with this
> e-mail please visit
>
http://www.aon.com/uk/en/email-footer/aon-limited.jsp> Aon Limited
> Registered Office: 8 Devonshire Square, London EC2M
> 4PL
> Registered in London No. 210725 . VAT Registration
> No. 480 8401 48
>
> Aon Limited is authorised and regulated by the
> Financial Services Authority in respect of insurance
> mediation activities only.
>
> [[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.
>
Apparently Analagous Threads
- R-code embedded in VBE -- Type mismatch errors
- Converting from density to cumulative distribution
- Counting number of common elements between the rows of two different matrices
- Reclassifying values within a vector to several other values
- Vectorizing sample()