基于时间戳的pandas合并，时间戳不完全匹配。

Question

有哪些方法可以合并时间戳不完全匹配的列？

DF1：

date    start_time  employee_id session_id
01/01/2016  01/01/2016 06:03:13 7261824 871631182

DF2:

->

DF2：

date    start_time  employee_id session_id
01/01/2016  01/01/2016 06:03:37 7261824 871631182

我可以按['date', 'employee_id', 'session_id']进行合并，但有时同一员工会在同一天有多个相同的会话，这会导致重复。如果我删除发生这种情况的行，那么我将失去有效的会话。

如果DF1的时间戳与DF2的时间戳相差不到5分钟，并且session_id和employee_id也匹配，是否有一种高效的方法进行合并？如果存在匹配记录，则时间戳始终稍晚于DF1，因为某个未来时刻会触发事件。

['employee_id', 'session_id', 'timestamp<5minutes']

编辑 - 我假设有人之前遇到过这个问题。

我考虑做以下操作：

1. 在每个数据框上获取我的时间戳
2. 创建一个列，该列是时间戳加上5分钟（四舍五入）
3. 创建一个列，该列是时间戳减去5分钟（四舍五入）

4. 创建一个10分钟间隔字符串以在文件中连接

   df1['low_time'] = df1['start_time'] - timedelta(minutes=5)
   df1['high_time'] = df1['start_time'] + timedelta(minutes=5)
   df1['interval_string'] = df1['low_time'].astype(str) + df1['high_time'].astype(str)
   

有人知道如何将这些5分钟的时间间隔四舍五入到最近的5分钟标记吗？

02:59:37 - 5 min = 02:55:00

02:59:37 + 5 min = 03:05:00

interval_string = '02:55:00-03:05:00'

