title: R Sum a Column Grouped By Other Columns With Rollups
body: <p>I've already written the following, which will summarize a target column from the input dataset, and includes partial sums (or rollups or whatever the preferred vernacular may be) for each of the other columns present.</p>
  
  <p>This works fine but has an undesirable nested <code>for</code> loop, which I would like to remove in favor of more "functional" approach. I already attempted this, but despite more than a little reading and practice, I remain in a state of <em>non-grokkery</em> when it comes to the various <code>apply</code> and/or <code>dplyr</code> functions.</p>
  
  <p>It may well be that everything I'm doing is wrong; e.g. the setup to prep for the loops may be unnecessary if the final solution doesn't need it, etc... basically I just want the generate the expected ouput when given the provided input...</p>
  
  <p>Anyway, here's the code:</p>
  
  <pre><code># dummy data -- assume this is given 
  #######################################################################
  df1 &lt;- c("AA","B","AA","B","AA","B","AA","B","AA","B","AA","B",
           "M","M","N","N","M","M","N","N","M","M","N","N",
           "X","X","X","X","Y","Y","Y","Y","Z","Z","Z","Z",
           2,3,4,4,2,3,5,4,3,2,5,4)
  dim(df1) &lt;- c(12,4)
  colnames(df1) &lt;- c("f1","f2","f3","cnt")
  df1 &lt;- as.data.frame(df1,stringsAsFactors=F)
  df1$cnt &lt;- as.integer(df1$cnt)
  #######################################################################
  library(data.table)
  
  # some hard-coded variables...
  anyStr &lt;- "(any)"       # this string cannot appear in df1
  targetColName &lt;- "cnt"  # name of the column being summed from df1
  outputColName &lt;- "sum"  # name of our output column
  
  # grab names of only the columns we're going after... (just do everything but the target)
  colsToSummarize = (colnames(df1)[!colnames(df1) %in% list(targetColName)])
  
  # create a data table of just the unique values for each of those columns...
  df2 &lt;- lapply(colsToSummarize, function(x) { unique(df1[,x])})
  df2 &lt;- as.data.table(df2)
  
  # add a dummy row that basically means "any value" ...
  # this string cannot otherwise be present in the data...
  df2 &lt;- rbind(df2,as.data.table(t(rep(anyStr,length(df2)))))
  colnames(df2) &lt;- c(colsToSummarize)
  
  # expand df2 to generate all possible settings found in df1...
  df2 &lt;- unique(expand.grid(df2))
  rownames(df2)&lt;-NULL
  
  # do all the sums... there's probably a clever way to do this using "apply" functions...
  df2[,eval(outputColName)] &lt;- 0
  for (i2 in 1:nrow(df2)) {
    for (i1 in 1:nrow(df1)) {
      isMatch = T
      for (j in colsToSummarize) {
        if ((df2[i2,eval(j)]!=anyStr) &amp; (df1[i1,eval(j)]!=df2[i2,eval(j)])) {
          isMatch = F
          break
        }
      }
      if (isMatch) {
        df2[i2,eval(outputColName)] = df2[i2,eval(outputColName)] + df1[i1,eval(targetColName)]
      }
    }
  }
  </code></pre>
  
  <p>So, the sample dummy data looks like:</p>
  
  <pre><code>&gt; df1
     f1 f2 f3 cnt
  1  AA  M  X   2
  2   B  M  X   3
  3  AA  N  X   4
  4   B  N  X   4
  5  AA  M  Y   2
  6   B  M  Y   3
  7  AA  N  Y   5
  8   B  N  Y   4
  9  AA  M  Z   3
  10  B  M  Z   2
  11 AA  N  Z   5
  12  B  N  Z   4
  </code></pre>
  
  <p>... and the expected output:</p>
  
  <pre><code>&gt; df2
        f1    f2    f3 sum
  1     AA     M     X   2
  2      B     M     X   3
  3  (any)     M     X   5
  4     AA     N     X   4
  5      B     N     X   4
  6  (any)     N     X   8
  7     AA (any)     X   6
  8      B (any)     X   7
  9  (any) (any)     X  13
  10    AA     M     Y   2
  11     B     M     Y   3
  12 (any)     M     Y   5
  13    AA     N     Y   5
  14     B     N     Y   4
  15 (any)     N     Y   9
  16    AA (any)     Y   7
  17     B (any)     Y   7
  18 (any) (any)     Y  14
  19    AA     M     Z   3
  20     B     M     Z   2
  21 (any)     M     Z   5
  22    AA     N     Z   5
  23     B     N     Z   4
  24 (any)     N     Z   9
  25    AA (any)     Z   8
  26     B (any)     Z   6
  27 (any) (any)     Z  14
  28    AA     M (any)   7
  29     B     M (any)   8
  30 (any)     M (any)  15
  31    AA     N (any)  14
  32     B     N (any)  12
  33 (any)     N (any)  26
  34    AA (any) (any)  21
  35     B (any) (any)  20
  36 (any) (any) (any)  41
  </code></pre>
  
  <p>Naturally, I'm OK with output that is essentially the same; (e.g. NA or blanks or whatever instead of "(any)", order of rows/columns is not important, etc...)</p>
  
  <p>Aside: this is not identical to a SQL <code>group by with rollup</code> since this provides all permutations rather than a subset based on the order of variables in  your <code>group by</code> clause... if someone reading this wants that subset, they would simply need to remove rows that contain unexpected "(any)" values.</p>