使用Pandas实现按Unique ID分组的前3行值与另一DataFrame值的加权求和计算

Let's break down how to compute your out_value column exactly as described using pandas. Here's a step-by-step implementation that matches your example:

Step 1: Set Up Example Data #

First, let's recreate your sample DataFrames so you can test the code directly:

import pandas as pd

# DataFrame A
df_a = pd.DataFrame({
    'unique_id': [1, 1, 1, 1, 2, 2, 2, 2],
    'value': [45, 33, 18, 26, 66, 44, 22, 19],
    'out_value': [None] * 8
})

# DataFrame B (weights)
df_b = pd.DataFrame({'num_values': [0.15, 0.30, 0.18]})
weights = df_b['num_values'].values

Step 2: Define the Grouped Calculation Function #

We'll create a function that processes each group of rows (grouped by unique_id), computes the weighted sum of the previous 3 rows' value using the weights from DataFrame B:

def calculate_out_value(group):
    # Create a rolling window of the last 3 values for each row
    rolling_values = group['value'].rolling(window=3)
    
    # For each window, reverse the order (to match your calculation logic: closest previous row first)
    # then multiply by weights and sum to get out_value
    group['out_value'] = rolling_values.apply(
        lambda window: (window[::-1] * weights).sum(),
        raw=True  # Pass window as a numpy array for faster computation
    )
    return group

Step 3: Apply the Function to Each Group #

Finally, apply the function to each group and update DataFrame A:

df_a = df_a.groupby('unique_id').apply(calculate_out_value)

Verify the Result #

Running this code will give you exactly the expected output:

How It Works #

• Rolling Window: rolling(window=3) captures the 3 values immediately preceding the current row for each group.
• Reverse & Weighted Sum: window[::-1] reverses the window (so the most recent previous value aligns with the first weight in DataFrame B), then we multiply each value by its corresponding weight and sum the results.
• Raw Parameter: Using raw=True speeds up the calculation by passing the window as a numpy array instead of a pandas Series.

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