John John - 2 months ago 16
R Question

rearranging dataframe in r

I am trying to work with some existing stock data I have. Ultimately I want to generate weighted returns on the data but I need to change it's structure first.

Existing Code:

library(reshape2)
library(xts)
data <- read.csv(file="mini_r_weights.csv",head=TRUE,sep=",")
names(data)
wts=data[4]
head(wts)
head(data, n=18)
try=data[1:3]
try


Output:

> data <- read.csv(file="mini_r_weights.csv",head=TRUE,sep=",")

> names(data)
[1] "Date" "Symbol" "Close" "Weight"

> wts=data[4]

> head(wts)
Weight
1 0.3
2 0.3
3 0.3
4 0.3
5 0.3
6 0.3

> head(data, n=18)
Date Symbol Close Weight
1 1/13/2012 AAPL 54.90965 0.30
2 1/17/2012 AAPL 55.54924 0.30
3 1/18/2012 AAPL 56.12606 0.30
4 1/19/2012 AAPL 55.94817 0.30
5 1/20/2012 AAPL 54.97374 0.30
6 1/23/2012 AAPL 55.90357 0.30
7 1/13/2012 DIS 36.19277 0.25
8 1/17/2012 DIS 36.26817 0.25
9 1/18/2012 DIS 36.77713 0.25
10 1/19/2012 DIS 37.17299 0.25
11 1/20/2012 DIS 37.05046 0.25
12 1/23/2012 DIS 36.99391 0.25
13 1/13/2012 IBM 158.84454 0.45
14 1/17/2012 IBM 159.58929 0.45
15 1/18/2012 IBM 160.53796 0.45
16 1/19/2012 IBM 160.05033 0.45
17 1/20/2012 IBM 167.14319 0.45
18 1/23/2012 IBM 168.43763 0.45

> try=data[1:3]

> try
Date Symbol Close
1 1/13/2012 AAPL 54.90965
2 1/17/2012 AAPL 55.54924
3 1/18/2012 AAPL 56.12606
4 1/19/2012 AAPL 55.94817
5 1/20/2012 AAPL 54.97374
6 1/23/2012 AAPL 55.90357
7 1/13/2012 DIS 36.19277
8 1/17/2012 DIS 36.26817
9 1/18/2012 DIS 36.77713
10 1/19/2012 DIS 37.17299
11 1/20/2012 DIS 37.05046
12 1/23/2012 DIS 36.99391
13 1/13/2012 IBM 158.84454
14 1/17/2012 IBM 159.58929
15 1/18/2012 IBM 160.53796
16 1/19/2012 IBM 160.05033
17 1/20/2012 IBM 167.14319
18 1/23/2012 IBM 168.43763


I need the data (in this case "try") in the format below:

Date AAPL DIS IBM
1/13/2012 54.90964982 36.19276852 158.8445426
1/17/2012 55.54924437 36.26817012 159.5892927
1/18/2012 56.12605664 36.77713093 160.5379623
1/19/2012 55.94817349 37.17298933 160.0503284
1/20/2012 54.97374008 37.05046173 167.1431858
1/23/2012 55.90357191 36.99391053 168.4376323


thank you

Answer

This would be done using reshape2 dcast function. This question has been answered here before, and you can read more about the cast function here and here

Really quickly though, here is how yours might look:

d = dcast(try, Date~Symbol)
Comments