Finding first event in one table that occurred after event in a second table, per row
<p>I have a pandas DataFrame that looks like this:</p>

<pre><code>email    signup_date    
a@a.com   7/21/16
b@b.com   6/6/16
d@d.com   5/5/16
b@b.com   4/4/16
</code></pre>

<p>I have a second pandas DataFrame with related events, when a signup actually got followed through on, that looks like this:</p>

<pre><code>email    call_date    
a@a.com   7/25/16
b@b.com   6/20/16
b@b.com   5/4/16
</code></pre>

<p>There are a few things to keep in mind. </p>

<ul>
<li>Some of the <code>signup_date</code>s may not have any later <code>call_date</code> for the corresponding email. That is, some people sign up but never get a call back.</li>
<li>Some people signup more than once and get called after each signup.</li>
<li>Some people signup more than once but don't get called after each signup.</li>
<li>Some people signup more than once but may only get called after several signups. In this case we want to attribute the call to the most recent signup.</li>
</ul>

<p>Ultimately the goal is to determine whether there is a call event that came after a sign up event for a given user but before the next sign up event for the same user, where the number of signup events per user is not known in advance. </p>

<p>Is there a <code>pandas</code> best practices way to do this? For now I'm using a for loop, and it's extremely slow (hasn't finished on 100,000 rows even after 20 minutes):</p>

<pre><code>response_date = []
for i in range(signups.shape[0]):
    unique_id = signups.unique_id.values[i]
    start_date = signups.signup_date.values[i]
    end_date = signups.signup_date.values[-1]
    if end_date is start_date:
        end_date = end_date + pd.Timedelta('1 year')
    tmp_df = calls[calls.unique_id == unique_id]
    tmp_df = tmp_df[tmp_df.timestamp &gt; start_date][tmp_df.timestamp &lt; end_date]
    tmp_df = tmp_df.sort_values('timestamp')
    if tmp_df.shape[0] &gt; 0 :
        response_date.append(tmp_df.timestamp.values[0])
    else:
        response_date.append(None)
</code></pre>

<p>Thanks for any advice!</p>

<p>Another solution with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_values.html" rel="nofollow"><code>sort_values</code></a> and aggregate <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.first.html" rel="nofollow"><code>first</code></a>:</p>

<pre><code>df = df1.merge(df2)
df = df[df.signup_date &lt;= df.call_date]
print (df.sort_values("signup_date", ascending=False)
         .groupby(['call_date', 'email'], as_index=False)
         .first())

   call_date    email signup_date
0 2016-05-04  b@b.com  2016-04-04
1 2016-06-20  b@b.com  2016-06-06
2 2016-07-25  a@a.com  2016-07-21
</code></pre>

python pandas