Pandas中按条件分组与偏移：订单簿缺失价格补全方案问询

Hey there! Let's work through this problem where we need to populate the 0 prices for D (delete) and E (execution) order records using the most recent N (new order) record's price for the same order_id—even when order_ids are reused over time.

Step-by-Step Approach #

The core idea is to track the last valid price from an N type record for each order_id, then propagate that value forward to all subsequent D/E records until a new N record for the same order_id replaces it.

1. Prepare the Example Data

First, let's set up a DataFrame matching your input:

import pandas as pd

data = {
    'type': ['N', 'E', 'E', 'D', 'N', 'N', 'D'],
    'order_id': [10, 10, 10, 10, 11, 10, 10],
    'price': [99, 0, 0, 0, 98, 97, 0]
}
df = pd.DataFrame(data)

2. Mark Invalid Prices as Missing

We'll replace the 0 prices for D/E types with pd.NA (missing values) so we can focus only on valid N type prices as our reference points:

# Replace non-N type 0 prices with missing values
df['price'] = df.apply(lambda row: row['price'] if row['type'] == 'N' else pd.NA, axis=1)

3. Forward Fill Prices by Order ID

Next, group the DataFrame by order_id and use forward fill (ffill()) to carry the last valid N price to all subsequent D/E records in the same group:

# Propagate the latest valid N price to D/E records in each order_id group
df['price'] = df.groupby('order_id')['price'].ffill()

Final Output #

Running the above code gives you the desired result:

Edge Case Notes #

• If a D/E record has an order_id with no prior N record, its price will stay pd.NA. You can adjust this with a fillna step (e.g., df['price'] = df['price'].fillna(0) if you want to revert to 0 for these cases).
• Critical: Make sure your DataFrame is sorted in chronological event order—forward fill relies on row order to determine which N record is the "most recent".

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