want to split columns

[neal]

i'm in a situation similar to that in section 9.2 of the ggplot
book.. i have

data.frame(path=1:10,p.1=rnorm(10),q.1=rnorm(10),r.1=rnorm(10),
p.2=rnorm(10),q.2=rnorm(10),r.2=rnorm(10),
p.3=rnorm(10),q.3=rnorm(10),r.3=rnorm(10),
p.4=rnorm(10),q.4=rnorm(10),r.4=rnorm(10),
p.5=rnorm(10),q.5=rnorm(10),r.5=rnorm(10))

and i want this to be long *and* split at the decimal point:

path variable value
1 p .3
...
1 p .2
1 q .4
...
1 q .5
1 r .6
...
1 r .6
2 p .7
...

looks like something that reshape/ddply/etc can do, but i
can't get it all together

thanks
-neal

[Heike Hofmann]

neal,

this should do it:

df <- data.frame(path=1:10,p.1=rnorm(10),q.1=rnorm(10),r.1=rnorm(10),

p.2=rnorm(10),q.2=rnorm(10),r.2=rnorm(10),

p.3=rnorm(10),q.3=rnorm(10),r.3=rnorm(10),

p.4=rnorm(10),q.4=rnorm(10),r.4=rnorm(10),

p.5=rnorm(10),q.5=rnorm(10),r.5=rnorm(10))

require(reshape)

dfm<- melt(df, id.var="path")

dfm$value <- as.numeric(gsub("[pqr]","",as.character(dfm$variable)))

dfm$variable  <- factor(substr(as.character(dfm$variable),1,1))

Heike

[neal]

hi Heiki

yes i can see that is part of what i want, but i want to
retain the original rnorm values... so for a smaller example

df<-data.frame(path=1:5,p.1=rnorm(5),q.1=rnorm(5),p.2=rnorm(5),q.
2=rnorm(5))

path p.1 q.1 p.2 q.2
1 1 -1.1120626 -1.3318245 0.04100873 -0.1307282
2 2 0.5576470 -2.1910462 -0.08200001 -1.3637397
3 3 0.3968100 0.7447393 0.43068756 -0.5225671
4 4 -0.9374627 1.3232445 -0.09596991 0.3902141
5 5 -1.5740812 0.3760718 0.64040733 -0.7931985

in this case i want

path time variable value
1 1 p -1.1120626
2 1 p .5576470
3 1 p 0.3968100
4 1 p -0.9374627
5 1 p -1.5740812
1 1 q -1.3318245
2 1 q -2.1910462
3 1 q 0.7447393
4 1 q 1.3232445
5 1 q 0.3760718
1 2 p 0.04100873
2 2 p -0.08200001
3 2 p 0.43068756
4 2 p -0.09596991
5 2 p 0.64040733
1 2 q -0.1307282
2 2 q -1.3637397
3 2 q -0.5225671
4 2 q 0.3902141
5 2 q -0.7931985

i think the code you sent is quite close, but it seems to clobber
the actual rnorms, but i think a small modification is all
that's needed...

thanks
-neal

[Dennis Murphy]

Hi:

You only need to make some cosmetic changes to Dr. Hoffmann's code:

# Using your originally presented data:

dfm<- melt(df, id.var="path")

dfm$time <- as.numeric(substr(as.character(dfm$variable),3,3))
dfm$variable  <- factor(substr(as.character(dfm$variable),1,1))
> str(dfm)
'data.frame':   150 obs. of  4 variables:
 $ path    : int  1 2 3 4 5 6 7 8 9 10 ...
 $ time    : num  1 1 1 1 1 1 1 1 1 1 ...
 $ variable: Factor w/ 3 levels "p","q","r": 1 1 1 1 1 1 1 1 1 1 ...
 $ value   : num  1.4777 -0.0101 1.2084 -0.3547 -0.2684 ...

head(dfm)
  path variable       value time
1    1        p  1.47771682    1
2    2        p -0.01008574    1
3    3        p  1.20840736    1
...

# To rearrange the ordering,

> dfm <- dfm[, c(1, 4, 2, 3)]
> head(dfm)
  path time variable       value
1    1    1        p  1.47771682
2    2    1        p -0.01008574
3    3    1        p  1.20840736
...
dim(dfm)
[1] 150   4

HTH,
Dennis

--
You received this message because you are subscribed to the ggplot2 mailing list.
Please provide a reproducible example: http://gist.github.com/270442

To post: email ggp...@googlegroups.com
To unsubscribe: email ggplot2+u...@googlegroups.com
More options: http://groups.google.com/group/ggplot2

[neal]

Hi Everyone

Working with this on my actual data, I've run into a problem
that I didn't think of when I first posted the question;

df<-data.frame(path=1:5,a.1=rnorm(5), a.2=rnorm(5),bbnum.25=rnorm(5),
dd.4.50=rnorm(5), eee.31.5=rnorm(5))

