python - 如何将两个数据框与重叠日期时间范围内的列合并

Question

从这个问题开始 How to join two dataframes for which column values are within a certain range? .但是我的日期时间范围列可能会相互重叠。

例如:

>>>df_1

  timestamp              A           B
0 2019-07-14 05:31:00    0.020228    0.026572
1 2019-07-14 06:32:00    0.057780    0.175499
2 2019-07-14 07:02:00    0.076623    0.875499

>>>df_2

  start                  end                    event
0 2019-07-14 05:30:00    2019-07-14 06:30:00    E1
1 2019-07-14 06:00:00    2019-07-14 07:00:00    E2
2 2019-07-14 06:30:01    2019-07-14 07:30:00    E3
3 2019-07-14 07:30:01    2019-07-14 08:30:00    E4

我想在 df_2 的区间内找到 df_1 的 A。我期望的结果如下:

  start                  end                    event timestamp             A
0 2019-07-14 05:30:00    2019-07-14 06:30:00    E1    2019-07-14 05:31:00    0.020228
1 2019-07-14 06:00:00    2019-07-14 07:00:00    E2    2019-07-14 06:32:00    0.057780
2 2019-07-14 06:30:01    2019-07-14 07:30:00    E3    2019-07-14 06:32:00    0.057780
3 2019-07-14 06:30:01    2019-07-14 07:30:00    E3    2019-07-14 07:02:00    0.076623

我遵循了上面链接中的答案，但我没有使用 pandas 方法来实现我的目标。当我尝试使用得票最高的答案时出现以下错误。

KeyError: 'indexer does not intersect a unique set of intervals'

有人能帮帮我吗？提前致谢。

Answer 1

它也可以像下面这样使用 numpy 广播和 bool 索引来完成

## load sample data
df1 = pd.DataFrame([('0', '2019-07-14 05:31:00', '0.020228', '0.026572'), ('1', '2019-07-14 06:32:00', '0.057780', '0.175499'), ('2', '2019-07-14 07:02:00', '0.076623', '0.875499')], columns=('id', 'timestamp', 'A', 'B'))
df2 = pd.DataFrame([('0', '2019-07-14 05:30:00', '2019-07-14 06:30:00', 'E1'), ('1', '2019-07-14 06:00:00', '2019-07-14 07:00:00', 'E2'), ('2', '2019-07-14 06:30:01', '2019-07-14 07:30:00', 'E3'), ('3', '2019-07-14 07:30:01', '2019-07-14 08:30:00', 'E4')], columns=('id', 'start', 'end', 'event'))

df1["timestamp"] = pd.to_datetime(df1["timestamp"])
df2["start"] = pd.to_datetime(df2["start"])
df2["end"] = pd.to_datetime(df2["end"])

解决方案

## df2[["start"]] is a column vector of size m and df1.timestamp.values is row 
## vector of size n then broad cast will result matrix of shape m,n which is 
## result of comparing each pair of m and n
compare = (df2[["start"]].values<df1.timestamp.values) & (df2[["end"]].values>df1.timestamp.values)

## get cell numbers which is in range 0 to matrix size which meets the condition
ind = np.arange(len(df1)*len(df2))[compare.ravel()]


## calculate row and column index from cell number
pd.concat([df2.iloc[ind//len(df1)].reset_index(drop=True), df1.iloc[ind%len(df1)].reset_index(drop=True)], axis=1, sort=False)

结果

    start               end                event    timestamp             A  B
0   2019-07-14 05:30:00 2019-07-14 06:30:00 E1  2019-07-14 05:31:00 0.020228    0.026572
1   2019-07-14 06:00:00 2019-07-14 07:00:00 E2  2019-07-14 06:32:00 0.057780    0.175499
2   2019-07-14 06:30:01 2019-07-14 07:30:00 E3  2019-07-14 06:32:00 0.057780    0.175499
3   2019-07-14 06:30:01 2019-07-14 07:30:00 E3  2019-07-14 07:02:00 0.076623    0.875499

编辑
为了回应@baccandr 的评论，这里有更多关于索引工作原理的解释。

比较后我们得到如下 bool 值的比较矩阵

array([[ True, False, False],
       [False,  True, False],
       [False,  True,  True],
       [False, False, False]])

• 您可以将此矩阵视为表格，列代表 df1 的索引，即 (0,1,2)，行代表 df2 的索引，即 (0 ,1,2,3)
• 如果 df1 和 df2 中对应的行满足条件，例如 df1 的第 0 行和 df2 的第 0 行，则单元格中的值为 True； df1的第2行和df2的第1行满足条件
• 要分别从 df1 和 df2 中找到满足条件的行，我们可以像 df1[compare.T] 和 df2[compare] 一样直接使用 compare 作为索引，但它不会以正确的顺序给出行配对。这将以递增的索引顺序给出行，但这并不总是正确的。
• 现在我们的目标是以正确的顺序获取满足条件的两个数据帧的索引。所以我们需要的是 df1 [0, 1, 2, 2] 的索引和 df2 [0,1,1,2] 的索引。使用 df1 和 df2 中的那些索引，我们从两者中以正确的顺序获得匹配
• 所以我们在这里所做的是从左到右然后向下计数矩阵的单元格，然后再向左向右计数，为每个单元格提供唯一的编号。然后过滤满足条件的单元格，转换为df1和df2的索引。

由于矩阵以矩阵格式表示 2 个数据帧的索引，我认为 np.where 可能不起作用。我们可以做到的另一种方法是使用 compare 作为 df2 的索引，并且只找到 df1 的索引，如下所示

这将为 df2 的每一行重复 df1 的索引，并按 df2 的顺序找到 df1 的索引

ind_df1 = np.tile(np.arange(len(df1)), len(df2))[compare.ravel()]
pd.concat([df2[compare].reset_index(drop=True), df1.iloc[ind_df1].reset_index(drop=True)], axis=1, sort=False)

我希望这能说明问题，如果您有其他想法，那么我很乐意在评论或回答中看到它