如何根据DataFrame数值筛选另一DataFrame的对应区间行？

Hey there! I’ve worked through your problem, and here are a couple of straightforward, practical ways to get the exact result you need. Let’s start by setting up the sample DataFrames first so you can test the code directly.

Step 1: Create Sample Data #

First, let’s replicate the DataFrames you described:

import pandas as pd

# Your main DataFrame with volume values
df = pd.DataFrame({'volume': [11, 24, 30]})

# The interval DataFrame with range and price data
df2 = pd.DataFrame({
    'range_low': [10, 21],
    'range_high': [20, 30],
    'price': [1, 2]
})

Method 1: Row-by-Row Matching (Great for Small Datasets) #

If your dataset isn’t massive, a simple apply function will work perfectly. We’ll write a helper function that checks each volume value against df2’s intervals and returns the matching row (or an empty DataFrame if no match exists).

def find_matching_interval(value):
    # Filter df2 to rows where the value falls within [range_low, range_high]
    matched_rows = df2[(df2['range_low'] <= value) & (value <= df2['range_high'])]
    return matched_rows

# Apply the function to every value in df['volume']
matches = df['volume'].apply(find_matching_interval)

# Check the results
print("Match for volume=11:")
print(matches.iloc[0])
print("\nMatch for volume=24:")
print(matches.iloc[1])
print("\nMatch for volume=30:")
print(matches.iloc[2])

Output:

Match for volume=11:
   range_low  range_high  price
0         10          20      1

Match for volume=24:
   range_low  range_high  price
1         21          30      2

Match for volume=30:
   range_low  range_high  price
1         21          30      2

Method 2: Vectorized Matching (Efficient for Large Datasets) #

If you’re working with big data, apply can be slow. Instead, use pd.merge_asof()—a vectorized method that’s way faster. This works best if your df2 intervals are non-overlapping and sorted (which they are in your example).

# First, ensure df2 is sorted by range_low (required for merge_asof)
df2_sorted = df2.sort_values('range_low')

# Merge df's volume values with the closest matching interval from df2
merged = pd.merge_asof(
    df.sort_values('volume'),
    df2_sorted,
    left_on='volume',
    right_on='range_low',
    direction='backward'
)

# Filter out any rows where volume exceeds the interval's range_high
final_result = merged[merged['volume'] <= merged['range_high']].reset_index(drop=True)

print("Final matched results:")
print(final_result)

Output:

Final matched results:
   volume  range_low  range_high  price
0      11         10          20      1
1      24         21          30      2
2      30         21          30      2

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