How to transform Columns to rows in R?

Question

I kind of have the same problem. I have data in this kind of order: ;=column

D1 ;hurs

1  ;0.12

1  ;0.23

1  ;0.34

1  ;0.01

2  ;0.24

2  ;0.67

2  ;0.78

2  ;0.98

and I like to have it like this:

D1; X; X; X; X    
1;0.12; 0.23; 0.34; 0.01; 
2;0.24; 0.67; 0.78; 0.98;

I would like to sort it with respect to D1 and like to reshape it? Does anyone have an idea? I need to do this for 7603 values of D1.

Do you need an output file with that format? Is the list of factors (D1) a sequence? — Emer, Mar 09 '11 at 20:32
Maybe I'm mising something, but why not just transpose the matrix? Then, use order to sort it. I may provide an example if you need. — Manoel Galdino, Mar 10 '11 at 21:03

score 1 · Answer 1 · answered Oct 19 '12 at 06:10

Digging up skeletons not likely to ever be claimed, why not use aggregate()?

dat = read.table(header = TRUE, sep = ";", text = "D1 ;hurs
1  ;0.12
1  ;0.23
1  ;0.34
1  ;0.01
2  ;0.24
2  ;0.67
2  ;0.78
2  ;0.98")
aggregate(hurs ~ D1, dat, c)
#   D1 hurs.1 hurs.2 hurs.3 hurs.4
# 1  1   0.12   0.23   0.34   0.01
# 2  2   0.24   0.67   0.78   0.98

If the lengths of each id in D1 are not the same, you can also use base R reshape() after first creating a "time" variable:

dat2 <- dat[-8, ]
dat2$timeSeq <- ave(dat2$D1, dat2$D1, FUN = seq_along)
reshape(dat2, direction="wide", idvar="D1", timevar="timeSeq")
#   D1 hurs.1 hurs.2 hurs.3 hurs.4
# 1  1   0.12   0.23   0.34   0.01
# 5  2   0.24   0.67   0.78     NA

score 1 · Answer 2 · answered Mar 09 '11 at 20:29

I would look into Hadley's reshape package. It does all sorts of great stuff. The code below will work with your toy example, but there is probably a more elegant way of doing this. Simply, your data already appear to be in the ?melt form, so you can simply ?cast it.

Also, check out these links

http://www.statmethods.net/management/reshape.html

http://had.co.nz/reshape/

library(reshape)

help(package=reshape)
?melt

D1 <- c(1,1,1,1,2,2,2,2)
hurs <- c(.12, .23, .34, .01, .24, .67, .78, .98)
var <- rep(paste("X", 1:4, sep=""), 2)

foo <- data.frame(D1, var, hurs)
foo

cast(foo, D1~var)

score 0 · Answer 3 · answered Dec 08 '12 at 18:35

0

reshape2 is actually better than reshape. Using reshape uses significantly more memory and time than reshape2 (at least for my specific example using something like 9million rows).

answered Dec 08 '12 at 18:35

Dean MacGregor

11,847
9
34
72

Not sure why you're criticizing answers that are nearly 3 months old, but yes it does count as a real answer. The newness of reshape2 makes it less likely that everyone knew about it. – Dean MacGregor Mar 03 '13 at 22:26

score 0 · Answer 4 · edited Dec 06 '13 at 11:25

0

You might check Hadley Wickham's reshape package and its cast() function

http://had.co.nz/reshape/

edited Dec 06 '13 at 11:25

BenMorel

34,448
50
182
322

answered Mar 09 '11 at 20:33

dnfehren

801
1
9
12

score 0 · Answer 5 · answered Mar 10 '11 at 09:05

I have assumed that there are unequal number of hurs per D1 (7603 values)

txt = 'D1 ;hurs
 1 ;0.12
 1 ;0.23
 1 ;0.34
 1 ;0.01
 2 ;0.24
 2 ;0.67
 2 ;0.78
 2 ;0.98'

dat <- read.table(textConnection(txt),header=T,sep=";")
dat$Lp <- 1:nrow(dat)
dat <- dat[order(dat$D1,dat$Lp),]
out <- split(dat$hurs,dat$D1)
out <- sapply(names(out),function(x) paste(paste(c(x,out[[x]]),collapse=";"),";",sep="",collapse=""))

How to transform Columns to rows in R?

5 Answers5

Linked