[R] ranking the difference of two columns in a dataframe by groups

[Peter Malewski]

First: I'm (as usual) unsure if I understood you right
Second: The grouping thing should be trivial
Third: For the rest, this might give you a hint:

xx <- data.frame(g=sort(rep(1:3,len=20)),x=rnorm(20,10,10),y=rnorm(20,10,10))
xx <- xx[order(xx$g,xx$y-xx$x),]
xx$New <- unlist(by(xx[,c("x","y")],xx$g,function(x){
  WhichMin <- which.min(abs(x[,1]-x[,2]))
  Len <- length(x[,1])
  if(WhichMin>1) Lower <- sort(-(seq(0,WhichMin-1,1)))
  else Lower <- 0
  Upper <- seq(WhichMin+1,Len,1)-WhichMin
  c(Lower,Upper)
    
})
                 )
xx$Diff <- xx$y-xx$x
xx

Peter

On Thu, Jun 07, 2001 at 09:21:55PM -0500, Jeff Miller wrote:
> 
> 
> Hi All,
> 
>     I'm wondering if there is a simple way to do the following.
> 
>     I have a dataframe, optiondata, with four columns: ticker, expdate,
> strike, and spot.
> 
>     A piece of it looks like this:
> 
> > optiondata
> 
>             sym        expdate           strike       spot
> 1          A           20010617       30.0         34.10
> 2          A           20010617       35.0         34.10
> 3          A           20010617       40.0         34.10
> 4          A           20010617       45.0         34.10
> 11        A           20010722       30.0         34.10
> 12        A           20010722       35.0         34.10
> 271     AAPL     20010722       27.5         20.94
> 272     AAPL     20010722       30.0         20.94
> 277     AAPL     20011021       12.5         20.94
> 278     AAPL     20011021       15.0         20.94
> 
> I'd like to add a fifth column to this dataframe,  moneyness.
> 
> The rule to calculate this quantity is:  take all distinct groups by ticker
> and expdate,
> then find the the strike closest to spot (min (abs( spot - strike))). This
> has moneyness zero.
> The strikes greater than this strike then have moneyness 1, 2, ...
> and the strikes below are -1, -2, ...
> 
> The example above with the new column would look like
> 
> > optiondata
> 
>             sym        expdate           strike       spot        moneyness
> 1          A           20010617       30.0         34.10    -1
> 2          A           20010617       35.0         34.10     0
> 3          A           20010617       40.0         34.10     1
> 4          A           20010617       45.0         34.10     2
> 11        A           20010722       30.0         34.10    -1
> 12        A           20010722       35.0         34.10     0
> 271     AAPL     20010722       27.5         20.94     0
> 272     AAPL     20010722       30.0         20.94     1
> 277     AAPL     20011021       12.5         20.94     0
> 278     AAPL     20011021       15.0         20.94     1
> 
> Thanks in advance for any help.
> 
>     Jeff Miller
> 
> 
> 
> 
> 
> 
> 
> 
> -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
> r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
> Send "info", "help", or "[un]subscribe"
> (in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
> _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._

-- 
P.Malewski, Limmerstr.47, 30451 Hannover, 0511-2135008
At work: http://www.MH-Hannover.de 0511 532 3194 / Fax: 0511 532 3190, 
P.Malewski at tu-bs.de, peter.malewski at gmx.de, malewski.peter at mh-hannover.de.
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._