Pandas计算指定支付码成功支付累计金额时内核崩溃的问题求助

I see the problem with your code — let's break down why it's causing a kernel crash and fix it with a more efficient approach.

Input DataFrame #

Your input data is sorted by order date, which is perfect for cumulative calculations:

Desired Output #

You want to compute the cumulative sum of successful payments (using 'OR' or 'IC' codes) up to the previous order for each customer:

Why Your Original Code Crashes #

The main issue is that your code combines filtered subsets with the full DataFrame in an inefficient way:

• df.query("PAYT_CODE == ('OR', 'IC')")["IS_PAYMENT_SUCCESSFUL"] returns a Series with only rows matching the condition.
• Multiplying this by df["ORDER_AMOUNT"] (the full column) causes index misalignment and creates a large intermediate object with NaNs, which eats up memory and leads to kernel crashes, especially on large datasets.
• Using lambda x: x.cumsum().shift().fillna(0) in transform adds unnecessary overhead compared to vectorized operations.

Fixed Solution #

We'll use vectorized operations (optimized for speed and memory) to achieve the desired result:

import pandas as pd
import numpy as np

# Sample data
data = {
    'CUST_NO': ['001', '001', '001', '002', '002', '002'],
    'ORDER_AMOUNT': [50, 20, 10, 55, 300, 215],
    'PAYT_CODE': ['OR', 'IC', 'IC', 'IC', 'MR', 'MR'],
    'IS_PAYMENT_SUCCESSFUL': [1, 0, 1, 1, 1, 0]
}
df = pd.DataFrame(data)

# Step 1: Create a helper column for valid amounts (only OR/IC and successful)
df['valid_amount'] = np.where(
    (df['PAYT_CODE'].isin(['OR', 'IC'])) & (df['IS_PAYMENT_SUCCESSFUL'] == 1),
    df['ORDER_AMOUNT'],
    0
)

# Step 2: Compute cumulative sum per customer, shift to get previous total, fill initial 0
df['CUMSUM_OR_IC_SUCCESSFUL'] = df.groupby('CUST_NO')['valid_amount'].cumsum().shift(1).fillna(0)

# Optional: Drop the helper column if not needed
df = df.drop('valid_amount', axis=1)

print(df)

Explanation #

1. Helper Column: valid_amount captures the order amount only if the payment code is 'OR'/'IC' and the payment was successful; otherwise, it's 0. This is a vectorized operation, so it's fast even on large datasets.
2. Cumulative Sum: We group by CUST_NO and compute the cumulative sum of valid_amount. Shifting by 1 ensures we get the sum of all valid payments before the current order.
3. Fill Initial Value: fillna(0) sets the first order's cumulative sum to 0, which matches your desired output.

Output #

Running this code will produce exactly the desired DataFrame you provided, without any kernel crashes.

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