按时间段加入Dataframes
按时间段加入Dataframes
提问于 2018-07-05 09:11:07
我正在尝试加入两个具有时间戳行的数据格式。框架A由具有启动时间A1和结束时间A2的事件组成。B帧中的事件都只有一次。
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的更多事件，我在事件的开始和结束时增加了2天。
方法1
# 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回答 1
Stack Overflow用户
回答已采纳
发布于 2018-08-02 10:00:35
谢谢斯科特，这个答案很有效：
# 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))页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51187462
复制相关文章
相似问题
腾讯云开发者
