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><strong><em>setup</em></strong></p>

<pre><code>from StringIO import StringIO
import pandas as pd

txt1 = """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"""

df1 = pd.read_csv(StringIO(txt1), parse_dates=[1], delim_whitespace=True)

txt2 = """email    call_date    
a@a.com   7/25/16
b@b.com   6/20/16
b@b.com   5/4/16"""

df2 = pd.read_csv(StringIO(txt2), parse_dates=[1], delim_whitespace=True)
</code></pre>

<p><strong><em>merge</em></strong><br>
combine <code>df1</code> and <code>df2</code> on <code>email</code></p>

<pre><code>df = df1.merge(df2)
df
</code></pre>

<p><a href="http://i.stack.imgur.com/SHMab.png" rel="nofollow"><img src="http://i.stack.imgur.com/SHMab.png" alt="enter image description here"></a></p>

<p><strong><em>filter 1</em></strong><br>
get rid of rows where <code>call_date</code> is prior to <code>signup_date</code></p>

<pre><code>cond1 = df.signup_date.le(df.call_date)
cond1

0     True
1     True
2    False
3     True
4     True
dtype: bool
</code></pre>

<hr>

<pre><code>df = df[cond1]
df
</code></pre>

<p><a href="http://i.stack.imgur.com/YopRh.png" rel="nofollow"><img src="http://i.stack.imgur.com/YopRh.png" alt="enter image description here"></a></p>

<p><strong><em>filter 2</em></strong><br>
<code>groupby</code> <code>['email', 'call_date']</code> and get most recent with <code>idxmax</code></p>

<pre><code>most_recent = df.groupby(['call_date', 'email']).signup_date.idxmax()
most_recent

call_date   email  
2016-05-04  b@b.com    4
2016-06-20  b@b.com    1
2016-07-25  a@a.com    0
Name: signup_date, dtype: int64
</code></pre>

<p><strong><em>result</em></strong></p>

<pre><code>df.ix[most_recent]
</code></pre>

<p><a href="http://i.stack.imgur.com/ZwZQW.png" rel="nofollow"><img src="http://i.stack.imgur.com/ZwZQW.png" alt="enter image description here"></a></p>

<hr>

<p><strong><em>all together</em></strong></p>

<pre><code>df = df1.merge(df2)
cond1 = df.signup_date.le(df.call_date)
df = df[cond1]
most_recent = df.groupby(['call_date', 'email']).signup_date.idxmax()
df.ix[most_recent]
</code></pre>

<p><a href="http://i.stack.imgur.com/ZwZQW.png" rel="nofollow"><img src="http://i.stack.imgur.com/ZwZQW.png" alt="enter image description here"></a></p>

python pandas