thr3ads.net - R help - [R] Dataset Transformation [Oct 2010]

If this information is useful, please help other people find it:
Share via:

Santosh Srinivas

2010-Oct-11 09:17 UTC

[R] Dataset Transformation

I need to transpose the following input dataset  into an output dataset like
below

 

Input

 


Date

TICKER

Price


11/10/2010

A

0.991642


11/10/2010

B

0.475023


11/10/2010

C

0.218642


11/10/2010

D

0.365135


12/10/2010

A

0.687873


12/10/2010

B

0.47006


12/10/2010

C

0.533542


12/10/2010

D

0.812439


13/10/2010

A

0.210848


13/10/2010

B

0.699799


13/10/2010

C

0.546003


13/10/2010

D

0.152316

 

Output needed 

 


Date

A

B

C

D


11/10/2010

0.991642

0.475023

0.218642

0.365135


12/10/2010

0.687873

0.47006

0.533542

0.812439


13/10/2010

0.210848

0.699799

0.546003

0.152316

 

I tried using the aggregate function but not quite getting the method.

 

 

 


	[[alternative HTML version deleted]]

Santosh Srinivas

2010-Oct-11 13:35 UTC

head link

[R] Dataset Transformation

Repost .. since the previous msg had problems

I need to transpose the following input dataset ?into an output dataset like
below

Input
Date		TICKER		Price
11/10/2010		A		0.991642
11/10/2010		B		0.475023
11/10/2010		C		0.218642
11/10/2010		D		0.365135
12/10/2010		A		0.687873
12/10/2010		B		0.47006
12/10/2010		C		0.533542
12/10/2010		D		0.812439
13/10/2010		A		0.210848
13/10/2010		B		0.699799
13/10/2010		C		0.546003
13/10/2010		D		0.152316

Output needed 

Date	A	B	C	D
11/10/2010	0.991642	0.475023	0.218642	0.365135
12/10/2010	0.687873	0.47006	0.533542	0.812439
13/10/2010	0.210848	0.699799	0.546003	0.152316

I tried using the aggregate function but not quite getting the method.

jim holtman

2010-Oct-11 13:42 UTC

head link

[R] Dataset Transformation