pd.merge(df1, df2, how = 'left', on = ['employee_id', 'session_id', 'date', 'interval_string']

有人知道如何像这样调整时间吗？这似乎可行。你仍然基于日期、员工和会话进行匹配，然后寻找基本上在相同10分钟间隔或范围内的时间。

- trench

---

有趣的问题。天真的解决方案是按最接近5分钟的时间戳合并，但如果它们恰好在5分钟标记的不同侧，则会将某些会话留作单独的行。您可以通过随机偏移量迭代应用该过程，最多进行一定数量的迭代，这将产生更好的结果。最强大的解决方案是聚类算法，但这更难实现。 - Igor Raush

这个链接可能会提供一些灵感。 - Igor Raush

理想情况下，您希望在join操作中使用类似SQL的where子句，指定一个日期，并基于另一个日期设置两个边界。如果直接在数据库中完成此操作或使用内存数据库（如SQLite）进行操作，我建议这样做。如果您在pandas中这样做，可能需要使用一些巧技，而且如果您以数据库方式实现，仍然可以将结果提取到pandas中进行交互式处理。 - ely

@Lance，请问两个数据框中包含的会话是真正唯一的吗？即，仅在合并它们时才适用去重复操作吗？或者，在同一个数据框中，是否可能存在“相同”会话的两行记录，其时间戳略有不同？ - Igor Raush

换句话说，DF1中的单个会话在DF2中是否保证恰好有零个或一个重复项？ - Igor Raush

Answer 1

我建议在pandas中使用这种方法:

pandas.merge_asof()

你需要注意的参数是direction,tolerance,left_on, 和 right_on

结合@Igor的回答:

import pandas as pd
from pandas import read_csv
from io import StringIO

# datetime column (combination of date + start_time)
dtc = [['date', 'start_time']]

# index column (above combination)
ixc = 'date_start_time'

df1 = read_csv(StringIO(u'''
date,start_time,employee_id,session_id
01/01/2016,02:03:00,7261824,871631182
01/01/2016,06:03:00,7261824,871631183
01/01/2016,11:01:00,7261824,871631184
01/01/2016,14:01:00,7261824,871631185
'''), parse_dates=dtc)

df2 = read_csv(StringIO(u'''
date,start_time,employee_id,session_id
01/01/2016,02:03:00,7261824,871631182
01/01/2016,06:05:00,7261824,871631183
01/01/2016,11:04:00,7261824,871631184
01/01/2016,14:10:00,7261824,871631185
'''), parse_dates=dtc)



df1['date_start_time'] = pd.to_datetime(df1['date_start_time'])
df2['date_start_time'] = pd.to_datetime(df2['date_start_time'])

# converting this to the index so we can preserve the date_start_time columns so you can validate the merging logic
df1.index = df1['date_start_time']
df2.index = df2['date_start_time']
# the magic happens below, check the direction and tolerance arguments
tol = pd.Timedelta('5 minute')
pd.merge_asof(left=df1,right=df2,right_index=True,left_index=True,direction='nearest',tolerance=tol)

输出

date_start_time date_start_time_x   employee_id_x   session_id_x    date_start_time_y   employee_id_y   session_id_y

2016-01-01 02:03:00 2016-01-01 02:03:00 7261824 871631182   2016-01-01 02:03:00 7261824.0   871631182.0
2016-01-01 06:03:00 2016-01-01 06:03:00 7261824 871631183   2016-01-01 06:05:00 7261824.0   871631183.0
2016-01-01 11:01:00 2016-01-01 11:01:00 7261824 871631184   2016-01-01 11:04:00 7261824.0   871631184.0
2016-01-01 14:01:00 2016-01-01 14:01:00 7261824 871631185   NaT NaN NaN

- jcp

---

非常酷。这是几年后的事情了，所以我正在使用的代码非常老旧，而且我不打算更新它（因为我很久没看过的东西可能会出问题），但这是一个不错的功能，我会记在心里，以备其他问题。 - trench

这个join是如何首先在('employee_id'，'session_id')上进行的，而不仅仅是在最接近的date_start_time上？我认为你需要在merge_asof中加入一个by=参数。 - Hugo Zaragoza

Answer 2

考虑以下您问题的迷你版本：

from io import StringIO
from pandas import read_csv, to_datetime

# how close do sessions have to be to be considered equal? (in minutes)
threshold = 5

# datetime column (combination of date + start_time)
dtc = [['date', 'start_time']]

# index column (above combination)
ixc = 'date_start_time'

df1 = read_csv(StringIO(u'''
date,start_time,employee_id,session_id
01/01/2016,02:03:00,7261824,871631182
01/01/2016,06:03:00,7261824,871631183
01/01/2016,11:01:00,7261824,871631184
01/01/2016,14:01:00,7261824,871631185
'''), parse_dates=dtc)

df2 = read_csv(StringIO(u'''
date,start_time,employee_id,session_id
01/01/2016,02:03:00,7261824,871631182
01/01/2016,06:05:00,7261824,871631183
01/01/2016,11:04:00,7261824,871631184
01/01/2016,14:10:00,7261824,871631185
'''), parse_dates=dtc)

提供

>>> df1
      date_start_time  employee_id  session_id
0 2016-01-01 02:03:00      7261824   871631182
1 2016-01-01 06:03:00      7261824   871631183
2 2016-01-01 11:01:00      7261824   871631184
3 2016-01-01 14:01:00      7261824   871631185
>>> df2
      date_start_time  employee_id  session_id
0 2016-01-01 02:03:00      7261824   871631182
1 2016-01-01 06:05:00      7261824   871631183
2 2016-01-01 11:04:00      7261824   871631184
3 2016-01-01 14:10:00      7261824   871631185

当合并时，您希望将 df2[0:3] 视为 df1[0:3] 的副本（因为它们分别相差不到5分钟），但要将 df1[3] 和 df2[3] 视为单独的会话。

解决方案1：区间匹配

这基本上就是您在编辑中建议的。您想要将两个表中的时间戳映射到以最接近5分钟舍入的时间戳为中心的10分钟间隔。

每个间隔可以通过其中点唯一表示，因此您可以在最接近5分钟的时间戳上合并数据框。例如：

import numpy as np

# half-threshold in nanoseconds
threshold_ns = threshold * 60 * 1e9

# compute "interval" to which each session belongs
df1['interval'] = to_datetime(np.round(df1.date_start_time.astype(np.int64) / threshold_ns) * threshold_ns)
df2['interval'] = to_datetime(np.round(df2.date_start_time.astype(np.int64) / threshold_ns) * threshold_ns)

# join
cols = ['interval', 'employee_id', 'session_id']
print df1.merge(df2, on=cols, how='outer')[cols]

打印

             interval  employee_id  session_id
0 2016-01-01 02:05:00      7261824   871631182
1 2016-01-01 06:05:00      7261824   871631183
2 2016-01-01 11:00:00      7261824   871631184
3 2016-01-01 14:00:00      7261824   871631185
4 2016-01-01 11:05:00      7261824   871631184
5 2016-01-01 14:10:00      7261824   871631185

请注意，这并不完全正确。尽管它们相差只有3分钟，但会话 df1 [2] 和 df2 [2] 不被视为重复项，因为它们位于时间间隔边界的两侧。

解决方案2：一对一匹配

这是另一种方法，它依赖于以下条件：在 df1 中的会话只能在 df2 中具有零个或一个副本。

我们用与 employee_id 和 session_id 匹配且距离少于5分钟的最接近的 df2 中的时间戳替换 df1 中的时间戳。

from datetime import timedelta

# get closest match from "df2" to row from "df1" (as long as it's below the threshold)
def closest(row):
    matches = df2.loc[(df2.employee_id == row.employee_id) &
                      (df2.session_id == row.session_id)]

    deltas = matches.date_start_time - row.date_start_time
    deltas = deltas.loc[deltas <= timedelta(minutes=threshold)]

    try:
        return matches.loc[deltas.idxmin()]
    except ValueError:  # no items
        return row

# replace timestamps in "df1" with closest timestamps in "df2"
df1 = df1.apply(closest, axis=1)

# join
cols = ['date_start_time', 'employee_id', 'session_id']
print df1.merge(df2, on=cols, how='outer')[cols]

打印

      date_start_time  employee_id  session_id
0 2016-01-01 02:03:00      7261824   871631182
1 2016-01-01 06:05:00      7261824   871631183
2 2016-01-01 11:04:00      7261824   871631184
3 2016-01-01 14:01:00      7261824   871631185
4 2016-01-01 14:10:00      7261824   871631185

这种方法明显较慢，因为您需要每行在df1中搜索整个df2。我所编写的内容可能还可以进一步优化，但在大型数据集上仍将需要很长时间。

- Igor Raush

---

看起来对我来说是一个不错的开始。关于你的第一个解决方案，我们是否可以包括一个加减区间范围，以防止事件出现在区间错误的一侧？该区间将是一个字符串，就像我打出的示例一样。不确定逻辑是否完全正确，但我已经在Excel测试数据上运行过了。 - trench

我认为你的方法也会遇到同样的问题。考虑到你正在将一个连续时间范围映射到离散区间中，这意味着你总是可以想象出一对时间戳，在连续范围上非常接近，但却落在不同的区间内。虽然我不确定我的方法是否与你的完全等价（尽管我认为可能是），但总体思路是一致的。 - Igor Raush

哈哈，真是让人头疼。不过还是谢谢你，我稍后会测试一下并告诉你结果。这应该至少能提高我的匹配率。 - trench

刚刚意识到，使用 DatetimeIndex.snap 可以显著地改善间隔分箱的解决方案。直到现在才知道这个方法。 - Igor Raush

这看起来很有趣，但文档完全没有帮助。我以前没有使用过DateTimeIndex，所以我不太确定该如何继续。df1 = df1.set_index(pd.DatetimeIndex(df1['call_start'], drop=False))这似乎已经创建了索引，但我尝试了几次添加.snap，但没有成功。当我谷歌搜索时，我找不到一个好的示例来跟随。 - trench

我花了10分钟的时间，也没能让它工作，很抱歉。 - Igor Raush

Answer 3

我建议使用内置的pandas Series dt round函数，将两个数据框舍入到共同的时间点，例如每5分钟向上舍入。这样，时间将始终以01:00:00的格式显示，例如01:05:00。以此方式，两个数据框将具有类似的时间索引以执行合并。

请参阅文档和示例pandas.Series.dt.round。

- Nguyen Bryan