Match Dataframes by Time Difference Threshold

Question

I have two dataframes and would like to match them by time stamp. For example:

A    
    Time                X
0   05-01-2017 09:08    3
1   05-01-2017 09:09    6
2   07-01-2017 09:09    5
3   07-01-2017 09:19    4
4   07-01-2017 09:19    8
5   07-02-2017 09:19    7
6   07-02-2017 09:19    5

B    
    Time                Y
0   06-01-2017 14:45    1
1   04-01-2017 03:31    9
2   07-01-2017 03:31    4
3   07-01-2017 14:57    5
4   09-01-2017 14:57    7

There is too much data to compare each item from df_A against each item from df_B. I would instead like to find each match that is within a controlled time threshold, for example 2 days. That is:

dT = Time A – Time B
-2 < dT < 2

The result should be:

C                        
Index A Time A          X   Index B Time B          Y   dT
0   05-01-2017 09:08    3   0   06-01-2017 14:45    1   -1.2
0   05-01-2017 09:08    3   1   04-01-2017 03:31    9   1.2
0   05-01-2017 09:08    3   2   07-01-2017 03:31    4   -1.8
1   05-01-2017 09:09    6   0   06-01-2017 14:45    1   -1.2
1   05-01-2017 09:09    6   1   04-01-2017 03:31    9   1.2
1   05-01-2017 09:09    6   2   07-01-2017 03:31    4   -1.8
2   07-01-2017 09:09    5   0   06-01-2017 14:45    1   0.8
2   07-01-2017 09:09    5   2   07-01-2017 03:31    4   0.2
2   07-01-2017 09:09    5   3   07-01-2017 14:57    5   -0.2
3   07-01-2017 09:19    4   0   06-01-2017 14:45    1   0.8
3   07-01-2017 09:19    4   2   07-01-2017 03:31    4   0.2
3   07-01-2017 09:19    4   3   07-01-2017 14:57    5   -0.2
4   07-01-2017 09:19    8   0   06-01-2017 14:45    1   0.8
4   07-01-2017 09:19    8   2   07-01-2017 03:31    4   0.2
4   07-01-2017 09:19    8   3   07-01-2017 14:57    5   -0.2
5   07-02-2017 09:19    7                
6   07-02-2017 09:19    5                
                            4   09-01-2017 14:57    7

I’ve tried the following code but it’s not working:

import pandas as pd
import datetime as dt
from   datetime import timedelta

# Data
df_A = pd.DataFrame({'X':[3,6,5,4,8,7,5], 'Time_A': [dt.datetime(2017,1,5,9,8),   dt.datetime(2017,1,5,9,9),  dt.datetime(2017,1,7,9,19), dt.datetime(2017,1,7,9,19),  dt.datetime(2017,1,7,9,19), dt.datetime(2017,2,7,9,19), dt.datetime(2017,2,7,9,19)]})
df_B = pd.DataFrame({'Y':[1,9,4,5,7],     'Time_B': [dt.datetime(2017,1,6,14,45), dt.datetime(2017,1,4,3,31), dt.datetime(2017,1,7,3,31), dt.datetime(2017,1,7,14,57), dt.datetime(2017,1,9,14,57)]})

# Match
def slice_datetime(Time, window):

return (Time + timedelta(hours=window)).strftime('%Y-%m-%d %H:%m')

lst = []
for Time in df_A[['X', 'Time_A']].iterrows():
    tmp = df_B.ix[slice_datetime(Time,-48):slice_datetime(Time,48)] # Define the time threshold (hours)
    if not tmp.empty:
        _match = pd.DataFrame()
        for Time_A, (X, Y, Time_B) in tmp.iterrows():
            lst.append([X, Y, Time_A, Time_B])

df_C = pd.DataFrame(lst, columns = ['X', 'Y', 'Time_A', 'Time_B'])

score 1 · Answer 1 · answered Jun 06 '18 at 12:43

1

you can create two new columns with the time boundaries

df_A["start_date"] = df_A["Time_A"]+datetime.timedelta(days=-2)
df_A["end_date"] = df_A["Time_A"]+datetime.timedelta(days=2)

