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

Jeff Miller jdm at xnet.com
Fri Jun 8 04:21:55 CEST 2001

```
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
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._

```