title: fuzzy and exact match of two databases
body: <p>I have two databases.  The first one has about 70k rows with 3 columns.  the second one has 790k rows with 2 columns.  Both databases have a common variable <code>grantee_name</code>.  I want to match each row of the first database to one or more rows of the second database based on this <code>grantee_name</code>.  Note that <code>merge</code> will not work because the <code>grantee_name</code> do not match perfectly.  There are different spellings etc.  So, I am using the <code>fuzzyjoin</code> package and trying the following:</p>
  <pre><code>library(&quot;haven&quot;); library(&quot;fuzzyjoin&quot;); library(&quot;dplyr&quot;)
  forfuzzy&lt;-read_dta(&quot;/path/forfuzzy.dta&quot;)
  filings &lt;- read_dta (&quot;/path/filings.dta&quot;)
  &gt; head(forfuzzy)
  # A tibble: 6 x 3
    grantee_name                 grantee_city grantee_state
    &lt;chr&gt;                        &lt;chr&gt;        &lt;chr&gt;        
  1 (ICS)2 MAINE CHAPTER         CLEARWATER   FL           
  2 (SUFFOLK COUNTY) VANDERBILT~ CENTERPORT   NY           
  3 1 VOICE TREKKING A FUND OF ~ WESTMINSTER  MD           
  4 10 CAN                       NEWBERRY     FL           
  5 10 THOUSAND WINDOWS          LIVERMORE    CA           
  6 100 BLACK MEN IN CHICAGO INC CHICAGO      IL   
  ... 7 - 70000 rows to go
  
  &gt; head(filings)
  # A tibble: 6 x 2
    grantee_name                       ein 
    &lt;chr&gt;                             &lt;dbl&gt;               
  1 ICS-2 MAINE CHAPTER              123456             
  2 SUFFOLK COUNTY VANDERBILT        654321            
  3 VOICE TREKKING A FUND OF VOICES  789456            
  4 10 CAN                           654987               
  5 10 THOUSAND MUSKETEERS INC       789123               
  6 100 BLACK MEN IN HOUSTON INC     987321      
  
  rows 7-790000 omitted for brevity
  </code></pre>
  <p>The above examples are clear enough to provide some good matches and some not-so-good matches.  Note that, for example, <code>10 THOUSAND WINDOWS</code> will match best with <code>10 THOUSAND MUSKETEERS INC</code> but it does not mean it is a good match.  There will be a better match somewhere in the <code>filings</code> data (not shown above).  That does not matter at this stage.</p>
  <p>So, I have tried the following:</p>
  <pre><code>df&lt;-as.data.frame(stringdist_inner_join(forfuzzy, filings, by=&quot;grantee_name&quot;, method=&quot;jw&quot;, p=0.1, max_dist=0.1, distance_col=&quot;distance&quot;))
  </code></pre>
  <p>Totally new to R.  This is resulting in an error:
  <code>cannot allocate vector of size 375GB</code> (with the big database of course).  A sample of 100 rows from <code>forfuzzy</code> always works.  So, I thought of iterating over a list of 100 rows at a time.</p>
  <p>I have tried the following:</p>
  <pre><code>n=100
  lst = split(forfuzzy, cumsum((1:nrow(forfuzzy)-1)%%n==0))
  
  df&lt;-as.data.frame(lapply(lst, function(df_)
  {
  (stringdist_inner_join(df_, filings, by=&quot;grantee_name&quot;, method=&quot;jw&quot;, p=0.1, max_dist=0.1, distance_col=&quot;distance&quot;, nthread = getOption(&quot;sd_num_thread&quot;)))
  }
  )%&gt;% bind_rows)
  </code></pre>
  <p>I have also tried the above with <code>mclapply</code> instead of <code>lapply</code>.  Same error happens even though I have tried a high-performance cluster setting 3 CPUs, each with 480G of memory and using <code>mclapply</code> with the option <code>mc.cores=3</code>.  Perhaps a <code>foreach</code> command could help, but I have no idea how to implement it.</p>
  <p>I have been advised to use the <code>purrr</code> and <code>repurrrsive</code> packages, so I try the following:</p>
  <pre><code>purrr::map(lst, ~stringdist_inner_join(., filings, by=&quot;grantee_name&quot;, method=&quot;jw&quot;, p=0.1, max_dist=0.1, distance_col=&quot;distance&quot;, nthread = getOption(&quot;sd_num_thread&quot;)))
  </code></pre>
  <p>This seems to be working, after a novice error in the <code>by=grantee_name</code> statement.  However, it is taking forever and I am not sure it will work.  A sample list in <code>forfuzzy</code> of 100 rows, with <code>n=10</code> (so 10 lists with 10 rows each) has been running for 50 minutes, and still no results.</p>