Hi All,
I want to ask if there is a transpose function for data frame like the
procedure of transpose in SAS? Because I want to partially transpose a
data frame which contains 5 columns (siteid, date, time, obs, mod), what
I want to do is to put time as the column variables along with siteid,
and date, and put obs and mod in the row names. specifically to
transpose a data frame:
siteid date time obs mod
A 7/8 01 2 5
A 7/8 02 3 8
A 7/8 03 5 8
A 7/9 01 3 6
A 7/9 02 5 8
A 7/9 03 6 7
......
B 7/8 01 4 7
B 7/8 02 7 19
B 7/8 03 4 9
......
To
siteid date name 01 02 03 ....
A 7/8 obs 2 3 5
A 7/8 mod 5 8 8
A 7/9 obs 3 5 6
A 7/9 mod 6 8 7
.......
B 7/8 obs 4 7 4
B 7/8 mod 7 19 9
........
Thank you very much!
Dave
On Mon, 29 Sep 2003 Kang.Daiwen at epamail.epa.gov wrote:> > Hi All, > > I want to ask if there is a transpose function for data frame like the > procedure of transpose in SAS? Because I want to partially transpose a > data frame which contains 5 columns (siteid, date, time, obs, mod), what > I want to do is to put time as the column variables along with siteid, > and date, and put obs and mod in the row names. specifically toI think reshape() does what you want -thomas> transpose a data frame: > > siteid date time obs mod > A 7/8 01 2 5 > A 7/8 02 3 8 > A 7/8 03 5 8 > A 7/9 01 3 6 > A 7/9 02 5 8 > A 7/9 03 6 7 > ...... > B 7/8 01 4 7 > B 7/8 02 7 19 > B 7/8 03 4 9 > ...... > > To > > siteid date name 01 02 03 .... > A 7/8 obs 2 3 5 > A 7/8 mod 5 8 8 > A 7/9 obs 3 5 6 > A 7/9 mod 6 8 7 > ....... > B 7/8 obs 4 7 4 > B 7/8 mod 7 19 9 > ........ > > > Thank you very much! > > Dave > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://www.stat.math.ethz.ch/mailman/listinfo/r-help >Thomas Lumley Assoc. Professor, Biostatistics tlumley at u.washington.edu University of Washington, Seattle
Dave - I'm not sure whether there is already a function which does exactly what you want, because this is kind of a special case. The functions I wold look at are: "by", "aggregate", "tapply", "mapply", and, in the package "nlme" one I didn't know about before called "gapply". But, in your case, the part that makes it doable is that all of the columns in that part of the dataframe which you want to transpose are numeric. (At least, it looks that way from your email.) And you have the same number of time points in every site x date. So you could extract just the three columns "time", "obs", "mod" as a numeric matrix, cast it to an array, use aperm(), then cast it back to a matrix and then back to a dataframe. The basic tools for this are: "data.matrix", "array", "aperm", "matrix" and "data.frame". I've got to let you work out the details yourself. The key when planning this is to remember that multi-dimensional arrays in R use Fortran storage order: the first index varies fastest. Some one else may be able to come up with a much more direct solution. HTH - tom blackwell - u michigan medical school - ann arbor - On Mon, 29 Sep 2003 Kang.Daiwen at epamail.epa.gov wrote:> I want to ask if there is a transpose function for data frame like the > procedure of transpose in SAS? Because I want to partially transpose a > data frame which contains 5 columns (siteid, date, time, obs, mod), what > I want to do is to put time as the column variables along with siteid, > and date, and put obs and mod in the row names. specifically to > transpose a data frame: > > siteid date time obs mod > A 7/8 01 2 5 > A 7/8 02 3 8 > A 7/8 03 5 8 > A 7/9 01 3 6 > A 7/9 02 5 8 > A 7/9 03 6 7 > ...... > B 7/8 01 4 7 > B 7/8 02 7 19 > B 7/8 03 4 9 > ...... > To > > siteid date name 01 02 03 .... > A 7/8 obs 2 3 5 > A 7/8 mod 5 8 8 > A 7/9 obs 3 5 6 > A 7/9 mod 6 8 7 > ....... > B 7/8 obs 4 7 4 > B 7/8 mod 7 19 9 > ........ > > Thank you very much! > > Dave
Assuming that the data frame is called m, you can - split the data frame by the
values of siteid and date- apply a function to create the new data frame for
each such group and - use rbind to put it back together like this: fn <-
function(x) { y <- t(x[,4:5]) data.frame( siteid=x[1,1], date=x[1,2],
name=colnames(x)[4:5], x01=y[,1], x02=y[,2], x03=y[,3] )}m <- do.call(
"rbind", lapply(split(m,list(m$siteid,m$date)),fn) )If the order
matters then add this line: m <- m[ order(m$siteid,m$date), ]--- On Mon
09/29, < Kang.Daiwen@epamail.epa.gov > wrote:From: [mailto:
Kang.Daiwen@epamail.epa.gov]To: r-help@stat.math.ethz.chDate: Mon, 29 Sep 2003
13:15:36 -0400Subject: [R] Data frame transposeHi All,I want to ask if there is
a transpose function for data frame like theprocedure of transpose in SAS?
Because I want to partially transpose adata frame which contains 5 columns
(siteid, date, time, obs, mod), whatI want to do is to put time as the column
variables along with siteid,and date, and put obs and mod in the row names.
specifically totranspose a data frame:siteid date time obs modA 7/8 01 2 5A 7/8
02 3 8A 7/8 03 5 8A 7/9 01 3 6A 7/9 02 5 8A 7/9 03 6 7......B 7/8 01 4 7B 7/8 02
7 19B 7/8 03 4 9......Tositeid date name 01 02 03 ....A 7/8 obs 2 3 5A 7/8 mod 5
8 8A 7/9 obs 3 5 6A 7/9 mod 6 8 7.......B 7/8 obs 4 7 4B 7/8 mod 7 19
9........Thank you very
much!Dave______________________________________________R-help@stat.math.ethz.ch
mailing listhttps://www.stat.math.ethz.ch/mailman/listinfo/r-help
_______________________________________________
No banners. No pop-ups. No kidding.
Introducing My Way - http://www.myway.com
[[alternative HTML version deleted]]
Sorry about the formatting in that previous email. Here it is
again, hopefully with the correct formatting this time.
Assuming that the data frame is called m, you can
- split the data frame by the values of siteid and date
- apply a function to create the new data frame for each such group and
- use rbind to put it back together like this:
fn <- function(x) {
y <- t(x[,4:5])
data.frame( siteid=x[1,1], date=x[1,2], name=colnames(x)[4:5], x01=y[,1],
x02=y[,2], x03=y[,3] )
}
m <- do.call( "rbind", lapply(split(m,list(m$siteid,m$date)),fn) )
If order matters, add this:
m <- m[order(m$siteid,m$date),]
--- On Mon 09/29, < Kang.Daiwen at epamail.epa.gov > wrote:
From: [mailto: Kang.Daiwen at epamail.epa.gov]
To: r-help at stat.math.ethz.ch
Date: Mon, 29 Sep 2003 13:15:36 -0400
Subject: [R] Data frame transpose
<br><br><br><br>Hi All,<br><br>I want to ask
if there is a transpose function for data frame like the<br>procedure of
transpose in SAS? Because I want to partially transpose a<br>data frame
which contains 5 columns (siteid, date, time, obs, mod), what<br>I want to
do is to put time as the column variables along with siteid,<br>and date,
and put obs and mod in the row names. specifically to<br>transpose a data
frame:<br><br>siteid date time obs mod<br> A
7/8 01 2 5<br> A 7/8 02 3
8<br> A 7/8 03 5 8<br> A 7/9
01 3 6<br> A 7/9 02 5
8<br> A 7/9 03 6 7<br>
......<br> B 7/8 01 4 7<br> B
7/8 02 7 19<br> B 7/8 03 4
9<br>
......<br><br>To<br><br>siteid date name 01
02 03 ....<br>A 7/8 obs 2 3
5<br>A 7/8 mod 5 8 8<br>A
7/9 obs 3 5 6<br>A 7/9 mod
6 8 7<br> .......<br>B 7/8
obs 4 7 4<br>B 7/8 mod 7
19 9<br> ........<br><br><br>Thank
you very
much!<br><br>Dave<br><br>______________________________________________<br>R-help
at stat.math.ethz.ch mailing
list<br>https://www.stat.math.ethz.ch/mailman/listinfo/r-help<br>
_______________________________________________
No banners. No pop-ups. No kidding.
Introducing My Way - http://www.myway.com