How to perform an operation on two groups in the same data.table, where the two groups both need to be referenced in the j field

Question

How to create a new column with the ratio of the 800 to 700 channel? I find myself running into these types of issues often, with much more complicated data.tables. Other examples would be to subtract the 800 channel of the same time from the 700 channel of the same time.

Example:

kdat <- data.table(channel=c(rep(c(700,800), each = 3)),
                   time=c(rep(1:3,2)),
                   value=c(1:6))

     channel time value
1:     700    1     1
2:     700    2     2
3:     700    3     3
4:     800    1     4
5:     800    2     5
6:     800    3     6

Options I can see are:
1.) Move from long to wide format and then divide, then convert back to long.
- Don't like because have to go back and forth between long and wide.
note: I go back to long since I like to keep all data together, and can do all plotting from a single data.table.

2.) kdat[channel==800,.(value)]/kdat[channel==700,.(value)]
- Don't like this because there is no checking to ensure the same times etc are matched up.

3.) Is there a way to do it with by .SD or some other way that I am missing?

Desired output:

  channel time value ratio
1: 700     1    1     4
...
6: 800     3    6     2

Answer 1

I would probably do

setkey(kdat, time)
kdat[ 
  dcast(kdat, time~channel, value="value")[, rat := `800`/`700`], 
  rat := i.rat
]

So you're changing from long to wide, but only in this temporary table used for merging, and only with the three relevant columns (time, channel and value).

---

If you're sure that every time that appears for one channel appears for the other, you can do

kdat[order(channel, time), rat := with(split(value, channel), `800`/`700`)]