pandas: merge conditional on time range
<p>I'd like to merge one data frame with another, where the merge is conditional on the date/time falling in a particular range. </p>

<p>For example, let's say I have the following two data frames.</p>

<pre><code>import pandas as pd
import datetime

# Create main data frame.
data = pd.DataFrame()
time_seq1 = pd.DataFrame(pd.date_range('1/1/2016', periods=3, freq='H'))
time_seq2 = pd.DataFrame(pd.date_range('1/2/2016', periods=3, freq='H'))
data = data.append(time_seq1, ignore_index=True)
data = data.append(time_seq1, ignore_index=True)
data = data.append(time_seq1, ignore_index=True)
data = data.append(time_seq2, ignore_index=True)
data['myID'] = ['001','001','001','002','002','002','003','003','003','004','004','004']
data.columns = ['Timestamp', 'myID']

# Create second data frame.
data2 = pd.DataFrame()
data2['time'] = [pd.to_datetime('1/1/2016 12:06 AM'), pd.to_datetime('1/1/2016 1:34 AM'), pd.to_datetime('1/2/2016 12:25 AM')]
data2['myID'] = ['002', '003', '004']
data2['specialID'] = ['foo_0', 'foo_1', 'foo_2']

# Show data frames.
data
             Timestamp myID
0  2016-01-01 00:00:00  001
1  2016-01-01 01:00:00  001
2  2016-01-01 02:00:00  001
3  2016-01-01 00:00:00  002
4  2016-01-01 01:00:00  002
5  2016-01-01 02:00:00  002
6  2016-01-01 00:00:00  003
7  2016-01-01 01:00:00  003
8  2016-01-01 02:00:00  003
9  2016-01-02 00:00:00  004
10 2016-01-02 01:00:00  004
11 2016-01-02 02:00:00  004

data2
                 time myID specialID
0 2016-01-01 00:06:00  002     foo_0
1 2016-01-01 01:34:00  003     foo_1
2 2016-01-02 00:25:00  004     foo_2
</code></pre>

<p>I would like to construct the following output. </p>

<pre><code># Desired output.
             Timestamp myID special_ID
0  2016-01-01 00:00:00  001        NaN
1  2016-01-01 01:00:00  001        NaN
2  2016-01-01 02:00:00  001        NaN
3  2016-01-01 00:00:00  002        NaN
4  2016-01-01 01:00:00  002      foo_0
5  2016-01-01 02:00:00  002        NaN
6  2016-01-01 00:00:00  003        NaN
7  2016-01-01 01:00:00  003        NaN
8  2016-01-01 02:00:00  003      foo_1
9  2016-01-02 00:00:00  004        NaN
10 2016-01-02 01:00:00  004      foo_2
11 2016-01-02 02:00:00  004        NaN
</code></pre>

<p>In particular, I want to merge <code>special_ID</code> into <code>data</code> such that <code>Timestamp</code> is the first time occurring after the value of <code>time</code>. For example, <code>foo_0</code> would be in the row corresponding to <code>2016-01-01 01:00:00</code> with <code>myID = 002</code> since that is the next time in <code>data</code> immediately following <code>2016-01-01 00:06:00</code> (the <code>time</code> of <code>special_ID = foo_0</code>) among the rows containing <code>myID = 002</code>. </p>

<p>Note, <code>Timestamp</code> is not the index of <code>data</code> and <code>time</code> is not the index of <code>data2</code>. Most other related posts seem to rely on using the datetime object as the index of the data frame.</p>

<p>Not very beautiful, but i think it works.</p>

<pre><code>data['specialID'] = None
foolist = list(data2['myID'])
for i in data.index:
    if data.myID[i] in foolist:
        if data.Timestamp[i]&gt; list(data2[data2['myID'] == data.myID[i]].time)[0]:
            data['specialID'][i] = list(data2[data2['myID'] == data.myID[i]].specialID)[0]
            foolist.remove(list(data2[data2['myID'] == data.myID[i]].myID)[0])

In [95]: data
Out[95]:
             Timestamp myID specialID
0  2016-01-01 00:00:00  001      None
1  2016-01-01 01:00:00  001      None
2  2016-01-01 02:00:00  001      None
3  2016-01-01 00:00:00  002      None
4  2016-01-01 01:00:00  002     foo_0
5  2016-01-01 02:00:00  002      None
6  2016-01-01 00:00:00  003      None
7  2016-01-01 01:00:00  003      None
8  2016-01-01 02:00:00  003     foo_1
9  2016-01-02 00:00:00  004      None
10 2016-01-02 01:00:00  004     foo_2
11 2016-01-02 02:00:00  004      None
</code></pre>

python datetime pandas