title: SQL like joins in pandas
body: <p>I have two dataframes, the first is of the form (note that the dates are datetime objects):</p>
  
  <pre><code>df = DataFrame('key': [0,1,2,3,4,5],
                 'date': [date0,date1, date2, date3, date4, date5],
                 'value': [0,10,20,30,40,50])
  </code></pre>
  
  <p>And a second which is of the form:</p>
  
  <pre><code>df2 = DataFrame('key': [0,1,2,3,4,5],
                  'valid_from': [date0, date0, date0, date3, date3, date3],
                  'valid_to': [date2, date2, date2, date5, date5, date5],
                  'value': [0, 100, 200, 300, 400, 500])
  </code></pre>
  
  <p>And I'm trying to efficiently join where the keys match and the date is between the valid_from and valid_to.  What I've come up with is the following:</p>
  
  <pre><code>def map_keys(df2, key, date):
      value = df2[df2['key'] == key &amp; 
          df2['valid_from'] &lt;= date &amp; 
          df2['valid_to'] &gt;= date]['value'].values[0]
      return value
  
  keys = df['key'].values
  dates = df['date'].values
  keys_dates = zip(keys, dates)
  
  values = []
  for key_date in keys_dates:
      value = map_keys(df2, key_date[0], key_date[1])
      values.append(value)
  
  df['joined_value'] = values
  </code></pre>
  
  <p>While this seems to do the job it doesn't feel like a particularly elegant solution.  I was wondering if anybody had a better idea for a join such as this.</p>
  
  <p>Thanks for you help - it is much appreciated.</p>