then join the two dataframe with the condition

(df_B.Time_B >= df_A.start_date)&(df_B.Time_B <= df_A.end_date)

hope this helps!

answered Jun 06 '18 at 12:43

Giuseppe Bonavita

349
3
10

Thanks that approack looks amazing. I have tried it and got the error "ValueError: Can only compare identically-labeled Series objects". – R. Cox Jun 06 '18 at 12:53
It would be good if I could get this approach to work as I think that I need it for the case where I am matching a time period. https://stackoverflow.com/questions/51187462/join-dataframes-by-time-period – R. Cox Aug 01 '18 at 08:52

score 1 · Accepted Answer · answered Jun 06 '18 at 14:47

Here is an idea how to do it without loop for:

import pandas as pd
df_A = pd.DataFrame({'X':[3,6,5,4,8,7,5], 
                     'Time_A': [pd.datetime(2017,1,5,9,8),   pd.datetime(2017,1,5,9,9),  
                                pd.datetime(2017,1,7,9,19), pd.datetime(2017,1,7,9,19),  
                                pd.datetime(2017,1,7,9,19), pd.datetime(2017,2,7,9,19), 
                                pd.datetime(2017,2,7,9,19)]})
df_B = pd.DataFrame({'Y':[1,9,4,5,7],     
                     'Time_B': [pd.datetime(2017,1,6,14,45), pd.datetime(2017,1,4,3,31), 
                                pd.datetime(2017,1,7,3,31), pd.datetime(2017,1,7,14,57), 
                                pd.datetime(2017,1,9,14,57)]})

#first reset_index and rename
df_A = df_A.reset_index().rename(columns = {'index':'index_A'})
df_B = df_B.reset_index().rename(columns = {'index':'index_B'})

#then create a list of index_B where time_B is within 2 days for each time_A
time_delta = pd.Timedelta(days=2) #check the documentation for more parameter
df_A['list_B'] = (df_A['Time_A'].apply(lambda time_A: 
                    df_B.index_B[(time_A - time_delta <= df_B['Time_B']) & 
                                 (time_A + time_delta >= df_B['Time_B'])].tolist()))

#now use pd.Series and stack, with reset_index drop and rename 
# for finally merge to achieve your goal 
df_C = (df_A.set_index(['index_A','Time_A','X'])['list_B']
            .apply(pd.Series).stack().astype(int)
            .reset_index().drop('level_3',1).rename(columns={0:'index_B'})
            .merge(df_B).sort_values('index_A'))

# Create the columns dT
df_C['dT'] = ((df_C['Time_A'] - df_C['Time_B']).dt.total_seconds()/(24.*3600.)).round(1)

#add the time from df_A and df_B without corresponding time in the other df
# using append and ~ with isin 
df_C = (df_C.append(df_A[~df_A['Time_A'].isin(df_C['Time_A'])].drop('list_B',1))
    .append(df_B[~df_B['Time_B'].isin(df_C['Time_B'])]).fillna(''))

You might have to reorder your columns after but you should get the output you want

Thanks this approach works with the dummy data. When I tried it with the real data I got the error "df_A = df_A.reset_index().rename(columns = {'index':'index_A'}) # AttributeError: 'function' object has no attribute 'reset_index'" — R. Cox, Jun 07 '18 at 09:42
@R.Cox for your real data, can you try type(df_A) because the error seems to indicate that df_A is not a pandas.Dataframe as in your example. — Ben.T, Jun 07 '18 at 10:13
Thanks. I had been copying my dataframe to df_A = my_df.copy. I found that it worked when instead of doing that I changed the code to replace df_A with my dataframe. I don't know why that made it work. — R. Cox, Jun 07 '18 at 12:06
@R.Cox so I reproduce your error I think, you should add parenthesis at the end of `df_A = my_df.copy` as `copy` is a function, such as `df_A = my_df.copy()` and you should not get this error anymore — Ben.T, Jun 07 '18 at 13:01

Match Dataframes by Time Difference Threshold

2 Answers2