title: R: Data.table on subset excluding by value
body: <p>Using <code>data.table</code> in <code>R</code>, I'm trying to make an operation on the subset <em>excluding</em> selected element.  I'm using the <code>by</code> operator, but I don't know if this is the right approach. </p>
  
  <p>Here's an example.  E.g. the  value for <code>Delta</code> in <code>IAH:SNA</code> is (3+3)/2 which is the mean of <code>Stops</code> in <code>IAH:SNA</code> once <code>Delta</code> has been excluded.</p>
  
  <pre><code>library(data.table)
  s1 &lt;- "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 &lt;- data.table(read.table(textConnection(s1), header=TRUE))
  
  setkey(d, Carrier, Market)
  
  f &lt;- function(x, y){
           subset(d, !(Carrier %in% x) &amp; 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 Southwest     2  1.500000
  </code></pre>
  
  <p>The above solution performs <em>very</em> poorly on large data sets (it's essentially an <code>mapply</code>), but I'm not sure how to do it in a fast <code>data.table</code>-like way. </p>
  
  <p>Perhaps one could (dynamically) generate a factor that does this?  I'm just not sure how. . .</p>
  
  <p>Is there a way to improve it? </p>
  
  <p>Edit: Just for the heck of it, here's a way to get a bigger version of the above </p>
  
  <pre><code>library(data.table)
  dl.dta &lt;- function(...){
        ## input years ..
        years &lt;- gsub("\\.", "_", c(...))
        baseurl &lt;- "http://www.transtats.bts.gov/Download/"
        names &lt;- paste("Origin_and_Destination_Survey_DB1BMarket", years, sep="_")
        info &lt;- t(sapply(names, function(x) file.exists(paste(x, c("zip", "csv"), sep="."))))
        to.download &lt;- paste(baseurl, names, ".zip", sep="")[!apply(info, 1, any)]
        if (length(to.download) &gt; 0){
            message("starting download...")
            sapply(to.download,
                   function(x) download.file(x, rev(strsplit(x, "/")[[1]])[1]))}
  
        to.unzip &lt;- paste(names,  "zip", sep=".")[!info[, 2]]
        if (length(to.unzip &gt; 0)){
            message("starting to unzip...")
            sapply(to.unzip, unzip)}
        paste(names, "csv", sep=".")}
  
  countWords.split &lt;- function(x, s=":"){
      ## Faster on my machine than grep for some reanon
      sapply(strsplit(as.character(x), s), length)}
  
  countWords.grep &lt;- function(x){
      sapply(gregexpr("\\W+", x), length)+1}
  
  fname &lt;- dl.dta(2013.1)
  cols &lt;- rep("NULL", 41)
  ## Columns to keep: 9 is Origin, 18 is Dest, 24 is groups of airports in travel
  ## 30 is RPcarrier (reporting carrier).  
  ## For more columns: 35 is market fare and 36 is distance.
  cols[9] &lt;- cols[18] &lt;- cols[24] &lt;- cols[30] &lt;- NA
  d &lt;- data.table(read.csv(file=fname,  colClasses=cols))
  d[, Market := paste(Origin, Dest, sep=":")]
  ## should probably
  d[, Stops := -2 + countWords.split(AirportGroup)]
  d[, Carrier := RPCarrier]
  d[, c("RPCarrier", "Origin", "Dest", "AirportGroup") := NULL]
  </code></pre>