title: Looping over data frame to cap and sum another data frame
body: <p>I am trying to use entries from <code>df1</code> to limit amounts in <code>df2</code>, then add them up based on their type and summarize in <code>df3</code>.  I'm not sure how to get it, the for loop using iterrows would be my best guess but it's not complete.</p>
  
  <p><strong>Code:</strong></p>
  
  <pre><code>import numpy as np
  import pandas as pd
  
  df1 = pd.DataFrame({'Caps':['25','50','100']})
  df2 = pd.DataFrame({'Amounts':['45','25','65','35','85','105','80'], \
                      'Type':   ['a' ,'b' ,'b' ,'c' ,'a' , 'b' ,'d' ]})
  df3 = pd.DataFrame({'Type':   ['a' ,'b' ,'c' ,'d']})
  
  df1['Caps'] = df1['Caps'].astype(float)
  df2['Amounts'] = df2['Amounts'].astype(float)
  
  for index1, row1 in df1.iterrows():
      for index2, row2 in df3.iterrows():
          df3[str(row1['Caps']+'limit')] = df2['Amounts'].where(
              df2['Type'] == row2['Type']).where(
              df2['Amounts']&lt;= row1['Caps'], row1['Caps']).sum()
  
  
  # My ideal output would be this:
  
  df3 = pd.DataFrame({'Type':['a','b','c','d'],
                      'Total':['130','195','35','80'],
                      '25limit':['50','75','25','25'],
                      '50limit':['95','125','35','50'],
                      '100limit':['130','190','35','80'],
                      })
  </code></pre>
  
  <p><strong>Output:</strong></p>
  
  <pre><code>&gt;&gt;&gt; df3
  
    Type Total 25limit 50limit 100limit
  0    a   130      50      95      130
  1    b   195      75     125      190
  2    c    35      25      35       35
  3    d    80      25      50       80
  </code></pre>