title: Optimal way of multiprocessing a rowwise matching operation between two data frames
body: <p>I'm working on an entity resolution task with large databases (<code>df1</code> ~0.5 mil. rows, <code>df2</code> up to 18 mil. rows).</p>
  <p>In <code>df1</code> I have first and last names, with first names being in regex form to allow for multiple variations of the same name - I didn't bother including it in the attached example, but the string values look something like: <code>^(^robert$|^rob$|^robb$)$</code>).</p>
  <p>In <code>df2</code> I have regular first and last names.</p>
  <p>My approach is to go through <code>df1</code> row by row, note the last name and first name regex, then filter <code>df2</code> first for an exact last name match, then for the first name regex match.</p>
  <p>This is simulated in the below code.</p>
  <pre><code>library(dplyr)
  library(data.table)
  
  set.seed(1)
  
  df1 &lt;- data.table(id1=sprintf(&quot;A%s&quot;,1:10000),
                    fnreg1=stringi::stri_rand_strings(n=10000,length=2,pattern=&quot;[a-z]&quot;),
                    lname1=stringi::stri_rand_strings(n=10000,length=2,pattern=&quot;[a-z]&quot;)) %&gt;%
    dplyr::mutate(fnreg1 = paste0(&quot;^(&quot;,fnreg1,&quot;)$&quot;))
  
  df2 &lt;- data.table(id2=sprintf(&quot;B%s&quot;,1:100000),
                    fname2=stringi::stri_rand_strings(n=100000,length=2,pattern=&quot;[a-z]&quot;),
                    lname2=stringi::stri_rand_strings(n=100000,length=2,pattern=&quot;[a-z]&quot;))
  
  
  process_row &lt;- function(i){
    
    rw &lt;- df1[i,]
    
    fnreg &lt;- rw$fnreg1
    ln &lt;- rw$lname1
    
    ln.match &lt;- df2[lname2==ln, ]
    out.match &lt;- ln.match[grepl(fnreg, fname2), ]
    
    return(cbind(rw,out.match))
    
  }
  
  ## 16 seconds
  tictoc::tic()
  out &lt;- lapply(1:nrow(df1), process_row) %&gt;% do.call(rbind,.) %&gt;% na.omit()
  tictoc::toc()
  </code></pre>
  <p>The <code>lapply</code> format I want to keep for parallelizing. I use the following code, note I'm on Windows so I need to prepare the clusters to get it working:</p>
  <pre><code>library(parallel)
  prep_cluster &lt;- function(export_vars){
    
    cl &lt;- makeCluster(detectCores()-1)
    clusterEvalQ(cl, library(dplyr))
    clusterEvalQ(cl, library(data.table))
    clusterExport(cl, export_vars)
    return(cl)
    
  }
  
  cl &lt;- prep_cluster(list(&quot;df1&quot;,&quot;df2&quot;,&quot;process_row&quot;))
  
  ## 2 seconds
  tictoc::tic()
  out.p &lt;- parLapply(cl, 1:nrow(df1), process_row) %&gt;% do.call(rbind,.) %&gt;% na.omit()
  tictoc::toc()
  
  stopCluster(cl)
  </code></pre>
  <p>For my large datasets, my code works pretty slowly. I'm almost certain that the way I defined <code>process_row</code> is very poorly optimized. But I'm not sure how to change the function to be faster and still conform to the <code>parLapply</code> format.</p>
  <p>Any tips appreciated.</p>
  <p>EDIT: I'm pretty short on memory, working with only 32GB - so I need to optimize it that way too.</p>
  <p>For the largest data files (18 mil rows) I am splitting them into chunks and matching each chunk separately.</p>