path a.1 a.2 bbnum.25 dd.4.50 eee.31.5
1 1 0.08595333 0.7309579 -0.4583091 -0.85701834 1.021900390
2 2 -0.54099131 -0.2382299 0.8426603 0.77227616 -0.259230826
3 3 0.45699980 -0.1509856 0.4171901 -0.84711629 -0.007809302
4 4 -0.65007115 -1.2413905 0.9613126 -0.14606821 -0.925703608
5 5 -1.93574364 -0.2711861 0.7053587 -0.07840072 -0.882576264

my problem is how to make this long. i can't use
substr because the number of characters varies. furthermore
for some, but not all, of the columns there's 2 times. what
i'd like is:

path t1 t2 variable value
1 1 NA a 0.08595333
2 1 NA a -0.54099131
3 1 NA a 0.45699980
4 1 NA a -0.65007115
5 1 NA a -1.93574364
1 2 NA a 0.7309579
2 2 NA a -0.2382299
3 2 NA a -0.1509856
4 2 NA a -1.2413905
5 2 NA a -0.2711861
1 25 NA bbnum -0.4583091
2 25 NA bbnum 0.8426603
3 25 NA bbnum 0.4171901
4 25 NA bbnum 0.9613126
5 25 NA bbnum 0.7053587
1 4 50 dd -0.85701834
2 4 50 dd 0.77227616
3 4 50 dd -0.84711629
4 4 50 dd -0.14606821
5 4 50 dd -0.07840072
1 31 5 eee 1.021900390
2 31 5 eee -0.259230826
3 31 5 eee -0.007809302
4 31 5 eee -0.925703608
5 31 5 eee -0.882576264

im pretty sure there's a solution involving strsplit, but
i've been working on it for a day and a half and i can't get
it... strsplit is giving a list which i can't figure out
how to make into the data.frame.

any help would be greatly appreciated, and thanks so much
for the helpful replies so far!

best
-neal

> > To unsubscribe: email ggplot2+u...@googlegroups.com<ggplot2%2Bunsu...@googlegroups.com>
> > More options:http://groups.google.com/group/ggplot2

[Brandon Hurr]

Would the easiest solution be to standardize the length of your strings? 

Just add 0's? 

df<-data.frame(path.0.0=1:5,a.1.0=rnorm(5), a.2.0=rnorm(5),bbnum.25.0=rnorm(5), dd.4.50=rnorm(5), eee.31.5=rnorm(5))

I'm sure your dataset is ginormous though... 

Brandon

More options: http://groups.google.com/group/ggplot2

[ONKELINX, Thierry]

First use melt() to convert your dataset to long format. Then use strsplit() to split the variable into more than one column.

----------------------------------------------------------------------------
ir. Thierry Onkelinx
Instituut voor natuur- en bosonderzoek
team Biometrie & Kwaliteitszorg
Gaverstraat 4
9500 Geraardsbergen
Belgium

Research Institute for Nature and Forest
team Biometrics & Quality Assurance
Gaverstraat 4
9500 Geraardsbergen
Belgium

tel. + 32 54/436 185
Thierry....@inbo.be
www.inbo.be

To call in the statistician after the experiment is done may be no more than asking him to perform a post-mortem examination: he may be able to say what the experiment died of.
~ Sir Ronald Aylmer Fisher

The plural of anecdote is not data.
~ Roger Brinner

The combination of some data and an aching desire for an answer does not ensure that a reasonable answer can be extracted from a given body of data.
~ John Tukey

> -----Oorspronkelijk bericht-----
> Van: ggp...@googlegroups.com
> [mailto:ggp...@googlegroups.com] Namens neal
> Verzonden: dinsdag 21 september 2010 16:52
> Aan: ggplot2
> Onderwerp: Re: want to split columns

> ggplot2+u...@googlegroups.com<ggplot2%2Bunsubscribe@googlegro
> > > ups.com> More options:http://groups.google.com/group/ggplot2

>
> --
> You received this message because you are subscribed to the
> ggplot2 mailing list.
> Please provide a reproducible example: http://gist.github.com/270442
>
> To post: email ggp...@googlegroups.com
> To unsubscribe: email ggplot2+u...@googlegroups.com

> More options: http://groups.google.com/group/ggplot2
>

Druk dit bericht a.u.b. niet onnodig af.
Please do not print this message unnecessarily.

Dit bericht en eventuele bijlagen geven enkel de visie van de schrijver weer
en binden het INBO onder geen enkel beding, zolang dit bericht niet bevestigd is
door een geldig ondertekend document. The views expressed in this message
and any annex are purely those of the writer and may not be regarded as stating
an official position of INBO, as long as the message is not confirmed by a duly
signed document.

[neal]

yes i think "add 0's" will take about as much work as what im asking
about.

neal

On Sep 21, 11:06 am, Brandon Hurr <bhiv...@gmail.com> wrote:
> Would the easiest solution be to standardize the length of your strings?
>
> Just add 0's?
>
> df<-data.frame(path.0.0=1:5,a.1.0=rnorm(5),
> a.2.0=rnorm(5),bbnum.25.0=rnorm(5), dd.4.50=rnorm(5), eee.31.5=rnorm(5))
>
> I'm sure your dataset is ginormous though...
>
> Brandon
>

