title: Pandas: Merge two dataframes with duplicate rows
body: <p><strong>Short Question</strong>
  Within Pandas, what's the most convenient way to merge two dataframes, such that all entries in the left dataframe receive the first matching value from the right dataframe?</p>
  
  <p><strong>Longer Question</strong>
  Say I have two spreadsheets: <code>people.csv</code> and <code>orders.csv</code>. <code>people.csv</code> contains several columns of information on the person, whereas <code>orders.csv</code> contains the person's full name, and a row of the # of orders that person placed.</p>
  
  <p>I need to create a third csv, <code>output.csv</code> which contains all of the columns from <code>people.csv</code> plus a column from <code>output.csv</code> matched on one of the columns within both spreadsheets (called "FULL_NAME" in one, and "CUSTOMER_FULL_NAME" in the other)</p>
  
  <p><code>people.csv</code> is sorted on the FULL_NAME field, but contains duplicate rows, such that there are multiple rows with "John Smith" in the FULL_NAME column. There are also duplicate rows within <code>orders.csv</code> but not the same number of duplicates (for example, <code>people.csv</code> may have 4 John Smith entries, but <code>orders.csv</code> may only have two).</p>
  
  <p>If I use the following code:</p>
  
  <pre><code>people = pd.read_csv('people.csv')
  orders = pd.read_csv('orders.csv')
  full = pd.merge(
      people,
      orders,
      left_on='FULL_NAME',
      right_on='CUSTOMER_FULL_NAME',
  )
  result.to_csv("output.csv")
  </code></pre>
  
  <p>... I get a CSV where only two of the rows with "John Smith" in the FULL_NAME field has John Smith's number of orders. The rows directly below have no value in that field. That's because <code>output.csv</code> only contained two rows with matching values for John Smith, whereas <code>people.csv</code> had 4.</p>
  
  <p>Is there a convenient method within Pandas to set the value of one column to the first matching column in the other dataframe, such that all 4 entries contain the first matching value from <code>orders.csv</code>?</p>
  
  <p><strong>EDIT</strong>
  Full current version of my script, returning CSVs containing rows not set with the expected values:</p>
  
  <pre><code>import pandas as pd
  
  community = pd.read_csv("orders.csv")
  full = pd.read_csv("people.csv")
  result = pd.merge(
      full,
      community.drop_duplicates(subset=['FULL_NAME'], keep='first'),
      left_on="CUSTOMER_FULL_NAME",
      right_on="FULL_NAME",
      how='left',
  )
  result.to_csv("output.csv")
  </code></pre>
  
  <p>So I think I'm missing something else here, because some of the rows are matching in the expected way. Here's an example from the ouput file:</p>
  
  <pre><code>ID      FULL_NAME   EMPLOYER            DIVISION            ORDER #
  7350    John Smith  RiteAid             Clinical Research   25
  7351    John Smith  RiteAid             Clinical Research   25
  7352    John Smith  Costco              Sales   
  7353    John Smith  Costco              Sales   
  </code></pre>
  
  <p>This John Smith rows doesn't have a duplicate value within the <code>orders.csv</code> file so I do think this is working since two of the rows got it. However, I didn't get a match on the John Smith rows that list Costco rather than RiteAid (or other different fields). This surprises me since I thought the index check was only on the FULL_NAME field.</p>
  
  <p>Any ideas on why the other rows might not be filled in?</p>