Python Pandas每周时间维度特定数据聚合优化方案咨询

Nice question! That loop-based approach can get messy, error-prone, and slow with large datasets—let’s clean this up using Pandas' vectorized operations, which are far cleaner, more reliable, and efficient.

First, let’s clarify the core goal from your description: you want to track each cohort of new subscribers (grouped by their subscription week) and count how many of them called the center in their subscription week, the following week, the week after that, etc. Your original code seems to be calculating something slightly different (total callers who subscribed on or before a given week, calling that week), so we’ll align the solution with your stated business need first.

Step-by-Step Optimized Solution #

1. Prep Date Columns

First, ensure your date fields are datetime types, then convert them to weekly periods (matches your original freq='W' which uses Sunday as the week end):

# Ensure dates are datetime (skip if already done)
mcases['cred_date'] = pd.to_datetime(mcases['cred_date'])
mcases['date_ref'] = pd.to_datetime(mcases['date_ref'])

# Convert dates to weekly periods (e.g., "2024-05-12" for the week ending May 12)
mcases['subscription_week'] = mcases['cred_date'].dt.to_period('W')
mcases['call_week'] = mcases['date_ref'].dt.to_period('W')

2. Calculate Relative Weeks Since Subscription

For each call, compute how many weeks after the user’s subscription it occurred (0 = subscription week, 1 = first week after, etc.):

# Calculate weeks between call and subscription
mcases['weeks_since_sub'] = (mcases['call_week'] - mcases['subscription_week']).n

# Filter out any calls that happened before subscription (edge case handling)
valid_calls = mcases[mcases['weeks_since_sub'] >= 0]

3. Aggregate Cohort Caller Data

Group by subscription week and relative week, then count unique callers:

# Get a wide table: rows = subscription weeks, columns = weeks since sub, values = unique callers
cohort_call_tracking = valid_calls.groupby(['subscription_week', 'weeks_since_sub'])['account_id'].nunique().unstack(fill_value=0)

# Or get a long table (great for visualization/analysis):
cohort_call_tracking_long = valid_calls.groupby(['subscription_week', 'weeks_since_sub'])['account_id'].nunique().reset_index(name='unique_callers')

Example Output (Wide Table) #

This shows that 15 users who subscribed in the week ending Jan 7 called that same week, 8 called the next week, and 3 called two weeks later.

Why This Is Better Than Your Original Code #

• No loops: Eliminates repetitive, error-prone manual indexing (like handling i+1 edge cases in your loop).
• Cohort-aligned: Directly matches your business goal of tracking each subscription week’s users over time.
• Faster: Pandas' groupby operations are optimized for large datasets—this will outperform a Python loop by orders of magnitude with big data.
• Flexible: Switch between wide/long formats easily for reporting or visualization.

If You Actually Need the Original Logic #

If your original code’s intent was to count all subscribers up to week i who called during week i (not cohort-specific), we can simplify that too:

weekly_total_callers = valid_calls[valid_calls['subscription_week'] <= valid_calls['call_week']].groupby('call_week')['account_id'].nunique()

This gives a series where each index is a call week, and the value is the number of unique callers who subscribed on or before that week.

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