如何基于条件与GroupBy实现Pandas数据集自合并？附股票数据示例

Got it, let's tackle this problem step by step. The core requirement is to add a TargetHitBarSeqId column to your DataFrame, where each value represents the first subsequent BarSeqId (with a higher number than the current row's BarSeqId) where StockPrice >= LongProfitTarget. If no matching BarSeqId exists later in the data, we'll fill it with NaN.

First, let's replicate the original data you provided (note: I noticed a minor discrepancy in StockPrice values between your original and target data, but we'll focus on the logic for TargetHitBarSeqId since that's the key ask):

Step 1: Replicate the Original Data #

import pandas as pd
import numpy as np

# Original dataset
data = {
    'StockPrice': [105, 100, 103, 103, 104, 105],
    'BarSeqId': [0, 1, 2, 3, 4, 5],
    'LongProfitTarget': [109, 105, 107, 108, 110, 113]
}
df = pd.DataFrame(data)

Solution 1: Efficient Matching with merge_asof (Best for Large Datasets) #

If you're working with large volumes of data, merge_asof is the way to go—it operates in O(n log n) time, which is much faster than row-wise loops. Here's how it works:

1. Create a lookup table sorted by StockPrice (required for merge_asof).
2. Match each row's LongProfitTarget to the first StockPrice that meets or exceeds it, ensuring we only consider subsequent BarSeqIds.

# Create a lookup table sorted by StockPrice
lookup_df = df[['StockPrice', 'BarSeqId']].sort_values('StockPrice')

# Prepare the original DataFrame for matching
df_matched = df.copy().sort_values('LongProfitTarget')

# Perform backward asof merge to find the first StockPrice >= LongProfitTarget
matched = pd.merge_asof(
    df_matched,
    lookup_df,
    left_on='LongProfitTarget',
    right_on='StockPrice',
    direction='backward',
    allow_exact_matches=True
)

# Filter to only keep matches where the target BarSeqId is after the current one
matched = matched[matched['BarSeqId_y'] > matched['BarSeqId_x']]

# Group by original BarSeqId and get the earliest matching BarSeqId
target_hits = matched.groupby('BarSeqId_x')['BarSeqId_y'].min().rename('TargetHitBarSeqId')

# Merge back to the original DataFrame
result = df.merge(target_hits, left_on='BarSeqId', right_index=True, how='left')

print(result)

Solution 2: Row-Wise Lookup with apply (Best for Small Datasets) #

For smaller datasets, using apply makes the logic more intuitive and easier to debug. We'll write a helper function to check each row's subsequent records for the first matching BarSeqId:

def find_first_target_hit(row, df):
    # Only check rows that come after the current one
    subsequent_rows = df.loc[row.name + 1:]
    # Filter rows where StockPrice meets or exceeds the target
    matching_rows = subsequent_rows[subsequent_rows['StockPrice'] >= row['LongProfitTarget']]
    # Return the first matching BarSeqId, or NaN if none exist
    return matching_rows['BarSeqId'].min() if not matching_rows.empty else np.nan

# Apply the function to every row
df['TargetHitBarSeqId'] = df.apply(find_first_target_hit, args=(df,), axis=1)

print(df)

Optimization Note #

Since BarSeqId is a continuous incrementing number, using df.loc[row.name + 1:] ensures we only check rows that come after the current one, which cuts down on unnecessary computations compared to checking the entire DataFrame.

Example Output #

Running either solution will produce a DataFrame with the TargetHitBarSeqId column populated as expected. (Note: The discrepancy with your sample target data is likely due to a typo in the original StockPrice values—adjust the input data and the logic will still hold.)

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