Hi I have used merge() to merge two data frames, very much like performing a SQL join. Now I want to do a few different SQL-style things and I wondered if there were functions to do it... Is there a "group by" style function? For example if I merge() two data frames and end up with multiple rows for each "id", and want to take the average of the values of a particular column? I know I can probably put something together using merge() and by() and then munging the results together myself, but is there something in R to perform this automatically? The second thing I'd like to do is like a cross-tab query; that is when after a merge() I end up with multiple rows for a particular "id", and want to cross-tab the data so that the multiple values become columns and I end up with one row for each "id" again e.g. ID Val 1 5 1 10 2 15 2 20 Becomes ID Val1 Val2 1 5 10 2 15 20 Thanks in advance! Mick
Hi Michael On 1 Dec 2004 at 11:50, michael watson (IAH-C) wrote:> Hi > > I have used merge() to merge two data frames, very much like > performing a SQL join. Now I want to do a few different SQL-style > things and I wondered if there were functions to do it... > > Is there a "group by" style function? For example if I merge() two > data frames and end up with multiple rows for each "id", and want to > take the average of the values of a particular column? I know I can > probably put something together using merge() and by() and then > munging the results together myself, but is there something in R to > perform this automatically?aggregate, by, *apply but AFAIK only after merge> > The second thing I'd like to do is like a cross-tab query; that is > when after a merge() I end up with multiple rows for a particular > "id", and want to cross-tab the data so that the multiple values > become columns and I end up with one row for each "id" again e.g.reshape in base or reShape in Hmisc is probably what you need Cheers Petr> > ID Val > 1 5 > 1 10 > 2 15 > 2 20 > > Becomes > > ID Val1 Val2 > 1 5 10 > 2 15 20 > > Thanks in advance! > > Mick > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! > http://www.R-project.org/posting-guide.htmlPetr Pikal petr.pikal at precheza.cz
Hi Michael,
regarding your second question you could use the `reshape()' function,
i.e.,
dat <- data.frame(ID=rep(1:2, each=2), Val=seq(5,20,5))
######
dat$time <- unlist(lapply(split(dat$ID, dat$ID), function(x)
1:length(x)), use.names=FALSE)
reshape(dat, direction="wide", idvar="ID",
v.names="Val")
I hope it helps.
Best,
Dimitris
----
Dimitris Rizopoulos
Ph.D. Student
Biostatistical Centre
School of Public Health
Catholic University of Leuven
Address: Kapucijnenvoer 35, Leuven, Belgium
Tel: +32/16/336899
Fax: +32/16/337015
Web: http://www.med.kuleuven.ac.be/biostat
http://www.student.kuleuven.ac.be/~m0390867/dimitris.htm
----- Original Message -----
From: "michael watson (IAH-C)" <michael.watson at bbsrc.ac.uk>
To: <r-help at stat.math.ethz.ch>
Sent: Wednesday, December 01, 2004 12:50 PM
Subject: [R] Data Frame Manipulations
> Hi
>
> I have used merge() to merge two data frames, very much like
> performing
> a SQL join. Now I want to do a few different SQL-style things and I
> wondered if there were functions to do it...
>
> Is there a "group by" style function? For example if I merge()
two
> data
> frames and end up with multiple rows for each "id", and want to
take
> the
> average of the values of a particular column? I know I can probably
> put
> something together using merge() and by() and then munging the
> results
> together myself, but is there something in R to perform this
> automatically?
>
> The second thing I'd like to do is like a cross-tab query; that is
> when
> after a merge() I end up with multiple rows for a particular
"id",
> and
> want to cross-tab the data so that the multiple values become
> columns
> and I end up with one row for each "id" again e.g.
>
> ID Val
> 1 5
> 1 10
> 2 15
> 2 20
>
> Becomes
>
> ID Val1 Val2
> 1 5 10
> 2 15 20
>
> Thanks in advance!
>
> Mick
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>