如何基于匹配与区间条件合并不同形状的Pandas DataFrame？

Let's tackle your problem step by step. First, let's clarify the structure of your DataFrames (I've formatted them properly for clarity):

Sample DataFrames #

import pandas as pd

df1 = pd.DataFrame({
    'no1': [1, 1, 1, 1, 3],
    'no2': [10, 50, 60, 70, 12],
    'other1': ['foo', 'foo', 'cat', 'cat', 'cat']
})

df2 = pd.DataFrame({
    'no1': [1, 1, 3],
    'start': [2, 100, 5],
    'stop': [40, 200, 15],
    'other2': ['dog', 'dog', 'dog']
})

The Problem with Your Original Approach #

Your attempt using np.where failed because you're trying to compare Series of different lengths directly—pandas tries to align them by index, which causes the Can only compare identically-labeled Series objects error. Even if you fixed that, this method would be inefficient because it doesn't leverage pandas' optimized merging operations.

Efficient Solution #

The best approach is to first merge the DataFrames on the matching no1 column, then filter the rows where df1['no2'] falls between df2['start'] and df2['stop']. This uses pandas' vectorized operations, which are far faster than manual row-wise checks.

Method 1: Merge + Filter #

# Merge on 'no1' first (inner join to keep only matching no1 values)
merged_df = pd.merge(df1, df2, on='no1')

# Filter rows where no2 is between start and stop
filtered_df = merged_df[(merged_df['no2'] > merged_df['start']) & (merged_df['no2'] < merged_df['stop'])]

# Keep only the columns you need
df3 = filtered_df[['no1', 'no2', 'other1', 'other2']]

Method 2: One-Liner with query() #

For a more concise version, use query() which is optimized for such conditional filtering:

df3 = pd.merge(df1, df2, on='no1').query('start < no2 < stop')[['no1', 'no2', 'other1', 'other2']]

Result #

Both methods will give you the desired output:

no1  no2 other1 other2
0    1   10    foo    dog
4    3   12    cat    dog

Performance Note #

If you're working with extremely large datasets, these methods are still efficient because pandas handles merging and filtering in C-backed operations, avoiding slow Python loops. For even bigger data, you could consider using dask.dataframe for out-of-core processing, but the above pandas methods should suffice for most cases.

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