Hi,
I've tried to capture the basics of this problem I'm having. Been
working on this for a couple of days and just cannot get past it. As a
test of this list software I've attached is a small text file zipped
up. I hope it gets through but if it doesn't I'll post the actual text
which is only 26 lines. Put it somewhere sensible and change the first
line in the code to point at it.
The R code I'm having trouble with is this:
Y = read.table("C:\\test.txt")
names(Y)[7] = "value"
Y
ReShape.Y = reshape(Y, varying=list(names(Y)[7]), direction='long')
ReShape.Y
cast(ReShape.Y, EnTime ~ ExTime)
When run the last two steps output the following:
> ReShape.Y
Trade PosType EnDate EnTime ExDate ExTime time value id
1 1 -1 1080103 800 1080103 1310 1 520 1
2 2 -1 1080104 755 1080104 1310 1 530 2
3 3 1 1080107 945 1080107 1310 1 -340 3
4 4 -1 1080108 820 1080108 1310 1 2150 4
5 5 -1 1080109 855 1080109 1245 1 -1040 5
6 6 1 1080109 1245 1080109 1310 1 110 6
7 7 1 1080110 925 1080110 1310 1 680 7
8 8 1 1080111 800 1080111 1155 1 -830 8
9 9 -1 1080111 1155 1080111 1310 1 160 9
10 10 -1 1080114 935 1080114 1210 1 -810 10
11 11 1 1080114 1210 1080114 1310 1 -280 11
12 12 -1 1080115 750 1080115 1310 1 -310 12
13 13 -1 1080116 755 1080116 950 1 -1340 13
14 14 1 1080116 950 1080116 1310 1 -550 14
15 15 -1 1080117 740 1080117 1310 1 700 15
16 16 -1 1080118 815 1080118 1310 1 440 16
17 17 1 1080122 750 1080122 1310 1 -400 17
18 18 1 1080123 1210 1080123 1310 1 1720 18
19 19 -1 1080124 810 1080124 1310 1 -60 19
20 20 -1 1080125 740 1080125 1310 1 1060 20
21 21 1 1080128 755 1080128 1310 1 1120 21
22 22 -1 1080130 850 1080130 1125 1 -1120 22
23 23 1 1080130 1125 1080130 1305 1 -1420 23
24 24 -1 1080130 1305 1080130 1310 1 180 24
25 25 1 1080131 805 1080131 1310 1 1080
25> cast(ReShape.Y, EnTime ~ ExTime)
Aggregation requires fun.aggregate: length used as default
EnTime 950 1125 1155 1210 1245 1305 1310
1 740 0 0 0 0 0 0 2
2 750 0 0 0 0 0 0 2
3 755 1 0 0 0 0 0 2
4 800 0 0 1 0 0 0 1
5 805 0 0 0 0 0 0 1
6 810 0 0 0 0 0 0 1
7 815 0 0 0 0 0 0 1
8 820 0 0 0 0 0 0 1
9 850 0 1 0 0 0 0 0
10 855 0 0 0 0 1 0 0
11 925 0 0 0 0 0 0 1
12 935 0 0 0 1 0 0 0
13 945 0 0 0 0 0 0 1
14 950 0 0 0 0 0 0 1
15 1125 0 0 0 0 0 1 0
16 1155 0 0 0 0 0 0 1
17 1210 0 0 0 0 0 0 2
18 1245 0 0 0 0 0 0 1
19 1305 0 0 0 0 0 0 1>
What I need/want is that instead of displaying '1' or '2' -
the
number of events that fit these EnTime/ExTime values - I need to have
the sum of the 'value' column from ReShape.Y.
Is this possible? I'm sure it's related the the Aggregation
requires fun.aggregate but cannot find an example of doing this sort
of thing, or don't recognize it when I'm looking at it.
Thanks,
Mark
On Fri, Jul 10, 2009 at 8:38 AM, Mark Knecht<markknecht at gmail.com> wrote: <SNIP>> ? What I need/want is that instead of displaying '1' or '2' - the > number of events that fit these EnTime/ExTime values - I need to have > the sum of the 'value' column from ReShape.Y. > > ? Is this possible? I'm sure it's related the the Aggregation > requires fun.aggregate but cannot find an example of doing this sort > of thing, or don't recognize it when I'm looking at it. > > Thanks, > Mark >SOLVED It's so strange that after looking at this off and on for two days then I suddenly discover cast(ReShape.Y, EnTime ~ ExTime, function(x) sum(x)) which does what I need:> cast(ReShape.Y, EnTime ~ ExTime, function(x) sum(x))EnTime 950 1125 1155 1210 1245 1305 1310 1 740 0 0 0 0 0 0 1760 2 750 0 0 0 0 0 0 -710 3 755 -1340 0 0 0 0 0 1650 4 800 0 0 -830 0 0 0 520 5 805 0 0 0 0 0 0 1080 6 810 0 0 0 0 0 0 -60 7 815 0 0 0 0 0 0 440 8 820 0 0 0 0 0 0 2150 9 850 0 -1120 0 0 0 0 0 10 855 0 0 0 0 -1040 0 0 11 925 0 0 0 0 0 0 680 12 935 0 0 0 -810 0 0 0 13 945 0 0 0 0 0 0 -340 14 950 0 0 0 0 0 0 -550 15 1125 0 0 0 0 0 -1420 0 16 1155 0 0 0 0 0 0 160 17 1210 0 0 0 0 0 0 1440 18 1245 0 0 0 0 0 0 110 19 1305 0 0 0 0 0 0 180>NOTE: There should have been a require(reshape) in the previous code for anyone trying to look at this later. Cheers, Mark
From: Mark Knecht <markknecht at gmail.com> To: r-help <r-help at r-project.org> Date: Fri, 10 Jul 2009 09:07:29 -0700 Subject: Re: [R] ReShape/cast question - sum of value in table On Fri, Jul 10, 2009 at 8:38 AM, Mark Knecht<markknecht at gmail.com> wrote: Hi Mark, You can simplify the code a bit:> > <SNIP> > > ? What I need/want is that instead of displaying '1' or '2' - the > > number of events that fit these EnTime/ExTime values - I need to have > > the sum of the 'value' column from ReShape.Y. > > > > ? Is this possible? I'm sure it's related the the Aggregation > > requires fun.aggregate but cannot find an example of doing this sort > > of thing, or don't recognize it when I'm looking at it. > > > > Thanks, > > Mark > > > SOLVED > > It's so strange that after looking at this off and on for two days > then I suddenly discover > > cast(ReShape.Y, EnTime ~ ExTime, function(x) sum(x)) >all you really need is cast(ReShape.Y, EnTime ~ ExTime, sum) although I prefer cast(ReShape.Y, EnTime ~ ExTime, fun.aggregate=sum) for readability. Also, you may want to look at the melt function in the reshape package for the original conversion from the wide to the long form. Note that the author of the reshape package has additional resources on his website: http://had.co.nz/reshape/ -Ista> > > which does what I need: > > > cast(ReShape.Y, EnTime ~ ExTime, function(x) sum(x)) > ?EnTime ? 950 ?1125 1155 1210 ?1245 ?1305 1310 > 1 ? ? 740 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 1760 > 2 ? ? 750 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 -710 > 3 ? ? 755 -1340 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 1650 > 4 ? ? 800 ? ? 0 ? ? 0 -830 ? ?0 ? ? 0 ? ? 0 ?520 > 5 ? ? 805 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 1080 > 6 ? ? 810 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 ?-60 > 7 ? ? 815 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 ?440 > 8 ? ? 820 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 2150 > 9 ? ? 850 ? ? 0 -1120 ? ?0 ? ?0 ? ? 0 ? ? 0 ? ?0 > 10 ? ?855 ? ? 0 ? ? 0 ? ?0 ? ?0 -1040 ? ? 0 ? ?0 > 11 ? ?925 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 ?680 > 12 ? ?935 ? ? 0 ? ? 0 ? ?0 -810 ? ? 0 ? ? 0 ? ?0 > 13 ? ?945 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 -340 > 14 ? ?950 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 -550 > 15 ? 1125 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 -1420 ? ?0 > 16 ? 1155 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 ?160 > 17 ? 1210 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 1440 > 18 ? 1245 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 ?110 > 19 ? 1305 ? ? 0 ? ? 0 ? ?0 ? ?0 ? ? 0 ? ? 0 ?180 > > > > NOTE: There should have been a require(reshape) in the previous code > for anyone trying to look at this later. > > Cheers, > Mark