title: Adding new column using custom function in data frame using dplyr/data.table in R
body: <p>I'm relatively new to R programing and am trying to figure out how to use custom functions to evaluate new columns of a data frame using <code>dplyr</code> or <code>data.table</code> in a memory efficient manner. Can someone please help</p>
  
  <p>Here is a brief summary of my problem</p>
  
  <p>Data frames 1 and 2 have the same type and number of columns</p>
  
  <pre><code>df1 &lt;- data.frame(col1 = c("A", "B", "C"), col2 = c(10,20,30))
  df2 &lt;- data.frame(col1 = c("DA", "EE", "FB", "C"), col2 = c(10,20,30,40))
  </code></pre>
  
  <p>These data frames have millions of records.</p>
  
  <p>Now I want to add a new column to one of the data frames (say df1) by using the values in df2. </p>
  
  <pre><code>library(dplyr)
  
  calculateCol3 &lt;- function(word) {
  df2 %&gt;%
      filter(grepl(paste0(word, "$"),col1) )%&gt;%
      summarize(col3= sum(col2))
  col3
  }
  
  df1 %&gt;% group_by(col1) %&gt;% mutate(col3 = calcualteCol3(col1))
  </code></pre>
  
  <p>This method works but it is painfully slow and I guess this is because of copying the data sets too many times. Can someone suggest a better way of doing the same? The expected result is:</p>
  
  <pre><code>col1 col2 col3
     A   10   10
     B   20   30
     C   30   40 
  </code></pre>
  
  <p>I also tried converting the data frames to data.table as follows</p>
  
  <pre><code>dt1 &lt;- data.table(df1)
  dt2 &lt;- data.table(df2)
  
  dt1[, col3 := calculateCol3(col1)}, by = 1:nrow(dt1)]
  </code></pre>
  
  <p>Everything seems to be slow. Am sure there is a better way to achieve this. Can someone help</p>
  
  <p>Thanks</p>