pandas: merge conditional on time range

datetime, pandas, python

Solution

You can use `merge_asof`, which is new in Pandas 0.19, to do most of the work. Then, combine `loc` and `duplicated` to remove secondary matches:

# Data needs to be sorted for merge_asof.
data = data.sort_values(by='Timestamp')

# Perform the merge_asof.
df = pd.merge_asof(data, data2, left_on='Timestamp', right_on='time', by='myID').drop('time', axis=1)

# Make the additional matches null.
df.loc[df['specialID'].duplicated(), 'specialID'] = np.nan

# Get the original ordering.
df = df.set_index(data.index).sort_index()

The resulting output:

             Timestamp myID specialID
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

Problem

I'd like to merge one data frame with another, where the merge is conditional on the date/time falling in a particular range. For example, let's say I have the following two data frames. ``` 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 ``` I would like to construct the following output. ``` # 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 ``` In particular, I want to merge `special_ID` into `data` such that `Timestamp` is the first time occurring after the value of `time`. For example, `foo_0` would be in the row corresponding to `2016-01-01 01:00:00` with `myID = 002` since that is the next time in `data` immediately following `2016-01-01 00:06:00` (the `time` of `special_ID = foo_0`) among the rows containing `myID = 002`. Note, `Timestamp` is not the index of `data` and `time` is not the index of `data2`. Most other related posts seem to rely on using the datetime object as the index of the data frame.

Original source