在数据分析和处理中，我们经常需要比较两个dataframe中的数据，例如检查一个dataframe中的数值是否落在另一个dataframe定义的区间内，并统计满足条件的匹配项数量。对于小规模数据，使用循环（如for循环）配合df.loc或df.at进行逐行比较可能可行，但当数据量增大时，这种迭代方式会带来显著的性能问题，因为它无法充分利用pandas底层c语言实现的优化。
考虑以下两个DataFrame作为示例：
import pandas as pd
import numpy as np
# 示例 DataFrame df1
data1 = {
'Date': pd.to_datetime(['2023-08-03 00:00:00', '2023-08-03 12:00:00', '2023-11-28 00:00:00', '2023-11-29 08:00:00', '2023-11-30 04:00:00']),
'High': [29249.8, 29395.8, 37341.4, 38419.0, 38148.9],
'Mid': [29136.6, 29228.1, 37138.2, 38136.3, 37800.1],
'Low': [29152.3, 29105.0, 37254.1, 38112.0, 38040.0]
}
df1 = pd.DataFrame(data1)
# 示例 DataFrame df2
data2 = {
'Start': pd.to_datetime(['2023-11-28 00:00:00', '2023-11-24 12:00:00']),
'Top': [37341.4, 38432.9],
'Bottom': [37138.2, 37894.4]
}
df2 = pd.DataFrame(data2)
print("DataFrame df1:")
print(df1)
print("\nDataFrame df2:")
print(df2)输出的DataFrame结构如下：
DataFrame df1:
Date High Mid Low
0 2023-08-03 00:00:00 29249.8 29136.6 29152.3
1 2023-08-03 12:00:00 29395.8 29228.1 29105.0
2 2023-11-28 00:00:00 37341.4 37138.2 37254.1
3 2023-11-29 08:00:00 38419.0 38136.3 38112.0
4 2023-11-30 04:00:00 38148.9 37800.1 38040.0
DataFrame 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的每一行，检查df1中有多少行满足以下条件：
- df1.Date 不等于 df2.Start。
- df1.High 大于 df2.Bottom。
- df1.Mid 或 df1.Low 中的最大值小于 df2.Top。
并将匹配的数量存储在df2的一个新列Match中。
核心方法：使用交叉合并（Cross Merge）
为了避免低效的迭代，我们可以利用Pandas的merge函数，特别是how='cross'参数来实现交叉合并（笛卡尔积）。交叉合并会生成一个包含两个DataFrame所有行组合的新DataFrame，这使得我们可以对所有可能的配对进行一次性、矢量化的条件检查。
步骤详解：
准备df2的索引： 在进行交叉合并之前，为了在后续统计匹配项时能够准确地将结果关联回df2的原始行，我们首先对df2进行reset_index()操作。这会将df2的原始索引转换为一个普通列（通常命名为index），方便后续value_counts的统计。
执行交叉合并： 使用df2.reset_index().merge(df1, how='cross')创建两个DataFrame的笛卡尔积。这个操作会生成一个新DataFrame，其行数为len(df2) * len(df1)，每一行都代表df2的一行与df1的一行的组合。
-
应用筛选条件： 在合并后的DataFrame上，我们可以直接应用上述三个条件进行矢量化筛选。
- x.Start != x.Date: 检查合并后的Start列（来自df2）是否不等于Date列（来自df1）。
- x.Bottom
- x.Top > x[['Mid', 'Low']].max(axis=1): 检查合并后的Top列（来自df2）是否大于Mid和Low列（均来自df1）中的最大值。这里的axis=1至关重要，它确保了max函数是在每一行内部，对Mid和Low这两个值进行比较。
统计匹配项： 筛选出满足所有条件的行后，使用value_counts('index')来统计每个原始df2索引对应的匹配行数。'index'在这里指的是在reset_index()操作中生成的列，它代表了df2的原始行标识。
重新索引并赋值： value_counts的结果可能不包含所有原始df2的索引（如果某些行没有匹配项）。为了确保df2的所有原始行都有对应的匹配计数，我们使用.reindex(df2.index, fill_value=0)来对结果进行重新索引，将没有匹配项的df2行对应的Match值填充为0。最后，将这个结果赋值给df2的新列Match。
示例代码
import pandas as pd
import numpy as np
# 示例 DataFrame df1
data1 = {
'Date': pd.to_datetime(['2023-08-03 00:00:00', '2023-08-03 12:00:00', '2023-11-28 00:00:00', '2023-11-29 08:00:00', '2023-11-30 04:00:00']),
'High': [29249.8, 29395.8, 37341.4, 38419.0, 38148.9],
'Mid': [29136.6, 29228.1, 37138.2, 38136.3, 37800.1],
'Low': [29152.3, 29105.0, 37254.1, 38112.0, 38040.0]
}
df1 = pd.DataFrame(data1)
# 示例 DataFrame df2
data2 = {
'Start': pd.to_datetime(['2023-11-28 00:00:00', '2023-11-24 12:00:00']),
'Top': [37341.4, 38432.9],
'Bottom': [37138.2, 37894.4]
}
df2 = pd.DataFrame(data2)
# 使用交叉合并进行高效值范围检查和匹配计数
df2['Match'] = (df2.reset_index()
.merge(df1, how='cross')
.loc[lambda x:
(x.Start != x.Date) &
(x.Bottom < x.High) &
(x.Top > x[['Mid', 'Low']].max(axis=1))]
.value_counts('index')
.reindex(df2.index, fill_value=0))
print("\n结果 DataFrame df2:")
print(df2)输出结果：
结果 DataFrame df2:
Start Top Bottom Match
0 2023-11-28 00:00:00 37341.4 37138.2 0
1 2023-11-24 12:00:00 38432.9 37894.4 3性能与注意事项
内存消耗： cross merge操作会生成一个大小为len(df1) * len(df2)的新DataFrame。如果df1和df2的行数都非常大，这个中间DataFrame可能会占用巨大的内存，甚至导致MemoryError。在处理大规模数据集时，务必评估内存可用性。
矢量化优势： 尽管可能存在内存开销，但cross merge后进行条件筛选是高度矢量化的操作，其执行速度远超Python层面的显式循环。对于中等规模的数据集，这种方法通常是最高效的选择。
条件复杂性： 这种方法非常灵活，可以轻松地将复杂的比较逻辑（如本例中的max(axis=1)）集成到筛选条件中，而无需改变基本框架。
-
替代方案（针对超大数据集）： 如果内存确实成为瓶颈，且数据集规模巨大，可能需要考虑更高级的策略，例如：
- 分块处理： 将一个或两个DataFrame分成小块，然后对每个块执行交叉合并和计算。
- 使用专门的库： 某些大数据处理库（如Dask）提供了处理超出内存数据集的DataFrame操作。
- 算法优化： 对于特定的条件，可能存在更优化的算法，例如使用区间树（Interval Tree）或KD树（KD-Tree）来加速范围查询，但这通常需要更专业的实现。
总结
利用Pandas的cross merge结合矢量化条件筛选，是解决两个DataFrame之间高效值范围检查和匹配计数问题的强大方法。它将原本需要耗时循环的逻辑转化为高效的Pandas内置操作，显著提升了数据处理性能。然而，开发者在使用此方法时应充分考虑其内存消耗特性，并根据实际数据规模做出权衡。在内存允许的范围内，cross merge无疑是实现此类任务的首选方案。
