title: Efficient data table/ frame/ tibble/ other aggregation in R
body: <p>I have a data.table of values for which I compute summarized statistics in a loop, and seek to aggregate the summarized results for additional processing. However, the loop processing time is unexpectedly lengthy as a result of the aggregation, and I'm seeking a faster solution.</p>
  
  <p>The method very much resembles the approach discussed here (<a href="https://stackoverflow.com/questions/37633187/assign-a-vector-to-a-specific-existing-row-of-data-table-in-r">Assign a vector to a specific existing row of data table in R</a>).</p>
  
  <p>Code (<strike>slightly abridged for readability, but material components preserved</strike> illustrative):</p>
  
  <pre><code>library(data.table);
  x &lt;- data.table(matrix(double(),nrow=10000,ncol=120));
  system.time({for (i in NROW(x):1) {
      m &lt;- matrix(rnorm(8*15),nrow=8,ncol=15);
  }});
  #  user  system elapsed 
  # 0.165   0.006   0.171 
  system.time({for (i in NROW(x):1) {
      m &lt;- matrix(rnorm(8*15),nrow=8,ncol=15);
      as.list(t(m[1:8,]));
  }});
  #   user  system elapsed 
  #  0.245   0.001   0.249
  system.time({for (i in NROW(x):1) {
      m &lt;- matrix(rnorm(8*15),nrow=8,ncol=15);
      x[i,] &lt;- as.list(t(m[1:8,]));
  }});
  #   user  system elapsed 
  # 36.227   0.682  37.529
  </code></pre>
  
  <p><strike></p>
  
  <pre><code># Obtain input data.table
  inputdt &lt;- fread('filename');
  
  # Preallocate summary statistics aggregate
  sumstatsdt &lt;- data.table(matrix(double(),nrow=10000,ncol=120));
  
  # Loop over input data.table (the *apply suite not suitable for mypkg::calcstats())
  for (i in NROW(inputdt):1) {
      # Produce a matrix of summary statistics for the row (of type double)
      sumstat_matrix &lt;- mypkg::calcstats(inputdt,...);
  
      # Aggregate the summary statistics (where "a","b","c",... are matrix row names of ordered statistics)
      # &gt;&gt;&gt;&gt; This is the operation that leads to lengthy execution time
      sumstatsdt[i,] &lt;- as.list(t(sumstat_matrix[c("a","b","c",...),]));
  };
  </code></pre>
  
  <p></strike>
  The input data.table contains 10,000 observations with 8 attributes, and there are a total of 1.2 million summary statistics to be stored (each of type 'double'). When commenting out the last line in the loop that  performs the aggregation, the total processing time is around <strong>24 sec</strong>. When run with the aggregation, total processing time increases to <strong>34 min</strong>.</p>
  
  <p>I have tried to use comparable code with <code>data.frame</code> and <code>cbind()</code> with loosely similar performance results (have not had a chance to try the  <code>tidyverse</code> suite). Recognize that deep copy operations will be somewhat slower, though the magnitude of the execution time difference given the relatively small dataset seems to indicate a different issue. </p>
  
  <p>Running R v3.4.4, data.table v1.11.4 on recent Fedora install. Memory usage is negligible (less than 3% of system RAM in use during R script execution). One of the 2.1GHz CPU processors with affinity to the R session runs at nearly 100% for the duration of the script execution. There are no other processes associated with that core and remaining cores are largely idle. (<strong>NB</strong>: Illustrative code run in KVM guest on different machine)</p>
  
  <p>Sidenote: Also curious as to why CPU bottleneck manifests in what otherwise appears to be a memory issue.</p>
  
  <p>Appreciate the time, and happy to provide additional info as useful.</p>
  
  <h3>Edit [2018.10.31]</h3>
  
  <ul>
  <li>Include illustrative code requested by <a href="https://stackoverflow.com/users/1855677/42">42</a></li>
  </ul>