thr3ads.net - R help - [R] Rearranging data [Feb 2010]

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

Anna Carter

2010-Feb-18 09:21 UTC

[R] Rearranging data

Hi!
I have just started learning R and today only I have joined this group. This is
my first mail and I wish to thank all of you for allowing me to be part of this
group.

I have following problem. I have an input.csv file such that

corp_id      date    investment_id       rate
corp1        17-Feb         1                 65
corp1        16-Feb         1                 70
corp1        15-Feb         1                 69
corp1        14-Feb         1                 89
corp1        13-Feb         1                 88
corp2        17-Feb         1                 95
corp2        16-Feb         1               135
corp2        15-Feb         1               140
corp3        17-Feb         2                 86
corp3        16-Feb         2                 73
corp5        17-Feb         3                 24
corp6        17-Feb         4                 44
corp11      17-Feb         1                 30
corp13      17-Feb         1                 16
corp21      15-Feb         3                 21


I have filtered them based on investment id as

invest_data = read.csv('input.csv')
investment_id = invest_data$investment_id

filtered1 <- subset(invest_data,investment_id==1)
filtered2 <- subset(invest_data,investment_id==2)
filtered3 <- subset(invest_data,investment_id==3)
filtered4 <- subset(invest_data,investment_id==4)

so filtered1 will give me
> filtered1   corp_id   date         investment_id    stock_rate
1    corp1   17-Feb             1                    65
2    corp1   16-Feb             1                    70
3    corp1   15-Feb             1                    69
4    corp1   14-Feb             1                    89
5    corp1   13-Feb             1                    88
6    corp2   17-Feb             1                    95
7    corp2   16-Feb             1                  135
8    corp2   15-Feb             1                  140
13  corp11  17-Feb             1                    30
14  corp17  17-Feb             1                    16

My objective is to rearrange filtered1 as

date         corp1   corp2   corp11    corp17
17-Feb       65        95         30          16
16-Feb       70       135
15-Feb       69       140 
14-Feb       89
13-Feb       88

#(The above figures represent the corp-wise and date-wise rates for
investment_id = 1.)

Please guide me how the filtered1 can be rearranged?

Thanking you

Anna Carter







      The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. 
	[[alternative HTML version deleted]]

Barry Rowlingson

2010-Feb-18 12:14 UTC

head link

[R] Rearranging data

On Thu, Feb 18, 2010 at 9:21 AM, Anna Carter <anna_carter09 at yahoo.com>
wrote:
> My objective is to rearrange filtered1 as
>
> date???????? corp1?? corp2?? corp11??? corp17
> 17-Feb?????? 65??????? 95???????? 30????????? 16
> 16-Feb?????? 70?????? 135
> 15-Feb?????? 69?????? 140
> 14-Feb?????? 89
> 13-Feb?????? 88
>
> #(The above figures represent the corp-wise and date-wise rates for
investment_id = 1.)
>
> Please guide me how the filtered1 can be rearranged?
 Ooh, so close! If you'd said 'reshaped' you'd be half way
there:
> filtered1   corp_id   date investment_id stock_rate
1    corp1 17-Feb             1         65
2    corp1 16-Feb             1         70
3    corp1 15-Feb             1         69
4    corp1 14-Feb             1         89
5    corp1 13-Feb             1         88
6    corp2 17-Feb             1         95
7    corp2 16-Feb             1        135
8    corp2 15-Feb             1        140
13  corp11 17-Feb             1         30
14  corp17 17-Feb             1         16
> cast(filtered1, date~corp_id,value="stock_rate")    date corp1 corp11 corp17 corp2
1 13-Feb    88     NA     NA    NA
2 14-Feb    89     NA     NA    NA
3 15-Feb    69     NA     NA   140
4 16-Feb    70     NA     NA   135
5 17-Feb    65     30     16    95

The ordering is different to your desire but it's trivial to rearrange
it if you need. Also you get NA's where there's no value in that cell.

Are you doing this purely for presentation? Because if not then might
be easier to keep the data in the long format and work on it like that
with the various 'apply' type functions. See also the 'plyr'
package.

Barry

