title: Merging two Python dataframes using a custom function for approximate matching and threshold score
body: <p>I have two dataframes that contain web address and top level domains. df1 has ~million rows and df2 has ~700k rows. I need to merge the two dataframes to obtain the common web addresses and corresponding domains along with columns unique to each data frame. Because transcribing web addresses and domains can lead to spelling mistakes, I need to merge using approximate merging. </p>
  
  <p>Here is an example:</p>
  
  <p>df1</p>
  
  <pre><code>      address            tld     test
  0     google            .com    14100 
  1     stackoverflow     .net    19587
  2     yahoo!            .com    21633
  3     bbcc              .com     9633
  4     nytimes           .net    61933
  </code></pre>
  
  <p>df2</p>
  
  <pre><code>      address            tld     type
  0     google            .com        1 
  1     stackoverrfloow   .net        5
  2     bbc               .com        4
  4     nytimes           .com        1
  </code></pre>
  
  <p>Here is the output that I expect:</p>
  
  <p>output</p>
  
  <pre><code>      address            tld     test   type
  0     google            .com    14100      1
  1     stackoverflow     .net    19587      5 
  2     bbcc              .com     9633      4
  </code></pre>
  
  <p>I created a function that returns percentage match using Levenshtein distance. It is a simple function that takes two strings as inputs, and returns the percent match. For example:</p>
  
  <pre><code>string1 = "stackoverflow"
  string2 = "stackoverrfloow"
  pct_match = pctLevenshtein(string1, string2)
  </code></pre>
  
  <p>This gives me a percentage match of 0.87. How can I use this function, along with a threshold score above which the approximate match is good enough, to do approximate match on address and tld columns to create the output data frame? The output is only a sample and it may also pick "nytimes" depending on the threshold score. I have tried the following using difflib's get_close_matches to find approximate matches and then merge but this is not exactly what I am trying to do.</p>
  
  <pre><code>df2['key1'] = df2.address.map(lambda x: difflib.get_close_matches(x, df1.address)[0])
  df2['key2'] = df2.tld.map(lambda x: difflib.get_close_matches(x, df1.tld)[0])
  </code></pre>
  
  <p>Everything I have tried so far has not worked. I am looking for something like this to work: </p>
  
  <pre><code>df2['key1'] = df2['address'].map(lambda x: pctMatchLevenshtein(x, df1['address']) if pctMatchLevenshtein(x, df1['address'])&gt;0.85 else 0)
  </code></pre>
  
  <p>Any tips on how to proceed are greatly appreciated. Thanks!</p>