如何使用Pandas高效判断DataFrame行的区间包含关系？

Hey there! As a fellow Pandas enthusiast, I totally get wanting to ditch those clunky nested loops—Pandas is built for vectorized operations, which are way faster and more idiomatic. Let's walk through a few better ways to solve your problem of finding rows in df2 that fit within any interval from df1.

First, let's set up your sample data so we can test the solutions:

import pandas as pd
import numpy as np

df1 = pd.DataFrame({
    'start': [1, 2],
    'value': ['TEST', 'TEST'],
    'end': [5, 3]
})

df2 = pd.DataFrame({
    'start': [2, 3],
    'value': ['TEST2', 'TEST2'],
    'end': [10, 4]
})

1. 推荐：Numpy广播的完全向量化方法 #

This is the most efficient approach, especially for large datasets, since it leverages numpy's broadcasting to avoid any explicit loops:

# Extract start/end columns as numpy arrays, reshape for broadcasting
df1_starts = df1['start'].to_numpy().reshape(1, -1)
df1_ends = df1['end'].to_numpy().reshape(1, -1)
df2_starts = df2['start'].to_numpy().reshape(-1, 1)
df2_ends = df2['end'].to_numpy().reshape(-1, 1)

# Check if each df2 row is contained in at least one df1 interval
contains_mask = ((df2_starts >= df1_starts) & (df2_ends <= df1_ends)).any(axis=1)

# Filter df2 to get the result
result = df2[contains_mask]
print(result)

输出： #

start   value  end
1      3  TEST2    4

Why this works: Broadcasting lets us compare every row in df2 against every row in df1 in one go. The any(axis=1) checks if there's at least one df1 interval that contains the df2 row. This is lightning fast compared to nested loops.

2. 交叉连接+过滤（适合小数据集） #

If your dataset is small and you prefer a more explicit approach, you can create a cross join of all df1/df2 row pairs, then filter for matches:

# Add a temporary key to enable cross join
df1['temp_key'] = 1
df2['temp_key'] = 1

# Create all possible row combinations
cross_join = pd.merge(df1, df2, on='temp_key').drop('temp_key', axis=1)

# Filter rows where df2's interval is inside df1's
matches = cross_join[
    (cross_join['start_y'] >= cross_join['start_x']) & 
    (cross_join['end_y'] <= cross_join['end_x'])
]

# Extract and deduplicate the df2 rows (since one df2 row might match multiple df1 rows)
result = matches[['start_y', 'value_y', 'end_y']].rename(
    columns={'start_y':'start', 'value_y':'value', 'end_y':'end'}
).drop_duplicates()

print(result)

Note: This uses more memory because it creates a row for every combination of df1 and df2, so avoid it for large datasets.

3. Apply逐行判断（可读性高，适合小数据） #

If you want code that's super readable (even if slightly less efficient), you can use apply to check each df2 row against df1:

def is_contained_in_any_df1_interval(row):
    # Check if this df2 row fits within any df1 interval
    return ((df1['start'] <= row['start']) & (df1['end'] >= row['end'])).any()

# Create a mask for df2 rows that meet the condition
contains_mask = df2.apply(is_contained_in_any_df1_interval, axis=1)

result = df2[contains_mask]
print(result)

This is easier to follow for beginners, but it's still a row-wise operation, so it won't be as fast as the vectorized numpy method for big data.

All of these methods will give you the exact result you're looking for—df2's second row. The numpy broadcasting method is the most "Pandas-idiomatic" since it leans into the vectorized operations that make Pandas so powerful.

内容的提问来源于stack exchange，提问作者E.J