如何在Pandas DataFrame按日重采样时实现布尔逻辑聚合及自定义列计算？

Got it, let's fix that 'ract' column for your daily resampled trades DataFrame! You already have the OHLC aggregation on price and sum on quantity sorted out—here's a clean, straightforward way to add that ratio of buy quantity to total quantity per day:

Step 1: Create a Helper Column (Clean & Maintainable) #

First, add a column to your original trades DataFrame that isolates quantity from buy trades. This makes the resampling logic simple and easy to follow:

# Add column for buy-only quantity (0 for non-buy trades)
trades['buy_quantity'] = trades['quantity'].where(trades['buy'], 0)

Step 2: Resample with All Required Aggregations #

Now, resample your data daily and compute all needed metrics in one pass. We'll explicitly name each aggregated column to keep the output organized:

import pandas as pd

# Perform daily resampling with OHLC, total quantity, and buy quantity
resampled_trades = trades.resample('1D').agg(
    # OHLC metrics for price
    price_open=('price', 'first'),
    price_high=('price', 'max'),
    price_low=('price', 'min'),
    price_close=('price', 'last'),
    # Total daily quantity
    total_quantity=('quantity', 'sum'),
    # Sum of quantity from buy trades
    buy_quantity=('buy_quantity', 'sum')
)

Step 3: Calculate the 'ract' Ratio #

Finally, compute the ract column by dividing the buy quantity sum by the total quantity sum. We'll handle division-by-zero cases to avoid errors:

# Calculate ract, filling NaNs (from days with zero total quantity) with 0
resampled_trades['ract'] = resampled_trades['buy_quantity'].div(resampled_trades['total_quantity']).fillna(0)

# Optional: Replace any infinite values (edge case if total_quantity was 0)
resampled_trades['ract'] = resampled_trades['ract'].replace([float('inf'), float('-inf')], 0)

Alternative: No Helper Column (One-Step Resample) #

If you don't want to modify the original DataFrame, you can compute the buy quantity sum directly in the aggregation using a lambda. Just note this relies on index alignment, so it's slightly less explicit:

resampled_trades = trades.resample('1D').agg(
    price_open=('price', 'first'),
    price_high=('price', 'max'),
    price_low=('price', 'min'),
    price_close=('price', 'last'),
    total_quantity=('quantity', 'sum'),
    buy_quantity=('quantity', lambda x: x[trades.loc[x.index, 'buy']].sum())
)

# Compute ract as before
resampled_trades['ract'] = resampled_trades['buy_quantity'].div(resampled_trades['total_quantity']).fillna(0)

Quick Notes: #

• Explicit column names in agg() make your resampled DataFrame much easier to read and work with later.
• Handling division by zero is critical—otherwise, you'll get NaN or inf values on days with no trades.
• The helper column approach is generally preferred for readability and avoiding index-related bugs, especially if your DataFrame has non-unique indices.

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