try this:
> x <- read.table(textConnection("Date            TICKER         
Price+ 11/10/2010              A               0.991642
+ 11/10/2010              B               0.475023
+ 11/10/2010              C               0.218642
+ 11/10/2010              D               0.365135
+ 12/10/2010              A               0.687873
+ 12/10/2010              B               0.47006
+ 12/10/2010              C               0.533542
+ 12/10/2010              D               0.812439
+ 13/10/2010              A               0.210848
+ 13/10/2010              B               0.699799
+ 13/10/2010              C               0.546003
+ 13/10/2010              D               0.152316"), header = TRUE,
as.is = TRUE)> closeAllConnections()
> x.m <- melt(x)
Using Date, TICKER as id variables> cast(x.m, Date ~ TICKER)        Date        A        B        C        D
1 11/10/2010 0.991642 0.475023 0.218642 0.365135
2 12/10/2010 0.687873 0.470060 0.533542 0.812439
3 13/10/2010 0.210848 0.699799 0.546003 0.152316>

On Mon, Oct 11, 2010 at 9:35 AM, Santosh Srinivas
<santosh.srinivas at gmail.com> wrote:> Repost .. since the previous msg had problems
>
> I need to transpose the following input dataset ?into an output dataset
like
> below
>
> Input
> Date ? ? ? ? ? ?TICKER ? ? ? ? ?Price
> 11/10/2010 ? ? ? ? ? ? ?A ? ? ? ? ? ? ? 0.991642
> 11/10/2010 ? ? ? ? ? ? ?B ? ? ? ? ? ? ? 0.475023
> 11/10/2010 ? ? ? ? ? ? ?C ? ? ? ? ? ? ? 0.218642
> 11/10/2010 ? ? ? ? ? ? ?D ? ? ? ? ? ? ? 0.365135
> 12/10/2010 ? ? ? ? ? ? ?A ? ? ? ? ? ? ? 0.687873
> 12/10/2010 ? ? ? ? ? ? ?B ? ? ? ? ? ? ? 0.47006
> 12/10/2010 ? ? ? ? ? ? ?C ? ? ? ? ? ? ? 0.533542
> 12/10/2010 ? ? ? ? ? ? ?D ? ? ? ? ? ? ? 0.812439
> 13/10/2010 ? ? ? ? ? ? ?A ? ? ? ? ? ? ? 0.210848
> 13/10/2010 ? ? ? ? ? ? ?B ? ? ? ? ? ? ? 0.699799
> 13/10/2010 ? ? ? ? ? ? ?C ? ? ? ? ? ? ? 0.546003
> 13/10/2010 ? ? ? ? ? ? ?D ? ? ? ? ? ? ? 0.152316
>
> Output needed
>
> Date ? ?A ? ? ? B ? ? ? C ? ? ? D
> 11/10/2010 ? ? ?0.991642 ? ? ? ?0.475023 ? ? ? ?0.218642 ? ? ? ?0.365135
> 12/10/2010 ? ? ?0.687873 ? ? ? ?0.47006 0.533542 ? ? ? ?0.812439
> 13/10/2010 ? ? ?0.210848 ? ? ? ?0.699799 ? ? ? ?0.546003 ? ? ? ?0.152316
>
> I tried using the aggregate function but not quite getting the method.
>
> ______________________________________________
> 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.
>


-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?

Gabor Grothendieck

2010-Oct-11 14:08 UTC

head link

[R] Dataset Transformation

On Mon, Oct 11, 2010 at 9:35 AM, Santosh Srinivas
<santosh.srinivas at gmail.com> wrote:> Repost .. since the previous msg had problems
>
> I need to transpose the following input dataset ?into an output dataset
like
> below
>
> Input
> Date ? ? ? ? ? ?TICKER ? ? ? ? ?Price
> 11/10/2010 ? ? ? ? ? ? ?A ? ? ? ? ? ? ? 0.991642
> 11/10/2010 ? ? ? ? ? ? ?B ? ? ? ? ? ? ? 0.475023
> 11/10/2010 ? ? ? ? ? ? ?C ? ? ? ? ? ? ? 0.218642
> 11/10/2010 ? ? ? ? ? ? ?D ? ? ? ? ? ? ? 0.365135
> 12/10/2010 ? ? ? ? ? ? ?A ? ? ? ? ? ? ? 0.687873
> 12/10/2010 ? ? ? ? ? ? ?B ? ? ? ? ? ? ? 0.47006
> 12/10/2010 ? ? ? ? ? ? ?C ? ? ? ? ? ? ? 0.533542
> 12/10/2010 ? ? ? ? ? ? ?D ? ? ? ? ? ? ? 0.812439
> 13/10/2010 ? ? ? ? ? ? ?A ? ? ? ? ? ? ? 0.210848
> 13/10/2010 ? ? ? ? ? ? ?B ? ? ? ? ? ? ? 0.699799
> 13/10/2010 ? ? ? ? ? ? ?C ? ? ? ? ? ? ? 0.546003
> 13/10/2010 ? ? ? ? ? ? ?D ? ? ? ? ? ? ? 0.152316
>
> Output needed
>
> Date ? ?A ? ? ? B ? ? ? C ? ? ? D
> 11/10/2010 ? ? ?0.991642 ? ? ? ?0.475023 ? ? ? ?0.218642 ? ? ? ?0.365135
> 12/10/2010 ? ? ?0.687873 ? ? ? ?0.47006 0.533542 ? ? ? ?0.812439
> 13/10/2010 ? ? ?0.210848 ? ? ? ?0.699799 ? ? ? ?0.546003 ? ? ? ?0.152316
>
> I tried using the aggregate function but not quite getting the method.
>

1. Try this:

Lines <- " Date            TICKER          Price
11/10/2010              A               0.991642
11/10/2010              B               0.475023
11/10/2010              C               0.218642
11/10/2010              D               0.365135
12/10/2010              A               0.687873
12/10/2010              B               0.47006
12/10/2010              C               0.533542
12/10/2010              D               0.812439
13/10/2010              A               0.210848
13/10/2010              B               0.699799
13/10/2010              C               0.546003
13/10/2010              D               0.152316"

DF <- read.table(textConnection(Lines), header = TRUE)
DF$Date <- as.Date(DF$Date,"%d/%m/%Y")
DFout <- reshape(DF, dir = "wide", timevar = "TICKER",
idvar = "Date")
names(DFout) <- sub("Price.", "", names(DFout))


2. or using read.zoo in the zoo package we can read it in and reshape
it all at once:

library(zoo)
z <- read.zoo(textConnection(Lines), header = TRUE,
		split = 2, format = "%d/%m/%Y")

At this point z is a zoo object in wide format:
> z                  A        B        C        D
2010-10-11 0.991642 0.475023 0.218642 0.365135
2010-10-12 0.687873 0.470060 0.533542 0.812439
2010-10-13 0.210848 0.699799 0.546003 0.152316

Since this is a multivariate time series you might want to just leave
it as a zoo object since you then get all of the facilities of zoo,
e.g.

plot(z) # multi-panel
plot(z, screen = 1) # all in one panel

but if you want it as a data frame then convert it like this:
> data.frame(Index = index(z), coredata(z))       Index        A        B        C        D
1 2010-10-11 0.991642 0.475023 0.218642 0.365135
2 2010-10-12 0.687873 0.470060 0.533542 0.812439
3 2010-10-13 0.210848 0.699799 0.546003 0.152316

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

R help - Oct 2010 - Dataset Transformation

[R] Dataset Transformation

[R] Dataset Transformation

[R] Dataset Transformation

[R] Dataset Transformation