title: pandas: grouping by key to cluster messy strings
body: <p>I have a table that looks like this:</p>
  
  <pre><code>company_id,company_name
  1,Amazon
  1,Amazon Ltd
  2,Google
  1,Amazon
  2,Gogle
  3,Facebook Ltd
  3,Facebook LTD
  1,AMAZON
  1,AMAZON LTD
  2,GOOGLE
  3,Facebook
  3,Face book
  </code></pre>
  
  <p>So I have a unique identifier for each company, but their textual representation differs. What I strive for is to eliminate these inconsistencies and have something along the lines of:</p>
  
  <pre><code>company_id,company_name
  1,Amazon
  1,Amazon
  2,Google
  1,Amazon
  2,Google
  3,Facebook
  3,Facebook
  1,Amazon
  1,Amazon
  2,Google
  3,Facebook
  3,Facebook
  </code></pre>
  
  <p>I'm not dead set on the selection criterion - it can be the most common value within said group, it can be a random one. But what I need is something efficient, because my table has grown to contain millions of rows.</p>
  
  <p>My solution was to create a hash map of unique combos of <code>id -&gt; name</code> and then replacing based on these. Something along the lines of:</p>
  
  <pre><code>dd = df.drop_duplicates().set_index('company_id').to_dict()['company_name']
  df.company_name = df.company_id
  df.company_name = df.company_name.replace(dd)
  </code></pre>
  
  <p>Which works fine on smaller sets, but it gets fairly slow and memory inefficient due to the large hash map it creates.</p>
  
  <p>I've also tried a <code>groupby</code> based on <code>company_id</code> and replacing all the <code>company_name</code>s within each group by a random value, but I couldn't get to modify the underlying dataframe (without <code>.loc</code>ing it, which is doubly inefficient).</p>
  
  <p>One last option that springs to mind is to create a separate frame with unique values (<code>df.drop_duplicates('company_id')</code>) and merge this with the original frame based on <code>company_id</code>, but it doesn't sound terribly efficient either.</p>