Re: [Rd] Any interest in "merge" and "by" implementations specifically for sorted data?

From: Kevin B. Hendricks <kevin.hendricks_at_sympatico.ca>
Date: Fri 28 Jul 2006 - 23:56:57 GMT

Hi Bill,

> Splus8.0 has something like what you are talking about
> that provides a fast way to compute
> sapply(split(xVector, integerGroupCode), summaryFunction)
> for some common summary functions. The 'integerGroupCode'
> is typically the codes from a factor, but you could compute
> it in other ways. It needs to be a "small" integer in
> the range 1:ngroups (like the 'bin' argument to tabulate).
> Like tabulate(), which is called from table(), these are
> meant to be called from other functions that can set up
> appropriate group codes. E.g., groupSums or rowSums or
> fancier things could be based on this.
>
> They do not insist you sort the input in any way. That
> would really only be useful for group medians and we haven't
> written that one yet.

The sort is also useful for recoding each group into subgroups based on some other numeric vector. This is the problem I run into trying to build portfolios that can be used as benchmarks for long term stock returns.

Another issue I have is that to recode a long character string that I use as a sort key for accessing a subgroup of the data in the data.frame to a set of small integers is not fast. I can make a fast implementation if the data is sorted by the key, but without the sort, just converting my sort keys to the required small integer codes would be expensive for very long vectors since my small integer codes would have to reflect the order of the data (ie. be increasing subportfolio numbers).

More specifically, I am now converting all of my SAS code to R code and the problem is I have lots of snippets of SAS that do the following ...

PROC SORT;
   BY MDSIZ FSIZ; /* WRITE OUT THE MIN SIZE CUTOFF VALUES */ PROC UNIVARIATE NOPRINT;
    VAR FSIZ;
    BY MDSIZ;
    OUTPUT OUT=TMPS1 MIN=XMIN; where my sort key MDSIZ is a character string that is the concatenation of the month ending date MD and the size portfolio of a particular firm (SIZ) and I want to find the cutoff points (the mins) for each of the portfolios for every month end date across all traded firms.

>
> The typical prototype is
>
>> igroupSums
> function(x, group = NULL, na.rm = F, weights = NULL, ngroups = if
> (is.null(
> group)) 1 else max(as.integer(group), na.rm = T))
>
> and the currently supported summary functions are
> mean : igroupMeans
> sum : igroupSums
> prod : igroupProds
> min : igroupMins
> max : igroupMaxs
> range : igroupRanges
> any : igroupAnys
> all : igroupAlls

SAS is similar in that is also has a specific list of functions you can request including all of the basic stats from a PROC univariate including higher moment stuff (skewness, kurtosis, robust statistics, and even statistical test results for each coded subgroup, and the nice thing is all combinations can be done with one call.

But to do that SAS does require the presorting, but it does run really fast for even super long vectors with lots of sort keys.

Similarly the next snippet of code, will take the file and resort it by the portfolio key and then the market to book ratio (MTB) for all trading firms for all monthly periods since 1980. It will then split each size portfolio for each month ending date into 5 equal portfolios based on market to book ratios (thus the need for the sort). SAS returns a coded integer vector PMTB (made up of 1s to 5 with 1s's for the smallest MTB and 5 for the largest MTB) repeated for each subgroup of MDSIZ. PMTB matches the original vector in length and therefore fits right into the data frame.

/* SPLIT INTO Market to Book QUINTILES BY MDSIZ */ PROC SORT;
   BY MDSIZ MTB;
PROC RANK GROUPS=5 OUT=TMPS0;
    VAR MTB;
    RANKS PMTB;
    BY MDSIZ; The problem of assigning elements of a long data vector to portfolios and sub portfolios based on the values of specific data columns which must be calculated at each step and are not fixed or hardcoded is one that finance can run into (and therefore I run into it).

So by sorting I could handle the need for "small integer" recoding and the small integers would have meaning (i.e. higher values will represent larger MTB firms, etc).

That just leaves the problem of calculating stats on short sequences of of a longer integer.

> They are fast:
>
>> x<-runif(2e6)
>> i<-rep(1:1e6, 2)
>> sys.time(sx <- igroupSums(x,i))
> [1] 0.66 0.67
>> length(sx)
> [1] 1000000
>
> On that machine R takes 44 seconds to go the lapply/split
> route:
>
>> unix.time(unlist(lapply(split(x,i), sum)))
> [1] 43.24 0.78 44.11 0.00 0.00

Yes! That is exactly what I need.

Are there plans for adding something like that to R?

Thanks,

Kevin



R-devel@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-devel Received on Sat Jul 29 10:01:40 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Sat 29 Jul 2006 - 02:27:07 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.