如何在Pandas中按Prospect_ID分组子数据框并分析销售周期？

Hey there! Let's work through your sales pipeline dashboard challenges using pandas—it’s the perfect tool for handling this kind of grouped time-series data. Below is a step-by-step solution tailored to your needs:

一、先做数据预处理 #

First, we need to fix data types (your dates use dd-mm-yyyy format, and numeric fields use commas as decimal separators):

import pandas as pd
from datetime import datetime

# Load your data (adjust this if you're reading from a CSV/Excel file)
data = [
    # Paste your raw data rows here, or use pd.read_csv()
]
df = pd.DataFrame(data)

# Convert date column to datetime format
df['Date_stage'] = pd.to_datetime(df['Date_stage'], format='%d-%m-%Y')

# Clean numeric columns (replace commas with dots and convert to proper types)
df['Probability'] = df['Probability'].str.replace(',', '.').astype(float)
df['Deal_size'] = df['Deal_size'].astype(int)
df['Weighted_Revenue'] = df['Weighted_Revenue'].astype(int)

二、实现你的核心分析需求 #

1. 计算各销售阶段转换的平均时长 #

We'll calculate time differences between adjacent stages per prospect, then aggregate averages by stage transition:

# Calculate days between consecutive stages for each prospect
df['days_diff'] = df.groupby('Prospect_ID')['Date_stage'].diff().dt.days

# Create a label for each stage transition (e.g., "Suspect → Prospect")
df['stage_transition'] = df.groupby('Prospect_ID')['Stage_name'].shift(1) + ' → ' + df['Stage_name']

# Compute average duration for each transition (ignore first stage rows with no prior data)
avg_stage_duration = df.dropna(subset=['days_diff']).groupby('stage_transition')['days_diff'].mean().round(2)
print("Average stage transition durations:\n", avg_stage_duration)

2. 计算每个Prospect的销售周期总时长 #

For completed deals, use the actual end date; for ongoing deals, use today's date as the "current end":

current_date = datetime.today()

def calculate_total_cycle(group):
    # Get the first Suspect date as the cycle start
    start_date = group[group['Stage_name'] == 'Suspect']['Date_stage'].min()
    # Check if the deal is completed
    if group['Status'].isin(['Won', 'Lost']).any():
        end_date = group['Date_stage'].max()
    else:
        end_date = current_date
    return (end_date - start_date).days

# Calculate total cycle days per prospect
sales_cycle_summary = df.groupby('Prospect_ID').apply(calculate_total_cycle).rename('total_cycle_days')
print("\nTotal sales cycle per prospect:\n", sales_cycle_summary)

3. 新增相邻阶段天数差列（适配未完成周期） #

We'll fill the final stage of ongoing deals with days since that stage started:

# First compute base days between consecutive stages
df['days_diff'] = df.groupby('Prospect_ID')['Date_stage'].diff().dt.days

# Fill the last stage of ongoing deals with days from stage start to today
def fill_ongoing_stage_days(group):
    if group['Status'].iloc[-1] == 'Open':
        last_row_idx = group.index[-1]
        group.loc[last_row_idx, 'days_diff'] = (current_date - group.loc[last_row_idx, 'Date_stage']).days
    return group

df = df.groupby('Prospect_ID').apply(fill_ongoing_stage_days)

三、识别每个Prospect的当前/最终阶段 #

You don't need to build a manual dictionary or iterate through stages! Pandas grouping handles this cleanly:

prospect_stage_summary = df.groupby('Prospect_ID').agg(
    stage_name=('Stage_name', 'last'),
    status=('Status', 'last'),
    latest_stage_date=('Date_stage', 'max')
).reset_index()

# Add a label to distinguish ongoing vs completed stages
prospect_stage_summary['stage_category'] = prospect_stage_summary['status'].apply(
    lambda x: 'Final Stage' if x in ['Won', 'Lost'] else 'Current Stage'
)
print("\nProspect current/final stage summary:\n", prospect_stage_summary)

四、适配后续数据更新 #

When you import new data, just re-run the preprocessing and analysis code above. Pandas will automatically detect new Prospect_IDs and new stage rows—no manual dictionary maintenance required!

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