如何在Python中高效合并带容差的两个DataFrame（保留所有匹配项）

Approach Overview #

Got it, let's tackle this problem step by step. You need all matches within the tolerance (not just the nearest one) and want efficiency while keeping the 13-column shape of df2, plus adding the difference value to Col1. The best way here is to leverage NumPy's vectorized operations—they're way faster than Python loops or messy cross-merges, especially with larger datasets. The core idea is to calculate all pairwise differences between df1['Col0'] and df2['Col2'], filter those within your tolerance, then map the valid matches back to df2 to build df3.

Step-by-Step Implementation #

First, let's create sample data to demonstrate the workflow:

import pandas as pd
import numpy as np

# Sample df1 (shape l x 2)
df1 = pd.DataFrame({
    'Col0': [1.0, 3.0, 5.0],
    'Col1': ['LabelA', 'LabelB', 'LabelC']  # Second column from df1
})

# Sample df2 (shape p x 13)
df2 = pd.DataFrame({
    'Col0': ['RowX', 'RowY', 'RowZ', 'RowW', 'RowV'],
    'Col1': [0, 0, 0, 0, 0],  # This will be replaced with the calculated difference
    'Col2': [0.8, 3.2, 4.7, 5.3, 6.0],
    # Fill remaining 10 columns with dummy data to hit 13 total columns
    **{f'Col{i}': np.random.rand(5) for i in range(3, 13)}
})

tolerance = 0.3  # Your desired tolerance threshold

Now, the efficient merging logic:

# Extract the numeric columns we'll use for matching
df1_vals = df1['Col0'].values
df2_vals = df2['Col2'].values

# Calculate absolute differences between all pairs (vectorized, blazingly fast!)
abs_diff = np.abs(df1_vals[:, None] - df2_vals[None, :])

# Find all index pairs where the difference is within tolerance
# matches[0] = indices from df1, matches[1] = indices from df2
matches = np.where(abs_diff <= tolerance)

# Build df3 by selecting the matching rows from df2
df3 = df2.iloc[matches[1]].copy()

# Replace df2's Col1 with the exact difference from the match
df3['Col1'] = abs_diff[matches]

Why This Works #

• Efficiency: NumPy's broadcast operations handle the pairwise difference calculation in C-level code, avoiding slow Python loops. This is critical if l and p are large—cross-merges would bloat memory with unnecessary rows, but this method only keeps valid matches.
• All Matches Included: Unlike pandas.merge_asof (which only picks the closest match), this retains every pair where the value difference falls within your tolerance.
• Shape Compliance: df3 maintains the 13-column structure of df2, with Col1 now showing the precise difference between the matched df1['Col0'] and df2['Col2'] values.

Alternative (Less Efficient) Pure Pandas Method #

If you're working with small datasets and prefer a pandas-only approach, you can use a cross-merge followed by filtering. Note this can be memory-heavy for large l and p:

# Cross-merge df1's Col0 with df2, then filter by tolerance
df_cross = df1[['Col0']].merge(df2, how='cross')
df_cross['diff'] = np.abs(df_cross['Col0'] - df_cross['Col2'])
df3 = df_cross[df_cross['diff'] <= tolerance].drop(columns=['Col0']).rename(columns={'diff': 'Col1'})

Key Notes #

• Floating Point Precision: If working with floating-point numbers, you might swap the absolute difference check for np.isclose if you need relative tolerance, but the absolute check aligns with your stated requirement.
• Duplicate Entries: If a single df2 row matches multiple df1 rows within tolerance, it will appear multiple times in df3—this is intentional, as you asked to keep all valid matches.

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