> > <ggplot2%2Bunsu...@googlegroups.com<ggplot2%252Buns...@googlegroups.com>

[Brandon Hurr]

Looks like melt() does it for you... 

> melt(df)

Using  as id variables

     variable       value

1    path.0.0  1.00000000

2    path.0.0  2.00000000

3    path.0.0  3.00000000

4    path.0.0  4.00000000

5    path.0.0  5.00000000

6       a.1.0 -1.03132109

7       a.1.0 -0.07508932

8       a.1.0  0.82858547

9       a.1.0 -1.03598466

10      a.1.0 -0.14715611

11      a.2.0 -0.28145388

12      a.2.0 -1.37368567

More options: http://groups.google.com/group/ggplot2

[Brandon Hurr]

I should add, "As Thierry suggested..."

[Andrie de Vries]

Neal

The following code should do what you want. It uses melt and
strsplit:

library(reshape2)
library(plyr)

df <- data.frame(path=1:5,
a.1=rnorm(5),
a.2=rnorm(5),
bbnum.25=rnorm(5),
dd.4.50=rnorm(5),
eee.31.5=rnorm(5))


split_at_period <- function(x){
tmp <- strsplit(as.character(x), "\\.")[[1]]
data.frame(t1=tmp[1],
t2=tmp[2],
t3=tmp[3])
}


# melt df
mdf <- melt(df, id.vars="path")

# use ply
pdf <- data.frame(path=mdf$path,
ldply(mdf$variable, split_at_period),
value=mdf$value)
pdf

[Ista Zahn]

Hi All,
I haven't followed this thread closely, but thought I would point out
that the reshape2 package has a colsplit function. colsplit() works
like this:

my.df <- data.frame(path=1:5,

a.1=rnorm(5),
a.2=rnorm(5),
bbnum.25=rnorm(5),
dd.4.50=rnorm(5),
eee.31.5=rnorm(5))

mydf.m <- melt(mydf, id.vars="path")

pdf2 <- cbind(mydf.m, colsplit(mydf.m$variable, pattern="\\.",
names=c("t1", "t2", "t3")))

best,
Ista

--
Ista Zahn
Graduate student
University of Rochester
Department of Clinical and Social Psychology
http://yourpsyche.org

[neal]

somehow that's not what im getting

melt(df)
Using as id variables
variable value

1 path 1.00000000
2 path 2.00000000
3 path 3.00000000
4 a.1 -0.05929815
5 a.1 -2.13510854
6 a.1 -0.62923324
...

also i solved the problem in case anyone is interested. as i
mentioned in my post and thierry noted again, the problem is to use
strsplit. here's my solution:

df<-data.frame(path=1:5,a.1=rnorm(5), a.2=rnorm(5),bbnum.25=rnorm(5),
dd.4.50=rnorm(5), eee.31.5=rnorm(5))

dfm<-melt(df,id="path")

f1<-function(vec) { vec[1] }
f2<-function(vec) { vec[2] }
f3<-function(vec) { vec[3] }

dfm$varname<-sapply(spvar,f1)
dfm$t1<-sapply(spvar,f2)
dfm$t2<-sapply(spvar,f3)

dfm
path variable value varname t1 t2
1 1 a.1 -0.05929815 a 1 <NA>
2 2 a.1 -2.13510854 a 1 <NA>
...
9 3 bbnum.25 -0.41941116 bbnum 25 <NA>
10 1 dd.4.50 -0.68719042 dd 4 50
...

thanks alot, i couldn't have done it without your help... (hopefully
i wont be back!!!!)

-neal

On Sep 21, 11:21 am, Brandon Hurr <bhiv...@gmail.com> wrote:
> I should add, "As Thierry suggested..."
>

> On Tue, Sep 21, 2010 at 16:19, Brandon Hurr <bhiv...@gmail.com> wrote:
> > Looks like melt() does it for you...
>
> > > melt(df)
> > Using  as id variables
> >      variable       value
> > 1    path.0.0  1.00000000
> > 2    path.0.0  2.00000000
> > 3    path.0.0  3.00000000
> > 4    path.0.0  4.00000000
> > 5    path.0.0  5.00000000
> > 6       a.1.0 -1.03132109
> > 7       a.1.0 -0.07508932
> > 8       a.1.0  0.82858547
> > 9       a.1.0 -1.03598466
> > 10      a.1.0 -0.14715611
> > 11      a.2.0 -0.28145388
> > 12      a.2.0 -1.37368567
>

> >> > > <ggplot2%2Bunsu...@googlegroups.com<ggplot2%252Buns...@googlegroups.com>
> >> <ggplot2%252Buns...@googlegroups.com<ggplot2%25252Bun...@googlegroups.com>

[neal]

3 different ways to solve the problem! thanks very much Ista and
Andrie!

-neal

> ...
>
> read more »