R: Data.table on subset excluding by value

Question

Using `data.table` in `R`, I'm trying to make an operation on the subset excluding selected element. I'm using the `by` operator, but I don't know if this is the right approach. Here's an example. E.g. the value for `Delta` in `IAH:SNA` is (3+3)/2 which is the mean of `Stops` in `IAH:SNA` once `Delta` has been excluded. ``` library(data.table) s1 <- "Market Carrier Stops IAH:SNA Delta 1 IAH:SNA Delta 1 IAH:SNA Southwest 3 IAH:SNA Southwest 3 MSP:CLE Southwest 2 MSP:CLE Southwest 2 MSP:CLE American 2 MSP:CLE JetBlue 1" d <- data.table(read.table(textConnection(s1), header=TRUE)) setkey(d, Carrier, Market) f <- function(x, y){ subset(d, !(Carrier %in% x) & Market == y, Stops)[, mean(Stops)]} d[, s := f(.BY[[1]], .BY[[2]]), by=list(Carrier, Market)] ## Market Carrier Stops s ## 1: MSP:CLE American 2 1.666667 ## 2: IAH:SNA Delta 1 3.000000 ## 3: IAH:SNA Delta 1 3.000000 ## 5: IAH:SNA Southwest 3 1.000000 ## 6: IAH:SNA Southwest 3 1.000000 ## 7: MSP:CLE Southwest 2 1.500000 ## 8: MSP:CLE S…

Accepted Answer

@Roland's answer will work for some functions (and when it does it will be best) but not in general. Unfortunately you can't apply the split-apply-combine strategy to the data as is to do the task, but you can if you make the data larger. Let's start with a simpler example:

```
dt = data.table(a = c(1,1,2,2,3,3), b = c(1:6), key = 'a')

# now let's extend this table the following way
# take the unique a's and construct all the combinations excluding one element
combinations = dt[, combn(unique(a), 2)]

# now combine this into a data.table with the excluded element as the index
# and merge it back into the original data.table
extension = rbindlist(apply(combinations, 2,
                  function(x) data.table(a = x, index = setdiff(c(1,2,3), x))))
setkey(extension, a)

dt.extended = extension[dt, allow.cartesian = TRUE]
dt.extended[order(index)]
#    a index b
# 1: 2     1 3
# 2: 2     1 4
# 3: 3     1 5
# 4: 3     1 6
# 5: 1     2 1
# 6: 1     2 2
# 7: 3     2 5
# 8: 3     2 6
# 9: 1     3 1
#10: 1     3 2
#11: 2     3 3
#12: 2     3 4

# Now we have everything we need:
dt.extended[, mean(b), by = list(a = index)]
#   a  V1
#1: 3 2.5
#2: 2 3.5
#3: 1 4.5
```

Going back to original data (and doing some operations slightly differently, to simplify expressions):

```
extension = d[, {Carrier.uniq = unique(Carrier);
                 .SD[, rbindlist(combn(Carrier.uniq, length(Carrier.uniq)-1,
                          function(x) data.table(Carrier = x,
                                   index = setdiff(Carrier.uniq, x)),
                          simplify = FALSE))]}, by = Market]
setkey(extension, Market, Carrier)

extension[d, allow.cartesian = TRUE][, mean(Stops), by = list(Market, Carrier = index)]
#    Market   Carrier       V1
#1: IAH:SNA Southwest 1.000000
#2: IAH:SNA     Delta 3.000000
#3: MSP:CLE   JetBlue 2.000000
#4: MSP:CLE Southwest 1.500000
#5: MSP:CLE  American 1.666667
```

R: Data.table on subset excluding by value

Solution

Problem