按时间段联接数据框 [英] Join Dataframes by Time Period

问题描述

我正在尝试合并两个带有时间戳行的数据框.帧A由具有开始时间A1和结束时间A2的事件组成. B帧中的每个事件只有一次.

I'm trying to join two dataframes that have time stamped rows. Frame A is made up of events with a start time A1 and an end time A2. The events in Frame B each have just the one time.

import pandas   as pd
import datetime as dt

# Data
df_A = pd.DataFrame({'A1': [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)],
                     'A2': [dt.datetime(2017,1,5,9,9),   dt.datetime(2017,1,5,9,12), dt.datetime(2017,1,7,9,26), dt.datetime(2017,1,7,9,20),  dt.datetime(2017,1,7,9,21), dt.datetime(2017,2,7,9,23), dt.datetime(2017,2,7,9,25)]})

df_B = pd.DataFrame({ '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)]})

# 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'})

为了在df_A中匹配更多事件，我在df_A中的事件开始和结束时额外增加了2天的时间.我尝试了各种方法来匹配它们:

I've added an extra 2 days at the start and at the end of the events in df_A in order to match more events from df_B. I've tried various methods to match them:

# List of index_B within dT Threshold
dT = pd.Timedelta(days=2)
def Match(t1, t2):
       return df_B.index_B(((t1 - dT <= df_B['B']) &
                            (t2 + dT >= df_B['B'])) .tolist())

df_A[['list_B']] = df_A[['A1', 'A2']].applymap(Match)

# Stack, Reset index, Drop, Rename, Merge
df_C = (df_A .set_index(['index_A','A1','A2'])['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'))

# Calculate dT
df_C['dT'] = ((df_C['A1'] - df_C['B']).dt.total_seconds()/(24.*3600.)).round(1)

# Add the Time
df_C = (df_C .append(df_A[~df_A['A1'] .isin(df_C['A1'])]  .drop('list_B',1))
             .append(df_B[~df_B['B']  .isin(df_C['B' ])]) .fillna(''))

方法1错误:

TypeError: ("Match() missing 1 required positional argument: 't2'", 'occurred at index A1')

方法2

from   datetime import timedelta

# Define the time interval
df_A["A1X"] = df_A["A1"] + dt.timedelta(days=-2)
df_A["A2X"] = df_A["A2"] + dt.timedelta(days= 2)

# Sort
df_A.sort_values('A1', inplace=True)
df_B.sort_values('B',  inplace=True)

# Join
df_C = df_A.join(df_B, on=(df_B.B >= df_A.A1X) & (df_B.B <= df_A.A2X), "inner")

方法2错误:

SyntaxError: positional argument follows keyword argument

方法3

from   datetime import timedelta

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

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

df_C = pd .DataFrame(lst, columns = ['A1', 'A2', 'B'])

方法3错误:

TypeError: can only concatenate tuple (not "datetime.timedelta") to tuple

推荐答案

感谢Scott，此答案有效:

Thanks Scott, this answer works:

# Define the time interval
df_A["A1X"] = df_A["A1"] + dt.timedelta(days=-2)
df_A["A2X"] = df_A["A2"] + dt.timedelta(days= 2)

Bv = df_B .B.values
A1 = df_A .A1X.values
A2 = df_A .A2X.values

i, j = np.where((Bv[:, None] >= A1) & (Bv[:, None] <= A2))

df_C = pd.DataFrame(np.column_stack([df_B .values[i], df_A .values[j]]),
                    columns = df_B .columns .append (df_A.columns))

这篇关于按时间段联接数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！