How to perform an operation on two groups in the same data table, where both groups should be indicated in the j field

Question

How to create a new column with a ratio of 800 - 700 channels? I often encounter such problems, with much more complex data. Other examples are subtracting 800 channel time from the 700th 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 

The options I see are:
1.) Go from long to wide format, and then split, and then convert back to long.
- I do not like it, because you need to go back and forth between long and wide.
note: I come back to the idea that I like to store all the data together, and can do all the storing from the same data table.

2.) kdat [channel == 800 ,. (value)] / kdat [channel == 700 ,. (value)]
- I do not like this, because there is no verification to ensure that the same times, etc. Match up.

3.) Is there a way to do this using .SD or some other way that I am missing?

Required Conclusion:

  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 change from long to wide, but only in this temporary table used for merging, and only with three corresponding columns (time, channel and value).

---

If you are sure that every time one channel appears for another, you can do

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

Answer 2

Good if you have to use .SD :)

 kdat[, copy(.SD)[.SD[channel == 800 ][.SD[channel == 700], rat := value / i.value, on='time' ], rat := i.rat, on='time']][]