-- 
blog: http://geospaced.blogspot.com/
web: http://www.maths.lancs.ac.uk/~rowlings
web: http://www.rowlingson.com/
twitter: http://twitter.com/geospacedman
pics: http://www.flickr.com/photos/spacedman

Henrique Dallazuanna

2010-Feb-18 12:41 UTC

head link

[R] Rearranging data

Try this also;

xtabs(rate ~ date + corp_id + investment_id, data = DF)


On Thu, Feb 18, 2010 at 7:21 AM, Anna Carter <anna_carter09 at yahoo.com>
wrote:> Hi!
> I have just started learning R and today only I have joined this group.
This is my first mail and I wish to thank all of you for allowing me to be part
of this group.
>
> I have following problem. I have an input.csv file such that
>
> corp_id??? ? date??? investment_id??? ?? rate
> corp1??? ??? 17-Feb??? ???? 1???????? ??? ??? 65
> corp1??? ??? 16-Feb??? ???? 1???????? ??? ??? 70
> corp1??? ??? 15-Feb??? ???? 1??? ???????????? 69
> corp1??? ??? 14-Feb??? ???? 1??? ???????????? 89
> corp1??? ??? 13-Feb??? ???? 1??? ???????????? 88
> corp2??? ??? 17-Feb??? ???? 1??? ???????????? 95
> corp2??? ??? 16-Feb??? ???? 1??? ?????????? 135
> corp2??? ??? 15-Feb??? ???? 1??? ?????????? 140
> corp3??? ??? 17-Feb??? ???? 2??? ???????????? 86
> corp3??? ??? 16-Feb??? ???? 2??? ???????????? 73
> corp5??? ??? 17-Feb??? ???? 3??? ???????????? 24
> corp6??? ??? 17-Feb??? ???? 4??? ???????????? 44
> corp11?? ?? 17-Feb??? ???? 1?????????????? ? 30
> corp13????? 17-Feb??? ???? 1???????????????? 16
> corp21????? 15-Feb??? ???? 3???????????????? 21
>
>
> I have filtered them based on investment id as
>
> invest_data = read.csv('input.csv')
> investment_id = invest_data$investment_id
>
> filtered1 <- subset(invest_data,investment_id==1)
> filtered2 <- subset(invest_data,investment_id==2)
> filtered3 <- subset(invest_data,investment_id==3)
> filtered4 <- subset(invest_data,investment_id==4)
>
> so filtered1 will give me
>
>> filtered1
> ?? corp_id?? date???????? investment_id??? stock_rate
> 1??? corp1?? 17-Feb???????????? 1??????????????????? 65
> 2??? corp1?? 16-Feb???????????? 1??????????????????? 70
> 3??? corp1?? 15-Feb???????????? 1??????????????????? 69
> 4??? corp1?? 14-Feb???????????? 1??????????????????? 89
> 5??? corp1?? 13-Feb???????????? 1??????????????????? 88
> 6??? corp2?? 17-Feb???????????? 1??????????????????? 95
> 7??? corp2?? 16-Feb???????????? 1????????????????? 135
> 8??? corp2?? 15-Feb???????????? 1????????????????? 140
> 13? corp11? 17-Feb???????????? 1??????????????????? 30
> 14? corp17? 17-Feb???????????? 1??????????????????? 16
>
> My objective is to rearrange filtered1 as
>
> date???????? corp1?? corp2?? corp11??? corp17
> 17-Feb?????? 65??????? 95???????? 30????????? 16
> 16-Feb?????? 70?????? 135
> 15-Feb?????? 69?????? 140
> 14-Feb?????? 89
> 13-Feb?????? 88
>
> #(The above figures represent the corp-wise and date-wise rates for
investment_id = 1.)
>
> Please guide me how the filtered1 can be rearranged?
>
> Thanking you
>
> Anna Carter
>
>
>
>
>
>
>
> ? ? ?The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
> ? ? ? ?[[alternative HTML version deleted]]
>
>
> ______________________________________________
> 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.
>
>


-- 
Henrique Dallazuanna
Curitiba-Paran?-Brasil
25? 25' 40" S 49? 16' 22" O

Reasonably Related Threads

Search for more possibly parallel threads

R help - Feb 2010 - Rearranging data

[R] Rearranging data

[R] Rearranging data

[R] Rearranging data

Reasonably Related Threads