使用merge_asof基于多键带容差合并DataFrame报错的技术咨询

Great question! The issue here comes from a key limitation of merge_asof that's easy to miss, but there's a straightforward fix if your use case fits the tool's intended workflow.

为什么会报错？ #

First off, merge_asof only supports a single column for the on parameter—this is the column it uses to calculate the "nearest match" logic. When you pass multiple columns to on, you'll hit that MergeError because the function can't perform asof matching across multiple keys simultaneously.

That said, if you want to first group by one key, then perform nearest matching on another key within each group, you can use the by parameter alongside on to achieve this.

修正后的代码实现 #

In your scenario, we can use key2 as the grouping key (by) and key_1 as the column for nearest matching (on). A critical requirement for merge_asof is that both DataFrames are sorted by the by and on columns—don't skip this step!

import pandas as pd
import numpy as np

data_key_1 = np.arange(10).astype(float)
data_key_2 = np.arange(10).astype(float)
data_key_1_noisy = data_key_1 - 0.25*np.random.rand(10)
data_key_2_noisy = data_key_2 - 0.1*np.random.rand(10)
data_target = list('abcdefghij')

# 多键合并修正方案
df_1 = pd.DataFrame(zip(data_key_1[::2], data_key_2[::2]), columns=['key_1', 'key2'])
df_2 = pd.DataFrame(zip(data_key_1_noisy, data_key_2_noisy, data_target), columns=['key_1', 'key2', 'target'])

# 必须先按分组键和匹配键排序
df_1_sorted = df_1.sort_values(['key2', 'key_1'])
df_2_sorted = df_2.sort_values(['key2', 'key_1'])

# 使用by指定分组，on指定近邻匹配的键
df_result_2 = pd.merge_asof(
    df_1_sorted, 
    df_2_sorted, 
    by='key2',          # 先按key2分组
    on='key_1',         # 在每组内基于key_1找最近匹配
    direction='nearest',
    tolerance=0.5
)

# 恢复原DataFrame的顺序（可选）
df_result_2 = df_result_2.sort_index()
print(df_result_2)

预期输出 #

Running this code will give you exactly the result you're looking for:

key_1  key2 target
0    0.0   0.0      a
1    2.0   2.0      c
2    4.0   4.0      e
3    6.0   6.0      g
4    8.0   8.0      i

进阶：如果需要基于多键的综合距离匹配 #

If your goal isn't "group first, match second" but instead finding the closest row based on a combined distance across multiple keys (like Euclidean distance), merge_asof isn't the right tool for the job. For this scenario, you'll need to calculate pairwise distances between rows and pick the minimum:

from scipy.spatial.distance import cdist

# Extract the key columns for distance calculation
left_keys = df_1[['key_1', 'key2']].values
right_keys = df_2[['key_1', 'key2']].values

# Calculate pairwise Euclidean distances
distances = cdist(left_keys, right_keys)

# Find the index of the closest right row for each left row
closest_right_idx = np.argmin(distances, axis=1)

# Merge the results
df_result = df_1.join(df_2.iloc[closest_right_idx].reset_index(drop=True))

Keep in mind this approach has an O(n*m) time complexity, so it's only feasible for small datasets.

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