ranking the difference of two columns in a dataframe by groups
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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._