如何在Pandas中高效比对两个DataFrame的数值范围（非迭代法）

问题场景 #

现有两个DataFrame：

df1 #

Date     High      Mid      Low
1   2023-08-03 00:00:00  29249.8  29136.6  29152.3
4   2023-08-03 12:00:00  29395.8  29228.1  29105.0
10  2023-08-04 12:00:00  29305.2  29250.1  29137.1
13  2023-08-05 00:00:00  29099.9  29045.3  29073.0
18  2023-08-05 20:00:00  29061.6  29047.1  29044.0
..                  ...      ...      ...      ...
696 2023-11-26 20:00:00  37732.1  37469.9  37370.0
703 2023-11-28 00:00:00  37341.4  37138.2  37254.1
707 2023-11-28 16:00:00  38390.7  38137.2  37534.4
711 2023-11-29 08:00:00  38419.0  38136.3  38112.0
716 2023-11-30 04:00:00  38148.9  37800.1  38040.0

df2 #

Start      Top   Bottom
0 2023-11-28 00:00:00  37341.4  37138.2
1 2023-11-24 12:00:00  38432.9  37894.4

需求 #

为df2新增Match列，统计df1中满足以下条件的行数：

• df1的Date与df2该行的Start日期不同
• df2该行的Bottom < df1的High
• df2该行的Top > df1中Mid和Low的最大值

现有迭代实现方式速度较慢：

for idx in df1.index:

    df2.loc[
        (df2.Start !=  df1.at[idx, 'Date']) &
        (df2.Bottom < df1.at[idx, 'High']) &
        (df2.Top > df1.loc[idx, ['Mid', 'Low']].max()),
        'Match'] += 1

询问是否有无需迭代的高效实现方式？

高效实现方案 #

可以利用Pandas的向量化运算和广播机制，完全避免循环，大幅提升效率，步骤如下：

1. 预处理df1，计算每行Mid和Low的最大值
   先给df1新增一列Mid_Low_Max，存储每行Mid和Low的最大值：

df1['Mid_Low_Max'] = df1[['Mid', 'Low']].max(axis=1)

2. 生成布尔匹配矩阵
   利用广播，生成一个形状为(len(df2), len(df1))的布尔矩阵，每个元素表示df2的某行与df1的某行是否满足所有条件：

# 条件1：日期不同
cond_date = df2['Start'].values[:, None] != df1['Date'].values
# 条件2：Bottom < High
cond_bottom = df2['Bottom'].values[:, None] < df1['High'].values
# 条件3：Top > Mid_Low_Max
cond_top = df2['Top'].values[:, None] > df1['Mid_Low_Max'].values

# 合并所有条件，满足所有条件则为True
match_matrix = cond_date & cond_bottom & cond_top

3. 统计每行匹配次数并赋值给df2的Match列
   对布尔矩阵的每一行求和，就是df2对应行的匹配次数：

df2['Match'] = match_matrix.sum(axis=1)

完整代码 #

# 预处理df1
df1['Mid_Low_Max'] = df1[['Mid', 'Low']].max(axis=1)

# 生成条件矩阵
cond_date = df2['Start'].values[:, None] != df1['Date'].values
cond_bottom = df2['Bottom'].values[:, None] < df1['High'].values
cond_top = df2['Top'].values[:, None] > df1['Mid_Low_Max'].values

# 计算匹配次数
df2['Match'] = (cond_date & cond_bottom & cond_top).sum(axis=1)

效率说明 #

• 避免了Python层面的循环，所有运算都在底层的NumPy/C实现中完成，效率远高于逐行迭代
• 广播机制自动处理了df2和df1的行对应关系，无需手动遍历索引

内容的提问来源于stack exchange，提问作者Viktor