[R] templated use of aggregate
David Winsemius
dwinsemius at comcast.net
Wed Jun 13 15:53:00 CEST 2012
On Jun 13, 2012, at 9:38 AM, Matthew Johnson wrote:
> Sorry, i'll try and put more flesh on the bones.
>
> please note, i changed the data in the example, as fiddling has raised
> another question that's best illustrated with a slightly different
> data set.
>
> first of all, when i do as you suggest, i obtain the following error:
>
>> PxMat <- aggregate(mm[,-1] ~ mm[,1], data=mm, sum)
>
> Error in aggregate.formula(mm[, -1] ~ mm[, 1], data = mm, sum) :
> 'names' attribute [3] must be the same length as the vector [1]
Very strange. When I just did it with the structure you (cross-)
posted on SO I got:
> adf <- aggregate(mm[,-1]~mm[,1], data=mm, sum); adf
snipped warning messages
mm[, 1] mm[, -1]
1 97.91 538
2 97.92 918
I had earlier tested it with a zoo object I had constructed and did it
again with the structure below.
mm[, 1] mm[, -1]
1 97.91 538
2 97.92 918
I'm using zoo_1.7-6 and R version 2.14.2 on a Mac. I do not remember
you posting the requested information about your versions.
--
David.
>
> my data.frame is an xts, and it looks like this:
>
> px_ym1 vol_ym1
> 2012-06-01 09:30:00 97.90 9
> 2012-06-01 09:30:00 97.90 60
> 2012-06-01 09:30:00 97.90 71
> 2012-06-01 09:30:00 97.90 5
> 2012-06-01 09:30:00 97.90 3
> 2012-06-01 09:30:00 97.90 21
> 2012-06-01 09:31:00 97.90 5
> 2012-06-01 09:31:00 97.89 192
> 2012-06-01 09:31:00 97.89 65
> 2012-06-01 09:31:00 97.89 73
> 2012-06-01 09:31:00 97.89 1
> 2012-06-01 09:31:00 97.89 1
> 2012-06-01 09:31:00 97.89 39
> 2012-06-01 09:31:00 97.90 15
> 2012-06-01 09:31:00 97.90 1
> 2012-06-01 09:31:00 97.89 1
> 2012-06-01 09:31:00 97.90 18
> 2012-06-01 09:31:00 97.89 1
> 2012-06-01 09:32:00 97.89 33
> 2012-06-01 09:34:00 97.89 1
> 2012-06-01 09:34:00 97.89 1
>
> dput(mn) returns:
>
>> dput(mn)
> structure(c(97.9, 97.9, 97.9, 97.9, 97.9, 97.9, 97.9, 97.89,
> 97.89, 97.89, 97.89, 97.89, 97.89, 97.9, 97.9, 97.89, 97.9, 97.89,
> 97.89, 97.89, 97.89, 9, 60, 71, 5, 3, 21, 5, 192, 65, 73, 1,
> 1, 39, 15, 1, 1, 18, 1, 33, 1, 1), .indexCLASS = c("POSIXct",
> "POSIXt"), .indexTZ = "GMT", class = c("xts", "zoo"), index =
> structure(c(1338543000,
> 1338543000, 1338543000, 1338543000, 1338543000, 1338543000,
> 1338543060,
> 1338543060, 1338543060, 1338543060, 1338543060, 1338543060,
> 1338543060,
> 1338543060, 1338543060, 1338543060, 1338543060, 1338543060,
> 1338543120,
> 1338543240, 1338543240), tzone = "GMT", tclass = c("POSIXct",
> "POSIXt")), .Dim = c(21L, 2L), .Dimnames = list(NULL, c("px_ym1",
> "vol_ym1")))
>
> as you can see, the xts data.frame xts data.frame that contains dates,
> prices and volumes. There is much more data over a long time period,
> and i'm interested in various sub-setting and then aggregate
> operations.
>
> I would like to split the data by time period and aggregate the data,
> such that i obtain a table which reports the volume traded at each
> price, for each of the time-period splits that i have chosen.
>
> I have employed the following approach:
>
> PxMat <- aggregate(.~px_ym1, data=mn, sum)
>
>
> which yields:
>
> px_ym1 vol_ym1
> 1 97.89 408
> 2 97.90 208
>
> and for subsets, i use the following grouping:
>
>> PxMat30 <- aggregate(.~px_ym1, data=mn[.indexmin(mn) == '30'], sum)
>
> Which yields:
>
> px_ym1 vol_ym1
> 1 97.9 169
>
> and
>
>> PxMat31 <- aggregate(.~px_ym1, data=mn[.indexmin(mn) == '31'], sum)
>
> which yields:
>
> px_ym1 vol_ym1
> 1 97.89 373
> 2 97.90 39
>
> and so on and so forth for each minute.
>
> when i try and sub-set using general notation, as follows:
>
> PxMat <- aggregate(.~mn[,1], data=mn, sum)
>
> this yields a different form of output:
>
> px_ym1 px_ym1 vol_ym1
> 1 97.90 1076.79 408
> 2 97.89 979.00 208
>
> the problem is that i now have the sum of the px_ym1 data (the sum
> of mn[,1])
>
> hopefully things are now clearer - sorry to have wasted your time up
> until now.
>
> assuming that i have now made my situation clear, i am hope you can
> help with four specific questions.
>
> 1/ My data-sets are HUGE, so speed is an issue - is this the fastest
> way to sub-set and aggregate an xts?
>
> 2/ is there a way to do this for multiple splits? say a table for each
> minute, day, week, or month? the return would potentially be a list
> with a table for each day / minute etc showing volume traded at each
> price -- but it doesn't have to be a list ...
>
> i am writing a function with loops that would generate a table that
> reports volume traded at each price for each case of a specified time
> split (say for four tables, one for each minute in the example data,
> returned as a list). my solution is slow, it seems like something that
> someone would have done better already. is this the case?
>
> 3/ is there a way to do the sub-setting with templated variables? i
> would like to obtain the table i get with the named aggregate
> functions (reproduced above) with multiple data frames, as the column
> names will differ from time to time. i cannot figure out how to stop
> the command from summing the mn[,1] column when i stop using variable
> names.
>
> 4/ on a related note, is it possible to apply different functions to
> different columns of data? It would be nice, for example, if the table
> returned from an aggregate command could be made to be:
>
> px_ym1 count vol_ym1
> 1 97.90 11 408
> 2 97.89 10 208
>
> where we have the price traded, the number of trades (a count of
> px_ym1 / mn[,1], and the sum of vol_ym1 (mn[,2]).
>
> thanks and best regards
>
> matt johnson
>
> On 13 June 2012 15:06, David Winsemius <dwinsemius at comcast.net> wrote:
>>
>>
>> On Jun 12, 2012, at 11:32 PM, Matthew Johnson wrote:
>>
>>> Dear R-help,
>>>
>>> I have an xts data set that i have subset by date.
>>>
>>> now it contains a date-time-stamp, and two columns (price and volume
>>> traded): my objective is to create tables of volume traded at a
>>> price - and
>>> i've been successfully using aggregate to do so in interactive use.
>>>
>>> say the data looks as follows:
>>>
>>> px_ym1 vol_ym1
>>> 2012-06-01 09:37:00 97.91 437
>>> 2012-06-01 09:37:00 97.91 64
>>> 2012-06-01 09:37:00 97.91 1
>>> 2012-06-01 09:37:00 97.91 5
>>> 2012-06-01 09:37:00 97.91 5
>>> 2012-06-01 09:37:00 97.92 174
>>> 2012-06-01 09:37:00 97.92 64
>>> 2012-06-01 09:37:00 97.92 125
>>> 2012-06-01 09:37:00 97.92 124
>>> 2012-06-01 09:37:00 97.92 64
>>> 2012-06-01 09:37:00 97.92 109
>>> 2012-06-01 09:37:00 97.92 64
>>> 2012-06-01 09:37:00 97.92 19
>>> 2012-06-01 09:37:00 97.92 45
>>> 2012-06-01 09:37:00 97.92 75
>>> 2012-06-01 09:37:00 97.92 3
>>> 2012-06-01 09:37:00 97.92 47
>>> 2012-06-01 09:37:00 97.91 26
>>> 2012-06-01 09:37:00 97.92 4
>>> 2012-06-01 09:37:00 97.92 1
>>>
>>> the the following gives me what i'm looking for:
>>>
>>>> adf <- aggregate(.~px_ym1, data=mm, sum)
>>>
>>>
>>> which is this table:
>>>
>>> px_ym1 vol_ym1
>>> 1 97.91 538
>>> 2 97.92 918
>>>
>>> however now i'm trying to code it to run automatically, and use of
>>> the
>>> templated version:
>>>
>>>> adf <- aggregate(.~mm[,1], data=mm, sum)
>>
>>
>> Did you try:
>>
>> adf <- aggregate(mm[,-1] ~ mm[,1], data=mm, sum)
>> adf
>>
>> I would have used names:
>>
>> adf <- aggregate(vol_ym1 ~ px_ym1, data=mm, sum)
>> adf
>>
>>
>>
>>> yields the following - which contains what i'd like, but is has
>>> also summed
>>> across the price column (not ideal).
>>>
>>> px_ym1 px_ym1 vol_ym1
>>> 1 97.91 587.46 538
>>> 2 97.92 1370.88 918
>>>
>>> how do i code this so that i can enter an xts data-frame with
>>> arbitrary
>>> names and still obtain the table with only the information i desire?
>>
>>
>> That is too far to the vague side of the vague-specific continuum.
>>
>>
>>>
>>> on a related point, is there a way to combine the two steps?
>>
>>
>> Er, which two steps would that be?
>>
>>
>>> the function
>>> i've written splits by date and then returns a list containing
>>> data-frames
>>> that report the volume traded at each price on each date
>>>
>>> - am i re-creating the wheel here? is there canned function that
>>> does this?
>>>
>>> thanks + best regards
>>>
>>> matt johnson
>>
>>
>>
>> David Winsemius, MD
>> West Hartford, CT
>>
David Winsemius, MD
West Hartford, CT
More information about the R-help
mailing list