title: How to map pandas Groupby dataframe with sum values to another dataframe using non-unique column
body: <p>I have two pandas dataframe <strong>df1</strong> and <strong>df2</strong>. Where i need to find <code>df1['seq']</code> by doing a groupby on <code>df2</code> and taking the sum of the column <code>df2['sum_column']</code>. Below are sample data and my current solution.</p>
  
  <p><strong>df1</strong></p>
  
  <pre><code>id    code amount  seq
  234     3    9.8    ?
  213     3    18
  241     3    6.4
  543     3    2
  524     2    1.8
  142     2    14
  987     2    11
  658     3    17
  </code></pre>
  
  <p><strong>df2</strong></p>
  
  <pre><code>c_id  name role    sum_column
  1     Aus  leader    6
  1     Aus  client    1
  1     Aus  chair     7
  2     Ned  chair     8
  2     Ned  leader    3
  3     Mar  client    5
  3     Mar  chair     2
  3     Mar  leader    4
  
  grouped = df2.groupby('c_id')['sum_column'].sum()
  df3 = grouped.reset_index()
  </code></pre>
  
  <p><strong>df3</strong></p>
  
  <pre><code>c_id  sum_column
   1      14
   2      11
   3      11
  </code></pre>
  
  <p>The next step where am having issues is to map the <strong>df3</strong> to <strong>df1</strong> and conduct a conditional check to see if <code>df1['amount']</code> is greater then <code>df3['sum_column']</code>.</p>
  
  <pre><code>df1['seq'] = np.where(df1['amount'] &gt; df1['code'].map(df3.set_index('c_id')[sum_column]), 1, 0)
  </code></pre>
  
  <p>printing out <code>df1['code'].map(df3.set_index('c_id')['sum_column'])</code>, I get only <code>NaN</code> values.</p>
  
  <p>Does anyone know what am doing wrong here?</p>
  
  <p>Expected results:
  <strong>df1</strong></p>
  
  <pre><code>id    code amount  seq
  234     3    9.8    0
  213     3    18     1
  241     3    6.4    0
  543     3    2      0
  524     2    1.8    0
  142     2    14     1
  987     2    11     0
  658     3    17     1
  </code></pre>