pandas python

SQL like joins in pandas

I have two dataframes, the first is of the form (note that the dates are datetime objects):

df = DataFrame('key': [0,1,2,3,4,5],
'date': [date0,date1, date2, date3, date4, date5],
'value': [0,10,20,30,40,50])

And a second which is of the form:

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])

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:

def map_keys(df2, key, date):
value = df2[df2['key'] == key &
df2['valid_from'] <= date &
df2['valid_to'] >= 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])
df['joined_value'] = values

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.

Thanks for you help – it is much appreciated.