如何在Pandas中对列应用函数并引用前一列值计算客户Recency

Hey there! I get exactly what you're trying to do here—building a recency sequence where each month's value depends on the previous one. Let's break down how to solve this, since it's a classic state-dependent calculation that needs more than just a simple column-wise function.

The Core Idea #

Recency here is a cumulative state: if a customer purchases in month t, reset recency to 0; if not, increment the recency from month t-1. Since each value depends on the prior one, we can't process columns independently—we need to iterate through each customer's time sequence (row-wise) or use a vectorized approach with grouping.

Method 1: Row-wise Iteration (Intuitive for Small Datasets) #

If your dataset isn't massive, a row-wise custom function is straightforward to understand and implement. Here's how to do it:

import pandas as pd

# Sample DataFrame matching your structure (CustomerID, '-1' initial column, months 0-12)
data = {
    'CustomerID': [1, 2],
    '-1': [0, 0],
    '0': [0, 0],
    '1': [1, 1],
    '2': [0, 0],
    '3': [0, 0],
    '4': [1, 1],
    '5': [0, 0],
    '6': [0, 0],
    '7': [1, 1],
    '8': [0, 0],
    '9': [1, 1],
    '10': [0, 0],
    '11': [1, 1],
    '12': [0, 1]
}
df = pd.DataFrame(data)

def compute_recency(row):
    recency_vals = []
    prev_recency = row['-1']  # Start with the initial value from the '-1' column
    # Iterate through each month column (skip the '-1' initial column)
    for col in row.index[row.index != '-1']:
        current_purchase = row[col]
        if current_purchase == 0:
            # Reset recency to 0 if there's a purchase
            recency_vals.append(0)
            prev_recency = 0
        else:
            # Increment recency from the previous month
            new_recency = prev_recency + 1
            recency_vals.append(new_recency)
            prev_recency = new_recency
    # Combine initial '-1' value with computed recency values
    return pd.Series([row['-1']] + recency_vals, index=row.index)

# Apply the function to each customer (row)
result_df = df.apply(compute_recency, axis=1)

What this does:

• We define compute_recency to track the previous month's recency value as it loops through each column in the row.
• For each month, if the purchase value is 0, we reset recency to 0 and update our tracking value. If not, we add 1 to the previous recency.
• Using apply(axis=1) runs this logic for every customer (row) in your DataFrame.

For your second customer, this will output exactly the sequence you mentioned: 0, 1, 0, 0, 1, 0, 0, 1, 0, 1, 0, 1, 2 for months 0-12.

Method 2: Vectorized Grouping (Efficient for Large Datasets) #

If you're working with a big dataset, row-wise iteration can be slow. Instead, we can reshape the data into long format, use grouping to track reset points, then reshape back to wide format:

# Convert wide DataFrame to long format
long_df = df.melt(id_vars='CustomerID', var_name='Time', value_name='Purchase')
# Convert 'Time' column to numeric (so '-1' becomes -1, months 0-12 stay as is)
long_df['Time'] = long_df['Time'].astype(int)
# Sort by customer and time to ensure correct sequence
long_df = long_df.sort_values(['CustomerID', 'Time'])

# Create groups that reset every time a purchase occurs (Purchase == 0)
long_df['reset_group'] = long_df.groupby('CustomerID')['Purchase'].apply(lambda x: (x == 0).cumsum())

# Calculate recency: 0 for purchases, incrementing count for non-purchases in each group
long_df['Recency'] = long_df.groupby(['CustomerID', 'reset_group']).cumcount()
long_df['Recency'] = long_df['Recency'].where(long_df['Purchase'] != 0, 0)

# Convert back to wide format
result_df = long_df.pivot(index='CustomerID', columns='Time', values='Recency').reset_index()

What this does:

• We first melt the wide table into a long table where each row is a customer-month pair.
• The reset_group column creates a new group every time a purchase (0) happens—this lets us track consecutive non-purchase periods.
• cumcount() counts the position within each group, which gives us the incrementing recency value for non-purchases. We then set recency to 0 for all purchase rows.
• Finally, we pivot back to the original wide format to match your initial data structure.

Key Notes #

• Make sure your initial -1 column has a value of 0 (since there's no prior month before the first recorded month, recency starts at 0).
• Both methods will handle edge cases like consecutive non-purchase months (e.g., two months without buying will give recency values 1 then 2).

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