title: how to collapse/compress/reduce string columns in pandas
body: <p>Essentially, what I am trying to do is join Table_A to Table_B using a key to do a lookup in Table_B to pull column records for names present in Table_A. </p>
  
  <p>Table_B can be thought of as the master name table that stores various attributes about a name. Table_A represents incoming data with information about a name. </p>
  
  <p>There are two columns that represent a name - a column named '<strong>raw_name</strong>' and a column named '<strong>real_name</strong>'. The '<strong>raw_name</strong>' has the string "code_" before the <strong>real_name</strong>.</p>
  
  <p>i.e.</p>
  
  <pre><code>raw_name = CE993_VincentHanna
  
  real_name = VincentHanna
  </code></pre>
  
  <p>Key = real_name, which exists in Table_A and Table_B</p>
  
  <p>Please see the mySQL tables and query here:  <a href="http://sqlfiddle.com/#!9/65e13/1" rel="nofollow">http://sqlfiddle.com/#!9/65e13/1</a></p>
  
  <p>For all <strong>real_names</strong> in Table_A that DO-NOT exist in Table_B I want to store <strong>raw_name</strong>/<strong>real_name</strong> pairs into an object so I can send an alert to the data-entry staff for manual insertion. </p>
  
  <p>For all <strong>real_names</strong> in Table_A that DO exist in Table_B, which means we know about this name and can add the new <strong>raw_name</strong> associated with this <strong>real_name</strong> into our master Table_B</p>
  
  <p>In mySQL, this is easy to do as you can see in my sqlfidde example. I join on real_name and I compress/collapse the result by groupby <strong>a.real_name</strong> since I don't care if there are multiple records in Table_B for the same real_name. </p>
  
  <p>All I want is to pull the attributes (stats1, stats2, stats3) so I can assign them to the newly discovered raw_name. </p>
  
  <p>In the mySQL query result I can then separate the NULL records to be sent for manual data-entry and automatically insert the remaining records into Table_B. </p>
  
  <p>Now, I am trying to do the same in Pandas but am stuck at the point of groupby on <strong>real-name</strong>.  </p>
  
  <pre><code>e = {'raw_name': pd.Series(['AW103_Waingro', 'CE993_VincentHanna', 'EES43_NeilMcCauley', 'SME16_ChrisShiherlis',
                            'MEC14_MichaelCheritto', 'OTP23_RogerVanZant', 'MDU232_AlanMarciano']),
       'real_name': pd.Series(['Waingro', 'VincentHanna', 'NeilMcCauley', 'ChrisShiherlis', 'MichaelCheritto', 
                             'RogerVanZant', 'AlanMarciano'])}
  
  f = {'raw_name': pd.Series(['SME893_VincentHanna', 'TVA405_VincentHanna', 'MET783_NeilMcCauley', 
                              'CE321_NeilMcCauley', 'CIN453_NeilMcCauley', 'NIPS16_ChrisShiherlis',
                              'ALTW12_MichaelCheritto', 'NSP42_MichaelCheritto', 'CONS23_RogerVanZant',
                              'WAUE34_RogerVanZant']),
       'real_name': pd.Series(['VincentHanna', 'VincentHanna', 'NeilMcCauley', 'NeilMcCauley', 'NeilMcCauley',
                               'ChrisShiherlis', 'MichaelCheritto', 'MichaelCheritto', 'RogerVanZant',
                               'RogerVanZant']),
       'stats1': pd.Series(['meh1', 'meh1', 'yo1', 'yo1', 'yo1', 'hello1', 'bye1', 'bye1', 'namaste1',
                            'namaste1']),
       'stats2': pd.Series(['meh2', 'meh2', 'yo2', 'yo2', 'yo2', 'hello2', 'bye2', 'bye2', 'namaste2',
                            'namaste2']),
       'stats3': pd.Series(['meh3', 'meh3', 'yo3', 'yo3', 'yo3', 'hello3', 'bye3', 'bye3', 'namaste3',
                            'namaste3'])}
  
  df_e = pd.DataFrame(e)
  df_f = pd.DataFrame(f)
  
  df_new = pd.merge(df_e, df_f, how='left', on='real_name', suffixes=['_left', '_right'])
  
  df_new_grouped = df_new.groupby(df_new['raw_name_left'])
  </code></pre>
  
  <p>Now how do I compress/collapse the groups in df_new_grouped on real-name like I did in mySQL.</p>
  
  <p>Once I have an object with the collapsed results I can slice the dataframe to report real_names we don't have a record of (NULL values) and those that we already know and can store the newly discovered raw_name. </p>